backup.yml

name: Supabase Scheduled Database Backup

on:
  schedule:
    # Run every Sunday at 02:00 UTC
    - cron: '0 2 * * 0'
  workflow_dispatch: # Allows manual trigger from GitHub UI

jobs:
  backup:
    name: Database Dump Backup
    runs-on: ubuntu-latest
    timeout-minutes: 10

    steps:
      - name: Checkout Code
        uses: actions/checkout@v4

      - name: Install PostgreSQL Client
        run: |
          sudo apt-get update
          sudo apt-get install -y postgresql-client

      - name: Run Database Dump & Encrypt
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          BACKUP_PASSPHRASE: ${{ secrets.BACKUP_PASSPHRASE }}
        run: |
          if [ -z "$DATABASE_URL" ]; then
            echo "DATABASE_URL secret is not defined. Skipping database dump."
            exit 0
          fi
          
          mkdir -p backups
          TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
          BACKUP_FILE="backups/clientecho_backup_${TIMESTAMP}.sql"
          
          echo "Starting pg_dump..."
          pg_dump "$DATABASE_URL" --no-owner --no-acl -f "$BACKUP_FILE"
          gzip "$BACKUP_FILE"
          
          # Encrypt with AES-256 using secret passphrase
          if [ -n "$BACKUP_PASSPHRASE" ]; then
            echo "Encrypting backup with AES-256..."
            gpg --symmetric --cipher-algo AES256 --batch --passphrase "$BACKUP_PASSPHRASE" -o "${BACKUP_FILE}.gz.gpg" "${BACKUP_FILE}.gz"
            rm -f "${BACKUP_FILE}.gz" # Remove unencrypted file
            echo "Encrypted backup ready: ${BACKUP_FILE}.gz.gpg"
          else
            echo "::warning::BACKUP_PASSPHRASE not set! In a public repository, skipping unencrypted artifact upload for safety."
            rm -f "${BACKUP_FILE}.gz"
            exit 0
          fi

      - name: Upload Encrypted Backup Artifact
        uses: actions/upload-artifact@v4
        with:
          name: db-backup-${{ github.run_id }}
          path: backups/*.sql.gz.gpg
          retention-days: 30