chore(ops): add nightly backup verification scripts

.gitignore

@@ -114,3 +114,6 @@ ROADMAP.md
 
 *.bak
 terminalshop.prod.*
+
+# Backup verification config (real values; example file is committed)
+scripts/backups/config.env

scripts/backups/01-log-tail.sh

@@ -0,0 +1,13 @@
+#!/usr/bin/env bash
+# Show last 20 lines of the cron log + its mtime. Cheapest "did it run" check.
+set -euo pipefail
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+source "$SCRIPT_DIR/lib.sh"
+
+print_header "Backup log: mtime + last 20 lines"
+spinner_start "fetching log from $SSH_HOST..."
+ssh "$SSH_HOST" "
+  sudo stat -c 'Last modified: %y' '$BACKUP_LOG'
+  echo; sudo tail -20 '$BACKUP_LOG'
+"
+spinner_stop

scripts/backups/02-snapshots.sh

@@ -0,0 +1,14 @@
+#!/usr/bin/env bash
+# List every restic snapshot with timestamps and tags.
+set -euo pipefail
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+source "$SCRIPT_DIR/lib.sh"
+
+print_header "All restic snapshots"
+spinner_start "querying restic via SFTP..."
+ssh "$SSH_HOST" "
+  export RESTIC_PASSWORD_FILE='$RESTIC_PASSWORD_FILE'
+  export RESTIC_REPOSITORY='$RESTIC_REPO'
+  sudo -E restic -o sftp.args='-i $RESTIC_SSH_KEY' snapshots
+"
+spinner_stop

scripts/backups/03-check.sh

@@ -0,0 +1,21 @@
+#!/usr/bin/env bash
+# restic repo integrity check.
+#   ./03-check.sh         metadata only (~5s)
+#   ./03-check.sh --deep  + 10% data-pack re-read (~30s)
+set -euo pipefail
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+source "$SCRIPT_DIR/lib.sh"
+
+EXTRA=""
+if [ "${1:-}" = "--deep" ]; then
+  EXTRA="--read-data-subset=10%"
+fi
+
+print_header "restic check ${EXTRA:-(metadata only)}"
+spinner_start "running restic check on $SSH_HOST..."
+ssh "$SSH_HOST" "
+  export RESTIC_PASSWORD_FILE='$RESTIC_PASSWORD_FILE'
+  export RESTIC_REPOSITORY='$RESTIC_REPO'
+  sudo -E restic -o sftp.args='-i $RESTIC_SSH_KEY' check $EXTRA
+"
+spinner_stop

scripts/backups/04-restore-verify.sh

@@ -0,0 +1,57 @@
+#!/usr/bin/env bash
+# Restore the latest snapshot to /tmp on the remote box, then:
+#   - PRAGMA integrity_check on the restored DB
+#   - row-count diff vs the live DB
+# Cleans up /tmp dir at the end.
+set -euo pipefail
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+source "$SCRIPT_DIR/lib.sh"
+
+print_header "Restore latest snapshot and verify SQLite"
+spinner_start "restoring latest snapshot to /tmp on $SSH_HOST..."
+
+ssh "$SSH_HOST" \
+  "PWFILE='$RESTIC_PASSWORD_FILE' \
+   REPO='$RESTIC_REPO' \
+   KEY='$RESTIC_SSH_KEY' \
+   LIVE='$LIVE_DB' \
+   bash -s" <<'REMOTE'
+set -e
+DIR=/tmp/tshop-restore-test
+sudo rm -rf "$DIR" && sudo mkdir -p "$DIR"
+
+sudo -E RESTIC_PASSWORD_FILE="$PWFILE" RESTIC_REPOSITORY="$REPO" \
+  restic -o sftp.args="-i $KEY" restore latest --target "$DIR"
+
+DB=$(sudo find "$DIR" -name 'terminalshop.db' | head -1)
+if [ -z "$DB" ]; then
+  echo 'restored DB not found' >&2
+  exit 1
+fi
+
+echo
+echo '--- restored file ---'
+sudo stat -c '%y  %s bytes  %n' "$DB"
+
+echo
+echo '--- PRAGMA integrity_check ---'
+sudo sqlite3 "$DB" 'PRAGMA integrity_check;'
+
+echo
+echo '--- PRAGMA quick_check ---'
+sudo sqlite3 "$DB" 'PRAGMA quick_check;'
+
+echo
+echo '--- restored vs live row counts ---'
+printf '%-22s %10s %10s\n' table restored live
+for t in $(sudo sqlite3 "$DB" 'SELECT name FROM sqlite_master WHERE type="table";'); do
+  r=$(sudo sqlite3 "$DB" "SELECT COUNT(*) FROM \"$t\";")
+  l=$(sudo sqlite3 "$LIVE" "SELECT COUNT(*) FROM \"$t\";" 2>/dev/null || echo 'n/a')
+  printf '%-22s %10s %10s\n' "$t" "$r" "$l"
+done
+
+sudo rm -rf "$DIR"
+echo
+echo 'OK'
+REMOTE
+spinner_stop

