migrations-production.yml

name: Migrations Production

on:
  workflow_run:
    workflows:
      - Deploy Production
    types:
      - completed
  workflow_dispatch:

permissions: {}

jobs:
  migrations-production:
    name: Migrations Production
    runs-on: ubuntu-latest
    environment: production

    if: github.event_name == 'workflow_dispatch' ||
      github.event.workflow_run.conclusion == 'success'

    permissions:
      contents: read

    steps:
      - name: Checkout repository (trusted base)
        uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1
        with:
          ref: main

      - name: Determine deployed SHA
        id: context
        run: |
          set -euo pipefail

          if [ "${GITHUB_EVENT_NAME}" = 'workflow_dispatch' ]; then
            echo "sha=$(git rev-parse HEAD)" >> "$GITHUB_OUTPUT"
            echo 'trigger_event=workflow_dispatch' >> "$GITHUB_OUTPUT"
            echo 'head_branch=main' >> "$GITHUB_OUTPUT"
            exit 0
          fi

          echo "sha=${{ github.event.workflow_run.head_sha }}" >> "$GITHUB_OUTPUT"
          echo "trigger_event=${{ github.event.workflow_run.event }}" >> "$GITHUB_OUTPUT"
          echo "head_branch=${{ github.event.workflow_run.head_branch }}" >> "$GITHUB_OUTPUT"
        shell: bash

      - name: Gate migrations (skip hotfix/unexpected triggers)
        id: gate
        run: |
          set -euo pipefail

          # Allow manual runs (environment protections still apply).
          if [ "${GITHUB_EVENT_NAME}" = 'workflow_dispatch' ]; then
            echo 'should_migrate=true' >> "$GITHUB_OUTPUT"
            exit 0
          fi

          # Only run when the deploy workflow ran on main.
          if [ '${{ steps.context.outputs.head_branch }}' != 'main' ]; then
            echo 'should_migrate=false' >> "$GITHUB_OUTPUT"
            exit 0
          fi

          echo 'should_migrate=true' >> "$GITHUB_OUTPUT"

      - name: Checkout deployed SHA (validated)
        # CodeQL can flag privileged workflows which checkout untrusted refs via actions/checkout.
        # We checkout main first (trusted) to load local actions/tooling, then fetch+detach the
        # deployed SHA from the upstream Deploy workflow context.
        if: steps.gate.outputs.should_migrate == 'true'
        run: |
          set -euo pipefail
          sha='${{ steps.context.outputs.sha }}'
          git fetch origin "$sha" --depth=1
          git checkout --detach "$sha"

      - name: Setup Node.js
        if: steps.gate.outputs.should_migrate == 'true'
        uses: actions/setup-node@820762786026740c76f36085b0efc47a31fe5020 # v7.0.0
        with:
          node-version: ">=24.16.0 <25"
          cache: "npm"

      - name: Install dependencies
        if: steps.gate.outputs.should_migrate == 'true'
        run: npm ci

      - name: Verify database schema is up to date
        id: verify
        if: steps.gate.outputs.should_migrate == 'true'
        env:
          ASTRO_DB_REMOTE_URL: ${{ secrets.ASTRO_DB_REMOTE_URL }}
          ASTRO_DB_APP_TOKEN: ${{ secrets.ASTRO_DB_APP_TOKEN }}
        run: |
          set -uo pipefail
          set +e
          output=$(npx astro db verify 2>&1)
          exit_code=$?
          set -e

          printf '%s\n' "$output"

          if [ "$exit_code" -ne 0 ]; then
            exit "$exit_code"
          fi

          if printf '%s' "$output" | grep -Fq 'Database schema is up to date.'; then
            echo 'needs_push=false' >> "$GITHUB_OUTPUT"
            exit 0
          fi

          if printf '%s' "$output" | grep -Fq 'Database schema is out of date.'; then
            echo 'needs_push=true' >> "$GITHUB_OUTPUT"
            exit 0
          fi

          echo 'Unexpected output from astro db verify' >&2
          exit 1

      - name: Push database migrations (production)
        if: steps.gate.outputs.should_migrate == 'true' &&
          steps.verify.outputs.needs_push == 'true'
        env:
          ASTRO_DB_REMOTE_URL: ${{ secrets.ASTRO_DB_REMOTE_URL }}
          ASTRO_DB_APP_TOKEN: ${{ secrets.ASTRO_DB_APP_TOKEN }}
        run: npx astro db push --remote