Backup console

A localhost web UI for the two jobs that matter when a Supabase project is at stake: standing up a new empty project from the committed schema, and recovering everything after a project is paused or lost. Jobs run detached, so closing the browser does not stop the work.

Why this existsThe backup scripts already worked from the shell. What they lacked was visibility: a restore of 1772 statements gives no useful feedback in a terminal, and a job that has silently stopped looks identical to one making progress. During development a restore sat frozen at 250/1772 for thirteen minutes before anyone noticed. The console exists mainly to make that state impossible to miss.

Four steps, gated #

Connect → choose a project → read the readiness report → run. Step four does not exist until step three has run. That is the whole design: you cannot act on a project before the console has told you what state it is in.

Changing the project selection collapses steps three and four and discards the report. A stale readiness report sitting next to a different project ref is how someone runs an action against the wrong database.

One primary action, chosen for you

An earlier version offered Fresh setup and Full recovery as two equal buttons. That made the operator repeat the diagnosis the console had just done. The primary button is now derived from the readiness result:

Readiness	Primary action
Checks failing	Set up this project — states how many items it will fix
All 13 passing	Demoted to a subordinate Run setup anyway, which says it has nothing to add

Backup and verify sit below as secondary actions. Full recovery is behind a disclosure, visually separated, and still requires the ref typed exactly.

Setup versus full recovery #

Two very different consequences, so the safe one is the easy one.

	Setup	Full recovery
Purpose	New, empty, working project	Restore a lost or paused project
Needs a backup?	No — applies the committed schema	Yes — replays a tarball
Schema	Yes — all 42 tables and every column	Yes
RLS policies	Yes — 153 database + 16 storage	Yes
Functions / RPCs	Yes — 73 public (80 across all schemas)	Yes
Indexes, FKs, triggers	Yes — 66 / 43 / 15	Yes
Signup trigger	Yes — its own phase, asserted	Yes, if the backup carried it
Storage buckets	Yes — 4, with size caps and MIME rules	Yes
Auth users	No	Yes — with password hashes
Table rows	No	Yes
Storage files	No	Yes
Writes `.env`	Yes — checkbox, on by default	Yes, after verification passes
Unlock step	Confirm only if the project has accounts	Type the target project ref

Setup needs no backup at all, which is the point — restore replays a tarball, and a tarball only exists if you already had a working backend. Someone setting up for the first time had no route through this console until setup became an action in its own right. It writes no database rows: no users, no seed data, no files.

Full recovery writes real user emails and bcrypt password hashes into the target, which is exactly what you want in a disaster and exactly what you do not want by accident.

The readiness report #

Thirteen checks in four groups, read-only, and the same ones ./supabase.sh check runs — the console consumes its --json output, so the two can never disagree. Every check exists because that exact thing silently failed at some point:

Group	Catches
schema	Missing tables, functions, policies, indexes; the six tables signup writes to; ten RPCs the compiled app calls directly.
auth	The signup trigger on `auth.users`, and any auth user with no `public.users` row.
community	Buddy handles populated; the overview returning `buddies`; and the buddy payload shape.
storage	All four buckets, and the object policies that let uploads through.

Failures are listed first within each group, each with the command or migration that fixes it. Status is an icon, a colour and the word Pass or Fail — never colour alone.

Why counts alone are not an answer A project can hold all 42 tables, 80 functions and 153 policies and still be unable to accept a single signup — that state shipped for weeks. The object counts were right; the trigger that seeds a new account was missing. Three of the thirteen checks look for defects that no count would reveal.

What is never included #

Two categories are structurally absent, not filtered out:

Google OAuth keys: The client ID and secret live in Supabase's auth configuration, not in the database. Nothing in the schema dump or the backup tarball touches them. After a restore you must re-enter them in the dashboard, along with the redirect URLs — Google sign-in will not work until you do.
The auth schema: auth.users and friends are owned by GoTrue. They cannot be created from SQL, so the schema file omits them entirely. Full recovery moves users through a separate insert path instead.

Surviving reload and close #

A restore takes minutes. If progress lived in the web server's memory, closing the tab would be survivable but restarting the server would lose the job — and a crashed server would leave an orphaned child still writing to a database. So all state is on disk:

backups/.job.json: Current job: id, kind, pid, target ref, state, timestamps.
backups/.progress.jsonl: Append-only structured events, streamed to the browser.
backups/.job.log: Raw child output, shown in the log pane.

The work happens in a detached child process, never inside an HTTP request. Any server instance reattaches by reading those files, and node scripts/job-runner.mjs status reports honestly with no server running at all.

A recorded pid is not proof of lifeThe process may have been killed, or the device rebooted. The runner probes the pid on every read and reconciles a stale running state to orphaned, so the UI never displays a job that is quietly dead.

Progress, ETA and staleness #

The worker appends machine-readable events. The UI reads those, never the human-readable log — parsing prose produces a UI that breaks whenever a message is reworded.

