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/ - Complete schema examples (all files <500 lines)

• drizzle-schemas.md - TypeScript/Drizzle examples

• sqlmodel-schemas.md - Python/SQLModel examples

• migrations.md - Migration patterns

• rls-policies.md - Row Level Security

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

• field-naming.md - Naming conventions

• indexing.md - Index patterns

• relationships.md - Foreign keys and relations

• templates/ - Copy-paste schema templates

• 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 and examples/sqlmodel-schemas.md for complete examples.

3. Standard Timestamps

All tables must have created_at and updated_at.

// 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 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 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 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 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 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 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", "tenant_id", "snake_case"

Template References

• TypeScript: cvi-template (Drizzle ORM + PlanetScale)

• Python: cvi-backend-template (SQLModel + PostgreSQL)

Critical Reminders

• snake_case - ALL database fields use snake_case (never camelCase)

• tenant_id - Required on all tables for multi-tenant isolation

• Timestamps - created_at and updated_at on all tables

• RLS policies - Enable on all tables with tenant_id

• Indexing - Index tenant_id, foreign keys, and unique fields

• Migrations - Always use migrations (Drizzle Kit or Alembic)

• Field naming - Booleans use is_/has_/can_ prefix, timestamps use _at suffix

• No raw SQL - Use ORM for queries (prevents SQL injection)

• Soft deletes - Use deleted_at timestamp, not hard deletes

• Foreign keys - Always define relationships explicitly

Next Steps

• Need examples? See examples/ for Drizzle and SQLModel schemas

• Need references? See reference/ for naming, indexing, relationships

• Need templates? See templates/ for copy-paste schema starters

• Need checklists? Use checklists/ for schema validation