vyndr/scripts/backup-db.sh

#!/usr/bin/env bash
#
# VYNDR nightly database backup (security follow-up item 2).
#
# Supabase free tier has ZERO backups (no scheduled, no PITR) — the ledger and
# everything else have no safety net. This dumps the WHOLE database nightly via
# the direct connection string, keeps 14 days locally, pushes a weekly copy
# off-box, and pages ntfy on ANY failure. Runs on the Hetzner box via cron.
#
# REQUIRED env (set on the box / in the container that runs the cron):
#   SUPABASE_DB_URL   the Supabase DIRECT connection string (session mode, the
#                     db.<ref>.supabase.co:5432 URL — NOT the :6543 pooler;
#                     pg_dump needs a real session). Kev pastes this in Coolify.
# OPTIONAL env:
#   BACKUP_DIR        local dump dir            (default /var/backups/vyndr)
#   BACKUP_KEEP_DAYS  local retention           (default 14)
#   BACKUP_REMOTE     off-box rsync target for the weekly copy, e.g.
#                     u123456@u123456.your-storagebox.de:vyndr-backups/
#                     (empty = skip the off-box push; a WARN is paged)
#   NTFY_URL          (default https://ntfy.sh)
#   NTFY_TOPIC        (default vyndr-backups-kev2026)
#
set -Eeuo pipefail

BACKUP_DIR="${BACKUP_DIR:-/var/backups/vyndr}"
KEEP_DAYS="${BACKUP_KEEP_DAYS:-14}"
NTFY_URL="${NTFY_URL:-https://ntfy.sh}"
NTFY_TOPIC="${NTFY_TOPIC:-vyndr-backups-kev2026}"
STAMP="$(date -u +%Y%m%d-%H%M%S)"
DUMP="${BACKUP_DIR}/vyndr-${STAMP}.dump"
MIN_BYTES="${BACKUP_MIN_BYTES:-50000}"   # a real dump of this DB is far bigger; guards an empty/failed dump

notify() {  # notify <title> <priority> <message>
  curl -fsS --max-time 15 \
    -H "Title: ${1}" -H "Priority: ${2}" -H "Tags: floppy_disk" \
    -d "${3}" "${NTFY_URL}/${NTFY_TOPIC}" >/dev/null 2>&1 || true
}

fail() { notify "VYNDR backup FAILED" "urgent" "${1}"; echo "ERROR: ${1}" >&2; exit 1; }
trap 'fail "backup script errored near line ${LINENO}"' ERR

[ -n "${SUPABASE_DB_URL:-}" ] || fail "SUPABASE_DB_URL is not set — cannot back up"
command -v pg_dump >/dev/null 2>&1 || fail "pg_dump not installed (apt-get install postgresql-client)"
mkdir -p "${BACKUP_DIR}"

# 1. Dump the whole DB in custom format (-Fc: compressed, restorable with pg_restore).
pg_dump "${SUPABASE_DB_URL}" -Fc --no-owner --no-privileges -f "${DUMP}" \
  || fail "pg_dump failed"

# 2. Sanity: a real dump is not tiny. An empty/near-empty file is a silent failure.
SIZE="$(stat -c%s "${DUMP}" 2>/dev/null || echo 0)"
[ "${SIZE}" -ge "${MIN_BYTES}" ] || fail "dump is only ${SIZE} bytes (< ${MIN_BYTES}) — treating as a failed backup"

# 2b. Integrity fingerprint: a valid custom-format archive lists its objects via
#     pg_restore --list (no target DB needed). Confirm it parses AND contains the
#     ledger — proves it's a real, restorable archive, not just a file of bytes.
TOC="$(pg_restore --list "${DUMP}" 2>/dev/null)" || fail "pg_restore --list failed — dump is not a valid archive"
OBJECTS="$(printf '%s\n' "${TOC}" | grep -c ';' || true)"
printf '%s\n' "${TOC}" | grep -qi 'TABLE DATA public ledger_entries' \
  || fail "dump archive does not contain ledger_entries — refusing to trust it"
echo "backup validated: ${DUMP} (${SIZE} bytes, ${OBJECTS} archive objects, ledger_entries present)"

# 3. Rotate: drop local dumps older than KEEP_DAYS.
find "${BACKUP_DIR}" -name 'vyndr-*.dump' -type f -mtime "+${KEEP_DAYS}" -delete || true

