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)