backup.sh

#!/bin/bash
set -e

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_to_neon() {
  # Early exit if no database
  if [ ! -f "$DB_PATH" ]; then
    return 0
  fi

  # Early exit if Neon not configured
  if [ -z "$NEON_PASSWORD" ] || [ -z "$NEON_HOST" ]; then
    return 0
  fi

  # Get metadata
  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 empty database (nothing to backup)
  if [ "$USER_COUNT" -eq 0 ] && [ "$ORG_COUNT" -eq 0 ]; then
    return 0
  fi

  # ✅ FIX: Fail if SQLite dump fails (don't use || true)
  if ! sqlite3 "$DB_PATH" .dump > /tmp/flowise_backup.sql 2>/dev/null; then
    log "❌ SQLite dump failed - skipping backup"
    rm -f /tmp/flowise_backup.sql 2>/dev/null || true
    return 1
  fi

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

  # Validate dump size
  if [ "$BACKUP_SIZE" -lt 1000 ]; then
    log "⚠️  Backup too small (${BACKUP_SIZE} bytes) - skipping"
    rm -f /tmp/flowise_backup.sql 2>/dev/null || true
    return 0
  fi

  # Encode to base64
  BASE64_CONTENT=$(base64 -w 0 /tmp/flowise_backup.sql)

  # ✅ CRITICAL FIX: Use psql properly without grep pipeline
  # ✅ SECURITY FIX: Use psql variable instead of direct substitution
  if ! PGPASSWORD="$NEON_PASSWORD" psql "$NEON_CONNECTION" -v ON_ERROR_STOP=1 \
    -v backup_b64="$BASE64_CONTENT" \
    -v user_count="$USER_COUNT" \
    -v org_count="$ORG_COUNT" \
    -v flow_count="$CF_COUNT" \
    -v cred_count="$CRED_COUNT" \
    -v backup_bytes="$BACKUP_SIZE" \
    <<EOF 2>&1
    CREATE TABLE IF NOT EXISTS flowise_backups (
      id SERIAL PRIMARY KEY,
      backup_date TIMESTAMP DEFAULT NOW(),
      sql_content_b64 TEXT,
      metadata JSONB
    );
    DELETE FROM flowise_backups WHERE id NOT IN (
      SELECT id FROM flowise_backups ORDER BY backup_date DESC LIMIT 7
    );
    INSERT INTO flowise_backups (sql_content_b64, metadata) 
    VALUES (
      :'backup_b64',
      jsonb_build_object(
        'users', :user_count::int,
        'orgs', :org_count::int, 
        'flows', :flow_count::int,
        'creds', :cred_count::int,
        'bytes', :backup_bytes::int
      )
    );
EOF
  then
    log "❌ Backup upload FAILED to Neon"
    rm -f /tmp/flowise_backup.sql 2>/dev/null || true
    return 1
  fi

  # Success
  BACKUP_SIZE_KB=$((BACKUP_SIZE / 1024))
  log "✅ Backup complete: ${BACKUP_SIZE_KB}KB | $CF_COUNT flows, $CRED_COUNT creds"

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

# Initial delay before first backup
sleep 120
backup_to_neon

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

# Continuous backup loop
while true; do
  sleep "$BACKUP_INTERVAL"
  backup_to_neon || log "⚠️  Backup cycle failed - will retry next interval"
done