scripts/backups/05-freshness.sh

@@ -0,0 +1,41 @@
+#!/usr/bin/env bash
+# Print latest snapshot age. Exit 0 if fresh, 1 if older than STALE_HOURS.
+set -euo pipefail
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+source "$SCRIPT_DIR/lib.sh"
+
+print_header "Latest snapshot freshness"
+
+spinner_start "querying latest snapshot..."
+LATEST=$(ssh "$SSH_HOST" "
+  export RESTIC_PASSWORD_FILE='$RESTIC_PASSWORD_FILE'
+  export RESTIC_REPOSITORY='$RESTIC_REPO'
+  sudo -E restic -o sftp.args='-i $RESTIC_SSH_KEY' snapshots --json
+" | jq -r 'max_by(.time) | .time')
+spinner_stop
+
+if [ -z "$LATEST" ] || [ "$LATEST" = "null" ]; then
+  echo "ERROR: could not parse latest snapshot time" >&2
+  exit 2
+fi
+
+NOW=$(date -u +%s)
+# GNU date (Linux) first, then BSD date (macOS) as fallback.
+if THEN=$(date -u -d "$LATEST" +%s 2>/dev/null); then
+  :
+else
+  CLEAN=$(echo "$LATEST" | sed 's/\.[0-9]*Z$/Z/')
+  THEN=$(date -u -j -f "%Y-%m-%dT%H:%M:%SZ" "$CLEAN" +%s)
+fi
+
+AGE_HRS=$(( (NOW - THEN) / 3600 ))
+echo "Latest snapshot: $LATEST"
+echo "Age:             ${AGE_HRS}h"
+echo "Threshold:       ${STALE_HOURS}h"
+
+if [ "$AGE_HRS" -lt "$STALE_HOURS" ]; then
+  print_ok "snapshot within freshness window"
+else
+  print_fail "STALE — investigate"
+  exit 1
+fi

scripts/backups/06-lfs-direct.sh

@@ -0,0 +1,59 @@
+#!/usr/bin/env bash
+# Independent filesystem check on lfs-remote. No restic, no password needed.
+# Proves the repo dir exists, files are present, recent uploads landed,
+# and the backup partition has room.
+set -euo pipefail
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+source "$SCRIPT_DIR/lib.sh"
+
+print_header "lfs-remote filesystem-level repo check"
+spinner_start "inspecting $LFS_HOST filesystem..."
+
+ssh "$LFS_HOST" "
+set -e
+REPO='$LFS_REPO_PATH'
+WARN_PCT=$LFS_DISK_WARN_PCT
+
+if [ ! -d \"\$REPO\" ]; then
+  echo 'ERROR: repo dir missing on lfs' >&2
+  exit 1
+fi
+
+echo '--- snapshot pointer count (one file per restic snapshot) ---'
+COUNT=\$(find \"\$REPO/snapshots\" -type f 2>/dev/null | wc -l)
+echo \"snapshots/: \$COUNT files\"
+
+echo
+echo '--- repo size on disk ---'
+du -sh \"\$REPO\"
+
+echo
+echo '--- recent uploads (files newer than 48h) ---'
+RECENT=\$(find \"\$REPO\" -type f -mtime -2 2>/dev/null | wc -l)
+echo \"files added in last 48h: \$RECENT\"
+if [ \"\$RECENT\" -eq 0 ]; then
+  echo 'WARN: no new files in last 48h' >&2
+fi
+
+echo
+echo '--- most recent snapshot pointer ---'
+LATEST=\$(ls -t \"\$REPO/snapshots\"/* 2>/dev/null | head -1)
+if [ -n \"\$LATEST\" ]; then
+  stat -c '%y  %n' \"\$LATEST\"
+else
+  echo 'WARN: no snapshot files found' >&2
+fi
+
+echo
+echo '--- disk usage ---'
+df -h \"\$REPO\"
+USED=\$(df --output=pcent \"\$REPO\" 2>/dev/null | tail -1 | tr -dc 0-9)
+if [ -n \"\$USED\" ] && [ \"\$USED\" -gt \"\$WARN_PCT\" ]; then
+  echo \"FAIL: backup partition \${USED}% full (threshold \${WARN_PCT}%)\" >&2
+  exit 1
+fi
+
+echo
+echo 'OK'
+"
+spinner_stop

scripts/backups/07-lfs-restic-check.sh

@@ -0,0 +1,38 @@
+#!/usr/bin/env bash
+# Independent restic check from the lfs-remote side. Reads repo via a
+# LOCAL filesystem path (not SFTP), so it proves the data is parseable
+# without any involvement from the prod box at restic level.
+#
+# Needs:
+#   - restic installed on lfs-remote (see scratchpkg port at /usr/ports/main/restic)
+#   - SSH access to both prod (to fetch the repo password) and lfs
+set -euo pipefail
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+source "$SCRIPT_DIR/lib.sh"
+
+print_header "lfs-remote restic check (independent vantage point)"
+
+# Precondition: restic must be on lfs.
+if ! ssh "$LFS_HOST" 'command -v restic >/dev/null 2>&1'; then
+  echo 'ERROR: restic not in PATH on lfs-remote' >&2
+  echo "Install via scratchpkg, then re-run." >&2
+  exit 2
+fi
+
+# Pull restic password from prod (needs sudo on prod).
+spinner_start "fetching restic password from $SSH_HOST..."
+PW=$(ssh "$SSH_HOST" "sudo cat '$RESTIC_PASSWORD_FILE'")
+spinner_stop
+if [ -z "$PW" ]; then
+  echo 'ERROR: could not read restic password from prod' >&2
+  exit 2
+fi
+
+# Run restic against the LOCAL repo path on lfs. Password fed via stdin
+# so it never lands in argv or a file on lfs.
+spinner_start "running restic check from $LFS_HOST..."
+ssh "$LFS_HOST" "restic -r '$LFS_REPO_PATH' --password-file=/dev/stdin check" <<<"$PW"
+spinner_stop
+
+echo
+print_ok "lfs-side restic check passed"

scripts/backups/check-all.sh

@@ -0,0 +1,138 @@
+#!/usr/bin/env bash
+# Master runner. Executes every backup check in sequence.
+#   ./check-all.sh              stop on first failure; interactive menu when TTY
+#   ./check-all.sh --keep-going keep running after failures (no menu)
+#   ./check-all.sh --deep       use --deep on 03-check.sh
+#
+# When stdin and stdout are TTYs, a failed step opens a drill-down menu:
+#   [r]etry  [l]og  [u]nlock  [s]kip  [q]uit
+# Non-TTY (cron / piped) skips the menu and behaves like the old runner.
+set -uo pipefail
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+
+KEEP_GOING=0
+DEEP=0
+for arg in "$@"; do
+  case "$arg" in
+    --keep-going) KEEP_GOING=1 ;;
+    --deep)       DEEP=1 ;;
+    -h|--help)
+      echo "Usage: $0 [--keep-going] [--deep]"
+      echo "  --keep-going  don't stop on first failure, suppress interactive menu"
+      echo "  --deep        pass --deep to 03-check.sh (10% data re-read)"
+      exit 0
+      ;;
+    *)
+      echo "unknown flag: $arg" >&2
+      exit 2
+      ;;
+  esac
+done
+
+# Pull in config + color helpers (also gives us SSH_HOST + restic env for unlock).
+source "$SCRIPT_DIR/lib.sh"
+
+declare -a STEP_NAMES=()
+declare -a STEP_STATUS=()   # OK | FAIL
+FAIL=0
+
+prompt_after_fail() {
+  # returns 0=skip, 1=quit, 2=retry
+  local script="$1"
+  while :; do
+    printf '\n%sNext?%s [r]etry  [l]og  [u]nlock  [s]kip  [q]uit > ' "$C_BOLD" "$C_RESET"
+    local choice
+    read -r choice
+    case "$choice" in
+      r|R) return 2 ;;
+      l|L) "$SCRIPT_DIR/01-log-tail.sh"; echo ;;
+      u|U)
+        echo
+        ssh "$SSH_HOST" "
+          export RESTIC_PASSWORD_FILE='$RESTIC_PASSWORD_FILE'
+          export RESTIC_REPOSITORY='$RESTIC_REPO'
+          sudo -E restic -o sftp.args='-i $RESTIC_SSH_KEY' unlock
+        "
+        echo
+        ;;
+      s|S) return 0 ;;
+      q|Q) return 1 ;;
+      "")  ;;
+      *)   printf 'unknown: %s\n' "$choice" ;;
+    esac
+  done
+}
+
+run_step() {
+  local script="$1"; shift
+  echo
+  printf '%s########## %s %s ##########%s\n' "$C_CYAN$C_BOLD" "$script" "$*" "$C_RESET"
+
+  local attempts=0
+  while :; do
+    attempts=$((attempts + 1))
+    if "$SCRIPT_DIR/$script" "$@"; then
+      STEP_NAMES+=("$script")
+      STEP_STATUS+=("OK")
+      return 0
+    fi
+
+    printf '%sFAILED:%s %s\n' "$C_RED$C_BOLD" "$C_RESET" "$script" >&2
+
+    if [ -t 0 ] && [ -t 1 ] && [ "$KEEP_GOING" -eq 0 ]; then
+      prompt_after_fail "$script"
+      case "$?" in
+        0) STEP_NAMES+=("$script"); STEP_STATUS+=("FAIL"); FAIL=$((FAIL + 1)); return 0 ;;
+        1) STEP_NAMES+=("$script"); STEP_STATUS+=("FAIL"); FAIL=$((FAIL + 1)); print_summary; exit 1 ;;
+        2) echo
+           printf '%s>>>>>> retry %s (attempt %d) <<<<<<%s\n' "$C_CYAN$C_BOLD" "$script" $((attempts + 1)) "$C_RESET"
+           continue ;;
+      esac
+    fi
+
+    # non-interactive path
+    STEP_NAMES+=("$script")
+    STEP_STATUS+=("FAIL")
+    FAIL=$((FAIL + 1))
+    if [ "$KEEP_GOING" -eq 0 ]; then
+      print_summary
+      exit 1
+    fi
+    return 0
+  done
+}
+
+print_summary() {
+  echo
+  printf '%s===============================================%s\n' "$C_CYAN$C_BOLD" "$C_RESET"
+  printf '   %-28s %s\n' "Script" "Result"
+  printf '   %-28s %s\n' "----------------------------" "--------"
+  local i
+  for i in "${!STEP_NAMES[@]}"; do
+    local name="${STEP_NAMES[$i]}"
+    local status="${STEP_STATUS[$i]}"
+    local color sym
+    case "$status" in
+      OK)   color="$C_GREEN"; sym="✓ OK"   ;;
+      FAIL) color="$C_RED"  ; sym="✗ FAIL" ;;
+    esac
+    printf '   %-28s %s%s%s\n' "$name" "$color" "$sym" "$C_RESET"
+  done
+  printf '%s===============================================%s\n' "$C_CYAN$C_BOLD" "$C_RESET"
+  if [ "$FAIL" -eq 0 ]; then
+    printf '%sAll checks passed.%s\n' "$C_GREEN$C_BOLD" "$C_RESET"
+  else
+    printf '%s%d check(s) failed.%s\n' "$C_RED$C_BOLD" "$FAIL" "$C_RESET"
+  fi
+}
+
+run_step 01-log-tail.sh
+run_step 02-snapshots.sh
+if [ "$DEEP" -eq 1 ]; then run_step 03-check.sh --deep; else run_step 03-check.sh; fi
+run_step 04-restore-verify.sh
+run_step 05-freshness.sh
+run_step 06-lfs-direct.sh
+run_step 07-lfs-restic-check.sh
+
+print_summary
+[ "$FAIL" -eq 0 ] || exit 1

