# GovCrawler 数据库设计文档

版本：v2.0（MySQL 5.7 部署版）
最后更新：2026-04-24
部署环境：`mysql://GovCrawler@192.168.1.222:3306/GovCrawler`，`utf8mb4` / `utf8mb4_unicode_ci`
迁移版本：`0004_add_local_dept_region`（通过 `alembic upgrade head` 自动对齐）

---

## 1. 设计目标与总体结构

政务网站信息采集系统的持久层把运行时状态（站点/栏目/抓取记录/文章）集中进 DB，把静态声明（YAML 配置）与运行时状态解耦；同时兼容**旧 YAML 铁律**和**管理后台直接 CRUD** 两种配置源。

共 **8 张业务表 + 1 张迁移版本表**：

```
crawl_site ───┬────────> site_department ──> local_department
              │                                     ↑
              ├────────> crawl_target ──────────────┤
              │              │                      │
              │              └──────> parser_override_json
              │
              ├────────> article ───> attachment
              │              ↑
              │              └──> local_department (dept_id)
              │
              ├────────> crawl_log
              │
              └────────> master_column_registry ──> crawl_target (subscribed_target_id)
```

执行层对三张表的**三条铁律**：
1. **去重唯一键铁律**：`article.url_hash` 始终唯一 (UNIQUE)。
2. **绑定约束铁律**：`site_department.dept_binding = 'mapped'` ⇔ `local_dept_id` 非空。
3. **YAML/UI 混合铁律**：`crawl_site.managed_by='yaml'` 的行遵循 YAML 是真相；`'ui'` 行由管理后台托管，sync_dir 永不改动。

---

## 2. 表结构规范

### 2.1 `crawl_site` — 站点登记

一个 row = 一个 CMS 网站。

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | BIGINT PK AUTO_INCREMENT | | 内部 PK |
| `site_code` | VARCHAR(50) UNIQUE NOT NULL | | 业务外键，例 `fogang` / `qingcheng` |
| `site_name` | VARCHAR(200) | | 中文名，例 "佛冈县人民政府" |
| `base_url` | VARCHAR(500) | | 根 URL |
| `site_role` | VARCHAR(50) | | `qingyuan_local` / `county_local` / `province_ref` / `nation_ref` |
| `cms_adapter` | VARCHAR(50) | XOR yaml_path | 适配器 ID，例 `gkmlpt` / `trs_wcm` |
| `adapter_params_json` | JSON | | 适配器个性化参数（可选） |
| `default_fetch_strategy` | VARCHAR(50) | | `httpx` / `playwright` |
| `strategy_override_reason` | VARCHAR(200) | | 覆写原因备注 |
| `respect_robots` | BOOLEAN NOT NULL | default TRUE | 是否尊重 robots.txt |
| `yaml_path` | VARCHAR(500) | XOR cms_adapter | 旧式 YAML 配置路径 |
| `managed_by` | VARCHAR(10) NOT NULL | default 'yaml' | `yaml` / `ui` — sync_dir 忽略规则 |
| `enabled` | BOOLEAN NOT NULL | default TRUE | 启停开关 |
| `remark` | VARCHAR(500) | | 自由备注 |
| `created_at` | DATETIME | default NOW | |
| `updated_at` | DATETIME | default NOW | |

**约束**：
- `ck_crawl_site_adapter_xor_yaml`：`cms_adapter` / `yaml_path` 两者恰好一个非空。
- `ck_crawl_site_managed_by`：`managed_by IN ('yaml','ui')`。

---

### 2.2 `site_department` — 站点部门路径

一个 row = 在某个站点上发现的一个部门路径（爬虫视角的"二级组织"）。
映射到 OA 权威部门 `local_department` 上，做对账。

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | BIGINT PK | | |
| `site_id` | BIGINT NOT NULL | FK→crawl_site.id CASCADE | |
| `dept_path` | VARCHAR(100) NOT NULL | UNIQUE(site_id, dept_path) | 站点上的路径，例 `/edu/` |
| `local_dept_id` | BIGINT | FK→local_department.dept_id SET NULL | OA 真实部门 |
| `dept_binding` | VARCHAR(20) NOT NULL | default 'pending' | `pending` / `mapped` / `city_level` / `cross_dept` / `external_ref` |
| `dept_display_name` | VARCHAR(200) | | 显示名，例 "清远市教育局" |
| `detect_status` | VARCHAR(20) | default 'pending' | 自动探测状态 |
| `last_probed_at` | DATETIME | | |
| `last_classify_json` | JSON | | 分类推断结果 |
| `enabled` | BOOLEAN NOT NULL | default TRUE | |
| `created_at` / `updated_at` | DATETIME | default NOW | |

