Zero cleanup action

action.yml

# To be placed inside .github/actions like .github/actions/cleanup-pr/action.yml

name: "Cleanup PR Database Resources"
description: "Cleanup database resources for a specific PR"

inputs:
  pr_number:
    description: "PR number to cleanup"
    required: true
  pg_cluster_url:
    description: "PostgreSQL cluster URL"
    required: true

runs:
  using: "composite"
  steps:
    - name: Install PostgreSQL client
      shell: bash
      run: |
        sudo apt-get update
        sudo apt-get install -y postgresql-client

    - name: Cleanup FlightControl Database
      shell: bash
      run: |
        psql ${{ inputs.pg_cluster_url }}/flightcontrol << EOF
        -- Drop triggers
        DROP TRIGGER IF EXISTS zero_ddl_start_${{ inputs.pr_number }} ON pg_event_trigger;
        DROP TRIGGER IF EXISTS zero_create_table_${{ inputs.pr_number }} ON pg_event_trigger;
        DROP TRIGGER IF EXISTS zero_alter_table_${{ inputs.pr_number }} ON pg_event_trigger;
        DROP TRIGGER IF EXISTS zero_create_index_${{ inputs.pr_number }} ON pg_event_trigger;
        DROP TRIGGER IF EXISTS zero_drop_table_${{ inputs.pr_number }} ON pg_event_trigger;
        DROP TRIGGER IF EXISTS zero_drop_index_${{ inputs.pr_number }} ON pg_event_trigger;
        DROP TRIGGER IF EXISTS zero_alter_publication_${{ inputs.pr_number }} ON pg_event_trigger;

        -- Drop replication slot
        DO \$\$ 
        BEGIN
          PERFORM pg_drop_replication_slot('zero_${{ inputs.pr_number }}');
          RAISE NOTICE 'Successfully dropped replication slot: zero_%', '${{ inputs.pr_number }}';
        EXCEPTION WHEN OTHERS THEN
          RAISE NOTICE 'Failed to drop replication slot: zero_%. Error: %', '${{ inputs.pr_number }}', SQLERRM;
        END;
        \$\$;

        -- Drop publications
        DROP PUBLICATION IF EXISTS _zero_public_${{ inputs.pr_number }};
        DROP PUBLICATION IF EXISTS _zero_metadata_${{ inputs.pr_number }};

        -- Drop schema and all its contents
        DROP SCHEMA IF EXISTS zero_${{ inputs.pr_number }} CASCADE;
        EOF

    - name: Cleanup Zero CVR Database
      shell: bash
      run: |
        psql ${{ inputs.pg_cluster_url }}/zero_cvr << EOF
        DROP SCHEMA IF EXISTS cvr_${{ inputs.pr_number }} CASCADE;
        EOF

    - name: Cleanup Zero CDC Database
      shell: bash
      run: |
        psql ${{ inputs.pg_cluster_url }}/zero_cdc << EOF
        DROP SCHEMA IF EXISTS cdc_${{ inputs.pr_number }} CASCADE;
        EOF

zero_cleanup.yml

name: Cleanup Zero Preview Env Artefacts

on:
  workflow_dispatch:
    inputs:
      pr_number:
        description: 'Pull Request Number to cleanup'
        required: true
        type: string
  schedule:
    # Run every day at midnight UTC
    - cron: '0 0 * * *'

jobs:
  find-closed-prs:
    # Only run this job for scheduled events
    if: github.event_name == 'schedule'
    runs-on: ubuntu-latest
    outputs:
      pr_numbers: ${{ steps.get-prs.outputs.pr_numbers }}
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Install and verify jq
        run: |
          sudo apt-get update
          sudo apt-get install -y jq
          jq --version

      - name: Get recently closed PRs
        id: get-prs
        run: |
          # Get PRs closed in the last 26 hours (24h + 2h buffer), excluding the last 30 minutes
          # Convert to minutes for the date filter
          MINUTES_AGO=1560  # 26 hours (24h + 2h buffer)
          CUTOFF_MINUTES=30
          
          # Calculate timestamps in seconds
          NOW=$(date +%s)
          MIN_TIME=$((NOW - MINUTES_AGO * 60))
          MAX_TIME=$((NOW - CUTOFF_MINUTES * 60))
          
          echo "Searching for PRs closed between $(date -d @${MIN_TIME}) and $(date -d @${MAX_TIME})"
          
          # Use GitHub CLI to get closed PRs
          prs=$(gh pr list \
            --state closed \
            --json number,closedAt \
            --jq "[.[] | select(
              (.closedAt | strptime(\"%Y-%m-%dT%H:%M:%SZ\") | mktime) > ${MIN_TIME} and 
              (.closedAt | strptime(\"%Y-%m-%dT%H:%M:%SZ\") | mktime) < ${MAX_TIME}
            ) | .number]") || (echo "Failed to fetch PRs" && exit 1)
          
          # Check if prs is valid JSON array
          echo "$prs" | jq empty || (echo "Invalid JSON output from gh pr list" && exit 1)
          
          # Log found PRs
          echo "Found PRs: $prs"
          if [ "$prs" = "[]" ]; then
            echo "No PRs found in the time window"
          fi
          
          echo "pr_numbers=${prs}" >> $GITHUB_OUTPUT
        env:
          GH_TOKEN: ${{ secrets.GITHUB_TOKEN }}

  cleanup-manual:
    if: github.event_name == 'workflow_dispatch'
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4
        
      - name: Run cleanup
        uses: ./.github/actions/cleanup-pr
        with:
          pr_number: ${{ inputs.pr_number }}
          pg_cluster_url: ${{ secrets.PG_CLUSTER_URL }}

  cleanup-scheduled:
    if: github.event_name == 'schedule'
    needs: find-closed-prs
    runs-on: ubuntu-latest
    strategy:
      matrix:
        pr_number: ${{ fromJson(needs.find-closed-prs.outputs.pr_numbers) }}
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4
        
      - name: Run cleanup
        uses: ./.github/actions/cleanup-pr
        with:
          pr_number: ${{ matrix.pr_number }}
          pg_cluster_url: ${{ secrets.PG_CLUSTER_URL }}