ewoooc/migrations/027_create_rag_query_log.sql

-- =============================================================================
-- Migration 027: rag_query_log — RAG 查詢遙測 (audit log)
-- Operation Ollama-First v5.0 — Phase 11
-- 日期: 2026-05-03 台北
-- 對應戰役: ADR-029（Hermes-First 雙塔）+ Phase 11 RAG 自主學習迴圈
-- =============================================================================
-- 說明:
--   每次 RAG 召回（hermes_qa / openclaw_qa / etc.）寫一筆，append-only。
--   核心指標:
--     - hit_count       : top_k 召回實際命中數（threshold 過濾後）
--     - saved_call      : 命中且最終未升級到 LLM => 真實節省成本
--     - feedback_score  : Telegram 👍/👎 後填回（NULL = 尚未反饋）
--   與 ai_calls / mcp_calls 透過 request_id 串鏈，跨表 trace 同一邏輯請求。
--
-- 設計決策:
--   1. embedding 與 ai_insights 同維度 1024 (bge-m3)，可跨表計 cosine
--   2. ivfflat lists=100 對齊既有風格；資料量達 1M 後依 sqrt(N) 重建
--      （009 ai_insights 用 HNSW，但本表寫入頻繁 + 不需即時最近鄰 query
--       採 ivfflat 寫入便宜，weekly 排程 REINDEX 即可，詳見 design doc）
--   3. used_results BIGINT[] 紀錄命中的 ai_insights.id，方便事後召回率分析
--      （不加 FK；ai_insights 可能 archive，避免 cascade）
--   4. query_text 限 4KB；query_text 可能含 PII（用戶問題）—
--      90 天保留 + 後續 PromotionGate 過濾後才允許進 ai_insights
--   5. caller 與 ai_calls.caller 共白名單（不重複定義 CHECK，避免雙寫漂移；
--      由 application logger 端強制；DB 端僅檢長度）
--
-- 回滾腳本（緊急用）:
--   DROP INDEX IF EXISTS idx_rag_query_log_embedding;
--   DROP INDEX IF EXISTS idx_rag_query_log_request_id;
--   DROP INDEX IF EXISTS idx_rag_query_log_caller;
--   DROP INDEX IF EXISTS idx_rag_query_log_queried_at;
--   DROP TABLE IF EXISTS rag_query_log;
-- =============================================================================

CREATE TABLE IF NOT EXISTS rag_query_log (
    id              BIGSERIAL    PRIMARY KEY,
    queried_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    -- 與 ai_calls.caller 同一張白名單（hermes_qa / openclaw_qa / ...）
    caller          VARCHAR(64)  NOT NULL,

    -- 用戶查詢（PII 風險，限 4KB；不 normalize 以保留原始查詢樣貌）
    query_text      TEXT         NOT NULL,

    -- bge-m3 embedding（1024 維，與 ai_insights.embedding 同源；可 NULL = embedding 失敗仍記錄此次嘗試）
    query_embedding VECTOR(1024),

    -- 召回參數
    top_k           INTEGER      NOT NULL DEFAULT 5,
    threshold       NUMERIC(4,3) NOT NULL DEFAULT 0.85,

    -- 召回結果
    hit_count       INTEGER      NOT NULL DEFAULT 0,
    used_results    BIGINT[],                     -- 命中的 ai_insights.id 陣列（軟連結，不加 FK）

    -- 是否成功避免 LLM 呼叫（核心成本指標）
    saved_call      BOOLEAN      NOT NULL DEFAULT FALSE,

    -- Telegram 👍/👎 反饋（1-5；NULL = 未反饋）
    feedback_score  INTEGER,

    -- 與 ai_calls.request_id 串鏈
    request_id      VARCHAR(64),

    -- ─────── 護欄 (對齊 critic-A11 風格) ───────
    CONSTRAINT chk_rag_threshold CHECK (
        threshold BETWEEN 0 AND 1
    ),
    CONSTRAINT chk_rag_top_k CHECK (
        top_k BETWEEN 1 AND 50
    ),
    CONSTRAINT chk_rag_hit_count CHECK (
        hit_count >= 0 AND hit_count <= top_k
    ),
    CONSTRAINT chk_rag_query_size CHECK (
        octet_length(query_text) <= 4096
    ),
    CONSTRAINT chk_rag_feedback CHECK (
        feedback_score IS NULL OR feedback_score BETWEEN 1 AND 5
    ),
    -- saved_call=TRUE 必須有命中（hit_count > 0）才合理
    CONSTRAINT chk_rag_saved_consistent CHECK (
        (saved_call = FALSE) OR (hit_count > 0)
    )
);

-- ─────────────────────────────────────────────────────────────────────────────
-- 索引設計
-- ─────────────────────────────────────────────────────────────────────────────

-- (1) 時間範圍掃描（日報 / 命中率報表）
CREATE INDEX IF NOT EXISTS idx_rag_query_log_queried_at
    ON rag_query_log (queried_at DESC);

-- (2) caller 分布（哪個入口 RAG 命中率高）
CREATE INDEX IF NOT EXISTS idx_rag_query_log_caller
    ON rag_query_log (caller, queried_at DESC);

-- (3) request_id 串鏈（部分索引，sparse 不全建）
CREATE INDEX IF NOT EXISTS idx_rag_query_log_request_id
    ON rag_query_log (request_id)
    WHERE request_id IS NOT NULL;

-- (4) pgvector ivfflat（cosine similarity；只索引非 NULL embedding）
--     注意: ivfflat 須先有資料才能正確訓練 lists；空表建索引會 fallback exact scan，
--           Phase 11 灌入首批查詢後若效能退化，REINDEX CONCURRENTLY 重訓
CREATE INDEX IF NOT EXISTS idx_rag_query_log_embedding
    ON rag_query_log
    USING ivfflat (query_embedding vector_cosine_ops)
    WITH (lists = 100)
    WHERE query_embedding IS NOT NULL;

-- ─────────────────────────────────────────────────────────────────────────────
-- 權限
-- ─────────────────────────────────────────────────────────────────────────────
GRANT ALL PRIVILEGES ON rag_query_log TO momo;
GRANT USAGE, SELECT ON SEQUENCE rag_query_log_id_seq TO momo;

-- 註: 90 天保留由 scheduler 任務執行（與 ai_calls 對齊）:
--   DELETE FROM rag_query_log WHERE queried_at < NOW() - INTERVAL '90 days';
--   建議 03:30 跑（ai_calls 03:00 之後），避免 IO 尖峰

DO $$
BEGIN
    RAISE NOTICE 'Migration 027 done: rag_query_log + 4 indexes (ivfflat 1024d) (Operation Ollama-First v5.0 P11)';
END $$;