Staging DB Restore Console

Status: Implemented (MVP) on branches feature/mgmt-db-restore in blitz + blitz-ui + fin-infra — pending review/merge + a staging build/deploy. The design below records the decisions; the Implementation section at the bottom records what was actually built. Goal: Refresh the staging finance database from a backup .bkp via the superadmin (mgmt) console, running the restore inside the Railway network instead of from a laptop.

Why

Staging finance is refreshed regularly from .bkp dumps (produced elsewhere, delivered via Zoho WorkDrive). Restoring from a laptop over Railway's public TCP proxy (shortline.proxy.rlwy.net) is slow (IST → us-east4, ~10–20 min) and drops mid-restore — pg_restore is thousands of small round-trips, and sustained COPYs on large tables get cut by the proxy (observed repeatedly, incl. the 10th and 15th July restores).

apimgmt already runs inside finaisse-stage, so it can reach both the finance Postgres and RustFS over *.railway.internal. Running the restore there makes it a same-region, in-network operation: fast and drop-proof, and it removes the laptop from the loop. See the manual procedure this replaces in Manual / Local Restore below.

Decision summary

Decision	Choice
Where it runs	apimgmt (blitz backend, in-network). Not fin-infra (IaC scripts run outside the network → still hit the proxy).
Entry point	Upload screen in the mgmt console (apps/mgmt), admin-only (Cloudflare Access + x-mgmt-api-key).
Backup storage	Dedicated RustFS "db-backup" bucket (private, access-scoped).
Target	Staging finance — drop + restore from the uploaded dump.
tenant_base refresh	Out of scope — flag only (see below).
Environment	Staging-only, hard guard. Never production.
Execution model	Synchronous MVP (202 + status) acceptable; Temporal as upgrade (see below).

Flow

mgmt console (admin)                apimgmt (in-network)                   Postgres / RustFS
  │  upload finance_<date>.bkp  ──▶   store dump ─────────────────────▶  RustFS db-backup bucket
  │                                   run restore (drop + restore) ───▶  finance  (*.railway.internal)
  │  ◀── status: verified / partial   verify row counts / 10 schemas

Reuse of Mit's provisioning plane (blitz #1099)

PR #1099 (feature/tenant-db-autoprovision) already built the admin DB-ops plane this feature extends. Reuse rather than rebuild:

