schema-dump.yml

name: Generate and Validate Flyway Schema

on:
  pull_request:
    types: [opened, synchronize, labeled]
    paths:
      - 'backend/src/main/resources/db/migration/**'
      - '.github/workflows/schema-dump.yml'
  workflow_dispatch:

jobs:
  generate-schema:
    runs-on: ubuntu-latest
    
    # Explicitly set permissions needed for committing
    permissions:
      contents: write
      pull-requests: read

    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: postgres
          POSTGRES_USER: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
    - name: Checkout repository
      uses: actions/checkout@v4
      with:
        ref: ${{ github.head_ref }}
        fetch-depth: 0

    - name: Run Flyway migrations
      uses: docker://flyway/flyway:10-alpine
      env:
        FLYWAY_URL: jdbc:postgresql://postgres:5432/postgres
        FLYWAY_USER: postgres
        FLYWAY_PASSWORD: postgres
      with:
        args: -locations=filesystem:./backend/src/main/resources/db/migration/ migrate

    - name: Generate new schema
      uses: docker://postgres:16
      with:
        args: >
          bash -c "PGPASSWORD=postgres pg_dump 
          -h postgres 
          -U postgres 
          -d postgres 
          --schema-only 
          > /github/workspace/backend/docs/db/schema.sql"
    - name: Cat schema
      run: cat backend/docs/db/schema.sql
      
    - name: Stage schema file
      run: git add backend/docs/db/schema.sql

    - name: Check for schema changes
      id: check-changes
      run: |
        if ! git diff --cached --quiet backend/docs/db/schema.sql; then
          echo "Schema changes detected"
          echo "changed=true" >> $GITHUB_OUTPUT
          git diff --cached backend/docs/db/schema.sql
        else
          echo "No schema changes detected"
          echo "changed=false" >> $GITHUB_OUTPUT
        fi

    - name: Handle schema changes
      if: steps.check-changes.outputs.changed == 'true'
      run: |
        if [[ "${{ contains(github.event.pull_request.labels.*.name, 'update_db_schema') }}" == "true" ]]; then
          git config --global user.name 'GitHub Action'
          git config --global user.email 'action@github.com'
          git commit -m "Update schema documentation based on migration changes"
          git push
        else
          echo "::error::Schema changes detected but 'update-schema' label is not present on the PR"
          echo "Please add the 'update_db_schema' label if these changes are intentional"
          exit 1
        fi