**约束**：
- `uq_site_department_path`：`(site_id, dept_path)` 唯一。
- `ck_site_department_binding`：`dept_binding IN ('pending','mapped','city_level','cross_dept','external_ref')`。
- `ck_site_department_binding_local_dept`：`dept_binding='mapped'` ⇔ `local_dept_id IS NOT NULL`。

---

### 2.3 `crawl_target` — 采集目标

一个 row = 一个具体栏目的抓取任务。

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | BIGINT PK | | |
| `site_id` | BIGINT NOT NULL | FK→crawl_site.id CASCADE | |
| `site_department_id` | BIGINT | FK→site_department.id CASCADE | 可选的部门路径归属 |
| `target_code` | VARCHAR(100) UNIQUE NOT NULL | | `{site}__{dept}__{col_id}` |
| `target_name` | VARCHAR(200) | | 中文名，例 "县政数局-政务动态" |
| `entry_url` | VARCHAR(1000) | | 栏目入口 URL |
| `sample_article_url` | VARCHAR(1000) | | dry-run 样本 URL |
| `dept_id` | BIGINT | FK→local_department.dept_id SET NULL | 直接绑定 OA 部门 |
| `parser_override_json` | JSON | | 选择器覆盖（低频兜底） |
| `channel_name` | VARCHAR(200) | | 栏目名 |
| `channel_path` | VARCHAR(1000) | | 栏目完整路径，例 "机构信息｜内设机构" |
| `content_category` | VARCHAR(100) | | 一级分类 |
| `content_subcategory` | VARCHAR(100) | | 二级分类 |
| `schedule_cron` | VARCHAR(100) | | cron 表达式（scheduler 读取）|
| `expected_cadence_days` | INT NOT NULL | default 30 | 健康度判定窗口 |
| `interval_sec` | INT | | 每条 URL 间隔，用于限流 |
| `interval_jitter_sec` | INT | | 正向随机跳动秒数；实际等待为 `interval_sec + 0..interval_jitter_sec` |
| `enabled` | BOOLEAN NOT NULL | default TRUE | |
| `last_crawled_at` | DATETIME | | 最近一次列表抓取时间 |
| `last_article_time` | DATETIME | | 最近入库文章时间 |
| `created_at` / `updated_at` | DATETIME | default NOW | |

---

### 2.4 `local_department` — OA 权威部门

清远市/县区政府组成部门 + 乡镇 + 虚拟参考节点的主数据。
PK 使用 OA 原始 `dept_id`，不自增（直接同源）。

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `dept_id` | BIGINT PK | | OA 部门 ID |
| `dept_name` | VARCHAR(250) | | 正式名称 |
| `parent_dept_id` | BIGINT | | 父级 dept_id（非强制 FK） |
| `dept_level` | INT | | 1 市级 / 2 县区 / 3 镇街 |
| `dept_code` | VARCHAR(250) | | OA 部门编码 |
| `short_name` | VARCHAR(50) | | 简称，用于 UI 拼接显示名 |
| `full_name` | VARCHAR(500) | | 全称 |
| `state` | INT | | OA 状态码 |
| `order_id` | INT | | OA 排序号 |
| `updated_at` | DATETIME | | OA 更新时间 |
| `region` | VARCHAR(50) INDEX | | **清远市/县区**归属，UI 下拉分组用 |

**种子数据**：由 `scripts/import_local_departments.py` 从 `清远市及县区政府组成部门和乡镇/*.xlsx` 导入 339 条，再由 `scripts/backfill_dept_region.py` 回填 `region`。

---

### 2.5 `article` — 文章

一个 row = 一篇抓到的政务公开文章。

**核心字段**：

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | BIGINT PK | | |
| `site_id` | BIGINT NOT NULL | FK→crawl_site.id RESTRICT | 保护历史文章不被误删 |
| `target_id` | BIGINT | FK→crawl_target.id SET NULL | |
| `dept_id` | BIGINT | FK→local_department.dept_id SET NULL | |
| `native_post_id` | VARCHAR(64) | | CMS 原始文章 ID |
| `url` | TEXT NOT NULL | | 原文 URL |
| `url_hash` | CHAR(64) UNIQUE NOT NULL | | URL SHA-256 — **全局去重铁律** |
| `title` | TEXT | | |
| `publish_time` | DATETIME | | UTC |

**政府信息公开要素**（MODAL 顶级展示）：

| 字段 | 类型 | 说明 |
|---|---|---|
| `index_no` | VARCHAR(200) | 索引号 |
| `doc_no` | VARCHAR(200) | 文号 |
| `publisher` | VARCHAR(500) | 发布机构 |
| `publish_date` | DATE | 成文日期 |
| `effective_date` | DATE | 生效日期 |
| `is_effective` | BOOLEAN | 是否仍有效，NULL 表示未知 |
| `expiry_date` | DATE | 失效/废止日期 |
| `topic_words` | VARCHAR(500) | 主题词 |
| `open_category` | VARCHAR(200) | 开放类别 |

