Skip to main content

Alembic Explained

Alembic is new to this repo as of the Strategy Tracker tables (strategy_classification, strategy, regime_master). Every table before that — scr_master, scr_nseeq_eod, the whole NSE/MCX/IEOD schema — was created by hand (CREATE TABLE run once, ORM models in app/models/models.py written afterward to match). This doc is the plain-language version of what Alembic does and, more importantly, the one sharp edge it has in a codebase like this one.

The one idea that matters​

Alembic is a migration tool for SQLAlchemy. You define your tables as Python classes (models), and instead of writing CREATE TABLE / ALTER TABLE SQL by hand, Alembic can:

  1. Autogenerate a migration file by diffing your Python models against the live database schema.
  2. Apply (upgrade) or undo (downgrade) that migration, tracked by a revision id stored in an alembic_version table it creates in the database.

Each migration file has an upgrade() and a downgrade() function, chained to the previous revision — so the full history of schema changes lives in backend/alembic/versions/ as ordinary, reviewable Python files, not as a memory of "what SQL did I run on prod that one time."

Why introduce it now, and not earlier​

Every table before Strategy Tracker was already living in tedb before any ORM model was written for it — there was nothing to "migrate," so there was no need for a migration tool. Strategy Tracker is the first case where the tables didn't exist yet and needed to be created from a schema definition — that's exactly the job Alembic is for.

The sharp edge: --autogenerate diffs the whole connected schema​

This is the one thing worth understanding before ever running alembic revision --autogenerate again in this repo.

Alembic's autogenerate does not just look at the models you're working on — by default it reflects every table currently in the connected database and diffs all of them against whatever target_metadata you gave it. Anything in the database that isn't in target_metadata looks, to Alembic, like a table that used to exist and should now be dropped.

This actually happened once while building the Strategy Tracker migration. target_metadata was pointed at a SQLAlchemy Base that (transitively) only described 3 new tables, but the live tedb database also contains every pre-existing hand-mapped table. The first --autogenerate run queued DROP TABLE for scr_master, scr_nseeq_eod, holidays, tasks, and everything else in the database it didn't recognize — all because they weren't part of the metadata Alembic was told to compare against. It was caught by reading the generated migration file before applying it — never actually run.

The fix lives in backend/alembic/env.py:

target_metadata = StrategyBase.metadata   # ONLY the 3 strategy-tracker tables

def include_object(object, name, type_, reflected, compare_to):
    if type_ == "table" and reflected and name not in _OWNED_TABLES:
        return False   # this table isn't ours — never propose dropping it
    if type_ == "index" and reflected and object.table.name not in _OWNED_TABLES:
        return False
    return True

include_object is Alembic's hook for exactly this situation: it gets a say over every reflected object before it's considered for the diff. Skipping anything not in _OWNED_TABLES means autogenerate can only ever propose changes to the tables this migration set actually owns.

Read every autogenerated migration before applying it

--autogenerate is a starting point, not a finished migration — the tool itself says so ("please adjust!") in the generated file. In a database this old, with this much hand-authored schema outside the ORM's knowledge, an unreviewed alembic upgrade head is one of the few genuinely destructive commands available in this repo. Always open the generated file in alembic/versions/ and read the upgrade()/downgrade() bodies before running it against tedb.

Why a separate StrategyBase​

Following from the above: strategies/models.py deliberately does not import the shared Base from app/database/session.py (the one app/models/models.py uses for ScrMaster etc.). It defines its own StrategyBase(DeclarativeBase). That's what makes target_metadata = StrategyBase.metadata safe by construction — it's structurally impossible for autogenerate to see the pre-existing tables at all, include_object or not. Any future package that gets its own Alembic-managed tables should follow the same pattern: a metadata scoped to just what that migration set owns, not the app's shared Base.

Sync vs. async — which URL Alembic uses​

The app's normal database session (app/database/session.py) is async (asyncpg), because FastAPI request handlers are async. Alembic migrations are not async — alembic/env.py uses settings.SYNC_DATABASE_URL (a plain postgresql:// URL via psycopg2), a property that already existed on Settings in app/core/config.py before Alembic was introduced. This is the normal Alembic convention, not something specific to this repo: migrations run once, synchronously, outside the request/response cycle, so there's no reason for them to go through the async driver.

Command reference​

All commands run from backend/, venv activated:

alembic upgrade head       # apply every migration not yet applied
alembic downgrade base     # undo every migration (drops the tables)
alembic current            # show which revision the DB is currently at
alembic history            # list all revisions, oldest to newest
alembic revision --autogenerate -m "description"   # generate a new migration — READ IT before applying

See Strategy Tracker → Local CLI for the exact sequence used to stand up and test these specific tables.