Skip to content

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.

Warning

Import every model module in alembic/env.py so Base.metadata includes all tables. Missing imports produce empty migrations.

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')

Note

When adding NOT NULL columns to existing tables, always include server_default so existing rows are populated automatically.

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

Warning

Downgrade in production requires caution. Dropping columns/tables is irreversible once committed. Always back up first.

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"