Database Migrations

This document details our migration workflow, the helper scripts we use to reduce mistakes, and the migration ownership policy.

Migration Tooling

Alembic

Database migration tool used for:

• Version control for schemas
• Up/down migrations
• Auto-generation
• Multi-environment support

Ownership Policy

IMPORTANT: Database migrations and revision files are handled exclusively by the lead developer.

• AI Agents: Do NOT attempt to create, modify, or run migration scripts unless explicitly instructed.
• Developers: Coordinate with the lead developer before applying schema changes outside local-only workflows.

Our Approach (By Environment)

Local Development (default: schema sync)

For local development we prefer a fast schema sync workflow that keeps your local database aligned to the current SQLAlchemy models without managing Alembic revision history.

• Use when: you need quick iterations locally and you can recreate your local DB if needed.
• Avoid when: you need to validate the exact Alembic history you’ll run in shared/dev/prod environments.
• Key limitation: schema sync tools typically create missing tables/columns but do not safely perform destructive operations (drops/renames) automatically.

Example script: Local schema sync

Shared/Deployed Environments (Alembic)

For shared environments (development/staging/production), we use Alembic to ensure schema changes are:

• Auditable (revision history)
• Repeatable (same scripts applied in order)
• Reviewable (migration diffs inspected before running)

Example scripts:

• Development migration runner
• Production migration runner

Helper Scripts (What Each One Does)

These scripts are wrappers around Alembic (or SQLAlchemy) to ensure:

• the correct environment is set
• the correct database URL is selected
• the commands are logged consistently
• dangerous operations require explicit confirmation

migrations/sync_local_schema.py

• Purpose: sync local DB schema to models (create missing tables/columns).
• Common usage:

PYTHONPATH=. python migrations/sync_local_schema.py --check
PYTHONPATH=. python migrations/sync_local_schema.py
PYTHONPATH=. python migrations/sync_local_schema.py --backup

migrations/run_dev_migrations.py

• Purpose: run Alembic commands against the development database without manually exporting environment variables.
• Common usage:

PYTHONPATH=. python migrations/run_dev_migrations.py current
PYTHONPATH=. python migrations/run_dev_migrations.py history
PYTHONPATH=. python migrations/run_dev_migrations.py upgrade head
PYTHONPATH=. python migrations/run_dev_migrations.py revision --autogenerate -m "Add new table"

migrations/run_prod_migrations.py

• Purpose: run Alembic commands against production with additional guardrails.
• Common usage:

PYTHONPATH=. python migrations/run_prod_migrations.py current
PYTHONPATH=. python migrations/run_prod_migrations.py upgrade head

migrations/run_*_downgrade.py

• Purpose: downgrade a database revision with explicit confirmation prompts.
• Use with extreme caution (downgrades can drop/lose data).

Example scripts:

• Development downgrade runner
• Production downgrade runner

Alembic env.py (How to Structure It)

Alembic’s alembic/env.py is responsible for two things that commonly go wrong:

• Choosing the correct database URL for the target environment
• Ensuring all models are imported so target_metadata = Base.metadata contains every table/index/enum (required for --autogenerate)

Recommended approach:

• Keep environment selection simple (e.g., ENVIRONMENT=development|staging|production)
• Map environment → DATABASE_URL_* variable names consistently
• Import Base plus a single helper such as database/imports.py: import_all_models() that imports all model modules
• Use a synchronous SQLAlchemy engine in Alembic (do not use async drivers here)

Complete example: Alembic env.py structure

Workflow

1. Create Models

Define your SQLAlchemy models in database/{entity}/models.py.

2. Generate Migration (shared dev/staging)

Create a new revision based on model changes (lead developer owned).

PYTHONPATH=. python migrations/run_dev_migrations.py revision --autogenerate -m "Add new table"

3. Review Migration Script

Always inspect the generated file in alembic/versions/ to ensure it captures the intended changes correctly.

4. Apply Migration

Apply the changes to the database.

PYTHONPATH=. python migrations/run_dev_migrations.py upgrade head

Common Commands

# Show current revision
alembic current

# Show history
alembic history

# Downgrade to previous revision
alembic downgrade -1

# Downgrade to base (empty database)
alembic downgrade base