feat(ops): nightly database backups with rotation (RECOMMENDATIONS #3)

Adds a compose 'backup' sidecar: daily pg_dump of the inboxintel DB into ./backups
(git-ignored), atomic write (.tmp -> rename), rotation after BACKUP_KEEP_DAYS
(default 7). Previously there were NO backups — a bad migration or volume loss
meant total data loss. Restore procedure documented in compose + SECURITY.md.

Verified: compose config parses; a live pg_dump against the staging DB produced a
valid dump over the compose network with the same image/credentials the sidecar uses.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

.env.example

@@ -15,3 +15,6 @@ FRONTEND_ORIGIN=http://localhost:8081
 # Set DEV_MODE=true and MAX_MESSAGES=1000 to test against a large mailbox.
 DEV_MODE=false
 MAX_MESSAGES=0
+
+# Nightly DB backup rotation (days of dumps to keep in ./backups)
+BACKUP_KEEP_DAYS=7

.gitignore

@@ -21,6 +21,9 @@ frontend/.vite/
 appsettings.*.local.json
 secrets.json
 
+## DB backups (never commit dumps)
+backups/
+
 ## Logs
 logs/
 *.log

SECURITY.md

@@ -29,7 +29,9 @@ reverse proxy.
    mitigate (a third party reading the DB files) reduces to "someone with access to your
    machine" — mitigate it at the layer that actually works:
    - **Use full-disk or volume encryption** on the host (BitLocker/LUKS) — strongly recommended.
-   - **Encrypt backups** of the `pgdata` volume the same way.
+   - **Encrypt backups**: nightly `pg_dump` rotation runs via the compose `backup` service
+     into `./backups/` (git-ignored) — keep that directory on an encrypted disk and copy it
+     off-machine. Restore: `docker compose exec -T postgres psql -U inboxintel -d inboxintel < backups/<file>.sql`.
    - Before any **multi-user** deployment, revisit per the multi-provider security design
      (host admins must not be able to read members' mail — plaintext bodies break that promise).
 2. **DB connection is not TLS** — Postgres is only reachable on the compose-internal network /

docker-compose.yml

@@ -22,6 +22,37 @@ services:
       timeout: 5s
       retries: 10
 
+  # Nightly logical backups (RECOMMENDATIONS #3 — previously there were NONE). Dumps
+  # rotate after BACKUP_KEEP_DAYS. The ./backups host directory should live on an
+  # encrypted disk and be included in your off-machine backup regime (see SECURITY.md).
+  # Restore: docker compose exec -T postgres psql -U inboxintel -d inboxintel < backups/<file>.sql
+  backup:
+    image: pgvector/pgvector:pg16
+    entrypoint: /bin/sh
+    command:
+      - -c
+      - |
+        while true; do
+          ts=$$(date -u +%Y%m%d-%H%M%S)
+          if pg_dump -h postgres -U inboxintel -d inboxintel > /backups/inboxintel-$$ts.sql.tmp; then
+            mv /backups/inboxintel-$$ts.sql.tmp /backups/inboxintel-$$ts.sql
+            echo "backup OK: inboxintel-$$ts.sql"
+          else
+            rm -f /backups/inboxintel-$$ts.sql.tmp
+            echo "backup FAILED at $$ts" >&2
+          fi
+          find /backups -name 'inboxintel-*.sql' -mtime +$${BACKUP_KEEP_DAYS:-7} -delete
+          sleep 86400
+        done
+    environment:
+      PGPASSWORD: ${POSTGRES_PASSWORD:?set POSTGRES_PASSWORD in deploy/.env}
+      BACKUP_KEEP_DAYS: ${BACKUP_KEEP_DAYS:-7}
+    volumes:
+      - ./backups:/backups
+    depends_on:
+      postgres:
+        condition: service_healthy
+
   api:
     build:
       context: .