zsjz-ai/sql/insight_agent_tables_align.sql

-- ============================================================================
-- AI 研判 · 补齐约束与索引（insight_agent_tables.sql 的补丁）
--
-- 背景：agent_insight_session / agent_insight_message 两张表的**列**已按
--       insight_agent_tables.sql 建好（含 NOT NULL 与主键），但建表时未带上
--       约束与索引：
--         · 缺 uk_aim_session_seq UNIQUE (session_id, seq)
--         · 缺 fk_aim_session FOREIGN KEY ... ON DELETE CASCADE
--         · 缺 idx_ais_case_list / idx_ais_ctx_hash / idx_aim_output
--       原因是那些约束写在 CREATE TABLE 语句**内部**，而
--       CREATE TABLE IF NOT EXISTS 对「表已存在」的情况整句跳过，
--       不会补建内部约束 —— 只补建了语句外的 CREATE INDEX IF NOT EXISTS。
--
-- 本脚本幂等：约束先 DROP IF EXISTS 再 ADD，索引用 IF NOT EXISTS。
-- 重复执行结果一致。
--
-- 执行库：PostgreSQL（master）
-- ============================================================================

-- 加约束需要 ACCESS EXCLUSIVE 锁：设个超时，避免在后端有长事务时无限等待
SET lock_timeout = '5s';

-- ============================================================
-- 1) seq 会话内唯一
--    与 UPDATE ... RETURNING last_seq 的行级锁形成双保险：
--    行锁防并发、唯一约束防「万一锁失效」时静默写入重号。
-- ============================================================
ALTER TABLE agent_insight_message DROP CONSTRAINT IF EXISTS uk_aim_session_seq;
ALTER TABLE agent_insight_message
  ADD CONSTRAINT uk_aim_session_seq UNIQUE (session_id, seq);

-- ============================================================
-- 2) 会话删除级联删消息
--    否则删会话会留下孤儿消息，越权/脏数据都从这里来。
-- ============================================================
ALTER TABLE agent_insight_message DROP CONSTRAINT IF EXISTS fk_aim_session;
ALTER TABLE agent_insight_message
  ADD CONSTRAINT fk_aim_session FOREIGN KEY (session_id)
      REFERENCES agent_insight_session (id) ON DELETE CASCADE;

-- ============================================================
-- 3) 历史列表
--    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);

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

-- ============================================================
-- 5) 线索聚合（部分索引）
--    绝大多数消息没有结构化产物，全量索引是浪费。
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_aim_output
  ON agent_insight_message (case_id, create_at DESC)
  WHERE output_json IS NOT NULL;

-- ============================================================
-- 6) 场景默认值对齐（旧库列默认值仍是 CALL_CONTINUOUS）
--    仅改 DEFAULT，不动存量数据：存量行按原场景语义保留，
--    新场景查询一律显式带 biz_type，不依赖默认值。
-- ============================================================
ALTER TABLE agent_insight_session
  ALTER COLUMN biz_type SET DEFAULT 'GRAPH_RELATION';

-- ============================================================
-- 7) 表 / 列注释（与 insight_agent_tables.sql 保持一致，便于运维直读）
-- ============================================================
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';

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（用户中止/异常时保留已生成正文）';