Context as Code
Documentation

Database Migrations

Tool-agnostic principles (source of truth, generate-then-review, naming, constraints checklist, squashing). Then: Alembic for Python services; Drizzle Kit for TypeScript/Next.js services.

Show:

Status: Active
Purpose: Tool-agnostic rules for schema evolution, migration authoring, and review. Applies whether you use Alembic, Drizzle Kit, Prisma Migrate, Flyway, or another migrator. For implementation details, see Alembic (Python) and Drizzle Kit (TypeScript).

1. Source of truth

  • Declarative schema in the repository is canonical — ORM models, schema.ts, Prisma schema, or equivalent define what the database should be.
  • Migration files are derived artifacts in the normal case: produced by the tool’s generator from that schema (or model diff), then committed.
  • Goal: Repeatability, clear diffs in PRs, and confidence that generator output matches the declared schema (running generate after a schema change should produce no unexpected diff).

2. Authoring workflow (default path)

  1. Change the schema / models in code.
  2. Generate a migration using the project’s standard command (e.g. Alembic revision --autogenerate, Drizzle Kit generate).
  3. Review the generated SQL (or operations) for correctness, safety, and performance.
  4. Test apply (and downgrade where supported) locally and in CI if applicable.
  5. Open a PR with the migration and schema change together.

Exceptions (must be explicit):

  • Hand-written or heavily edited SQL — acceptable for data backfills, one-off fixes, complex multi-step cutovers, or when the generator cannot express the change. The PR description (or a short comment in the migration) must state why generation was not used.
  • Fully AI-authored migration SQL without a generator step — treat like hand-written: rare, must be justified and reviewed like production code.

3. Naming and ordering

  • Migrations form an ordered history. Names (or tags) should be human-scannable in logs, PRs, and support incidents.
  • Prefer a consistent convention aligned with your tool’s journal, for example:
    • Sequential numeric prefix + short descriptive_snake_case slug (e.g. 0003_add_pack_purchases_table), or
    • The tool’s default tag only if the team explicitly accepts opaque names and documents that choice.
  • Renaming generated files/tags is allowed only when it does not break the migration journal / checksum contract your tool enforces — typically before merge, before any shared or production database has applied the migration. Never rename or reorder migrations that have already been applied downstream.
  • Document the chosen convention for each service in that service’s README or docs/07-data-architecture/ (instance), and point here for principles.

4. Indexes, constraints, and table design

When adding or changing tables, use a checklist (adapt to your domain):

  • Foreign keys where relationships are enforced in the database.
  • CHECK constraints (or equivalent) for enums, status fields, and invariants that must hold at the row level.
  • Indexes for common filters, sort keys, and join columns; avoid redundant indexes.
  • Uniqueness (unique constraints or partial uniques) where the domain requires it.
  • Standard columns per service baseline where applicable (e.g. UUID primary keys, created_at / updated_at, soft-delete column) — align new tables with existing patterns in the same service.
  • Named constraints and indexes where the dialect supports it, for clearer errors and diffs.

Record service-specific baselines in 07 Data Architecture or the service README; keep this document as the generic checklist.

5. Squashing and baseline migrations

  • Replacing a long chain of early migrations with a single baseline (or a squash) is allowed only when no environment that matters still depends on applying that history: e.g. no production database, no shared staging that must replay from zero, no replica that must stay in sync with the old chain.
  • Before squashing: confirm all deploy targets; document the decision in the PR and in service or data-architecture docs.
  • Never squash or rewrite history for a database that has already applied the old migrations without a controlled cutover plan (new DB, dump/restore, or explicit migration of migration metadata).

6. Review and operations

  • PRs should review migration safety (locks, table size, backfills), rollback story where applicable, and alignment with schema and naming conventions.
  • Irreversible or long-running migrations should be called out and, if needed, scheduled or shipped in phases.

Related documents

  • Alembic migration patterns (Python / FastAPI)
  • Drizzle Kit migration patterns (TypeScript)
  • Session Management
  • Unit of Work Pattern

Last updated: 2026-03-24

Framework implementations

Status: Extracted
Purpose: Alembic migration patterns, production vs development migrations, and migration workflow for Python / SQLAlchemy services.

Before this doc: Read Migration principles (all stacks) — source of truth, generate-then-review, naming philosophy, indexes/constraints checklist, and squashing rules apply to every stack. This file is the Alembic implementation of those principles.

