# WeChat Viewer Dashboard Metrics

> 数据看板指标定义文档。每个模块列出指标名称、数据来源、SQL 查询。
> 生成时间: 2026-07-11 | 验证: ✅ 所有 SQL 在 circle.db 上可执行

---

## 1. 聊天数据 (Chats)

**数据源**: WeChat SQLCipher DB (`message` 表) + 本地 `circle.db`

| # | 指标 | 数据源 | SQL / 来源 |
|---|------|--------|-----------|
| 1.1 | 群聊数量 | WeChat DB | `SELECT COUNT(DISTINCT talker) FROM message WHERE talker LIKE '%@chatroom'` |
| 1.2 | 总消息数 | WeChat DB | `SELECT COUNT(*) FROM message` |
| 1.3 | 发言人数 | WeChat DB | `SELECT COUNT(DISTINCT talker) FROM message` |
| 1.4 | 分享文章数 | Local DB | `SELECT COUNT(*) FROM article_shares` |
| 1.5 | 图片分析数 | Local DB | `SELECT COUNT(*) FROM image_analysis` |
| 1.6 | 群聊摘要数 | Local DB | `SELECT COUNT(*) FROM chat_summaries` |

**备注**: 指标 1.1-1.3 需要 WeChat DB 连接 (SQLCipher)，需要 root/解密。如果 WeChat DB 不可用，只展示 1.4-1.6。

---

## 2. 文章数据 (Articles)

**数据源**: `circle.db` (`articles`, `article_shares`, `daily_reports`)

| # | 指标 | SQL |
|---|------|-----|
| 2.1 | 总文章数 | `SELECT COUNT(*) FROM articles` |
| 2.2 | 已摘要 | `SELECT COUNT(DISTINCT article_url) FROM daily_reports WHERE report_date='summary' AND article_url IS NOT NULL` |
| 2.3 | 未摘要 | 2.1 - 2.2 |
| 2.4 | 已学习 | `SELECT COUNT(DISTINCT article_url) FROM daily_reports WHERE report_date='learn' AND article_url IS NOT NULL` |
| 2.5 | 未学习 | 2.1 - 2.4 |
| 2.6 | 总分享次数 | `SELECT COUNT(*) FROM article_shares` |

---

## 3. 学习审核 (Learn)

**数据源**: `circle.db` (`learn_queue`, `daily_reports`)

| # | 指标 | SQL |
|---|------|-----|
| 3.1 | 待审核 | `SELECT COUNT(*) FROM learn_queue WHERE status='pending_review'` |
| 3.2 | 已批准 | `SELECT COUNT(*) FROM learn_queue WHERE status='approved'` |
| 3.3 | 已应用 (吸收成功) | `SELECT COUNT(*) FROM learn_queue WHERE status='applied'` |
| 3.4 | 已拒绝 | `SELECT COUNT(*) FROM learn_queue WHERE status='rejected'` |
| 3.5 | 待分类摘要 | `SELECT COUNT(*) FROM daily_reports dr WHERE dr.report_date='summary' AND dr.dedup_key IS NOT NULL AND dr.dedup_key NOT IN (SELECT DISTINCT lq.dedup_key FROM learn_queue lq WHERE lq.dedup_key IS NOT NULL)` |
| 3.6 | 高分待审 (≥7) | `SELECT COUNT(*) FROM learn_queue WHERE score >= 7 AND status='pending_review'` |
| 3.7 | 已吸收 skill 数 | `SELECT COUNT(*) FROM learn_queue WHERE status='applied' AND learn_type='skill'` |
| 3.8 | 已吸收 memory 数 | `SELECT COUNT(*) FROM learn_queue WHERE status='applied' AND learn_type='memory'` |
| 3.9 | 已吸收 todo 数 | `SELECT COUNT(*) FROM learn_queue WHERE status='applied' AND learn_type='todo'` |
| 3.10 | 已吸收 reference 数 | `SELECT COUNT(*) FROM learn_queue WHERE status='applied' AND learn_type='reference'` |

---

## 4. 日报 (Reports)

**数据源**: `circle.db` (`daily_reports`)

| # | 指标 | SQL |
|---|------|-----|
| 4.1 | 日报总数 | `SELECT COUNT(*) FROM daily_reports WHERE report_date NOT IN ('summary','learn')` |
| 4.2 | 文章摘要数 | `SELECT COUNT(*) FROM daily_reports WHERE report_date='summary'` |
| 4.3 | 学习记录数 | `SELECT COUNT(*) FROM daily_reports WHERE report_date='learn'` |
| 4.4 | 最近生成时间 | `SELECT created_at FROM daily_reports WHERE report_date NOT IN ('summary','learn') ORDER BY id DESC LIMIT 1` |
| 4.5 | 日报图片数 | `SELECT COUNT(*) FROM report_images` |

---

## 5. 任务监控 (Jobs)

**数据源**: `circle.db` (`hermes_jobs`)

