Alembic Migrations¶

Vectis uses Alembic for schema migrations against a single PostgreSQL schema. All modules share one migration history in vectis/backend/alembic/versions/.

Generating a Migration¶

After changing SQLAlchemy models, auto-generate a revision:

cd vectis/backend
alembic revision --autogenerate -m "add_loyalty_points_table"

Always review the generated file — autogenerate misses data migrations, custom constraints, and partial indexes.

Running Migrations¶

alembic upgrade head       # apply all pending
alembic upgrade +1         # one revision at a time
alembic current            # check current revision
alembic history --verbose  # show history

Conventions¶

1. Descriptive names — add_loyalty_points_table, not update_schema
2. Always include downgrade — every upgrade() needs a matching downgrade()
3. Test both directions — upgrade head, downgrade -1, upgrade head
4. Server defaults for new NOT NULL columns — existing rows need a value
5. BigInteger PKs — all new tables must use sa.BigInteger() primary keys

Example: Multi-Currency Migration¶

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

def upgrade() -> None:
    op.add_column('channels', sa.Column(
        'supported_currencies', postgresql.JSONB(), nullable=True, server_default='["USD"]',
    ))
    op.create_table('exchange_rates',
        sa.Column('id', sa.BigInteger(), primary_key=True),
        sa.Column('base_currency', sa.String(3), nullable=False, index=True),
        sa.Column('target_currency', sa.String(3), nullable=False, index=True),
        sa.Column('rate', sa.Numeric(18, 8), nullable=False),
        sa.Column('effective_from', sa.DateTime(timezone=True), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()')),
        sa.UniqueConstraint('base_currency', 'target_currency', 'effective_from',
                            name='uq_er_pair_effective'),
    )
    op.add_column('orders', sa.Column(
        'currency', sa.String(3), nullable=False, server_default='USD',
    ))

def downgrade() -> None:
    op.drop_column('orders', 'currency')
    op.drop_table('exchange_rates')
    op.drop_column('channels', 'supported_currencies')

Seeding After Migrations¶

vectis/core/seed.py creates the default channel, roles, permissions, and an admin user. Run after applying migrations to a fresh database:

python -m vectis.core.seed

The seed is idempotent — safe to run on a populated database.

Rolling Back¶

alembic downgrade -1           # revert last migration
alembic downgrade a3e2f1b8c9d4 # revert to specific revision
alembic downgrade base         # revert everything

Schema-Drift Guard¶

make check runs a schema-drift guard that diffs the SQLAlchemy metadata graph against alembic upgrade head. The check fails if the live schema and the model definitions disagree — either because a model was edited without a migration or because a migration drifted from the model.

The guard is part of every local check; CI is currently disabled in favor of running make check inside the API container before pushing.

Troubleshooting¶

• "Target database is not up to date" — run alembic upgrade head first
• Empty migration — model module not imported in alembic/env.py
• Merge conflicts — alembic merge heads -m "merge_branches"
• Schema-drift failure in make check — either generate the missing migration with alembic revision --autogenerate -m "..." or fix the model so it matches the live schema. Don't bypass the gate.

Notable Recent Migrations¶