backup.sh

#!/bin/bash
set -e

# ============================================
# CONFIGURATION
# ============================================
DB_PATH="/data/.flowise/database.sqlite"
BACKUP_INTERVAL=${BACKUP_INTERVAL_SECONDS:-86400}
NEON_CONNECTION="postgresql://${NEON_USER}@${NEON_HOST}/${NEON_DB}?sslmode=require"

log() {
  echo "[$(date +'%Y-%m-%d %H:%M:%S')] [BACKUP] $1"
}

# ============================================
# BACKUP FUNCTION
# ============================================
backup_to_neon() {
  # Prereqs
  if [ ! -f "$DB_PATH" ]; then
    return 0
  fi
  if [ -z "$NEON_PASSWORD" ] || [ -z "$NEON_HOST" ] || [ -z "$NEON_DB" ] || [ -z "$NEON_USER" ]; then
    return 0
  fi

  # Basic stats
  USER_COUNT=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM user;" 2>/dev/null || echo "0")
  ORG_COUNT=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM organization;" 2>/dev/null || echo "0")
  CF_COUNT=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM chat_flow;" 2>/dev/null || echo "0")
  CRED_COUNT=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM credential;" 2>/dev/null || echo "0")

  # Skip if nothing meaningful
  if [ "$USER_COUNT" -eq 0 ] && [ "$ORG_COUNT" -eq 0 ]; then
    return 0
  fi

  # Create FILTERED SQL dump:
  #  - Exclude chat history, agentflow execution history, and other transient tables
  #  NOTE: 'execution' was the root cause of DB bloat (62MB / 1727 rows of agentflow logs)
  sqlite3 "$DB_PATH" .dump 2>/dev/null | \
    grep -v "INSERT INTO \"execution\"" | \
    grep -v "INSERT INTO execution " | \
    grep -v "INSERT INTO execution_data" | \
    grep -v "INSERT INTO chat_message " | \
    grep -v "INSERT INTO chat_message_feedback" | \
    grep -v "INSERT INTO upsert_history" | \
    grep -v "INSERT INTO login_activity" | \
    grep -v "INSERT INTO lead" | \
    grep -v "CREATE TABLE sqlite_sequence" \
    > /tmp/flowise_filtered.sql 2>/dev/null || true

  BACKUP_SIZE=$(wc -c < /tmp/flowise_filtered.sql 2>/dev/null || echo "0")

  # Skip tiny/failed dumps
  if [ "$BACKUP_SIZE" -lt 500 ]; then
    log "⚠️  Dump too small (${BACKUP_SIZE}B) - skipping"
    rm -f /tmp/flowise_filtered.sql 2>/dev/null || true
    return 0
  fi

  # SAFETY GUARD: Warn and skip if dump is suspiciously large (> 5MB)
  # Anything over 5MB means something is leaking into the backup that shouldn't be.
  # Threshold: 5MB raw = ~6.6MB base64 = safe for 3 copies within Neon's 512MB free tier.
  MAX_DUMP_BYTES=5242880  # 5 MB
  if [ "$BACKUP_SIZE" -gt "$MAX_DUMP_BYTES" ]; then
    BACKUP_SIZE_MB=$((BACKUP_SIZE / 1048576))
    log "🚨 ABORT: Dump is ${BACKUP_SIZE_MB}MB — exceeds 5MB safety limit!"
    log "🚨 A table is leaking into the backup. Inspect SQLite with:"
    log "🚨   sqlite3 $DB_PATH '.tables'"
    log "🚨   sqlite3 $DB_PATH 'SELECT name, count(*) FROM sqlite_master WHERE type=\"table\"'"
    rm -f /tmp/flowise_filtered.sql 2>/dev/null || true
    return 0
  fi

  # Base64 encode (busybox-compatible)
  BASE64_CONTENT=$(cat /tmp/flowise_filtered.sql | base64 | tr -d '\n')

  # Upload to Neon
  PGPASSWORD="$NEON_PASSWORD" psql "$NEON_CONNECTION" -q -v ON_ERROR_STOP=1 <<EOF
    CREATE TABLE IF NOT EXISTS flowise_backups (
      id SERIAL PRIMARY KEY,
      backup_date TIMESTAMP DEFAULT NOW(),
      sql_content_b64 TEXT,
      metadata JSONB
    );
    -- Keep only 3 copies (was 7 - each snapshot ~200KB now, 3 copies << 1MB total)
    DELETE FROM flowise_backups
    WHERE id NOT IN (
      SELECT id FROM flowise_backups
      ORDER BY backup_date DESC
      LIMIT 3
    );
    INSERT INTO flowise_backups (sql_content_b64, metadata)
    VALUES (
      '${BASE64_CONTENT}',
      '{"users": ${USER_COUNT}, "orgs": ${ORG_COUNT}, "flows": ${CF_COUNT}, "creds": ${CRED_COUNT}, "bytes": ${BACKUP_SIZE}, "filtered": true}'::jsonb
    );
EOF

  if [ $? -eq 0 ]; then
    BACKUP_SIZE_KB=$((BACKUP_SIZE / 1024))
    log "✅ Backup complete: ${BACKUP_SIZE_KB}KB | $CF_COUNT flows, $CRED_COUNT creds (history filtered)"
  else
    log "❌ Backup failed (psql error)"
  fi

  rm -f /tmp/flowise_filtered.sql 2>/dev/null || true
}

# ============================================
# MAIN LOOP
# ============================================
sleep 120  # let Flowise initialize

backup_to_neon

INTERVAL_HOURS=$((BACKUP_INTERVAL / 3600))
log "🔄 Backup loop started (every ${INTERVAL_HOURS}h)"

while true; do
  sleep "$BACKUP_INTERVAL"
  backup_to_neon
done