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)  # &amp; → &
  → 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

  • &amp; 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)