awoooi/.gitea/workflows/run-migration.yml

# ADR-090-B: Gitea CI 自動 migration workflow
# 建立時間: 2026-04-18 台北時區
# 建立者: ogt + Claude Opus 4.7 (1M)
#
# 目的: 每次 main 分支有新 migration SQL 檔,自動:
#   1. 用 MIGRATION_DATABASE_URL (awoooi_migrator 限權帳號) 連 PG
#   2. 只跑「新增」的 migration (比對已執行列表)
#   3. 跑後寫 asset_discovery_run + automation_operation_log 記錄
#   4. 失敗自動 rollback (single transaction + ON_ERROR_STOP)
#
# 觸發: push to main,且 apps/api/migrations/ 有變更

name: run-migration

on:
  push:
    branches: [main]
    paths:
      - 'apps/api/migrations/*.sql'
  workflow_dispatch:

env:
  TELEGRAM_ALERT_CHAT_ID: "-1003711974679"

jobs:
  migrate:
    runs-on: ubuntu-latest  # 或 self-hosted runner on 110

    steps:
      - name: Checkout
        uses: actions/checkout@v4
        with:
          fetch-depth: 2  # 需比對上一個 commit

      - name: Install migration tools
        run: |
          set -euo pipefail
          missing=""
          for bin in psql jq curl; do
            if ! command -v "$bin" >/dev/null 2>&1; then
              missing="$missing $bin"
            fi
          done
          if [ -z "$missing" ]; then
            exit 0
          fi
          if command -v apt-get >/dev/null 2>&1; then
            apt-get update -qq
            apt-get install -y -q postgresql-client jq curl
          elif command -v apk >/dev/null 2>&1; then
            apk add --no-cache postgresql-client jq curl
          else
            echo "::error::missing required tools:$missing"
            exit 1
          fi

      - name: Identify new migrations
        id: diff
        run: |
          ALL_NEW_FILES=$(git diff --no-renames --name-only --diff-filter=A HEAD~1 HEAD -- 'apps/api/migrations/*.sql' || true)
          NEW_FILES=$(echo "$ALL_NEW_FILES" | grep -Ev '(_down|rollback)\.sql$' || true)
          SKIPPED_ROLLBACK_FILES=$(echo "$ALL_NEW_FILES" | grep -E '(_down|rollback)\.sql$' || true)
          echo "new_files<<EOF" >> $GITHUB_OUTPUT
          echo "$NEW_FILES" >> $GITHUB_OUTPUT
          echo "EOF" >> $GITHUB_OUTPUT
          echo "=== New migration files ==="
          echo "$NEW_FILES"
          if [ -n "$SKIPPED_ROLLBACK_FILES" ]; then
            echo "=== Rollback/down migrations skipped by design ==="
            echo "$SKIPPED_ROLLBACK_FILES"
          fi

      - name: Apply new migrations
        if: steps.diff.outputs.new_files != ''
        env:
          # 從 Gitea secrets 取，不直接明碼輸出。
          # MIGRATION_DATABASE_URL 是限權帳號；DATABASE_URL 只在 PostgreSQL
          # 明確回報「必須是 table owner」時作為受控 fallback。
          PGURL: ${{ secrets.MIGRATION_DATABASE_URL }}
          OWNER_PGURL: ${{ secrets.DATABASE_URL }}
        run: |
          set -euo pipefail
          if [ -z "$PGURL" ]; then
            echo "::error::MIGRATION_DATABASE_URL secret not set in Gitea"
            exit 1
          fi
          PGURL_PSQL="${PGURL/postgresql+asyncpg:\/\//postgresql:\/\/}"
          OWNER_PGURL_PSQL="${OWNER_PGURL/postgresql+asyncpg:\/\//postgresql:\/\/}"

          apply_migration() {
            local url="$1"
            local file="$2"
            psql "$url" \
              -v ON_ERROR_STOP=1 \
              --single-transaction \
              -f "$file"
          }

          # 套用每個新檔 (single transaction per file)
          echo "${{ steps.diff.outputs.new_files }}" | while IFS= read -r file; do
            [ -z "$file" ] && continue
            echo "=== Applying: $file ==="
            migration_err="$(mktemp)"
            if ! apply_migration "$PGURL_PSQL" "$file" 2>"$migration_err"; then
              if grep -Eq "(must be owner of table|permission denied for table)" "$migration_err"; then
                if [ -z "$OWNER_PGURL_PSQL" ]; then
                  cat "$migration_err" >&2
                  echo "::error::migration requires table owner but DATABASE_URL secret is not set"
                  exit 1
                fi
                echo "::warning::migration requires table owner; retrying with owner connection"
                apply_migration "$OWNER_PGURL_PSQL" "$file"
              else
                cat "$migration_err" >&2
                exit 1
              fi
            fi
            rm -f "$migration_err"
            echo "=== OK: $file ==="
          done

      - name: Seed asset_discovery_run (audit)
        if: steps.diff.outputs.new_files != ''
        env:
          PGURL: ${{ secrets.MIGRATION_DATABASE_URL }}
          OWNER_PGURL: ${{ secrets.DATABASE_URL }}
        run: |
          set -euo pipefail
          if [ -z "$PGURL" ]; then
            echo "::error::MIGRATION_DATABASE_URL secret not set in Gitea"
            exit 1
          fi
          PGURL_PSQL="${PGURL/postgresql+asyncpg:\/\//postgresql:\/\/}"
          OWNER_PGURL_PSQL="${OWNER_PGURL/postgresql+asyncpg:\/\//postgresql:\/\/}"
          FILES_JSON=$(echo "${{ steps.diff.outputs.new_files }}" | jq -Rn '[inputs | select(length > 0)]')
          SUMMARY_JSON=$(jq -cn \
            --arg commit_sha "${{ github.sha }}" \
            --argjson files "$FILES_JSON" \
            '{type: "ci_migration", commit_sha: $commit_sha, files: $files}')
          SUMMARY_JSON_SQL=${SUMMARY_JSON//\'/\'\'}

          seed_audit() {
            local url="$1"
            psql "$url" -v ON_ERROR_STOP=1 <<SQL
            INSERT INTO asset_discovery_run (
              run_id, triggered_by, scope, scan_depth, status,
              started_at, ended_at, tools_used, summary
            ) VALUES (
              gen_random_uuid(),
              'ci:gitea',
              ARRAY['schema_migration'],
              'full',
              'success',
              NOW(),
              NOW(),
              '{"psql": 1, "gitea_ci": 1}'::jsonb,
              '${SUMMARY_JSON_SQL}'::jsonb
            );
          SQL
          }

          audit_err="$(mktemp)"
          if ! seed_audit "$PGURL_PSQL" 2>"$audit_err"; then
            if grep -q "permission denied for table asset_discovery_run" "$audit_err"; then
              if [ -z "$OWNER_PGURL_PSQL" ]; then
                cat "$audit_err" >&2
                echo "::error::audit requires table insert privilege but DATABASE_URL secret is not set"
                exit 1
              fi
              echo "::warning::audit requires owner connection; retrying with owner connection"
              seed_audit "$OWNER_PGURL_PSQL"
            else
              cat "$audit_err" >&2
              exit 1
            fi
          fi
          rm -f "$audit_err"

      - name: Notify Telegram (if configured)
        if: always()
        env:
          TG_TOKEN: ${{ secrets.TELEGRAM_BOT_TOKEN }}
          TG_CHAT: ${{ env.TELEGRAM_ALERT_CHAT_ID }}
        run: |
          STATUS="${{ job.status }}"
          CICD_STATUS="success"
          [ "$STATUS" != "success" ] && CICD_STATUS="failed"
          if AWOOI_CICD_STATUS="${CICD_STATUS}" \
            AWOOI_CICD_STAGE=run-migration \
            AWOOI_CICD_JOB_NAME="Migration CI" \
            AWOOI_CICD_COMMIT_SHA="${{ github.sha }}" \
            AWOOI_CICD_SUMMARY="Migration CI: ${STATUS}" \
            scripts/ci/notify-awoooi-cicd.sh; then
            echo "Migration notification mirrored through AWOOI API"
            exit 0
          fi
          if [ -n "$TG_TOKEN" ] && [ -n "$TG_CHAT" ]; then
            MSG="🗄️ Migration CI: \`${STATUS}\` — commit ${{ github.sha }}"
            curl -s -X POST "https://api.telegram.org/bot${TG_TOKEN}/sendMessage" \
              -d chat_id="${TG_CHAT}" \
              -d parse_mode="Markdown" \
              -d text="${MSG}" || true
          fi