db-backup.yml

name: Automated DB Backup

on:
  schedule:
    # Midnight UTC, every day. GitHub runs scheduled jobs on a best-effort basis
    # and may delay them under load, so treat the timestamp as approximate.
    - cron: '0 0 * * *'
  workflow_dispatch:
    inputs:
      dry_run:
        description: 'Dump, encrypt and verify, but do not write to S3'
        type: boolean
        default: false

permissions:
  contents: read
  # Needed by the failure step below, which files an issue when a backup fails.
  issues: write

# A backup run should never overlap with the previous one.
concurrency:
  group: db-backup
  cancel-in-progress: false

jobs:
  backup:
    name: Dump, encrypt and upload to S3
    runs-on: ubuntu-latest
    if: ${{ secrets.AWS_ACCESS_KEY_ID != '' }}
    timeout-minutes: 30
    steps:
      - name: Checkout repo
        uses: actions/checkout@v4

      # The runner's default postgresql-client tends to lag the managed Postgres
      # server, and pg_dump refuses to dump a newer server ("server version
      # mismatch"). Install the matching major from the PGDG archive instead.
      # Override with the PG_MAJOR repository variable when the server upgrades.
      - name: Install PostgreSQL client ${{ vars.PG_MAJOR || '17' }}
        env:
          PG_MAJOR: ${{ vars.PG_MAJOR || '17' }}
        run: |
          set -euo pipefail
          KEYRING=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
          sudo install -d /usr/share/postgresql-common/pgdg
          sudo curl -fsSL -o "$KEYRING" https://www.postgresql.org/media/keys/ACCC4CF8.asc
          printf 'deb [signed-by=%s] https://apt.postgresql.org/pub/repos/apt %s-pgdg main\n' \
            "$KEYRING" "$(lsb_release -cs)" \
            | sudo tee /etc/apt/sources.list.d/pgdg.list > /dev/null
          sudo apt-get update
          sudo apt-get install -y --no-install-recommends "postgresql-client-${PG_MAJOR}"
          pg_dump --version

      - name: Configure AWS credentials
        uses: aws-actions/configure-aws-credentials@v4
        with:
          aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
          aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          aws-region: ${{ vars.AWS_REGION || 'us-east-1' }}

      - name: Run backup
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          S3_BUCKET: ${{ secrets.S3_BUCKET }}
          BACKUP_ENCRYPTION_KEY: ${{ secrets.BACKUP_ENCRYPTION_KEY }}
          BACKUP_RETENTION_DAYS: ${{ vars.BACKUP_RETENTION_DAYS || '30' }}
          BACKUP_EXCLUDE_SCHEMAS: ${{ vars.BACKUP_EXCLUDE_SCHEMAS || '' }}
          BACKUP_DRY_RUN: ${{ inputs.dry_run && '1' || '0' }}
        run: bash scripts/backup.sh

      # A nightly backup that fails quietly is indistinguishable from one that
      # works, right up until a restore is needed. Surface it as an issue.
      - name: Report failure
        if: failure() && github.event_name == 'schedule'
        uses: actions/github-script@v7
        with:
          script: |
            const title = 'Nightly database backup failed';
            const runUrl = `${context.serverUrl}/${context.repo.owner}/${context.repo.repo}` +
              `/actions/runs/${context.runId}`;
            const body = [
              `The scheduled database backup failed on ${new Date().toISOString()}.`,
              '',
              `Run: ${runUrl}`,
              '',
              'The database is running without a fresh off-site backup until this is fixed.',
              'See docs/disaster-recovery.md for the runbook.',
            ].join('\n');

            // Reuse an existing open report rather than filing one per night.
            const existing = await github.rest.issues.listForRepo({
              owner: context.repo.owner,
              repo: context.repo.repo,
              state: 'open',
              labels: 'backup-failure',
            });

            if (existing.data.length > 0) {
              await github.rest.issues.createComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                issue_number: existing.data[0].number,
                body,
              });
            } else {
              await github.rest.issues.create({
                owner: context.repo.owner,
                repo: context.repo.repo,
                title,
                body,
                labels: ['backup-failure'],
              });
            }