database-migration.yml

# Run Database Migrations
#
# This workflow manages database schema migrations across all cloud providers.
# It can run migrations up (apply) or down (rollback) with safety checks.
#
# Required GitHub Secrets:
#   - DB_PASSWORD_AWS: Database password for AWS Aurora
#   - DB_PASSWORD_GCP: Database password for GCP Cloud SQL
#   - DB_PASSWORD_AZURE: Database password for Azure Flexible Server
#   - (AWS auth via OIDC: vars.AWS_ROLE_TO_ASSUME)
#   - (GCP auth via WIF: vars.GCP_WORKLOAD_IDENTITY_PROVIDER + vars.GCP_SERVICE_ACCOUNT)
#   - AZURE_CLIENT_ID, AZURE_TENANT_ID, AZURE_SUBSCRIPTION_ID (OIDC federation)
#
# Required GitHub Variables:
#   - DB_HOST_AWS_DEV: Aurora endpoint (dev)
#   - DB_HOST_AWS_STAGING: Aurora endpoint (staging)
#   - DB_HOST_AWS_PROD: Aurora endpoint (prod)
#   - (Similar for GCP and Azure)
#
# Triggered by:
#   - Manual workflow dispatch
#   - Before deployment workflows (optional)

name: Database Migration

# Least privilege by default: only the jobs that actually authenticate to a
# cloud provider get `id-token: write`, and every one of those jobs is bound to
# a deployment environment (`aws-db-<env>`, `gcp-db-<env>`, `azure-db-<env>`).
# `validate` and `summary` never authenticate, so they stay on the `contents:
# read` floor below.
permissions:
  contents: read

on:
  workflow_dispatch:
    inputs:
      cloud:
        description: 'Cloud provider'
        required: true
        type: choice
        options: [aws, gcp, azure, all]
      environment:
        description: 'Environment'
        required: true
        type: choice
        options: [dev, staging, prod]
      direction:
        description: 'Migration direction'
        required: true
        type: choice
        options: [up, down]
        default: up
      steps:
        description: 'Number of migrations to apply/rollback (0 = all; for direction=down an explicit positive value is required)'
        required: false
        type: number
        default: 0
      confirm:
        description: 'Type "rollback-prod" to confirm a down migration on prod (ignored otherwise)'
        required: false
        type: string
        default: ''
      compute_platform:
        description: 'AWS compute platform whose database to migrate (terraform/environments/aws has two independent RDS instances, one per namespace; ignored for gcp/azure)'
        required: false
        type: choice
        options: [lambda, fargate]
        default: lambda
  workflow_call:
    inputs:
      cloud:
        required: true
        type: string
      environment:
        required: true
        type: string
      direction:
        required: false
        type: string
        default: up
      compute_platform:
        required: false
        type: string
        default: lambda

env:
  MIGRATIONS_PATH: internal/database/postgres/migrations
  # Must satisfy `required_version = ">= 1.10.0"` declared by every
  # terraform/environments/*/main.tf. Keep in sync with ci.yml/pre-commit.yml.
  TF_VERSION: '1.10.5'

