WeChat Group Activity Stats

WeChat Group Activity Stats

Overview

Extracts group chat activity from WeChat's local encrypted SQLite database on macOS. Gives you: per-member message counts (total / 1mo / 3mo / 6mo), activity tags (🔥超活跃 → 💀死号), an interactive dark-themed web dashboard, and a JSON API for automation.

The full pipeline: decrypt → analyze → dashboard. Agent guides the user through each step, handling platform quirks and permissions.

Prerequisites: macOS, WeChat 4.x installed, admin password (sudo).

Tool directory: ~/wechat-group-stats/ (this project — contains wechat-stats.py, dashboard.html, wechat-server.py). The upstream decryption engine (ylytdeng/wechat-decrypt) should be installed separately at ~/wechat-decrypt/.

When to Use

• User says: "analyze my WeChat group activity", "check who's active in my group", "WeChat group stats"
• User wants to identify inactive members to remove
• User wants a recurring activity dashboard
• User asks about setting up WeChat DB decryption for analytics

Quick Start (for the Agent)

When the user asks to analyze a WeChat group, guide them through this sequence:

1. Check Prerequisites

# Verify WeChat is installed
ls /Applications/WeChat.app

# Check if wechat-decrypt is already set up (external dependency)
ls ~/wechat-decrypt/find_all_keys_macos ~/wechat-decrypt/decrypt_db.py

# Check if this project is cloned
ls ~/wechat-group-stats/wechat-stats.py

If wechat-decrypt isn't set up, clone and install:

git clone https://github.com/ylytdeng/wechat-decrypt.git ~/wechat-decrypt
cd ~/wechat-decrypt
python3 -m venv .venv && source .venv/bin/activate
pip install pycryptodome zstandard pilk tqdm
cc -O2 -o find_all_keys_macos find_all_keys_macos.c -framework Foundation

If wechat-group-stats isn't cloned:

git clone <repo-url> ~/wechat-group-stats

2. Re-sign WeChat (one-time)

WeChat's Hardened Runtime prevents memory access. Re-signing removes it.

Ask the user to quit WeChat first, then run:

killall WeChat
sudo codesign --force --sign - /Applications/WeChat.app

If codesign fails with "Operation not permitted":

• Grant Terminal Full Disk Access in 系统设置 → 隐私与安全性 → 完全磁盘访问
• Re-open Terminal and retry
• Alternative: cp -R /Applications/WeChat.app ~/Desktop/ && sign the copy

Verify: codesign -dv /Applications/WeChat.app 2>&1 | grep flags should show flags=0x2(adhoc).

Then ask user to re-open WeChat and log in.

3. Extract Keys

cd ~/wechat-decrypt
sudo ./find_all_keys_macos

Outputs all_keys.json. If it fails with task_for_pid: 5, the re-sign in step 2 didn't work — go back.

4. Decrypt Database

Auto-detects the db_storage path and creates config.json:

cd ~/wechat-decrypt && source .venv/bin/activate
python3 decrypt_db.py

Decrypted DBs land in ~/wechat-decrypt/decrypted/.

5. Set Custom Group Name (one-time)

python3 wechat-stats.py --set-name "群ID" "自定义群名"
# Example:
python3 wechat-stats.py --set-name "45379818937@chatroom" "链上前进四🚀"

6. Run Analysis

python3 wechat-stats.py --group "链上前进四" --decrypted-dir ./decrypted

Outputs wechat-stats.json. If the group name isn't found, run without --group to list all groups, then pick the right one.

7. Launch Dashboard

python3 wechat-server.py
# Opens at http://localhost:8080/dashboard.html

Dashboard features: member ranking, sortable columns, activity distribution bars, 🔄 one-click refresh button, search filter, tab to toggle active/inactive members.

Database Schema Reference

Full details in references/wechat-db-schema.md — includes the critical real_sender_id → wxid mapping chain discovered 2025-06-03, and why ext_buffer should NOT be used for group names.

Useful for custom queries beyond the built-in analysis:

Mapping chain: Msg_<md5>.real_sender_id → extract wxid from message_content → contact.username → contact.nick_name/remark

