---
name: investibook-usage-reporting
description: |
  Fetch and analyze Investibook usage/cost metrics from prod backend SQLite
  token usage plus Clinic Grafana DevOps Prometheus ACP metrics. Use for daily
  usage reports, weekly trend reports, team/user/workspace breakdowns, oncall
  adoption analysis, charts, and Feishu-ready summaries.
---

# Investibook Usage Reporting

Use this skill when the user asks for Investibook cost, ACP interactions, active
users, workspace trends, top users, team trends, oncall usage, charts, or weekly
reports.

## Primary Runbook

Read and follow:

```text
docs/guides/2026-07-03-investibook-usage-reporting.zh.md
```

Use the team override mapping:

```text
docs/guides/investibook-usage-team-overrides.csv
```

For reproducing the historical 2026-05-28 to 2026-06-25 report, use the active
usage-user mapping snapshot:

```text
docs/guides/investibook-usage-team-mapping-snapshot-20260528-20260625.csv
```

## Data Source

Grafana dashboard:

```text
https://clinic.pingcap.com/grafana/d/investibook-ops/investibook-yun-ying-kan-ban
```

Prometheus datasource:

```text
name: DevOps-prometheus
id: 80
query endpoint: https://clinic.pingcap.com/grafana/api/datasources/proxy/80/api/v1/query
```

Known production labels:

```text
cluster="prod-devops"
namespace="investibook"
job="backend"
prometheus="monitoring/o11y-vmagent"
```

State clearly that the current dashboard is prod-only unless the labels or
datasource prove otherwise.

## Authentication

Use the user's local Chrome Clinic login session via the existing Clinic Grafana
access scripts. First check login:

```bash
PYTHONPATH=/tmp/codex-pycookiecheat-20260630:agentkit/skills/nutshell-skills/platform/op-clinic-grafana-access/scripts \
/Users/lingtaoli/.cache/codex-runtimes/codex-primary-runtime/dependencies/python/bin/python3 \
agentkit/skills/nutshell-skills/platform/op-clinic-grafana-access/scripts/check_login.py \
--base-url https://clinic.pingcap.com --profile Default
```

If the session is expired, ask the user to login to Clinic in Google Chrome and
rerun the check. Never print cookies, tokens, or credential values.

## Time Windows

Use Asia/Shanghai for all business reporting.

- Daily: local day `00:00` to next day `00:00`.
- Weekly: explicit 7-day windows. If the user provides the previous report or
  dates, advance that same cadence; otherwise use the most recent complete
  Asia/Shanghai natural week used by the current report series, typically
  Monday-to-Sunday. The old Thursday-to-Thursday examples are historical only.
- Always include concrete start/end dates in the output.

## Cost And ACP Sources

Use prod backend SQLite `interaction_token_usage` as the source of truth for
cost, tokens, user/workspace/team cost rankings, and workspace-level cost rows.
The collector applies runtime-aware accounting:

- OpenCode `session_id` values beginning with `ses_` are cumulative session
  snapshots. Daily cost is `latest_before_day_end - latest_before_day_start`.
- PI UUID session rows and backend-owned rows such as `image-parse:*` are
  per-interaction increments. Daily cost is the sum of rows inside the day.

Use Prometheus only for ACP interaction counts and channel interaction
breakdowns. Do not use `investibook_acp_session_cost` for current mixed-runtime
weekly cost reporting; PI sessions can make that gauge undercount.
For live Grafana cost panels, use `investibook_acp_interaction_cost_total`,
which is emitted from the same runtime-aware DB contribution calculation. For
historical backfill and weekly report writing, keep using prod SQLite/daily JSON
instead of reconstructing cost from Prometheus.

## PromQL

Total ACP interactions:

```promql
sum(increase(investibook_acp_session_interactions_total[<range>]))
```

ACP interactions by user:

```promql
topk(1000, sum by (user_email) (increase(investibook_acp_session_interactions_total{user_email!="unknown"}[<range>])))
```

Interactions by user and channel:

```promql
topk(2000, sum by (user_email, channel) (increase(investibook_acp_session_interactions_total{user_email!="unknown"}[<range>])))
```

Live dashboard total cost:

```promql
sum(increase(investibook_acp_interaction_cost_total[<range>]))
```

Live dashboard cost by user:

```promql
topk(1000, sum by (user_email) (increase(investibook_acp_interaction_cost_total{user_email!="unknown"}[<range>])))
```

Live dashboard workspace-user-channel cost:

```promql
topk(10000, sum by (workspace_id, user_email, channel) (increase(investibook_acp_interaction_cost_total{workspace_id!="unknown",user_email!="unknown"}[<range>])))
```

