Staging DB Restore Console (Design)

Status: Design — not yet implemented. 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 the Database Restore runbook.

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

• [ ] apimgmt: POST …/mgmt/v1/restore — accept upload, store to RustFS db-backup bucket
• [ ] Restore handler/activity: shell out to bundled restore.sh against finance via resolveAdminUrl()
• [ ] Add postgresql-client to the runtime image (apimgmt, or wfw if Temporal)
• [ ] Staging-only hard guard + typed confirmation + audit record
• [ ] Verify-by-row-counts result surfaced to the UI
• [ ] finance_restored_at timestamp + stale-template warning on tenant-create
• [ ] mgmt console: upload screen + status/poll
• [ ] Backup bucket: private, retention policy

References

• Manual procedure: Database Restore runbook
• Script (all gotchas): fin-infra/railway/scripts/restore.sh
• Provisioning plane: blitz PR #1099, blitz/src/docs/tenant-db-provisioning.md