zsjz-ai/sql/insight_agent_tables.sql

-- ============================================================================
-- AI 研判 · 会话与消息（独立存储，不复用 agent_chat_session / agent_message）
--
-- 设计要点（见 continuous-insight-plan.md §4.1）：
--   1) 表级隔离：独立表名 agent_insight_*，与通用对话表物理分离；
--   2) 案件级隔离：会话行必带 case_id，所有列表查询强制 WHERE case_id = ?；
--      消息表**冗余 case_id**，保证「越权查询在 SQL 层面就写不出来」；
--   3) 会话级排序：seq 会话内单调递增 + 唯一，分页排序只认它
--      （id 是随机 UUID 派生、create_at 会同毫秒撞车，都不能用来排序）。
--
-- 执行库：PostgreSQL（master）
--
-- ⚠️ 本脚本只适合「空库首次建表」。
--    表已存在时 CREATE TABLE IF NOT EXISTS 会**整句跳过**，连内部的
--    uk_aim_session_seq / fk_aim_session 一起跳过（只有语句外的 CREATE INDEX
--    会被补建）。已有库请改用 insight_agent_tables_align.sql 补齐约束与索引。
-- ============================================================================

-- ============================================================
-- AI 研判 · 会话表
-- ============================================================
CREATE TABLE IF NOT EXISTS agent_insight_session (
  id                BIGINT       PRIMARY KEY,               -- 应用侧生成（generateId()，随机非单调，仅作主键）
  case_id           BIGINT       NOT NULL,                  -- ★ 案件级硬隔离边界
  biz_type          VARCHAR(32)  NOT NULL DEFAULT 'GRAPH_RELATION',  -- 研判场景维度（本期恒 GRAPH_RELATION）
  scope             VARCHAR(16)  NOT NULL DEFAULT 'BATCH',  -- BATCH | PAIR(预留)
  agent_row_id      BIGINT,                                 -- 使用的 agent 配置行
  model_id          BIGINT       NOT NULL,                  -- 创建时定的默认模型，会话内不变
  title             VARCHAR(128),                           -- 「对象关系研判 · 2024-12-01 14:32」
  context           JSONB        NOT NULL,                  -- ★ 查询条件 + 结果快照（动态 sysPrompt 的数据源）
  context_hash      VARCHAR(64)  NOT NULL,                  -- context 的 sha256，用于同条件复用/去重提示
  sys_prompt        TEXT         NOT NULL,                  -- ★ 实际使用的完整 sysPrompt，会话内不可变（不下发）
  message_count     INT          NOT NULL DEFAULT 0,        -- 消息条数（列表页直读，不 count(*)）
  last_seq          BIGINT       NOT NULL DEFAULT 0,        -- ★ 会话内消息序号水位（原子分配用）
  total_tokens      BIGINT       NOT NULL DEFAULT 0,
  last_message_at   TIMESTAMP,                              -- 最后活动时间（列表排序用）
  pinned            BOOLEAN      NOT NULL DEFAULT FALSE,
  status            VARCHAR(16)  NOT NULL DEFAULT 'ACTIVE', -- ACTIVE | STREAMING | ARCHIVED
  create_at         TIMESTAMP    NOT NULL DEFAULT now(),
  update_at         TIMESTAMP    NOT NULL DEFAULT now()
);

-- 历史列表：WHERE case_id=? AND biz_type=? ORDER BY pinned DESC, last_message_at DESC
CREATE INDEX IF NOT EXISTS idx_ais_case_list
  ON agent_insight_session (case_id, biz_type, last_message_at DESC);

-- 同条件复用 / 去重提示：WHERE case_id=? AND context_hash=?
CREATE INDEX IF NOT EXISTS idx_ais_ctx_hash
  ON agent_insight_session (case_id, context_hash);