**元数据 / 落盘 / 状态**：

| 字段 | 类型 | 说明 |
|---|---|---|
| `channel_name` / `channel_path` | VARCHAR | 栏目路径 |
| `content_category` / `content_subcategory` | VARCHAR(100) | 内容分类 |
| `metadata_json` | JSON | 扩展字段 |
| `content_text` | TEXT | 正文纯文本 |
| `raw_html_path` | TEXT | 原始 HTML 盘上路径 |
| `text_path` | TEXT | `.txt` 盘上路径 |
| `has_attachment` | BOOLEAN | default FALSE |
| `status` | VARCHAR(20) | `raw` / `ready` / `failed` |
| `fetch_strategy` | VARCHAR(50) | `httpx` / `playwright` |
| `source_raw` | VARCHAR(500) | 源信息快照 |
| `fetched_at` | DATETIME NOT NULL | default NOW（UTC） |
| `exported_to_rag_at` | DATETIME | RAG 侧 ack 后回填 |
| `created_at` / `updated_at` | DATETIME | |

**索引 & 约束**：
- `uq_article_url_hash`：`url_hash` UNIQUE。
- `uq_article_site_native`：当 `native_post_id` 非空时 `(site_id, native_post_id)` 唯一（Postgres partial index，MySQL 退化为普通唯一索引）。
- `ix_article_site_pub`：`(site_id, publish_time)`。
- `ix_article_target_pub`：`(target_id, publish_time)`。
- `ix_article_exported`：`exported_to_rag_at`。
- `ix_article_status`：`status`。
- `ck_article_status`：`status IN ('raw','ready','failed')`。

---

### 2.6 `attachment` — 附件

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | BIGINT PK | | |
| `article_id` | BIGINT NOT NULL | FK→article.id CASCADE | |
| `file_name` | TEXT | | |
| `file_ext` | VARCHAR(20) | | pdf / docx / xls… |
| `size_bytes` | BIGINT | | |
| `file_path` | TEXT | | 落盘相对路径 |
| `file_hash` | CHAR(64) | INDEX | SHA-256，用于跨文件去重 |
| `downloaded_at` | DATETIME | default NOW | |

**索引**：`ix_attachment_file_hash`。

---

### 2.7 `crawl_log` — 抓取日志

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | BIGINT PK | | |
| `site_id` | BIGINT | FK→crawl_site.id SET NULL | |
| `target_id` | BIGINT | FK→crawl_target.id SET NULL | |
| `article_url` | TEXT | | |
| `strategy` | VARCHAR(50) | | httpx / playwright |
| `http_status` | INT | | |
| `duration_ms` | INT | | |
| `success` | BOOLEAN NOT NULL | | |
| `error_msg` | TEXT | | |
| `occurred_at` | DATETIME | default NOW | |

**索引**：`ix_crawl_log_site_occurred`、`ix_crawl_log_target_occurred`。

---

### 2.8 `master_column_registry` — 全局栏目目录

Qingyuan 专项增强：CMS 适配器探测到的所有栏目，不一定每个都会被订阅为 crawl_target。

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | BIGINT PK | | |
| `adapter_id` | VARCHAR(50) NOT NULL | | `gkmlpt` / `trs_wcm` |
| `site_id` | BIGINT NOT NULL | FK→crawl_site.id CASCADE | |
| `column_id` | VARCHAR(64) NOT NULL | | CMS 原始栏目 ID |
| `column_name` | VARCHAR(500) | | |
| `column_path` | VARCHAR(1000) | | |
| `admin_level` | VARCHAR(20) | | `city` / `county` / `township` |
| `topic_tags` | JSON | | 主题标签数组 |
| `post_count` | INT | | 近期文章数 |
| `last_seen_at` | DATETIME | | |
| `subscribed_target_id` | BIGINT | FK→crawl_target.id SET NULL | 被订阅的 target |
| `active` | BOOLEAN | default TRUE | |
| `created_at` / `updated_at` | DATETIME | default NOW | |

**约束**：`uq_mcr_adapter_site_col` — `(adapter_id, site_id, column_id)` 唯一。

---

## 3. 外键级联与删除语义

| 表.字段 | FK | ON DELETE | 语义 |
|---|---|---|---|
| site_department.site_id | crawl_site.id | CASCADE | 站点删 → 部门路径一起删 |
| site_department.local_dept_id | local_department.dept_id | SET NULL | OA 部门删 → 绑定置空 |
| crawl_target.site_id | crawl_site.id | CASCADE | 站点删 → target 一起删 |
| crawl_target.site_department_id | site_department.id | CASCADE | 部门删 → 其下 target 也删 |
| crawl_target.dept_id | local_department.dept_id | SET NULL | |
| article.site_id | crawl_site.id | **RESTRICT** | 有文章的站点禁止删（保护历史） |
| article.target_id | crawl_target.id | SET NULL | |
| article.dept_id | local_department.dept_id | SET NULL | |
| attachment.article_id | article.id | CASCADE | 文章删 → 附件一起 |
| crawl_log.site_id | crawl_site.id | SET NULL | 日志独立留存 |
| crawl_log.target_id | crawl_target.id | SET NULL | |
| master_column_registry.site_id | crawl_site.id | CASCADE | |
| master_column_registry.subscribed_target_id | crawl_target.id | SET NULL | |

