Backups and recovery

Backup strategy

Two independent layers protect the database: Neon point-in-time history for fast recoveries, and an automated nightly external pg_dump in a private Cloudflare R2 bucket for recoveries beyond Neon’s history window.

Layer	Provider	Frequency	Retention
Point-in-time history	Neon (Time Travel)	Continuous	Free tier: 6 hours. Paid tiers: 1-7 days, configured per plan. Verify in the Neon project settings - do not assume 7 days
External `pg_dump`	R2 backup bucket (private)	Nightly (02:00 UTC), plus `workflow_dispatch`	daily 30 days; weekly 12 weeks; longterm (manual) 90 days

A backup that has never been restored is not evidence of recoverability. Restore tests are mandatory before first customer use and on a scheduled cadence after that (see below).

Automated nightly backup

The .github/workflows/backup.yml workflow runs outside the deploy pipeline: scheduled nightly, or on demand with a tier selection. It creates the backup package locally and uploads it to the private R2 backup bucket - never the product-images bucket. Package contents (stored at muze-prod/<tier>/<UTC-timestamp>/):

File	Purpose
`<base>.dump`	`pg_dump --format=custom --no-owner --no-acl` (selectively restorable)
`<base>.dump.sha256`	Integrity checksum so a corrupted/half-uploaded dump is detectable before restore
`<base>.dump.manifest.json`	`capturedAt`, `source`, `format`, `gitCommit`, `migrationState`, `checksumSha256`, `tier`, `restoreTestedAt`

Encryption and access:

R2 encrypts every object at rest at the storage layer (AES-256) by default. The S3 API’s server-side-encryption header (SSE-S3) is not implemented by R2 and is not needed, so uploads do not pass --sse.
The bucket is private; the API token used is least privilege (write to the backup bucket only) and lives in GitHub secrets / the runbook, never in the dump or the repository.
Dumps hold employee, auth and order data - treat them as confidential, and never commit them to Git (working artefacts under backend/scripts/backups/ are gitignored).

Running it

# Scheduled nightly run is automatic once the workflow is on main.
# Manual run (dispatch resolves the workflow against the default branch:
# the file must exist on `main` first):
gh workflow run "Database Backup" --field tier=daily
# Pre-migration / major-release snapshot (90 day retention):
gh workflow run "Database Backup" --field tier=longterm

Manual backups

All backups must use the database’s direct (unpooled) connection (DIRECT_URL). The normal DATABASE_URL uses Neon connection pooling and is for application traffic. pg_dump/pg_restore must also match the server’s PostgreSQL major version (Neon is currently 18; Homebrew installs an older default - use brew install postgresql@18 and add /opt/homebrew/opt/postgresql@18/bin to PATH first, otherwise pg_dump aborts with “server version mismatch”).

./backend/scripts/backups/backup.sh                    # local-only dump
DIRECT_URL=... R2_ACCOUNT_ID_BACKUP=... R2_ACCESS_KEY_ID_BACKUP=... \
R2_SECRET_ACCESS_KEY_BACKUP=... R2_BUCKET_NAME_BACKUP=... \
./backend/scripts/backups/backup.sh                    # dump + R2 upload

Recovery procedures

Accidental data deletion

Inspect the deletion via the Neon dashboard (Time Travel / tables view) and confirm the exact point in time to recover to.
If it is within Neon’s history window: open “Restore” from that point in time on a branch, verify the data, then promote the branch.
If it is beyond the window: restore the latest R2 dump to an isolated branch with restore-test.sh, verify, then point the app at the branch.

Migration failure

Full Neon outage beyond the history window

Suspend the application (Render dashboard) to stop writes.
Pull the latest dump from the R2 backup bucket and restore it into an isolated Neon branch with restore-test.sh.
Verify the branch holds the expected data (tables, row parity, prisma migration state).
Promote the branch and swap the service’s DATABASE_URL (and DIRECT_URL) in Render - choose Save & Deploy, not Save-only.
Resume the application and run smoke tests (login / order / entitlement).

Restore tests

Restore proofs run on an isolated Neon branch/DB - never on production.

gh workflow run "Database Backup" --field tier=longterm          # grab a dump
./backend/scripts/backups/restore-test.sh \
  backend/scripts/backups/muze-prod_20260925T020000Z.dump \
  "$ISOLATED_TARGET_URL" \
  "$SOURCE_URL"            # optional: source for row parity

The script restores the dump, verifies schema-qualified table counts, prisma migration state (against the manifest), and per-table row parity, then marks restoreTestedAt in the R2 manifest. Finish with application-level smoke tests (health, login, order, entitlement) against the restored target, record the duration, then securely restrict/delete the test database.

Restore test	Frequency
First production dump	Before first customer use
Scheduling	Monthly / quarterly

Test history

Date	Dump	Result
2026-09-21	`muze-prod_2026-09-21T075352Z` (daily)	PASS — 33 tables restored, prisma migration parity 33/33, per-table row parity vs production (Employee 34,217; Store 414; Client 50; Order 6; `_prisma_migrations` 34), `restoreTestedAt` stamped `2026-09-21T07:56:48Z`. Target: isolated local PostgreSQL 18 cluster (deleted after the test).

App-level smoke tests (health, login, order, entitlement) against a restored Neon branch remain part of production-launch readiness (Phase 2.10 gate).

Recovery time objectives

Scenario	Expected RTO	Method
Accidental data deletion	Under 30 minutes	Neon point-in-time restore
Migration failure	Under 15 minutes	Fix or redeploy
Database corruption	Under 1 hour	Branch restore from backup
Full Neon outage	Dependent on Neon	Wait for provider recovery

End-to-end data coverage

Surface	Covered by	Notes
Database rows (employees, orders, entitlements, auth)	Nightly `pg_dump` + Neon PITR	Source of truth
Product images	R2 product-images bucket	Enable R2 versioning or a bucket-level backup - `pg_dump` does not cover image files
Environment variables / secrets	Render dashboard + GitHub secrets	Never in a dump; record them separately
Firebase config	Firebase console	Managed separately
Zoho Books	Zoho’s own retention/export policy	MUZE stores only integration references (invoice #, waybill, status)

Overview

System Architecture

Security

Business Engines

Notifications & Reports

Data

Operations

Engineering

Backup strategy

Automated nightly backup

Running it

Manual backups

Recovery procedures

Accidental data deletion

Migration failure

Full Neon outage beyond the history window

Restore tests

Test history

Recovery time objectives

End-to-end data coverage

​Backup strategy

​Automated nightly backup

​Running it

​Manual backups

​Recovery procedures

​Accidental data deletion

​Migration failure

​Full Neon outage beyond the history window

​Restore tests

​Test history

​Recovery time objectives

​End-to-end data coverage

Backup strategy

Automated nightly backup

Running it

Manual backups

Recovery procedures

Accidental data deletion

Migration failure

Full Neon outage beyond the history window

Restore tests

Test history

Recovery time objectives

End-to-end data coverage