backup.yml

name: Scheduled DB Backup

# Daily portable dump of the production Postgres database.
#
# WHERE BACKUPS ACTUALLY COME FROM
#   The managed cluster's own snapshots are the PRIMARY backup and are
#   not this workflow's job. This workflow produces the thing snapshots
#   can't: a portable pg_dump that restores onto any Postgres anywhere,
#   so a Fly account/region loss can't take the backups down with the
#   primary. A missing BACKUP_ENCRYPTION_KEY therefore no longer means
#   "no backup" — only "no off-platform copy".
#
# What it does:
#   1. Runs backend/scripts/backup_db.sh ON the Fly machine (pg_dump
#      custom format -> pg_restore --list verification -> prune). The
#      machine already holds DATABASE_URL, so the credential stays in
#      Fly and is never copied into GitHub. Dumps land in /data/backups.
#   2. If the BACKUP_ENCRYPTION_KEY repo secret is set: pulls the newest
#      dump off the machine, encrypts it with AES-256 (openssl, PBKDF2),
#      and stores it as a GitHub Actions artifact (30-day retention).
#      The artifact is ciphertext only; the repo is public, so an
#      unencrypted DB must NEVER be uploaded — that is why this step is
#      gated on the key existing.
#   3. Prints the cluster's own snapshot list for visibility.
#
# Restore: see docs/runbooks/DISASTER_RECOVERY.md (includes the decrypt
# one-liner and backend/scripts/restore_db.sh).
#
# (Before 2026-09 this backed up a SQLite file. The database moved to
# Postgres; the workflow's shape is deliberately unchanged.)

on:
  schedule:
    - cron: "17 9 * * *" # daily 09:17 UTC (off the top-of-hour GH congestion)
  workflow_dispatch:

# Least-privilege token. Without this block a job gets whatever the
# repository's default GITHUB_TOKEN scope is, which is broader than
# anything here needs — CodeQL's actions/missing-workflow-permissions
# flagged every job in this file. Nothing here writes through the API:
# no `gh` calls, no git push, no package publish. Artifact upload does
# not need a contents scope either (it uses the runtime token).
#
# A job that genuinely needs more should declare it at the JOB level
# rather than widening this.
permissions:
  contents: read

jobs:
  backup:
    runs-on: ubuntu-latest
    env:
      FLY_API_TOKEN: ${{ secrets.FLY_API_TOKEN }}
      BACKUP_KEY: ${{ secrets.BACKUP_ENCRYPTION_KEY }}
    steps:
      - uses: superfly/flyctl-actions/setup-flyctl@master

      - name: Run pg_dump on the machine
        run: |
          flyctl ssh console -a sentinel-command -C "bash /app/scripts/backup_db.sh"

      # Visibility only — never fails the job. But it must not fail
      # SILENTLY either: the database now lives on a DIFFERENT Fly app
      # (sentinel-postgres) than this one, and an app-scoped FLY_API_TOKEN
      # cannot see another app — it returns `Could not find App`, which
      # with a bare `|| true` would look identical to "no snapshots".
      # Verified: a deploy token scoped to sentinel-command can ssh into
      # sentinel-command but cannot list the database app's volumes.
      # So say which of the two happened.
      - name: List cluster snapshots (visibility)
        run: |
          if out=$(flyctl volumes list -a sentinel-postgres 2>&1); then
            echo "$out"
          else
            echo "$out"
            echo "::warning::Could not read sentinel-postgres volumes. If this says" \
              "'Could not find App', FLY_API_TOKEN is scoped to sentinel-command only" \
              "and cannot see the database app — the backup itself still ran, but" \
              "cluster snapshots are NOT being checked here. Use an org-scoped token" \
              "to restore this visibility."
          fi

      - name: Pull newest dump off the machine
        if: env.BACKUP_KEY != ''
        run: |
          LATEST=$(flyctl ssh console -a sentinel-command -C "sh -c 'ls -1t /data/backups/sentinel-*.dump | head -1'" | tr -d '\r' | tail -1)
          echo "newest dump: $LATEST"
          test -n "$LATEST"
          flyctl ssh sftp get "$LATEST" ./backup.dump -a sentinel-command
          ls -lh backup.dump

      - name: Encrypt for off-platform storage
        if: env.BACKUP_KEY != ''
        run: |
          # AES-256-CBC + PBKDF2. Decrypt with:
          #   openssl enc -d -aes-256-cbc -pbkdf2 -iter 200000 \
          #     -in backup.dump.enc -out backup.dump -pass pass:<key>
          # then restore with backend/scripts/restore_db.sh.
          openssl enc -aes-256-cbc -pbkdf2 -iter 200000 -salt \
            -in backup.dump -out backup.dump.enc -pass env:BACKUP_KEY
          rm backup.dump
          ls -lh backup.dump.enc

      - name: Store encrypted dump as artifact (30-day retention)
        if: env.BACKUP_KEY != ''
        uses: actions/upload-artifact@v7
        with:
          name: db-backup-${{ github.run_id }}
          path: backup.dump.enc
          retention-days: 30
          if-no-files-found: error

      # Deliberately NOT a ::warning:: — the operator decided on 2026-09-07
      # to accept Fly as the single backup location and not keep copies
      # elsewhere. A daily warning annotation for a settled decision is
      # noise that trains people to ignore annotations. Setting the
      # secret still enables the encrypted artifact path above, so
      # reversing the decision needs no code change.
      - name: Off-platform copy not configured (by design)
        if: env.BACKUP_KEY == ''
        run: |
          echo "BACKUP_ENCRYPTION_KEY is unset — off-platform copies are OFF by choice."
          echo "Backups are Fly-only: managed cluster snapshots (primary) plus the"
          echo "portable pg_dump written above. Accepted risk: losing the Fly account"
          echo "loses every copy. Set the secret to turn encrypted artifacts back on."