# 案件表与外部表结构
**本文引用的文件**
- [case_table.sql](file://sql/case_table.sql)
- [case_table_1.sql](file://sql/case_table_1.sql)
- [table_data_1.sql](file://sql/table_data_1.sql)
- [external_tables.sql](file://sql/external_tables.sql)
- [external_table_field.sql](file://sql/external_table_field.sql)
- [user.sql](file://sql/user.sql)
## 目录
1. [引言](#引言)
2. [项目结构与数据范围](#项目结构与数据范围)
3. [核心数据模型总览](#核心数据模型总览)
4. [用户、案件与数据源关系](#用户案件与数据源关系)
5. [案件内部表设计](#案件内部表设计)
6. [外部表定义与字段映射](#外部表定义与字段映射)
7. [实体关系图](#实体关系图)
8. [关键字段与索引设计](#关键字段与索引设计)
9. [典型查询模式](#典型查询模式)
10. [数据版本、迁移与删除策略](#数据版本迁移与删除策略)
11. [安全访问控制建议](#安全访问控制建议)
12. [结论](#结论)
## 引言
本文面向调查分析场景中的数据库设计,重点说明三类数据:
- 案件内部表:用于承载通话、交易、住宿、出行等案件相关原始记录。
- 外部表:承载从公安数据源导入的出入境、车辆、违章、违法犯罪人员、在逃人员等扩展实体。
- 外部表元数据:`table_info` 与 `table_field`(通过 `external_table_field.sql` 注入)描述外部表的中文名称、英文表名、分类、字段映射和排序。
仓库中 `sql/` 目录下的 SQL 脚本是本次设计的权威来源;其中 `case_table.sql`、`case_table_1.sql`、`external_tables.sql`、`external_table_field.sql` 构成主体,`table_data_1.sql` 提供一条配置样例,`user.sql` 提供平台用户与案件归属迁移逻辑。
## 项目结构与数据范围
本设计文档仅覆盖以下 SQL 文件对应的表和元数据:
| 文件 | 主要职责 |
|---|---|
| `sql/case_table.sql` | 案件内部业务表及索引,包括交易、通话、账号开户、快递、酒店、航班、火车、同住同乘、时序表等 |
| `sql/case_table_1.sql` | 案件支撑表,包括文件信息、AI 非表格档案、基础人员、人员分组、关系节点边、治理树、配置、错误日志、图谱历史等 |
| `sql/external_tables.sql` | 13 张外部数据表定义,如出入境证件、出入境记录、机动车、驾驶证、车辆违章、同户人、同住址、违法犯罪人员、在逃人员等 |
| `sql/external_table_field.sql` | 外部表在 `table_info`、`table_field` 中的注册与字段映射插入语句 |
| `sql/table_data_1.sql` | 治理配置样例,例如是否启用“开卡信息” |
| `sql/user.sql` | 平台用户表、初始管理员、以及 `case_info.owner_id` 归属迁移 |
```mermaid
graph TB
A["平台用户
sys_user"] --> B["案件主表
case_info"]
B --> C["文件信息
file_info"]
C --> D["案件内部表
trans_record / call_record / ..."]
B --> E["外部表元数据
table_info / table_field"]
E --> F["外部表
cert_info / entry_rec / vehicle_info / lic_info / veh_violation / household / co_resident / criminal / fugitive / ..."]
C --> G["AI 非表格档案
file_ai_profile"]
B --> H["关系图谱
rel_node / rel_edge / edge_conf / rel_node_conf"]
```
**图表来源**
- [user.sql:15-75](file://sql/user.sql#L15-L75)
- [case_table_1.sql:1-120](file://sql/case_table_1.sql#L1-L120)
- [case_table.sql:1-200](file://sql/case_table.sql#L1-L200)
- [external_tables.sql:1-430](file://sql/external_tables.sql#L1-L430)
- [external_table_field.sql:1-365](file://sql/external_table_field.sql#L1-L365)
**章节来源**
- [case_table.sql:1-554](file://sql/case_table.sql#L1-L554)
- [case_table_1.sql:1-260](file://sql/case_table_1.sql#L1-L260)
- [external_tables.sql:1-430](file://sql/external_tables.sql#L1-L430)
- [external_table_field.sql:1-365](file://sql/external_table_field.sql#L1-L365)
- [table_data_1.sql:1-2](file://sql/table_data_1.sql#L1-L2)
- [user.sql:1-75](file://sql/user.sql#L1-L75)
## 核心数据模型总览
当前仓库中的核心实体可划分为四层:
| 层次 | 实体示例 | 作用 |
|---|---|---|
| 平台层 | `sys_user` | 系统登录与管理员标志,不实现完整权限体系 |
| 案件层 | `case_info`(由 `user.sql` 引用)、`file_info` | 案件主对象及其导入文件元信息 |
| 业务表层 | `trans_record`、`call_record`、`account_opening_info`、`hotel_stay_info`、`flight_ticket_info`、`train_ticket_info`、`together_live_info`、`time_series_info` 等 | 案件内结构化调查数据 |
| 外部扩展层 | `cert_info`、`entry_rec`、`vehicle_info`、`lic_info`、`veh_violation`、`household`、`co_resident`、`criminal`、`fugitive`、`fugi_revoke`、`fugi_co`、`fugi_co_revoke` | 公安数据源扩展实体 |
| 元数据层 | `table_info`、`table_field` | 外部表的中文名称、英文表名、分类、字段中文名、英文名、类型、必填、排序等 |
需要特别注意:
- `case_table_1.sql` 中的 `file_info`、`person_basic_info`、`person_group`、`rel_node`、`rel_edge` 等属于案件内部支撑表。
- `external_tables.sql` 中的 13 张表属于外部表定义。
- `external_table_field.sql` 不是物理表定义,而是向 `table_info`、`table_field` 插入元数据的语句;这些元数据用于 UI 展示、字段匹配和语义检索。
**章节来源**
- [case_table_1.sql:1-160](file://sql/case_table_1.sql#L1-L160)
- [external_tables.sql:1-200](file://sql/external_tables.sql#L1-L200)
- [external_table_field.sql:1-80](file://sql/external_table_field.sql#L1-L80)
## 用户、案件与数据源关系
### 用户与案件
`user.sql` 明确说明:平台库使用 PostgreSQL,`sys_user` 只承担认证和最小权限边界,不实现完整角色权限体系。新增字段 `case_info.owner_id` 将案件归属到创建者,并通过索引加速按用户查询其案件。
```mermaid
erDiagram
SYS_USER {
bigint id PK
varchar username
varchar pwd_hash
varchar display_name
smallint is_admin
smallint status
timestamp create_time
timestamp update_time
timestamp last_login_time
}
CASE_INFO {
bigint id PK
bigint owner_id FK
}
SYS_USER ||--o{ CASE_INFO : "拥有"
```
**图表来源**
- [user.sql:15-75](file://sql/user.sql#L15-L75)
### 文件与案件
`file_info` 是文件导入的基础元数据表,包含文件名、工作簿名、批次号、模板号、目标表名、行数、成功失败数、状态、哈希值、创建时间等。它通过 `pid` 关联案件主表(注释中写为 `pid`,通常对应 `case_info.id`),从而把上传文件和解析结果归到具体案件。
```mermaid
erDiagram
FILE_INFO {
bigint id PK
bigint pid
varchar fileName
varchar sheetName
bigint batchId
bigint templateId
varchar tableName
integer lineNo
varchar header
varchar password
varchar charSet
varchar delimiter
varchar fileType
varchar filePath
bigint fileSize
integer dataNum
integer successNum
integer failNum
varchar fileStatus
varchar md5
timestamp createDateTime
}
CASE_INFO {
bigint id PK
}
CASE_INFO ||--o{ FILE_INFO : "包含文件"
```
**图表来源**
- [case_table_1.sql:12-42](file://sql/case_table_1.sql#L12-L42)
### 文件与案件内部表
大多数案件内部表都有 `fileId` 和 `sheetId`,表示数据来源文件和工作簿页签。这是跨表定位原始数据的关键外键语义,即使 SQL 中没有显式声明 `FOREIGN KEY`。
**章节来源**
- [user.sql:40-75](file://sql/user.sql#L40-L75)
- [case_table_1.sql:12-42](file://sql/case_table_1.sql#L12-L42)
- [case_table.sql:1-120](file://sql/case_table.sql#L1-L120)
## 案件内部表设计
### 交易记录表族
- `trans_record`:交易明细。关键字段包括 `personName`、`personCardNo`、`personAccount`、`otherName`、`otherCardNo`、`transAmount`、`transDateTime`、`transSerialNo` 等。
- `trans_record_all`:全量或聚合后的交易表,字段结构与明细表基本一致。
- 索引策略:对高频定位列 `personName`、`personCardNo`、`otherName`、`otherCardNo` 建索引;时间、金额等分析过滤依赖 DuckDB zonemap,不额外建索引。
### 通话记录表族
- `call_record`:通话记录。关键字段包括 `personPhone`、`personCertNo`、`otherPhone`、`callDateTime`、`duration`、`callDirection`、`callType` 等。
- `call_record_all`:通话全量表。
- `use_open_info_call_record`:基于开卡信息的通话记录。
- `call_opening_info`:电话开户信息,包含运营商、号码状态、激活时间、首次通话时间等。
### 其他案件内部表
| 表名 | 业务含义 | 关键外键语义 |
|---|---|---|
| `account_opening_info` | 银行账号开户信息 | `fileId`、`sheetId`、`personName`、`personCardNo` |
| `express_info` | 快递收发件信息 | `fileId`、`sheetId`、收件寄件人姓名、电话、运单号 |
| `resident_population_info` | 常住人口信息 | `fileId`、`sheetId`、身份证号、姓名、地址 |
| `hotel_stay_info` | 酒店住宿信息 | `fileId`、`sheetId`、姓名、身份证号、入住离店时间 |
| `flight_ticket_info` | 航班订票信息 | `fileId`、`sheetId`、姓名、身份证号、航班号、票价 |
| `flight_departure_info` | 航班出发信息 | `fileId`、`sheetId`、航班号、旅客信息 |
| `together_flight_info` | 同航班服务 | `fileId`、`sheetId`、航班号、同行人 |
| `together_train_info` | 同火车服务 | `fileId`、`sheetId`、车次、车厢座位、同行人 |
| `train_ticket_info` | 火车票订票信息 | `fileId`、`sheetId`、车次、出发到达、座位 |
| `together_live_info` | 同住服务 | `fileId`、`sheetId`、被查询人与同住人 |
| `track_car_ticket_info` | 汽车票务联网售票记录 | `fileId`、`sheetId`、车牌、乘客、发车到达 |
| `time_series_info` | 时序汇总表 | `tableNameEn`、`tableNameCn`、人物、时间、扩展字段 |
```mermaid
flowchart TD
Start["导入文件"] --> FileInfo["写入 file_info"]
FileInfo --> Parse["解析并落库到案件内部表"]
Parse --> Trans["交易表 trans_record"]
Parse --> Call["通话表 call_record"]
Parse --> Account["账号开户表 account_opening_info"]
Parse --> Travel["交通出行表 flight/train/car"]
Parse --> Stay["住宿表 hotel_stay_info"]
Parse --> TimeSeries["时序表 time_series_info"]
Trans --> Query["按案件、人物、账号、手机号查询"]
Call --> Query
Account --> Query
Travel --> Query
Stay --> Query
TimeSeries --> Query
```
**图表来源**
- [case_table_1.sql:12-42](file://sql/case_table_1.sql#L12-L42)
- [case_table.sql:1-200](file://sql/case_table.sql#L1-L200)
- [case_table.sql:201-554](file://sql/case_table.sql#L201-L554)
**章节来源**
- [case_table.sql:1-200](file://sql/case_table.sql#L1-L200)
- [case_table.sql:201-554](file://sql/case_table.sql#L201-L554)
## 外部表定义与字段映射
### 外部表清单
`external_tables.sql` 定义了 13 张外部数据表,主要用于调查场景中的扩展实体:
| 序号 | 英文表名 | 中文含义 | 主要调查用途 |
|---|---|---|---|
| 1 | `cert_info` | 出入境证件信息 | 证件持有者身份、签发机关、有效期 |
| 2 | `entry_rec` | 出入境记录信息 | 出入境口岸、时间、交通工具、签证 |
| 3 | `vehicle_info` | 机动车信息 | 车辆登记、住所、能源类型、载客载货 |
| 4 | `lic_info` | 驾驶证信息 | 准驾车型、发证机关、记分、状态 |
| 5 | `veh_violation` | 机动车违章信息 | 违法时间、地点、处理机关、违法行为 |
| 6 | `household` | 同户人 | 户籍家庭成员、关系、婚姻、死亡日期 |
| 7 | `co_resident` | 同住址 | 共同居住人员、民族、职业、婚姻 |
| 8 | `co_veh_viol` | 同车违章 | 同一车牌多次违章的人员 |
| 9 | `criminal` | 违法犯罪人员信息 | 别名、性别、身高、体表标记、案件类别 |
| 10 | `fugitive` | 在逃人员登记信息 | 通缉令编号、立案单位、抓捕级别、奖金 |
| 11 | `fugi_revoke` | 在逃人员撤销信息 | 撤销人、撤销时间、抓获方式、抓获单位 |
| 12 | `fugi_co` | 在逃同案人员 | 同案人员登记、审批、立案日期 |
| 13 | `fugi_co_revoke` | 在逃同案撤销人员 | 撤销流程、抓获地点、案情简要 |
这些外部表普遍包含 `id`、`fileId`、`sheetId`,以及大量以 VARCHAR 存储的公安业务字段。它们通过身份证号、姓名、车牌号、证件号等与案件内部人员、交易、通话、出行数据进行关联。
### 外部表字段映射机制
`external_table_field.sql` 向 `table_info` 和 `table_field` 插入元数据。每条字段记录包含:
- `table_id`:对应 `table_info.id`。
- `table_name_en`:外部表英文名。
- `field_name_cn`:字段中文名。
- `field_name_en`:字段英文名。
- `field_type`:字段类型,当前均为 `TEXT`。
- `required`:是否必填。
- `field_len`:长度限制。
- `matched`:是否已匹配。
- `direction_conf`:方向配置。
- `field_sort`:显示顺序。
- `zf`:校验或标识字段。
`table_info` 中还包含 `classify`,当前值为“公安数据库”,以及 `has_inner`、`has_table` 等开关,用于区分内部表与外部表、是否已有物理表等。
```mermaid
classDiagram
class TableInfo {
+bigint id
+bigint main_id
+varchar table_name_cn
+varchar table_name_en
+varchar func_regex
+integer line_no
+varchar headers
+varchar classify
+integer has_inner
+integer has_table
+timestamp create_time
}
class TableField {
+bigint table_id
+varchar table_name_en
+varchar field_name_cn
+varchar field_name_en
+varchar field_type
+integer required
+integer field_len
+integer matched
+varchar direction_conf
+integer field_sort
+integer zf
}
TableInfo "1" --> "many" TableField : "字段映射"
```
**图表来源**
- [external_table_field.sql:1-80](file://sql/external_table_field.sql#L1-L80)
### 外部表在调查场景中的实体覆盖
| 调查维度 | 对应外部表 | 说明 |
|---|---|---|
| 通话 | `call_record`、`call_record_all`、`use_open_info_call_record`、`call_opening_info` | 通话明细、全量、开卡信息通话、电话开户 |
| 交易 | `trans_record`、`trans_record_all` | 个人转账、账户余额、商户、终端、渠道 |
| 轨迹 | `flight_ticket_info`、`flight_departure_info`、`train_ticket_info`、`track_car_ticket_info`、`together_flight_info`、`together_train_info`、`time_series_info` | 航班、火车、汽车票及同行记录 |
| 人员 | `person_basic_info`、`household`、`co_resident`、`criminal`、`fugitive`、`fugi_revoke`、`fugi_co`、`fugi_co_revoke` | 基础人员、同户、同住、违法犯罪、在逃 |
| 车辆违法 | `vehicle_info`、`lic_info`、`veh_violation`、`co_veh_viol` | 机动车、驾驶证、违章、同车违章 |
| 其他扩展 | `cert_info`、`entry_rec`、`hotel_stay_info`、`express_info`、`resident_population_info` | 出入境证件、出入境记录、酒店、快递、人口 |
**章节来源**
- [external_tables.sql:1-430](file://sql/external_tables.sql#L1-L430)
- [external_table_field.sql:1-365](file://sql/external_table_field.sql#L1-L365)
- [case_table.sql:1-554](file://sql/case_table.sql#L1-L554)
## 实体关系图
下面给出一个整合用户、案件、文件、内部表、外部表和元数据的实体关系图。注意:当前 SQL 脚本中大部分表之间没有显式 `FOREIGN KEY`,因此关系更多体现为业务语义而非数据库约束。
```mermaid
erDiagram
SYS_USER {
bigint id PK
varchar username
smallint is_admin
smallint status
}
CASE_INFO {
bigint id PK
bigint owner_id FK
}
FILE_INFO {
bigint id PK
bigint pid
varchar tableName
varchar fileStatus
}
TABLE_INFO {
bigint id PK
varchar table_name_en
varchar table_name_cn
varchar classify
}
TABLE_FIELD {
bigint table_id FK
varchar table_name_en
varchar field_name_cn
varchar field_name_en
}
TRANS_RECORD {
bigint id PK
bigint fileId
bigint sheetId
varchar personName
varchar personCardNo
decimal transAmount
timestamp transDateTime
}
CALL_RECORD {
bigint id PK
bigint fileId
bigint sheetId
varchar personPhone
varchar otherPhone
timestamp callDateTime
}
CERT_INFO {
bigint id PK
bigint fileId
bigint sheetId
varchar cid
varchar certNo
}
ENTRY_REC {
bigint id PK
bigint fileId
bigint sheetId
varchar certNo
varchar ioDate
}
VEHICLE_INFO {
bigint id PK
bigint fileId
bigint sheetId
varchar plateNo
}
LIC_INFO {
bigint id PK
bigint fileId
bigint sheetId
varchar licNo
}
CRIMINAL {
bigint id PK
bigint fileId
bigint sheetId
varchar cid
varchar name
}
FUGITIVE {
bigint id PK
bigint fileId
bigint sheetId
varchar certNo
varchar caseNo
}
SYS_USER ||--o{ CASE_INFO : "owner_id"
CASE_INFO ||--o{ FILE_INFO : "pid"
TABLE_INFO ||--o{ TABLE_FIELD : "table_id"
FILE_INFO ||--o{ TRANS_RECORD : "fileId"
FILE_INFO ||--o{ CALL_RECORD : "fileId"
FILE_INFO ||--o{ CERT_INFO : "fileId"
FILE_INFO ||--o{ ENTRY_REC : "fileId"
FILE_INFO ||--o{ VEHICLE_INFO : "fileId"
FILE_INFO ||--o{ LIC_INFO : "fileId"
FILE_INFO ||--o{ CRIMINAL : "fileId"
FILE_INFO ||--o{ FUGITIVE : "fileId"
```
**图表来源**
- [user.sql:15-75](file://sql/user.sql#L15-L75)
- [case_table_1.sql:12-42](file://sql/case_table_1.sql#L12-L42)
- [case_table.sql:1-200](file://sql/case_table.sql#L1-L200)
- [external_tables.sql:1-200](file://sql/external_tables.sql#L1-L200)
- [external_table_field.sql:1-80](file://sql/external_table_field.sql#L1-L80)
## 关键字段与索引设计
### 公共字段约定
| 字段 | 类型 | 含义 | 是否常用索引 |
|---|---|---|---|
| `id` | BIGINT | 主键,部分表使用序列生成 | 主键索引 |
| `fileId` | BIGINT | 所属文件 ID | 常用于按文件定位,但脚本未统一建索引 |
| `sheetId` | BIGINT | 工作簿页签 ID | 常用于联合文件定位 |
| `personName` | VARCHAR | 人员姓名 | 多数表有单独索引 |
| `personCertNo` / `cid` | VARCHAR | 身份证号 | 多数表有单独索引 |
| `personPhone` / `otherPhone` | VARCHAR | 手机号 | 通话表有索引 |
| `transDateTime` / `callDateTime` | TIMESTAMP | 时间字段 | 依赖 zonemap,未额外建索引 |
| `transAmount` / `transBalance` | DECIMAL(18,2) | 金额字段 | 依赖 zonemap,未额外建索引 |
### 已明确定义的索引
- 交易表:`idx_trans_record_person_name`、`idx_trans_record_person_card_no`、`idx_trans_record_other_name`、`idx_trans_record_other_card_no`。
- 通话表:`idx_call_record_person_name`、`idx_call_record_person_phone`、`idx_call_record_other_name`、`idx_call_record_other_phone`。
- 账号开户:`idx_account_opening_info_person_name`、`idx_account_opening_info_person_card_no`、`idx_account_opening_info_phone`。
- 电话开户:`idx_call_opening_info_person_phone`、`idx_call_opening_info_person_name`。
- 快递:`idx_express_info_recipient_name`、`idx_express_info_recipient_phone`、`idx_express_info_sender_phone`。
- 常住人口:`idx_resident_population_info_name`。
- 酒店:`idx_hotel_stay_info_person_name`、`idx_hotel_stay_info_person_cert_no`。
- 航班订票:`idx_flight_ticket_info_person_name`、`idx_flight_ticket_info_person_cert_no`。
- 航班出发:`idx_flight_departure_info_person_name`。
- 同航班:`idx_together_flight_info_person_cert_no`、`idx_together_flight_info_companion_name`。
- 同火车:`idx_together_train_info_person_name`、`idx_together_train_info_person_cert_no`、`idx_together_train_info_companion_name`。
- 火车票:`idx_train_ticket_info_person_name`。
- 同住:`idx_together_live_info_query_cert_no`、`idx_together_live_info_together_name`。
- 汽车票务:`idx_track_car_ticket_info_person_name`、`idx_track_car_ticket_info_person_cert_no`。
- 时序表:`idx_time_series_info_person_name`、`idx_time_series_info_other_name`。
- 平台用户:`uk_sys_user_username`。
- 案件归属:`idx_case_info_owner`。
### 索引设计原则
SQL 注释明确指出 DuckDB 索引约定:
1. `CREATE INDEX` 创建的是 ART 索引,适合单列高选择性等值或 IN 查询。
2. 时间、日期、金额等分析过滤主要依赖 DuckDB 自动维护的 zonemap,不额外创建 ART 索引。
3. 为避免拖慢批量导入,仅给“人、卡、账号、手机、证件号”这类高频定位列建索引。
这一原则意味着:未来新增索引时,应优先评估是否属于高频定位列,而不是对所有 VARCHAR 列都建索引。
**章节来源**
- [case_table.sql:1-120](file://sql/case_table.sql#L1-L120)
- [case_table.sql:201-554](file://sql/case_table.sql#L201-L554)
- [user.sql:28-75](file://sql/user.sql#L28-L75)
## 典型查询模式
### 按案件查询
通过 `file_info.pid` 找到案件下的所有文件,再根据 `fileId` 筛选各案件内部表。外部表也通过 `fileId` 与同一批导入文件关联。
推荐思路:
1. 先查 `file_info`,用 `pid = ?` 获取该案件的所有文件。
2. 用 `fileId IN (...)` 筛选 `trans_record`、`call_record`、`hotel_stay_info`、`flight_ticket_info`、`train_ticket_info` 等。
3. 对外部表同样使用 `fileId IN (...)` 筛选。
### 按外部表查询
外部表适合按身份证号、姓名、车牌号、证件号、案件编号进行定位。例如:
- 按身份证号查 `cert_info`、`entry_rec`、`criminal`、`fugitive`。
- 按车牌号查 `vehicle_info`、`veh_violation`、`co_veh_viol`。
- 按案件编号查 `fugitive.caseNo`、`fugi_co.caseNo`、`fugi_co_revoke.caseNo`。
### 字段枚举值查询
当前外部表字段大多定义为 VARCHAR,且 `table_field.field_type` 统一为 `TEXT`。因此:
- 枚举值通常以字符串形式存在,例如“性别”“状态”“案件类别”。
- 若需严格枚举,建议在应用层或后续扩展表中增加字典表;当前 SQL 未提供独立枚举字典。
- 查询时应使用大小写一致的字符串匹配,并注意不同数据源可能使用不同编码。
### 按人物关联查询
常见关联路径:
- 通过 `personName` 或 `personCertNo` 在通话、交易、出行、住宿、外部人员表之间交叉查找。
- 通过 `companionName`、`companionName` 在 `together_flight_info`、`together_train_info` 中查找同行人员。
- 通过 `togetherCertNo` 在 `together_live_info` 中查找同住人员。
```mermaid
sequenceDiagram
participant User as "调查员"
participant File as "file_info"
participant Internal as "案件内部表"
participant External as "外部表"
User->>File: "按案件 ID 查询文件"
File-->>User: "返回 fileId 列表"
User->>Internal: "按 fileId 查询通话、交易、出行、住宿"
Internal-->>User: "返回案件内部数据"
User->>External: "按 fileId 查询外部扩展实体"
External-->>User: "返回出入境、车辆、在逃等数据"
```
**图表来源**
- [case_table_1.sql:12-42](file://sql/case_table_1.sql#L12-L42)
- [case_table.sql:1-200](file://sql/case_table.sql#L1-L200)
- [external_tables.sql:1-430](file://sql/external_tables.sql#L1-L430)
**章节来源**
- [case_table.sql:1-554](file://sql/case_table.sql#L1-L554)
- [external_tables.sql:1-430](file://sql/external_tables.sql#L1-L430)
- [external_table_field.sql:1-365](file://sql/external_table_field.sql#L1-L365)
## 数据版本、迁移与删除策略
### 数据版本
当前仓库未使用 Flyway 或 Liquibase。`user.sql` 注释明确说明:“执行方式:手工执行(项目无 Flyway / Liquibase)”。因此:
- 数据迁移应以 SQL 脚本为单位管理。
- 每个脚本应具备幂等性,避免重复执行报错。
- 对于新表或新列,应使用 `CREATE TABLE IF NOT EXISTS`、`ADD COLUMN IF NOT EXISTS`、`CREATE INDEX IF NOT EXISTS` 等幂等操作。
### 迁移规范
- 平台库迁移:`user.sql` 负责 `sys_user`、初始管理员、`case_info.owner_id` 字段和索引。
- 案件库迁移:`case_table.sql`、`case_table_1.sql`、`external_tables.sql`、`external_table_field.sql` 负责案件数据结构和外部表元数据。
- AI 非表格文件:`file_ai_profile` 在建表注释中说明会通过代码中的 `CaseDataSourceRegistry#ensureAiProfileTable` 做幂等补建,并通过 `AI_PROFILE_MIGRATIONS` 补列;修改列时必须同步代码常量与测试。
### 删除策略
当前 SQL 脚本普遍使用 `DROP TABLE IF EXISTS` 重建表,这更适合初始化或测试环境,不适合生产直接运行。生产删除策略建议:
- 不要直接 `DROP TABLE`。
- 对敏感数据采用逻辑删除:增加 `deleted`、`delete_time` 或类似字段。
- 对审计要求高的表保留归档副本,而不是原地删除。
- 对临时中间表可使用命名空间前缀或分区,便于清理。
- 删除前必须评估索引、视图、外部表元数据、前端菜单和 API 是否仍引用该表。
### 配置项
`table_data_1.sql` 提供了一条 `govern_conf` 配置样例,用于控制是否使用“开卡信息”等能力。治理类开关应放在 `govern_conf` 或专用配置表中,不应散落在业务表。
**章节来源**
- [user.sql:1-20](file://sql/user.sql#L1-L20)
- [user.sql:40-75](file://sql/user.sql#L40-L75)
- [case_table_1.sql:12-28](file://sql/case_table_1.sql#L12-L28)
- [table_data_1.sql:1-2](file://sql/table_data_1.sql#L1-L2)
## 安全访问控制建议
### 当前权限模型
`user.sql` 明确说明:
- 只有 `is_admin` 一个标志,作用域限定在“能否调用 `/user/**` 和看到用户管理入口”。
- 不建 `sys_role`、`sys_menu`、`sys_permission`、`sys_user_role`。
- 不实现 StpInterface,`StpUtil.hasRole`、`checkPermission` 一律不用。
- 前端路由由本地静态菜单生成,`permCodeList` 保持空。
这意味着:**数据库层面没有细粒度行级权限或表级权限模型**。安全边界主要由应用层和用户归属字段承担。
### 建议的安全措施
| 层级 | 建议 |
|---|---|
| 平台层 | 使用 `sys_user.status` 控制启用停用;`pwd_hash` 使用 BCrypt;禁止明文密码入库 |
| 案件层 | 强制按 `owner_id` 过滤案件查询;多租户或共享案件需在应用层实现授权校验 |
| 文件层 | 对 `filePath`、`md5`、`password`、`charSet` 等敏感字段进行脱敏或加密 |
| 业务层 | 对身份证号、手机号、车牌号、住址、照片 URL 等敏感字段进行脱敏输出 |
| 外部表层 | 外部表字段多为 VARCHAR,应避免把大体积照片或附件直接存入库;建议只存 URL 或文件 ID |
| 审计层 | 保留 `clean_error_log`、`graph_his` 等辅助表用于问题排查,但不应泄露敏感原文 |
**章节来源**
- [user.sql:15-40](file://sql/user.sql#L15-L40)
- [case_table_1.sql:12-28](file://sql/case_table_1.sql#L12-L28)
- [external_tables.sql:1-430](file://sql/external_tables.sql#L1-L430)
## 结论
本案数据模型围绕“用户 → 案件 → 文件 → 内部表 / 外部表”展开:
- 用户通过 `owner_id` 拥有案件。
- 案件通过 `file_info` 管理导入文件。
- 文件解析后落入案件内部表,如交易、通话、出行、住宿、时序等。
- 外部表承载公安数据源的扩展实体,包括出入境、车辆、驾驶证、违章、同户人、同住址、违法犯罪人员、在逃人员等。
- 外部表元数据通过 `table_info` 和 `table_field` 注册,驱动字段映射、分类和排序。
当前设计偏向 DuckDB 分析型查询,强调高频定位列索引和 zonemap 优化,同时缺少显式外键约束和完整权限体系。生产使用中应补充:
- 明确的迁移脚本版本管理。
- 逻辑删除和归档策略。
- 敏感字段脱敏。
- 外部表枚举值的字典化。
- 更严格的按用户、按案件的数据隔离。