Database Migration Workflow (/db-migration) — express

Stack flavor: express. ← Back to overview

[!CAUTION] Mandatory for ANY change to the database schema. Follow every step to prevent data loss or drift.

Trigger Conditions

• Files: Changes to backend/src/db/schema.ts
• Keywords: "Add a column", "New table", "Change database", "Migration"

---

1. Schema Update

• [ ] Drizzle: Modify backend/src/db/schema.ts
• Always use uuid type for primary and foreign keys — never TEXT
• Follow existing table patterns in the file
• [ ] Types: Update any TypeScript interfaces that reflect the DB structure

2. Generation

• [ ] Command: Run the migration generator:

  cd backend && npm run migrate:generate
  

  This runs drizzle-kit generate and outputs a .sql file to backend/migrations/

• [ ] Review (CRITICAL): Open the generated .sql file and verify:

• No unexpected DROP statements — if you see any, investigate before proceeding
• Manual IF NOT EXISTS guards: drizzle-kit does NOT add these automatically — you MUST add them:
  • CREATE TABLE IF NOT EXISTS
  • ALTER TABLE ... ADD COLUMN IF NOT EXISTS
  • CREATE INDEX IF NOT EXISTS
• Complex constraints: Wrap in DO $$ ... END $$ blocks to handle duplicates gracefully
• See .agent/rules/migration-safety.md for the full idempotency rules

3. Application

• [ ] Apply locally:

  cd backend && npm run migrate:dev
  

  This runs tsx src/migrate.ts against your local/dev database

• [ ] Verification: Query the database (or run cd backend && npm run db:studio) to confirm the new structure exists

4. Backend Update

• [ ] Drizzle Queries: Update routes in backend/src/routes/ to use the new fields/tables
• [ ] Validation: Update Zod schemas in the affected route file to include new fields
• [ ] Types: Confirm Drizzle's inferred TypeScript types are correct (cd backend && npm run type-check)

5. Production

• [ ] Build: cd backend && npm run build
• [ ] Deploy: The production migration runs via npm run migrate (node dist/src/migrate.js) on startup