awoooi/apps/api/migrations/adr090d_kpi_data_sources.sql

-- ADR-090-D: MASTER §7.1 北極星 KPI 資料源建立
-- 建立時間: 2026-04-18 晚 (台北時區)
-- 建立者: ogt + Claude Opus 4.7 (1M)
--
-- 背景:
--   MASTER §7.1 15 個 KPI 對標發現 4 張關鍵表根本沒建立,導致以下 KPI 永遠
--   量不到:
--     #3 fine-tune JSONL /week    → finetune_exports 表
--     #6 Declarative 修復使用率   → remediation_events 表
--     #10 notification_outcomes   → notification_outcomes 表
--
--   此 migration 補齊 3 張資料源表(idempotent)。
--
-- 對應 MASTER § 指標:
--   §3.3 D3 修復抽象(Imperative → Declarative)
--   §3.4 D4 學習深度(Fine-tune)
--   §3.6 D6 自我治理(通知品質)

-- ═══════════════════════════════════════════════════════════════════
-- 1. finetune_exports — Phase 3 Fine-tune JSONL 產出追蹤
-- ═══════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS finetune_exports (
    export_id         BIGSERIAL       PRIMARY KEY,
    export_type       TEXT            NOT NULL,  -- 'evidence_snapshot' | 'agent_session' | 'decision_outcome'
    source_table      TEXT,                      -- 來源表名 (incidents / agent_sessions ...)
    source_ids        TEXT[],                    -- 涵蓋的 source record ids
    file_path         TEXT,                      -- 匯出的 JSONL 檔案路徑
    record_count      INT             NOT NULL DEFAULT 0,
    size_bytes        BIGINT,
    checksum_sha256   TEXT,
    created_at        TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    metadata          JSONB           NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT finetune_export_type_valid CHECK (export_type IN (
        'evidence_snapshot','agent_session','decision_outcome',
        'incident_rca','playbook_outcome','rlhf_trace'
    ))
);

COMMENT ON TABLE finetune_exports IS
    'ADR-090-D: MASTER §7.1 #3 Fine-tune JSONL 產出追蹤。每次 finetune_exporter 匯出寫一筆。';

CREATE INDEX IF NOT EXISTS idx_finetune_exports_created
    ON finetune_exports(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_finetune_exports_type
    ON finetune_exports(export_type);


-- ═══════════════════════════════════════════════════════════════════
-- 2. remediation_events — Phase 5 Declarative 修復追蹤
-- ═══════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS remediation_events (
    event_id              BIGSERIAL       PRIMARY KEY,
    incident_id           TEXT,
    approval_id           TEXT,
    remediation_type      TEXT            NOT NULL, -- 'declarative' | 'imperative' | 'gitops_pr' | 'kubectl'
    action_name           TEXT,
    target_resource       TEXT,                     -- deployment/awoooi-api 等
    namespace             TEXT,
    dry_run               BOOLEAN         NOT NULL DEFAULT false,
    status                TEXT            NOT NULL, -- 'pending' | 'success' | 'failed' | 'rolled_back'
    error_message         TEXT,
    blast_radius_score    INT,
    duration_ms           INT,
    executed_by           TEXT,                     -- 'ai_agent' | 'human:ogt' | 'cron'
    triggered_by_op_id    UUID,                     -- 指向 automation_operation_log.op_id
    created_at            TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    completed_at          TIMESTAMPTZ,
    metadata              JSONB           NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT remediation_type_valid CHECK (remediation_type IN (
        'declarative','imperative','gitops_pr','kubectl','ansible','helm','argocd_sync'
    )),
    CONSTRAINT remediation_status_valid CHECK (status IN (
        'pending','success','failed','rolled_back','dry_run_ok','dry_run_failed'
    ))
);

COMMENT ON TABLE remediation_events IS
    'ADR-090-D: MASTER §7.1 #6 Declarative 修復使用率。每次 declarative_remediation 執行寫一筆。';

CREATE INDEX IF NOT EXISTS idx_remediation_events_time
    ON remediation_events(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_remediation_events_type
    ON remediation_events(remediation_type);
CREATE INDEX IF NOT EXISTS idx_remediation_events_incident
    ON remediation_events(incident_id) WHERE incident_id IS NOT NULL;


-- ═══════════════════════════════════════════════════════════════════
-- 3. notification_outcomes — 通知成果追蹤
-- ═══════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS notification_outcomes (
    outcome_id            BIGSERIAL       PRIMARY KEY,
    incident_id           TEXT,
    approval_id           TEXT,
    channel               TEXT            NOT NULL, -- 'telegram' | 'email' | 'slack' | 'webhook'
    notification_type     TEXT,                     -- TYPE-1/2/3/4/4D/5S/6B/7E/8M
    recipient             TEXT,                     -- chat_id / email / user
    message_id            TEXT,                     -- telegram message_id 等
    sent_at               TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    delivery_status       TEXT            NOT NULL, -- 'delivered' | 'failed' | 'pending'
    delivery_error        TEXT,
    -- 人類互動追蹤 (RLHF 語料黃金)
    user_action           TEXT,                     -- 'approved' | 'rejected' | 'silenced' | 'ignored' | 'no_response'
    user_action_at        TIMESTAMPTZ,
    user_comment          TEXT,
    -- 通知品質
    snoozed_count         INT             NOT NULL DEFAULT 0,
    time_to_action_sec    INT,                       -- 收到到按鈕按下的秒數
    metadata              JSONB           NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT notif_channel_valid CHECK (channel IN (
        'telegram','email','slack','webhook','sms','discord'
    )),
    CONSTRAINT notif_delivery_valid CHECK (delivery_status IN (
        'delivered','failed','pending','rate_limited'
    ))
);

COMMENT ON TABLE notification_outcomes IS
    'ADR-090-D: MASTER §7.1 #10 notification_outcomes 追蹤。每次 telegram_gateway 推送寫一筆,用戶按鈕觸發時 update user_action。';

CREATE INDEX IF NOT EXISTS idx_notification_outcomes_sent
    ON notification_outcomes(sent_at DESC);
CREATE INDEX IF NOT EXISTS idx_notification_outcomes_incident
    ON notification_outcomes(incident_id) WHERE incident_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_notification_outcomes_approval
    ON notification_outcomes(approval_id) WHERE approval_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_notification_outcomes_pending_action
    ON notification_outcomes(sent_at DESC)
    WHERE user_action IS NULL AND delivery_status='delivered';


-- ═══════════════════════════════════════════════════════════════════
-- 驗收 (執行後可手動跑)
-- ═══════════════════════════════════════════════════════════════════

-- SELECT table_name FROM information_schema.tables
-- WHERE table_schema='public'
--   AND table_name IN ('finetune_exports','remediation_events','notification_outcomes')
-- ORDER BY table_name;
-- 預期: 3 筆

-- SELECT conname FROM pg_constraint WHERE conrelid IN (
--   'finetune_exports'::regclass,
--   'remediation_events'::regclass,
--   'notification_outcomes'::regclass
-- ) AND contype='c' ORDER BY conname;