Skip to main content
Every environment applies schema changes with the same tool (Alembic, configured by alembic.ini and migrations/env.py) and the same privilege boundary: only a one-off task with owner/migrator credentials ever runs DDL. The long-running API and product worker never receive those credentials. This page covers the branch layout, how each environment invokes it, and what happens when a tenant schema needs to catch up on a migration after it was already provisioned.

​
Branch layout

migrations/versions/ is not one linear history — it’s three independent Alembic branches sharing one alembic.ini / migrations/env.py, distinguished by branch_labels: The control and tenant_template branches both start from down_revision = None — they’re independent trees, not descendants of the legacy unlabeled branch. migrations/env.py’s _tenant_schema_argument() reads an -x schema=<tenant_schema> Alembic argument; when it’s set (only ever for the tenant_template branch), run_migrations_online() creates that schema if missing, points search_path at it, and tracks that schema’s own alembic_version row via version_table_schema — so every tenant gets an independently-versioned copy of the same table set, and the control branch always migrates the fixed control schema regardless.
The tenant_template migrations themselves use unqualified table names (no schema prefix) specifically so the same migration file works correctly regardless of which schema search_path is currently pointed at — see the module docstring in migrations/versions/20260722_t001_tenant_template_schema.py. Every table in that branch also carries a tenant_id column and a FORCE ROW LEVEL SECURITY policy as defense in depth on top of schema-per-tenant isolation — see Tenancy and data model.

​
DSN resolution: MIGRATIONS_DATABASE_URL vs APP_DATABASE_URL

services/api/tenancy/db.py documents three connection-string roles:
  • APP_DATABASE_URL — the app_rw role’s connection string. Used by the API and the normal product worker for all runtime reads/writes. This role cannot run DDL.
  • MIGRATIONS_DATABASE_URL — the owning/migrator role’s direct connection string. Used only by Alembic and the provisioner, for DDL and for advisory locks that don’t tolerate transaction-mode connection pooling. migrations/env.py’s _database_url() reads this first, falling back to CAUSELOOP_POSTGRES_DSN and then DATABASE_URL for compatibility with the legacy MVP1 relational runtime’s env-var conventions.
  • DATABASE_URL — a backward-compatible owner/pooler URL, re-targeted to app_rw only when APP_DATABASE_URL is absent.
This split is the mechanism behind the “only the migration task/container has DDL access” boundary stated everywhere else in this documentation: any process that should run migrations gets MIGRATIONS_DATABASE_URL; any process that should only serve traffic gets APP_DATABASE_URL and nothing else.

​
The local one-off migrate service

infra/docker-compose.product.yml defines a migrate service (restart: "no") that runs:
scripts/migrate_product.py does three things in order, using MIGRATIONS_DATABASE_URL (the compose file sets it to the causeloop superuser DSN, distinct from the app_rw DSN the API uses):
  1. run_control_migrations() — alembic upgrade control@head, applying every control branch revision.
  2. ensure_app_rw_role() — creates the app_rw Postgres role if it doesn’t exist (or updates its password/attributes if it does), with NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS. Because CREATE ROLE ... PASSWORD is DDL and can’t take a bind parameter, this uses psycopg.sql.Literal to embed the password safely, on its own autocommitted connection.
  3. grant_app_rw_on_control() — grants app_rw SELECT, INSERT, UPDATE, DELETE on every table in the control schema, plus a default-privileges rule so future tables in that schema are automatically granted too.
Every other container in the compose file that touches the database (local-admin, product-worker, provisioner-worker, api) declares depends_on: migrate: condition: service_completed_successfully (directly, or transitively through another dependency), so nothing can run against a database migrate hasn’t already finished preparing. Note that migrate only runs the control@head upgrade — it never touches tenant_template. Tenant schemas are migrated individually, at provisioning time (see below), not as part of this global step.

​
AWS and Azure: the same script, as a one-off task/job

Both cloud templates run the identical command as a one-off workload, distinct from the long-running services:
  • AWS (deploy/aws/main.tf): an aws_ecs_task_definition.migration Fargate task definition with command = ["python", "scripts/migrate_product.py"], receiving only MIGRATIONS_DATABASE_URL (the owner DSN) and APP_RW_PASSWORD as Secrets Manager secrets. deploy/aws/run-migration.sh runs it with aws ecs run-task --launch-type FARGATE (no public IP), waits with aws ecs wait tasks-stopped, and asserts the container’s exit code is 0 before returning — it fails loudly under set -eu rather than leaving you to check the console.
  • Azure (deploy/azure/main.bicep): a Microsoft.App/jobs Container Apps Job (migrationJob) whose container also runs command: ['python', 'scripts/migrate_product.py'], started manually with az containerapp job start --name causeloop-production-migration --resource-group YOUR_RESOURCE_GROUP and requiring a successful execution before proceeding.
