supabase-backup.yml

name: Supabase Database Backup

on:
  schedule:
    # Runs daily at 02:00 UTC
    - cron: '0 2 * * *'
  workflow_dispatch:
    inputs:
      reason:
        description: 'Reason for manual backup trigger'
        required: false
        default: 'Manual backup'

concurrency:
  group: supabase-backup
  cancel-in-progress: false

permissions:
  contents: read

jobs:
  backup:
    name: pg_dump → Cloudflare R2
    runs-on: ubuntu-latest
    timeout-minutes: 30

    steps:
      # ── 1. Setup ──────────────────────────────────────────────────────────────
      - name: Checkout repository
        uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7

      - name: Install PostgreSQL client tools
        run: |
          sudo apt-get update -qq
          sudo apt-get install -y --no-install-recommends postgresql-client

      - name: Install AWS CLI (for R2 S3-compatible upload)
        run: |
          curl -fsSL "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o awscliv2.zip
          unzip -q awscliv2.zip
          sudo ./aws/install --update
          aws --version

      # ── 2. Generate backup ────────────────────────────────────────────────────
      - name: Set backup filename
        id: filename
        run: |
          TIMESTAMP=$(date -u '+%Y-%m-%d-%H%M%S')
          echo "BACKUP_FILE=tikka-backup-${TIMESTAMP}.sql.gz" >> "$GITHUB_OUTPUT"
          echo "TIMESTAMP=${TIMESTAMP}" >> "$GITHUB_OUTPUT"

      - name: Run pg_dump and compress
        env:
          SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }}
        run: |
          echo "Starting pg_dump at $(date -u)"
          pg_dump \
            --dbname="$SUPABASE_DB_URL" \
            --format=plain \
            --no-owner \
            --no-acl \
            --schema=public \
          | gzip -9 > "${{ steps.filename.outputs.BACKUP_FILE }}"

          BACKUP_SIZE=$(du -sh "${{ steps.filename.outputs.BACKUP_FILE }}" | cut -f1)
          echo "Backup created: ${{ steps.filename.outputs.BACKUP_FILE }} (${BACKUP_SIZE})"

      - name: Validate backup file
        run: |
          FILE="${{ steps.filename.outputs.BACKUP_FILE }}"
          FILE_SIZE=$(stat -c%s "$FILE" 2>/dev/null || stat -f%z "$FILE" 2>/dev/null)

          if [ "$FILE_SIZE" -lt 1024 ]; then
            echo "::error::Backup file is suspiciously small (${FILE_SIZE} bytes). Aborting upload."
            exit 1
          fi

          if ! gzip -t "$FILE" 2>/dev/null; then
            echo "::error::Backup file is not valid gzip. Aborting upload."
            exit 1
          fi

          echo "Backup validation passed: ${FILE_SIZE} bytes, valid gzip"

      # ── 3. Upload to Cloudflare R2 ────────────────────────────────────────────
      - name: Upload backup to Cloudflare R2
        env:
          AWS_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }}
          AWS_DEFAULT_REGION: auto
          R2_ENDPOINT_URL: ${{ secrets.R2_ENDPOINT_URL }}
          R2_BUCKET_NAME: ${{ secrets.R2_BUCKET_NAME }}
        run: |
          aws s3 cp \
            "${{ steps.filename.outputs.BACKUP_FILE }}" \
            "s3://${R2_BUCKET_NAME}/daily/${{ steps.filename.outputs.BACKUP_FILE }}" \
            --endpoint-url "${R2_ENDPOINT_URL}" \
            --checksum-algorithm SHA256

          echo "Uploaded to R2: s3://${R2_BUCKET_NAME}/daily/${{ steps.filename.outputs.BACKUP_FILE }}"

      - name: Verify upload integrity
        env:
          AWS_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }}
          AWS_DEFAULT_REGION: auto
          R2_ENDPOINT_URL: ${{ secrets.R2_ENDPOINT_URL }}
          R2_BUCKET_NAME: ${{ secrets.R2_BUCKET_NAME }}
        run: |
          REMOTE_SIZE=$(aws s3 head-object \
            --key "daily/${{ steps.filename.outputs.BACKUP_FILE }}" \
            --bucket "${R2_BUCKET_NAME}" \
            --endpoint-url "${R2_ENDPOINT_URL}" \
            --query 'ContentLength' \
            --output text)
          LOCAL_SIZE=$(stat -c%s "${{ steps.filename.outputs.BACKUP_FILE }}" 2>/dev/null || stat -f%z "${{ steps.filename.outputs.BACKUP_FILE }}" 2>/dev/null)

          if [ "$REMOTE_SIZE" != "$LOCAL_SIZE" ]; then
            echo "::error::Upload verification failed: remote=${REMOTE_SIZE}, local=${LOCAL_SIZE}"
            exit 1
          fi
          echo "Upload verified: ${LOCAL_SIZE} bytes"

      # ── 4. Prune old backups (retain last 30 days) ────────────────────────────
      - name: Prune backups older than 30 days
        env:
          AWS_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }}
          AWS_DEFAULT_REGION: auto
          R2_ENDPOINT_URL: ${{ secrets.R2_ENDPOINT_URL }}
          R2_BUCKET_NAME: ${{ secrets.R2_BUCKET_NAME }}
        run: |
          CUTOFF=$(date -u -d '30 days ago' '+%Y-%m-%d')
          echo "Pruning backups older than ${CUTOFF}..."

          aws s3 ls "s3://${R2_BUCKET_NAME}/daily/" \
            --endpoint-url "${R2_ENDPOINT_URL}" \
          | awk '{print $4}' \
          | grep -E '^tikka-backup-[0-9]{4}-[0-9]{2}-[0-9]{2}' \
          | while read -r KEY; do
              FILE_DATE=$(echo "$KEY" | grep -oE '[0-9]{4}-[0-9]{2}-[0-9]{2}' | head -1)
              if [[ "$FILE_DATE" < "$CUTOFF" ]]; then
                echo "  Deleting old backup: ${KEY}"
                aws s3 rm "s3://${R2_BUCKET_NAME}/daily/${KEY}" \
                  --endpoint-url "${R2_ENDPOINT_URL}"
              fi
            done

          echo "Pruning complete."

      # ── 5. Cleanup local file ─────────────────────────────────────────────────
      - name: Clean up local dump file
        if: always()
        run: rm -f "${{ steps.filename.outputs.BACKUP_FILE }}"

      # ── 6. Job summary ────────────────────────────────────────────────────────
      - name: Write job summary
        if: always()
        run: |
          cat >> "$GITHUB_STEP_SUMMARY" <<EOF
          ## Tikka Supabase Backup Report

          | Field      | Value |
          |------------|-------|
          | **Status** | ${{ job.status }} |
          | **File**   | \`${{ steps.filename.outputs.BACKUP_FILE }}\` |
          | **Bucket** | \`daily/${{ steps.filename.outputs.BACKUP_FILE }}\` |
          | **Run at** | $(date -u) |
          | **Trigger**| \`${{ github.event_name }}\` |
          EOF

  # ── Failure notification job ─────────────────────────────────────────────────
  notify-failure:
    name: Notify on failure
    needs: backup
    if: failure() && github.event_name == 'schedule'
    runs-on: ubuntu-latest
    permissions:
      issues: write
    steps:
      - name: Notify Slack on backup failure
        if: ${{ secrets.SLACK_WEBHOOK_URL != '' }}
        uses: slackapi/slack-github-action@dcb1066f776dd043e64d0e8ba94ca15cc7e1875d # v4.0.0
        with:
          webhook: ${{ secrets.SLACK_WEBHOOK_URL }}
          webhook-type: incoming-webhook
          payload: |
            {
              "text": "*Tikka Supabase Backup FAILED*",
              "blocks": [
                {
                  "type": "header",
                  "text": {
                    "type": "plain_text",
                    "text": "Backup Workflow Failed",
                    "emoji": true
                  }
                },
                {
                  "type": "section",
                  "fields": [
                    { "type": "mrkdwn", "text": "*Repo:*\n${{ github.repository }}" },
                    { "type": "mrkdwn", "text": "*Branch:*\n${{ github.ref_name }}" },
                    { "type": "mrkdwn", "text": "*Triggered:*\n${{ github.event.schedule || 'manual' }}" },
                    { "type": "mrkdwn", "text": "*Run:*\n<${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}|View Logs>" }
                  ]
                }
              ]
            }

      - name: Create GitHub Issue on failure
        uses: actions/github-script@3a2844b7e9c422d3c10d287c895573f7108da1b3 # v9
        with:
          script: |
            const title = `Backup workflow failed — ${new Date().toISOString().split('T')[0]}`;
            const body = `## Backup Failure Report\n\n` +
              `**Workflow:** [Supabase Database Backup](${context.serverUrl}/${context.repo.owner}/${context.repo.repo}/actions/runs/${context.runId})\n` +
              `**Scheduled run:** ${context.eventName === 'schedule' ? 'Yes' : 'No (manual)'}\n` +
              `**Failed at:** ${new Date().toISOString()}\n\n` +
              `### Required Actions\n` +
              `1. Check the [workflow logs](${context.serverUrl}/${context.repo.owner}/${context.repo.repo}/actions/runs/${context.runId})\n` +
              `2. Verify SUPABASE_DB_URL secret is still valid\n` +
              `3. Check Cloudflare R2 credentials\n` +
              `4. Confirm a recent backup exists in R2\n\n` +
              `### Impact\n` +
              `No automatic daily backup was created. Manual backup may be required.`;

            await github.rest.issues.create({
              owner: context.repo.owner,
              repo: context.repo.repo,
              title,
              body,
              labels: ['backup-failure', 'devops', 'urgent']
            });