article-two-table-schema.md
2.68 KB
Article Two-Table Schema (2026-07-10)
Why Two Tables?
同一篇文章被多个微信群分享时:
- 旧设计 (article_queue): 每篇×每组 = 多行,冗余标题+URL,查"某群有哪些文章"需要 DISTINCT + 全表扫描
- 新设计 (articles + article_shares): 文章元数据一行,分享记录独立行,按 chatroom_id 索引 O(1) 查询
Tables
articles (一篇一行)
CREATE TABLE articles (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url TEXT UNIQUE, -- 清洗后的完整 URL (& → &)
title TEXT,
first_seen_at TEXT DEFAULT (datetime('now','localtime'))
);
article_shares (一条分享一行)
CREATE TABLE article_shares (
id INTEGER PRIMARY KEY AUTOINCREMENT,
article_id INTEGER REFERENCES articles(id),
chatroom_id TEXT, -- 哪个群 (@chatroom 结尾)
talker TEXT, -- 谁发的 (wxid 或 chatroom_id)
source_msg_id INTEGER, -- 微信原始 msgId
msg_create_time INTEGER, -- 微信原始消息时间戳 (毫秒)
shared_at TEXT DEFAULT (datetime('now','localtime'))
);
Key Query: Articles in Group X
SELECT DISTINCT a.id, a.url, a.title,
MAX(s.msg_create_time) as createTime,
r.content as summary, r.id as report_id
FROM articles a
JOIN article_shares s ON a.id = s.article_id
LEFT JOIN daily_reports r ON r.article_url = a.url AND r.report_date = 'summary'
WHERE s.chatroom_id = ?
GROUP BY a.id
ORDER BY MAX(s.msg_create_time) DESC
Key Query: Unsummarized Articles
SELECT DISTINCT a.id, a.url, a.title
FROM articles a
JOIN article_shares s ON a.id = s.article_id
WHERE a.id NOT IN (
SELECT DISTINCT a2.id FROM articles a2
INNER JOIN daily_reports r ON r.article_url = a2.url AND r.report_date = 'summary'
)
GROUP BY a.id ORDER BY a.id DESC
Extract Flow
WeChat DB (message WHERE type=49)
→ xml.etree parse: <url>, <title>, <des>
→ html.unescape(url) # & → &
→ article_upsert(url, title) # INSERT OR IGNORE
→ article_share_add(article_id, chatroom_id, talker, msg_id, createTime)
Migration from article_queue
article_queue 表保留兼容。新 API 同时写两套(extract 写 articles+article_shares,老 article_add 仍然可用)。/api/articles 已切换到新表。
Pitfalls
&in WeChat XML URLs must be unescaped before storing, otherwise summarize API's regex__biz=([^&]+)won't matchmsg_create_timeis in milliseconds since epoch (WeChat convention). Divide by 1000 for Pythontime.localtime()GROUP BY a.idrequired because articles can have multiple shares (same article shared in different groups)