Schema Migration Guide

Issue: #2190

This document explains Open ACE's schema migration strategy, authoring rules, best practices, and troubleshooting.

The Schema Authority Model

Open ACE adopts the authority model of Alembic as the only channel for schema changes:

┌─────────────────────────────────────────────────────────────┐
│              Schema change authority model                   │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  Production: alembic upgrade head is the ONLY schema        │
│              change channel                                 │
│                                                             │
│  Development: SQLite bootstrap is allowed, but must stay    │
│              separated from the production path             │
│                                                             │
│  Forbidden: create_app() running DDL against PostgreSQL     │
│             runtime auto-adding columns / ALTER TABLE       │
│             business code repairing schema on missing cols  │
└─────────────────────────────────────────────────────────────┘

Preparation Before Migrating

1. Check the current database version

alembic current

Example output:

20260801_001_add_platform_tenant_admin_roles (head)

2. Review pending migrations

alembic history --verbose

3. Back up the database

# PostgreSQL
pg_dump -h localhost -U open-ace ace > backup_$(date +%Y%m%d).sql

# SQLite
cp open-ace.db open-ace.db.backup

See also ../guide/DATABASE_BACKUP.md for the full backup/restore procedures (Kubernetes CronJob and Docker Compose).

Running Migrations

Standard upgrade flow

# 1. Check the current version
alembic current

# 2. Run the upgrade
alembic upgrade head

# 3. Verify the upgrade succeeded
alembic current  # should print the latest revision

# 4. Verify schema integrity
python3 scripts/verify_schema_integrity.py

Downgrade (rollback)

# Roll back one revision
alembic downgrade -1

# Roll back to a specific revision
alembic downgrade <revision_id>

# Roll back all migrations (database structure kept)
alembic downgrade base

Note: downgrades drop columns and may lose data. Always back up before downgrading in production.

Migration Authoring Rules (MIG001–MIG003)

