Backups (2026-10-24)

What is backed up, how to take a manual copy, where to keep it, how to prove it restores, and how long to keep it. Two things hold all our state: the Postgres database on Railway (every table, including the append-only play ledger and the wallet) and the media volume mounted at /data on the backend service (creative files and game images, under /data/media).

Never run any of this against production from a laptop without the founder's go-ahead, and never on a Friday evening before a campaign weekend.

What Railway already does

Railway takes automatic daily backups of the Postgres service and keeps them for a limited window on paid plans. That protects against a disk failure, not against us deleting the wrong rows or losing the volume. Confirm it is on: Railway project respectful-adaptation, the Postgres service, Backups tab. Note the retention it shows here: (to be filled in after checking).

Volumes are not included in those backups. The media volume needs the manual copy below until a scheduled job exists.

Manual database backup through railway ssh

The database is reachable only from inside Railway's network, so the dump runs inside the backend container and is streamed out.

1. From a machine with the Railway CLI logged in, go to backend/ (it is linked to project respectful-adaptation, service dooh-platform, environment production): cd backend && railway status must show exactly that. If it shows anything else, stop.
2. Check that pg_dump exists in the container: railway ssh -- which pg_dump. If it prints nothing, the image lacks the Postgres client. Install it for this session only: railway ssh -- bash -c 'apt-get update -qq && apt-get install -y -qq postgresql-client' (the change does not survive a redeploy, which is fine).
3. Take the dump and stream it to your machine, compressed: railway ssh -- bash -c 'pg_dump --no-owner --no-privileges --format=custom "$DATABASE_URL" | gzip -c | base64 -w0' | base64 -d > dooh-prod-$(date +%Y%m%d-%H%M).dump.gz The base64 step keeps the binary safe across the SSH text stream. Expect a few MB today.
4. Check it: gunzip -c dooh-prod-*.dump.gz | pg_restore --list | head. You should see the table list (plays, audience_summaries, wallet_users, ...). An empty or error output means the dump failed; do not keep it.
5. Record it in the backup log (date, size, who took it, where it was stored).

If railway ssh cannot stream (older CLI versions), write the dump to /data/backups/ inside the container in step 3 and fetch it in a second step with railway ssh -- cat /data/backups/<file> | .... Delete it from the volume afterwards so the volume does not fill up.

Manual media volume backup

1. Same linked backend/ directory and the same railway status check.
2. Stream a tar of the media directory: railway ssh -- bash -c 'tar -czf - -C /data media' > dooh-media-$(date +%Y%m%d-%H%M).tar.gz
3. Check it: tar -tzf dooh-media-*.tar.gz | wc -l should be close to the number of creatives plus game images (compare with the Approvals board).
4. Record it in the backup log.

Media files are content-addressed in practice (one file per upload, never edited), so an older media backup plus a newer database dump is consistent for everything uploaded before the media backup; anything newer must be re-uploaded by the advertiser.

Where to store backups

• A private object-storage bucket to be created (any S3-compatible provider with a region in India is fine; name it freebee-backups). Private, versioning on, no public access, access key kept only in the founder's password manager.
• Folder layout: db/YYYY/MM/<file> and media/YYYY/MM/<file>.
• Until the bucket exists: an encrypted external drive kept off-site, plus the founder's laptop. Not a shared drive, not email, not a chat upload: the database contains hashed and encrypted phone numbers and advertiser emails.
• The SECRET_KEY of the backend must be backed up separately (password manager). Without it the encrypted phone numbers in a restored database cannot be decrypted and the wallet lookups will not match. Rotating the key requires a migration, not a backup.

Retention rule

• Manual database dumps: keep weekly dumps for 3 months, monthly dumps for 2 years, and a yearly dump for 8 years (the play ledger is the invoice record, and Indian company and tax law expect accounting records to be kept for 8 years; confirm with the accountant).
• Media backups: keep the latest 3 monthly copies; older creatives that no booking references can be dropped with the advertiser's deletion request.
• Delete expired backups on the first working day of each month and note it in the log. A backup that holds an erased wallet number is deleted when it expires; the privacy notice says we keep backups for a limited time and do not restore erased data from them.

Monthly restore drill

Do this once a month, on a laptop or a throwaway Railway environment, never on production. It takes about 30 minutes.

1. Take or download the latest database dump and media tar.
2. Start a local Postgres (Docker: docker run --rm -e POSTGRES_PASSWORD=drill -p 5433:5432 postgres:16).
3. Restore: pg_restore --no-owner --no-privileges -d postgresql://postgres:drill@localhost:5433/postgres <dump>.
4. Untar the media into a temp folder and start the API against both: DATABASE_URL=postgresql://postgres:drill@localhost:5433/postgres MEDIA_STORE_DIR=/tmp/drill/media uvicorn app.main:app --port 8199.
5. Sign in as ops (the production ops password) and check the four things on the checklist below.
6. Write the drill result in the backup log: date, dump date, size, how long the restore took, anything that failed, and the fix.

Checklist for a good drill:

• Row counts for plays, audience_summaries, bookings, creatives, wallet_users match what the dump listed (pg_restore --list plus a SELECT count(*) each).
• One approved creative's file opens from the restored media folder.
• The ops Today page loads and shows the screens with their last check-in times from the dump date.
• A wallet sign-in with a test number (not a real shopper's) still works, proving the SECRET_KEY used matches the data.

The script: backend/scripts/backup.sh

A small helper for the local side and for use inside the container:

• scripts/backup.sh <target-dir> with no DATABASE_URL or a SQLite URL copies var/dev.db and the media_store folder into a timestamped folder under the target. Safe to run on a laptop any time.
• With a Postgres DATABASE_URL set (inside the Railway container) it runs pg_dump into the same layout, plus a tar of MEDIA_STORE_DIR.
• It refuses to run if the target directory does not exist or is not writable, and it never deletes anything.

Set up a scheduled run (Railway cron or a GitHub Action with the Railway CLI) only after the bucket exists; until then the monthly manual run plus Railway's own backups is the rule.