Msg table hash: md5(group_username.encode()).hexdigest()

Data path (WeChat 4.x):

~/Library/Containers/com.tencent.xinWeChat/Data/Documents/
  xwechat_files/<wxid>/db_storage/{message,contact,session}/*.db

WeChat Encryption Parameters (4.x)

Common Pitfalls

1. real_sender_id ≠ contact.id. The message table uses an internal sender ID space that does NOT match contact.id or chatroom_member.member_id. Must extract wxid from message_content (e.g. "wxid_xxx:\n内容") and match against contact.username. See references/wechat-db-schema.md for the full mapping chain and extraction code.

2. Display name priority: remark(备注) > nick_name(昵称) > alias(别名) > username. Using alias > nick will show machine names like "XYiDao" instead of human names like "毅". For example: contact with alias="XYiDao", nick="毅" should display as "毅", not "XYiDao".

3. WeChat wasn't quit before re-signing. Running binary/dylib files are locked — codesign fails. Always killall WeChat first. Common error: internal error in Code Signing subsystem / In subcomponent: ...libEGL.dylib.

4. Re-signing needs "Full Disk Access" for Terminal. If sudo codesign fails with Operation not permitted / In subcomponent: ...WeChatAppEx.app, Terminal.app lacks FDA. Go to 系统设置 → 隐私与安全性 → 完全磁盘访问, add Terminal.app, then re-open Terminal and retry.

5. --deep flag causes nested bundle failures. WeChat.app contains WeChatAppEx.app inside — --deep tries to recursively sign it and fails with Operation not permitted. Use codesign --force --sign - without --deep. Only the main bundle needs signing to unlock task_for_pid. If signing in /Applications still fails even with FDA (some macOS versions block writes there regardless), copy WeChat to Desktop first: cp -R /Applications/WeChat.app ~/Desktop/WeChat_signed.app && sudo codesign --force --sign - ~/Desktop/WeChat_signed.app, then run the signed copy.

6. Key extraction needs WeChat running and ad-hoc signed. The C scanner (find_all_keys_macos) reads WeChat's process memory via mach_vm — this is blocked by Hardened Runtime (flags=0x10000). Error: task_for_pid failed: 5.

7. WeChat updates will overwrite the ad-hoc signature. After a WeChat auto-update, you need to re-sign again and re-extract keys. The database encryption key may also change.

8. Group names not stored locally in WeChat 4.x. The ext_buffer protobuf in chat_room contains member info but the group display name is fetched from the server and may not be in the local DB. Do NOT attempt to extract group names from ext_buffer — the protobuf's first Chinese string is typically a member's nickname (e.g. "秋刀鱼配柠檬"), not the group name. Use --set-name to assign custom names stored in group-names.json. Without a custom name, display as "群聊 (X人)" where X is the member count. The extract_group_name() function in wechat-stats.py is intentionally disabled and returns None.

9. Analysis script reads already-decrypted DBs. After initial setup, daily refreshes only need step 6 (no sudo, no WeChat restart). Only re-run steps 3-4 if WeChat updated or you suspect key changes.

10. Dashboard needs HTTP server. Opening dashboard.html directly from filesystem will fail to load wechat-stats.json due to CORS. Always use python3 wechat-server.py or python3 -m http.server 8080. The dedicated server also provides the /api/run endpoint for the one-click refresh button.

11. WCDB compression in message_content. The WeChat message DB uses WCDB compression. About 30% of rows have WCDB_CT_message_content set to a non-zero value (typically 4), meaning message_content contains compressed binary — not readable text. When extracting wxid from message_content, always filter: AND (WCDB_CT_message_content IS NULL OR WCDB_CT_message_content = 0). Without this filter, LIMIT 1 has a ~30% chance of returning binary garbage, causing the wxid mapping to fail silently and the member's message count to stay at 0 (tagged as 💀死号). This can affect 60+ members in a large group. SQLite's LIKE operator triggers automatic decompression, so WHERE message_content LIKE '%wxid_%' would work too, but WCDB_CT_message_content = 0 is more explicit and avoids false matches.