Traverse's PITR and deploy discipline in detail

The complete durability story of traverse, end to end — the parts interviewers drill.

The WAL pipeline (recite as a diagram)

Postgres (postgres:17-alpine, PGDATA subdirectory)
  wal_level=replica · archive_mode=on · archive_timeout=300
        │ archive_command
        ▼
S3 bucket traverse-db-backups-697114252450 (us-east-1)
        + base backups via scripts/pg-backup-sync.sh (host cron, 3-day)
        ▼
Aiven managed PG — offsite copy (AIVEN_BACKUP_DB_URL)

Design decisions embedded here:

1. archive_timeout=300: WAL switches segments on volume or time — without it, a quiet database could hold an unarchived segment for hours, silently destroying your RPO. Five minutes of possible loss, documented.
2. Two buckets, two regions, two credential sets: backups (us-east-1, S3_BACKUP_REGION) vs careers/user files (ap-south-1, AWS_REGION). Rationale: separate blast radius — leaked careers credentials cannot read the database backups; region separation survives a regional incident. Two env var pairs, deliberately not shared.
3. Port binding: Postgres port is published on 127.0.0.1 only — the database is unreachable from the internet on a cloud VM where everything else is container-internal.
4. PITR as admin surface: pitr.service.ts — status, backup listing, snapshot, restore, rollback-safety, history. A restore is executable, not a doc. That's the difference between "I have backups" and "I can recover" — the interview phrasing for the same fact.

The deploy contract (why the container refuses to start)

Dockerfile → start.sh:

redis-server --daemonize yes ...   # embedded redis (known, documented duplication)
npx prisma migrate deploy || exit 1 # REFUSE to serve on a stale schema
exec node dist/index.js

And docker-compose.yml overrides CMD with wait-for-postgres.sh (TCP poll with prefixed PG_WAIT_* vars so a bare PORT can't leak in and move the app's listener) before exec'ing start.sh.

The discipline chain on deploy day (scripts/deploy-remote.sh, run by GitLab CI over SSH):

1. rm docker-compose.yml; git checkout -f; git fetch; git reset --hard FETCH_HEAD — host state is never the source of truth; git is. Deliberately no git stash -u: stashing would delete wal-archive/ from the working tree and break WAL shipping (a real, documented tradeoff!).
2. docker-compose up -d --build — note --build: the compose file bind-mounts src/ but runs compiled dist/ baked into the image; forgetting --build serves old code (a documented trap).
3. Health gate: 20 attempts × 5 s of fetch('http://localhost:3000/api/health/db') — the deploy fails loudly if the DB never comes healthy.
4. /api/version is bumped by a pre-commit hook via src/version.json — every deploy is identifiable at runtime.

Migration safety details: Prisma stores a sha256 per applied migration — editing an applied migration file fails migrate deploy (immutability of history). New migrations apply in one transaction per migration. The laptop footgun is documented: the repo's .env points at production, so all local migration work must use .env.test → localhost:5432/traverse_test.

The narrative paragraph (memorize)

"Durability is layered: WAL archiving to a dedicated S3 bucket in a separate region with separate credentials from user files, base backups synced offsite to Aiven, an RPO of five minutes from archive_timeout, and restore exposed as admin endpoints so recovery is executable. Deploys treat the host as disposable — git reset hard, rebuild, health-gate on /api/health/db — and the container refuses to boot on a failed or stale migration, because a stale schema serving traffic fails in ways monitoring can't see. The one deliberate wart: the compose bind-mounts src but runs baked dist, so 'up -d' without --build serves old code; that's documented in the runbook."