Database Migration Workflow (/db-migration) — fastapi

Stack flavor: fastapi. ← Back to overview

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