Database migrations (Alembic)

Single source of truth for the schema is capital_shared.models.Base. Both services share one Postgres. Migrations are owned by the back-office image (it ships alembic.ini + migrations/); the cabinet just uses the schema.

• Dev / tests - AUTO_CREATE_TABLES defaults true and is_prod is false, so Base.metadata.create_all builds an ephemeral SQLite. Tests do not use Alembic.
• Stage / prod - AUTO_CREATE_TABLES=false (set in compose); Alembic is the only thing that touches schema.

Common commands (run inside the back-office container)

# current revision on the live DB
docker compose exec -T backoffice alembic current

# apply all pending migrations (safe to re-run; no-op when up to date)
docker compose exec -T backoffice alembic upgrade head

# baseline an existing DB that already has the tables (one-time)
docker compose exec -T backoffice alembic stamp head

Changing the schema

1. Edit the models in shared/capital_shared/models.py.
2. Autogenerate a migration (locally, against an empty DB so the diff is the change): bash ALEMBIC_URL="sqlite+aiosqlite:///./_gen.db" backoffice/.venv/bin/alembic upgrade head ALEMBIC_URL="sqlite+aiosqlite:///./_gen.db" backoffice/.venv/bin/alembic revision --autogenerate -m "add X" rm -f _gen.db
3. Review the generated file in migrations/versions/ (autogen misses some things - server defaults, data backfills; add them by hand).
4. Deploy, then docker compose exec -T backoffice alembic upgrade head.

The URL comes from ALEMBIC_URL or DATABASE_URL (async driver). This replaces the old "manual ALTER TABLE ... ADD COLUMN" workaround - column changes now go through a reviewed migration.