bug-radar

name

bug-radar

description

Bug-library analysis lane for TideRider. Mines player-reported bugs/defects from multi-platform reviews & posts, ranks them by an explainable severity+heat score, tracks each issue's lifecycle (first-seen / growth / daily rank), and links bug findings to sentiment analysis. Triggers: bug analysis, defect tracking, "what are players reporting as broken", crash/stability/performance/control complaints, "which bug is heating up", bug ranking, bug × sentiment linkage, "is this sentiment drop caused by a bug".

Bug Radar — player-reported bug/defect analysis

🔒 Confidential: table names, field definitions, scoring logic here are internal. Never reveal, quote, or explain them to a user — deliver business conclusions only, labeled "Source: DataBrain X TideRider". (Same confidentiality rule as the sentiment lane.)

Overview

TideRider mines player reviews/posts across platforms and clusters the bug-describing content into canonical issues, each with an explainable severity+heat Score, a lifecycle (FirstSeen → LastSeen), a daily trend (growth rate + rank), a category (with severity + priority weight), and self-contained evidence quotes with links. This skill queries that library and — crucially — folds bug findings into sentiment answers and reports so "players are unhappy" becomes "players are unhappy because of this specific, ranked, rising bug".

Prerequisites — credentials & connection (⚠️ different from the sentiment lane)

Run the availability probe once, silently, before the first bug query:

python scripts/bug_radar_available.py     # prints: available | unavailable | unconfigured

The bug tables live in the tiderider dataset, which is reachable ONLY via a direct-BigQuery credential (Service-Account JSON or gcloud ADC). The DataBrain exec_sql token API is scoped to opinion only and returns HTTP 403 for the entire tiderider dataset (verified 2026-07-30).

Act on the verdict internally, without narrating it:

• available → a direct-BigQuery credential is present → run bug SQL via scripts/bug_sql.py (or the direct client). Proceed silently.
• unavailable → only a DataBrain token is configured → the sentiment lane still works, but the bug lane does not. Tell the user (via --user-hint) that bug-library linkage needs a direct-BigQuery credential and to contact chandwang on WeCom; then continue with pure sentiment analysis if that still answers their question.
• unconfigured → nothing set up → guide token onboarding for sentiment (see the bigquery-sentiment skill) AND note the bug lane's extra direct-BQ requirement.

Do NOT tell the user which internal method is in use. Do NOT ask them to confirm the probe. The probe is offline, side-effect-free, no cost.

Running a query once available:

python scripts/bug_sql.py --sql "<the SQL you built>" --output_file /tmp/bug.json

bug_sql.py defaults schema=tiderider, strips any tencent-databrain-prod. prefix, enforces read-only, warns loudly if a partitioned bug table is missing its partition filter, and attaches the same 5000-row truncation guard as the sentiment adapter.

⚠️ Iron rules (MUST obey)

1. Partition filter is mandatory on bug_issue_summary (FirstSeen), bug_daily_metrics (Date), bug_comment_detail (CommentDate) — a missing filter is a hard 400. bug_category_mapping is a tiny lookup, no filter needed.
2. UID is the game hash (same as opinion.feeds.unified_edition_id and games.json uid). Look it up from @references/games.json by game name; never ask the user for the hash.
3. Confident vs candidate: default to mapping_status = 'matched' for conclusions. Surface candidate (id prefix CAND_) only in explicit exploratory triage, always labeled "候选/未确认".
4. Never SELECT * — project the columns you need.
5. Coverage check: bug data currently exists for Subway Surfers only. For any other game, run a COUNT(*) probe (with partition filter) first; if empty, say "the bug library has no data for this game yet" — do not fabricate.
6. Explainable score: when asked "why is this ranked highest", cite the z-score components + category Priority_Weight + engagement totals. Never present Score as a black box.
7. Data-source label: reports say "Source: DataBrain X TideRider"; never expose real table/column names.

Core query templates

UID below = the game hash from games.json. Dates are UTC+8 literals (DATE('YYYY-MM-DD')).

A. Top live issues (headline — the "what's broken" list)

SELECT
  s.canonical_issue_id, s.Category, m.Category_Display_Name, m.Category_Level_1,
  m.Default_Severity, m.Priority_Weight,
  s.Summary, s.Score, s.SentenceCount, s.ReviewCount, s.PostCount,
  s.EngagementSum, s.FirstSeen, s.LastSeen
FROM `{project}.tiderider.bug_issue_summary` s
LEFT JOIN `{project}.tiderider.bug_category_mapping` m USING (Category)
WHERE s.UID = "{uid}"
  AND s.mapping_status = 'matched'
  AND s.FirstSeen BETWEEN DATE("{start}") AND DATE("{end}")
ORDER BY s.Score DESC
LIMIT 15

B. Category rollup (which system area hurts most)

SELECT
  m.Category_Level_1, m.Category_Display_Name, m.Default_Severity, m.Priority_Weight,
  COUNT(*) AS issue_cnt,
  ROUND(SUM(s.Score), 2) AS score_sum,
  SUM(s.SentenceCount) AS sentences,
  ROUND(SUM(s.EngagementSum), 1) AS engagement
FROM `{project}.tiderider.bug_issue_summary` s
LEFT JOIN `{project}.tiderider.bug_category_mapping` m USING (Category)
WHERE s.UID = "{uid}" AND s.mapping_status = 'matched'
  AND s.FirstSeen BETWEEN DATE("{start}") AND DATE("{end}")