Important: `investibook_acp_session_interactions_total` does not currently have
`workspace_id`. Workspace count must be derived from positive DB token-usage
cost rows and means distinct positive-cost workspaces.

## Team Mapping Rules

Always load the base member/team table first. This is the source that allows new
Investibook users to be mapped to teams automatically:

```text
/Users/lingtaoli/investibook/pingcap_members_by_team.csv
```

Normalize email addresses with lowercase and trim before matching. Match usage
metric `user_email` to the member CSV `email` exactly. Prefer rows with
`is_primary_dept=yes` and `status=active`; if multiple active primary rows still
exist, mark the user as ambiguous instead of guessing.

Then apply the long-lived override patch table:

```text
docs/guides/investibook-usage-team-overrides.csv
```

The base member CSV is the broader member/team table. The override file is a
long-lived patch table. The snapshot file is only the active users that appeared
in one historical usage report window after merging the base member CSV and
overrides; do not treat it as a live org directory or all-hands member table.

Before publishing team-level results:

1. List unknown users sorted by cost and interactions.
2. If any unknown or unmatched user exists, show the list to the human reviewer
   and ask whether to add rows to `investibook-usage-team-overrides.csv` before
   finalizing the daily JSON.
3. If unknown cost is more than 1% of total cost or more than `$5`, treat it as
   a hard gate: stop and ask for mapping instead of publishing team trends.
4. Mention any remaining human-approved low-impact unknown users in the report
   notes.

For every run, include an `unmatched_users` output with email, cost,
interactions, workspace count, and top workspace. This output is the queue for
adding new rows to the override file when the base member table is incomplete or
uses a different email alias.

## Required Outputs

For daily automation, write outputs under:

```text
outputs/usage_reports/YYYY-MM-DD/
```

For new chart/front-end consumption, prefer the split output directory:

```text
outputs/usage_daily_reports/YYYY-MM-DD/
```

Recommended files:

- `daily_summary.json`
- `daily_report.json`
- `workspace_changes.json`
- `chart_ready_usage.json`
- `daily_user_usage.csv`
- `daily_workspace_detail.csv`
- `weekly_team_trend.md`
- `weekly_user_histograms.md`
- `charts/`

## Daily Collector

Use the repository collector when the user asks to refresh real daily JSON:

```bash
PYTHONPATH=/tmp/codex-pycookiecheat-20260630 \
/Users/lingtaoli/.cache/codex-runtimes/codex-primary-runtime/dependencies/python/bin/python3 \
tools/usage_reporting/collect_daily_usage.py \
--start YYYY-MM-DD \
--end YYYY-MM-DD
```

For historical classification or DB-derived refreshes where Clinic/Grafana
queries are slow or unstable, preserve already-collected ACP interaction counts
from existing split daily JSON:

```bash
PYTHONPATH=/tmp/codex-pycookiecheat-20260630 \
/Users/lingtaoli/.cache/codex-runtimes/codex-primary-runtime/dependencies/python/bin/python3 \
tools/usage_reporting/collect_daily_usage.py \
--start YYYY-MM-DD \
--end YYYY-MM-DD \
--reuse-existing-prometheus
```

The collector writes:

- `outputs/usage_reports/YYYY-MM-DD/derived_daily_usage.json`
- `outputs/usage_reports/YYYY-MM-DD/daily_summary.json`
- `outputs/usage_reports/YYYY-MM-DD/chart_ready_usage.json`
- `outputs/usage_reports/YYYY-MM-DD/daily_workspace_detail.json`
- `outputs/usage_reports/daily_cost_acp_series.json`
- `outputs/usage_reports/workspace_usage_START_to_END.json`
- `outputs/usage_daily_reports/YYYY-MM-DD/daily_summary.json`
- `outputs/usage_daily_reports/YYYY-MM-DD/daily_report.json`
- `outputs/usage_daily_reports/YYYY-MM-DD/workspace_changes.json`
- `outputs/usage_daily_reports/YYYY-MM-DD/chart_ready_usage.json`
- `outputs/usage_daily_reports/daily_cost_acp_series.json`
- `outputs/usage_daily_reports/workspace_index.json`
- `outputs/usage_daily_reports/workspace_mode_classification.json`
- `outputs/usage_daily_reports/profile_spend_by_usedby.json`
- `outputs/usage_daily_reports/feedback_demands.json`