TypeScript / Drizzle: See drizzle-kit-migration-patterns.md.

Migration Tool: Alembic

Tool: Alembic (SQLAlchemy migration tool)
Purpose: Version-controlled database schema changes
Location: infrastructure/database/migrations/

Migration Structure

migrations/
├── alembic.ini              # Production migration config (committed)
├── alembic.local.ini        # Local dev migration config (git-ignored)
├── env.py                   # Shared Alembic environment script
├── reset_db_and_migrations.py  # Database reset script for local dev
├── versions/                # Production migrations (committed to git)
│   └── .gitkeep
└── devMigrations/          # Local dev migrations (git-ignored)
    └── __init__.py

Production Migrations

Location: versions/ directory
Config: alembic.ini
Purpose: Migrations that are committed to git and used in production

Usage

# From project root
alembic -c infrastructure/database/migrations/alembic.ini upgrade head
alembic -c infrastructure/database/migrations/alembic.ini revision --autogenerate -m "description"
alembic -c infrastructure/database/migrations/alembic.ini downgrade -1

Characteristics

  • ✅ Committed to version control
  • ✅ Used in production environments
  • ✅ Reviewed in pull requests
  • ✅ Applied in staging and production
  • ✅ Never deleted (historical record)

Local Development Migrations

Location: devMigrations/ directory
Config: alembic.local.ini
Purpose: Local development migrations that can be safely deleted and regenerated

Usage

# From project root
alembic -c infrastructure/database/migrations/alembic.local.ini upgrade head
alembic -c infrastructure/database/migrations/alembic.local.ini revision --autogenerate -m "description"

Reset Database and Migrations

Use the reset script for a clean slate:

# From project root - Full reset (wipe DB + recreate migrations)
python infrastructure/database/migrations/reset_db_and_migrations.py --with-db-wipe
 
# Only wipe database
python infrastructure/database/migrations/reset_db_and_migrations.py --only-db-wipe

Characteristics

  • ❌ Never commit files in devMigrations/ directory
  • 🔄 Safe to delete devMigrations/ - can be regenerated from models
  • 🚫 Don't use devMigrations/ for production migrations
  • 📁 Git-ignored - devMigrations/ and alembic.local.ini won't appear in version control

Migration Workflow

Development Process

  1. Local Testing: Use alembic.local.ini and devMigrations/ for fresh migrations
  2. Production Testing: Use alembic.ini and versions/ for team migrations
  3. Production Changes: Create migrations in versions/ for commits

Migration Creation Process

  1. Modify Models: Update SQLAlchemy models in infrastructure/database/adapters/sqlalchemy/models/
  2. Generate Migration: Run alembic revision --autogenerate -m "description"
  3. Review Migration: Check generated migration file for correctness
  4. Test Locally: Apply migration locally and test
  5. Create PR: Commit migration to versions/ directory
  6. Review: Migration reviewed in pull request
  7. Apply Staging: Apply migration in staging environment
  8. Apply Production: Apply migration in production (with approval)

Migration Best Practices

Naming Conventions

  • Use descriptive migration names
  • Include purpose in migration message
  • Example: add_user_authentication_table, add_index_to_messages_created_at

Backward Compatibility

  • Consider backward compatibility when modifying schemas
  • Use multi-step migrations for breaking changes
  • Test rollback procedures

Data Migrations vs Schema Migrations

  • Schema Migrations: Structure changes (tables, columns, indexes)
  • Data Migrations: Data transformations (migrating data, cleaning data)
  • Combine when needed, but keep separate when possible

Large Data Migrations

  • Break large migrations into smaller steps
  • Use background jobs for large data transformations
  • Monitor migration performance

Rollback Safety

  • Always test rollback procedures
  • Ensure downgrade functions are correct
  • Document any irreversible migrations

Architecture Integration

This migration setup follows hexagonal architecture principles:

  • Infrastructure Concern: All migration files live in infrastructure/database/migrations/
  • Separation: Production and dev migrations are completely separate
  • Model Location: Models are in infrastructure/database/adapters/sqlalchemy/models/
  • Settings Integration: Uses config.settings.get_settings() for database URL

Related documents

  • Migration principles (all stacks)
  • Drizzle Kit migration patterns
  • Session Management — Database session patterns
  • Unit of Work Pattern — Transaction management
  • Repository implementation — Repository patterns

Reference implementation: infrastructure/database/migrations/ in any FastAPI service.

Middleware StackSession Management