data-modeling

What this skill does

# 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

```typescript
// ✅ 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
});
```

```python
# ✅ 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):

```typescript
// ✅ 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)

```typescript
// 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)

```typescript
// 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(),
});
```

```python
# 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)

```typescript
// 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);
```

```python
# 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

```typescript
// 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

```typescript
// 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

```sql
-- 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

```typescript
// 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

```bash
# Generate migration
bun run db:generate

# Apply migration
bun run db:migrate

# Rollback migration (manual)
```

### Alembic (SQLModel)

```bash
# 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. **snake_case**: All fields use snake_case (NEVER camelCase)
3. **Timestamps**: created_at and updated_at on all tables
4. **Indexes**: Always index tenant_id and foreign keys
5. **RLS policies**: Enable RLS on all tables for te