---

## 4. 迁移版本管理（Alembic）

```
alembic/versions/
├── 0001_initial_schema.py         # 原始 v1 表结构
├── 0002_schema_v2_rebuild.py      # v2 §5 重建（当前基线）
├── 0003_add_managed_by.py         # crawl_site.managed_by (yaml/ui)
└── 0004_add_local_dept_region.py  # local_department.region + 索引
```

部署：
```bash
uv run alembic upgrade head
```

---

## 5. 种子数据 & 导入脚本

| 脚本 | 作用 |
|---|---|
| `scripts/import_local_departments.py` | 从 9 个 xlsx 导入 339 条 OA 部门到 `local_department` |
| `scripts/backfill_dept_region.py` | 回填 `local_department.region`（按 Excel 文件名归属） |
| `scripts/pg_to_mysql.py` | 一次性：旧 postgres → 新 MySQL 全量迁移 |

---

## 6. 典型查询

**24h 成功率**：
```sql
SELECT COUNT(*) total,
       SUM(success) ok
FROM crawl_log
WHERE occurred_at >= NOW() - INTERVAL 1 DAY;
```

**按部门统计文章**：
```sql
SELECT ld.region, ld.short_name, COUNT(a.id) n
FROM article a
JOIN local_department ld ON ld.dept_id = a.dept_id
WHERE a.status = 'ready'
GROUP BY ld.region, ld.short_name
ORDER BY n DESC;
```

**未导出 RAG 的文章**：
```sql
SELECT id, title, site_id, fetched_at
FROM article
WHERE status = 'ready' AND exported_to_rag_at IS NULL
ORDER BY fetched_at
LIMIT 100;
```

**某站点待对账的部门路径**：
```sql
SELECT sd.id, sd.dept_path, sd.dept_display_name
FROM site_department sd
JOIN crawl_site cs ON cs.id = sd.site_id
WHERE cs.site_code = 'fogang' AND sd.dept_binding = 'pending';
```

---

## 7. 索引与性能注意

- `article` 按 `(site_id, publish_time)` / `(target_id, publish_time)` 最常查询，两个复合索引已覆盖。
- `article.url_hash` UNIQUE 是**全局去重**关键，插入前总是先查，别跳过。
- `crawl_log` 按 `(site_id, occurred_at)` / `(target_id, occurred_at)` 构建 24h 成功率监控，覆盖索引。
- `master_column_registry.topic_tags` 是 JSON，MySQL 5.7 支持 JSON 函数但不支持直接 GIN 索引；按 topic 过滤走 `JSON_CONTAINS` / `LIKE` + 应用层过滤。
- 大表：article (预期千万级) / crawl_log (亿级)。运行一年后可能需要分区（按 `fetched_at` / `occurred_at`）或按站点分表。

---

## 8. MySQL 部署特殊说明

- **字符集**：`utf8mb4` / `utf8mb4_unicode_ci`（支持 emoji + 全汉字）。
- **存储引擎**：InnoDB（支持 FK + 事务）。
- **大小写**：MySQL 5.7 默认 `lower_case_table_names=1`，表名存储为小写（`govcrawler`），应用端 URL 用 `GovCrawler` 也能命中。
- **partial index**：`uq_article_site_native` 在 Postgres 是 partial（`native_post_id IS NOT NULL`），MySQL 退化为**普通唯一索引**——这会导致空值唯一冲突。当前数据量不触发，未来如果 `native_post_id` 普遍为空且有并发写入，需要改为应用层去重。
- **密码策略**：MySQL 5.7 默认 validate_password.policy=MEDIUM，建新用户需 ≥ 8 位 + 大小写 + 数字 + 特殊字符。

---

## 9. 变更记录

| 版本 | 日期 | 变更 |
|---|---|---|
| v1.0 | 2026-03-xx | 初始 schema（5 张表） |
| v2.0 | 2026-04-22 | §5 重建：引入 `local_department` / `site_department` / `master_column_registry`，url_hash 铁律 |
| v2.1 | 2026-04-23 | 新增 `crawl_site.managed_by`（yaml/ui） |
| v2.2 | 2026-04-24 | 新增 `local_department.region`（清远市/县区归属） |
| v2.2-mysql | 2026-04-24 | 部署迁移至 MySQL 5.7（192.168.1.222） |