Need	Already exists (#1099)
Admin (CREATEDB/superuser) connection for DROP/CREATE	resolveAdminUrl() + POSTGRES_ADMIN_URL in libs/common/src/provisioning.ts
Atomic drop that survives reconnect races	DropDatabaseActivity → DROP DATABASE … WITH (FORCE) (systemwf/src/activities/provisioning.ts)
DB-name safety / reserved-name guards	assertProvisionableDbName, reservedDbNames()
Async orchestration pattern (if used)	provisionTenant workflow + systemwf activities

Net-new: an upload endpoint in apimgmt; a RestoreDatabaseActivity/handler that shells out to pg_restore (custom-format dumps can't be replayed via a SQL client the way CREATE DATABASE … TEMPLATE can); the staging guard; and the stale-flag below.

#1099 provisions tenants by cloning a golden template (CREATE DATABASE … TEMPLATE tenant_base) — it does not restore from a dump file. This feature is the missing restore-from-file path, built on the same plane.

The restore logic (must carry these — see fin-infra/railway/scripts/restore.sh)

The hardened restore.sh already encodes every gotcha; bundle it into the runtime image and shell out to it rather than reimplementing:

• uuidv7() shim — dumps come from PG18 and use DEFAULT uuidv7(); Railway staging is PG17, where uuidv7() doesn't exist. Install a pg_catalog.uuidv7() shim when the target server is < 18. (RDS is planned PG16 — same requirement.)
• No --exit-on-error — benign "already exists" collisions must not abort the run.
• DROP DATABASE … WITH (FORCE) — avoids the reconnect race (pgAdmin polling / app pools).
• Verify by real row counts — success = schemas-with-data == schemas the dump should load (e.g. 10/10), not "pg_restore finished". A dropped connection leaves a complete-looking schema with partial data.

Image requirement: the postgresql-client binaries (pg_restore) must be added to whichever image runs the restore (apimgmt if synchronous; the wfw worker if via Temporal). Neither image ships them today.

Execution model: Temporal vs synchronous

With tenant_base refresh out of scope, the operation is effectively single-step (drop + restore finance), which removes the main argument for Temporal (multi-step orchestration).

• Synchronous MVP (recommended first cut): apimgmt receives the file → stores to RustFS → runs the restore → returns 202 + a status the UI polls. In-network restore is ~seconds today, well within HTTP timeouts. Simplest; pg_restore lives in the apimgmt image; RustFS handoff optional (can restore from a temp file).
• Temporal (upgrade path): durable + retriable; needed if the DB grows enough that a synchronous request risks nginx/Cloudflare Access timeouts (~100s). Requires RustFS as the apimgmt → wfw handoff, and pg_restore in the worker image. Reuses #1099's activity plane.

Start synchronous; move to Temporal when duration warrants.

Guardrails (non-negotiable)

This is a destructive "drop + restore the primary application DB" action exposed in a web UI.

• Staging-only hard guard — refuse to run unless the target is the known staging DB / an explicit staging flag is set. Must be impossible to point at production (AWS prod is coming).
• Typed confirmation in the UI (type the DB name / RESTORE).
• Audit record in the mgmt DB — who, when, which file, verified/partial outcome.
• Backup bucket — private, access-scoped, encrypted at rest if available; retention/cleanup policy (dumps are full confidential financial snapshots). apimgmt stays private (no public domain); the mgmt UI front door is gated by Cloudflare Access.

tenant_base staleness — flagged, not rebuilt

tenant_base is the golden template new tenants are cloned from; it is a frozen copy of finance. Restoring finance makes tenant_base stale, so tenants provisioned afterward would clone old schema/data. Rebuilding it is a separate concern, out of scope here.

Instead, flag it: on a successful restore, record finance_restored_at; if that is newer than when tenant_base was last built, show a "template stale — rebuild before provisioning" warning on the tenant-create screen (optionally a block with override). Whoever owns provisioning rebuilds on their own schedule. See blitz/src/docs/tenant-db-provisioning.md for the rebuild procedure.

Out of scope

• Rebuilding tenant_base (flag only, above).
• Production restores (staging-only by design).
• Curating a clean tenant baseline vs. raw restored finance (a provisioning concern).

Reuse for AWS Phase 2

The same in-network, admin-triggered restore is the pattern the Railway → RDS migration needs (restore from inside the VPC, not a laptop over the internet). See AWS Implementation.

Implementation checklist

• [x] apimgmt: POST …/o/mgmt/v1/restore — accept upload; archive to RustFS db-backup bucket (best-effort)
• [x] Restore handler: shell out to bundled restore.sh against finance via resolveAdminUrl()
• [x] Add postgresql-client-18 to the apimgmt image (PGDG repo — see gotcha below)
• [x] Staging-only hard guard (RESTORE_ENABLED) + typed confirmation + audit record (existing [mgmt-audit])
• [x] Verify-by-row-counts result surfaced to the UI (poll GET …/restore/:id)
• [ ] finance_restored_at timestamp + stale-template warning on tenant-create — deferred (flag-only, see above)
• [x] mgmt console: upload screen + status/poll (apps/mgmt → /restore)
• [~] Backup bucket: create the private db-backup bucket once (archive is best-effort; restore works without it)

Implementation (as built)

MVP shipped on feature/mgmt-db-restore across three repos. Synchronous fire-and-poll model (no Temporal): POST returns 202 + job immediately and runs the restore in the background; the UI polls GET …/restore/:id to a terminal status. This dodges the ~100s Cloudflare Access / nginx timeout without a worker.

blitz (apimgmt backend):

• packages/mgmt/mgmta/controllers/mgmt/v1/restore.controller.ts — POST /restore (multipart upload + typed confirm), GET /restore/:id. Mounted under /o/mgmt/v1, so the full path is /api/o/mgmt/v1/restore — already covered by apimgmt's apiKeyGuard (x-mgmt-api-key + [mgmt-audit] on the mutation; operator from X-Operator-Email).
• …/services/mgmt/restore.service.ts — hard guard, one-at-a-time, Bun.spawn of the bundled restore.sh against finance (URL built as buildTenantConnectionString(resolveAdminUrl(), "finance"), RESTORE_ASSUME_YES=1), parses the script's verify lines into verified/partial/failed + row counts, and best-effort-archives the dump to RustFS.
• …/schemas/mgmt/restore.ts — request/job schemas.
• build/Dockerfile.service — new INSTALL_PG_CLIENT build arg; build/restore/restore.sh bundled. docker-compose.yaml sets INSTALL_PG_CLIENT=1 on apimgmt only.

The one non-obvious gotcha — postgresql-client-18 from PGDG. The release image is oven/bun:slim (Debian). Its default postgresql-client is 15/16, which cannot read a PG18 custom-format dump (unsupported version … in file header) — and our dumps come from PG18. So the image adds the PGDG apt repo and installs postgresql-client-18 specifically. This is gated behind INSTALL_PG_CLIENT so every other service builds byte-for-byte unchanged.

blitz-ui (mgmt console): apps/mgmt/src/pages/restore/index.vue (upload + typed-confirm + poll + status card), store actions startRestore/getRestoreJob in stores/mgmt.ts, and a "DB Restore" nav entry in layouts/default.vue. Multipart POST bypasses mgmtFetch (which forces JSON) so the browser sets the multipart boundary.

fin-infra (IaC): backend/infrastructure.ts sets RESTORE_ENABLED=1 + DB_BACKUP_BUCKET on staging apimgmt. RESTORE_ENABLED is the hard guard — never set it on production; the restore endpoint refuses to run without it, so prod can't restore even with the code deployed.

Deploy steps: rebuild blitz-apimgmt (build-push-apimgmt.yml) + blitz-mgmtui (build-push-mgmtui.yml) images → deploy:backend/deploy:frontend → create the private db-backup RustFS bucket once.

Known MVP limits: jobs are in-memory (lost on apimgmt restart — a restore is rare + manual); one restore at a time; tenant_base staleness is not yet flagged (deferred). Temporal upgrade path (durable/retriable) is unchanged from the design above.

Manual / Local Restore

Before the console existed (and still useful for local dev seeding or if the console is unavailable), restore a .bkp dump directly via fin-infra/railway/scripts/restore.sh, which handles every gotcha below automatically.

TL;DR

cd fin-infra/railway/scripts

# Railway staging (drops & replaces the finance DB — prompts for typed confirmation):
./restore.sh /path/to/finance_<date>.bkp \
  --url 'postgresql://postgres:<password>@shortline.proxy.rlwy.net:37585/finance'

# Local dev (defaults: db=finance host=localhost port=5432 user=$USER, password via PGPASSWORD):
./restore.sh /path/to/finance_<date>.bkp

The script drops & recreates the target DB, restores, then verifies by real row counts. Success looks like:

==> Schemas with data: 10 / 10 expected
==> ✅ Restore verified: NNNNN rows across 10 schema(s) in finance on <host>

If it prints PARTIAL restore: X/10 schemas or 0 rows, it failed — re-run (see Troubleshooting).

Before you start

• Get the dump onto local disk first. If it lives in Zoho WorkDrive TrueSync (~/Library/CloudStorage/ZohoWorkDriveTrueSync-Finaisse/...), the file may be a cloud placeholder (metadata only, no bytes — stat -f %b <file> shows blocks=0). Restoring straight from a placeholder stalls mid-read. Copy it to a real local path to force hydration, then verify:

  cp "<truesync-path>/finance_<date>.bkp" /tmp/finance.bkp
  stat -f "blocks=%b" /tmp/finance.bkp          # must be > 0
  pg_restore -l /tmp/finance.bkp | grep -c "TABLE DATA"   # sanity: lists data entries

• Client tools: needs pg_restore / psql (brew install libpq; PG18 client is fine).
• Confirm the target. The script DROPS the target DB. Remote targets require you to type the DB name to confirm (set RESTORE_ASSUME_YES=1 only for automation).

Why this isn't a plain pg_restore (the gotchas)

Gotcha	What the script does
Dumps come from PostgreSQL 18 and use DEFAULT uuidv7() on PK columns. uuidv7() is a PG18-only built-in — Railway staging is PG17, RDS is planned PG16.	Installs a uuidv7() shim into pg_catalog only when the server is < 18. Without it, every CREATE TABLE fails with function uuidv7() does not exist.
pg_restore --exit-on-error aborts on the first benign "already exists" collision, leaving a schema with 0 rows.	Runs without --exit-on-error; collisions are expected and harmless.
"pg_restore finished" is not proof of success — a uuidv7-aborted run leaves a complete-looking schema with no data.	Verifies by real row counts and checks schemas-with-data == schemas the dump should load (catches partial loads).
Railway's public TCP proxy (*.rlwy.net) silently drops a briefly-idle connection mid-restore.	Sets libpq TCP keepalives so the socket survives.

Verify a restore manually

export PGPASSWORD=<password>
PSQL="psql -h shortline.proxy.rlwy.net -p 37585 -U postgres -d finance"

$PSQL -c "SELECT count(*) FILTER (WHERE n_live_tup>0) AS with_rows,
                 count(DISTINCT schemaname) FILTER (WHERE n_live_tup>0) AS schemas_with_data,
                 sum(n_live_tup) AS total_rows
          FROM pg_stat_user_tables;"

Expect 10 schemas with data (agent, cash, closehub, coresystem, finbase, invoice, journal, public, reconciliation, sys). Row estimates are populated by ANALYZE, which the script runs; a fresh manual restore may show 0 until you ANALYZE;.

Troubleshooting

Symptom	Cause / fix
function uuidv7() does not exist	Target is PG<18 and the shim wasn't installed. Confirm the script printed Server major version: 17 (not 1700 — that was a fixed bug) and installing uuidv7() shim.
PARTIAL restore: X/10 schemas	Connection dropped mid-restore (Railway proxy). Keepalives are on by default — just re-run. If it keeps dropping, restore from inside the Railway network (see below).
Restore stalls reading the file	Dump is an un-hydrated TrueSync placeholder — copy it local first (see Before you start).
Hangs on DROP DATABASE	An open session (e.g. pgAdmin tab) holds the DB. The script terminates other sessions, but close stray clients if it blocks.
Restore looks huge (1.5 MB dump → ~30 MB DB)	Normal — the dump is compressed; the live DB adds indexes, FK structures, and page padding.

Notes

• Sizing: a finance dump is ~1.5–1.6 MB; restored DB ~30 MB; ~35k rows (dominated by sys.calendarday). A restore over the proxy takes a few minutes.
• Password rotation: the staging postgres superuser password is long-lived. Rotate it in the Railway dashboard if it has been shared; the new value only needs to go into the --url arg / PGPASSWORD.
• Faster/robust restores (future): for large dumps or the AWS RDS migration, run the restore from inside the target's private network (a runner co-located with Postgres), not from a laptop over the internet. Railway private networking is per-project, so a staging refresh runner must live in finaisse-stage (can reuse its RustFS/S3 to stage the file).

References

• Script (all gotchas): fin-infra/railway/scripts/restore.sh (bundled into apimgmt at build/restore/restore.sh)
• Provisioning plane: blitz PR #1099, blitz/src/docs/tenant-db-provisioning.md