backup.yml

name: Supabase Disaster Recovery Backup

on:
  # Run daily at 02:00 IST (20:30 UTC previous day)
  schedule:
    - cron: '30 20 * * *'
  
  # Allow manual trigger
  workflow_dispatch:

jobs:
  backup:
    runs-on: ubuntu-latest
    
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4
        with:
          token: ${{ secrets.GITHUB_TOKEN }}
      
      - name: Install PostgreSQL 17 client tools
        run: |
          # Add PostgreSQL 17 repository
          sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
          sudo apt-get update
          # Install PostgreSQL 17 client tools
          sudo apt-get install -y postgresql-client-17
          # Set up alternatives to use PostgreSQL 17 by default
          sudo update-alternatives --install /usr/bin/psql psql /usr/lib/postgresql/17/bin/psql 100
          sudo update-alternatives --install /usr/bin/pg_dump pg_dump /usr/lib/postgresql/17/bin/pg_dump 100
          sudo update-alternatives --install /usr/bin/pg_restore pg_restore /usr/lib/postgresql/17/bin/pg_restore 100
      
      - name: Verify tools installation
        run: |
          psql --version
          pg_dump --version
          pg_restore --version
      
      - name: Create backup directory
        run: mkdir -p supabase_snapshot
      
      - name: Verify SUPABASE_DB_URL is set
        env:
          SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }}
        run: |
          if [ -z "$SUPABASE_DB_URL" ]; then
            echo "❌ ERROR: SUPABASE_DB_URL is not set!"
            echo "Please add SUPABASE_DB_URL to your repository secrets."
            exit 1
          else
            echo "✅ SUPABASE_DB_URL is set (length: ${#SUPABASE_DB_URL} characters)"
          fi
      
      - name: Run backup script
        env:
          SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }}
        run: |
          chmod +x backup.sh
          # Explicitly export the environment variable
          export SUPABASE_DB_URL="${SUPABASE_DB_URL}"
          # Ensure PostgreSQL 17 binaries are in PATH
          export PATH="/usr/lib/postgresql/17/bin:$PATH"
          ./backup.sh
      
      - name: List backup files
        run: |
          echo "Backup files created:"
          ls -lh supabase_snapshot/
      
      - name: Encrypt backup files
        env:
          BACKUP_ENCRYPTION_KEY: ${{ secrets.BACKUP_ENCRYPTION_KEY }}
        run: |
          echo "🔒 Encrypting backup files..."
          
          # Check if encryption key is set
          if [ -z "$BACKUP_ENCRYPTION_KEY" ]; then
            echo "❌ ERROR: BACKUP_ENCRYPTION_KEY is not set!"
            echo "Please add BACKUP_ENCRYPTION_KEY to your repository secrets."
            echo "This is required to encrypt backups in a public repository."
            exit 1
          fi
          
          # Create encrypted archive
          tar -czf supabase_snapshot.tar.gz supabase_snapshot/
          
          # Encrypt using GPG with symmetric encryption
          echo "$BACKUP_ENCRYPTION_KEY" | gpg --batch --yes --passphrase-fd 0 --symmetric --cipher-algo AES256 -o supabase_snapshot.tar.gz.gpg supabase_snapshot.tar.gz
          
          # Remove unencrypted files
          rm -rf supabase_snapshot/
          rm supabase_snapshot.tar.gz
          
          echo "✅ Backup encrypted successfully"
          ls -lh supabase_snapshot.tar.gz.gpg
      
      - name: Upload encrypted backup artifacts
        uses: actions/upload-artifact@v4
        with:
          name: supabase-backup-encrypted-${{ github.run_number }}-${{ github.run_attempt }}
          path: supabase_snapshot.tar.gz.gpg
          retention-days: 30
          compression-level: 0
      
      - name: Generate job summary
        if: always()
        run: |
          echo "# Supabase Backup Summary" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "**Date:** $(date)" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "🔒 **Security:** Backup is encrypted with AES256" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          
          if [ -f supabase_snapshot.tar.gz.gpg ]; then
            echo "## Encrypted Backup File" >> $GITHUB_STEP_SUMMARY
            echo "" >> $GITHUB_STEP_SUMMARY
            echo "| File | Size |" >> $GITHUB_STEP_SUMMARY
            echo "|------|------|" >> $GITHUB_STEP_SUMMARY
            size=$(du -h supabase_snapshot.tar.gz.gpg | cut -f1)
            echo "| supabase_snapshot.tar.gz.gpg | $size |" >> $GITHUB_STEP_SUMMARY
            echo "" >> $GITHUB_STEP_SUMMARY
            echo "## How to Decrypt" >> $GITHUB_STEP_SUMMARY
            echo '```bash' >> $GITHUB_STEP_SUMMARY
            echo "# Download the artifact, then run:" >> $GITHUB_STEP_SUMMARY
            echo "gpg --decrypt supabase_snapshot.tar.gz.gpg > supabase_snapshot.tar.gz" >> $GITHUB_STEP_SUMMARY
            echo "tar -xzf supabase_snapshot.tar.gz" >> $GITHUB_STEP_SUMMARY
            echo '```' >> $GITHUB_STEP_SUMMARY
          fi
          
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "✅ Backup completed successfully!" >> $GITHUB_STEP_SUMMARY