Three repository-level migration constraints are enforced automatically by scripts/lint/check_migration_rules.py. They are easy to miss and hard to infer from local unit tests alone (Issue #1704), because the failure only surfaces when migrations load from a synthetic pre-merged tree (CI) or run against PostgreSQL (no PG service in the default CI job). All three rules are enforced by a pre-commit hook and by the Migration Graph CI workflow (.github/workflows/migration-graph.yml), which assembles the pre-merged tree (base branch + PR) and checks it before merge.

MIG001 — Migrations must not import `app.*` runtime modules

The migration-graph CI job and ScriptDirectory.get_heads() load each migration module from a synthetic pre-merged tree that does not contain the app/ package. A migration that does from app.xxx import ... therefore fails to import there, breaking the single-head check with an opaque ImportError — even though every local test passes.

Rule: migration files under migrations/versions/ must not import app or any app.* submodule. Operate via alembic.op, sqlalchemy, schema introspection queries (information_schema / sqlite_master), and the sibling migrations.baseline helper only. The only exception is an import guarded by if TYPE_CHECKING:, which is never executed at import time and so cannot break module loading.

MIG002 — PostgreSQL `CONCURRENTLY` operations must use the approved pattern

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Issuing it the wrong way raises ACTIVE SQL TRANSACTION (or silently misbehaves) during alembic upgrade on PostgreSQL. There is exactly one approved pattern:

def _is_postgresql() -> bool:
    return op.get_bind().dialect.name == "postgresql"


def upgrade() -> None:
    if _is_postgresql():
        with op.get_context().autocommit_block():          # <- required wrapper
            op.create_index(
                INDEX_NAME, TABLE, COLUMNS,
                postgresql_concurrently=True,              # <- required kwarg
            )
    else:
        op.create_index(INDEX_NAME, TABLE, COLUMNS)        # SQLite: plain index

The downgrade() mirrors this with op.drop_index(..., postgresql_concurrently=True) inside its own autocommit_block(). The check rejects two mistakes:

Raw concurrent DDL via op.execute(...) / conn.execute(...) / sa.text(...) with a string literal containing CONCURRENTLY. Raw SQL bypasses Alembic's autocommit handling. The check matches any statement led by a CONCURRENTLY-bearing DDL verb (CREATE/DROP/REINDEX/REFRESH, covering CREATE/DROP INDEX, REINDEX, and REFRESH MATERIALIZED VIEW); use op.create_index/op.drop_index (wrapped as above) instead. REFRESH MATERIALIZED VIEW CONCURRENTLY has no Alembic helper — if you genuinely need it, run it outside Alembic (e.g. a post-deploy script), not in a migration.
postgresql_concurrently=True outside an autocommit_block(). The kwarg is what issues ... CONCURRENTLY; it is only valid outside a transaction, so the call must be lexically nested inside the with op.get_context().autocommit_block(): statement (inline the op.create_index call under the with, do not delegate to a sibling helper).

MIG003 — A released revision id must never disappear

Alembic keys history off the revision string, not the filename: every deployed database stores it verbatim in alembic_version.version_num. Delete or rewrite an id that is already reachable from the base branch and every database stamped with it dies on the next upgrade head with Can't locate revision identified by '<id>' — and stays stuck for every later migration too.

This is not hypothetical: 20260731_003_add_proxy_token_terminated_fields was renumbered to 20260731_004_... so a newer migration could take the 003 slot, wedging every database upgraded in that window. The recovery was migrations/versions/20260731_003_bridge_renamed_proxy_token_revision.py — a no-DDL node that exists only to keep the old id alive.

Rule: renaming the file is fine; only the id matters. If an id genuinely has to stop carrying DDL, keep the node and empty its upgrade() instead of deleting it. The check compares post-merge ids against the base branch (default origin/main), which is precisely what a deployed database will experience.

Running the checks

# Check the committed migrations/versions/ tree
python3 scripts/lint/check_migration_rules.py

# Check an alternate tree (e.g. a synthetic pre-merged tree)
python3 scripts/lint/check_migration_rules.py /path/to/migrations/versions

The pre-commit hook check-migration-rules runs this on every commit that touches migrations/versions/*.py; the Migration Graph CI workflow (.github/workflows/migration-graph.yml) runs it against the pre-merged tree and additionally asserts a single Alembic head (two PRs forking off the same parent migration create no git conflict but fork the chain — only the pre-merge single-head check catches it). Both exit non-zero with a file:line: MIGxx ... message on violation.

Migration Strategies

Expand/Contract pattern

For rolling-upgrade scenarios, use the expand/contract migration pattern:

Phase 1: Expand

Add nullable columns or columns with defaults:

# migration: add_column_expand.py
def upgrade():
    op.add_column(
        "users",
        sa.Column("new_field", sa.Text(), nullable=True),  # nullable
    )

Properties:

New applications can use the new column
Old applications ignore it and are unaffected
New and old versions can coexist

Phase 2: Steady state

All pods upgrade to the new version and the new column is used normally.

Phase 3: Contract (optional)

Once rollback is no longer required, add constraints or drop old columns:

# migration: add_column_constraint.py
def upgrade():
    # Add a NOT NULL constraint
    op.alter_column(
        "users",
        "new_field",
        nullable=False,
        server_default="default_value",
    )

Compatibility window

How application releases map to schema versions:

App version	Minimum upgrade starting point	Required at runtime
v2.0.x	`baseline_2026_06_23`	Alembic head shipped with the release
v1.2.x	`baseline_2026_06_23`	Alembic head shipped with the release

Databases on a pre-baseline revision (e.g. a historical hash from before v1.2.0) cannot be upgraded in place; there is no supported migration path from them.

Decision logic:

Install / upgrade: scripts/check_min_revision.py runs before alembic upgrade head and accepts any revision in the baseline lineage, so the upgrade can apply the missing migrations. A pre-baseline revision fails the install with an error.
Web and scheduler startup: check_schema_compatibility() (app/repositories/schema_guard.py) requires the fully migrated head, so services never run on a partially migrated schema.

Forbidden Operations

❌ No runtime DDL in production

# Wrong (forbidden)
@app.before_request
def ensure_columns():
    # Do NOT run ALTER TABLE at runtime
    cursor.execute("ALTER TABLE users ADD COLUMN ...")

❌ Business code must not modify the schema

# Wrong
def create_session():
    try:
        cursor.execute("INSERT INTO sessions ...")
    except ColumnMissingError:
        # Do not try to repair the schema
        cursor.execute("ALTER TABLE sessions ADD COLUMN ...")
        cursor.execute("INSERT INTO sessions ...")

✅ The correct approach

When a missing column is discovered:

Stop the application
Run the migration
Restart the application

Troubleshooting

Migration execution failure

Symptom: alembic upgrade head errors out

Diagnosis steps:

# 1. Check the database connection
pg_isready -h <host> -p <port>

# 2. Check the current version
alembic current

# 3. Check the migration history
alembic history

# 4. Inspect the detailed error
alembic upgrade head --sql

Common errors and fixes:

Error	Cause	Fix
`Can't locate revision`	broken migration chain	check `down_revision` values; if a released id was deleted, restore it (MIG003)
`relation "xxx" already exists`	migration re-executed	use idempotency checks
`column "xxx" of relation "xxx" does not exist`	column not added	check migration ordering
`ACTIVE SQL TRANSACTION` on a `CONCURRENTLY` index	raw DDL or missing `autocommit_block()`	use the MIG002 template

Schema version mismatch

Symptom: application startup fails reporting the schema version is too old

Fix:

# 1. Check the current version
alembic current

# 2. Run the upgrade
alembic upgrade head

# 3. For a fresh database, run full initialization
alembic upgrade head
python3 scripts/init_db.py  # create default users

Concurrent migration conflicts across pods

Symptom: multiple pods start at once and migrations hit lock waits or deadlocks

Fix:

Option 1: Kubernetes init container

initContainers:
- name: run-migrations
  image: open-ace:latest
  command: ["alembic", "upgrade", "head"]
  env:
  - name: DATABASE_URL
    valueFrom:
      secretKeyRef:
        name: db-credentials
        key: url

Option 2: a dedicated migration job

# Run the migration job before deploying the application
apiVersion: batch/v1
kind: Job
metadata:
  name: schema-migration
spec:
  template:
    spec:
      containers:
      - name: migrator
        image: open-ace:latest
        command: ["alembic", "upgrade", "head"]

/readyz Endpoint

The application exposes /readyz for readiness checks:

curl http://localhost:19888/readyz

Example response:

{
  "status": "ready",
  "checks": {
    "database": {"status": "ok"},
    "schema_version": {
      "status": "ok",
      "compatible": true,
      "current": "20260801_001",
      "required": "baseline_2026_06_23"
    },
    "background_services": {"status": "ok"}
  }
}

Status codes:

200 OK: all checks passed, service is ready
503 Service Unavailable: a check failed and needs fixing

Note: /readyz reports status only — it never repairs schema problems.

Production audit

Before applying schema changes, audit all production instances:

python3 scripts/audit_production_schema.py --output audit_report.txt

The report covers:

Current schema version
Missing columns
Migration priorities

Best Practices

1. Writing migration files

def upgrade():
    # ✅ Use conditional checks for idempotency
    inspector = sa.inspect(op.get_bind())
    existing_columns = {col["name"] for col in inspector.get_columns("users")}

    if "new_column" not in existing_columns:
        op.add_column("users", sa.Column("new_column", sa.Text()))

def downgrade():
    # ✅ Check the column exists before dropping
    inspector = sa.inspect(op.get_bind())
    existing_columns = {col["name"] for col in inspector.get_columns("users")}

    if "new_column" in existing_columns:
        op.drop_column("users", "new_column")

2. Testing migrations

# 1. Verify upgrade in a test environment
alembic upgrade head

# 2. Verify downgrade
alembic downgrade -1
alembic upgrade head

# 3. Verify idempotency (repeat execution)
alembic upgrade head
alembic upgrade head  # must not error

3. Code-review checklist

When a PR includes a migration, check:

Both upgrade and downgrade paths are provided
Conditional checks make it idempotent
No app.* imports (MIG001)
CONCURRENTLY indexes use the autocommit_block() template (MIG002)
No previously released revision id was deleted or rewritten (MIG003)
Whether the expand/contract pattern applies is stated
Impact on rolling upgrades is noted
The upgrade path from baseline is tested

DATABASE_SCHEMA.md — database domain map (103 tables)
DATABASE_CONVENTIONS.md — field naming and type conventions
../guide/DATABASE_BACKUP.md — backup before migrating
scripts/lint/check_migration_rules.py — MIG001–MIG003 enforcement
.github/workflows/migration-graph.yml — Migration Graph CI workflow

Getting help

If you hit a schema migration problem:

Collect the error logs
Capture the output of alembic current
Capture the output of python3 scripts/audit_production_schema.py --json
Open an issue with the above attached

The Schema Authority Model​

Preparation Before Migrating​

1. Check the current database version​

2. Review pending migrations​

3. Back up the database​

Running Migrations​

Standard upgrade flow​

Downgrade (rollback)​

Migration Authoring Rules (MIG001–MIG003)​

MIG001 — Migrations must not import app.* runtime modules​

MIG002 — PostgreSQL CONCURRENTLY operations must use the approved pattern​

MIG003 — A released revision id must never disappear​

Running the checks​

Migration Strategies​

Expand/Contract pattern​

Phase 1: Expand​

Phase 2: Steady state​

Phase 3: Contract (optional)​

Compatibility window​

Forbidden Operations​

❌ No runtime DDL in production​

❌ Business code must not modify the schema​

✅ The correct approach​

Troubleshooting​

Migration execution failure​

Schema version mismatch​

Concurrent migration conflicts across pods​

Option 1: Kubernetes init container​

Option 2: a dedicated migration job​

/readyz Endpoint​

Production audit​

Best Practices​

1. Writing migration files​

2. Testing migrations​

3. Code-review checklist​

Related documentation​

Getting help​