After every collector run, inspect all generated `daily_report.json` files for
`summary.unknown_user_count > 0`, non-empty `unmatched_users`, or any team row
with `(unknown)`. If any appear, do not silently submit the JSON. Summarize the
unknown emails with cost, ACP interactions, workspace count, and likely alias
candidates from `pingcap_members_by_team.csv`, then ask the human reviewer to
confirm the mapping or approve leaving them unknown. When the reviewer confirms
the mapping, update `docs/guides/investibook-usage-team-overrides.csv`, rerun the
collector for the affected dates, and only then sync `outputs/usage_daily_reports`
to `frontend/data/usage_daily_reports`.

It uses two production data paths:

1. Clinic Grafana / Prometheus datasource `DevOps-prometheus` id `80` for ACP
   interactions and user/channel interaction breakdown.
2. The prod backend pod SQLite database at `/data/db/investibook.db` for
   cost/tokens from `interaction_token_usage`, plus `messages_v2`, workspace
   titles, owners, and message counts.

Run `aws sso login --profile dbaas-prod-clinic-oncall` when the prod EKS
context reports an expired SSO session. For Clinic/Grafana, first open
`https://clinic.pingcap.com` in Google Chrome and then run the `check_login.py`
preflight from the Authentication section.

### Filled Derived Fields

The collector fills these derived fields beyond raw cost/ACP/message counts:

- `users[].frequency_bucket`, `users[].depth_bucket`,
  `users[].behavior_mode`, and `charts.user_behavior_quadrant`.
  - Daily frequency threshold is `max(2 workspaces, P60(workspace_count))`.
  - Daily depth threshold is `max(4 ACP/workspace, P60(avg_acp_per_ws))`.
- `message_categories`, `workspaces[].message_categories`, and
  `charts.message_category_stack`.
  - Classification is rule-based keyword matching over user messages only.
  - Apply `tools/usage_reporting/message_category_manual_overrides.json` before
    keyword matching for human-reviewed short or multilingual messages that
    cannot be safely covered by broad rules.
  - Categories follow the runbook priority order: query correction, diagnosis
    correction, artifact iteration, ad hoc query, diagnosis start,
    explanation follow-up, flow control, methodology/capability consultation,
    background context, uncategorized.
  - Treat standalone links, pasted SQL, error/log snippets, stack traces,
    screenshots, long pasted context, standalone case IDs, file/image IDs,
    labelled cluster/org/table IDs, and standalone time windows as `背景补充`
    unless the message also contains an explicit query, diagnosis, correction,
    or output-iteration intent.
  - Treat short follow-up commands such as translation requests, shortening,
    Markdown/table rewrites, answer drafts, and customer-response rewrites as
    `产出物迭代`; treat retry/new/continue commands as `流程控制/确认`.
  - For question-like messages that do not match fixed keywords, classify them
    as `Ad hoc 查询` when they mention evidence objects such as cluster, log,
    SQL, CPU, TiKV/TiDB, metrics, or Grafana; otherwise classify them as
    `解释追问`.
  - Before publishing regenerated JSON, sample real `未归类` messages from
    `messages_v2` and only add high-confidence rules. Do not force every
    message out of `未归类`; keep it as the final bucket for genuinely ambiguous
    fragments.
  - Treat `estimated_cost_by_turn` as a proxy: `total_cost *
    category_user_messages / total_user_messages`.

### Split Daily and Workspace JSON

Prefer the split directory for the front-end weekly report:

- `outputs/usage_daily_reports/YYYY-MM-DD/daily_summary.json`: compact daily
  status, totals, source, and window metadata.
- `outputs/usage_daily_reports/YYYY-MM-DD/daily_report.json`: day-level teams,
  users, message categories, top changed workspaces, unmatched users, and chart
  payloads. It references `workspace_changes.json` instead of embedding all
  workspace rows.
- `outputs/usage_daily_reports/YYYY-MM-DD/workspace_changes.json`: all
  workspaces that changed that day, split into `new_workspaces`,
  `updated_existing_workspaces`, and `all_changed_workspaces`.
- `outputs/usage_daily_reports/YYYY-MM-DD/chart_ready_usage.json`: only chart
  payloads for quick rendering.
- `outputs/usage_daily_reports/daily_cost_acp_series.json`: cross-day cost,
  ACP, workspace count, and active user time series, rebuilt by scanning
  existing daily summaries so running a single new day keeps previous days.
- `outputs/usage_daily_reports/workspace_index.json`: appendable index of daily
  workspace change files. Build workspace-window aggregates on demand from this
  index instead of committing fixed-window files such as
  `workspace_usage_2026-06-25_to_2026-07-06.json`.
- `outputs/usage_daily_reports/workspace_mode_classification.json`: derived
  workspace mode labels from activity signals. Current rule labels are
  `持续迭代`, `深度单次诊断`, `单次任务`, and `轻量尝试`.
