Migration Rollout Safety
Why this skill exists
A migration is not a script. It runs unattended, on every environment, at a time
you do not choose, against data you cannot inspect first. It has no dry run, no
per-row audit log, no immutable reviewed plan of the rows it will touch, and no
automatic rollback or recovery once it has applied successfully — everything a
repair script gives you by construction. (A migration that raises inside an
explicit transaction does roll back — that is exactly why guards belong there;
what you cannot undo is a migration that succeeded on data it should have
refused.) On dev and prod it runs through Supabase Branching — outside GitHub
Actions, before the new app code is serving — so a failure is invisible in CI
and a silent success on wrong data is worse.
Repeated failure modes this skill blocks:
- An
update ... where <legacy condition> buried in a migration, invented to
"fix legacy rows" — unbounded, and applied unattended the moment the PR
merges, without anyone inspecting the rows it affects or recording what it
changed.
alter table ... add column if not exists added to paper over a local
iteration — hiding the fact that the schema contract it depends on was never
merged, or was rejected.
create index concurrently inside a migration — index builds on real data
need an operator-controlled statement timeout, progress reporting, retry, and
INVALID-index recovery, none of which a migration can offer.
- A blocking
create index on a large existing table — it holds a write lock
for the whole build and can hit the runner's timeout. Separately, an
interrupted CONCURRENTLY build (from the out-of-band script) can leave an
INVALID index behind that create index if not exists will never repair.
drop table / drop column on a state-bearing object that still has live
rows or still-referenced Vault secrets — data and secrets orphaned forever.
- Editing or renaming a migration that already shipped, without knowing which
environments applied it.
Scope
Applies to any change under apps/web/supabase/migrations/** — new file,
edit to an unshipped file, emergency edit to a shipped file, or a rename.
Complements, does not replace:
| Need |
Skill |
| Schema/RLS/function conventions, diff workflow, env delivery matrix |
.agents/skills/hamster-database/SKILL.md |
| Writing the audited repair/backfill script this skill sends you to |
.agents/skills/backfill-script-creator/SKILL.md |
| Concurrent index builder scripts |
apps/web/supabase/scripts/lib/concurrent-index-build.sh (header is the contract) |
| Context-graph perf validation for index/RPC changes |
.agents/skills/hamster-context-graph-perf/SKILL.md |
The core rule
Migrations install schema and fail closed. Production data repair,
backfill, and reconciliation run in explicit, audited scripts — before or
after the deploy, never inside it.
A migration may read data to assert its own preconditions and postconditions.
It may not fix data it finds wrong. When the data is wrong, the migration
raises and names the script the operator must run.
Decision table: migration or script?
| What you need to do |
Where it goes |
| Create/alter table, column, constraint, enum, policy, trigger, function, view |
Migration |
| Create a small/new-table index |
Migration (create index if not exists) |
| Create an index on a large existing table |
apps/web/supabase/scripts/build-*-index*.sh (CONCURRENTLY, out of band) before the migration; the migration reconciles + asserts |
| Repair legacy/invalid rows so new schema invariants hold |
apps/web/supabase/scripts/repair-*.ts before the migration; the migration only asserts |
| Populate a new column for existing rows (unbounded, or independent of the DDL) |
apps/web/supabase/scripts/backfill-*.ts after the migration |
| Dedup rows before adding a unique constraint |
repair-*/dedupe-* script before; migration asserts zero duplicates, then adds the constraint |
| Delete/cleanup obsolete rows or Vault secrets |
Audited cleanup script (e.g. cleanup-orphaned-connection-secrets.ts); migration asserts empty and refuses otherwise |
| Convert an existing column into its replacement, where the new schema is unusable without it and the conversion is bounded + deterministic |
Migration, under the narrow exception below |
| Reset/patch a specific tenant's data |
Script, never a migration |
| Insert fixed-literal seed / lookup rows the schema or product defines (idempotent, no dependency on existing rows) |
Migration |
Rule of thumb: if the statement reads or depends on production history, it
belongs in a script. A statement whose row set is fixed literals, or derives
only from the DDL in the same file, may stay — the latter under the exception
below.
Forbidden in migrations (pre-PR blockers)
-- ❌ Open-ended production repair: unbounded, no reviewed plan of the affected
-- rows, no record of what changed, applied unattended on merge.
update public.account_deletion_requests
set lease_expires_at = coalesce(started_at, updated_at, now()) + interval '6 hours'
where status = 'running' and lease_expires_at is null;
-- ❌ Re-homing / reparenting / re-keying legacy rows by heuristic.
update public.document d set parent_document_id = <guessed root> where <edge is invalid>;
-- ❌ Deleting "bad" rows so a constraint can be added.
delete from public.sync_entity_mappings where <duplicate predicate>;
-- ❌ Defensive DDL papering over a local revision: it hides that the schema
-- contract it depends on was never merged (or was rejected).
alter table public.account_deletion_requests
add column if not exists phase integer not null default 0;
-- ❌ CREATE INDEX CONCURRENTLY: needs a dedicated session, an operator-set
-- statement timeout, progress polling and INVALID-index recovery, so it belongs
-- in a builder script — and it can never sit inside begin/commit.
create index concurrently idx_foo on public.big_table (account_id);
-- ❌ Blocking build on a large existing table: holds a write lock for the whole
-- build and can hit the runner's ~2 min cap, aborting the deploy.
create index idx_foo on public.big_table (account_id);
-- ❌ Unguarded destructive DDL on a state-bearing table that may hold live rows.
drop table public.job_webhook_secrets;
What is not forbidden. Inserting fixed-literal seed or lookup rows that the
schema or product defines is ordinary migration work, provided it is idempotent
(on conflict do nothing / where not exists) and does not read existing rows.
The blocker targets update/delete and any insert derived from existing
rows — that is repair, not seeding.
Note: create index concurrently is described as "safe for large tables" in
generic Postgres advice and in older notes. In this repo it is a blocker
inside a migration — not because concurrency is wrong, but because a real-data
index build needs an operator in the loop: a raised statement timeout, progress
reporting, retry, and drop-and-rebuild recovery for an INVALID index, all of
which the builder script provides and a migration cannot. On top of that, any
apply path that wraps the file in a transaction rejects CONCURRENTLY outright.
Use the builder-script path.
Replace each with: an assertion in the migration + a named script in the rollout
notes. Assertion template:
do $$
declare
v_invalid bigint;
begin
select count(*) into v_invalid
from public.document d
left join public.document p on p.id = d.parent_document_id
where d.document_type = 'blueprint'
and d.parent_document_id is not null
and (p.id is null or p.document_type <> 'blueprint'
or p.account_id is distinct from d.account_id);
if v_invalid > 0 then
raise exception
'Cannot separate Blueprint folders: % Blueprint(s) have an invalid parent edge, which the new hierarchy reads would silently adopt into another bucket. Run supabase/scripts/repair-invalid-blueprint-parent-edges.ts --env <env> --execute before retrying',
v_invalid
using errcode = '23514';
end if;
end;
$$;
Assertion quality bar — a raise that does not tell the operator what to run is
half a guard:
- States the invariant that is now being trusted, and why violating it is bad.
- Reports the offending counts.
- Names the exact script + flags to fix it.
- Sets
using errcode = '23514' (check violation) so it is distinguishable
from an incidental SQL error.
- Uses the same predicate the repair script postchecks, so "script is
done" means exactly "migration will now pass".
Narrow exception: schema-coupled conversion
A data-touching statement may stay in the migration only if every one of
these holds. If you cannot state all five in the migration's comment header,
it goes in a script.
- Schema-coupled — the new schema is unusable without it (e.g. a marker
column the new reads depend on), and it must be atomic with the DDL: a
window where the DDL has landed and the value has not is a broken product.
- Bounded and derived from the DDL, not from history — writes at most one
row per pre-existing logical entity (e.g. one workspace-root marker per
account), not "all rows matching a legacy condition".
- Deterministic — the source of the value is unambiguous and enforced. If
ambiguity is possible (two candidate roots), the migration must refuse, not
pick.
- Transaction-wrapped, with the lock taken before the check — see below.
- Guarded by read-only pre- and post-assertions — a preflight proving the
premise still holds inside the transaction, and a postcondition proving the
conversion actually produced the intended state.
Worked example: 20260814101500_blueprint-workspace-root-marker.sql. It adds
is_workspace_root, derives it from the formerly-unique parentless root, and
then drops the index enforcing that uniqueness. Its preflight, inside the
transaction and behind the lock, proves (a) the old unique index still exists
and is valid, and (b) every account owning Blueprints has exactly one
parentless one — raising with the seeding script name when it does not. Whatever
an operator queried before deploying is irrelevant; only a check inside the
transaction proves the premise still holds at apply time.
Missing-vs-duplicate is worth reporting separately: they need different
remediation (seed one, or reparent extras).
Explicit transactions and lock scope
Apply paths differ, so never rely on implicit atomicity. A local
supabase migration up may execute a migration's statements without wrapping
them, while remote db push / Branching may wrap the file in a transaction. You
cannot depend on either. Any migration that takes a lock, or performs a
multi-statement conversion that must be all-or-nothing, MUST open with begin;
and end with commit; so its atomicity is a property of the file rather than of
the runner that happens to apply it.
begin;
-- Hold the writer lock for the whole transaction so no concurrent write can
-- slip in between the check and the DDL. Reads are unaffected.
lock table public.document in share row exclusive mode;
-- preflight assertions (inside the transaction, behind the lock)
-- DDL
-- bounded conversion
-- postcondition assertion
commit;
Lock rules:
- Take the lock before you count. A count outside the lock proves nothing:
a service-role insert between the count and the
drop/alter reintroduces
exactly the state you refused to run on.
- Pick the weakest lock that closes the race.
share row exclusive blocks
writers but not readers — right for a conversion. access exclusive (blocks
everything, including reads) is only for a drop-class change, e.g. proving
public.job_webhook_secrets is empty before dropping it.
- Keep the locked section short. No unbounded scans, no per-row loops, no
analyze inside it.
create index concurrently cannot run inside a transaction block, so it can
never be added to a transaction-wrapped migration — and it needs
operator-controlled timeout, progress and INVALID-index recovery anyway, so
it moves out of band to a builder script (see below).
Concurrent index workflow
Never create index concurrently in a migration. Never blocking-build on a
large existing table. Instead:
- Operator script, declaring targets and delegating to the shared engine —
copy
apps/web/supabase/scripts/build-account-delete-foreign-key-indexes.sh.
Set TARGET_MIGRATION, NAMES, DDL (create index concurrently if not exists ...), SIZE_TABLES, ANALYZE_TABLES, then
source lib/concurrent-index-build.sh; concurrent_index_build_main "$@".
The engine owns dry-run-by-default, the env→session-pooler map, typed remote
confirmation, invalid-index scan, drop+rebuild of INVALID indexes, progress
polling, verify, and analyze.
- Remote envs must use the session pooler (5432) — the transaction pooler
(6543) cannot run
CONCURRENTLY; the direct db.<ref> host is IPv6-only.
- Run
--env <env> --apply on every deployed environment (dev, tux,
prod) before the migration lands there. Local is not a deployed env: a
fresh pnpm supabase:web:reset is reconciled by the migration's own
create index if not exists.
- Migration reconciles and asserts. Plain
create index if not exists —
a fast no-op after the out-of-band build, and still correct for fresh local
and CI databases. Around it:
- Preflight per target index:
- absent → allowed only if the table is small; raise if
pg_table_size(...) > 256 * 1024 * 1024, because a blocking build there
would freeze writes. Name the builder script and --env <env> --apply.
- present but
INVALID (interrupted CONCURRENTLY build) → raise at
any table size. create index if not exists sees the name, skips
forever, and the index is never used. Only a drop + rebuild fixes it.
- Postcondition: every target index exists and
indisvalid. This catches
an index dropped or invalidated between the preflight and the DDL, and any
target the reconciliation skipped. Report the offending names.
Reference implementation of both halves:
apps/web/supabase/migrations/20260814005700_index-account-delete-foreign-keys.sql
plus its builder script.
Destructive DDL
drop table / drop column on anything that holds production state must
assert empty state and stop if cleanup is needed — never discard rows as a
side effect of a schema change.
Scope: this applies to state-bearing objects only. Dropping a stateless
object — function, policy, trigger, view — is ordinary schema work and needs no
empty-state guard. For drop column, require either an assertion that no
non-null values remain, or explicit reviewed evidence in the PR that
discarding that data is intentional.
begin;
-- Strongest lock: taken before counting, so nothing can be inserted between
-- the count and the DROP.
lock table public.job_webhook_secrets in access exclusive mode;
do $$
declare
v_rows bigint;
v_secrets bigint;
begin
select count(*) into v_rows from public.job_webhook_secrets;
select count(*) into v_secrets
from vault.secrets
where name like 'job!_webhook!_%' escape '!';
if v_rows > 0 or v_secrets > 0 then
raise exception
'Cannot remove per-job webhook secrets: % row(s) remain in public.job_webhook_secrets and % Vault secret(s) named job_webhook_%% remain. Resolve them with a reviewed, audited cleanup before retrying',
v_rows, v_secrets
using errcode = '23514';
end if;
end;
$$;
drop table public.job_webhook_secrets;
commit;
Checklist:
- Count the rows and every satellite resource the rows own (Vault secrets,
storage objects, queue jobs). A dropped row that owned a Vault secret orphans
that secret permanently —
on delete cascade does not reach vault.secrets.
- Escape
_/% in like patterns (like 'job!_webhook!_%' escape '!') —
unescaped wildcards silently widen or narrow the match.
- Double
%% in raise format strings when the literal % is part of the
message.
- Cleanup lives in a script (see
cleanup-orphaned-connection-secrets.ts,
revoke-orphaned-context-connection-secrets.ts), not in the migration.
Editing a migration that already shipped
Default: write a follow-up migration. Editing or renaming a shipped file is
an emergency exception, permitted only when both hold:
- Environment migration state is proven — you have confirmed, per
environment, whether the file was already applied. Supabase Dashboard →
Database → Migrations for dev/prod; the
migrate-tux job log for tux.
"Probably not applied yet" is not proof.
- Rollout and reconciliation are explicit — the PR states, per
environment, what will happen on the next apply and what an operator must
run to converge already-applied environments.
| Env |
How schema arrives |
Visible in GitHub Actions? |
Effect of editing a shipped file |
| local |
pnpm supabase:web:reset |
n/a |
Free; reset re-applies from scratch |
| dev |
Supabase Branching on dev |
No |
Already applied (async, ungated by CI). Edited SQL does not re-run; a rename re-runs the whole file and fails on any non-idempotent statement |
| tux |
supabase db push in the migrate-tux CI job |
Yes (gates deploy-tux) |
Pushes anything not yet applied; a renamed file with new SQL executes as a fresh migration |
| prod |
Supabase Branching on main |
No |
Same as dev |
Consequences to reason about explicitly:
- A rename is a new migration to Branching: every statement in the file must
be idempotent (
if not exists, create or replace, drop ... if exists +
recreate) or dev/prod will fail on a duplicate-object error. This is a
different question from "is the SQL correct".
- Editing SQL in place converges nothing on dev/prod — the old version stays
applied. Any divergence must be closed by a follow-up migration or a script.
- A guard added to an already-applied migration is still worth adding: it
protects the environments that have not applied it yet and every fresh
database (local, CI, new branch). Say so in the PR, and say what closes the
gap on the environments that already ran the unguarded version.
- Renaming for the ordering check (
.github/workflows/101-pr-policy.yml, which
runs apps/web/supabase/scripts/check-migration-order.ts)
uses date -u +%Y%m%d%H%M%S — never bare date. See
.agents/skills/hamster-database/references/rules/migration-workflow.md.
Required PR / runbook evidence
A migration PR is not reviewable without these. Missing evidence is a blocker.
- Rollout order, explicitly ordered, per environment: which script runs
before the migration, which after, and which are no-ops. Repeat it as a
comment header in the migration file itself (
-- ROLLOUT: ...), because the
operator debugging a failed Branching apply reads the SQL, not the PR.
- Preconditions asserted — list them, and for each, the script that fixes a
violation.
- Postconditions asserted — what proves the migration achieved its intent.
- Lock and transaction posture — which lock mode, why that mode, and
confirmation of explicit
begin;/commit; if a lock or atomic conversion is
involved.
- Script evidence — dry-run output per environment for every script in the
rollout, and a note that the script's postcheck uses the migration's
predicate.
- Fresh-database proof —
pnpm supabase:web:reset succeeds (guards must not
fire on an empty database), plus
pnpm supabase:web:diff:drift-check after editing any schema or
migration file, and regenerated types (pnpm supabase:web:typegen).
- Shipped-file edits — the environment state proof and convergence plan from
the section above.
Pre-PR review: blocker triage
Start from the staged diff:
git diff origin/dev...HEAD --name-only -- 'apps/web/supabase/migrations/**'
git diff origin/dev...HEAD -- 'apps/web/supabase/migrations/**'
Read every added statement against the table below.
| Finding |
Severity |
create index concurrently inside a migration |
Blocker |
| Non-concurrent index build on a large existing table, no size guard |
Blocker |
update/delete, or an insert derived from existing rows, that fails the five-point exception |
Blocker |
drop table/drop column on a state-bearing object with no empty-state assertion (and, for drop column, no reviewed evidence that discarding the data is intended) |
Blocker |
Lock taken, or atomic conversion performed, without explicit begin;/commit; |
Blocker |
| Count/assertion taken before the lock (racy guard) |
Blocker |
| Edit or rename of a shipped migration without proven env state + convergence plan |
Blocker |
add column if not exists covering a local-only iteration |
Blocker (fold into the original DDL) |
Assertion that raises without naming the remediation script, or without errcode |
Should-fix |
| Missing postcondition assertion on a conversion or index reconciliation |
Should-fix |
| Rollout order absent from the migration's comment header |
Should-fix |
Key paths
| Path |
Purpose |
apps/web/supabase/migrations/ |
Migrations (this skill's scope) |
apps/web/supabase/scripts/lib/concurrent-index-build.sh |
Shared CONCURRENTLY build engine + flag contract |
apps/web/supabase/scripts/build-account-delete-foreign-key-indexes.sh |
Builder script to copy |
apps/web/supabase/scripts/repair-invalid-blueprint-parent-edges.ts |
Audited pre-migration repair to copy |
apps/web/supabase/scripts/check-migration-order.ts |
Local ordering check |
.github/workflows/101-pr-policy.yml |
CI gate that runs the ordering check |
1---2name: hamster-migration-rollout-safety3description: "Rollout safety rules for every change under apps/web/supabase/migrations/**. Use whenever you write, review, rename, or recover a Supabase migration — especially in pre-PR review or when a deploy broke an environment. Migrations install schema and fail closed; production data repair, backfill, reconciliation, and CREATE INDEX CONCURRENTLY belong in explicit audited scripts run before/after deployment. Trigger even if the user only says 'add a column', 'fix these legacy rows in the migration', 'add an index', 'drop this old table', or 'just edit the migration that already shipped' — those are exactly the cases that corrupt data or wedge a deploy."4version: "1.0.0"5metadata:6 source: https://skillmd.com/skills/gethamster/hamster-migration-rollout-safety7---89# Migration Rollout Safety1011## Why this skill exists1213A migration is not a script. It runs unattended, on every environment, at a time14you do not choose, against data you cannot inspect first. It has no dry run, no15per-row audit log, no immutable reviewed plan of the rows it will touch, and no16automatic rollback or recovery once it has applied successfully — everything a17repair script gives you by construction. (A migration that raises inside an18explicit transaction *does* roll back — that is exactly why guards belong there;19what you cannot undo is a migration that succeeded on data it should have20refused.) On dev and prod it runs through Supabase Branching — *outside* GitHub21Actions, before the new app code is serving — so a failure is invisible in CI22and a silent success on wrong data is worse.2324Repeated failure modes this skill blocks:2526- An `update ... where <legacy condition>` buried in a migration, invented to27 "fix legacy rows" — unbounded, and applied unattended the moment the PR28 merges, without anyone inspecting the rows it affects or recording what it29 changed.30- `alter table ... add column if not exists` added to paper over a *local*31 iteration — hiding the fact that the schema contract it depends on was never32 merged, or was rejected.33- `create index concurrently` inside a migration — index builds on real data34 need an operator-controlled statement timeout, progress reporting, retry, and35 `INVALID`-index recovery, none of which a migration can offer.36- A blocking `create index` on a large existing table — it holds a write lock37 for the whole build and can hit the runner's timeout. Separately, an38 interrupted `CONCURRENTLY` build (from the out-of-band script) can leave an39 `INVALID` index behind that `create index if not exists` will never repair.40- `drop table` / `drop column` on a state-bearing object that still has live41 rows or still-referenced Vault secrets — data and secrets orphaned forever.42- Editing or renaming a migration that already shipped, without knowing which43 environments applied it.4445## Scope4647Applies to **any** change under `apps/web/supabase/migrations/**` — new file,48edit to an unshipped file, emergency edit to a shipped file, or a rename.4950Complements, does not replace:5152| Need | Skill |53| --- | --- |54| Schema/RLS/function conventions, diff workflow, env delivery matrix | `.agents/skills/hamster-database/SKILL.md` |55| Writing the audited repair/backfill script this skill sends you to | `.agents/skills/backfill-script-creator/SKILL.md` |56| Concurrent index builder scripts | `apps/web/supabase/scripts/lib/concurrent-index-build.sh` (header is the contract) |57| Context-graph perf validation for index/RPC changes | `.agents/skills/hamster-context-graph-perf/SKILL.md` |5859## The core rule6061> **Migrations install schema and fail closed. Production data repair,62> backfill, and reconciliation run in explicit, audited scripts — before or63> after the deploy, never inside it.**6465A migration may *read* data to assert its own preconditions and postconditions.66It may not *fix* data it finds wrong. When the data is wrong, the migration67raises and names the script the operator must run.6869## Decision table: migration or script?7071| What you need to do | Where it goes |72| --- | --- |73| Create/alter table, column, constraint, enum, policy, trigger, function, view | Migration |74| Create a small/new-table index | Migration (`create index if not exists`) |75| Create an index on a large existing table | `apps/web/supabase/scripts/build-*-index*.sh` (CONCURRENTLY, out of band) **before** the migration; the migration reconciles + asserts |76| Repair legacy/invalid rows so new schema invariants hold | `apps/web/supabase/scripts/repair-*.ts` **before** the migration; the migration only asserts |77| Populate a new column for existing rows (unbounded, or independent of the DDL) | `apps/web/supabase/scripts/backfill-*.ts` **after** the migration |78| Dedup rows before adding a unique constraint | `repair-*`/`dedupe-*` script before; migration asserts zero duplicates, then adds the constraint |79| Delete/cleanup obsolete rows or Vault secrets | Audited cleanup script (e.g. `cleanup-orphaned-connection-secrets.ts`); migration asserts empty and refuses otherwise |80| Convert an existing column into its replacement, where the new schema is unusable without it and the conversion is bounded + deterministic | Migration, under the narrow exception below |81| Reset/patch a specific tenant's data | Script, never a migration |82| Insert fixed-literal seed / lookup rows the schema or product defines (idempotent, no dependency on existing rows) | Migration |8384Rule of thumb: **if the statement reads or depends on production history, it85belongs in a script.** A statement whose row set is fixed literals, or derives86only from the DDL in the same file, may stay — the latter under the exception87below.8889## Forbidden in migrations (pre-PR blockers)9091```sql92-- ❌ Open-ended production repair: unbounded, no reviewed plan of the affected93-- rows, no record of what changed, applied unattended on merge.94update public.account_deletion_requests95set lease_expires_at = coalesce(started_at, updated_at, now()) + interval '6 hours'96where status = 'running' and lease_expires_at is null;9798-- ❌ Re-homing / reparenting / re-keying legacy rows by heuristic.99update public.document d set parent_document_id = <guessed root> where <edge is invalid>;100101-- ❌ Deleting "bad" rows so a constraint can be added.102delete from public.sync_entity_mappings where <duplicate predicate>;103104-- ❌ Defensive DDL papering over a local revision: it hides that the schema105-- contract it depends on was never merged (or was rejected).106alter table public.account_deletion_requests107 add column if not exists phase integer not null default 0;108109-- ❌ CREATE INDEX CONCURRENTLY: needs a dedicated session, an operator-set110-- statement timeout, progress polling and INVALID-index recovery, so it belongs111-- in a builder script — and it can never sit inside begin/commit.112create index concurrently idx_foo on public.big_table (account_id);113114-- ❌ Blocking build on a large existing table: holds a write lock for the whole115-- build and can hit the runner's ~2 min cap, aborting the deploy.116create index idx_foo on public.big_table (account_id);117118-- ❌ Unguarded destructive DDL on a state-bearing table that may hold live rows.119drop table public.job_webhook_secrets;120```121122**What is not forbidden.** Inserting fixed-literal seed or lookup rows that the123schema or product defines is ordinary migration work, provided it is idempotent124(`on conflict do nothing` / `where not exists`) and does not read existing rows.125The blocker targets `update`/`delete` and any `insert` **derived from** existing126rows — that is repair, not seeding.127128Note: `create index concurrently` is described as "safe for large tables" in129generic Postgres advice and in older notes. **In this repo it is a blocker130inside a migration** — not because concurrency is wrong, but because a real-data131index build needs an operator in the loop: a raised statement timeout, progress132reporting, retry, and drop-and-rebuild recovery for an `INVALID` index, all of133which the builder script provides and a migration cannot. On top of that, any134apply path that wraps the file in a transaction rejects `CONCURRENTLY` outright.135Use the builder-script path.136137Replace each with: an assertion in the migration + a named script in the rollout138notes. Assertion template:139140```sql141do $$142declare143 v_invalid bigint;144begin145 select count(*) into v_invalid146 from public.document d147 left join public.document p on p.id = d.parent_document_id148 where d.document_type = 'blueprint'149 and d.parent_document_id is not null150 and (p.id is null or p.document_type <> 'blueprint'151 or p.account_id is distinct from d.account_id);152153 if v_invalid > 0 then154 raise exception155 'Cannot separate Blueprint folders: % Blueprint(s) have an invalid parent edge, which the new hierarchy reads would silently adopt into another bucket. Run supabase/scripts/repair-invalid-blueprint-parent-edges.ts --env <env> --execute before retrying',156 v_invalid157 using errcode = '23514';158 end if;159end;160$$;161```162163Assertion quality bar — a raise that does not tell the operator what to run is164half a guard:1651661. States the invariant that is now being trusted, and why violating it is bad.1672. Reports the offending counts.1683. Names the exact script + flags to fix it.1694. Sets `using errcode = '23514'` (check violation) so it is distinguishable170 from an incidental SQL error.1715. Uses the **same predicate** the repair script postchecks, so "script is172 done" means exactly "migration will now pass".173174## Narrow exception: schema-coupled conversion175176A data-touching statement may stay in the migration **only if every one of177these holds**. If you cannot state all five in the migration's comment header,178it goes in a script.1791801. **Schema-coupled** — the new schema is unusable without it (e.g. a marker181 column the new reads depend on), and it must be atomic with the DDL: a182 window where the DDL has landed and the value has not is a broken product.1832. **Bounded and derived from the DDL, not from history** — writes at most one184 row per pre-existing logical entity (e.g. one workspace-root marker per185 account), not "all rows matching a legacy condition".1863. **Deterministic** — the source of the value is unambiguous and enforced. If187 ambiguity is possible (two candidate roots), the migration must *refuse*, not188 pick.1894. **Transaction-wrapped, with the lock taken before the check** — see below.1905. **Guarded by read-only pre- and post-assertions** — a preflight proving the191 premise still holds *inside* the transaction, and a postcondition proving the192 conversion actually produced the intended state.193194Worked example: `20260814101500_blueprint-workspace-root-marker.sql`. It adds195`is_workspace_root`, derives it from the formerly-unique parentless root, and196then drops the index enforcing that uniqueness. Its preflight, inside the197transaction and behind the lock, proves (a) the old unique index still exists198*and is valid*, and (b) every account owning Blueprints has exactly one199parentless one — raising with the seeding script name when it does not. Whatever200an operator queried before deploying is irrelevant; only a check inside the201transaction proves the premise still holds at apply time.202203Missing-vs-duplicate is worth reporting separately: they need different204remediation (seed one, or reparent extras).205206## Explicit transactions and lock scope207208**Apply paths differ, so never rely on implicit atomicity.** A local209`supabase migration up` may execute a migration's statements without wrapping210them, while remote `db push` / Branching may wrap the file in a transaction. You211cannot depend on either. Any migration that takes a lock, or performs a212multi-statement conversion that must be all-or-nothing, MUST open with `begin;`213and end with `commit;` so its atomicity is a property of the file rather than of214the runner that happens to apply it.215216```sql217begin;218219-- Hold the writer lock for the whole transaction so no concurrent write can220-- slip in between the check and the DDL. Reads are unaffected.221lock table public.document in share row exclusive mode;222223-- preflight assertions (inside the transaction, behind the lock)224-- DDL225-- bounded conversion226-- postcondition assertion227228commit;229```230231Lock rules:232233- **Take the lock before you count.** A count outside the lock proves nothing:234 a service-role insert between the count and the `drop`/`alter` reintroduces235 exactly the state you refused to run on.236- **Pick the weakest lock that closes the race.** `share row exclusive` blocks237 writers but not readers — right for a conversion. `access exclusive` (blocks238 everything, including reads) is only for a `drop`-class change, e.g. proving239 `public.job_webhook_secrets` is empty before dropping it.240- **Keep the locked section short.** No unbounded scans, no per-row loops, no241 `analyze` inside it.242- `create index concurrently` cannot run inside a transaction block, so it can243 never be added to a transaction-wrapped migration — and it needs244 operator-controlled timeout, progress and `INVALID`-index recovery anyway, so245 it moves out of band to a builder script (see below).246247## Concurrent index workflow248249Never `create index concurrently` in a migration. Never blocking-build on a250large existing table. Instead:2512521. **Operator script**, declaring targets and delegating to the shared engine —253 copy `apps/web/supabase/scripts/build-account-delete-foreign-key-indexes.sh`.254 Set `TARGET_MIGRATION`, `NAMES`, `DDL` (`create index concurrently if not255 exists ...`), `SIZE_TABLES`, `ANALYZE_TABLES`, then256 `source lib/concurrent-index-build.sh; concurrent_index_build_main "$@"`.257 The engine owns dry-run-by-default, the env→session-pooler map, typed remote258 confirmation, invalid-index scan, drop+rebuild of `INVALID` indexes, progress259 polling, verify, and `analyze`.260 - Remote envs must use the **session pooler (5432)** — the transaction pooler261 (6543) cannot run `CONCURRENTLY`; the direct `db.<ref>` host is IPv6-only.262 - Run `--env <env> --apply` on every **deployed** environment (dev, tux,263 prod) *before* the migration lands there. Local is not a deployed env: a264 fresh `pnpm supabase:web:reset` is reconciled by the migration's own265 `create index if not exists`.2662. **Migration reconciles and asserts.** Plain `create index if not exists` —267 a fast no-op after the out-of-band build, and still correct for fresh local268 and CI databases. Around it:269 - **Preflight per target index:**270 - *absent* → allowed only if the table is small; raise if271 `pg_table_size(...) > 256 * 1024 * 1024`, because a blocking build there272 would freeze writes. Name the builder script and `--env <env> --apply`.273 - *present but `INVALID`* (interrupted `CONCURRENTLY` build) → raise **at274 any table size**. `create index if not exists` sees the name, skips275 forever, and the index is never used. Only a drop + rebuild fixes it.276 - **Postcondition:** every target index exists and `indisvalid`. This catches277 an index dropped or invalidated between the preflight and the DDL, and any278 target the reconciliation skipped. Report the offending names.279280Reference implementation of both halves:281`apps/web/supabase/migrations/20260814005700_index-account-delete-foreign-keys.sql`282plus its builder script.283284## Destructive DDL285286`drop table` / `drop column` on anything that holds production state must287**assert empty state and stop if cleanup is needed** — never discard rows as a288side effect of a schema change.289290**Scope:** this applies to *state-bearing* objects only. Dropping a stateless291object — function, policy, trigger, view — is ordinary schema work and needs no292empty-state guard. For `drop column`, require either an assertion that no293non-null values remain, **or** explicit reviewed evidence in the PR that294discarding that data is intentional.295296```sql297begin;298299-- Strongest lock: taken before counting, so nothing can be inserted between300-- the count and the DROP.301lock table public.job_webhook_secrets in access exclusive mode;302303do $$304declare305 v_rows bigint;306 v_secrets bigint;307begin308 select count(*) into v_rows from public.job_webhook_secrets;309310 select count(*) into v_secrets311 from vault.secrets312 where name like 'job!_webhook!_%' escape '!';313314 if v_rows > 0 or v_secrets > 0 then315 raise exception316 'Cannot remove per-job webhook secrets: % row(s) remain in public.job_webhook_secrets and % Vault secret(s) named job_webhook_%% remain. Resolve them with a reviewed, audited cleanup before retrying',317 v_rows, v_secrets318 using errcode = '23514';319 end if;320end;321$$;322323drop table public.job_webhook_secrets;324325commit;326```327328Checklist:329330- Count the rows **and** every satellite resource the rows own (Vault secrets,331 storage objects, queue jobs). A dropped row that owned a Vault secret orphans332 that secret permanently — `on delete cascade` does not reach `vault.secrets`.333- Escape `_`/`%` in `like` patterns (`like 'job!_webhook!_%' escape '!'`) —334 unescaped wildcards silently widen or narrow the match.335- Double `%%` in `raise` format strings when the literal `%` is part of the336 message.337- Cleanup lives in a script (see `cleanup-orphaned-connection-secrets.ts`,338 `revoke-orphaned-context-connection-secrets.ts`), not in the migration.339340## Editing a migration that already shipped341342**Default: write a follow-up migration.** Editing or renaming a shipped file is343an emergency exception, permitted only when both hold:3443451. **Environment migration state is proven** — you have confirmed, per346 environment, whether the file was already applied. Supabase Dashboard →347 Database → Migrations for dev/prod; the `migrate-tux` job log for tux.348 "Probably not applied yet" is not proof.3492. **Rollout and reconciliation are explicit** — the PR states, per350 environment, what will happen on the next apply and what an operator must351 run to converge already-applied environments.352353| Env | How schema arrives | Visible in GitHub Actions? | Effect of editing a shipped file |354| --- | --- | --- | --- |355| local | `pnpm supabase:web:reset` | n/a | Free; reset re-applies from scratch |356| dev | Supabase Branching on `dev` | No | Already applied (async, ungated by CI). Edited SQL does **not** re-run; a **rename** re-runs the whole file and fails on any non-idempotent statement |357| tux | `supabase db push` in the `migrate-tux` CI job | Yes (gates `deploy-tux`) | Pushes anything not yet applied; a renamed file with new SQL executes as a fresh migration |358| prod | Supabase Branching on `main` | No | Same as dev |359360Consequences to reason about explicitly:361362- A rename is a **new** migration to Branching: every statement in the file must363 be idempotent (`if not exists`, `create or replace`, `drop ... if exists` +364 recreate) or dev/prod will fail on a duplicate-object error. This is a365 different question from "is the SQL correct".366- Editing SQL in place converges nothing on dev/prod — the old version stays367 applied. Any divergence must be closed by a follow-up migration or a script.368- A guard added to an already-applied migration is still worth adding: it369 protects the environments that have not applied it yet and every fresh370 database (local, CI, new branch). Say so in the PR, and say what closes the371 gap on the environments that already ran the unguarded version.372- Renaming for the ordering check (`.github/workflows/101-pr-policy.yml`, which373 runs `apps/web/supabase/scripts/check-migration-order.ts`)374 uses `date -u +%Y%m%d%H%M%S` — never bare `date`. See375 `.agents/skills/hamster-database/references/rules/migration-workflow.md`.376377## Required PR / runbook evidence378379A migration PR is not reviewable without these. Missing evidence is a blocker.380381- **Rollout order**, explicitly ordered, per environment: which script runs382 before the migration, which after, and which are no-ops. Repeat it as a383 comment header in the migration file itself (`-- ROLLOUT: ...`), because the384 operator debugging a failed Branching apply reads the SQL, not the PR.385- **Preconditions asserted** — list them, and for each, the script that fixes a386 violation.387- **Postconditions asserted** — what proves the migration achieved its intent.388- **Lock and transaction posture** — which lock mode, why that mode, and389 confirmation of explicit `begin;`/`commit;` if a lock or atomic conversion is390 involved.391- **Script evidence** — dry-run output per environment for every script in the392 rollout, and a note that the script's postcheck uses the migration's393 predicate.394- **Fresh-database proof** — `pnpm supabase:web:reset` succeeds (guards must not395 fire on an empty database), plus396 `pnpm supabase:web:diff:drift-check` after editing any schema or397 migration file, and regenerated types (`pnpm supabase:web:typegen`).398- **Shipped-file edits** — the environment state proof and convergence plan from399 the section above.400401## Pre-PR review: blocker triage402403Start from the staged diff:404405```bash406git diff origin/dev...HEAD --name-only -- 'apps/web/supabase/migrations/**'407git diff origin/dev...HEAD -- 'apps/web/supabase/migrations/**'408```409410Read every added statement against the table below.411412| Finding | Severity |413| --- | --- |414| `create index concurrently` inside a migration | **Blocker** |415| Non-concurrent index build on a large existing table, no size guard | **Blocker** |416| `update`/`delete`, or an `insert` derived from existing rows, that fails the five-point exception | **Blocker** |417| `drop table`/`drop column` on a state-bearing object with no empty-state assertion (and, for `drop column`, no reviewed evidence that discarding the data is intended) | **Blocker** |418| Lock taken, or atomic conversion performed, without explicit `begin;`/`commit;` | **Blocker** |419| Count/assertion taken before the lock (racy guard) | **Blocker** |420| Edit or rename of a shipped migration without proven env state + convergence plan | **Blocker** |421| `add column if not exists` covering a local-only iteration | **Blocker** (fold into the original DDL) |422| Assertion that raises without naming the remediation script, or without `errcode` | Should-fix |423| Missing postcondition assertion on a conversion or index reconciliation | Should-fix |424| Rollout order absent from the migration's comment header | Should-fix |425426## Key paths427428| Path | Purpose |429| --- | --- |430| `apps/web/supabase/migrations/` | Migrations (this skill's scope) |431| `apps/web/supabase/scripts/lib/concurrent-index-build.sh` | Shared CONCURRENTLY build engine + flag contract |432| `apps/web/supabase/scripts/build-account-delete-foreign-key-indexes.sh` | Builder script to copy |433| `apps/web/supabase/scripts/repair-invalid-blueprint-parent-edges.ts` | Audited pre-migration repair to copy |434| `apps/web/supabase/scripts/check-migration-order.ts` | Local ordering check |435| `.github/workflows/101-pr-policy.yml` | CI gate that runs the ordering check |