# Article Two-Table Schema (2026-07-10) ## Why Two Tables? 同一篇文章被多个微信群分享时: - 旧设计 (article_queue): 每篇×每组 = 多行,冗余标题+URL,查"某群有哪些文章"需要 DISTINCT + 全表扫描 - 新设计 (articles + article_shares): 文章元数据一行,分享记录独立行,按 chatroom_id 索引 O(1) 查询 ## Tables ### articles (一篇一行) ```sql CREATE TABLE articles ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT UNIQUE, -- 清洗后的完整 URL (& → &) title TEXT, first_seen_at TEXT DEFAULT (datetime('now','localtime')) ); ``` ### article_shares (一条分享一行) ```sql 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 ```sql 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 ```sql 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: , , <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 match - `msg_create_time` is in milliseconds since epoch (WeChat convention). Divide by 1000 for Python `time.localtime()` - `GROUP BY a.id` required because articles can have multiple shares (same article shared in different groups)