- `outputs/usage_daily_reports/profile_spend_by_usedby.json`: Profile/UsedBy
  spend from Helix API key assignment spend detail
  `/api/v1/apikeys/assignments/{assignment_id}/spend/detail`.
- `outputs/usage_daily_reports/feedback_demands.json`: placeholder for curated
  demand/feedback items from Lark docs or meetings. Keep empty when demand
  analysis is out of scope.

Cost semantics:

- `daily_summary.summary.total_cost`, `users[].cost`, `teams[].cost`,
  `workspaces[].cost`, and `daily_delta.cost` must come from runtime-aware
  `interaction_token_usage` contributions.
- `token_usage.cost_amount` should match the same runtime-aware contribution
  used for `daily_delta.cost`.
- `investibook_acp_session_cost` is historical monitoring data only until the
  PI gauge semantics are repaired and verified.

If the front-end embeds static report data, mirror the generated split tree to:

```text
frontend/data/usage_daily_reports/
```

Keep `outputs/usage_daily_reports/` as the canonical generated source, and copy
it mechanically to the front-end data directory after generation. Do not edit
the mirrored JSON by hand.

Daily semantics:

- A workspace is `new` when its prod DB `workspaces.created_at` falls inside the
  local report day.
- A workspace is `updated_existing` when it was created before the local report
  day but has cost, token usage, or message activity in that day.
- Each daily report should therefore include both today's newly created
  workspaces and previously created workspaces whose message/token/cost activity
  changed that day.

Supplemental semantics:

- `workspace_mode_classification.json` is safe to regenerate from daily files.
- Scenario fields are embedded directly in each `workspace_changes.json` row:
  `scenario_group`, `scenario_category`, `scenario_confidence`,
  `scenario_classification_method`, `scenario_secondary_categories`, and
  `scenario_evidence`. Current labels are conservative rule-based
  classifications from workspace title, user/system message context, and
  message category signals. Use `scenario_evidence.review_required` for
  human-review queues. Treat Lark as an entry channel, not a standalone product
  usage category. Do not emit `multi_topic_diagnostic_workbench` or
  `unknown_or_mixed` as raw `scenario_category` values: multi-topic workspaces
  should increment every matched label in `scenario_category_counts`, while
  unknown workspaces should use `scenario_group="unknown"` and
  `scenario_category=null`.
- `profile_spend_by_usedby.json` is independently sourced from Helix. If the
  local Chrome Helix session is unavailable, the collector preserves the
  existing file rather than replacing real data with an empty placeholder.
- `feedback_demands.json` is an explicit contract file even when empty, so
  front-end/report code can distinguish "not needed" from "file missing".

### Workspace-Level JSON

Use `outputs/usage_daily_reports/workspace_index.json` plus the referenced
daily `workspace_changes.json` files as the primary source for
workspace-centric charts and follow-up analysis. This keeps the repository
appendable: adding a new day adds a new dated directory and refreshes the index,
without replacing a fixed-window aggregate file.

When a chart needs a workspace window aggregate, build it at render/build time
from the indexed daily change files and preserve `daily[]` breakdowns per
workspace in memory or in a generated, non-source artifact.

Workspace-level fields include:

- identity: `workspace_id`, `title`, `url`
- attribution: `user_emails`, `primary_user_email`, `team_paths`, `teams`,
  `channels`
- activity: `first_active_date`, `last_active_date`, `active_dates`,
  `active_days`
- totals: `total_cost`, `message_count`, `user_message_count`,
  `assistant_message_count`, `token_usage`
- behavior proxies: `message_categories`, `cost_per_user_message`
- review slots: `is_inspection`, `quality_notes`
- `daily[]`: per-day user/channel/cost/message/category/token detail

Prefer this workspace-level JSON when building:

- top workspace cost charts
- message-depth scatter plots
- workspace quality review queues
- future scenario classification inputs
- day/team rollups that need to avoid double-counting message categories

### Historical Feishu Backfill

The published Feishu weekly report can be preserved as a chart fixture under:

```text
outputs/usage_reports/2026-06-25/weekly_published_report_backfill.json
```

This file may contain weekly `diagnostic_scenarios`,
`non_diagnostic_scenarios`, and `charts.scenario_distribution` extracted from
the Lark document. Do not spread these weekly aggregate counts back onto daily
workspace rows unless a workspace-level source table is available.

### Remaining Data Gaps / Follow-ups

