db-migration

In plain terms

Makes database schema changes safely, using the right migration tools for your stack so nothing breaks.

What this skill does

Safe database schema updates using SQLModel and Alembic migrations.

fastapiexpress

[!CAUTION] This workflow is mandatory for ANY change to the database schema to prevent data loss or drift.

⚠️ Trigger Conditions

• Files: Changes to backend/app/models/*.py.
• Keywords: "Add a column", "New table", "Change database", "Migration".

1. Schema Update

• [ ] SQLModel: Modify the relevant model in backend/app/models/[model].py.
• [ ] Schemas: Update corresponding Pydantic schemas in backend/app/schemas/[model].py if request/response shapes change.

2. Generate Migration

• [ ] Autogenerate: Create a new Alembic revision:

  cd backend && alembic revision --autogenerate -m "describe your change here"
  

• [ ] Review (CRITICAL): Open the generated file in backend/alembic/versions/ and verify:
  • No unexpected DROP statements.
  • SAFE SQL: Ensure all statements follow the Migration Safety Rules.
  • Add IF NOT EXISTS guards to tables/columns/indexes.
  • Wrap complex constraints in DO $$ blocks.

3. Apply Migration

• [ ] Local Run: Apply to the local/dev database:

  cd backend && alembic upgrade head
  

• [ ] Verification: Query the database (or use alembic current) to confirm the new structure is in place.
• [ ] Downgrade Test: Verify the downgrade() function works without errors:

  cd backend && alembic downgrade -1 && alembic upgrade head
  

4. Backend Update

• [ ] Routers/Services: Update any FastAPI routers or services to use the new fields/tables.
• [ ] Validation: Update Pydantic schemas in backend/app/schemas/ to include new fields.

5. Frontend Sync

• [ ] OpenAPI: Run /openapi-sync to regenerate frontend/src/types/api.ts from the updated backend schema.

[!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