db-backup.yml

name: Database Backup

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

jobs:
  backup:
    name: pg_dump - encrypt - upload to S3
    runs-on: ubuntu-latest
    timeout-minutes: 30

    steps:
      # ── 0. Install postgresql-client matching server version ───────────────
      - name: Install postgresql-client
        run: |
          sudo apt-get update -qq
          sudo apt-get install -y --no-install-recommends postgresql-client

      # ── 1. Dump ────────────────────────────────────────────────────────────
      - name: Run pg_dump
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          # Parse DATABASE_URL: postgresql://user:password@host:port/dbname
          # Use Python for reliable URL parsing - avoids sed edge cases with
          # special characters in passwords
          eval "$(python3 - <<'EOF'
import urllib.parse, os, shlex
url = os.environ["DATABASE_URL"]
p = urllib.parse.urlparse(url)
# unquote percent-encoded chars, then shell-quote for safe eval
print(f'export PGUSER={shlex.quote(urllib.parse.unquote(p.username or ""))}')
print(f'export PGPASSWORD={shlex.quote(urllib.parse.unquote(p.password or ""))}')
print(f'export PGHOST={shlex.quote(p.hostname or "")}')
print(f'export PGPORT={p.port or 5432}')
print(f'export PGDATABASE={shlex.quote(p.path.lstrip("/"))}')
EOF
          )"

          TIMESTAMP=$(date -u +"%Y-%m-%dT%H-%M-%SZ")
          DUMP_FILE="stellarstream-backup-${TIMESTAMP}.sql.gz"
          echo "DUMP_FILE=${DUMP_FILE}" >> "$GITHUB_ENV"
          echo "TIMESTAMP=${TIMESTAMP}" >> "$GITHUB_ENV"

          pg_dump \
            --format=plain \
            --no-owner \
            --no-acl \
            --no-password \
            | gzip -9 > "/tmp/${DUMP_FILE}"

          DUMP_SIZE=$(du -sh "/tmp/${DUMP_FILE}" | cut -f1)
          echo "Dump completed. Size: ${DUMP_SIZE}"

          # Fail fast if dump is suspiciously small (< 1KB = something went wrong)
          DUMP_BYTES=$(stat -c%s "/tmp/${DUMP_FILE}")
          if [ "$DUMP_BYTES" -lt 1024 ]; then
            echo "ERROR: Dump file is too small (${DUMP_BYTES} bytes). Aborting."
            exit 1
          fi

      # ── 2. Encrypt ─────────────────────────────────────────────────────────
      - name: Encrypt dump with GPG (AES-256)
        env:
          GPG_PASSPHRASE: ${{ secrets.BACKUP_GPG_PASSPHRASE }}
        run: |
          ENCRYPTED_FILE="${DUMP_FILE}.gpg"
          echo "ENCRYPTED_FILE=${ENCRYPTED_FILE}" >> "$GITHUB_ENV"

          echo "$GPG_PASSPHRASE" | gpg --batch \
              --yes \
              --passphrase-fd 0 \
              --pinentry-mode loopback \
              --cipher-algo AES256 \
              --symmetric \
              --output "/tmp/${ENCRYPTED_FILE}" \
              "/tmp/${DUMP_FILE}"

          # Verify the encrypted file is non-empty
          if [ ! -s "/tmp/${ENCRYPTED_FILE}" ]; then
            echo "ERROR: Encrypted file is empty or missing."
            exit 1
          fi

          echo "Encrypted size: $(du -sh /tmp/${ENCRYPTED_FILE} | cut -f1)"

          # Wipe the plaintext dump immediately
          shred -u "/tmp/${DUMP_FILE}"

      # ── 3. Upload to S3 ────────────────────────────────────────────────────
      - name: Upload to S3
        env:
          AWS_ACCESS_KEY_ID:     ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          AWS_DEFAULT_REGION:    ${{ secrets.AWS_REGION }}
          S3_BUCKET:             ${{ secrets.BACKUP_S3_BUCKET }}
        run: |
          # Path: backups/YYYY/MM/DD/<filename>
          S3_KEY="backups/$(date -u +%Y/%m/%d)/${ENCRYPTED_FILE}"

          aws s3 cp "/tmp/${ENCRYPTED_FILE}" "s3://${S3_BUCKET}/${S3_KEY}" \
            --sse AES256 \
            --storage-class STANDARD_IA \
            --metadata "timestamp=${TIMESTAMP},source=github-actions,project=stellarstream"

          echo "Uploaded: s3://${S3_BUCKET}/${S3_KEY}"

          # Wipe local encrypted file after upload
          shred -u "/tmp/${ENCRYPTED_FILE}"

      # ── 4. Verify upload ───────────────────────────────────────────────────
      - name: Verify S3 object exists
        env:
          AWS_ACCESS_KEY_ID:     ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          AWS_DEFAULT_REGION:    ${{ secrets.AWS_REGION }}
          S3_BUCKET:             ${{ secrets.BACKUP_S3_BUCKET }}
        run: |
          S3_KEY="backups/$(date -u +%Y/%m/%d)/${ENCRYPTED_FILE}"

          CONTENT_LENGTH=$(aws s3api head-object \
            --bucket "$S3_BUCKET" \
            --key "$S3_KEY" \
            --query 'ContentLength' \
            --output text)

          echo "Verified: s3://${S3_BUCKET}/${S3_KEY} (${CONTENT_LENGTH} bytes)"

          if [ "$CONTENT_LENGTH" -lt 1 ]; then
            echo "ERROR: S3 object has zero size."
            exit 1
          fi

      # ── 5. Notify on failure ───────────────────────────────────────────────
      - name: Notify on failure
        if: failure()
        env:
          SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }}
        run: |
          # SLACK_WEBHOOK_URL is optional - skip silently if not configured
          if [ -z "${SLACK_WEBHOOK_URL}" ]; then
            echo "SLACK_WEBHOOK_URL not set, skipping notification."
            exit 0
          fi

          curl -s -X POST "$SLACK_WEBHOOK_URL" \
            -H 'Content-type: application/json' \
            --data '{
              "text": ":rotating_light: *StellarStream DB Backup FAILED*",
              "attachments": [{
                "color": "danger",
                "fields": [
                  {"title": "Workflow", "value": "${{ github.workflow }}", "short": true},
                  {"title": "Run", "value": "<${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}|View logs>", "short": true}
                ]
              }]
            }'