scripts/backups/config.env.example

@@ -0,0 +1,28 @@
+# Backup verification config.
+# Copy to config.env (gitignored) and fill in your real values.
+#
+#   cp config.env.example config.env
+#   $EDITOR config.env
+
+# SSH alias for the prod box (resolved via ~/.ssh/config).
+SSH_HOST="ubuntu-helsinki"
+
+# Restic repository URL. Format: sftp:HOST:PATH
+# HOST is the Tailscale name of the backup target box.
+RESTIC_REPO="sftp:YOUR_TAILNET_HOST.ts.net:/root/backups/tshop"
+
+# Paths on the REMOTE prod box (not your laptop).
+RESTIC_PASSWORD_FILE="/root/.restic-password"
+RESTIC_SSH_KEY="/root/.ssh/tshop-backup"
+BACKUP_LOG="/var/log/backup-tshop.log"
+LIVE_DB="/root/terminalShop/data/terminalshop.db"
+
+# Freshness alert threshold (hours). 24h cron + 4h slop = 28.
+STALE_HOURS="28"
+
+# lfs-remote = the box that RECEIVES the backups.
+# Used by 06-lfs-direct.sh and 07-lfs-restic-check.sh for out-of-band checks.
+LFS_HOST="lfs-remote"
+LFS_REPO_PATH="/root/backups/tshop"
+# 06 fails if the backup partition is more full than this percent.
+LFS_DISK_WARN_PCT="80"

