BASH backup_db.sh

#!/usr/bin/env bash
# Automated SQLite backup for Command Sovereignty.
#
# - Uses sqlite3 .backup (safe with WAL / live writers)
# - Verifies integrity of the backup copy before keeping it
# - Compresses with gzip
# - Retains 14 days of backups
#
# Cron: every 6 hours (see crontab entry installed alongside this script)

set -euo pipefail

DB="/home/vincent/projects/command-sovereignty/instance/auth.db"
BACKUP_DIR="/home/vincent/projects/command-sovereignty/backups"
RETENTION_DAYS=14
TS="$(date +%Y%m%d_%H%M%S)"
TMP="${BACKUP_DIR}/auth_${TS}.db.tmp"
OUT="${BACKUP_DIR}/auth_${TS}.db"

mkdir -p "$BACKUP_DIR"

# Also check legacy path in case DATABASE_URL ever changes
if [ ! -f "$DB" ]; then
    LEGACY="/home/vincent/projects/command-sovereignty/auth.db"
    if [ -f "$LEGACY" ]; then
        DB="$LEGACY"
    else
        echo "ERROR: database not found at $DB (or $LEGACY)" >&2
        exit 1
    fi
fi

# Guard: skip empty or corrupt DB (prevents backing up a wiped file)
if [ ! -s "$DB" ]; then
    echo "WARN: database is empty ($DB) — skipping backup" >&2
    exit 0
fi

# Guard: verify the DB actually has tables before backing it up
TABLE_COUNT=$(sqlite3 "$DB" "SELECT count(*) FROM sqlite_master WHERE type='table';" 2>/dev/null || echo "0")
if [ "$TABLE_COUNT" = "0" ]; then
    echo "WARN: database has no tables ($DB) — skipping backup" >&2
    exit 0
fi

# Guard: verify key tables exist and have data (catches wiped DB early)
PROFILE_COUNT=$(sqlite3 "$DB" "SELECT count(*) FROM profiles;" 2>/dev/null || echo "0")
if [ "$PROFILE_COUNT" = "0" ]; then
    echo "ERROR: profiles table is empty or missing — DB may have been wiped!" >&2
    echo "ERROR: Last backup size: $(ls -lh ${BACKUP_DIR}/auth_*.db.gz 2>/dev/null | tail -1 | awk '{print $5}')" >&2
    # Alert via Telegram if webhook is configured
    if [ -n "$HERMES_TELEGRAM_WEBHOOK" ]; then
        curl -s -X POST "$HERMES_TELEGRAM_WEBHOOK" \
            -H 'Content-Type: application/json' \
            -d "{\"text\":\"🚨 Command Sovereignty DB alert: profiles table empty at $(date). Check: $DB\"}" \
            || true
    fi
    exit 1
fi

# 1. Online backup (consistent snapshot even while the app is writing)
sqlite3 "$DB" ".backup '$TMP'"

# 2. Integrity check on the snapshot
if ! sqlite3 "$TMP" "PRAGMA integrity_check;" | grep -q "^ok$"; then
    echo "ERROR: integrity check FAILED for backup $TMP" >&2
    rm -f "$TMP"
    exit 1
fi

# 3. Compress
mv "$TMP" "$OUT"
gzip -f "$OUT"

# 4. Retention: delete backups older than N days
find "$BACKUP_DIR" -name 'auth_*.db.gz' -mtime +"$RETENTION_DAYS" -delete

echo "OK: backup written to ${OUT}.gz"