backup-freshness-checker: read-only dw_unified + secrets backup-gap detector with CNCP card

6adae00a8bf0a015536d734f39e6299484bd0761 · 2026-06-14 21:15:29 -0700 · Steve Abrams
Files touched

A .gitignore
A README.md
A check-backups.sh
Diff

commit 6adae00a8bf0a015536d734f39e6299484bd0761
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jun 14 21:15:29 2026 -0700

    backup-freshness-checker: read-only dw_unified + secrets backup-gap detector with CNCP card
---
 .gitignore       |   8 ++++
 README.md        |  32 ++++++++++++++++
 check-backups.sh | 113 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 153 insertions(+)

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..1924158
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,8 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+.next/
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..34e6897
--- /dev/null
+++ b/README.md
@@ -0,0 +1,32 @@
+# backup-freshness-checker
+
+READ-ONLY detector for silent backup gaps. Verifies a recent, **restorable**
+backup actually exists for the canonical data + secret stores, and surfaces a
+CNCP parking-lot card when anything is stale, zero-byte, or missing.
+
+## What it checks
+1. **dw_unified** canonical DB dump on Kamatera (`my-server`) — newest dump in
+   `/root/backups/db` + `/root/db-sync`. Flags: newest dump is 0 bytes, no
+   non-empty dump exists, or newest restorable dump is older than 48h.
+2. **secrets master** `~/Projects/secrets-manager/.env` — mtime + size only
+   (NEVER reads contents). Flags missing/empty; notes that no automated backup
+   of this file was detected.
+
+## Hard rules (by design)
+- Never creates, runs, or modifies any backup. Never `pg_dump`s.
+- Never echoes secret contents — mtime/size only.
+- Detect-and-surface only. Exit 2 = critical, 1 = warn, 0 = all clear.
+
+## Run
+    ./check-backups.sh
+
+## Suggested cadence (NOT installed — Steve-gated)
+Mac2 launchd daily, e.g. 09:00 PT. A plist is intentionally NOT shipped here so
+nothing schedules itself without Steve's say-so.
+
+## Origin
+Born 2026-06-14 (YOLO cycle-3 #5). Found the dw_unified nightly pg_dump silently
+producing 0-byte dumps since 2026-06-13 (`password authentication failed for
+user "dw_admin"` — stale hardcoded pw in `/root/DW-Agents/scripts/db-backup.sh`,
+a recurrence of the same 2026-05-12 silent-failure class). Last GOOD dump was
+2026-06-03 (1.28 GB), i.e. 11 days stale.
diff --git a/check-backups.sh b/check-backups.sh
new file mode 100755
index 0000000..2bcdd2d
--- /dev/null
+++ b/check-backups.sh
@@ -0,0 +1,113 @@
+#!/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
(oldest) · back to Backup Freshness Checker · (newest)