db_wipe_watchdog.sh - Second Brain

⬇️ Download ← Back
#!/usr/bin/env bash
# DB wipe watchdog for Command Sovereignty
#
# Runs every 5 minutes via systemd timer. Checks the /health endpoint
# which verifies critical DB tables exist and have data. If wipe detected,
# automatically restores from the latest backup and restarts the service.
#
# Sends Telegram alert via Hermes agent-relay if recovery was needed.

set -euo pipefail

HEALTH_URL="${HEALTH_URL:-http://127.0.0.1:5003/health}"
DB_PATH="/home/vincent/projects/command-sovereignty/instance/auth.db"
BACKUP_DIR="/home/vincent/projects/command-sovereignty/backups"
LOG_FILE="/home/vincent/projects/command-sovereignty/logs/watchdog.log"
SERVICE_NAME="command-sovereignty"

mkdir -p "$(dirname "$LOG_FILE")"

log() {
    echo "$(date -Iseconds) $1" >> "$LOG_FILE"
}

# Check health endpoint
log "INFO: Checking health at $HEALTH_URL"
HTTP_CODE=$(curl -s -o /tmp/cs_health.json -w "%{http_code}" "$HEALTH_URL" 2>/dev/null || echo "000")

if [ "$HTTP_CODE" = "200" ]; then
    log "INFO: Health check passed (200)"
    exit 0
fi

# Wipe detected or app down — attempt recovery
log "WARN: Health check returned $HTTP_CODE — attempting recovery"

# Check if we have a recent backup
LATEST_BACKUP=$(ls -t "${BACKUP_DIR}"/auth_*.db.gz 2>/dev/null | head -1)
if [ -z "$LATEST_BACKUP" ]; then
    log "ERROR: No backups found in $BACKUP_DIR — cannot auto-restore"
    exit 1
fi

log "INFO: Restoring from $LATEST_BACKUP"

# Decompress backup to temp file
TMP_DB=$(mktemp /tmp/auth_restore_XXXXXX.db)
gunzip -c "$LATEST_BACKUP" > "$TMP_DB"

# Verify integrity
INTEGRITY=$(sqlite3 "$TMP_DB" "PRAGMA integrity_check;" 2>/dev/null | head -1)
if [ "$INTEGRITY" != "ok" ]; then
    log "ERROR: Backup integrity check failed: $INTEGRITY"
    rm -f "$TMP_DB"
    exit 1
fi

# Verify it has data
PROFILE_COUNT=$(sqlite3 "$TMP_DB" "SELECT count(*) FROM profiles;" 2>/dev/null || echo "0")
if [ "$PROFILE_COUNT" = "0" ]; then
    log "ERROR: Backup has no profiles — not restoring"
    rm -f "$TMP_DB"
    exit 1
fi

log "INFO: Backup verified OK ($PROFILE_COUNT profiles)"

# Backup current (possibly wiped) DB before overwriting
if [ -f "$DB_PATH" ]; then
    cp "$DB_PATH" "${DB_PATH}.watchdog_bak.$(date +%Y%m%d_%H%M%S)"
    # Clean up old watchdog backups (keep 3)
    ls -t "${DB_PATH}".watchdog_bak.* 2>/dev/null | tail -n +4 | xargs rm -f 2>/dev/null || true
fi

# Restore
cp "$TMP_DB" "$DB_PATH"
rm -f "${DB_PATH}-wal" "${DB_PATH}-shm" "$TMP_DB"

log "INFO: DB restored, restarting service"

# Restart the service
systemctl --user restart "${SERVICE_NAME}.service" 2>/dev/null || {
    log "ERROR: Failed to restart ${SERVICE_NAME}.service"
    exit 1
}

# Wait for service to come back up
sleep 3
FINAL_CODE=$(curl -s -o /dev/null -w "%{http_code}" "$HEALTH_URL" 2>/dev/null || echo "000")

if [ "$FINAL_CODE" = "200" ]; then
    log "INFO: Recovery successful — service healthy after restore"
else
    log "ERROR: Recovery failed — service still unhealthy (HTTP $FINAL_CODE)"
    exit 1
fi

exit 0
BASH db_wipe_watchdog.sh