scripts/backups/lib.sh

@@ -0,0 +1,65 @@
+#!/usr/bin/env bash
+# Shared loader for the backup verification scripts.
+# Each script should: source "$SCRIPT_DIR/lib.sh"
+
+SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
+
+if [ ! -f "$SCRIPT_DIR/config.env" ]; then
+  echo "ERROR: $SCRIPT_DIR/config.env missing." >&2
+  echo "Run: cp $SCRIPT_DIR/config.env.example $SCRIPT_DIR/config.env" >&2
+  exit 1
+fi
+
+# shellcheck source=/dev/null
+source "$SCRIPT_DIR/config.env"
+
+# ANSI colors. Emit only when stdout is a TTY and NO_COLOR is unset.
+if [ -t 1 ] && [ -z "${NO_COLOR:-}" ]; then
+  C_RED=$'\033[1;31m'
+  C_GREEN=$'\033[1;32m'
+  C_YELLOW=$'\033[1;33m'
+  C_BLUE=$'\033[1;34m'
+  C_CYAN=$'\033[1;36m'
+  C_BOLD=$'\033[1m'
+  C_RESET=$'\033[0m'
+else
+  C_RED=""; C_GREEN=""; C_YELLOW=""; C_BLUE=""; C_CYAN=""; C_BOLD=""; C_RESET=""
+fi
+
+print_header() { printf '\n%s=== %s ===%s\n' "$C_CYAN" "$1" "$C_RESET"; }
+print_ok()     { printf '%sOK%s%s\n'    "$C_GREEN"  "$C_RESET" "${1:+ — $1}"; }
+print_fail()   { printf '%sFAIL%s%s\n'  "$C_RED"    "$C_RESET" "${1:+ — $1}" >&2; }
+print_warn()   { printf '%sWARN%s%s\n'  "$C_YELLOW" "$C_RESET" "${1:+ — $1}" >&2; }
+print_info()   { printf '%s%s%s\n'      "$C_BLUE"   "$1"       "$C_RESET"; }
+
+# Spinner: shows a rotating braille char while a foreground command runs.
+# Use as a pair around the silent/blocking call:
+#   spinner_start "fetching log..."
+#   ssh ...
+#   spinner_stop
+# Auto-skips when stdout isn't a TTY (cron / piped). Real output from the
+# wrapped command will visually overwrite the spinner — by design.
+SPINNER_PID=""
+spinner_start() {
+  [ -t 1 ] && [ -z "${NO_COLOR:-}" ] || return 0
+  local msg="${1:-working}"
+  (
+    trap 'exit 0' TERM
+    local chars=('⠋' '⠙' '⠹' '⠸' '⠼' '⠴' '⠦' '⠧' '⠇' '⠏')
+    local i=0
+    while :; do
+      i=$(( (i + 1) % ${#chars[@]} ))
+      printf '\r%s%s%s %s' "$C_CYAN" "${chars[$i]}" "$C_RESET" "$msg"
+      sleep 0.08
+    done
+  ) &
+  SPINNER_PID=$!
+  disown "$SPINNER_PID" 2>/dev/null || true
+}
+spinner_stop() {
+  [ -n "$SPINNER_PID" ] || return 0
+  kill "$SPINNER_PID" 2>/dev/null || true
+  wait "$SPINNER_PID" 2>/dev/null || true
+  printf '\r\033[K'
+  SPINNER_PID=""
+}