db-backup.yml

name: Database Dump

on:
  schedule:
    - cron: '0 0 * * 1' # Every Monday at midnight UTC
  workflow_dispatch:

permissions: {}

concurrency:
  group: ${{ github.workflow }}
  cancel-in-progress: false

jobs:
  preflight:
    name: Preflight
    timeout-minutes: 5
    runs-on: ubuntu-latest
    permissions:
      contents: read # Tag-collision check
    outputs:
      dump-file: ${{ steps.names.outputs.dump-file }}
      tag: ${{ steps.names.outputs.tag }}
      date: ${{ steps.names.outputs.date }}
    steps:
      - name: Compute dump name and release tag
        id: names
        run: |
          set -euo pipefail
          DATE="$(date -u +%Y-%m-%d)"
          {
            echo "date=${DATE}"
            echo "dump-file=inferencex-${DATE}.dump.zst"
            echo "tag=db-dump/${DATE}"
          } >> "$GITHUB_OUTPUT"

      - name: Fail fast if the release tag already exists
        env:
          GH_TOKEN: ${{ github.token }}
          TAG: ${{ steps.names.outputs.tag }}
        run: |
          set -euo pipefail
          if gh release view "$TAG" --repo "$GITHUB_REPOSITORY" >/dev/null 2>&1; then
            echo "::error::Release $TAG already exists; delete it first to re-dump for the same date."
            exit 1
          fi

  dump:
    name: Dump and compress
    needs: preflight
    timeout-minutes: 180
    runs-on: ubuntu-latest
    steps:
      - name: Dump, compress, and split database
        env:
          DATABASE_URL: ${{ secrets.DATABASE_WRITE_URL }}
          DUMP_FILE: ${{ needs.preflight.outputs.dump-file }}
        run: |
          set -euo pipefail
          docker run --rm --env DATABASE_URL \
            postgres:18-alpine@sha256:9a8afca54e7861fd90fab5fdf4c42477a6b1cb7d293595148e674e0a3181de15 \
            sh -ceu 'exec pg_dump \
              --dbname="$DATABASE_URL" \
              --format=custom \
              --compress=none \
              --no-owner \
              --no-privileges \
              --exclude-table-data=public.user_feedback \
              --verbose' \
            | zstd -v -T0 -19 --long=27 \
            | split -b 1900m -d -a 2 - "${DUMP_FILE}.part"

      - name: Verify compressed stream
        env:
          DUMP_FILE: ${{ needs.preflight.outputs.dump-file }}
        run: |
          set -euo pipefail
          cat "${DUMP_FILE}".part* | zstd -t --long=27

      - name: Checksum parts
        env:
          DUMP_FILE: ${{ needs.preflight.outputs.dump-file }}
        run: |
          set -euo pipefail
          sha256sum "${DUMP_FILE}".part* > SHA256SUMS
          cat SHA256SUMS

      - name: Upload dump parts
        uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7
        with:
          name: db-dump-parts
          path: |
            inferencex-*.dump.zst.part*
            SHA256SUMS
          if-no-files-found: error
          retention-days: 1
          compression-level: 0

  release:
    name: Create release
    needs: [preflight, dump]
    timeout-minutes: 30
    runs-on: ubuntu-latest
    permissions:
      contents: write # Create the database dump release
    steps:
      - name: Download dump parts
        uses: actions/download-artifact@3e5f45b2cfb9172054b4087a40e8e0b5a5461e7c # v8
        with:
          name: db-dump-parts

      - name: Verify checksums
        run: |
          set -euo pipefail
          sha256sum -c SHA256SUMS

      - name: Create release
        env:
          GH_TOKEN: ${{ github.token }}
          TAG: ${{ needs.preflight.outputs.tag }}
          DATE: ${{ needs.preflight.outputs.date }}
          DUMP_GLOB: ${{ needs.preflight.outputs.dump-file }}.part*
        run: |
          set -euo pipefail
          cat > release-notes.md <<'EOF'
          Weekly InferenceX database dump.

          Verify (optional):
          `sha256sum -c SHA256SUMS`

          Reassemble and decompress:
          `cat *.dump.zst.part* | zstd -d --long=27 > inferencex.dump`

          Restore to PostgreSQL or Neon:
          `pg_restore --exit-on-error --clean --if-exists --no-owner --no-privileges --dbname="YOUR_TARGET_DATABASE_URL" inferencex.dump`
          EOF

          # shellcheck disable=SC2086
          gh release create "$TAG" $DUMP_GLOB SHA256SUMS \
            --repo "$GITHUB_REPOSITORY" \
            --title "DB Dump $DATE" \
            --notes-file release-notes.md \
            --latest=false