database-backup.yml

name: Database Backup

on:
  schedule:
    # Run nightly at 2 AM UTC
    - cron: "0 2 * * *"
  workflow_dispatch:
    inputs:
      storage_type:
        description: "Storage type (s3 or gcs)"
        required: false
        default: "s3"

jobs:
  backup-database:
    name: Backup PostgreSQL Database
    runs-on: ubuntu-latest

    steps:
      - name: Checkout code
        uses: actions/checkout@v4

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

      - name: Configure AWS credentials
        if: github.event.inputs.storage_type != 'gcs' && secrets.AWS_ACCESS_KEY_ID != ''
        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: ${{ secrets.AWS_REGION || 'us-east-1' }}

      - name: Setup Google Cloud SDK
        if: github.event.inputs.storage_type == 'gcs'
        uses: google-github-actions/setup-gcloud@v1
        with:
          service_account_key: ${{ secrets.GCS_SA_KEY }}
          project_id: ${{ secrets.GCP_PROJECT_ID }}

      - name: Run database backup
        env:
          DB_HOST: ${{ secrets.DB_HOST }}
          DB_PORT: ${{ secrets.DB_PORT || '5432' }}
          DB_USER: ${{ secrets.DB_USER }}
          DB_NAME: ${{ secrets.DB_NAME || 'stellar_indigopay' }}
          DB_PASSWORD: ${{ secrets.DB_PASSWORD }}
          STORAGE_TYPE: ${{ github.event.inputs.storage_type || 's3' }}
          S3_BUCKET: ${{ secrets.S3_BUCKET }}
          S3_PREFIX: ${{ secrets.S3_PREFIX || 'backups/' }}
          GCS_BUCKET: ${{ secrets.GCS_BUCKET }}
          GCS_PREFIX: ${{ secrets.GCS_PREFIX || 'backups/' }}
          RETENTION_DAYS: ${{ secrets.BACKUP_RETENTION_DAYS || '30' }}
          BACKUP_DIR: /tmp/backups
        run: |
          bash scripts/backup-db.sh

      - name: Upload backup as artifact
        if: success()
        uses: actions/upload-artifact@v4
        with:
          name: database-backup
          path: /tmp/backups/stellar_indigopay_backup_*.sql.gz
          retention-days: 7

      - name: Backup status check
        if: failure()
        run: |
          echo "::error::Database backup failed. Check the logs above."
          exit 1

      - name: Upload backup status
        if: always()
        uses: actions/github-script@v7
        with:
          script: |
            const fs = require('fs');
            const status = '${{ job.status }}';
            console.log(`Backup job completed with status: ${status}`);
            if (status === 'success') {
              core.notice('✅ Database backup completed successfully');
            } else {
              core.error('❌ Database backup failed');
            }

  verify-backup:
    name: Verify Backup Integrity
    runs-on: ubuntu-latest
    needs: backup-database
    if: always() && needs.backup-database.result == 'success'

    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Download backup artifact
        uses: actions/download-artifact@v4
        with:
          name: database-backup
          path: /tmp/backups

      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '18'

      - name: Run backup verification
        id: verify
        run: |
          BACKUP_FILE=$(ls /tmp/backups/*.sql.gz 2>/dev/null || ls /tmp/backups/*.sql 2>/dev/null || echo "")
          if [ -z "$BACKUP_FILE" ]; then
            echo "::error::No backup file found in /tmp/backups/"
            exit 1
          fi
          echo "Verifying backup: $BACKUP_FILE"
          node backend/scripts/verify-backup.js --backup "$BACKUP_FILE" > /tmp/verification-report.json
          cat /tmp/verification-report.json

      - name: Upload verification report
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: backup-verification-report
          path: /tmp/verification-report.json
          retention-days: 90

      - name: Send verification failure notification
        if: failure()
        run: |
          echo "::error::Backup verification failed! Check the verification report above."
          # Optionally send to PagerDuty or Slack via webhook
          # curl -X POST "${{ secrets.PAGERDUTY_WEBHOOK_URL }}" \
          #   -H 'Content-Type: application/json' \
          #   -d '{"event_type":"trigger","description":"Backup verification failed","severity":"critical"}'

  notify-on-failure:
    name: Notify on Failure
    runs-on: ubuntu-latest
    needs: [backup-database, verify-backup]
    if: failure()

    steps:
      - name: Create issue on backup failure
        uses: actions/github-script@v7
        with:
          script: |
            const failedJob = '${{ needs.backup-database.result }}' === 'failure' ? 'Backup' : 'Verification';
            const title = `🚨 Database ${failedJob} Failed`;
            const body = `Database ${failedJob.toLowerCase()} failed on ${new Date().toISOString()}.\n\nCheck the workflow run: ${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}`;

            github.rest.issues.create({
              owner: context.repo.owner,
              repo: context.repo.repo,
              title: title,
              body: body,
              labels: ['bug', 'devops', 'backup']
            });