Grey Haven Database Conventions

Database schema standards for Drizzle ORM (TypeScript) and SQLModel (Python).

Follow these conventions for all Grey Haven multi-tenant database schemas.

Supporting Documentation

• [examples/](examples/) - Complete schema examples (all files <500 lines)

- [drizzle-schemas.md](examples/drizzle-schemas.md) - TypeScript/Drizzle examples - [sqlmodel-schemas.md](examples/sqlmodel-schemas.md) - Python/SQLModel examples - [migrations.md](examples/migrations.md) - Migration patterns - [rls-policies.md](examples/rls-policies.md) - Row Level Security

• [reference/](reference/) - Detailed references (all files <500 lines)

- [field-naming.md](reference/field-naming.md) - Naming conventions - [indexing.md](reference/indexing.md) - Index patterns - [relationships.md](reference/relationships.md) - Foreign keys and relations

• [templates/](templates/) - Copy-paste schema templates
• [checklists/](checklists/) - Schema validation checklists

Critical Rules

1. snake_case Fields (ALWAYS)

Database columns MUST use snake_case, never camelCase.

// ✅ CORRECT
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  tenant_id: uuid("tenant_id").notNull(),
  email_address: text("email_address").notNull(),
});

// ❌ WRONG - Don't use camelCase
export const users = pgTable("users", {
  createdAt: timestamp("createdAt"),  // WRONG!
  tenantId: uuid("tenantId"),        // WRONG!
});

2. tenant_id Required (Multi-Tenant)

Every table MUST include tenant_id for data isolation.

// TypeScript - Drizzle
export const organizations = pgTable("organizations", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),  // REQUIRED
  name: text("name").notNull(),
});

# Python - SQLModel
class Organization(SQLModel, table=True):
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    tenant_id: UUID = Field(foreign_key="tenants.id", index=True)  # REQUIRED
    name: str = Field(max_length=255)

See [examples/drizzle-schemas.md](examples/drizzle-schemas.md) and [examples/sqlmodel-schemas.md](examples/sqlmodel-schemas.md) for complete examples.

3. Standard Timestamps

All tables must have createdat and updatedat.

// TypeScript - Reusable timestamps
export const baseTimestamps = {
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull().$onUpdate(() => new Date()),
};

export const teams = pgTable("teams", {
  id: uuid("id").primaryKey().defaultRandom(),
  ...baseTimestamps,  // Spread operator
  tenant_id: uuid("tenant_id").notNull(),
  name: text("name").notNull(),
});

# Python - Mixin pattern
class TimestampMixin:
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow, sa_column_kwargs={"onupdate": datetime.utcnow})

class Team(TimestampMixin, SQLModel, table=True):
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    tenant_id: UUID = Field(index=True)
    name: str = Field(max_length=255)

4. Row Level Security (RLS)

Enable RLS on all tables with tenant_id.

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policy
CREATE POLICY "tenant_isolation" ON users
  FOR ALL TO authenticated
  USING (tenant_id = (current_setting('request.jwt.claims')::json->>'tenant_id')::uuid);

See [examples/rls-policies.md](examples/rls-policies.md) for complete RLS patterns.

Quick Reference

Field Naming Patterns

Boolean fields: Prefix with is, has, can_

is_active: boolean("is_active")
has_access: boolean("has_access")
can_edit: boolean("can_edit")

Timestamp fields: Suffix with _at

created_at: timestamp("created_at")
updated_at: timestamp("updated_at")
deleted_at: timestamp("deleted_at")
last_login_at: timestamp("last_login_at")

Foreign keys: Suffix with _id

tenant_id: uuid("tenant_id")
user_id: uuid("user_id")
organization_id: uuid("organization_id")

See [reference/field-naming.md](reference/field-naming.md) for complete naming guide.

Indexing Patterns

Always index:

• tenant_id (for multi-tenant queries)
• Foreign keys (for joins)
• Unique constraints (email, slug)
• Frequently queried fields

// Composite index for tenant + lookup
export const usersIndex = index("users_tenant_email_idx").on(
  users.tenant_id,
  users.email_address
);

See [reference/indexing.md](reference/indexing.md) for index strategies.

Relationships

One-to-many:

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),  // User has many posts
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.user_id], references: [users.id] }),
}));

See [reference/relationships.md](reference/relationships.md) for all relationship patterns.

Drizzle ORM (TypeScript)

Installation:

bun add drizzle-orm postgres
bun add -d drizzle-kit

Basic schema:

// db/schema.ts
import { pgTable, uuid, text, timestamp, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
  tenant_id: uuid("tenant_id").notNull(),
  email_address: text("email_address").notNull().unique(),
  is_active: boolean("is_active").default(true).notNull(),
});

Generate migration:

bun run drizzle-kit generate:pg
bun run drizzle-kit push:pg

See [examples/migrations.md](examples/migrations.md) for migration workflow.

SQLModel (Python)

Installation:

pip install sqlmodel psycopg2-binary

Basic model:

# app/models/user.py
from sqlmodel import Field, SQLModel
from uuid import UUID, uuid4
from datetime import datetime

class User(SQLModel, table=True):
    __tablename__ = "users"
    
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    tenant_id: UUID = Field(foreign_key="tenants.id", index=True)
    email_address: str = Field(unique=True, index=True, max_length=255)
    is_active: bool = Field(default=True)

Generate migration:

alembic revision --autogenerate -m "Add users table"
alembic upgrade head

See [examples/migrations.md](examples/migrations.md) for Alembic setup.

When to Apply This Skill

Use this skill when:

• ✅ Designing new database schemas
• ✅ Creating Drizzle or SQLModel models
• ✅ Writing database migrations
• ✅ Setting up RLS policies
• ✅ Adding indexes for performance
• ✅ Defining table relationships
• ✅ Reviewing database code in PRs
• ✅ User mentions: "database", "schema", "Drizzle", "SQLModel", "migration", "RLS", "tenantid", "snakecase"

Template References

• TypeScript: cvi-template (Drizzle ORM + PlanetScale)
• Python: cvi-backend-template (SQLModel + PostgreSQL)

Critical Reminders

1. snakecase - ALL database fields use snakecase (never camelCase)
2. tenant_id - Required on all tables for multi-tenant isolation
3. Timestamps - createdat and updatedat on all tables
4. RLS policies - Enable on all tables with tenant_id
5. Indexing - Index tenant_id, foreign keys, and unique fields
6. Migrations - Always use migrations (Drizzle Kit or Alembic)
7. Field naming - Booleans use is/has/can prefix, timestamps use at suffix
8. No raw SQL - Use ORM for queries (prevents SQL injection)
9. Soft deletes - Use deleted_at timestamp, not hard deletes
10. Foreign keys - Always define relationships explicitly

Next Steps

• Need examples? See [examples/](examples/) for Drizzle and SQLModel schemas
• Need references? See [reference/](reference/) for naming, indexing, relationships
• Need templates? See [templates/](templates/) for copy-paste schema starters
• Need checklists? Use [checklists/](checklists/) for schema validation