_migrate-database.yml

name: Migrate Database

on:
  workflow_call:
    inputs:
      azure_environment:
        required: true
        type: string
      cluster_location_acronym:
        required: true
        type: string
      service_principal_id:
        required: true
        type: string
      subscription_id:
        required: true
        type: string
      database_name:
        required: true
        type: string
      relative_project_path:
        description: "Relative path to the project containing the migrations"
        required: true
        type: string
      relative_startup_project:
        description: "Relative path to the startup project"
        required: true
        type: string
      db_context:
        required: true
        type: string
      apply_migrations:
        required: true
        type: boolean

    outputs:
      has_migrations_to_apply:
        value: ${{ jobs.plan-migrations.outputs.has_migrations_to_apply }}

concurrency:
  group: ${{ inputs.database_name }}-${{ inputs.azure_environment }}-migrations
  cancel-in-progress: false

jobs:
  plan-migrations:
    name: Plan
    runs-on: ubuntu-24.04
    outputs:
      has_migrations_to_apply: ${{ steps.generate-migration-script.outputs.has_migrations_to_apply }}
      migration_json: ${{ steps.generate-migration-script.outputs.migration_json }}
    env:
      UNIQUE_PREFIX: ${{ vars.UNIQUE_PREFIX }}
      TENANT_ID: ${{ vars.TENANT_ID }}
      CLUSTER_RESOURCE_GROUP_NAME: ${{ vars.UNIQUE_PREFIX }}-${{ inputs.azure_environment }}-${{ inputs.cluster_location_acronym }}
      POSTGRES_SERVER_NAME: ${{ vars.UNIQUE_PREFIX }}-${{ inputs.azure_environment }}-${{ inputs.cluster_location_acronym }}
      POSTGRES_HOST: ${{ vars.UNIQUE_PREFIX }}-${{ inputs.azure_environment }}-${{ inputs.cluster_location_acronym }}.postgres.database.azure.com

    steps:
      - name: Checkout Code
        uses: actions/checkout@v6

      - name: Setup .NET Core SDK
        uses: actions/setup-dotnet@v5
        with:
          global-json-file: application/global.json

      - name: Restore .NET Tools
        working-directory: application
        run: dotnet tool restore

      - name: Build Backend Solution
        working-directory: application
        run: dotnet build ${{ inputs.relative_startup_project }}

      - name: Login to Azure
        uses: azure/login@v3
        with:
          client-id: ${{ inputs.service_principal_id }}
          tenant-id: ${{ env.TENANT_ID }}
          subscription-id: ${{ inputs.subscription_id }}

      - name: Open Firewall
        working-directory: cloud-infrastructure/cluster
        env:
          CLUSTER_RESOURCE_GROUP_NAME: ${{ env.CLUSTER_RESOURCE_GROUP_NAME }}
          POSTGRES_SERVER_NAME: ${{ env.POSTGRES_SERVER_NAME }}
          DATABASE_NAME: ${{ inputs.database_name }}
        run: bash ./firewall.sh open

      - name: Generate Script for Pending Migrations
        id: generate-migration-script
        working-directory: application
        run: |
          ENTRA_USER=$(az postgres flexible-server microsoft-entra-admin list --resource-group ${{ env.CLUSTER_RESOURCE_GROUP_NAME }} --server-name ${{ env.POSTGRES_SERVER_NAME }} --query "[0].principalName" --output tsv)
          CONNECTION_STRING="Host=${{ env.POSTGRES_HOST }};Database=${{ inputs.database_name }};Username=$ENTRA_USER;Password=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv);Ssl Mode=VerifyFull;"
          
          echo "Checking for pending migrations..."
          MIGRATION_INFO=$(dotnet ef migrations list \
            --project ${{ inputs.relative_project_path }} \
            --startup-project ${{ inputs.relative_startup_project }} \
            --context ${{ inputs.db_context }} \
            --connection "$CONNECTION_STRING" \
            --no-build \
            --json)
          
          MIGRATION_JSON=$(echo "$MIGRATION_INFO" | sed -n '/^[{[]/,$p')
          PENDING_MIGRATIONS_JSON=$(echo "$MIGRATION_JSON" | jq '[.[] | select(.applied == false)]')
          PENDING_MIGRATIONS_COUNT=$(echo "$PENDING_MIGRATIONS_JSON" | jq '. | length')
          LAST_APPLIED_MIGRATION=$(echo "$MIGRATION_JSON" | jq -r '[.[] | select(.applied == true) | .id] | sort | last // "0"')
          
          if [ "$PENDING_MIGRATIONS_COUNT" -gt "0" ]; then
            LAST_PENDING_MIGRATION=$(echo "$PENDING_MIGRATIONS_JSON" | jq -r '.[-1].id')
            echo "$PENDING_MIGRATIONS_COUNT pending migration(s) detected:"
            echo "$PENDING_MIGRATIONS_JSON"
            echo "Generating migration script from $LAST_APPLIED_MIGRATION to $LAST_PENDING_MIGRATION..."

            dotnet ef migrations script \
              "$LAST_APPLIED_MIGRATION" "$LAST_PENDING_MIGRATION" \
              --project ${{ inputs.relative_project_path }} \
              --startup-project ${{ inputs.relative_startup_project }} \
              --context ${{ inputs.db_context }} \
              --idempotent \
              --no-build \
              --output migration.sql
            
            echo "has_migrations_to_apply=true" >> $GITHUB_OUTPUT
            
            echo "migration_script<<EOF" >> $GITHUB_OUTPUT
            cat migration.sql >> $GITHUB_OUTPUT
            echo "EOF" >> $GITHUB_OUTPUT
            
            echo "migration_json<<EOF" >> $GITHUB_OUTPUT
            echo "$PENDING_MIGRATIONS_JSON" >> $GITHUB_OUTPUT
            echo "EOF" >> $GITHUB_OUTPUT
          else
            echo "No pending migrations detected."
            echo "Here is a list of all migrations:"
            echo "$MIGRATION_JSON"
            echo "has_migrations_to_apply=false" >> $GITHUB_OUTPUT
          fi

      - name: Close Firewall
        if: always()
        working-directory: cloud-infrastructure/cluster
        env:
          CLUSTER_RESOURCE_GROUP_NAME: ${{ env.CLUSTER_RESOURCE_GROUP_NAME }}
          POSTGRES_SERVER_NAME: ${{ env.POSTGRES_SERVER_NAME }}
          DATABASE_NAME: ${{ inputs.database_name }}
        run: bash ./firewall.sh close

      - name: Upload Migration Script
        if: steps.generate-migration-script.outputs.has_migrations_to_apply == 'true'
        uses: actions/upload-artifact@v7
        with:
          name: migration-script-${{ inputs.azure_environment }}-${{ inputs.cluster_location_acronym }}
          path: application/migration.sql

      - name: Generate Migration Information
        id: migration-info
        if: steps.generate-migration-script.outputs.has_migrations_to_apply == 'true'
        uses: actions/github-script@v8
        env:
          MIGRATION_JSON: ${{ steps.generate-migration-script.outputs.migration_json }}
          MIGRATION_SCRIPT: ${{ steps.generate-migration-script.outputs.migration_script }}
        with:
          script: |
            const migrationJson = JSON.parse(process.env.MIGRATION_JSON);
            
            const migrationsList = migrationJson.map(m => `- ${m.name} (${m.id})`).join('\n');
            
            const migrationInfo = `## Approve Database Migration \`${{ inputs.database_name }}\` database on \`${{ inputs.azure_environment }}\`
            
            The following pending migration(s) will be applied to the database when approved:
            ${migrationsList}
            
            ### Migration Script
            \`\`\`sql
            ${process.env.MIGRATION_SCRIPT}
            \`\`\`
            `;
            
            console.log(migrationInfo);
            
            core.setOutput('markdown', migrationInfo);

      - name: Add Migration Information to Pull Request
        if: github.event_name == 'pull_request' && steps.generate-migration-script.outputs.has_migrations_to_apply == 'true'
        uses: actions/github-script@v8
        env:
          MIGRATION_INFO: ${{ steps.migration-info.outputs.markdown }}
        with:
          github-token: ${{ secrets.GITHUB_TOKEN }}
          script: |
            // Delete previous migration info comments for this specific database
            const comments = await github.rest.issues.listComments({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              per_page: 100
            });

            const MIGRATION_HEADER = '## Approve Database Migration `${{ inputs.database_name }}` database on `${{ inputs.azure_environment }}`';

            for (const comment of comments.data) {
              if (comment.body && comment.body.startsWith(MIGRATION_HEADER)) {
                await github.rest.issues.deleteComment({
                  owner: context.repo.owner,
                  repo: context.repo.repo,
                  comment_id: comment.id
                });
              }
            }

            // Add the new migration info comment
            await github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: process.env.MIGRATION_INFO
            });

      - name: Add Migration Information to Summary
        if: steps.generate-migration-script.outputs.has_migrations_to_apply == 'true' && inputs.azure_environment == 'prod'
        uses: actions/github-script@v8
        env:
          MIGRATION_INFO: ${{ steps.migration-info.outputs.markdown }}
        with:
          script: core.summary.addRaw(process.env.MIGRATION_INFO).write();

  apply-migrations:
    name: Deploy
    needs: plan-migrations
    if: needs.plan-migrations.outputs.has_migrations_to_apply == 'true' && inputs.apply_migrations
    runs-on: ubuntu-24.04
    environment:
      name: ${{ inputs.azure_environment == 'prod' && 'production' || 'staging' }}
      url: ${{ format('{0}/{1}/actions/runs/{2}', github.server_url, github.repository, github.run_id) }}

    env:
      UNIQUE_PREFIX: ${{ vars.UNIQUE_PREFIX }}
      TENANT_ID: ${{ vars.TENANT_ID }}
      CLUSTER_RESOURCE_GROUP_NAME: ${{ vars.UNIQUE_PREFIX }}-${{ inputs.azure_environment }}-${{ inputs.cluster_location_acronym }}
      POSTGRES_SERVER_NAME: ${{ vars.UNIQUE_PREFIX }}-${{ inputs.azure_environment }}-${{ inputs.cluster_location_acronym }}
      POSTGRES_HOST: ${{ vars.UNIQUE_PREFIX }}-${{ inputs.azure_environment }}-${{ inputs.cluster_location_acronym }}.postgres.database.azure.com

    steps:
      - name: Checkout Code
        uses: actions/checkout@v6

      - name: Login to Azure
        uses: azure/login@v3
        with:
          client-id: ${{ inputs.service_principal_id }}
          tenant-id: ${{ env.TENANT_ID }}
          subscription-id: ${{ inputs.subscription_id }}

      - name: Download Migration Script
        uses: actions/download-artifact@v8
        with:
          name: migration-script-${{ inputs.azure_environment }}-${{ inputs.cluster_location_acronym }}
          path: .

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

      - name: Open Firewall
        working-directory: cloud-infrastructure/cluster
        env:
          CLUSTER_RESOURCE_GROUP_NAME: ${{ env.CLUSTER_RESOURCE_GROUP_NAME }}
          POSTGRES_SERVER_NAME: ${{ env.POSTGRES_SERVER_NAME }}
          DATABASE_NAME: ${{ inputs.database_name }}
        run: bash ./firewall.sh open

      - name: Apply Migrations
        run: |
          echo "Applying migrations to ${{ inputs.database_name }} database on ${{ env.POSTGRES_HOST }}..."
          ACCESS_TOKEN=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)
          ENTRA_USER=$(az postgres flexible-server microsoft-entra-admin list --resource-group ${{ env.CLUSTER_RESOURCE_GROUP_NAME }} --server-name ${{ env.POSTGRES_SERVER_NAME }} --query "[0].principalName" --output tsv)
          PGPASSWORD=$ACCESS_TOKEN psql -v ON_ERROR_STOP=1 "host=${{ env.POSTGRES_HOST }} dbname=${{ inputs.database_name }} user='$ENTRA_USER' sslmode=verify-full sslrootcert=system" -f migration.sql
          echo "Migrations applied successfully!"

      - name: Display Migration Summary
        uses: actions/github-script@v8
        with:
          script: core.summary.addRaw(`✅ Migrations successfully applied to \`${{ inputs.database_name }}\` database on \`${{ inputs.azure_environment }}\`.`).write();

      - name: Close Firewall
        if: always()
        working-directory: cloud-infrastructure/cluster
        env:
          CLUSTER_RESOURCE_GROUP_NAME: ${{ env.CLUSTER_RESOURCE_GROUP_NAME }}
          POSTGRES_SERVER_NAME: ${{ env.POSTGRES_SERVER_NAME }}
          DATABASE_NAME: ${{ inputs.database_name }}
        run: bash ./firewall.sh close