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.
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.
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_alland not created by it. Policies come fromtools/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_idthat is missing fromSCOPED_TABLESis 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 for what the policy is protecting.