Skip to main content

teudb Migrations

TradeEntry now has two Alembic environments, one per database. Getting them confused is the single most dangerous mistake available in this codebase.

The two environments​

DatabaseConfigScript locationOwns
tedbalembic.inibackend/alembic/st_*, te_*, scr_global_*
teudbalembic_user.inibackend/alembic_user/all usr_* tables
cd backend

# tedb - unchanged, exactly as before
alembic upgrade head
alembic revision --autogenerate -m "..."

# teudb - note the -c flag on EVERY command
alembic -c alembic_user.ini upgrade head
alembic -c alembic_user.ini revision --autogenerate -m "..."
alembic -c alembic_user.ini current
alembic -c alembic_user.ini history
Never point one environment at the other's database

The two databases have separate, incompatible alembic_version tables. Running alembic upgrade head (no -c) against teudb, or the reverse, will attempt to apply the wrong migration chain.

Because the version tables differ, this usually fails loudly rather than corrupting silently - but do not rely on that. Check alembic -c alembic_user.ini current before any destructive operation.

Why a second directory rather than --name sections​

Alembic supports both a second .ini file and multiple [section] blocks in one alembic.ini selected with --name. We chose the second directory. Two reasons:

1. alembic/env.py must not be touched. That file contains the _OWNED_TABLES set and the include_object() filter at lines 55-63. Its entire job is stopping --autogenerate from proposing DROP TABLE scr_master against the production database. It is the highest-blast-radius file in the repository.

A --name approach would require branching inside that shared env.py on config.config_ini_section to pick between target_metadata=[StrategyBase.metadata, AABase.metadata] plus the filter, versus target_metadata=UserBase.metadata with no filter. Adding a mode switch to that file is not worth saving one config file.

2. teudb is a genuinely different kind of environment, not a parameterisation. Alembic owns 100% of teudb, so its env.py needs no include_object filter at all. There is nothing to protect, because there is nothing in that database that Alembic did not create.

Separate alembic_version tables come free, since they are different databases.

alembic_user/env.py​

import os, sys
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app.core.config import settings
from app.database.user_session import UserBase
import app.models.user_models  # noqa: F401 - registers every usr_* table on UserBase

config = context.config
config.set_main_option("sqlalchemy.url", settings.USER_SYNC_DATABASE_URL)
target_metadata = UserBase.metadata
# No include_object filter: Alembic owns 100% of teudb.
The noqa: F401 import is load-bearing

import app.models.user_models looks unused and linters will offer to remove it. Without it, UserBase.metadata is empty and --autogenerate will cheerfully propose dropping every table in teudb.

Creating the database​

teudb must exist before the first migration. backend/scripts/create_teudb.sh is idempotent:

CREATE DATABASE teudb OWNER postgres ENCODING 'UTF8';
\c teudb
CREATE EXTENSION IF NOT EXISTS citext;

The script can reuse nseeod/pgs.py's database_conn(dbname, auto=True) - it already accepts an arbitrary database name, so database_conn('postgres') gives the maintenance connection needed to issue CREATE DATABASE.

Check CITEXT availability first
SELECT * FROM pg_available_extensions WHERE name = 'citext';

If unavailable, see the fallback documented in Overview.

Traps carried over from the tedb chain​

These are documented in backend/AA/CLAUDE.md rule 3 and apply identically here.

Deleting a model does not produce a DROP​

On the tedb side, include_object skips any reflected table absent from the metadata - which also silently shields your own just-retired table. teudb has no such filter, so autogenerate will propose drops correctly.

That is safer, but it cuts both ways: a model accidentally deleted or renamed produces a real DROP TABLE in the generated migration. Always read the generated file before applying it.

Autogenerate has no RENAME concept​

Run blind against a renamed table, it emits drop_table + create_table, silently discarding every row. Table and column renames must be hand-written using op.alter_table(... ) / op.alter_column(..., new_column_name=...).

Postgres does not rename a table's constraints, indexes, or sequences when the table is renamed - rename them explicitly too, or the next autogenerate will propose "fixing" them.

Always name foreign keys explicitly​

# WRONG - autogenerate's default; downgrade() cannot resolve None to a constraint name
op.create_foreign_key(None, 'usr_subscription', 'usr_master', ['usrid'], ['usrid'])

# CORRECT
op.create_foreign_key('fk_usr_subscription_usrid', 'usr_subscription',
                      'usr_master', ['usrid'], ['usrid'])

This project sets no naming_convention on the metadata, so autogenerate cannot resolve it for you.

CHECK constraint bodies are not diffed​

Autogenerate does not detect a changed CHECK expression. Widening ck_usr_setting_theme to admit a fifth theme, or ck_usr_master_status to admit a new status, must be hand-written - exactly as migration ece2eaf3518e did for ck_te_asset_asset_class on the tedb side.

Verifying a migration​

cd backend

# 1. Apply
alembic -c alembic_user.ini upgrade head

# 2. Prove models match the database - this must produce an EMPTY migration
alembic -c alembic_user.ini revision --autogenerate -m probe
#    ... inspect it, confirm it is empty, then DELETE it

# 3. Prove the tedb chain is untouched
alembic current          # still 604991c1d258
git diff --stat alembic/env.py    # must be empty
git diff --stat alembic/env.py must be empty

Any patch that modifies the tedb Alembic environment while adding teudb support has done something wrong. This check belongs in the acceptance criteria of Patch 6.

Testing against a throwaway database​

Tests must not run against the real teudb. The SAVEPOINT-rollback fixture in backend/tests/conftest.py protects data, but a botched migration during development does not.

Add TEST_UDB_NAME=teudb_test support to the test fixture and point the session factory at it. The existing conftest.py pattern - a session-scoped sync engine plus a join_transaction_mode="create_savepoint" session - should be reused, not reinvented, with a parallel user_db_session fixture.

Deployment​

pull.sh runs both chains as its own step ("3 · Database Migrations"), between backend dependencies and the frontend build:

alembic upgrade head                        # tedb
alembic -c alembic_user.ini upgrade head    # teudb

Triggered only when backend/alembic/versions/* or backend/alembic_user/versions/* changed in the pull — a deploy with no new migration files skips this step entirely.

Abort before restart if a migration fails

A restarted application running against an un-migrated teudb fails every single login. pull.sh's migration step exit 1s immediately on either chain's failure, before reaching the service restart - set -e alone does not guarantee this, since both alembic calls are wrapped in if for a clean summary line, which exempts them from set -e's automatic abort.

First deploy of teudb is still manual

pull.sh migrates an existing teudb - it does not create the database or set JWT_SECRET. The very first deploy that introduces teudb needs CREATE DATABASE teudb and a JWT_SECRET value in backend/.env done by hand first (see teudb Overview), or the app won't even start to be migrated. Every deploy after that is unattended.