scripts/backup-db.sh

#!/bin/bash
# Daily SQLite backup — safe online backup using .backup command
# Add to crontab: 0 3 * * * /path/to/researcher/scripts/backup-db.sh

set -e

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
PROJECT_DIR="$(dirname "$SCRIPT_DIR")"

DB_PATH="${PROJECT_DIR}/data/researcher.db"
BACKUP_DIR="${PROJECT_DIR}/data/backups"
KEEP_DAYS=14

mkdir -p "$BACKUP_DIR"

TIMESTAMP=$(date +%Y%m%d-%H%M%S)
BACKUP_FILE="$BACKUP_DIR/researcher-$TIMESTAMP.db"

# Use SQLite online backup (safe with WAL mode)
python3 -c "
import sqlite3, shutil
src = sqlite3.connect('$DB_PATH')
dst = sqlite3.connect('$BACKUP_FILE')
src.backup(dst)
dst.close()
src.close()
"

# Compress
gzip "$BACKUP_FILE"
echo "Backup: ${BACKUP_FILE}.gz ($(du -h "${BACKUP_FILE}.gz" | cut -f1))"

# Prune old backups
find "$BACKUP_DIR" -name "researcher-*.db.gz" -mtime +"$KEEP_DAYS" -delete
echo "Pruned backups older than $KEEP_DAYS days"