Grey Haven Data Modeling Standards

Design database schemas for Grey Haven Studio's multi-tenant SaaS applications using SQLModel (FastAPI) and Drizzle ORM (TanStack Start) with PostgreSQL and RLS.

Multi-Tenant Principles

CRITICAL: Every Table Requires tenant_id

// ✅ CORRECT - Drizzle
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(), // REQUIRED!
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
  // ... other fields
});

# ✅ CORRECT - SQLModel
class User(SQLModel, table=True):
    __tablename__ = "users"
    
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    tenant_id: UUID = Field(foreign_key="tenants.id", index=True) # REQUIRED!
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    # ... other fields

Naming Conventions

ALWAYS use snake_case (never camelCase):

// ✅ CORRECT
email_address: text("email_address")
created_at: timestamp("created_at")
is_active: boolean("is_active")
tenant_id: uuid("tenant_id")

// ❌ WRONG
emailAddress: text("emailAddress")  // WRONG!
createdAt: timestamp("createdAt")   // WRONG!

Standard Fields (Required on All Tables)

// Every table should have:
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()
deleted_at: timestamp("deleted_at") // For soft deletes (optional)

Core Tables

1. Tenants Table (Root)

// Drizzle
export const tenants = pgTable("tenants", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  slug: text("slug").notNull().unique(),
  is_active: boolean("is_active").default(true).notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

# SQLModel
class Tenant(SQLModel, table=True):
    __tablename__ = "tenants"
    
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    name: str = Field(max_length=255)
    slug: str = Field(max_length=100, unique=True)
    is_active: bool = Field(default=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)

2. Users Table (With Tenant Isolation)

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

// Index for tenant_id
export const usersTenantIndex = index("users_tenant_id_idx").on(users.tenant_id);

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

Relationships

One-to-Many

// Drizzle - User has many Posts
export const posts = pgTable("posts", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  user_id: uuid("user_id").notNull(),
  title: text("title").notNull(),
  // ... other fields
});

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

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

Many-to-Many

// Drizzle - User has many Roles through UserRoles
export const user_roles = pgTable("user_roles", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  user_id: uuid("user_id").notNull(),
  role_id: uuid("role_id").notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
});

// Indexes for join table
export const userRolesUserIndex = index("user_roles_user_id_idx").on(user_roles.user_id);
export const userRolesRoleIndex = index("user_roles_role_id_idx").on(user_roles.role_id);

RLS Policies

Enable RLS on All Tables

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

-- Tenant isolation policy
CREATE POLICY "tenant_isolation"
ON users
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Admin override policy
CREATE POLICY "admin_override"
ON users
FOR ALL
TO admin_role
USING (true);

Indexes

Required Indexes

// ALWAYS index tenant_id
export const usersTenantIndex = index("users_tenant_id_idx").on(users.tenant_id);

// Index foreign keys
export const postsUserIndex = index("posts_user_id_idx").on(posts.user_id);

// Composite indexes for common queries
export const postsCompositeIndex = index("posts_tenant_user_idx")
  .on(posts.tenant_id, posts.user_id);

Migrations

Drizzle Kit

# Generate migration
bun run db:generate

# Apply migration
bun run db:migrate

# Rollback migration (manual)

Alembic (SQLModel)

# Generate migration
alembic revision --autogenerate -m "add users table"

# Apply migration
alembic upgrade head

# Rollback migration
alembic downgrade -1

Supporting Documentation

All supporting files are under 500 lines per Anthropic best practices:

• [examples/](examples/) - Complete schema examples

- [drizzle-models.md](examples/drizzle-models.md) - Drizzle schema examples - [sqlmodel-models.md](examples/sqlmodel-models.md) - SQLModel examples - [relationships.md](examples/relationships.md) - Relationship patterns - [rls-policies.md](examples/rls-policies.md) - RLS policy examples - [INDEX.md](examples/INDEX.md) - Examples navigation

• [reference/](reference/) - Data modeling references

- [naming-conventions.md](reference/naming-conventions.md) - Field naming rules - [indexes.md](reference/indexes.md) - Index strategies - [migrations.md](reference/migrations.md) - Migration patterns - [INDEX.md](reference/INDEX.md) - Reference navigation

• [templates/](templates/) - Copy-paste ready templates

- [drizzle-table.ts](templates/drizzle-table.ts) - Drizzle table template - [sqlmodel-table.py](templates/sqlmodel-table.py) - SQLModel table template

• [checklists/](checklists/) - Schema checklists

- [schema-checklist.md](checklists/schema-checklist.md) - Pre-PR schema validation

When to Apply This Skill

Use this skill when:

• Creating new database tables
• Designing multi-tenant data models
• Adding relationships between tables
• Creating RLS policies
• Generating database migrations
• Refactoring existing schemas
• Implementing soft deletes
• Adding indexes for performance

Template Reference

These patterns are from Grey Haven's production templates:

• cvi-template: Drizzle ORM + PostgreSQL + RLS
• cvi-backend-template: SQLModel + PostgreSQL + Alembic

Critical Reminders

1. tenant_id: Required on EVERY table (no exceptions!)
2. snakecase: All fields use snakecase (NEVER camelCase)
3. Timestamps: createdat and updatedat on all tables
4. Indexes: Always index tenant_id and foreign keys
5. RLS policies: Enable RLS on all tables for tenant isolation
6. Soft deletes: Use deleted_at instead of hard deletes
7. Foreign keys: Explicitly define relationships
8. Migrations: Test both up and down migrations
9. Email fields: Name as email_address (not email)
10. Boolean fields: Use is/has/can_ prefix