`check-backups.sh`

114 lines
#!/bin/bash
# backup-freshness-checker — READ-ONLY backup-gap detector.
#
# Verifies a recent, RESTORABLE backup actually exists for the canonical data
# store (dw_unified on Kamatera) + the canonical secret store (Mac2
# secrets-manager/.env). Emits a CNCP parking-lot card when any backup is
# stale, zero-byte, or missing.
#
# HARD READ-ONLY: never creates/runs/modifies a backup, never pg_dumps, never
# echoes secret contents. Detect-and-surface ONLY.
#
# Born 2026-06-14 (YOLO cycle-3 #5) after discovering the dw_unified nightly
# pg_dump had been silently producing 0-byte dumps since 2026-06-13 due to a
# stale dw_admin password in /root/DW-Agents/scripts/db-backup.sh.

set -uo pipefail

SSH_HOST="my-server"                       # Kamatera (~/.ssh/config alias)
SECRETS_ENV="$HOME/Projects/secrets-manager/.env"
CNCP_URL="http://127.0.0.1:3333/api/parking-lot"

# Cadence thresholds (hours)
DW_DUMP_MAX_AGE_H=48                        # dw_unified dump must be < 48h old
SECRETS_MAX_AGE_H=$((24 * 30))             # secrets .env: warn if untouched > 30d (informational only)

NOW=$(date +%s)
ALERTS=()        # human-readable problem lines
SEV="ok"         # ok | warn | critical

note()  { echo "[backup-check] $*"; }
crit()  { ALERTS+=("CRITICAL: $*"); SEV="critical"; }
warn()  { ALERTS+=("WARN: $*"); [ "$SEV" = "ok" ] && SEV="warn"; }

# ---------------------------------------------------------------------------
# 1. dw_unified canonical DB dump (Kamatera)
# ---------------------------------------------------------------------------
note "checking dw_unified dumps on $SSH_HOST ..."
REMOTE=$(ssh -o ConnectTimeout=15 -o BatchMode=yes "$SSH_HOST" '
  find /root/backups/db /root/db-sync -maxdepth 1 -iname "dw_unified*.dump" -type f \
    -printf "%T@\t%s\t%p\n" 2>/dev/null | sort -rn | head -5
' 2>/dev/null)

if [ -z "$REMOTE" ]; then
  crit "dw_unified: NO dump artifacts found on $SSH_HOST (/root/backups/db, /root/db-sync). Restore impossible."
else
  NEWEST_LINE=$(echo "$REMOTE" | head -1)
  N_EPOCH=$(echo "$NEWEST_LINE" | cut -f1 | cut -d. -f1)
  N_SIZE=$(echo "$NEWEST_LINE" | cut -f2)
  N_PATH=$(echo "$NEWEST_LINE" | cut -f3)

  GOOD_LINE=$(echo "$REMOTE" | awk -F'\t' '$2+0 > 0 {print; exit}')
  if [ -z "$GOOD_LINE" ]; then
    crit "dw_unified: newest dump $N_PATH is ${N_SIZE} bytes and NO non-empty dump found in scan window. No restorable backup."
  else
    G_EPOCH=$(echo "$GOOD_LINE" | cut -f1 | cut -d. -f1)
    G_SIZE=$(echo "$GOOD_LINE" | cut -f2)
    G_PATH=$(echo "$GOOD_LINE" | cut -f3)
    G_AGE_H=$(( (NOW - G_EPOCH) / 3600 ))
    G_SIZE_H=$(awk -v b="$G_SIZE" 'BEGIN{printf "%.2f GB", b/1073741824}')
    note "newest restorable dw_unified dump: $G_PATH ($G_SIZE_H, ${G_AGE_H}h old)"

    if [ "$N_SIZE" -eq 0 ]; then
      crit "dw_unified: NEWEST dump $N_PATH is 0 bytes (failed run). Last GOOD dump is ${G_AGE_H}h old ($G_SIZE_H) — nightly job is broken."
    fi
    if [ "$G_AGE_H" -gt "$DW_DUMP_MAX_AGE_H" ]; then
      crit "dw_unified: newest restorable dump is ${G_AGE_H}h old (> ${DW_DUMP_MAX_AGE_H}h threshold). $G_PATH"
    fi
  fi
fi

# ---------------------------------------------------------------------------
# 2. Canonical secret store freshness (Mac2) — mtime/size ONLY, never contents
# ---------------------------------------------------------------------------
note "checking secrets store $SECRETS_ENV ..."
if [ ! -f "$SECRETS_ENV" ]; then
  crit "secrets store MISSING: $SECRETS_ENV not found. Canonical secret master absent."
else
  S_EPOCH=$(stat -f %m "$SECRETS_ENV" 2>/dev/null || stat -c %Y "$SECRETS_ENV")
  S_SIZE=$(stat -f %z "$SECRETS_ENV" 2>/dev/null || stat -c %s "$SECRETS_ENV")
  S_AGE_H=$(( (NOW - S_EPOCH) / 3600 ))
  note "secrets .env: ${S_SIZE} bytes, last modified ${S_AGE_H}h ago (contents NOT read)"
  if [ "$S_SIZE" -eq 0 ]; then
    crit "secrets store $SECRETS_ENV is 0 bytes — empty secret master."
  fi
  if [ "$S_AGE_H" -gt "$SECRETS_MAX_AGE_H" ]; then
    warn "secrets .env untouched for ${S_AGE_H}h (informational). NOTE: no automated backup of this file was detected."
  fi
fi

# ---------------------------------------------------------------------------
# 3. Surface to CNCP parking-lot when anything is wrong
# ---------------------------------------------------------------------------
if [ "$SEV" = "ok" ]; then
  note "ALL CLEAR — verified recent restorable backup exists."
  exit 0
fi

BODY=$(printf '%s\n' "${ALERTS[@]}")
note "PROBLEMS DETECTED ($SEV):"
printf '  %s\n' "${ALERTS[@]}"

TITLE="Backup gap detected ($SEV): dw_unified / secrets"
if command -v jq >/dev/null 2>&1; then
  PAYLOAD=$(jq -n --arg t "$TITLE" --arg b "$BODY" --arg s "$SEV" \
    '{title:$t, body:$b, severity:$s, source:"backup-freshness-checker", category:"infra"}')
else
  PAYLOAD="{\"title\":\"$TITLE\",\"body\":\"backup gap; see logs\",\"severity\":\"$SEV\",\"source\":\"backup-freshness-checker\"}"
fi
curl -s -m 6 -H 'Content-Type: application/json' -X POST "$CNCP_URL" -d "$PAYLOAD" >/dev/null 2>&1 \
  && note "posted CNCP card: $TITLE" \
  || note "CNCP post failed (panel down?) — alerts above stand."

[ "$SEV" = "critical" ] && exit 2 || exit 1