# 3b. SSH key for the off-box push (Session 64).
#     The container filesystem is EPHEMERAL — a keypair generated inside it dies
#     on the next redeploy and the off-box push would silently start failing. So
#     the PRIVATE key is injected as an env var (Coolify secret) and written to a
#     0600 temp file per run. Hetzner Storage Box speaks full OpenSSH on PORT 23
#     (port 22 is SFTP-only, mod_sftp) — verified live; rsync must target 23.
SSH_KEY_FILE=""
cleanup_key() { [ -n "${SSH_KEY_FILE}" ] && rm -f "${SSH_KEY_FILE}" || true; }
trap cleanup_key EXIT
# HOST KEY IS STATICALLY PINNED (Session 64). This used to be
# StrictHostKeyChecking=accept-new — trust-on-first-use, which accepts whatever
# host key it meets first and would happily trust an impostor on the first run.
# scripts/storagebox_known_hosts carries the ED25519 line verified out-of-band
# (SHA256:XqONwb1S0zuj5A1CDxpOSuD2hnAArV1A3wKY7Z3sdgM). A mismatch is now a HARD
# FAIL — which is the point. Never weaken this to accept-new/=no//dev/null.
KNOWN_HOSTS="${BACKUP_KNOWN_HOSTS:-$(dirname "$0")/storagebox_known_hosts}"
[ -f "${KNOWN_HOSTS}" ] || fail "pinned known_hosts missing at ${KNOWN_HOSTS} — refusing to push without host-key verification"
RSYNC_SSH="ssh -p ${BACKUP_SSH_PORT:-23} -o StrictHostKeyChecking=yes -o UserKnownHostsFile=${KNOWN_HOSTS} -o BatchMode=yes"
if [ -n "${BACKUP_SSH_KEY:-}" ]; then
  SSH_KEY_FILE="$(mktemp)"
  chmod 600 "${SSH_KEY_FILE}"
  # Accept EITHER form (Session 64):
  #   1. base64 (recommended — `base64 -w0`; survives any env-var mangling of
  #      newlines, which is the usual way an injected SSH key silently breaks)
  #   2. raw PEM with literal \n escapes
  # Detect base64 by trying to decode and checking for the PEM header.
  DECODED="$(printf '%s' "${BACKUP_SSH_KEY}" | base64 -d 2>/dev/null || true)"
  case "${DECODED}" in
    *"PRIVATE KEY"*)
      printf '%s\n' "${DECODED}" > "${SSH_KEY_FILE}"
      echo "ssh key: base64-decoded"
      ;;
    *)
      printf '%b\n' "${BACKUP_SSH_KEY}" | sed -e 's/[[:space:]]*$//' > "${SSH_KEY_FILE}"
      echo "ssh key: used raw (not base64)"
      ;;
  esac
  chmod 600 "${SSH_KEY_FILE}"
  RSYNC_SSH="${RSYNC_SSH} -i ${SSH_KEY_FILE}"
fi

# 4. OFF-BOX COPY — DEFERRED (Session 64).
#    The dump now lands on a PERSISTENT VOLUME (BACKUP_DIR=/app/backups), so it
#    already survives redeploys — the container-ephemeral risk is closed. Storage
#    Box SSH auth is not working yet, so the off-box push is explicitly DEFERRED:
#    it must never fail the backup. A durable on-box dump is a real backup; a
#    failing rsync on top of it is a follow-up, not an incident.
#
#    Set BACKUP_OFFBOX=1 (with BACKUP_SSH_KEY) to re-enable. Until then we log
#    and page at LOW priority, and we never call a deferred push a failure.
if [ "${BACKUP_OFFBOX:-0}" = "1" ] && [ -n "${BACKUP_REMOTE:-}" ] && [ -n "${BACKUP_SSH_KEY:-}" ]; then
  # Phase 1b — GUARANTEE THE REMOTE DIRECTORY EXISTS. The box starts with only
  # .ssh/, and rsync of a file into a missing parent either fails or silently
  # writes the dump AS the directory name (one file, overwritten nightly, which
  # would read as "backups exist" while retaining exactly one). Prefer rsync's
  # own --mkpath; fall back to an explicit ssh mkdir -p for older rsync.
  REMOTE_HOST="${BACKUP_REMOTE%%:*}"
  REMOTE_PATH="${BACKUP_REMOTE#*:}"
  MKPATH_FLAG=""
  if rsync --help 2>&1 | grep -q -- '--mkpath'; then
    MKPATH_FLAG="--mkpath"
  else
    ${RSYNC_SSH} "${REMOTE_HOST}" "mkdir -p '${REMOTE_PATH}'" \
      || echo "warn: remote mkdir -p failed; relying on an existing directory"
  fi

  # OFF-BOX IS REQUIRED NOW (Session 64, Phase 2b). A failed push must never
  # again read as success: it PAGES, and the run reports offbox_ok:false.
  # Exit code deliberately still reflects ON-BOX durability — a good on-box dump
  # must not raise a false total-failure alarm. Surface the truth; don't
  # manufacture a failure.
  if rsync -az --timeout=120 ${MKPATH_FLAG} -e "${RSYNC_SSH}" "${DUMP}" "${BACKUP_REMOTE}"; then
    echo "off-box push OK -> ${BACKUP_REMOTE}"
    echo "OFFBOX_OK=1"
    notify "VYNDR backup OK (+off-box)" "default" "Nightly dump ${STAMP} (${SIZE} bytes) pushed off-box."
  else
    echo "off-box push FAILED (on-box dump is durable, but OFF-BOX IS REQUIRED)"
    echo "OFFBOX_OK=0"
    notify "VYNDR OFF-BOX PUSH FAILED" "urgent" "Dump ${STAMP} (${SIZE} bytes) is on the persistent volume but did NOT reach the Storage Box. The database has no off-box copy tonight."
  fi
else
  echo "off-box push DEFERRED (BACKUP_OFFBOX!=1 or remote/key unset) — on-box dump is durable at ${DUMP}"
  echo "OFFBOX_OK=deferred"
fi

echo "backup ok: ${DUMP} (${SIZE} bytes)"

exit 0