ai_clean_sqlite.sql 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222
  1. -- ============================================================================
  2. -- AI 清洗链路(module/aiclean)元数据表 —— SQLite 方言
  3. --
  4. -- 落在 master:加密 SQLite 平台库 qingjian/system/db/plat
  5. -- (cipher=aes256cbc,驱动 io.github.willena:sqlite-jdbc / org.sqlite.JDBC)。
  6. -- 与 table_info / table_field 同库,但一张都不碰模板库:AI 生成的模板、字段、
  7. -- 清洗函数链、任务轨迹全部存这六张表。之所以不写 table_info:一旦写行并打开
  8. -- table_field.matched,既有匹配器就会命中该模板,进而走到
  9. -- StrategyType.fromCode(main_id) 抛异常(StrategyType.java:82)。
  10. --
  11. -- 列集合与 sql/ai_clean.sql(PG 时代参考)完全一致,仅改写方言:
  12. -- bigint / integer / smallint -> INTEGER
  13. -- varchar(n) / text -> TEXT
  14. -- numeric(6,4) -> REAL
  15. -- timestamp default current_timestamp -> TEXT default (strftime(...localtime))
  16. -- 主键一律 INTEGER not null primary key(rowid 别名):id 由应用侧雪花算法供给,
  17. -- 不需要 AUTOINCREMENT。不含 case_id_src —— 那是 PG 侧未执行补丁
  18. -- ai_clean_template_drop_case_src.sql 的残留,实体无此字段,模板跨案件共享。
  19. --
  20. -- 全部语句幂等(if not exists / on conflict do nothing),可重复执行。
  21. -- plat 中已存在的表一律不动,本脚本只补建缺失的六张表。
  22. --
  23. -- 执行方式(本机无 sqlite3 CLI,Python 打不开加密库,用驱动 jar + jshell):
  24. -- 见 QUICK_START「平台库初始化」。执行前先停服务并备份 plat 文件。
  25. --
  26. -- 设计文档:docs/design/ai-clean-parallel.md
  27. -- ============================================================================
  28. -- ----------------------------------------------------------------------------
  29. -- 1. AI 模板(等价于 table_info 的角色,但完全独立)
  30. -- header_md5 唯一键是「零模型重放」的收口:同一张表头签名第二次上传直接命中,
  31. -- 不再调用大模型。
  32. -- ----------------------------------------------------------------------------
  33. create table if not exists ai_clean_template
  34. (
  35. id INTEGER not null primary key,
  36. table_name_cn TEXT not null,
  37. table_name_en TEXT not null,
  38. headers TEXT,
  39. header_md5 TEXT not null,
  40. header_line_no INTEGER,
  41. func_regex TEXT,
  42. block_no INTEGER,
  43. struct_ops TEXT,
  44. needs_struct INTEGER default 0,
  45. category TEXT,
  46. category_l2 TEXT,
  47. main_ref_template_id INTEGER,
  48. match_tier TEXT,
  49. status TEXT default 'DRAFT',
  50. ver INTEGER default 1,
  51. confidence REAL,
  52. create_by INTEGER,
  53. create_time TEXT default (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')),
  54. update_time TEXT
  55. );
  56. create unique index if not exists uk_ai_clean_tpl_md5 on ai_clean_template (header_md5);
  57. create index if not exists idx_ai_clean_tpl_status on ai_clean_template (status);
  58. create index if not exists idx_ai_clean_tpl_category on ai_clean_template (category);
  59. -- ----------------------------------------------------------------------------
  60. -- 2. AI 模板字段(等价于 table_field 的角色)
  61. -- 既是动态表 DDL 的真源(field_type/field_len -> DuckDB 类型),
  62. -- 也是前端列表头的真源(field_name_cn + field_sort)。
  63. -- ----------------------------------------------------------------------------
  64. create table if not exists ai_clean_field
  65. (
  66. id INTEGER not null primary key,
  67. ai_template_id INTEGER not null,
  68. column_name TEXT not null,
  69. field_name_cn TEXT,
  70. field_name_en TEXT,
  71. field_type TEXT,
  72. required INTEGER default 0,
  73. field_len INTEGER default 0,
  74. field_sort INTEGER default 0,
  75. direction_conf TEXT,
  76. col_index INTEGER,
  77. matched INTEGER default 1,
  78. create_time TEXT default (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime'))
  79. );
  80. create index if not exists idx_ai_clean_field_tpl on ai_clean_field (ai_template_id);
  81. -- ----------------------------------------------------------------------------
  82. -- 3. AI 清洗规则(函数链快照)
  83. -- 只存扁平枚举 hint,绝不存带 @class 的多态 JSON:TableRuleDTO/Fun 用的是
  84. -- Jackson @JsonTypeInfo(use=CLASS),让模型输出它等于开放任意类名反序列化攻击面。
  85. -- 加载时由 AiRuleCompiler 在内存里把 hint 还原成 Fun 对象。
  86. -- ----------------------------------------------------------------------------
  87. create table if not exists ai_clean_rule
  88. (
  89. id INTEGER not null primary key,
  90. ai_template_id INTEGER,
  91. col_index INTEGER not null,
  92. field_name_en TEXT,
  93. field_name_cn TEXT,
  94. hints TEXT,
  95. ref_template_id INTEGER,
  96. ver INTEGER default 1,
  97. create_time TEXT default (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime'))
  98. );
  99. create index if not exists idx_ai_clean_rule_tpl on ai_clean_rule (ai_template_id);
  100. -- ----------------------------------------------------------------------------
  101. -- 4. AI 清洗任务(幂等游标 + 状态线 + 成本埋点)
  102. -- 唯一键 (case_id,file_id,sheet_id,block_no,ver) 就是「同一块只被处理一次」的保证。
  103. -- ----------------------------------------------------------------------------
  104. create table if not exists ai_clean_job
  105. (
  106. id INTEGER not null primary key,
  107. case_id INTEGER not null,
  108. file_id INTEGER not null,
  109. sheet_id INTEGER not null,
  110. block_no INTEGER default 0,
  111. ver INTEGER default 1,
  112. batch_id INTEGER,
  113. file_name TEXT,
  114. sheet_name TEXT,
  115. status TEXT not null,
  116. exit_code TEXT,
  117. matched_by TEXT,
  118. match_tier TEXT,
  119. ref_template_id INTEGER,
  120. ai_template_id INTEGER,
  121. header_md5 TEXT,
  122. header_row INTEGER,
  123. confidence REAL,
  124. detail TEXT,
  125. draft TEXT,
  126. model_name TEXT,
  127. token_in INTEGER,
  128. token_out INTEGER,
  129. llm_calls INTEGER default 0,
  130. cost_ms INTEGER,
  131. row_total INTEGER,
  132. row_loaded INTEGER,
  133. row_rejected INTEGER,
  134. row_warned INTEGER,
  135. row_unprocessed INTEGER,
  136. fail_reason TEXT,
  137. create_time TEXT default (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')),
  138. update_time TEXT
  139. );
  140. create unique index if not exists uk_ai_clean_job on ai_clean_job (case_id, file_id, sheet_id, block_no, ver);
  141. create index if not exists idx_ai_clean_job_status on ai_clean_job (status);
  142. create index if not exists idx_ai_clean_job_file on ai_clean_job (file_id);
  143. -- ----------------------------------------------------------------------------
  144. -- 5. 智能清洗批次(融合入口的状态机与幂等锚点)
  145. -- 唯一键 (case_id, batch_id) 让重复点按钮返回同一批次而不是重跑一遍。
  146. -- ----------------------------------------------------------------------------
  147. create table if not exists ai_clean_batch
  148. (
  149. id INTEGER not null primary key,
  150. case_id INTEGER not null,
  151. batch_id INTEGER not null,
  152. stage TEXT not null,
  153. file_count INTEGER default 0,
  154. sheet_total INTEGER default 0,
  155. matched_count INTEGER default 0,
  156. ai_judged_count INTEGER default 0,
  157. existing_hit_count INTEGER default 0,
  158. ai_load_count INTEGER default 0,
  159. loaded_rows INTEGER default 0,
  160. rejected_rows INTEGER default 0,
  161. unprocessed_rows INTEGER default 0,
  162. self_built_count INTEGER default 0,
  163. need_review_count INTEGER default 0,
  164. failed_count INTEGER default 0,
  165. need_review_detail TEXT,
  166. last_error TEXT,
  167. create_time TEXT default (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')),
  168. update_time TEXT
  169. );
  170. create unique index if not exists uk_ai_clean_batch on ai_clean_batch (case_id, batch_id);
  171. create index if not exists idx_ai_clean_batch_stage on ai_clean_batch (stage);
  172. -- ----------------------------------------------------------------------------
  173. -- 6. 表头同义词库(评分归一 + 绑定域)
  174. -- 星型词组:alias_word -> std_word。只进 HeaderScorer 评分与 filterBinds 绑定域,
  175. -- 绝不进 HeaderFingerprint 指纹 —— 同义词一旦折叠指纹,存量模板 header_md5
  176. -- 会随词条配置漂移。
  177. -- ----------------------------------------------------------------------------
  178. create table if not exists ai_clean_synonym
  179. (
  180. id INTEGER not null primary key,
  181. std_word TEXT not null,
  182. alias_word TEXT not null,
  183. remark TEXT,
  184. status INTEGER default 1,
  185. create_by INTEGER,
  186. create_time TEXT default (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')),
  187. update_time TEXT
  188. );
  189. create unique index if not exists uk_ai_clean_synonym on ai_clean_synonym (std_word, alias_word);
  190. -- ----------------------------------------------------------------------------
  191. -- 内置词条(建表脚本自带的引导数据,不是从 PG 搬的业务数据)
  192. --
  193. -- 必须写入:GlobalCache.synonymMap 初值为空 Map,loadSynonymMap() 遇空表返回 null
  194. -- 从而保留空快照,且 HeaderScorer 已无硬编码 SYNONYMS 兜底 —— 空表会静默丢失
  195. -- EXACT 同义词归一(户名↔姓名、交易金额↔发生额、账号↔卡号…),只剩代码内模糊规则。
  196. -- ----------------------------------------------------------------------------
  197. insert into ai_clean_synonym (id, std_word, alias_word, remark) values
  198. (1, '户名', '账户名称', '内置:户名系'),
  199. (2, '户名', '客户名称', '内置:户名系'),
  200. (3, '户名', '姓名', '内置:户名系'),
  201. (4, '交易金额', '发生额', '内置:金额系'),
  202. (5, '交易金额', '交易额', '内置:金额系'),
  203. (6, '交易金额', '金额', '内置:金额系'),
  204. (7, '日期', '交易日期', '内置:日期系'),
  205. (8, '时间', '交易时间', '内置:时间系'),
  206. (9, '账号', '卡号', '内置:账号系'),
  207. (10, '账号', '银行账号', '内置:账号系'),
  208. (11, '账号', '账户', '内置:账号系'),
  209. (12, '电话', '手机号', '内置:电话系'),
  210. (13, '电话', '手机', '内置:电话系'),
  211. (14, '电话', '联系电话', '内置:电话系'),
  212. (15, '身份证号', '身份证', '内置:证件系'),
  213. (16, '身份证号', '身份证号码', '内置:证件系'),
  214. (17, '身份证号', '证件号码', '内置:证件系')
  215. on conflict (std_word, alias_word) do nothing;