`scripts/devops/backup-database.sh`

132 lines
#!/bin/bash
#
# LUXVAULT Database Backup Script
# Creates timestamped backups of SQLite database and exports
# Runs daily at 2am via cron
#

set -euo pipefail

PROJECT_DIR="/root/Projects/handbag-auth-nextjs"
DATA_DIR="$PROJECT_DIR/data/auction-history"
BACKUP_DIR="$PROJECT_DIR/backups"
LOG_FILE="$PROJECT_DIR/logs/backup.log"

# Backup retention (days)
RETENTION_DAYS=30

# Database files to backup
DB_FILE="$DATA_DIR/auctions.db"
CSV_FILE="$DATA_DIR/auction-history.csv"
LOG_FILE_SRC="$DATA_DIR/scraper.log"

# Timestamp for backup
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_NAME="luxvault_backup_${TIMESTAMP}"

# Logging
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"
}

error() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] ERROR: $*" | tee -a "$LOG_FILE" >&2
}

# Create backup directory
mkdir -p "$BACKUP_DIR"

log "========================================="
log "Starting database backup: $BACKUP_NAME"

# Check if database exists
if [ ! -f "$DB_FILE" ]; then
    error "Database file not found: $DB_FILE"
    exit 1
fi

# Create backup subdirectory
BACKUP_PATH="$BACKUP_DIR/$BACKUP_NAME"
mkdir -p "$BACKUP_PATH"

# Backup SQLite database (with integrity check)
log "Backing up SQLite database..."
if sqlite3 "$DB_FILE" "PRAGMA integrity_check;" | grep -q "ok"; then
    cp "$DB_FILE" "$BACKUP_PATH/auctions.db"
    log "✓ Database backed up successfully"
else
    error "Database integrity check failed!"
    exit 1
fi

# Backup CSV file
if [ -f "$CSV_FILE" ]; then
    log "Backing up CSV file..."
    cp "$CSV_FILE" "$BACKUP_PATH/auction-history.csv"
    log "✓ CSV backed up successfully"
fi

# Backup scraper log
if [ -f "$LOG_FILE_SRC" ]; then
    log "Backing up scraper log..."
    cp "$LOG_FILE_SRC" "$BACKUP_PATH/scraper.log"
    log "✓ Log backed up successfully"
fi

# Generate backup metadata
cat > "$BACKUP_PATH/metadata.json" <<EOF
{
  "backup_name": "$BACKUP_NAME",
  "timestamp": "$(date -Iseconds)",
  "source": "$PROJECT_DIR",
  "files": {
    "database": "auctions.db",
    "csv": "auction-history.csv",
    "log": "scraper.log"
  },
  "database_size": $(stat -f%z "$DB_FILE" 2>/dev/null || stat -c%s "$DB_FILE"),
  "record_count": $(sqlite3 "$DB_FILE" "SELECT COUNT(*) FROM auctions;")
}
EOF

# Compress backup
log "Compressing backup..."
cd "$BACKUP_DIR"
tar -czf "${BACKUP_NAME}.tar.gz" "$BACKUP_NAME"
COMPRESSED_SIZE=$(stat -f%z "${BACKUP_NAME}.tar.gz" 2>/dev/null || stat -c%s "${BACKUP_NAME}.tar.gz")
log "✓ Backup compressed: ${BACKUP_NAME}.tar.gz ($(echo "scale=2; $COMPRESSED_SIZE / 1024 / 1024" | bc)MB)"

# Remove uncompressed backup directory
rm -rf "$BACKUP_PATH"

# Clean up old backups
log "Cleaning up old backups (retention: ${RETENTION_DAYS} days)..."
find "$BACKUP_DIR" -name "luxvault_backup_*.tar.gz" -mtime +$RETENTION_DAYS -delete
REMAINING_BACKUPS=$(find "$BACKUP_DIR" -name "luxvault_backup_*.tar.gz" | wc -l)
log "✓ Cleanup complete. Remaining backups: $REMAINING_BACKUPS"

# Calculate total backup size
TOTAL_SIZE=$(du -sh "$BACKUP_DIR" | cut -f1)
log "Total backup storage: $TOTAL_SIZE"

# Verify backup integrity
log "Verifying backup integrity..."
if tar -tzf "$BACKUP_DIR/${BACKUP_NAME}.tar.gz" > /dev/null 2>&1; then
    log "✓ Backup verification successful"
else
    error "Backup verification failed!"
    exit 1
fi

log "Backup completed successfully: ${BACKUP_NAME}.tar.gz"
log "========================================="

# Optional: Upload to remote storage (uncomment and configure)
# if command -v aws &> /dev/null; then
#     log "Uploading to S3..."
#     aws s3 cp "$BACKUP_DIR/${BACKUP_NAME}.tar.gz" s3://your-bucket/luxvault-backups/
#     log "✓ Uploaded to S3"
# fi

exit 0