grey-haven-data-modeling

# 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).notN