# ROBOTS ON PAYROLL / THE VAULT
# What this is: a GitHub Actions workflow that pg_dumps your Postgres to Cloudflare R2 nightly, with 30-day retention and a failure alert.
# How to use it: download this file and give it to your AI (Claude, ChatGPT,
# Cursor, Lovable chat) as a reference. Say: "Use this as my playbook for
# setting up my nightly database backup."
# Latest version + full guide: https://robotsonpayroll.com/resources/github-actions-runbooks
#
# nightly-backup.yml
# Nightly pg_dump of your Postgres (Supabase / Neon / any Postgres) to
# Cloudflare R2, with 30-day retention pruning and a failure alert.
#
# SECRETS REQUIRED:
#   DATABASE_URL         - Postgres connection string. For Supabase use the
#                          DIRECT (non-pooled, port 5432) connection string;
#                          pg_dump through a transaction pooler can fail.
#   R2_ACCOUNT_ID        - Cloudflare account ID (dashboard -> R2 -> right sidebar)
#   R2_ACCESS_KEY_ID     - from an R2 API token, scoped to ONE bucket, Object Read & Write only
#   R2_SECRET_ACCESS_KEY - same token
#   ALERT_WEBHOOK_URL    - Slack/Discord incoming webhook
#
# ONE-TIME SETUP:
#   1. Create an R2 bucket (example name below: "db-backups"). R2 has zero
#      egress fees, which is why it beats S3 for backups you hope to never read.
#   2. Create an R2 API token: Object Read & Write, limited to that one bucket.
#      R2 does not accept GitHub OIDC, so these are real keys; scope them hard.
#   3. Change BUCKET below to your bucket name.

name: Nightly database backup

on:
  schedule:
    # 03:17 UTC. Odd minute on purpose: :00 crons queue behind everyone
    # else's :00 crons and get delayed 5-30 min, sometimes skipped entirely.
    - cron: "17 3 * * *"
  workflow_dispatch: {} # always keep a manual trigger next to schedule

env:
  BUCKET: db-backups # <- your R2 bucket name
  PREFIX: postgres # folder inside the bucket

jobs:
  backup:
    runs-on: ubuntu-latest
    permissions:
      contents: read

    steps:
      # Match pg_dump's major version to your server. Supabase and Neon run
      # Postgres 15-17 depending on project age; ubuntu-latest ships an older
      # client, and pg_dump refuses to dump a server NEWER than itself.
      # Installing the v17 client dumps any server up to 17.
      - name: Install Postgres 17 client
        run: |
          sudo apt-get -qq update
          sudo apt-get -qq install -y postgresql-common
          sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
          sudo apt-get -qq install -y postgresql-client-17

      - name: Dump database
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          STAMP=$(date -u +%Y%m%dT%H%M%SZ)
          echo "STAMP=$STAMP" >> "$GITHUB_ENV"
          # --no-owner --no-privileges: restores cleanly to a different
          # project/role, which is exactly the disaster scenario.
          pg_dump "$DATABASE_URL" --no-owner --no-privileges \
            | gzip > "backup-${STAMP}.sql.gz"
          ls -lh backup-*.sql.gz

      # Sanity check: an "empty" dump usually means a wrong URL or a pooler
      # issue, and it exits 0 anyway. Fail if the file is suspiciously small.
      - name: Verify dump is not empty
        run: |
          SIZE=$(stat -c%s backup-${STAMP}.sql.gz)
          echo "Dump size: $SIZE bytes"
          if [ "$SIZE" -lt 1024 ]; then
            echo "Dump under 1KB, something is wrong"; exit 1
          fi

      # AWS CLI is preinstalled on ubuntu-latest and speaks R2's S3 API.
      # The two CHECKSUM env vars are REQUIRED: AWS CLI v2.23+ sends CRC32
      # integrity checksums by default and R2 rejects them.
      - name: Upload 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
          AWS_REQUEST_CHECKSUM_CALCULATION: when_required
          AWS_RESPONSE_CHECKSUM_VALIDATION: when_required
          ENDPOINT: https://${{ secrets.R2_ACCOUNT_ID }}.r2.cloudflarestorage.com
        run: |
          aws s3 cp "backup-${STAMP}.sql.gz" \
            "s3://${BUCKET}/${PREFIX}/backup-${STAMP}.sql.gz" \
            --endpoint-url "$ENDPOINT"

      # ---- 30-DAY RETENTION ----
      # Delete objects older than 30 days. rclone does this in one line and
      # its Cloudflare provider mode auto-handles R2 quirks.
      - name: Prune backups older than 30 days
        env:
          RCLONE_CONFIG_R2_TYPE: s3
          RCLONE_CONFIG_R2_PROVIDER: Cloudflare
          RCLONE_CONFIG_R2_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }}
          RCLONE_CONFIG_R2_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }}
          RCLONE_CONFIG_R2_ENDPOINT: https://${{ secrets.R2_ACCOUNT_ID }}.r2.cloudflarestorage.com
        run: |
          curl -fsSL https://rclone.org/install.sh | sudo bash
          rclone delete "r2:${BUCKET}/${PREFIX}/" --min-age 30d --s3-no-check-bucket
          echo "Current backups:"
          rclone ls "r2:${BUCKET}/${PREFIX}/"

      # Optional dead-man's-switch: GitHub silently skips crons under load and
      # auto-disables schedules after 60 days of repo inactivity. Pinging an
      # external monitor (healthchecks.io free tier) on success means you get
      # an email when the backup STOPS happening, not just when it fails.
      # - name: Ping healthcheck
      #   run: curl -fsS https://hc-ping.com/YOUR-UUID-HERE

      - name: Alert on failure
        if: failure()
        env:
          WEBHOOK: ${{ secrets.ALERT_WEBHOOK_URL }}
        run: |
          msg="Nightly DB backup FAILED for ${GITHUB_REPOSITORY}. Run: ${GITHUB_SERVER_URL}/${GITHUB_REPOSITORY}/actions/runs/${GITHUB_RUN_ID}"
          curl -sS -X POST -H 'Content-Type: application/json' \
            -d "{\"text\": \"$msg\", \"content\": \"$msg\"}" \
            "$WEBHOOK"

# RESTORE TEST (do this once a quarter, on the calendar, for real):
#   1. Download the latest backup from R2.
#   2. Create a throwaway Postgres (new Neon branch or local Docker).
#   3. gunzip -c backup-XXXX.sql.gz | psql "$THROWAWAY_URL"
#   4. Row-count 2-3 important tables against prod.
# A backup you have never restored is a hope, not a backup.