migrations.md

Migrations

All migrations live in host/migrations/versions/ — not in module packages. Alembic runs from the repo root via host/alembic.ini and shares the app's .env / SM_DATABASE_URL.

Autogenerate's make_include_object() filter preserves foreign keys declared only in migrations by ignoring unmatched reflected constraints. Newly modeled foreign keys on existing tables still generate CreateForeignKeyOp; set ignore_unmodeled_fks=False to allow dropping database-only constraints (#812). A foreign key with the same columns and target but different options (ondelete, deferrable) is emitted as a drop plus a create; one with other columns or another target is a different constraint and stays preserved. SQLite cannot ALTER constraints: wrap such ops in op.batch_alter_table(...) by hand before applying the revision there.

For sa.Enum columns backed by Python StrEnum, generated revisions freeze the column's actual configured labels and preserve name, native_enum, create_constraint, length and configured values_callable behavior without importing application enums/callables. Plain enum fields store member names (e.g. READY); to store values (e.g. ready), configure the runtime type with values_callable=lambda e: [m.value for m in e]. Custom callable labels are frozen in the revision. Existing revisions are not rewritten (#813).

Why centralized?

• Dependency ordering is global. If invoices depends on orders_order.id, their migrations must order correctly. One shared Alembic history, with down_revision links between modules, enforces this.
• Autogenerate sees everything. host/migrations/env.py calls build_module_metadata() to union every installed module's MetaData. Autogenerate diffs the DB against that union and writes one migration covering all changes.
• Operators run one command. make migrate is the only target. No "did you also run orders/migrate?" footgun.

