79 lines
2.7 KiB
SQL
79 lines
2.7 KiB
SQL
-- =============================================================================
|
||
-- Migration 037: action_plans source/status guardrails
|
||
-- 日期: 2026-05-12 台北
|
||
-- =============================================================================
|
||
-- 背景:
|
||
-- action_plans 是 Group A（CodeReview/OpenClaw）與 Group B（NemoTron）
|
||
-- 共用的 superset schema。migration 019 解了缺欄，但沒有 caller/source 護欄，
|
||
-- 新寫入容易變成 action_type、created_by 都空的不可歸因資料。
|
||
--
|
||
-- 設計:
|
||
-- 1. 使用 NOT VALID：不掃描歷史資料，但 PostgreSQL 仍會檢查新 INSERT/UPDATE。
|
||
-- 2. 不限制 plan_type/payload 組合，先只防止不可歸因與明顯非法狀態。
|
||
-- 3. 若未來新增 caller，需新增 migration 擴充白名單。
|
||
-- =============================================================================
|
||
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint
|
||
WHERE conname = 'chk_action_plans_source_marker'
|
||
) THEN
|
||
ALTER TABLE action_plans
|
||
ADD CONSTRAINT chk_action_plans_source_marker
|
||
CHECK (action_type IS NOT NULL OR created_by IS NOT NULL)
|
||
NOT VALID;
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint
|
||
WHERE conname = 'chk_action_plans_action_type'
|
||
) THEN
|
||
ALTER TABLE action_plans
|
||
ADD CONSTRAINT chk_action_plans_action_type
|
||
CHECK (
|
||
action_type IS NULL
|
||
OR action_type IN ('auto', 'code_review_fix', 'openclaw_recommendation')
|
||
)
|
||
NOT VALID;
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint
|
||
WHERE conname = 'chk_action_plans_created_by'
|
||
) THEN
|
||
ALTER TABLE action_plans
|
||
ADD CONSTRAINT chk_action_plans_created_by
|
||
CHECK (
|
||
created_by IS NULL
|
||
OR created_by IN (
|
||
'nemotron', 'openclaw', 'code_review_pipeline',
|
||
'ai_orchestrator', 'watcher_agent', 'agent_actions',
|
||
'elephant_alpha', 'manual', 'system'
|
||
)
|
||
)
|
||
NOT VALID;
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint
|
||
WHERE conname = 'chk_action_plans_status'
|
||
) THEN
|
||
ALTER TABLE action_plans
|
||
ADD CONSTRAINT chk_action_plans_status
|
||
CHECK (
|
||
status IS NULL
|
||
OR status IN (
|
||
'pending', 'approved', 'rejected', 'executed',
|
||
'auto_pending', 'auto_disabled', 'pending_review'
|
||
)
|
||
)
|
||
NOT VALID;
|
||
END IF;
|
||
END $$;
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE 'Migration 037 done: action_plans guardrail CHECK constraints added as NOT VALID';
|
||
END $$;
|