Database Migrations¶

Migrations are managed by Alembic and live in the appmecca-common submodule at appmecca-common/src/appmecca/db/migrations/. They ship inside the appmecca-common wheel (which provides the appmecca package) so both the deploy agent (on EC2) and the API container (in Docker) load the exact same migration chain at runtime.

Single Source of Truth¶

Two callers run the same code:

The deploy agent, on first boot, calls appmecca.db.migrate.ensure_database(url) from _ensure_database() in appmecca-common/src/appmecca/agent/watch.py. That function CREATE DATABASEs the appmecca database if missing then runs alembic upgrade head.
The API container, on every container start, runs the same appmecca.db.migrate.ensure_database(url) from appmecca-api/entrypoint.sh.

ensure_database also provisions the separate appmecca_logs database and returns a result dict — database, alembic, schema_present, public_table_count, logs_database, logs_tables, errors, fatal. The API entrypoint fails closed on that result: a fatal error, or an absent schema despite alembic reporting head, exits the container non-zero so blue/green never cuts traffic to an API that would 500 on every query.

Both callers serialise on a fixed PostgreSQL advisory lock (0x6170706d — 'appm') taken in migrations/env.py, so concurrent first-boot invocations from agent + API are race-free; whichever caller wins the lock applies migrations, the loser sees head already applied and returns immediately.

The advisory lock must be taken INSIDE alembic's transaction

env.py calls pg_advisory_xact_lock after context.begin_transaction(). Under SQLAlchemy 2.0's commit-as-you-go model, executing anything before that opens an implicit transaction alembic does not own — it is never committed, so the entire migration silently rolls back on connection close. The symptom is brutal: the log reports alembic_upgrade_head_complete while persisting zero tables. An always-on AST guard (tests/unit/db/test_migrate_env_transaction.py) pins the ordering.

Alembic's own version-table lock guards only the alembic_version row, not the DDL a migration runs — hence the explicit advisory lock.

There is no second source of schema truth — no hand-rolled appmecca.db.schema module, no inline DDL safety net in entrypoint.sh, no separate _run_migrations() helper.

Running Migrations¶

# From the platform repo
export APPMECCA_DATABASE_URL="postgresql://appmecca:password@host:5432/appmecca"

# Apply all pending migrations (delegates to alembic upgrade head)
.venv/bin/python -c "from appmecca.db.migrate import ensure_database; import os; ensure_database(os.environ['APPMECCA_DATABASE_URL'])"

# Or directly via alembic
.venv/bin/alembic -c appmecca-common/src/appmecca/db/migrations/alembic.ini upgrade head

# Check current revision
.venv/bin/alembic -c appmecca-common/src/appmecca/db/migrations/alembic.ini current

Network Access Required

The database lives in a private subnet. To run migrations from your laptop, open an SSH tunnel through the bastion (appmecca ssh connect bastion -e dev) or run from inside the VPC.

Creating New Migrations¶

.venv/bin/alembic -c appmecca-common/src/appmecca/db/migrations/alembic.ini revision -m "add notification_preferences column"

This creates a new file in appmecca-common/src/appmecca/db/migrations/versions/. Open it and write the upgrade() and downgrade() bodies. Use op.execute() with raw SQL — the platform doesn't use SQLAlchemy ORM models for migrations.

Naming convention: alembic generates a random hash for revision. The chain instead uses zero-padded sequential ids — the file is NNN_short_description.py with revision: str = "NNN" and down_revision pointing at the previous number. Rename the generated file and edit both fields to match.

One chain, many sessions

Two people creating a revision at the same time will both branch off the same down_revision, and alembic refuses to run a chain with two heads. Check the current head before writing a new migration, and re-point down_revision if someone landed first.

Idempotency convention: every CREATE TABLE should use IF NOT EXISTS, every CREATE INDEX should use IF NOT EXISTS, every ADD COLUMN should use IF NOT EXISTS. Triggers must be wrapped in:

DO $$ BEGIN
    CREATE TRIGGER trg_xyz ...;
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

This makes every migration safe to re-apply against a partially-migrated state — important because the platform deploys with no downtime, so an in-flight schema change must converge regardless of which container hits the DB first.

The Chain¶

001_initial_schema.py is the consolidated baseline, created after the previous chain (001 + 002–011, later also 002–017) was collapsed in April 2026 (see docs/plans/FIX-MIGRATION-CONSOLIDATION-PLAN.md). It creates 42 tables in one migration — including the multi-tenancy tables (users, groups, group_members, sessions, invites, platform_blocks, audit_events), the queue (deploy_queue, admission_overrides), and the health, email, usage and backup tables.

Note

The docstring at the top of 001_initial_schema.py still says "Tables (24 total)" and lists 25 names. That text predates the 002–017 merge and was never updated — count the CREATE TABLE statements, not the comment.

It also installs the update_updated_at() trigger function, the deploy_queue_status_error_consistency CHECK constraint, all indexes, and updated_at triggers on the eight tables that own an updated_at column.

Migrations 002 through 055 add tables and columns on top of that baseline, bringing the total to 66 tables. Load-bearing ones worth knowing:

Migration	What it does
`006`	Scopes the `apps` UNIQUE by `group_id` so two tenants can register the same repo name
`010`	Drops the `environment` column from ~31 tables and reshapes every constraint that used it
`011`	Restores the UNIQUE constraints that `010` had reduced to plain indexes
`034`	Apex / uniform-procedure columns on `custom_domains` plus the global `UNIQUE INDEX` on `domain`
`035`	`ns_zones`, for the NS-hosting tier
`036`	Adds the generated `lane` column on `deploy_queue` (control vs deploy); extended by `046` and `047`
`037`	Adds `branch` to `target_groups` and re-keys it
`042`	Drops `builds_app_name_tag_key` so a rebuild of an already-built commit no longer collides

See Database Schema for the resulting shapes.

Regenerating a generated column

A generated column cannot be altered in place, and ADD COLUMN IF NOT EXISTS against an existing column silently no-ops — shipping the old expression. Migrations 046 and 047 therefore DROP COLUMN IF EXISTS lane and re-ADD it whenever the classifier CASE changes. Follow that pattern for any future edit to lane.

Migration Strategy¶

Migrations use raw SQL via op.execute() rather than SQLAlchemy ORM models. This keeps the migration layer independent and avoids ORM coupling — the API uses asyncpg, the agent uses sync psycopg2, neither uses SQLAlchemy at runtime.

The `appmecca_logs` Database¶

The separate appmecca_logs database (unified log storage, container stats) is managed outside alembic by appmecca.db.logs_schema using direct CREATE TABLE IF NOT EXISTS, provisioned by the same ensure_database() call. Single source of truth, no divergence — alembic isn't needed today. If the logs schema ever grows non-trivial migrations, repeat the alembic pattern with a second chain at appmecca-common/src/appmecca/db/migrations_logs/.

Testing Migrations¶

The chain is exercised against a real postgres:16 container, not a mock:

make test-migrations        # full alembic chain against real PostgreSQL

tests/integration/test_migrations_apply.py applies the chain, asserts it is idempotent when re-applied, and includes an information_schema guard pinning the dynamic child-table purge invariant. make check and scripts/run-ci.sh both run it.

Rollback¶

# Downgrade one revision
.venv/bin/alembic -c appmecca-common/src/appmecca/db/migrations/alembic.ini downgrade -1

# Downgrade to base (drop all tables — destructive)
.venv/bin/alembic -c appmecca-common/src/appmecca/db/migrations/alembic.ini downgrade base

Warning

Downgrading drops tables and data. Always back up before downgrading.

Rollback is not the recovery path in a deployed environment

The API applies migrations from whichever appmecca-common wheel was baked into its image at build time. Downgrading the database while containers running the newer code are live will break them. To move backwards, roll the code back and redeploy; treat downgrade as a local-development tool.