Module data upgrades in consuming hosts (#711)

Scaffolded hosts own their migration graphs. A schema-only baseline generated from current models does not include this repository's historical data changes. Before deploying upgraded packages, run make doctor or uv run smpy doctor. Semantic module checks (SM033) recognize equivalent/manual ports independently of upstream revision ids.

uv run smpy migrations prepare <upgrade-id> --head <host-head-or-label@head> copies a frozen, self-contained recipe into the host's own migrations/versions; it never applies it. An explicit current head is required. The bundled ids are mail.settings_namespace_v1 (system settings move, target wins, matching d7b3a9e5c1f2) and users.hashed_bearer_tokens_v1 (invalidate credentials before altering both token columns, matching a626b1c8d023). The first recipe for an unlabelled module declares mail/users; existing labels are not redeclared. Review the generated revision before applying it with upgrade heads.

Boot checks run before module settings hydration/startup; token incompatibility refuses boot outside development, while obsolete mail keys warn. Never-migrated first runs defer checks until the wizard applies migrations. No automatic credential deletion happens at startup. See the scaffolded host upgrade guide for the operator workflow and token sign-out risk.

Doctor uses the DB session factory and listeners used at boot: soft-deleted rows are hidden and tenant queries are scoped. Because doctor has no host settings, it always enforces strict tenant isolation, which is stricter than a single-tenant boot: a check that passes doctor also passes boot. A check must use tenant_context(id) for one tenant or explicitly use all_tenants() / execution_options(all_tenants=True) for an installation-wide check. Add execution_options(include_deleted=True) only when the check also needs trashed rows. These opt-ins work at boot too (#815).

The real graph

The history is not a single linear chain. It has several roots and several heads, and make migrate runs upgrade heads for that reason. On main today:

Root	Branch
77162e7b184b initial schema	the users trunk (also holds file_storage, background_tasks, api_tokens revisions)
a738c2e4f601 auth refresh outcomes (#738)	auth (root and head)
c9d2e4f6a810 OIDC identity cache (#192)	oidc (root and head)
e36ab09c4a92 tenants initial schema	tenants (f1a9c3d7b2e4 head)
f733c7938fd1 sharing resource share	sharing (root and head)
d263ea727663 framework sm_lease table	leases (its own head; leases)

Head	Owning module	Label Alembic reports
f6c2d4e8b109	file_storage (tenant browse index)	api_tokens, background_tasks, file_storage, users
b8d4f2a6c901	audit_log	audit_log
f1a9c3d7b2e4	tenants	tenants
d263ea727663	framework (sm_lease)	leases
f6a3b9d2e8c4	keycloak	keycloak
f733c7938fd1	sharing	sharing
c9d2e4f6a810	oidc	oidc
a738c2e4f601	auth	auth

audit_log forks off the trunk at 41cf2c53660e, and keycloak forks off the users revision 873ca2015033.

A module's first migration sets branch_labels = ("<module_name>",). Alembic inherits a label onto every descendant, so the label names a revision and everything above it, and it does not isolate the module. It is only a named target (upgrade <label>@head, revision --head <label>@head) and the source of the Doctor's module column, which reads the first label on each revision.

Known mislabels

The declared branch_labels are auth (added by a738c2e4f601), audit_log, api_tokens, background_tasks, keycloak, tenants, sharing, leases, users (added by a625f0b7c912), oidc (added by c9d2e4f6a810) and file_storage (added by f5b1c3e8a207). The graph carries these defects; they are documented rather than rewritten, because changing labels on revisions that installed databases already applied is not worth the risk:

• 51b2058d8afd_api_tokens_initial sits in the middle of the trunk, so every revision above it (file_storage, background_tasks including the outbox, and the users revisions through c8e4f1a2b6d9) reports the api_tokens label. The api_tokens head is therefore really a users revision.
• keycloak branches off a users revision, so a Keycloak-only install still builds the users schema.
• settings, permissions and feature_flags have no label of their own. users and file_storage gained labels late (a625f0b7c912, f5b1c3e8a207), so their earlier revisions stay unlabelled and report api_tokens.

Guidance: give an unlabelled module a branch_labels on its next revision. Adding a label to an existing revision file is technically possible, because alembic_version stores revision ids and never labels, but it also relabels every descendant and changes what <label>@head resolves to, so treat it as a deliberate, reviewed change rather than a cleanup.

host/tests/test_migration_graph.py pins the roots, heads, each revision's owning module and the mislabels above. Adding a root, head or revision fails it until the table is updated.

Browser refresh coordination (#738)

Migration a738c2e4f601 creates auth_refresh_outcome on an independent auth root. Apply upgrade heads before using OIDC or Keycloak renewal. The 30-second outcomes contain only the principal, expiry, sealed refresh token and session coordination keys; expired rows are purged when a later renewal publishes its outcome (lookups never write). outcome_key (primary key) is a hash of the session, its sealed refresh token being spent and the provider; lock_key (indexed) is the per-session hash, which logout uses to delete the session's outcomes. Independent transactions publish outcomes before releasing the session lease, without waiting for the request transaction. No Redis is required. Downgrade drops this short-lived cache; it does not alter user accounts.

Users token storage upgrade (#626)

Migration a626b1c8d023 follows a625f0b7c912 on users@head and changes refresh and access token keys to SHA-256 digests. Behaviour change: upgrade deletes existing refresh/access token rows, requiring one re-login for clients using them (including sm_auth cookies). Other session cookies are unaffected. Downgrade also deletes token rows: hashes cannot be restored to bearer secrets. Cookie login, token login and refresh prune up to 100 spent rows per table for that user. Refresh reuse evidence is retained while the family has a live token, including when an ancestor has expired; inactive users' rows wait until their next login.

Day-to-day workflow

Create a migration

make migration msg="add orders tables"

This runs alembic -c host/alembic.ini revision --autogenerate from the repo root. The resulting file lands in host/migrations/versions/XXXX_add_orders_tables.py.

A post_write_hooks entry in host/alembic.ini (this repo's and every scaffolded app's) runs the project's ruff on each new revision: ruff check --select I --fix, then ruff format. A fresh revision therefore passes make lint. Only the import rules are fixed, so an empty handwritten revision keeps its sa and op imports. Ruff must be installed in the venv Alembic runs from: every scaffold puts it in the default dev dependency group (the workspace root's, or the host's for smpy create-host). Without it Alembic writes the file and then fails with Could not find module ruff; run uv add --dev ruff. An app scaffolded before 0.0.45 can copy the [post_write_hooks] block from a new smpy new app's host/alembic.ini, provided its Alembic is 1.16.3 or newer, the first release with the module hook runner (#896).

Always open and read the generated file before committing. Autogenerate is good but not perfect:

• It detects column additions, drops, type changes, index/constraint changes, new tables.
• It misses semantic constraints (e.g. "this ENUM now has one more value"), and it sometimes produces drops that should be renames.
• Constraint naming needs to be stable — provide explicit name="..." kwargs when creating indexes/constraints in your models.

Apply migrations

make migrate

Runs alembic -c host/alembic.ini upgrade heads. Idempotent.

Downgrade

make downgrade                                                 # back one revision
uv run --project host alembic -c host/alembic.ini downgrade <revision_id>   # to a specific revision

Do not use downgrade orders@base to roll back just the orders module. The label names a revision, and orders@base unwinds that revision, all of its descendants, and everything beneath it down to the root it sits on, including other modules'. With this graph, downgrade api_tokens@base would drop the whole users trunk (users, file_storage, the background_tasks outbox and the initial schema), not only the api_tokens tables.

Removing one module's schema

• If the module's revisions are the latest on their branch (nothing from another module sits above them), downgrade to the down_revision of the module's first revision: alembic -c host/alembic.ini downgrade <that down_revision id>.
• Otherwise write a dedicated migration that drops the module's tables (and anything depending on them). Other modules are untouched.

First migration of a new module

When you scaffold a module with smpy create-module, the first autogenerate revision produces a file that needs this marker added by hand:

# host/migrations/versions/XXXX_add_orders_tables.py

revision = "..."
down_revision = "..."
branch_labels = ("orders",)  # ← add this
depends_on = None

The marker names the revision so you can target it (for example alembic upgrade orders@head); later orders revisions do not need it. It does not turn the module into an independent branch, and it is inherited by every revision stacked on top of it, so put it on the module's own first revision and not on a revision that other modules build on.

Alembic environment setup

host/migrations/env.py looks roughly like:

from simple_module_db import (
    build_module_metadata,
    make_include_object,
    make_process_revision_directives,
    render_item,
)

target_metadata = build_module_metadata()
include_object = make_include_object(target_metadata)
process_revision_directives = make_process_revision_directives(target_metadata)

context.configure(
    target_metadata=target_metadata,
    include_object=include_object,
    render_item=render_item,
    process_revision_directives=process_revision_directives,
)

• target_metadata — union of every module's MetaData.
• include_object — accepts only tables present in target_metadata, so autogenerate never diffs system tables (alembic_version) or any host-owned tables outside the module system.
• render_item — collapses SQLModel's AutoString to sa.String and renders StrEnum columns with their configured labels and storage options (no application imports in revisions, #813).
• process_revision_directives — re-emits expression-based (functional) indexes that autogenerate silently drops under SQLite.

Boot-time migration check

On startup, the framework compares alembic_version to every head (resolve_head_revisions in simple_module_hosting.migrations; the history has several, so it never asks for a single head):

• Every environment: a database behind head fails the boot (Database is N revision(s) behind … Run: make migrate), except a never-migrated first run, which boots far enough to serve the /setup wizard. The result is stored on app.state.migration.
• make doctor reports the same state as SM010 before you boot. Don't ship a web process that's pointed at an unmigrated DB.

Fix locally with make migrate. In production, run migrations before rolling over the web tier.

Diagnostic: SM011

Fires as a warning when a database at every head lacks a table a module's models declare, so no migration creates it. make doctor checks the database SM_DATABASE_URL points at; a development boot checks its own database once it is open. Typical causes:

• You added a model but haven't generated a migration for it yet.
• You renamed a table but the old migration still references the old name.
• You used __abstract__ = True somewhere it shouldn't be.

make doctor and the dev-mode boot log print the offending table names; while the database is behind head the check is skipped, since the table may be in a pending revision. Resolution: run make migration msg="...", review, make migrate.

Cross-module foreign keys

If invoices has an FK to orders_order.id:

• Alembic will emit ADD CONSTRAINT in the invoices table's migration.
• The migration that creates invoices_invoice must come after the one that creates orders_order in the history (an ancestor, not a sibling branch).
• smpy create-module + make migration handle this naturally as long as depends_on is correct in ModuleMeta.

All tables share the host's single schema on both Postgres and SQLite, so a cross-module FK is just an ordinary same-schema reference (orders_order.id ← invoices_invoice.order_id).

On SQLite, FKs are off by default but the test suite enables them; in production SQLite use (rare), set PRAGMA foreign_keys = ON.

Data migrations

Alembic supports ad-hoc op.execute("UPDATE ...") inside a migration. Use for:

• Backfilling new NOT NULL columns — add as nullable, backfill, alter to NOT NULL.
• Renaming columns — op.alter_column with new_column_name=... preserves data.
• Migrating enum values.

Data migrations run as part of alembic upgrade. If they fail mid-way, you're left at an intermediate state — design them to be idempotent (use ON CONFLICT, IF EXISTS, UPDATE ... WHERE x IS NULL).

For long-running backfills on big tables, break into batches and run outside Alembic:

# scripts/backfill_orders_total.py
async def backfill(batch_size=1000): ...

Migration drift in monorepos

git pull introducing two parallel branches of Alembic revisions:

revision A  ← you created
revision B  ← teammate's branch created

Both have down_revision = <previous>, so the same branch now has two heads. (Heads on different labelled branches are expected and need no merge; upgrade heads applies them all.) Resolve a same-branch fork by:

1. uv run alembic heads — list them.
2. uv run alembic merge -m "merge A and B" <rev-A> <rev-B> — creates a merge revision with both as parents.
3. Commit the merge revision.

Keep merges small; a merge revision with its own op.* logic is a code smell.

Initial-migration gotchas

When you autogenerate a migration for a freshly-added module, autogenerate writes op.create_table(...) for every table in the module's MetaData. Inspect:

• Are the table names right (the module-prefixed orders_order, identical on Postgres and SQLite)?
• Do indexes and constraints have stable names? Rename via name=... on the model if not.
• Did autogenerate also pick up any other module's tables? That means you forgot make migrate after the last scaffold. Squash the file down to just this module's changes.

Testing migrations

The db_session fixture stamps alembic_version at head on a fresh in-memory DB, which validates that build_module_metadata() + your migrations produce a schema the tables can operate against. That said, integration tests don't run every Alembic op — if you want to exercise the migrations explicitly:

@pytest.mark.asyncio
async def test_migration_up_then_down(tmp_path):
    from alembic.config import Config
    from alembic import command

    db_url = f"sqlite:///{tmp_path}/migrate_test.db"
    cfg = Config("host/alembic.ini")
    cfg.set_main_option("sqlalchemy.url", db_url)

    command.upgrade(cfg, "heads")
    command.downgrade(cfg, "base")

That catches the common "autogenerate wrote a broken downgrade" class of bugs.