> ## Documentation Index
> Fetch the complete documentation index at: https://docs.causeloop.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Migrations

> Alembic's three-branch layout, the one-off migrate container that owns DDL, the AWS/Azure migration jobs, the migrate_all_tenants.py caveat, and rollback posture.

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`:

| Branch                | Root revision                                            | Head (as of this writing) | Applies to                                                                                                                                                                                                |
| --------------------- | -------------------------------------------------------- | ------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| *(unlabeled/default)* | `20260708_0001`                                          | `20260714_0007`           | The historical MVP1 relational research runtime's schema (embedding store, fishbone hierarchy) — not the multi-tenant product schema.                                                                     |
| `control`             | `20260722_c001` (`branch_labels = ("control",)`)         | `20260727_c003`           | The single control-plane schema (`control.*`): employees, tenants, invitations, audit log, durable platform jobs, runtime heartbeats.                                                                     |
| `tenant_template`     | `20260722_t001` (`branch_labels = ("tenant_template",)`) | `20260727_t005`           | The per-tenant schema template: users, sessions, role profiles, sources, datasets, records, ingest jobs, model checkpoints, LLM settings — everything that lives inside one tenant's own Postgres schema. |

```mermaid theme={null}
flowchart TD
    subgraph unlabeled["unlabeled branch (legacy research runtime)"]
        U1["20260708_0001"] --> U2["..."] --> U7["20260714_0007 (head)"]
    end
    subgraph control["control branch"]
        C1["20260722_c001<br/>CREATE SCHEMA control"] --> C2["20260722_c002"] --> C3["20260727_c003 (head)"]
    end
    subgraph tenant["tenant_template branch"]
        T1["20260722_t001<br/>-x schema=&lt;tenant_schema&gt;"] --> T2["..."] --> T5["20260727_t005 (head)"]
    end
```

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.

<Note>
  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](/architecture/tenancy-and-data-model).
</Note>

## 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:

```bash theme={null}
python scripts/migrate_product.py
```

`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:

```python theme={null}
command.upgrade(cfg, "tenant_template@head")
```

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):

```bash theme={null}
uv run python scripts/migrate_all_tenants.py
uv run python scripts/migrate_all_tenants.py --only acme-bank --only globex-credit
```

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.

<Warning>
  `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](/deployment/render-vercel-demo)). On `product_app` environments, use `scripts/migrate_all_tenants.py` instead.
</Warning>

## 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](/deployment/local-parity-stack).

## Related

* [Cloud environments](/deployment/environments)
* [Local parity stack](/deployment/local-parity-stack)
* [AWS deployment](/deployment/aws)
* [Tenancy and data model](/architecture/tenancy-and-data-model)