215 lines
8.0 KiB
YAML
215 lines
8.0 KiB
YAML
# 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:
|
||
SRE_GROUP_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 != ''
|
||
run: |
|
||
set -euo pipefail
|
||
# 從 Gitea secrets 取，不放 step-level env，避免 runner log 展開。
|
||
# MIGRATION_DATABASE_URL 是限權帳號；DATABASE_URL 只在 PostgreSQL
|
||
# 明確回報「必須是 table owner」時作為受控 fallback。
|
||
PGURL="$(cat <<'AWOOOI_SECRET_MIGRATION_DATABASE_URL'
|
||
${{ secrets.MIGRATION_DATABASE_URL }}
|
||
AWOOOI_SECRET_MIGRATION_DATABASE_URL
|
||
)"
|
||
OWNER_PGURL="$(cat <<'AWOOOI_SECRET_DATABASE_URL'
|
||
${{ secrets.DATABASE_URL }}
|
||
AWOOOI_SECRET_DATABASE_URL
|
||
)"
|
||
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 != ''
|
||
run: |
|
||
set -euo pipefail
|
||
PGURL="$(cat <<'AWOOOI_SECRET_MIGRATION_DATABASE_URL'
|
||
${{ secrets.MIGRATION_DATABASE_URL }}
|
||
AWOOOI_SECRET_MIGRATION_DATABASE_URL
|
||
)"
|
||
OWNER_PGURL="$(cat <<'AWOOOI_SECRET_DATABASE_URL'
|
||
${{ secrets.DATABASE_URL }}
|
||
AWOOOI_SECRET_DATABASE_URL
|
||
)"
|
||
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()
|
||
run: |
|
||
TG_TOKEN="$(cat <<'AWOOOI_SECRET_TG_TOKEN'
|
||
${{ secrets.TELEGRAM_BOT_TOKEN }}
|
||
AWOOOI_SECRET_TG_TOKEN
|
||
)"
|
||
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 "${{ env.SRE_GROUP_CHAT_ID }}" ]; then
|
||
MSG="🗄️ Migration CI: \`${STATUS}\` — commit ${{ github.sha }}"
|
||
curl -s -X POST "https://api.telegram.org/bot${TG_TOKEN}/sendMessage" \
|
||
-d chat_id="${{ env.SRE_GROUP_CHAT_ID }}" \
|
||
-d parse_mode="Markdown" \
|
||
-d text="${MSG}" || true
|
||
fi
|