数据库 Schema设计方法详解（SQLite 选型理由与表结构）

AI 热点监控工具的数据层用 SQLite 承载全部持久化，表结构围绕四条核心数据设计：关键词、采集结果、热点条目与通知记录。选 SQLite 的根本原因是部署形态：工具是单机运行的轻量服务，一轮采集写几十到几百条记录，读取为主、写入低频，SQLite 在这种负载下比 PostgreSQL 少一层网络开销，启动零配置，数据库就是一个文件。打开 WAL 模式后读写并发能力足够覆盖项目上限，我在 schema 上还叠加了唯一约束去重、复合索引和 FTS5 全文检索，这套组合跑满 16+ 数据源也没有性能问题。

一、为什么选 SQLite 而不是 MySQL 或 PostgreSQL

选型时对比过三者的运维成本与并发特性。工具类项目的突出特点是没有专职 DBA，SQLite 免安装、免连接配置、单文件备份，git 里都能直接带版本。WAL 模式（PRAGMA journal_mode=WAL）让读事务和写事务并发执行，读写互不阻塞，配合 busy_timeout 处理写锁竞争，单机场景下完全够用。

维度	SQLite	MySQL	PostgreSQL
部署复杂度	零配置，嵌入进程	独立服务，需维护	独立服务，需维护
单机读性能	本地文件读，无网络开销	需走连接	需走连接
写并发	单写者，WAL 下读写并发	行级锁，多写者	MVCC，多写者
备份	直接拷贝文件	dump / binlog	pg_dump
适用负载	单服务器、中低写量	中高并发	高并发复杂查询

热点监控的写入特征是每 30 分钟一批突发写入，其余时间只有用户操作的零星写。写事务在 WAL 模式下通常亚毫秒级完成，读者不阻塞，即使采集批量落库时列表页也在正常响应。数据量一年量级在百万条以内，SQLite 单文件足以承载。

二、核心表结构设计

Schema 用 Prisma 定义，启动时自动建表。四张表的关系是一对多：关键词 → 采集结果 → 热点条目，通知独立成表。

model Keyword {
  id          Int      @id @default(autoincrement())
  text        String   @unique
  status      String   @default("active")  // active | paused | deleted
  variants    String?  // AI 查询扩展变体，JSON 数组
  createdAt   DateTime @default(now())
}

model RawItem {
  id          Int      @id @default(autoincrement())
  keywordId   Int
  source      String   // hackernews / bing / weibo ...
  title       String
  url         String
  urlHash     String   @unique   // MD5(url)，跨源去重键
  publishedAt DateTime
  rawData     String?  // 源数据 JSON，保留原始字段
  analyzed    Boolean  @default(false)
  @@index([keywordId, publishedAt])
  @@index([source])
}

model Hotspot {
  id          Int      @id @default(autoincrement())
  rawItemId   Int
  title       String
  summary     String?            // AI 摘要
  relevance   Int      @default(0)  // 0-100 相关分
  importance  String   @default("medium") // urgent | high | medium | low
  isFake      Boolean  @default(false)    // AI 真假识别
  reason      String?            // 相关性分析理由
  createdAt   DateTime @default(now())
  @@index([importance, createdAt])
  @@index([relevance])
}

model Notification {
  id        Int      @id @default(autoincrement())
  hotspotId Int
  channel   String   // websocket | email | inbox
  status    String   @default("sent")
  readAt    DateTime?
  createdAt DateTime @default(now())
}

2.1 去重设计放在数据库层

多数据源聚合最怕同一条内容重复入库。方案是对 URL 做规范化后取 MD5 生成 urlHash，设成唯一索引，插入冲突时 onConflict 跳过。这样即使 AI 查询扩展产生多个变体、同一篇文章被多个源抓到，数据库层直接挡掉重复，应用代码不用每次比对。

2.2 索引跟着查询走

列表页最常用的筛选是”按重要程度 + 时间倒序”和”按关键词 + 时间倒序”，所以建了 [importance, createdAt] 和 [keywordId, publishedAt] 两个复合索引。排序字段做秒级去重用的自增主键，覆盖大多数分页场景，全表扫描只发生在跨全部历史数据的汇总统计里，频率极低。

2.3 FTS5 做站内搜索

热点全文检索用 SQLite 的 FTS5 虚拟表，建 hotspot_fts(title, summary) 对应正文，搜索接口直接用 match 语法。索引通过触发器在写入热点时同步更新，避免应用层手动维护一致性。

CREATE VIRTUAL TABLE hotspot_fts USING fts5(title, summary, content=Hotspot);
CREATE TRIGGER hotspot_ai AFTER INSERT ON Hotspot BEGIN
  INSERT INTO hotspot_fts(rowid, title, summary)
  VALUES (new.id, new.title, new.summary);
END;

三、启用的三条 SQLite 生产级配置

建库连接后固定执行三条 PRAGMA，缺一不可：

PRAGMA journal_mode=WAL 开启预写日志，读写并发；
PRAGMA busy_timeout=5000 写锁竞争时等待而非立即报错；
PRAGMA synchronous=NORMAL 兼顾持久性与写入速度，WAL 模式下断电最多丢最近几帧。

四、SQLite 的边界与退出路径

工具是单机部署，所以 SQLite 够用，但边界要提前划清。数据库文件不能放 NFS 之类的网络盘上，文件锁在共享存储上不可靠。若未来要水平扩容、多实例采集共用一份数据，再迁 PostgreSQL：Prisma schema 的表结构差异很小，把 urlHash 唯一约束和索引原样迁移即可，应用代码不需要改。

常见问题（FAQ）

Q1：SQLite 能扛住高并发写入吗？

WAL 模式支持并发读写但同一时刻单写者，适合热点工具这类低频批量写入。

Q2：去重为什么用 URL 哈希而不是标题？

同源转载标题会变但 URL 常一致，URL 哈希去重更稳，标题差异交给后续聚类。

Q3：数据量大了会不会卡？

加好复合索引、定期删历史、开 FTS5，百万级条目内查询仍保持毫秒级。