BASH backup_verify.sh

#!/bin/bash
# Backup verification for AgentForms
# Checks backup validity, integrity, and restore capability

set -e

BACKUP_DIR="/home/vincent/projects/agentforms/data/backups"

echo "=== AgentForms Backup Verification ==="

# 1. Check backup exists
LATEST_BACKUP=$(ls -t "$BACKUP_DIR"/app_*.db 2>/dev/null | head -1)
if [ -z "$LATEST_BACKUP" ]; then
  echo "FAIL: No backup found in $BACKUP_DIR"
  exit 1
fi
echo "Found: $(basename $LATEST_BACKUP) ($(du -h "$LATEST_BACKUP" | cut -f1))"

# 2. Verify valid SQLite with read-only compatible check
TABLE_COUNT=$(sqlite3 -readonly "$LATEST_BACKUP" "SELECT count(*) FROM sqlite_master WHERE type='table';")
if [ "$TABLE_COUNT" -lt 1 ]; then
  echo "FAIL: Backup has no tables ($TABLE_COUNT)"
  exit 1
fi
echo "OK: Backup has $TABLE_COUNT tables"

# 3. Check key tables exist
for tbl in users sites submissions api_keys monthly_usage; do
  EXISTS=$(sqlite3 -readonly "$LATEST_BACKUP" "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='$tbl';")
  if [ "$EXISTS" -ne 1 ]; then
    echo "FAIL: Missing required table: $tbl"
    exit 1
  fi
done
echo "OK: All required tables present"

# 4. Check data counts
USER_COUNT=$(sqlite3 -readonly "$LATEST_BACKUP" "SELECT count(*) FROM users;")
SITE_COUNT=$(sqlite3 -readonly "$LATEST_BACKUP" "SELECT count(*) FROM sites;")
echo "OK: $USER_COUNT user(s), $SITE_COUNT site(s)"

# 5. Copy-and-read simulation (restore test)
RESTORE_TEST="/tmp/af_restore_test_$$"
cp "$LATEST_BACKUP" "$RESTORE_TEST"
sqlite3 "$RESTORE_TEST" "PRAGMA integrity_check;" > /dev/null 2>&1
RESTORE_OK=$?
rm -f "$RESTORE_TEST"

if [ $RESTORE_OK -eq 0 ]; then
  echo "OK: Full integrity check passed (restore simulation)"
else
  echo "WARN: Integrity check had issues in restore copy — check WAL mode"
fi

# 6. Count total backups
BACKUP_COUNT=$(ls -1 "$BACKUP_DIR"/app_*.db 2>/dev/null | wc -l)
echo "Total backups: $BACKUP_COUNT (retention: 7)"

echo "=== All backup checks passed ==="