# Migrations that run on every boot

Connect migrates its schema on every boot, from the mapped models, with no migration framework: `migrate.py` compares what the models declare against what the database has and adds what is missing. It is additive only and never drops or rewrites data. The price of that is exact — every step must survive being run again, and against two instances booting at once.

- **Status:** Available
- **Audience:** both, developer
- **Last verified:** 2026-09-10
- **Canonical:** https://connectbyjbrh.com/research/boot-time-migrations/

## Why the models are the source of truth

`create_all` adds new tables and never new columns, so a database created by an earlier version silently loses features as the models move on: the code expects a column, the table does not have it, and the failure arrives at the first query rather than at deploy. Walking the mapped models at boot and adding what is absent closes that gap without a numbered chain of scripts to keep in order.

The trade is real and worth stating plainly. A numbered chain gives you a reviewable history and an explicit down-path; boot-time reconciliation gives you a database that matches the code that is actually running, on every machine, with no step anyone can forget to run. The second is worth more when deploys are frequent and the schema is additive.

## What idempotent has to mean here

| Step | Made safe by | What it costs to get wrong |
|---|---|---|
| Add a missing column | Compare against the live inspection first; add only what is absent | A duplicate-column error aborts boot, and the instance never serves a request |
| Add a declared index | Create by name, only when missing | A column added to an existing table arrives unindexed — for `workspace_id` that is a sequential scan on the one column in every scoped read |
| Backfill a value | Update only rows that still need it | A repeat that overwrites corrected data is worse than the gap it filled |
| A one-off repair | A stamp row, written once, tolerating a concurrent boot | Re-running a repair re-promotes a mailbox somebody deliberately demoted |
| Move a uniqueness constraint | Drop the old and create the new in one transaction | Leaving both in force keeps the global constraint that refuses a legitimate second business |

Two of these were learned by aborting boots rather than by design. DDL compiled without the target dialect emits `DATETIME`, which PostgreSQL does not have, so the first timestamp column added to an existing table would have stopped every PostgreSQL deployment from starting. The same shape again: PostgreSQL rejects `DEFAULT 0` on a boolean column, so the first boolean column added did stop them. Both are now compiled for the database that will run the statement, in a form both supported backends accept, so there is no branch to keep in step.

## Two boots at the same instant

A rolling deploy is exactly two boots at the same moment, and a stamped migration written as *look for the stamp, then insert one* is a check-then-insert. Both instances saw no stamp, both inserted, and the one that committed second died on the primary key — inside the migration step the lifespan handler awaits, so that instance never served a request at all.

The insert now tolerates the conflict. Losing that race is not a failure: the stamp the other boot wrote says exactly what this one was about to say, and the work behind a stamp is idempotent by construction — which is why it is stamped rather than guarded.

> **Careful** Any boot-time step that reads-then-writes is a race unless the write itself is conflict-tolerant. The window is small, and a rolling deploy aims two instances straight at it.

## The guard reflection cannot express

A reflection API answers questions about *shape*: which tables exist, which columns and types they carry, which indexes are declared. It cannot answer questions about *policy* or *history* — whether row-level security is enabled and forced on a table, whether a repair has already been applied, whether a default was meant to be permanent. Those facts have to be carried somewhere else.

**Row-level security** — Not visible to `create_all` and not created by it. Policies come from `tools/pg_harden.py`, an operational script, so a new scoped table needs its policy applied in the same release
**Scope membership** — A table carrying `workspace_id` that is missing from `SCOPED_TABLES` is filtered by nothing at all, and reflection has no opinion about that
**Whether a repair already ran** — A stamp row in `settings`, because the database cannot be asked
**Whether a repair *should* run again** — A separate, cheaper invariant check that touches only the workspaces that are actually wrong

Three `prospect_*` tables shipped with row-level security off for exactly this reason: the tables were created at boot, the policy was not, and nothing in the schema comparison could notice. They were enabled and FORCED afterwards with the same policy every other scoped table carries, and a test now fails on any new table that repeats it. The guard that reflection could not express became an assertion in the suite instead.

## Limits of the approach

- Additive only. Dropping a column, narrowing a type or rewriting data is outside what runs at boot, and deliberately so — a destructive step that runs automatically on every machine is a bad trade at any speed.
- Boot gets slower in proportion to the work, and a step that blocks holds the instance out of service while it runs. Backfills therefore have to be bounded or stamped.
- There is no down-path. Rolling back the code does not roll back an added column; the additive-only rule is what makes that acceptable.
- None of this proves the policies are correct — only that they are applied. Whether a policy says the right thing is what the isolation tests are for.

## Questions

### Why not use a migration framework?

Because the failure this replaces is a database that is out of step with the code running against it, and a framework does not prevent that — it moves it to whether somebody ran the step. Reconciling from the models at boot means the machine that is starting has the schema that machine's code expects. The cost is that nothing may be destructive.

### What happens if a migration step fails?

The instance does not finish starting, which is the correct outcome: an application serving requests against a schema it could not reconcile would fail later, in a request, with a much less useful message. Both of the aborted-boot incidents above were found this way rather than in production data.

### How do you keep a new scoped table from repeating the policy gap?

By treating the policy as part of the release rather than as an afterthought, and by having a test that fails on a table with a workspace column and no forced policy. See [Three independent layers of tenant isolation](/research/three-layers-of-isolation/) for what the policy is protecting.

## Related

- [Data that is invisible rather than missing](https://connectbyjbrh.com/research/workspace-stamp/)
- [Three independent layers of tenant isolation](https://connectbyjbrh.com/research/three-layers-of-isolation/)
- [Five ways a test suite has passed while proving nothing](https://connectbyjbrh.com/research/false-passing-tests/)
- [Security and isolation](https://connectbyjbrh.com/docs/security/)
- [The query cost that grows with the business](https://connectbyjbrh.com/research/n-plus-one-that-grows/)

## What this page is based on

- `backend/app/migrate.py` — the module docstring, `add_missing_columns`, `ensure_declared_indexes`, `_stamp_once` and the dialect-compiled DDL notes
- `docs-source/sources/GENERAL.md` §3 — `SCOPED_TABLES`, `CONTROL_PLANE_TABLES`, and RLS from `tools/pg_harden.py`
- `docs-source/sources/CHANNELS.md` §4 — three `prospect_*` tables that shipped without a policy, and the test that now fails on a repeat