jobs:
  # Validate migration request
  validate:
    name: Validate Migration Request
    runs-on: ubuntu-latest
    permissions:
      contents: read
    outputs:
      is_safe: ${{ steps.check.outputs.is_safe }}

    steps:
      - name: Checkout code
        uses: actions/checkout@93cb6efe18208431cddfb8368fd83d5badbf9bfd # v5.0.1
        with:
          persist-credentials: false

      - name: Safety checks
        id: check
        env:
          CLOUD: ${{ inputs.cloud }}
          ENVIRONMENT: ${{ inputs.environment }}
          DIRECTION: ${{ inputs.direction }}
          STEPS: ${{ inputs.steps }}
          CONFIRM: ${{ inputs.confirm }}
          COMPUTE_PLATFORM: ${{ inputs.compute_platform }}
        run: |
          set -uo pipefail
          IS_SAFE=true

          # `workflow_call` never declares a `steps` input, so on that path this
          # renders empty. Treat that the same as the explicit default of "0"
          # (apply/rollback everything) rather than rejecting it outright.
          STEPS="${STEPS:-0}"

          # Both triggers default `compute_platform` to "lambda", but
          # workflow_call's is a plain `type: string` with no `choice`
          # enforcement, so re-validate it here like every other input.
          COMPUTE_PLATFORM="${COMPUTE_PLATFORM:-lambda}"

          # workflow_dispatch's cloud/environment/direction are `type: choice`,
          # enforced server-side before the run starts. workflow_call's are plain
          # `type: string` with no such enforcement, so re-validate them here
          # against the same allowlist regardless of which trigger fired.
          case "$CLOUD" in
            aws|gcp|azure|all) ;;
            *)
              echo "❌ Invalid cloud provider: '$CLOUD' (expected aws, gcp, azure, or all)"
              IS_SAFE=false
              ;;
          esac

          case "$ENVIRONMENT" in
            dev|staging|prod) ;;
            *)
              echo "❌ Invalid environment: '$ENVIRONMENT' (expected dev, staging, or prod)"
              IS_SAFE=false
              ;;
          esac

          case "$DIRECTION" in
            up|down) ;;
            *)
              echo "❌ Invalid direction: '$DIRECTION' (expected up or down)"
              IS_SAFE=false
              ;;
          esac

          case "$COMPUTE_PLATFORM" in
            lambda|fargate) ;;
            *)
              echo "❌ Invalid compute_platform: '$COMPUTE_PLATFORM' (expected lambda or fargate)"
              IS_SAFE=false
              ;;
          esac

          # Check if migrations directory exists
          if [ ! -d "$MIGRATIONS_PATH" ]; then
            echo "❌ Migrations directory not found: $MIGRATIONS_PATH"
            IS_SAFE=false
          fi

          # `steps` must be a non-negative integer regardless of direction: 0
          # means "everything" for direction=up, and direction=down requires an
          # explicit positive value (checked separately below). Reject anything
          # else before it can reach a migrate invocation in either direction.
          if ! [[ "$STEPS" =~ ^(0|[1-9][0-9]*)$ ]]; then
            echo "❌ 'steps' must be a non-negative integer (got '$STEPS')."
            IS_SAFE=false
          fi

          # Down migrations must specify an explicit positive step count.
          # steps=0 (the default) would run 'migrate down -all' and drop the
          # entire schema, so it is rejected here for direction=down.
          if [[ "$DIRECTION" == "down" ]]; then
            if ! [[ "$STEPS" =~ ^[1-9][0-9]*$ ]]; then
              echo "❌ direction=down requires an explicit positive 'steps' value (got '$STEPS')."
              echo "Rolling back ALL migrations at once is not supported by this workflow."
              IS_SAFE=false
            fi

            # Production down migrations additionally require typed confirmation.
            if [[ "$ENVIRONMENT" == "prod" ]]; then
              echo "⚠️ WARNING: Attempting to rollback migrations on PRODUCTION"
              echo "This operation is destructive and may cause data loss!"
              if [[ "$CONFIRM" != "rollback-prod" ]]; then
                echo "❌ Production rollback requires typing 'rollback-prod' in the 'confirm' input."
                IS_SAFE=false
              fi
            fi
          fi

          # Check migration files
          if [ -d "$MIGRATIONS_PATH" ]; then
            UP_COUNT=$(find "$MIGRATIONS_PATH" -maxdepth 1 -type f -name '*.up.sql' 2>/dev/null | wc -l)
            DOWN_COUNT=$(find "$MIGRATIONS_PATH" -maxdepth 1 -type f -name '*.down.sql' 2>/dev/null | wc -l)

            echo "Migration files found:"
            echo "  Up migrations: $UP_COUNT"
            echo "  Down migrations: $DOWN_COUNT"

            if [ "$UP_COUNT" -eq 0 ]; then
              echo "❌ No migration files found"
              IS_SAFE=false
            fi
          fi

          echo "is_safe=$IS_SAFE" >> "$GITHUB_OUTPUT"

          if [[ "$IS_SAFE" != "true" ]]; then
            echo "Validation failed; refusing to run migrations."
            exit 1
          fi

      - name: Display migration plan
        env:
          CLOUD: ${{ inputs.cloud }}
          ENVIRONMENT: ${{ inputs.environment }}
          DIRECTION: ${{ inputs.direction }}
          STEPS: ${{ inputs.steps || 'all' }}
        run: |
          set -uo pipefail
          {
            echo "## Database Migration Plan"
            echo ""
            echo "**Cloud:** $CLOUD"
            echo "**Environment:** $ENVIRONMENT"
            echo "**Direction:** $DIRECTION"
            echo "**Steps:** $STEPS"
            echo ""

            if [[ "$ENVIRONMENT" == "prod" ]] && [[ "$DIRECTION" == "down" ]]; then
              echo "⚠️ **WARNING:** This will rollback migrations on PRODUCTION!"
            fi
          } >> "$GITHUB_STEP_SUMMARY"

  # Run AWS migrations
  migrate-aws:
    name: Migrate AWS Database
    runs-on: ubuntu-latest
    needs: validate
    if: |
      needs.validate.outputs.is_safe == 'true' &&
      (inputs.cloud == 'aws' || inputs.cloud == 'all')
    # Serializes migrations against one database: two dispatches for the same
    # cloud and environment must not run `migrate` concurrently against the same
    # schema_migrations table (#1593). Deliberately NOT the `aws-tfstate-*` group
    # deploy-aws-lambda.yml and rollback.yml use: this job only runs `terraform
    # init` + `terraform output -raw` to read the DB endpoint, takes no state
    # lock, and the resource needing protection is the database, not the state
    # file. Different clouds, and the same cloud in different environments, are
    # different databases and stay concurrent.
    #
    # `inputs.environment` is a required `choice` on `workflow_dispatch`, so that
    # path is constrained to dev|staging|prod server-side. On the `workflow_call`
    # path it is a plain `type: string`; `required: true` makes the caller pass
    # the key but GitHub does not enforce that the value is non-empty, and an
    # empty suffix would collapse every environment into one group. The fallback
    # below keeps such a call in its own group instead. `validate` rejects an
    # environment outside the allowlist before this job runs, so the sentinel
    # should be unreachable in practice; it is here so the group can never be
    # silently ambiguous if that ordering ever changes.
    #
    # `cancel-in-progress: false` because cancelling mid-`migrate` leaves
    # schema_migrations dirty, which then needs the CUDLY_FORCE_MIGRATION_VERSION
    # recovery path in internal/database/postgres/migrations/migrate.go. Two
    # consequences, both accepted as better than concurrent writers to one
    # database:
    #   - GitHub keeps one pending entry per group, so a queued migration can be
    #     evicted by a later dispatch. Unlike rollback.yml, the `summary` job
    #     below does not exit 1 on a non-success result, so an evicted migration
    #     leaves the run green; read the per-cloud results it prints.
    #   - if the `environment:` binding below carries required reviewers, it is
    #     undocumented whether a job parked awaiting approval holds its group.
    concurrency:
      group: db-migrate-aws-${{ inputs.environment || 'unset' }}
      cancel-in-progress: false
    permissions:
      id-token: write
      contents: read
    environment:
      name: aws-db-${{ inputs.environment }}

    steps:
      - name: Checkout code
        uses: actions/checkout@93cb6efe18208431cddfb8368fd83d5badbf9bfd # v5.0.1
        with:
          persist-credentials: false

      - name: Set up Go
        uses: actions/setup-go@4a3601121dd01d1626a1e23e37211e3254c1c06c # v6.4.0
        with:
          go-version-file: go.mod

      - name: Install golang-migrate
        # No @version suffix: installed as a package of this module, so the
        # migrate version and its dependency set come from go.mod, the same way
        # the Dockerfile and `make install-tools` build it. -tags 'pgx5' keeps
        # lib/pq out of the binary that talks to the production database.
        # See issue #1849.
        run: go install -tags 'pgx5' github.com/golang-migrate/migrate/v4/cmd/migrate

      - name: Configure AWS credentials
        uses: aws-actions/configure-aws-credentials@d979d5b3a71173a29b74b5b88418bfda9437d885 # v6.1.1
        with:
          role-to-assume: ${{ vars.AWS_ROLE_TO_ASSUME }}
          aws-region: ${{ vars.AWS_REGION || 'us-east-1' }}

      - name: Setup Terraform
        uses: hashicorp/setup-terraform@dfe3c3f87815947d99a8997f908cb6525fc44e9e # v4.0.1
        with:
          terraform_version: ${{ env.TF_VERSION }}

      - name: Get database endpoint from Terraform
        id: get-endpoint
        env:
          TF_BACKEND: ${{ secrets.TF_BACKEND_AWS }}
          ENVIRONMENT: ${{ inputs.environment }}
          COMPUTE_PLATFORM: ${{ inputs.compute_platform || 'lambda' }}
        run: |
          set -euo pipefail
          cd terraform/environments/aws

          # terraform/environments/aws is one root applied into two independent
          # state objects, each with its own RDS instance (module.database has
          # no `count` on compute_platform): github-<env>/ for Lambda deploys,
          # github-fargate-<env>/ for Fargate. Picking the wrong one migrates a
          # database nobody is serving traffic from while the live one stays on
          # the old schema, so the namespace is an explicit operator input
          # (`inputs.compute_platform`), never guessed. `validate` already
          # rejects anything outside lambda|fargate before this job runs.
          case "$COMPUTE_PLATFORM" in
            lambda)  STATE_KEY="github-${ENVIRONMENT}/terraform.tfstate" ;;
            fargate) STATE_KEY="github-fargate-${ENVIRONMENT}/terraform.tfstate" ;;
            *)
              echo "::error::Unknown compute_platform: '$COMPUTE_PLATFORM'"
              exit 1
              ;;
          esac
          printf '%s\nkey = "%s"\n' "$TF_BACKEND" "$STATE_KEY" > /tmp/backend.tfbackend
          terraform init -backend-config=/tmp/backend.tfbackend

          # No `2>/dev/null || echo ""` here: letting terraform's own error
          # reach the log is what tells an operator WHICH cause they hit
          # (output renamed, empty state for this environment/platform, wrong
          # key, insufficient IAM on the state bucket) instead of the single
          # undifferentiated "Failed to get database endpoint" below.
          DB_ENDPOINT=$(terraform output -raw database_proxy_endpoint)
          if [ -z "$DB_ENDPOINT" ]; then
            echo "::error::terraform output returned an empty database_proxy_endpoint for compute_platform=$COMPUTE_PLATFORM, environment=$ENVIRONMENT"
            exit 1
          fi
          echo "endpoint=$DB_ENDPOINT" >> "$GITHUB_OUTPUT"

      - name: Run migrations
        env:
          # migrate reads the password from PGPASSWORD (pgx), so it never goes
          # into the URL and needs no percent-encoding or derived-value mask.
          PGPASSWORD: ${{ secrets.DB_PASSWORD_AWS }}
          DB_ENDPOINT: ${{ steps.get-endpoint.outputs.endpoint }}
          DIRECTION: ${{ inputs.direction }}
          STEPS: ${{ inputs.steps }}
        run: |
          set -uo pipefail
          # pgx5://, not postgresql://: migrate is installed with -tags 'pgx5'
          # above and golang-migrate dispatches on the URL scheme (issue #1849).
          DB_URL="pgx5://cudly@${DB_ENDPOINT}:5432/cudly?sslmode=require"

          # `validate` already rejects a malformed `steps`/`direction` before this
          # job is ever reached, but this job sits behind an environment gate and
          # is the thing that actually runs `migrate`, so the checks are repeated
          # here against the re-parsed shell variable as defense in depth.
          STEPS="${STEPS:-0}"

          if [[ "$DIRECTION" == "up" ]]; then
            if ! [[ "$STEPS" =~ ^(0|[1-9][0-9]*)$ ]]; then
              echo "❌ 'steps' must be a non-negative integer (got '$STEPS')."
              exit 1
            fi
            if [[ "$STEPS" == "0" ]]; then
              echo "Applying all pending migrations..."
              migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" up
            else
              echo "Applying $STEPS migration(s)..."
              migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" up "$STEPS"
            fi
          else
            if ! [[ "$STEPS" =~ ^[1-9][0-9]*$ ]]; then
              echo "❌ Refusing to roll back without an explicit positive 'steps' value."
              exit 1
            fi
            echo "Rolling back $STEPS migration(s)..."
            migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" down "$STEPS"
          fi

      - name: Get migration version
        env:
          # migrate reads the password from PGPASSWORD (pgx), so it never goes
          # into the URL and needs no percent-encoding or derived-value mask.
          PGPASSWORD: ${{ secrets.DB_PASSWORD_AWS }}
          DB_ENDPOINT: ${{ steps.get-endpoint.outputs.endpoint }}
        run: |
          set -uo pipefail
          # pgx5://, not postgresql://: migrate is installed with -tags 'pgx5'
          # above and golang-migrate dispatches on the URL scheme (issue #1849).
          DB_URL="pgx5://cudly@${DB_ENDPOINT}:5432/cudly?sslmode=require"
          VERSION=$(migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" version 2>&1 || echo "unknown")
          echo "Current migration version: $VERSION"
          echo "MIGRATION_VERSION=$VERSION" >> "$GITHUB_ENV"

  # Run GCP migrations
  migrate-gcp:
    name: Migrate GCP Database
    runs-on: ubuntu-latest
    needs: validate
    if: |
      needs.validate.outputs.is_safe == 'true' &&
      (inputs.cloud == 'gcp' || inputs.cloud == 'all')
    # Serializes migrations against the GCP database for one environment
    # (#1593). A different database from `migrate-aws`'s, so a different group:
    # `cloud=all` still migrates the three clouds concurrently. The group-key,
    # empty-suffix and `cancel-in-progress: false` reasoning documented on
    # `migrate-aws` applies here identically.
    concurrency:
      group: db-migrate-gcp-${{ inputs.environment || 'unset' }}
      cancel-in-progress: false
    permissions:
      id-token: write
      contents: read
    environment:
      name: gcp-db-${{ inputs.environment }}

    steps:
      - name: Checkout code
        uses: actions/checkout@93cb6efe18208431cddfb8368fd83d5badbf9bfd # v5.0.1
        with:
          persist-credentials: false

      - name: Set up Go
        uses: actions/setup-go@4a3601121dd01d1626a1e23e37211e3254c1c06c # v6.4.0
        with:
          go-version-file: go.mod

      - name: Install golang-migrate
        # No @version suffix: installed as a package of this module, so the
        # migrate version and its dependency set come from go.mod, the same way
        # the Dockerfile and `make install-tools` build it. -tags 'pgx5' keeps
        # lib/pq out of the binary that talks to the production database.
        # See issue #1849.
        run: go install -tags 'pgx5' github.com/golang-migrate/migrate/v4/cmd/migrate

      - name: Authenticate to Google Cloud
        uses: google-github-actions/auth@7c6bc770dae815cd3e89ee6cdf493a5fab2cc093 # v3.0.0
        with:
          workload_identity_provider: ${{ vars.GCP_WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ vars.GCP_SERVICE_ACCOUNT }}

      - name: Set up Cloud SDK
        uses: google-github-actions/setup-gcloud@aa5489c8933f4cc7a4f7d45035b3b1440c9c10db # v3.0.1

      - name: Setup Terraform
        uses: hashicorp/setup-terraform@dfe3c3f87815947d99a8997f908cb6525fc44e9e # v4.0.1
        with:
          terraform_version: ${{ env.TF_VERSION }}

      - name: Get database endpoint from Terraform
        id: get-endpoint
        env:
          TF_BACKEND: ${{ secrets.TF_BACKEND_GCP }}
          ENVIRONMENT: ${{ inputs.environment }}
        run: |
          set -euo pipefail
          printf '%s\nprefix = "github-%s"\n' "$TF_BACKEND" "$ENVIRONMENT" > /tmp/backend.tfbackend
          cd terraform/environments/gcp
          terraform init -backend-config=/tmp/backend.tfbackend

          # No `2>/dev/null || echo ""`: let terraform's own error reach the
          # log instead of collapsing every distinct cause into one message.
          DB_ENDPOINT=$(terraform output -raw database_private_ip)
          if [ -z "$DB_ENDPOINT" ]; then
            echo "::error::terraform output returned an empty database_private_ip for environment=$ENVIRONMENT"
            exit 1
          fi
          echo "endpoint=$DB_ENDPOINT" >> "$GITHUB_OUTPUT"

      - name: Run migrations
        env:
          # migrate reads the password from PGPASSWORD (pgx), so it never goes
          # into the URL and needs no percent-encoding or derived-value mask.
          PGPASSWORD: ${{ secrets.DB_PASSWORD_GCP }}
          DB_ENDPOINT: ${{ steps.get-endpoint.outputs.endpoint }}
          DIRECTION: ${{ inputs.direction }}
          STEPS: ${{ inputs.steps }}
        run: |
          set -uo pipefail
          # pgx5://, not postgresql://: migrate is installed with -tags 'pgx5'
          # above and golang-migrate dispatches on the URL scheme (issue #1849).
          DB_URL="pgx5://cudly@${DB_ENDPOINT}:5432/cudly?sslmode=require"

          STEPS="${STEPS:-0}"

          if [[ "$DIRECTION" == "up" ]]; then
            if ! [[ "$STEPS" =~ ^(0|[1-9][0-9]*)$ ]]; then
              echo "❌ 'steps' must be a non-negative integer (got '$STEPS')."
              exit 1
            fi
            if [[ "$STEPS" == "0" ]]; then
              migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" up
            else
              migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" up "$STEPS"
            fi
          else
            if ! [[ "$STEPS" =~ ^[1-9][0-9]*$ ]]; then
              echo "❌ Refusing to roll back without an explicit positive 'steps' value."
              exit 1
            fi
            migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" down "$STEPS"
          fi

  # Run Azure migrations
  migrate-azure:
    name: Migrate Azure Database
    runs-on: ubuntu-latest
    needs: validate
    if: |
      needs.validate.outputs.is_safe == 'true' &&
      (inputs.cloud == 'azure' || inputs.cloud == 'all')
    # Serializes migrations against the Azure database for one environment
    # (#1593). A different database from `migrate-aws`'s and `migrate-gcp`'s, so
    # a different group: `cloud=all` still migrates the three clouds
    # concurrently. The group-key, empty-suffix and `cancel-in-progress: false`
    # reasoning documented on `migrate-aws` applies here identically.
    concurrency:
      group: db-migrate-azure-${{ inputs.environment || 'unset' }}
      cancel-in-progress: false
    permissions:
      id-token: write
      contents: read
    environment:
      name: azure-db-${{ inputs.environment }}

    steps:
      - name: Checkout code
        uses: actions/checkout@93cb6efe18208431cddfb8368fd83d5badbf9bfd # v5.0.1
        with:
          persist-credentials: false

      - name: Set up Go
        uses: actions/setup-go@4a3601121dd01d1626a1e23e37211e3254c1c06c # v6.4.0
        with:
          go-version-file: go.mod

      - name: Install golang-migrate
        # No @version suffix: installed as a package of this module, so the
        # migrate version and its dependency set come from go.mod, the same way
        # the Dockerfile and `make install-tools` build it. -tags 'pgx5' keeps
        # lib/pq out of the binary that talks to the production database.
        # See issue #1849.
        run: go install -tags 'pgx5' github.com/golang-migrate/migrate/v4/cmd/migrate

      - name: Azure Login
        uses: azure/login@532459ea530d8321f2fb9bb10d1e0bcf23869a43 # v3.0.0
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

      - name: Setup Terraform
        uses: hashicorp/setup-terraform@dfe3c3f87815947d99a8997f908cb6525fc44e9e # v4.0.1
        with:
          terraform_version: ${{ env.TF_VERSION }}

      - name: Get database endpoint from Terraform
        id: get-endpoint
        env:
          TF_BACKEND: ${{ secrets.TF_BACKEND_AZURE }}
          ENVIRONMENT: ${{ inputs.environment }}
        run: |
          set -euo pipefail
          printf '%s\nkey = "github-%s.terraform.tfstate"\n' "$TF_BACKEND" "$ENVIRONMENT" > /tmp/backend.tfbackend
          cd terraform/environments/azure
          terraform init -backend-config=/tmp/backend.tfbackend

          # No `2>/dev/null || echo ""`: let terraform's own error reach the
          # log instead of collapsing every distinct cause into one message.
          DB_ENDPOINT=$(terraform output -raw database_server_fqdn)
          if [ -z "$DB_ENDPOINT" ]; then
            echo "::error::terraform output returned an empty database_server_fqdn for environment=$ENVIRONMENT"
            exit 1
          fi
          echo "endpoint=$DB_ENDPOINT" >> "$GITHUB_OUTPUT"

      - name: Run migrations
        env:
          # migrate reads the password from PGPASSWORD (pgx), so it never goes
          # into the URL and needs no percent-encoding or derived-value mask.
          PGPASSWORD: ${{ secrets.DB_PASSWORD_AZURE }}
          DB_ENDPOINT: ${{ steps.get-endpoint.outputs.endpoint }}
          DIRECTION: ${{ inputs.direction }}
          STEPS: ${{ inputs.steps }}
        run: |
          set -uo pipefail
          # pgx5://, not postgresql://: migrate is installed with -tags 'pgx5'
          # above and golang-migrate dispatches on the URL scheme (issue #1849).
          DB_URL="pgx5://cudly@${DB_ENDPOINT}:5432/cudly?sslmode=require"

          STEPS="${STEPS:-0}"

          if [[ "$DIRECTION" == "up" ]]; then
            if ! [[ "$STEPS" =~ ^(0|[1-9][0-9]*)$ ]]; then
              echo "❌ 'steps' must be a non-negative integer (got '$STEPS')."
              exit 1
            fi
            if [[ "$STEPS" == "0" ]]; then
              migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" up
            else
              migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" up "$STEPS"
            fi
          else
            if ! [[ "$STEPS" =~ ^[1-9][0-9]*$ ]]; then
              echo "❌ Refusing to roll back without an explicit positive 'steps' value."
              exit 1
            fi
            migrate -path "$MIGRATIONS_PATH" -database "$DB_URL" down "$STEPS"
          fi

  # Summary
  summary:
    name: Migration Summary
    runs-on: ubuntu-latest
    permissions:
      contents: read
    needs: [validate, migrate-aws, migrate-gcp, migrate-azure]
    if: always()

    steps:
      - name: Post summary
        env:
          CLOUD: ${{ inputs.cloud }}
          ENVIRONMENT: ${{ inputs.environment }}
          DIRECTION: ${{ inputs.direction }}
          RESULT_AWS: ${{ needs.migrate-aws.result }}
          RESULT_GCP: ${{ needs.migrate-gcp.result }}
          RESULT_AZURE: ${{ needs.migrate-azure.result }}
        run: |
          set -uo pipefail
          {
            echo "## Database Migration Results"
            echo ""
            echo "**Cloud:** $CLOUD"
            echo "**Environment:** $ENVIRONMENT"
            echo "**Direction:** $DIRECTION"
            echo ""

            echo "### Results"

            if [[ "$CLOUD" == "aws" ]] || [[ "$CLOUD" == "all" ]]; then
              echo "- AWS: $RESULT_AWS"
            fi

            if [[ "$CLOUD" == "gcp" ]] || [[ "$CLOUD" == "all" ]]; then
              echo "- GCP: $RESULT_GCP"
            fi

            if [[ "$CLOUD" == "azure" ]] || [[ "$CLOUD" == "all" ]]; then
              echo "- Azure: $RESULT_AZURE"
            fi
          } >> "$GITHUB_STEP_SUMMARY"