# Schema migrations A schema migration changes the shape of a database that already holds data. Connect runs its migrations at every boot rather than as a separate deploy step, which removes a whole class of ordering mistakes and imposes one hard rule in exchange: every step must be safe to run again, on a database where it has already been run. - **Status:** Reference - **Audience:** developer, both - **Last verified:** 2026-09-10 - **Canonical:** https://connectbyjbrh.com/docs/technology/migrations/ ## Why run them at boot The alternative — a migration command somebody runs before or after releasing — has a failure mode that is quiet and expensive: the application starts against a schema it does not expect, because the command was skipped, ran against the wrong database, or ran after the instance that needed it. Running at boot makes the schema a precondition of starting rather than an adjacent task. It also makes every environment converge on the same shape. A test database, a preview database and production all reach the current schema by the same path, so “it works locally” stops depending on which migrations somebody remembered to run. **What you gain** — No ordering step to forget, and no environment that quietly lags the code **What you owe** — Every step idempotent, fast enough to sit in the start-up path, and safe when two instances start at once **What you cannot do** — A long rewrite of a large table. That is an operational task with its own window, not a boot step ## The guard is the migration A boot migration is written as a pair: a check that asks whether the change is already present, and the change itself. The check is the part that gets the design attention, because the change is usually one statement and the check is where correctness lives. A guard that answers *no* when the answer is *yes* re-applies work every boot — at best wasteful, at worst an error that stops start-up. A guard that answers *yes* when the answer is *no* skips the change silently, and the application runs against a schema missing the column it is about to write to. The second is far harder to notice, because nothing fails until the first request that needs it. > **Note** Idempotent means safe when repeated, not merely harmless. Adding a row “only if missing” is idempotent; appending to a list every time is not, even though each run individually succeeds. ## The index a reflection API cannot see The obvious way to write a guard is to ask the ORM's reflection API what the table currently has: list the columns, list the indexes, compare. That works for ordinary indexes, which are described by the columns they cover. It does not work for an **expression index** — an index over a computed value rather than a column, such as a case-folded or normalised form of a field. There is no column for the reflection API to report, so the index is absent from the list it hands back, and a guard that trusts that list concludes the index is missing on every single boot. ```text guard by reflection -> inspector.get_indexes(table) -> expression index not listed -> create attempted every boot guard by catalogue -> query pg_indexes / pg_class by index name -> found -> skipped correctly ``` So the guard for that kind of index has to query the database catalogue by name rather than ask an abstraction. It is a small piece of portability given up for a guard that tells the truth, which is the right trade in a step that runs at every start-up. ## Does Connect use boot migrations? Yes — `migrate.py` owns the schema and runs at every boot. Three consequences are worth knowing, because they have each produced a real defect. 1. **Creating a table is not securing it.** `create_all` makes a table at boot, but row-level security comes from `tools/pg_harden.py`, an operational script. Three prospecting tables once shipped with row-level security switched off for exactly this reason; they were enabled and forced afterwards, and `test_global_prospect_intelligence_v1` now fails on any new table that repeats it. 2. **A scoped table missing from `SCOPED_TABLES` is scoped by nothing.** The kernel filters what that list names. A `workspace_id` column is not a filter; it is a place to put one. 3. **Data repairs belong to the schema owner, at boot.** A row stamped with an empty workspace identifier cannot be repaired from a request: the policy's `WITH CHECK` refuses the write, turning an invisible row into a server error. `migrate.backfill_canonical_workspace` does it as the schema owner instead, every boot, until there is nothing left to repair. None of this is visible from a screen, and none of it needs to be. It matters here because it explains why some fixes in this product arrive as a boot step rather than as a button. ## Failure modes **Start-up is slower after a release** — A migration is doing real work. Acceptable once; a signal to move the work out of the boot path if it repeats **A change appears to have been applied and was not** — A guard answered yes wrongly. Expression indexes and anything else reflection cannot see are the usual cause **Two instances start together and one errors** — The change was not written to tolerate a concurrent identical attempt **A new table has no isolation** — The hardening step did not run for it, or the table is missing from the scoped list ## Questions ### Do I have to run anything when Connect is updated? No. Migrations run as part of start-up, so a release brings its own schema with it. There is no maintenance command for a workspace to run and no window during which the database is half-updated relative to the code that reads it. ### Why would a guard need to query the database catalogue directly? Because reflection describes indexes by the columns they cover, and an expression index covers a computed value rather than a column. It therefore does not appear in the reflected list at all, and a guard built on that list would try to create it again at every boot. Asking the catalogue by index name gives the true answer. ### Does creating a table automatically make it workspace-isolated? No, and this is the mistake worth remembering. Table creation happens at boot; row-level security is applied by an operational script, and the workspace kernel filters only the tables named in its scoped list. A new scoped table needs both in the same release or it is protected by neither. ## Related - [PostgreSQL](https://connectbyjbrh.com/docs/technology/postgresql/) - [Row-level security](https://connectbyjbrh.com/docs/technology/row-level-security/) - [Relational data modelling](https://connectbyjbrh.com/docs/technology/relational-data/) - [Workspace isolation](https://connectbyjbrh.com/docs/security/workspace-isolation/) - [Versioning](https://connectbyjbrh.com/docs/technology/versioning/) ## What this page is based on - `docs-source/sources/GENERAL.md` §3 and §4 — the data model, migrate.py - `docs-source/sources/CHANNELS.md` §1 and §4 — backfill at boot, RLS on new tables - Connect capability registry (docs-source/facts.py)