vyndr/docs/BACKUP-RUNBOOK.md

# VYNDR Backup Runbook (security follow-up item 2)

Supabase free tier has **zero** backups (no scheduled, no PITR). `scripts/backup-db.sh`
is the safety net: a nightly full-database `pg_dump`, 14 days kept locally, a
weekly copy pushed off-box, ntfy alert on any failure.

## Where it runs

`SUPABASE_DB_URL` is set in Coolify **on the VYNDR API service** — so the backup
runs **inside that container**, which already has the env, the Supabase network,
and now `pg_dump`/`pg_restore`/`rsync` (added to the Dockerfile). The Hetzner box
can't reach `db.<ref>.supabase.co` directly and doesn't hold the connection
string; the container is the right place.

**`SUPABASE_DB_URL`** must be the **direct** connection string (session mode):
Supabase → Settings → Database → **Connection string → URI**, the
`db.<ref>.supabase.co:5432` one (NOT the `:6543` pooler — `pg_dump` needs a real
session). Already set. Optional: `BACKUP_DIR` (mount a Coolify **persistent
volume** here so dumps survive redeploys — e.g. `/var/backups/vyndr`),
`BACKUP_REMOTE` (off-box rsync target, below).

## ✅ THE CRON NOW SHIPS AS CODE (Session 64) — no install step

**Read this before following the manual instructions below; they are now the
FALLBACK, not the primary path.**

`src/backupScheduler.js` runs the nightly backup **inside the API container**,
armed from `server.js` at boot. The container already holds `SUPABASE_DB_URL`,
`pg_dump` and the Supabase network route, so **deploy == installed**. Nothing to
add to crontab, nothing to click in Coolify.

- **Arming is opt-OUT:** armed whenever `SUPABASE_DB_URL` is set. The S62 design
  was opt-in (a host cron someone had to add) and nobody ever added it — the
  database went unbacked every night for weeks. That failure mode is now
  impossible.
- **Kill switch:** `BACKUP_CRON=0`.
- **Schedule:** `BACKUP_HOUR_UTC` (default 3) / `BACKUP_MINUTE_UTC` (default 10).
- **Failure pages high-priority ntfy.** Silence is the danger with backups.
- Boot log line: `[backupScheduler] armed — nightly 03:10 UTC ...`

### ⚠️ DURABILITY — the one thing still requiring a human

The container filesystem is **ephemeral**: a dump written inside it is LOST on the
next redeploy. The scheduler detects this and pages a warning at boot when
neither is configured. Set ONE of:

1. **`BACKUP_REMOTE`** — off-box rsync target (Hetzner Storage Box, ~€3/mo). Best.
2. **`BACKUP_DIR`** pointed at a **Coolify persistent volume** (e.g. `/var/backups/vyndr`).

Until one is set, backups run but do not survive a deploy. An undurable backup
that reads as "backed up" is worse than a loud gap — hence the boot-time page.

---

## Install the cron (host → docker exec into the API container)

Find the API container name (`docker ps | grep vyndr`), then a host cron:

```bash
# Nightly at 03:10 UTC. BACKUP_REMOTE can also be set in Coolify instead.
sudo crontab -e
# add (replace <api-container>):
10 3 * * * docker exec -e BACKUP_REMOTE='u123456@u123456.your-storagebox.de:vyndr-backups/' <api-container> sh /app/scripts/backup-db.sh >> /var/log/vyndr-backup.log 2>&1
```

`docker exec` inherits the container's env (`SUPABASE_DB_URL`) + network + the
newly-installed `pg_dump`. (If you'd rather, Coolify's **Scheduled Tasks** can run
`sh /app/scripts/backup-db.sh` on the API service on the same cadence.)

## Off-box target (simplest reliable pick)

**Hetzner Storage Box** over `rsync`/SSH — you're already on Hetzner, it's ~€3/mo
for 1TB, and needs no extra tooling. Create one, add the box's SSH key to it, set
`BACKUP_REMOTE=u<id>@u<id>.your-storagebox.de:vyndr-backups/`. The script pushes
the latest dump every Sunday. (Alternative: Backblaze B2 via `rclone` if you'd
rather keep it off Hetzner entirely — swap the `rsync` line for `rclone copy`.)

## Restore / FINGERPRINT (proves it's a real backup, not just a file)

**Built-in (runs on every backup):** the script validates each dump with
`pg_restore --list` — a dump that isn't a valid archive, or that doesn't contain
`ledger_entries`, is treated as a FAILURE and paged. So every successful run has
already proven the archive parses and holds the ledger.

**Full restore proof (run once to fingerprint):** from the host, restore the
newest dump into a throwaway postgres and count the ledger:

```bash
# 1. Produce a dump on demand (writes into the container's BACKUP_DIR)
docker exec <api-container> sh /app/scripts/backup-db.sh
# 2. Copy the newest dump out of the container
newest=$(docker exec <api-container> sh -lc 'ls -t /var/backups/vyndr/vyndr-*.dump | head -1')
docker cp "<api-container>:${newest}" /tmp/vyndr-latest.dump
# 3. Restore into a scratch postgres + count a known table
docker run -d --name vyndr-restore-test -e POSTGRES_PASSWORD=x -p 55432:5432 postgres:15
sleep 6
pg_restore --no-owner --no-privileges -d "postgresql://postgres:x@localhost:55432/postgres" /tmp/vyndr-latest.dump
psql "postgresql://postgres:x@localhost:55432/postgres" -c "select count(*) from public.ledger_entries;"
docker rm -f vyndr-restore-test
```

A non-zero `ledger_entries` count from the restored dump = the backup is real and
restorable. Record the date + row count as the fingerprint.

## Alerting

Any hard failure (missing env, `pg_dump` error, empty/undersized dump) pages
`ntfy` topic `vyndr-backups-kev2026` at urgent priority. The weekly off-box push
failing (or `BACKUP_REMOTE` unset) pages at high priority but does not fail the
run — the local dump still succeeded. Subscribe the phone to that topic.