f154ac022e
All checks were successful
CD Pipeline / tests (push) Successful in 1m34s
Code Review / ai-code-review (push) Successful in 28s
Type Sync Check / check-type-sync (push) Successful in 1m10s
CD Pipeline / build-and-deploy (push) Successful in 10m19s
CD Pipeline / post-deploy-checks (push) Successful in 3m1s
130 lines
4.2 KiB
YAML
130 lines
4.2 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'
|
|
|
|
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: |
|
|
NEW_FILES=$(git diff --name-only --diff-filter=A HEAD~1 HEAD -- 'apps/api/migrations/*.sql' || true)
|
|
echo "new_files<<EOF" >> $GITHUB_OUTPUT
|
|
echo "$NEW_FILES" >> $GITHUB_OUTPUT
|
|
echo "EOF" >> $GITHUB_OUTPUT
|
|
echo "=== New migration files ==="
|
|
echo "$NEW_FILES"
|
|
|
|
- name: Apply new migrations
|
|
if: steps.diff.outputs.new_files != ''
|
|
env:
|
|
# 從 Gitea secrets 取,不直接明碼
|
|
PGURL: ${{ secrets.MIGRATION_DATABASE_URL }}
|
|
run: |
|
|
set -euo pipefail
|
|
if [ -z "$PGURL" ]; then
|
|
echo "::error::MIGRATION_DATABASE_URL secret not set in Gitea"
|
|
exit 1
|
|
fi
|
|
|
|
# 套用每個新檔 (single transaction per file)
|
|
echo "${{ steps.diff.outputs.new_files }}" | while IFS= read -r file; do
|
|
[ -z "$file" ] && continue
|
|
echo "=== Applying: $file ==="
|
|
psql "$PGURL" \
|
|
-v ON_ERROR_STOP=1 \
|
|
--single-transaction \
|
|
-f "$file"
|
|
echo "=== OK: $file ==="
|
|
done
|
|
|
|
- name: Seed asset_discovery_run (audit)
|
|
if: steps.diff.outputs.new_files != ''
|
|
env:
|
|
PGURL: ${{ secrets.MIGRATION_DATABASE_URL }}
|
|
run: |
|
|
FILES_JSON=$(echo "${{ steps.diff.outputs.new_files }}" | jq -Rn '[inputs | select(length > 0)]')
|
|
psql "$PGURL" -c "
|
|
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,
|
|
jsonb_build_object(
|
|
'type', 'ci_migration',
|
|
'commit_sha', '${{ github.sha }}',
|
|
'files', $FILES_JSON
|
|
)
|
|
);
|
|
"
|
|
|
|
- name: Notify Telegram (if configured)
|
|
if: always()
|
|
env:
|
|
TG_TOKEN: ${{ secrets.TELEGRAM_BOT_TOKEN }}
|
|
TG_CHAT: ${{ env.TELEGRAM_ALERT_CHAT_ID }}
|
|
run: |
|
|
if [ -n "$TG_TOKEN" ] && [ -n "$TG_CHAT" ]; then
|
|
STATUS="${{ job.status }}"
|
|
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
|