database-backup.yml

name: database-backup

on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch:

jobs:
  backup-and-restore-check:
    runs-on: ubuntu-latest
    services:
      restore-postgres:
        image: postgres:16
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: ignition_restore_check
        ports:
          - 5432:5432
        options: >-
          --health-cmd "pg_isready -U postgres -d ignition_restore_check"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - uses: actions/checkout@v4

      - name: Install PostgreSQL client
        run: sudo apt-get update && sudo apt-get install -y postgresql-client

      - name: Ensure database secret is configured
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          if [ -z "$DATABASE_URL" ]; then
            echo "DATABASE_URL repository secret is required for scheduled backups."
            exit 1
          fi

      - name: Create database backup
        id: backup
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          DATABASE_BACKUP_DIR: ${{ runner.temp }}/database-backups
          DATABASE_BACKUP_RETENTION_DAYS: 14
        run: |
          node ignition-api/scripts/backup-database.mjs
          BACKUP_FILE="$(find "$DATABASE_BACKUP_DIR" -name '*.dump' -type f | sort | tail -n 1)"
          echo "backup_file=$BACKUP_FILE" >> "$GITHUB_OUTPUT"

      - name: Verify backup manifest
        env:
          BACKUP_FILE: ${{ steps.backup.outputs.backup_file }}
        run: node ignition-api/scripts/verify-database-backup.mjs

      - name: Restore backup into check database
        env:
          BACKUP_FILE: ${{ steps.backup.outputs.backup_file }}
          RESTORE_DATABASE_URL: postgresql://postgres:postgres@localhost:5432/ignition_restore_check?schema=public
        run: node ignition-api/scripts/restore-database.mjs

      - name: Archive encrypted runner artifact
        uses: actions/upload-artifact@v4
        with:
          name: ignition-pay-database-backup
          path: |
            ${{ runner.temp }}/database-backups/*.dump
            ${{ runner.temp }}/database-backups/*.json
          retention-days: 14