Copy production Supabase data (public schema + auth logins) into a preview branch.
Two ways to run it — a web UI and a CLI script. Both live in this folder and read .env.
⚠️ Local tool only. It holds production credentials + an account-wide Supabase access token and shells out topg_dump/psql. The server binds127.0.0.1. Never deploy it or expose the port..envand*.sqlare gitignored.
pg_dumpproduction: all ofpublic, plusauth.users+auth.identities(so you can log in with real prod accounts).- Restores into the target branch inside one transaction with
session_replication_role = replica— this disables the app's insert triggers (notify / recompute_total / push-dispatch) and FK checks during load, after truncating the branch's existing rows so nothing collides. - Verifies: prints prod-vs-branch row counts for key tables + an orphan-profiles check.
A branch is usually schema-ahead of prod: its PR migration may add a NOT NULL column
(without a default) to a table prod already has. The prod dump then COPYs without that
column, and replica mode also disables any trigger that would resolve the NULL — so the
load used to abort (first hit: 0112's price_lists.currency). Now, before the load, the
tool diffs information_schema.columns between prod and the branch and, inside the same
restore transaction:
DROP NOT NULLon each branch-only strict column (logged per column);- loads the dump;
- runs
post-load.sql(this folder, optional) — idempotent backfills for exactly those columns; keep every entry guarded oninformation_schemaand scoped towhere <col> is null; - re-adds
NOT NULLper column. A column that still holdsNULLs is left nullable on the branch with aWARNINGtelling you to add a backfill topost-load.sql— the sync still completes.
Branch-new tables (restored empty) and identity/generated columns are skipped — they don't need relaxing. Once the PR merges and prod has the column, the drift disappears and the shim is a no-op.
A migration that drops or renames a column creates the mirror-image problem, and it fails
harder and earlier. pg_dump --data-only writes an explicit column list, so
COPY public.t (id, gone, …) is rejected on the statement, before a single row is read —
an empty table fails exactly as loudly as a full one. (First hit: scandi-oms 0138, which
replaced conversation_proposals.kind with operation and killed a sync on a table holding
zero rows in prod.)
So each prod-only column is grafted back onto the branch for the load — nullable and bare, with no constraints and no default — then dropped again. Nullable and bare matters: the branch dropped that column deliberately, and re-adding prod's constraints could fail against branch data or, worse, succeed and leave the branch schema quietly wrong.
The order is what makes a rename recoverable rather than merely survivable:
- graft the prod-only columns back;
- load the dump;
- run
post-load.sql— the old and the new column are both visible here and nowhere else, which is exactly what a rename's backfill needs; - drop the grafted columns again, after a
WARNINGif one actually carried rows — fired on evidence rather than on the mere existence of drift, so it doesn't cry wolf on empty tables.
If you see that warning, prod had data in a column this branch no longer has and nothing
preserved it: add an idempotent backfill to post-load.sql (the 0138 entry is the worked
example) and re-run.
Not copied: Storage bucket file bytes (only DB rows) → bucket images 404 on the branch. Branches are ephemeral — re-run after any branch reset (new push / rebase / migration).
- Node 18+ (tested on v22) — for the web UI.
- PostgreSQL client tools (
pg_dump,psql) onPATH. If they aren't, setPG_BINin.envto the Postgresbindir (e.g.C:\Program Files\PostgreSQL\18\bin). bash(Git Bash) — only for the CLI fallback script.
PROD_DB_URL— prod session-mode pooler string (port 5432). Used for prod creds, the pooler host, and the prod project ref.SUPABASE_ACCESS_TOKEN— create at https://supabase.com/dashboard/account/tokens. The web UI uses it to list branches and fetch each branch's DB password automatically.- optional
PG_BIN,PORT(default 8787),SUPABASE_PROJECT_REF(only if it can't be derived fromPROD_DB_URL).
node server.mjs
# → http://127.0.0.1:8787Pick a branch from the dropdown, click Copy production → branch, confirm the wipe, and watch the live log + verification table. The production (default) branch is blocked as a target.
# set BRANCH_DB_URL in .env first, then:
bash copy-prod-to-branch.sh| File | Purpose |
|---|---|
server.mjs |
Zero-dependency Node web server (UI + Management API + dump/restore). |
public/index.html |
The single-page UI (Tailwind via CDN). |
copy-prod-to-branch.sh |
CLI fallback; same restore logic, manual branch string. |
post-load.sql |
Optional idempotent backfills for drifted columns in either direction (see shim above). |
.env |
Secrets (gitignored — post-load.sql is the one un-ignored .sql). |