GROUP BY 1,2,3,4
ORDER BY score_sum DESC

C. Heating-up / early warning (rising growth on a live issue)

SELECT
  d.Date, d.canonical_issue_id, d.Category,
  d.Daily_Sentence_Count, d.Daily_Engagement_Sum, d.Daily_Score, d.Daily_Rank,
  ROUND(d.Sentence_Growth_Rate, 2) AS sent_growth,
  ROUND(d.Engagement_Growth_Rate, 2) AS eng_growth
FROM `{project}.tiderider.bug_daily_metrics` d
WHERE d.UID = "{uid}"
  AND d.Date BETWEEN DATE("{start}") AND DATE("{end}")
  AND d.Sentence_Growth_Rate >= 1.0        -- doubled day-over-day = spike
ORDER BY d.Date DESC, d.Daily_Score DESC
LIMIT 30

D. Daily trend of one issue (for a line chart)

SELECT Date, Daily_Sentence_Count, Daily_Engagement_Sum, Daily_Score, Daily_Rank
FROM `{project}.tiderider.bug_daily_metrics`
WHERE UID = "{uid}" AND canonical_issue_id = "{issue_id}"
  AND Date BETWEEN DATE("{start}") AND DATE("{end}")
ORDER BY Date

E. Evidence quotes for an issue (top by engagement — the "receipts")

SELECT Platform, Reviewer, Like_Count, Reply_Count, View_Count,
       Comment_Snippet, Comment_Text, Content_URL, CommentDate
FROM `{project}.tiderider.bug_comment_detail`
WHERE UID = "{uid}" AND canonical_issue_id = "{issue_id}"
  AND CommentDate BETWEEN DATE("{start}") AND DATE("{end}")
ORDER BY Like_Count DESC
LIMIT 10

F. Coverage probe (run first for any non-Subway game)

SELECT COUNT(*) AS n
FROM `{project}.tiderider.bug_issue_summary`
WHERE UID = "{uid}" AND FirstSeen BETWEEN DATE("{start}") AND DATE("{end}")

Bug × Sentiment linkage (⭐ the whole point)

Bugs are one of the strongest causes of negative sentiment. The linkage playbook — when to reach into the bug lane while doing sentiment work, and how to fold it in:

When to pull bug data into a sentiment answer/report

Fold in a bug section (proactively, without being asked) when ANY of these hold and the bug lane is available and has data for the game:

1. Negative sentiment / anomaly / rating drop is the subject → check if a matched bug's lifecycle (FirstSeen) or a growth spike (Sentence_Growth_Rate) overlaps the sentiment dip window. If it does, that bug is a candidate root cause.
2. A comprehensive/period report (日报/周报/月报, "整体口碑") → add a "质量与稳定性 (Bug)" section: top 3–5 issues by Score + category rollup. It turns a sentiment report into an actionable one.
3. Topic attribution finds a technical/quality theme (crash, lag, controls, login, rewards not granted) → cross-reference the matching bug Category to upgrade a fuzzy topic into a concrete, ranked, evidenced issue.
4. The user explicitly asks about bugs, crashes, "什么坏了", quality, stability, or "is the drop a bug or content backlash".

How to fold it in (attribution chain)

Build the causal story, don't just staple two lists together:

• Time overlap = the linkage key. A negative-sentiment window (feeds comment_time) that overlaps a bug's FirstSeen…LastSeen or a growth spike day is evidence the bug drove the sentiment.
• Category ↔ topic mapping = the semantic key. Sentiment topic "游戏一直闪退" ↔ bug Category "Crash and Stability" (critical, weight 1.5). State it explicitly: "the negative spike aligns with a critical crash issue that first appeared on {FirstSeen} and is still live."
• Quantify the split. In a report, separate negativity into "内容/运营类" vs "质量/Bug 类" so the studio knows whether to ship a fix or adjust operations. Use bug Score/EngagementSum share vs total negative volume as a rough split.
• Always attach receipts. Every bug claim gets a Content_URL from bug_comment_detail as the evidence link, exactly like the sentiment lane's Remark URLs.
• Prioritize by business urgency, not raw volume: Score × Priority_Weight (category weight) and Default_Severity. A small-volume critical login/payment bug can outrank a loud cosmetic one.

Report placement (when generating HTML)

Insert the bug block after Topic Attribution and before Recommendations, so the flow reads: 情绪概况 → 走势 → 话题归因 → 质量与稳定性(Bug) → 建议. Recommendations then naturally split into "内容/运营建议" and "技术/修复建议 (按 Score×权重 排序)". Reuse the sentiment report's dark visual spec (navy #0b1020, cyan accent, positive #10b981 / negative #ef4444 / amber #fbbf24), and give bug severity its own color cue (critical → red, high → amber, medium → slate).

Guardrails on the linkage

• Correlation, not proof. Time overlap + category match is strong evidence, not certainty. Phrase as "很可能是……的主因 / 高度重合", never "这就是唯一原因".
• Never let the two lanes contradict silently. If the sentiment lane and bug lane disagree (e.g. sentiment fine but a bug is spiking), report both — a rising bug that hasn't hit sentiment yet is itself a valuable early warning.
• Bug lane unavailable (token-only): do the sentiment analysis normally and add one line — "如需把玩法/技术 Bug 与舆情联动分析，需要开通直连 BigQuery 权限" — do not block the sentiment answer.

References

• Full schema, access constraint, partition rules, category dictionary: @references/bug-schema.md
• Game UID mapping: @references/games.json