Both providers apply the same release order documented in docs/CLOUD_DEPLOYMENT.md: deploy infrastructure with long-running services disabled (activate_services=false / activateServices=false) → run the migration task/job → run the administrator-bootstrap task/job → only then enable the frontend, API, and workers.

​
Provisioning-time tenant migrations

New tenants never need a separate migration step: provision_tenant_by_slug() (in services/api/tenancy/provisioner.py) calls run_tenant_migrations(schema_name) as part of provisioning, which runs:
with -x schema=<tenant_schema> set via cfg.cmd_opts. Because Alembic tracks its own per-schema alembic_version row (via version_table_schema in migrations/env.py), this is naturally idempotent — a schema already at head is a no-op if run again.

​
The migrate_all_tenants.py caveat for already-provisioned tenants

New tenant_template revisions only reach a tenant automatically at provisioning time. An already-live tenant’s schema does not pick up a new revision on its own — something has to explicitly run run_tenant_migrations() against it again. scripts/migrate_all_tenants.py does exactly that, for every non-offboarded tenant (or a --only <slug> subset):
It lists tenants from control.tenants (excluding lifecycle_state = 'offboarded') and calls run_tenant_migrations(schema_name) for each — safe to re-run, since a tenant already at head is a no-op.
services/api/onboarding_api.py documents a real operational limitation with this script, in a comment directly above the POST /admin/tenants/{tenant_id}/run-migrations endpoint it explains: this deployment has had no direct external Postgres access from at least one network the backend has been developed from — Render’s external database endpoint refuses the connection. That means scripts/migrate_all_tenants.py cannot always be run locally against already-provisioned tenants on that deployment, even though brand-new tenants still pick up new tenant_template migrations automatically at provisioning time (provision_tenant always runs to head).The workaround is the run-migrations endpoint itself: it calls the exact same run_tenant_migrations(tenant.schema_name) function, but over HTTPS, from wherever the backend process itself already has working database connectivity — so an already-live tenant can be caught up the same way without needing local network access to the database. The comment marks this as TEMPORARY, to be removed once local migration access is fixed or every current tenant is past whichever migration prompted adding it.This endpoint is deliberately outside product_app.py’s ONBOARDING_OPERATIONS allowlist (which ends at go-live), so _selected_router drops it — on every environment that runs product_app:app (local Compose, AWS, Azure) it 404s. It’s reachable only on the legacy services.api.main:app deployment, i.e. the current Render demo (see Render + Vercel demo). On product_app environments, use scripts/migrate_all_tenants.py instead.

​
Rollback posture

Every migration file in migrations/versions/ defines a real downgrade() — most execute destructive DROP TABLE IF EXISTS / DROP COLUMN IF EXISTS statements, not a no-op placeholder. That means alembic downgrade is technically available, but running it against a database holding real tenant data means losing whatever columns or tables that revision introduced — there’s no data-preserving downgrade path. In practice, docs/CLOUD_OPERATIONS.md establishes the actual rollback discipline used in production: readiness, not alembic downgrade, is the deployment and rollback gate. GET /health/ready — not /health/live — is what should decide whether a deployment is trusted; traffic should never be routed on liveness alone. If a migration or deploy is bad, the documented recovery path is restore-oriented, not downgrade-oriented:
  1. Stop new writes (scale API and workers to zero) if data integrity is uncertain.
  2. Preserve PostgreSQL job rows, logs, request IDs, and object versions as-is.
  3. Restore PostgreSQL first — it is the metadata and business authority, ahead of anything in the object store.
  4. Restore or select the correct object versions next.
  5. Bring the provisioner, product worker, API, and frontend back up in that order.
  6. Require readiness and reconcile any queued/running jobs before reopening traffic.
The same document’s AWS and Azure quarterly recovery drills (restore RDS/Flexible Server to an isolated instance, run migrations in validation mode against the restore, verify tenant/control counts and one insight snapshot, verify a sampled object’s SHA-256, then tear down only the named rehearsal resources) are the practiced form of this same restore-first posture. scripts/rehearse_product_backup.sh is the local, cheap-to-run version of the same rehearsal — see Local parity stack.