- `workspaces[].scenario_category`: filled by the daily collector using a
  conservative rule pass over workspace title, user/system message context, and
  message category signals. Treat it as an initial chart label, not a human-reviewed
  diagnosis taxonomy. Current categories include SQL/query plan, DDL/schema
  change, session/process management, lock/txn, TiKV hotspot/coprocessor,
  storage/IO, backup/restore, data import/load, memory/OOM, GC/allocator,
  resource control/capacity, PD/TSO/scheduling, CDC/replication,
  network/connectivity, component bug/failure, topology or scaling,
  inspection/report generation, skill/workflow setup, eval/regression,
  product/admin usage, ticket/customer response, fleet inventory/capacity
  analysis, and support-case triage.
- `workspaces[].quality_notes`: requires top workspace chat review or an LLM
  quality pass; do not fabricate.
- `workspaces[].is_inspection`: can be filled by a conservative rule pass over
  title/source/message text, but is not enabled until the rule list is reviewed.
- `workspaces[].acp_interactions`: exact workspace-level ACP is unavailable
  because `investibook_acp_session_interactions_total` has no `workspace_id`
  label. Use token usage records or user message turns only as a clearly marked
  proxy.
- Profile / UsedBy spend (`aiops`, `investibook-dev`, `clinic`, etc.) is pulled
  from Helix API key assignment spend detail when the local Chrome Helix session
  can refresh auth.
- Eval score JSON is out of scope for this usage collector and should come from
  eval/audit outputs.

### PR / Upload Checklist

When publishing refreshed usage data, include the generation contract alongside
the JSON data:

- `tools/usage_reporting/collect_daily_usage.py`
- `tools/usage_reporting/message_category_manual_overrides.json`
- `agentkit/skills/builtin/investibook-usage-reporting/SKILL.md`
- `docs/guides/2026-07-03-investibook-usage-reporting.zh.md`
- `outputs/usage_daily_reports/`
- `frontend/data/usage_daily_reports/` when the front-end reads static data

Before opening or updating a PR, verify:

```bash
python3 -m py_compile tools/usage_reporting/collect_daily_usage.py
python3 - <<'PY'
import json
from pathlib import Path
for path in Path("outputs/usage_daily_reports").rglob("*.json"):
    json.loads(path.read_text())
PY
```

Also check that `workspace_index.json`, `daily_cost_acp_series.json`, and the
front-end mirror cover the same date range.

## Weekly Report Shape

Produce Feishu-ready report sections in the current report format:

1. `本周总结`: totals plus explicit comparison to the previous 1-2 report
   windows. Mention internal batch-noise users only after calculating impact.
2. `数据口径`: spell out daily-summary vs workspace-window aggregation, timezone,
   workspace de-duplication, and multi-label scenario counting.
3. `开发成本统计`: use `profile_spend_by_usedby.json` as Helix month-to-date
   Profile/UsedBy spend, not as the weekly usage collector cost.
4. `日使用趋势`: compare with prior weeks, then show daily cost, ACP, active
   users, workspace rows, and user messages.
5. `用户画像统计`: include top teams and top users; summarize which individuals
   drive each high-usage team.
6. `行为模式分析`: include message-category distribution and a single workspace
   mode trend table with per-mode weekly `avg user msg/WS` and total cost.
7. `Workspace 分析`: top workspace table with links, followed by detailed
   top-5 analysis. Read actual `messages_v2` and `interaction_token_usage`
   before explaining problem description, cost reason, and whether Investibook
   helped the user reach the desired result.
8. `本周场景分布` and `非诊断使用`: use multi-label counts for scenario bars, and
   primary `scenario_group` for diagnosis/non-diagnosis/unknown shares.
9. `覆盖率分析`: include new TCOC/ST/TCID coverage numbers only when this run has
   fresh coverage JSON or a reliable current chart source. Otherwise say it was
   not recollected; do not reuse old screenshots or old numbers.

Prefer distributions over binary labels such as loyal/non-loyal. Use language
like stable multi-week usage, single-week spike, light trial, or multi-case
diffusion only after checking the distribution.

## Quality Gates

Do not finalize a report until these are checked:

- Timezone and concrete start/end dates are explicit.
- Datasource is `DevOps-prometheus` id `80`.
- Current metric labels still indicate prod.
- Cost comes from prod SQLite `interaction_token_usage` with runtime-aware
  OpenCode cumulative delta and PI per-row increment semantics.
- Team mapping unknown impact is below threshold.
- Workspace count is described as cost-derived.
- ACP interactions are not conflated with frontend message count.
- Unknown team/user rows have been reviewed with the human reviewer before
  publishing JSON or team charts.
- Top workspace details are based on real chat/token records, not only titles.
- Feishu outline is fetched after writing to confirm all main sections exist.