Database Schema

ORM: Drizzle ORM
Database: PostgreSQL (via Supabase)

Schema Overview

erDiagram
    agencies ||--o{ users : has
    users ||--o{ projects : owns
    projects ||--o{ project_versions : has
    projects ||--o{ generated_assets : has
    projects ||--o{ design_briefs : has

    agencies {
        uuid id PK
        string name
        string slug
        string plan
        timestamp created_at
    }

    users {
        uuid id PK
        uuid agency_id FK
        string email
        string role
        timestamp created_at
    }

    projects {
        uuid id PK
        uuid user_id FK
        string name
        string status
        jsonb brief
        jsonb current_output
        timestamp created_at
        timestamp updated_at
    }

    design_briefs {
        uuid id PK
        uuid project_id FK
        jsonb style
        jsonb brand_personality
        jsonb sections
        jsonb color_logic
        string motion_level
        timestamp created_at
    }

    generated_assets {
        uuid id PK
        uuid project_id FK
        string type
        string url
        jsonb prompt_used
        timestamp created_at
    }

Drizzle Schema Files

Location: src/lib/server/db/schema/

projects.ts

import { pgTable, uuid, text, jsonb, timestamp } from 'drizzle-orm/pg-core'

export const projects = pgTable('projects', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: uuid('user_id').notNull().references(() => users.id),
  name: text('name').notNull(),
  status: text('status').notNull().default('draft'),
  brief: jsonb('brief'),
  currentOutput: jsonb('current_output'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow()
})

design_briefs.ts

export const designBriefs = pgTable('design_briefs', {
  id: uuid('id').defaultRandom().primaryKey(),
  projectId: uuid('project_id').notNull().references(() => projects.id, { onDelete: 'cascade' }),
  style: jsonb('style').notNull(),
  brandPersonality: jsonb('brand_personality').notNull(),
  sections: jsonb('sections').notNull(),
  colorLogic: jsonb('color_logic').notNull(),
  motionLevel: text('motion_level').notNull().default('subtle'),
  createdAt: timestamp('created_at').defaultNow()
})

Common Queries

Fetch project with latest brief

const project = await db
  .select()
  .from(projects)
  .leftJoin(designBriefs, eq(designBriefs.projectId, projects.id))
  .where(eq(projects.id, projectId))
  .orderBy(desc(designBriefs.createdAt))
  .limit(1)

Update project status

await db
  .update(projects)
  .set({ status: 'generating', updatedAt: new Date() })
  .where(eq(projects.id, projectId))

Migrations

See Database Migrations Guide