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

  if [ -z "$NEON_PASSWORD" ] || [ -z "$NEON_HOST" ]; then
    return 0
  fi

  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")

  if [ "$USER_COUNT" -eq 0 ] && [ "$ORG_COUNT" -eq 0 ]; then
    return 0
  fi

  sqlite3 "$DB_PATH" .dump > /tmp/flowise_backup.sql 2>/dev/null || true

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

  if [ "$BACKUP_SIZE" -lt 1000 ]; then
    rm -f /tmp/flowise_backup.sql 2>/dev/null || true
    return 0
  fi

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

  PGPASSWORD="$NEON_PASSWORD" psql "$NEON_CONNECTION" -v ON_ERROR_STOP=1 <<EOF 2>&1 | grep -i "error" && log "❌ Upload error" || true
    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 ('$BASE64_CONTENT', '{"users": $USER_COUNT, "orgs": $ORG_COUNT, "flows": $CF_COUNT, "creds": $CRED_COUNT, "bytes": $BACKUP_SIZE}'::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"
  else
    log "❌ Backup failed"
  fi

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

sleep 120
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