Database Migrations

The Sales Agent uses Alembic for database schema migrations with SQLAlchemy ORM models. Migrations run automatically on startup in Docker deployments.

Overview

All database schema changes are managed through Alembic migration scripts. Each migration captures a discrete schema change with both upgrade() and downgrade() functions, allowing the schema to move forward or roll back to any point in its history.

The migration system supports:

  • Automatic migration generation from SQLAlchemy model changes
  • Sequential numeric revision IDs for clear ordering
  • Automatic execution on container startup in Docker deployments
  • Manual execution for local development and production deployments

Configuration

Alembic is configured through two files in the project root:

File Purpose
alembic.ini Alembic configuration including database URL and script location
alembic/env.py Migration environment setup, model imports, and connection handling

Migration scripts are stored in alembic/versions/ and are committed to version control.

Migration File Structure

Each migration file follows a standard pattern:

"""Description of migration

Revision ID: 001
Revises: initial_schema
Create Date: YYYY-MM-DD HH:MM:SS
"""
revision: str = "001"
down_revision: str | None = "initial_schema"

def upgrade() -> None:
    # Apply changes
    pass

def downgrade() -> None:
    # Revert changes
    pass

Key fields:

  • revision – A numeric string identifier for this migration (e.g., "001", "002").
  • down_revision – The revision this migration builds on. Set to None for the initial migration.
  • upgrade() – Contains the SQL or ORM operations to apply the schema change.
  • downgrade() – Contains the operations to revert the schema change.

Running Migrations

Environment Command
Docker Compose Automatic on startup
Docker manual docker-compose exec adcp-server python scripts/ops/migrate.py
Fly.io fly ssh console -C "cd /app && python scripts/ops/migrate.py"
Local alembic upgrade head
Rollback one step alembic downgrade -1
Rollback to specific alembic downgrade <revision>
Check current revision alembic current
View migration history alembic history

Applying Migrations Locally

# Apply all pending migrations
uv run alembic upgrade head

# Apply migrations up to a specific revision
uv run alembic upgrade 005

# Check which revision the database is on
uv run alembic current

Rolling Back

# Roll back the most recent migration
uv run alembic downgrade -1

# Roll back to a specific revision
uv run alembic downgrade 003

# Roll back all migrations (reset to empty schema)
uv run alembic downgrade base

Rolling back migrations in production requires caution. Data migrations that transform or delete data may not be fully reversible. Always back up the database before rolling back in a production environment.

Creating New Migrations

Autogenerate from Model Changes

After modifying SQLAlchemy models, generate a migration automatically:

uv run alembic revision --autogenerate -m "add_new_column"

This compares the current database schema against the SQLAlchemy model definitions and generates the appropriate upgrade() and downgrade() operations.

Always review autogenerated migrations before applying them. Alembic autogenerate does not detect all change types – for example, it cannot detect changes to column names, changes to constraints on existing columns, or data migrations.

Manual Migration

For changes that autogenerate cannot handle, create an empty migration and write the operations manually:

uv run alembic revision -m "migrate_legacy_data"

Then edit the generated file in alembic/versions/ to add your operations.

Naming Conventions

  • Revision IDs – Sequential numeric strings: 001, 002, 003, and so on.
  • Messages – Use snake_case descriptions that summarize the change: add_policy_compliance_fields, create_gam_inventory_tables.

Existing Migrations

The project includes 18+ migrations covering the full schema evolution. Key categories include:

Category Description
Schema foundation Initial tables, format definitions, and core relationships
Policy compliance Fields for regulatory and business policy enforcement
Super admin config Configuration tables for platform-level administration
GAM inventory Google Ad Manager inventory tables and sync tracking
Principal/advertiser mapping Relationship tables between principals and advertiser accounts
Context persistence Workflow state and conversation context storage
JSON validation Partial schema handling and JSON column validation

Best Practices

Always Include Both Directions

Every migration must implement both upgrade() and downgrade(). If a change is genuinely irreversible, document that in the downgrade() function and raise an exception:

def downgrade() -> None:
    raise RuntimeError(
        "This migration cannot be reversed. "
        "Data was transformed in a lossy operation."
    )

Test Against Fresh and Existing Databases

Run migrations against both a clean database (from base to head) and a database at the previous revision:

# Fresh database
uv run alembic downgrade base
uv run alembic upgrade head

# Single step from current
uv run alembic upgrade +1

Make Data Migrations Idempotent

Data migrations (as opposed to schema migrations) should be safe to run multiple times without side effects. Use conditional logic or upserts instead of raw inserts:

def upgrade() -> None:
    conn = op.get_bind()
    # Only insert if the row does not already exist
    existing = conn.execute(
        sa.text("SELECT 1 FROM config WHERE key = 'default_currency'")
    ).fetchone()
    if not existing:
        conn.execute(
            sa.text("INSERT INTO config (key, value) VALUES ('default_currency', 'USD')")
        )

Review Autogenerated SQL

Autogenerate is a starting point, not a final product. Review the generated operations for:

  • Correct column types and nullability
  • Proper index and constraint definitions
  • Foreign key cascading behavior
  • Default values and server defaults

Troubleshooting

Migration Head Conflicts

If two developers create migrations with the same down_revision, Alembic reports a “multiple heads” error:

# Check for multiple heads
uv run alembic heads

# Merge heads into a single migration
uv run alembic merge -m "merge_heads"

Database Out of Sync

If the database schema does not match the migration history (common after manual schema changes), stamp the database to mark it at a known revision without running migrations:

uv run alembic stamp <revision>

Stamping does not modify the database schema. It only updates the Alembic version table. Use this only when you are certain the schema matches the specified revision.

Further Reading