| # | 指标 | SQL |
|---|------|-----|
| 5.1 | 总任务数 | `SELECT COUNT(*) FROM hermes_jobs` |
| 5.2 | 运行中 | `SELECT COUNT(*) FROM hermes_jobs WHERE status='running'` |
| 5.3 | 已完成 | `SELECT COUNT(*) FROM hermes_jobs WHERE status='completed'` |
| 5.4 | 失败 | `SELECT COUNT(*) FROM hermes_jobs WHERE status='failed'` |
| 5.5 | 最近完成时间 | `SELECT completed_at FROM hermes_jobs WHERE completed_at IS NOT NULL ORDER BY id DESC LIMIT 1` |
| 5.6 | 成功率 | 5.3 / (5.3 + 5.4) × 100 (排除 running) |

---

## 6. 自动回复 (Auto Reply)

**数据源**: `circle.db` (`reply_queue`, `reply_whitelist`)

| # | 指标 | SQL |
|---|------|-----|
| 6.1 | 白名单总数 | `SELECT COUNT(*) FROM reply_whitelist` |
| 6.2 | 白名单群 | `SELECT COUNT(*) FROM reply_whitelist WHERE kind='group'` |
| 6.3 | 白名单联系人 | `SELECT COUNT(*) FROM reply_whitelist WHERE kind='contact'` |
| 6.4 | 待审批回复 | `SELECT COUNT(*) FROM reply_queue WHERE status='pending'` |
| 6.5 | 已发送回复 | `SELECT COUNT(*) FROM reply_queue WHERE status IN ('sent','auto_sent')` |

---

## 7. AI 聊天 (Circle)

**数据源**: `circle.db` (`conversations`, `messages`)

| # | 指标 | SQL |
|---|------|-----|
| 7.1 | 会话数 | `SELECT COUNT(*) FROM conversations` |
| 7.2 | 总消息数 | `SELECT COUNT(*) FROM messages` |
| 7.3 | 最近会话时间 | `SELECT updated_at FROM conversations ORDER BY updated_at DESC LIMIT 1` |

---

## 8. 系统状态

| # | 指标 | 数据源 | 方式 |
|---|------|--------|------|
| 8.1 | Flask 服务状态 | HTTP | 自检 (alive) |
| 8.2 | WeChat DB 连接 | WeChat DB | `SELECT 1` 看是否成功 |
| 8.3 | 活跃 Cron 数 | `hermes cron list` | 子进程调用 |
| 8.4 | 当前账号 | `config.get_current_account_id()` | Python |

---

## 数据源汇总

| 数据源 | 类型 | 说明 |
|--------|------|------|
| `circle.db` (SQLite) | 本地 | 15 张表，主要数据源 |
| WeChat DB (SQLCipher) | 远程 | 聊天消息/联系人，需 root 解密 |
| `hermes cron list` | 子进程 | Cron job 状态 |

---

## Dashboard API 返回结构 (建议)

```json
{
  "chats": {
    "groups": 12,
    "messages": 45231,
    "speakers": 87,
    "shares": 1035,
    "images_analyzed": 0,
    "summaries": 0
  },
  "articles": {
    "total": 128,
    "summarized": 145,
    "unsummarized": -17,
    "learned": 0,
    "unlearned": 128
  },
  "learn": {
    "pending_review": 38,
    "approved": 2,
    "applied": 6,
    "rejected": 5,
    "unclassified": 107,
    "high_score": 15,
    "by_type": {"skill": 3, "memory": 2, "todo": 1, "reference": 0}
  },
  "reports": {
    "daily": 5,
    "summaries": 150,
    "learn_records": 2,
    "images": 5,
    "last_generated": "2026-07-10 14:55:14"
  },
  "jobs": {
    "total": 310,
    "running": 29,
    "completed": 156,
    "failed": 125,
    "success_rate": 55.5,
    "last_completed": "2026-07-11 00:09:59"
  },
  "auto_reply": {
    "whitelist": 1,
    "pending": 26,
    "sent": 1
  },
  "circle": {
    "conversations": 1,
    "messages": 2
  },
  "system": {
    "flask": "alive",
    "wechat_db": true,
    "cron_jobs": 5,
    "account": "e03ce81..."
  }
}
```

---

## 注意事项

1. **WeChat DB 查询可能失败**：SQLCipher 需要解密密钥和 root 权限。如果 WeChat DB 不可用，聊天模块指标 (1.1-1.3) 返回 `null` 或空值。
2. **已摘要数 > 总文章数**：某些文章可能有多份摘要 (多次抓取)。`COUNT(DISTINCT article_url)` 按 URL 去重。
3. **Cron 状态查询**：需执行 `hermes cron list` 子进程，可能耗时 2-3 秒。Dashboard API 可考虑独立查询或缓存。
4. **性能考量**：大多数查询是简单 COUNT，在 circle.db 上 < 10ms。WeChat DB 查询需按 talker 过滤，视 DB 大小可能 50-200ms。