Db Design Migrations

1. Define domain intent:
   • What entity/state is missing?
   • Is this a new table, enum value, column, or constraint?
2. Map ownership:
   • DB schema (packages/db/schema/*)
   • API read/write services (apps/api/service/**)
   • GraphQL schema/resolvers (if API contract changes)
   • Web feature hooks/components (if UI depends on new fields)
3. Decide compatibility:
   • Can old code run during rollout?
   • Do we need phased migration/backfill?
4. Define data safety:
   • Indexes/uniques needed?
   • FK onDelete behavior (cascade vs set null)?

Schema conventions in this repo

• IDs: text("id").primaryKey().$defaultFn(() => createId())
• Timestamps: timestamp("<name>", { withTimezone: true })
• Most tables include createdAt and updatedAt with .defaultNow()
• Enums: pgEnum(...) defined near related table
• Types: always export <Type> and New<Type> from schema file
• Add new schema exports in packages/db/schema/index.ts

Implementation workflow

1. Update/add schema files under packages/db/schema/.
2. Export new schema module from packages/db/schema/index.ts (if new file).
3. Generate migration from repo root:

bun run db:generate --name <descriptive_name>

4. Review generated migration for correctness (do not hand-edit).
5. Apply migrations:

bun run db:migrate

6. If DB changes affect API contract, run GraphQL codegen:

cd apps/api && bun run codegen
cd apps/web && bun run codegen

7. Run typechecks for touched apps:

bunx tsc --noEmit -p apps/api/tsconfig.json
bunx tsc --noEmit -p apps/web/tsconfig.json

Output format for planning responses

• Change summary
• DB design decisions (with tradeoffs)
• Files to modify (exact paths)
• Migration plan (including phased rollout if needed)
• Validation commands
• Risks + rollback/mitigation plan
• Approval checklist

Notes on rollback

• Prefer forward-fix migrations over destructive rollback.
• If a migration is unsafe for live traffic, split into phases and gate behavior in API until backfill completes.