{"t":1234,"phase":"schema","done":250,"total":1772,"ok":250,"failed":0}
{"t":1235,"phase":"schema","level":"error","msg":"42P01 relation … does not exist",
 "stmt":"ALTER TABLE ONLY \"public\".\"group_members\" ADD CONSTRAINT …"}

Each phase reports done, total, failed and skipped. Two derived numbers matter more than the percentage:

Rate and ETA: Computed over a rolling twelve-sample window, not from job start. Early batches are slower while the connection warms up, so a whole-run average under-reports throughput and gives a pessimistic, drifting ETA.
Last event age: Seconds since the most recent event. This is the field that distinguishes slow from hung, and the reason the console was worth building.

Credentials #

You paste one secret per project: a Supabase personal access token. The console calls the Management API to fetch that project's anon and service_role keys, so those are never typed or stored by hand.

Credentials reach the worker through its environment only.
.job.json records the project ref — never a key.
Nothing is written to .env, and nothing is committed.

The console can also create a project from the token: it posts to the Management API, waits for ACTIVE_HEALTHY, then reads the new keys — so a fresh setup needs no dashboard visit at all.

Binds to 127.0.0.1 onlyThe console handles service-role keys and shows project refs. On 0.0.0.0 anything on the same network could read it, so it listens on loopback exclusively. Do not put it behind a tunnel or reverse proxy.

Safety rules #

Every one of these exists because the failure it prevents actually happened during development.

Restore refuses to infer its target: backup.sh restore once fell through to .backup_env and then .env when --supabase-url was omitted — files that normally hold production credentials. It now errors out rather than guessing.
Target is echoed before work begins: The resolved URL is printed and shown in the UI, with a warning when it matches the project this checkout normally uses.
Full recovery needs the ref typed: Restore writes users and every table into whatever it points at. A single click is the wrong shape for that.
Never probe a live target: Running queries against a project mid-restore creates objects out of order, and every subsequent batch collides and falls back to slow per-statement replay. It looks exactly like a performance problem and is not one.

Why it is fast #

Statements are applied in batches of fifty, in order. Measured against the Management API, a single statement costs roughly 2200 ms while fifty in one request cost about 845 ms, so the schema drops from ~1772 round trips to ~36. Auth users batch similarly, 43 rows into two requests.

On a batch failure the batch is replayed one statement at a time, so a genuine error still reports with its exact statement. That replay is safe only because every emitted statement is idempotent — CREATE … IF NOT EXISTS, guarded ADD CONSTRAINT blocks, DROP POLICY IF EXISTS — so re-running a partially applied batch cannot corrupt anything.

The replay is only useful if failures are classified honestly It was not. does not exist was treated as an idempotent skip alongside already exists — so a statement failing because its dependency had never been created was counted as “already present”. 1,176 statements were discarded and the phase reported 0 failed on a restore that had created 11 of 42 tables. The classifier is now narrow, the schema phase aborts on a real failure, and it cross-checks every manifest table against the target before touching data.

Batches are sequential, not parallelEleven parallel workers would be faster and wrong. The dump is dependency-ordered — tables, then constraints, then functions, then triggers, then policies — so concurrent parts would attempt a foreign key against a table another part had not yet created. Ordering is worth more than the seconds.

Verification #

Every job ends with a verify pass against the target. It checks table existence, per-table row counts, the auth user count, buckets and object counts — and, importantly, code:

PASS routines 80/80 — all present
PASS triggers 15/15 — all present
PASS policies 153/153 — all present
PASS trigger auth.users.on_auth_user_created — present
PASS auth users with a public.users row — 43/43
[verify] RESULT: PASS (96/96 checks passed)

The routine, trigger and policy checks were added after a restore reported 91/91 checks passed while silently missing most RPCs. Verify was counting rows only, so a database with every table and almost no functions looked perfect. A database that cannot run the app must fail verification.

The last two lines were added for a worse version of the same problem. Verification compared the target against what the manifest recorded, so it could only ever find objects the backup knew about. The signup trigger lives on auth.users, which both dump tools exclude, so it was never recorded — and a restore passed 94/94 on a database that could not accept a single signup. Those two checks now run unconditionally, precisely because a backup taken before the fix does not list the trigger at all.

Operations #

# start the console (loopback only)
node scripts/backup-ui.mjs
# → http://127.0.0.1:8000

# check a job without any browser
node scripts/job-runner.mjs status

# stop a running job
node scripts/job-runner.mjs stop

The same operations remain available head-less through ./backup.sh; see Sync & backup for the command-line reference and the tarball layout.

After a restore #

Three steps are outside the database and must be done by hand:

Re-enter the Google OAuth client ID and secret in Supabase Auth providers.
Add the redirect URLs the app expects, including the Android deep link.
Point the app at the new project — SUPABASE_URL, SUPABASE_ANON_KEY, SUPABASE_SERVICE_ROLE_KEY.

A restored project with no OAuth configuration will accept email sign-in and reject Google. That is expected, and preferable to shipping client secrets around in a backup file.