COMMENT ON TABLE  agent_insight_session IS 'AI研判会话（独立存储，不复用通用对话表）';
COMMENT ON COLUMN agent_insight_session.case_id IS '案件ID：案件级硬隔离边界，所有查询强制携带';
COMMENT ON COLUMN agent_insight_session.biz_type IS '研判场景：本期恒 GRAPH_RELATION（对象关系分析），为将来同表扩场景预留';
COMMENT ON COLUMN agent_insight_session.scope IS '分析范围：BATCH=当前图谱整体，PAIR=单个节点/关系（预留）';
COMMENT ON COLUMN agent_insight_session.context IS '查询条件+图谱快照（节点/关系），动态系统提示词的数据源';
COMMENT ON COLUMN agent_insight_session.context_hash IS 'context 的 sha256，用于同条件复用与去重提示';
COMMENT ON COLUMN agent_insight_session.sys_prompt IS '会话内实际使用的完整系统提示词，仅运维/追溯读，不通过接口下发';
COMMENT ON COLUMN agent_insight_session.last_seq IS '会话内消息序号水位，消息 seq 由它原子分配';
COMMENT ON COLUMN agent_insight_session.status IS 'ACTIVE | STREAMING（流式进行中，用于并发抢占）| ARCHIVED';

-- ============================================================
-- AI 研判 · 消息表
-- ============================================================
CREATE TABLE IF NOT EXISTS agent_insight_message (
  id                  BIGINT      PRIMARY KEY,
  session_id          BIGINT      NOT NULL,
  case_id             BIGINT      NOT NULL,                 -- ★ 冗余，保证消息查询永远可带 case_id
  seq                 BIGINT      NOT NULL,                 -- ★ 会话内递增，分页排序列
  role                VARCHAR(16) NOT NULL,                 -- user | assistant | system
  content             TEXT,                                 -- 正文
  content_type        VARCHAR(16) NOT NULL DEFAULT 'text',  -- text | insight
  message_type        VARCHAR(16) NOT NULL DEFAULT 'chat',  -- chat | first_round(首轮自动分析) | followup
  metadata            JSONB,                                -- 思考过程/中断标记/耗时等轻量字段
  tool_events         JSONB,                                -- ★ 工具调用明细（大，列表查询不 select）
  output_json         JSONB,                                -- ★ ```insight 结构化产物（首轮 assistant 才有）
  token_count         INT         NOT NULL DEFAULT 0,
  reply_to_message_id BIGINT,                               -- 追问指向的首轮消息（UI 分组用）
  starred             BOOLEAN     NOT NULL DEFAULT FALSE,
  status              VARCHAR(16) NOT NULL DEFAULT 'DONE',  -- DONE | INTERRUPTED
  create_at           TIMESTAMP   NOT NULL DEFAULT now(),

  CONSTRAINT uk_aim_session_seq UNIQUE (session_id, seq),
  CONSTRAINT fk_aim_session FOREIGN KEY (session_id)
      REFERENCES agent_insight_session (id) ON DELETE CASCADE
);

-- 线索聚合（为「线索推送」页预留）：按案件捞有结构化产物的消息
CREATE INDEX IF NOT EXISTS idx_aim_output
  ON agent_insight_message (case_id, create_at DESC)
  WHERE output_json IS NOT NULL;

COMMENT ON TABLE  agent_insight_message IS 'AI研判消息（独立存储；case_id 冗余自会话表，用于强制归属过滤）';
COMMENT ON COLUMN agent_insight_message.seq IS '会话内单调序号，分页排序列（勿用 create_at/id 排序）';
COMMENT ON COLUMN agent_insight_message.case_id IS '冗余自会话表：保证消息查询永远可带 case_id 过滤';
COMMENT ON COLUMN agent_insight_message.message_type IS 'chat | first_round(首轮自动分析) | followup';
COMMENT ON COLUMN agent_insight_message.output_json IS '```insight 围栏解析后的结构化产物，仅首轮 assistant 消息有值';
COMMENT ON COLUMN agent_insight_message.status IS 'DONE | INTERRUPTED（用户中止/异常时保留已生成正文）';

-- ============================================================
-- seq 原子分配（说明，非 DDL）
-- 与 INSERT 消息同事务执行，依赖 PG 行级排他锁保证不重号：
--   UPDATE agent_insight_session
--      SET last_seq = last_seq + 1, update_at = now()
--    WHERE id = #{sessionId}
--   RETURNING last_seq;
-- 禁止使用 SELECT max(seq) + 1：READ COMMITTED 下并发会重号。
-- ============================================================