deploy-migrations.yml

name: Deploy Migrations

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]
  workflow_dispatch:

env:
  FORCE_JAVASCRIPT_ACTIONS_TO_NODE24: true

jobs:
  # ---------------------------------------------------------------------------
  # PR check: reset staging DB then push migrations for real execution
  # ---------------------------------------------------------------------------
  staging:
    if: github.event_name == 'pull_request'
    runs-on: ubuntu-latest
    # Only one staging validation at a time — later PRs queue behind earlier ones
    concurrency:
      group: staging-migrations
      cancel-in-progress: false

    env:
      SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
      SUPABASE_DB_PASSWORD: ${{ secrets.SUPABASE_STAGING_DB_PASSWORD }}
      SUPABASE_PROJECT_ID: ${{ secrets.SUPABASE_STAGING_PROJECT_ID }}

    steps:
      - name: Check staging secrets are configured
        id: check-secrets
        run: |
          if [ -z "$SUPABASE_PROJECT_ID" ] || [ -z "$SUPABASE_DB_PASSWORD" ]; then
            echo "⚠️ Staging secrets not configured — skipping migration validation."
            echo "Add SUPABASE_STAGING_PROJECT_ID and SUPABASE_STAGING_DB_PASSWORD repository secrets to enable."
            echo "skip=true" >> "$GITHUB_OUTPUT"
          else
            echo "skip=false" >> "$GITHUB_OUTPUT"
          fi

      - uses: actions/checkout@v6
        if: steps.check-secrets.outputs.skip == 'false'

      # supabase/setup-cli@v1 still targets node20; keep FORCE_JAVASCRIPT_ACTIONS_TO_NODE24 until they ship a node24 version
      - uses: supabase/setup-cli@v1
        if: steps.check-secrets.outputs.skip == 'false'
        with:
          version: latest

      - name: Link staging project
        if: steps.check-secrets.outputs.skip == 'false'
        run: supabase link --project-ref $SUPABASE_PROJECT_ID

      - name: Reset staging DB to clean state
        if: steps.check-secrets.outputs.skip == 'false'
        run: supabase db reset --linked --yes

      - name: Push migrations to staging
        if: steps.check-secrets.outputs.skip == 'false'
        run: supabase db push --yes

  # ---------------------------------------------------------------------------
  # Production deploy: runs on push to main
  # ---------------------------------------------------------------------------
  deploy:
    if: github.event_name == 'push' || github.event_name == 'workflow_dispatch'
    runs-on: ubuntu-latest

    env:
      SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
      SUPABASE_DB_PASSWORD: ${{ secrets.SUPABASE_DB_PASSWORD }}
      SUPABASE_PROJECT_ID: ${{ secrets.SUPABASE_PROJECT_ID }}

    steps:
      - uses: actions/checkout@v6

      # supabase/setup-cli@v1 still targets node20; keep FORCE_JAVASCRIPT_ACTIONS_TO_NODE24 until they ship a node24 version
      - uses: supabase/setup-cli@v1
        with:
          version: latest

      - name: Link Supabase project
        run: supabase link --project-ref $SUPABASE_PROJECT_ID

      - name: Deploy migrations
        run: supabase db push --yes --include-all

      - name: Verify critical tables exist
        env:
          SUPABASE_URL: ${{ secrets.NEXT_PUBLIC_SUPABASE_URL }}
          SUPABASE_KEY: ${{ secrets.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY }}
        run: |
          tables=(page_versions collections collection_views collection_rows comments)
          failed=()
          for table in "${tables[@]}"; do
            body=$(curl -s \
              "${SUPABASE_URL}/rest/v1/${table}?limit=1" \
              -H "apikey: ${SUPABASE_KEY}" \
              -H "Authorization: Bearer ${SUPABASE_KEY}" 2>&1)
            if echo "$body" | grep -q "PGRST205"; then
              echo "MISSING: $table — $body"
              failed+=("$table")
            else
              echo "OK: $table"
            fi
          done

          # Verify critical columns exist by selecting them explicitly.
          # PGRST204 means the column is absent from the schema cache.
          columns=(
            "pages:is_private"
            "pages:cover"
            "pages:share_token"
          )
          for entry in "${columns[@]}"; do
            table="${entry%%:*}"
            column="${entry##*:}"
            body=$(curl -s \
              "${SUPABASE_URL}/rest/v1/${table}?select=${column}&limit=1" \
              -H "apikey: ${SUPABASE_KEY}" \
              -H "Authorization: Bearer ${SUPABASE_KEY}" 2>&1)
            if echo "$body" | grep -qE "PGRST(204|205)"; then
              echo "MISSING COLUMN: ${table}.${column} — $body"
              failed+=("${table}.${column}}")
            else
              echo "OK: ${table}.${column}"
            fi
          done

          if [ ${#failed[@]} -gt 0 ]; then
            echo "Missing from schema cache: ${failed[*]}"
            exit 1
          fi