当 AI 需要长期记忆：为什么 PostgreSQL 正在成为企业记忆基础设施

-- 1. 启用 pgvector 扩展
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. 短期记忆：当前交互上下文
CREATE TABLE active_sessions (
    session_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID NOT NULL,
    agent_id      UUID NOT NULL,
    context       JSONB NOT NULL,          -- 当前对话状态、意图、参数
    created_at    TIMESTAMPTZ DEFAULT now(),
    updated_at    TIMESTAMPTZ DEFAULT now()
);

-- 3. 情景记忆：历史事件与对话
CREATE TABLE episodic_events (
    event_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id    UUID REFERENCES active_sessions(session_id),
    user_id       UUID NOT NULL,
    agent_id      UUID NOT NULL,
    event_type    TEXT NOT NULL,            -- 'conversation', 'decision', 'tool_call', 'escalation'
    content       JSONB NOT NULL,           -- 事件详情
    sentiment     TEXT,                     -- 'positive', 'neutral', 'negative', 'frustrated'
    importance    SMALLINT DEFAULT 3,       -- 1-5, 越高越重要
    occurred_at   TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_episodic_user_time ON episodic_events (user_id, occurred_at DESC);
CREATE INDEX idx_episodic_type_importance ON episodic_events (event_type, importance DESC);

-- 4. 语义记忆：知识条目 + embedding
CREATE TABLE semantic_knowledge (
    knowledge_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain        TEXT NOT NULL,            -- 'product', 'policy', 'finance', 'support'
    title         TEXT NOT NULL,
    body          TEXT NOT NULL,
    embedding     vector(1536),             -- OpenAI ada-002 维度，按实际模型调整
    metadata      JSONB NOT NULL DEFAULT '{}',  -- 来源、可信度、版本等
    effective_from TIMESTAMPTZ DEFAULT now(),
    effective_to   TIMESTAMPTZ,             -- NULL 表示当前有效
    superseded_by  UUID REFERENCES semantic_knowledge(knowledge_id),
    created_at    TIMESTAMPTZ DEFAULT now()
);
-- 向量索引（IVFFlat，生产环境数据量大时换 HNSW）
CREATE INDEX idx_semantic_embedding ON semantic_knowledge
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
-- 全文搜索索引
CREATE INDEX idx_semantic_fts ON semantic_knowledge
    USING gin (to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(body, '')));

-- 5. 程序记忆：工作流状态与业务策略
CREATE TABLE workflow_state (
    workflow_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_type TEXT NOT NULL,            -- 'loan_approval', 'claim_processing', 'order_fulfillment'
    status        TEXT NOT NULL DEFAULT 'active',  -- 'active', 'suspended', 'completed', 'failed'
    current_step  TEXT NOT NULL,
    context       JSONB NOT NULL,           -- 工作流变量、审批链、条件分支状态
    user_id       UUID NOT NULL,
    agent_id      UUID NOT NULL,
    created_at    TIMESTAMPTZ DEFAULT now(),
    updated_at    TIMESTAMPTZ DEFAULT now()
);

-- 6. 长期记忆：审计与决策追溯
CREATE TABLE audit_trail (
    audit_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    decision_type TEXT NOT NULL,
    context_snapshot JSONB NOT NULL,         -- 决策时的完整上下文快照
    tools_invoked JSONB NOT NULL DEFAULT '[]',
    outcome       TEXT NOT NULL,
    reasoning_trace JSONB NOT NULL DEFAULT '{}',  -- 推理步骤记录
    user_id       UUID NOT NULL,
    agent_id      UUID NOT NULL,
    created_at    TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_audit_decision_time ON audit_trail (decision_type, created_at DESC);

-- 7. 行级安全：确保用户只能访问自己的记忆
ALTER TABLE episodic_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE semantic_knowledge ENABLE ROW LEVEL SECURITY;
ALTER TABLE workflow_state ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_trail ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_episodic_access ON episodic_events
    FOR ALL USING (user_id = current_setting('app.current_user_id')::UUID);

CREATE POLICY user_workflow_access ON workflow_state
    FOR ALL USING (user_id = current_setting('app.current_user_id')::UUID);

CREATE POLICY user_audit_access ON audit_trail
    FOR ALL USING (user_id = current_setting('app.current_user_id')::UUID);

-- 场景：查找最近 30 天与"交易失败"相关的高价值客户未解决升级事件
-- 同时检索语义知识中的相关政策，排除已被新版本取代的旧条目

-- 设置当前用户（应用层在连接时设置）
SET app.current_user_id = 'a1b2c3d4-5678-90ab-cdef-123456789012';

WITH relevant_events AS (
    -- 情景记忆：结构化过滤 + 时间窗口 + 重要性排序
    SELECT
        e.event_id,
        e.content,
        e.sentiment,
        e.importance,
        e.occurred_at,
        e.event_type
    FROM episodic_events e
    WHERE e.user_id = current_setting('app.current_user_id')::UUID
      AND e.event_type = 'escalation'
      AND e.occurred_at > now() - INTERVAL '30 days'
      AND e.content->>'resolution_status' = 'unresolved'
      AND e.content->>'account_tier' IN ('premium', 'enterprise')
    ORDER BY e.importance DESC, e.occurred_at DESC
    LIMIT 20
),
relevant_knowledge AS (
    -- 语义记忆：向量相似度 + 时效性过滤 + 排除已取代条目
    SELECT
        k.knowledge_id,
        k.title,
        k.body,
        k.domain,
        k.effective_from,
        1 - (k.embedding <=> '[0.02, -0.01, ...]'::vector) AS similarity  -- 替换为实际查询 embedding
    FROM semantic_knowledge k
    WHERE k.domain = 'policy'
      AND k.superseded_by IS NULL            -- 排除已被取代的旧版本
      AND k.effective_to IS NULL             -- 当前仍然有效
      AND k.effective_from <= now()          -- 已生效
    ORDER BY k.embedding <=> '[0.02, -0.01, ...]'::vector
    LIMIT 10
),
active_workflows AS (
    -- 程序记忆：当前用户相关的工作流状态
    SELECT
        w.workflow_id,
        w.workflow_type,
        w.current_step,
        w.context
    FROM workflow_state w
    WHERE w.user_id = current_setting('app.current_user_id')::UUID
      AND w.status = 'active'
    LIMIT 5
)
-- 组装上下文：为 LLM 提供结构化的多维度记忆
SELECT json_build_object(
    'events',        (SELECT json_agg(relevant_events)  FROM relevant_events),
    'knowledge',     (SELECT json_agg(relevant_knowledge) FROM relevant_knowledge),
    'active_flows',  (SELECT json_agg(active_workflows) FROM active_workflows)
) AS assembled_context;