insights.py 37 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610
  1. """Session Insights Engine: aggregates the SQLite state DB into usage insights (tokens, cost estimates, tool/skill
  2. usage, activity, model/platform breakdowns). ``InsightsEngine(db).generate(days=30)`` → ``format_terminal(report)``."""
  3. import json
  4. import sqlite3
  5. import time
  6. from collections import Counter, defaultdict
  7. from datetime import datetime
  8. from decimal import Decimal
  9. from typing import Any, Dict, List, Optional
  10. from agent.usage_pricing import CanonicalUsage, estimate_usage_cost, format_cost_label, format_duration_compact, has_known_pricing
  11. from hermes_cli.timefmt import coerce_epoch
  12. from hermes_time import safe_strftime
  13. import itertools
  14. _TOKEN_KEYS = ("input_tokens", "output_tokens", "cache_read_tokens", "cache_write_tokens")
  15. _SKILL_TOOLS = {"skill_view", "skill_manage"}
  16. def _fmt_est_cost(est_cost: float) -> str:
  17. """Shared label helper so sub-cent totals render at 4dp, not "~$0.00".
  18. Routes through ``format_cost_label`` so sub-cent aggregates render at 4dp instead of collapsing to
  19. "~$0.00" (#79220 bug class — the same dishonesty this module's cost buckets exist to fix, #77223).
  20. """
  21. return format_cost_label(Decimal(str(est_cost)))
  22. def _estimate_cost(session_or_model: dict[str, Any] | str, input_tokens: int = 0, output_tokens: int = 0, *, cache_read_tokens: int = 0,
  23. cache_write_tokens: int = 0, provider: Optional[str] = None, base_url: Optional[str] = None) -> tuple[float, str]:
  24. """Estimate the USD cost for a session row or a model/token tuple."""
  25. if isinstance(session_or_model, dict):
  26. s = session_or_model
  27. model = s.get("model") or ""
  28. usage = CanonicalUsage(**{k: s.get(k) or 0 for k in _TOKEN_KEYS})
  29. provider, base_url = s.get("billing_provider"), s.get("billing_base_url")
  30. else:
  31. model = session_or_model or ""
  32. usage = CanonicalUsage(input_tokens, output_tokens, cache_read_tokens, cache_write_tokens)
  33. result = estimate_usage_cost(model, usage, provider=provider, base_url=base_url)
  34. return float(result.amount_usd or 0.0), result.status
  35. def _bar_chart(values: list[int], max_width: int = 20) -> list[str]:
  36. peak = max(values) if values else 1
  37. return ["" for _ in values] if peak == 0 else ["█" * max(1, int(v / peak * max_width)) if v > 0 else "" for v in values]
  38. def _short_model(model: Optional[str]) -> str:
  39. """Display name: strip the provider prefix; empty → "unknown"."""
  40. return (model or "unknown").split("/")[-1]
  41. def _parse_json(raw: Any, kind: type) -> Any:
  42. """JSON-decode *raw* when it is a string; the value if it is a *kind*, else None."""
  43. try:
  44. if isinstance(raw, str):
  45. raw = json.loads(raw)
  46. except (json.JSONDecodeError, TypeError):
  47. return None
  48. return raw if isinstance(raw, kind) else None
  49. def _iter_functions(raw_calls: Any):
  50. """Yield the ``function`` dict of every well-formed entry in a tool_calls column."""
  51. for call in _parse_json(raw_calls, list) or []:
  52. if isinstance(call, dict):
  53. yield call.get("function", {})
  54. def _hour12(hr: int) -> str:
  55. return f"{hr % 12 or 12}{'AM' if hr < 12 else 'PM'}"
  56. def _day(ts: Any) -> str:
  57. return safe_strftime(datetime.fromtimestamp(ts), "%b %d") if ts and (ts := coerce_epoch(ts)) else "?"
  58. # A source filter also matches the gateway's ``local`` rows that surface created: interactive local
  59. # sessions are stored as ``local`` with the surface kept in ``created_source``
  60. # (gateway/session_a2a.storage_source), so ``--source cli`` still finds the classic CLI's sessions.
  61. _LOCAL_SURFACE = " OR ({a}source = 'local' AND {a}created_source = ?)"
  62. def _scoped(before: str, after: str = "", *, alias: str = "s.") -> tuple[str, str]:
  63. """(unfiltered, source-filtered) query pair sharing one body. Built once at class definition,
  64. so no runtime value can alter query structure."""
  65. src = f" AND ({alias}source = ?{_LOCAL_SURFACE.format(a=alias)})"
  66. return before + after, before + src + after
  67. class InsightsEngine:
  68. """Analyzes session history from a SessionDB (or raw sqlite3 connection)."""
  69. _SESSION_COLS = ("id, source, model, started_at, ended_at, "
  70. "message_count, tool_call_count, input_tokens, output_tokens, "
  71. "cache_read_tokens, cache_write_tokens, billing_provider, "
  72. "billing_base_url, billing_mode, estimated_cost_usd, "
  73. "actual_cost_usd, cost_status, cost_source, api_call_count")
  74. _GET_SESSIONS_ALL, _GET_SESSIONS_WITH_SOURCE = _scoped(
  75. f"SELECT {_SESSION_COLS} FROM sessions WHERE started_at >= ?",
  76. " ORDER BY started_at DESC",
  77. alias="",
  78. )
  79. # ``INDEXED BY`` pins the partial index so the plan is deterministic on a
  80. # fresh state.db (before ANALYZE) for both branches; without it the
  81. # source-filtered probe falls back to idx_messages_session_active and scans
  82. # each session's non-tool-call rows. The pin is a HARD dependency (SQLite
  83. # raises ``no such index``): read-only opens skip ``_init_schema``, so an
  84. # older writer's DB may lack it — ``__init__`` probes once and falls back
  85. # to the unpinned variants (identical rows, optimizer-chosen plan).
  86. _MESSAGES_ASSISTANT_CALLS_INDEX = "idx_messages_assistant_calls_by_session"
  87. _ASSISTANT_CALLS = (
  88. f" FROM messages m INDEXED BY {_MESSAGES_ASSISTANT_CALLS_INDEX}"
  89. " JOIN sessions s ON s.id = m.session_id"
  90. " WHERE s.started_at >= ?"
  91. )
  92. _GET_TOOL_CALLS_ALL, _GET_TOOL_CALLS_WITH_SOURCE = _scoped(
  93. "SELECT m.session_id, m.tool_calls" + _ASSISTANT_CALLS,
  94. " AND m.role = 'assistant' AND m.tool_calls IS NOT NULL",
  95. )
  96. _GET_SKILL_CALLS_ALL, _GET_SKILL_CALLS_WITH_SOURCE = _scoped(
  97. "SELECT m.tool_calls, m.timestamp" + _ASSISTANT_CALLS,
  98. " AND m.role = 'assistant' AND m.tool_calls IS NOT NULL"
  99. " AND (instr(m.tool_calls, 'skill_view') > 0"
  100. " OR instr(m.tool_calls, 'skill_manage') > 0)",
  101. )
  102. _GET_TOOL_NAMES_ALL, _GET_TOOL_NAMES_WITH_SOURCE = _scoped(
  103. """SELECT m.session_id, m.tool_name, COUNT(*) as count
  104. FROM messages m
  105. JOIN sessions s ON s.id = m.session_id
  106. WHERE s.started_at >= ?""",
  107. """
  108. AND m.role = 'tool' AND m.tool_name IS NOT NULL
  109. GROUP BY m.session_id, m.tool_name""",
  110. )
  111. _GET_MESSAGE_STATS_ALL, _GET_MESSAGE_STATS_WITH_SOURCE = _scoped(
  112. """SELECT
  113. COUNT(*) as total_messages,
  114. SUM(CASE WHEN m.role = 'user' THEN 1 ELSE 0 END) as user_messages,
  115. SUM(CASE WHEN m.role = 'assistant' THEN 1 ELSE 0 END) as assistant_messages,
  116. SUM(CASE WHEN m.role = 'tool' THEN 1 ELSE 0 END) as tool_messages
  117. FROM messages m
  118. JOIN sessions s ON s.id = m.session_id
  119. WHERE s.started_at >= ?""",
  120. )
  121. _GET_MODEL_USAGE_ALL, _GET_MODEL_USAGE_WITH_SOURCE = _scoped(
  122. "SELECT u.session_id, u.model, u.billing_provider, u.billing_base_url,"
  123. " u.api_call_count, u.input_tokens, u.output_tokens,"
  124. " u.cache_read_tokens, u.cache_write_tokens, u.reasoning_tokens,"
  125. " u.estimated_cost_usd, u.actual_cost_usd, u.cost_status,"
  126. " u.cost_source, u.billing_mode"
  127. " FROM session_model_usage u"
  128. " JOIN sessions s ON s.id = u.session_id"
  129. " WHERE s.started_at >= ?",
  130. )
  131. _PINNED = ("_GET_TOOL_CALLS", "_GET_SKILL_CALLS")
  132. _SOURCE_SCOPED = ("_GET_SESSIONS", "_GET_TOOL_CALLS", "_GET_SKILL_CALLS", "_GET_TOOL_NAMES",
  133. "_GET_MESSAGE_STATS", "_GET_MODEL_USAGE")
  134. def __init__(self, db):
  135. self.db = db
  136. self._conn = db._conn
  137. try:
  138. self._has_assistant_calls_index = bool(self._conn.execute(
  139. "SELECT 1 FROM sqlite_master WHERE type='index' AND name=?", (self._MESSAGES_ASSISTANT_CALLS_INDEX,)).fetchone())
  140. except sqlite3.Error:
  141. self._has_assistant_calls_index = False
  142. if not self._has_assistant_calls_index:
  143. strip = f" INDEXED BY {self._MESSAGES_ASSISTANT_CALLS_INDEX}"
  144. for base in self._PINNED:
  145. for suffix in ("_ALL", "_WITH_SOURCE"):
  146. setattr(self, base + suffix, getattr(self, base + suffix).replace(strip, ""))
  147. # Same read-only caveat: an older writer's DB has no ``created_source`` (and no ``local`` rows).
  148. try:
  149. self._local_surfaces = any(row[1] == "created_source" for row in self._conn.execute("PRAGMA table_info(sessions)"))
  150. except sqlite3.Error:
  151. self._local_surfaces = False
  152. if not self._local_surfaces:
  153. for base in self._SOURCE_SCOPED:
  154. sql = getattr(self, base + "_WITH_SOURCE")
  155. for alias in ("", "s."):
  156. sql = sql.replace(_LOCAL_SURFACE.format(a=alias), "")
  157. setattr(self, base + "_WITH_SOURCE", sql)
  158. def _query(self, base: str, cutoff: float, source: Optional[str]) -> list:
  159. """Rows of ``<base>_WITH_SOURCE`` or ``<base>_ALL`` (instance attrs, so the unpinned fallback applies)."""
  160. if not source:
  161. return self._conn.execute(getattr(self, base + "_ALL"), (cutoff,)).fetchall()
  162. params = (cutoff, source, source) if self._local_surfaces else (cutoff, source)
  163. return self._conn.execute(getattr(self, base + "_WITH_SOURCE"), params).fetchall()
  164. def generate(self, days: int = 30, source: str | None = None) -> dict[str, Any]:
  165. """Generate a complete insights report for the last ``days`` days, optionally filtered by source platform."""
  166. cutoff = time.time() - (days * 86400)
  167. # Drain the SessionDB's async accounting queue so counters are exact
  168. # (self.db may be a raw sqlite3 connection in tests — guard).
  169. flush = getattr(self.db, "flush_token_counts", None)
  170. if callable(flush):
  171. flush()
  172. sessions = self._get_sessions(cutoff, source)
  173. tool_usage = self._get_tool_usage(cutoff, source)
  174. skill_usage = self._get_skill_usage(cutoff, source)
  175. message_stats = self._get_message_stats(cutoff, source)
  176. if not sessions:
  177. return {"days": days, "source_filter": source, "empty": True, "overview": {}, "models": [], "platforms": [], "tools": [],
  178. "skills": self._compute_skill_breakdown([]), "activity": {}, "top_sessions": []}
  179. models = self._compute_model_breakdown(sessions, cutoff, source)
  180. return {
  181. "days": days, "source_filter": source, "empty": False, "generated_at": time.time(),
  182. "overview": self._compute_overview(sessions, message_stats, models),
  183. "models": models,
  184. "platforms": self._compute_platform_breakdown(sessions),
  185. "tools": self._compute_tool_breakdown(tool_usage),
  186. "skills": self._compute_skill_breakdown(skill_usage),
  187. "activity": self._compute_activity_patterns(sessions),
  188. "top_sessions": self._compute_top_sessions(sessions),
  189. }
  190. def get_usage_breakdown(self, days: int = 30, source: str | None = None) -> dict[str, Any]:
  191. """Analytics-usage payload (tools + skills) without a full generate(); the
  192. instr()-prefiltered skill query loads only skill_view/skill_manage messages."""
  193. cutoff = time.time() - (days * 86400)
  194. return {"tools": self._compute_tool_breakdown(self._get_tool_usage(cutoff, source)),
  195. "skills": self._compute_skill_breakdown(self._get_skill_usage(cutoff, source))}
  196. # ------------------------------------------------------------------ SQL
  197. def _get_sessions(self, cutoff: float, source: str | None = None) -> list[dict]:
  198. # Coerce the two epoch columns once at load: one corrupt/TEXT cell must degrade to "unknown"
  199. # for that session, never abort the whole report (#99959).
  200. rows = [dict(row) for row in self._query("_GET_SESSIONS", cutoff, source)]
  201. for row in rows:
  202. for col in ("started_at", "ended_at"):
  203. row[col] = coerce_epoch(row.get(col), session_id=row.get("id"), field=col)
  204. return rows
  205. def _get_tool_usage(self, cutoff: float, source: str | None = None) -> list[dict]:
  206. """Tool call counts from two sources: ``tool_name`` on 'tool' rows (set
  207. by the gateway) and ``tool_calls`` JSON on assistant rows (covers CLI,
  208. where tool_name is not populated). The two views are reconciled PER
  209. SESSION (max — they describe the same calls), then summed across
  210. sessions: a global max dropped every call from a session that only
  211. carried the other representation (#9814)."""
  212. by_session_tool = Counter()
  213. for row in self._query("_GET_TOOL_NAMES", cutoff, source):
  214. by_session_tool[(row["session_id"], row["tool_name"])] += row["count"]
  215. calls_by_session_tool = Counter()
  216. for row in self._query("_GET_TOOL_CALLS", cutoff, source):
  217. try:
  218. names = filter(None, (fn.get("name") for fn in _iter_functions(row["tool_calls"])))
  219. calls_by_session_tool.update((row["session_id"], name) for name in names)
  220. except (TypeError, AttributeError):
  221. continue
  222. tool_counts = Counter()
  223. for key in set(by_session_tool) | set(calls_by_session_tool):
  224. tool_counts[key[1]] += max(by_session_tool.get(key, 0), calls_by_session_tool.get(key, 0))
  225. return [{"tool_name": name, "count": count} for name, count in tool_counts.most_common()]
  226. def _get_skill_usage(self, cutoff: float, source: str | None = None) -> list[dict]:
  227. """Extract per-skill usage from assistant tool calls."""
  228. skill_counts: dict[str, dict[str, Any]] = {}
  229. for row in self._query("_GET_SKILL_CALLS", cutoff, source):
  230. timestamp = row["timestamp"]
  231. for func in _iter_functions(row["tool_calls"]):
  232. tool_name = func.get("name")
  233. if tool_name not in _SKILL_TOOLS:
  234. continue
  235. skill_name = (_parse_json(func.get("arguments"), dict) or {}).get("name")
  236. if not isinstance(skill_name, str) or not skill_name.strip():
  237. continue
  238. entry = skill_counts.setdefault(skill_name, {"skill": skill_name, "view_count": 0, "manage_count": 0, "last_used_at": None})
  239. entry["view_count" if tool_name == "skill_view" else "manage_count"] += 1
  240. if timestamp is not None and (entry["last_used_at"] is None or timestamp > entry["last_used_at"]):
  241. entry["last_used_at"] = timestamp
  242. return list(skill_counts.values())
  243. def _get_message_stats(self, cutoff: float, source: str | None = None) -> dict:
  244. rows = self._query("_GET_MESSAGE_STATS", cutoff, source)
  245. return dict(rows[0]) if rows else {"total_messages": 0, "user_messages": 0, "assistant_messages": 0, "tool_messages": 0}
  246. def _get_model_usage(self, cutoff: float, source: str | None = None) -> list[dict]:
  247. """Per-model usage rows; [] when the table is missing (older DB) so the caller falls back to the per-session aggregate."""
  248. try:
  249. return [dict(row) for row in self._query("_GET_MODEL_USAGE", cutoff, source)]
  250. except sqlite3.OperationalError:
  251. return []
  252. # -------------------------------------------------------------- Compute
  253. def _compute_overview(self, sessions: list[dict], message_stats: dict, models: Optional[list[dict]] = None) -> dict:
  254. # Per-model breakdown includes auxiliary usage rows (vision/compression/
  255. # titles) plus reconciled residuals, while session counters carry
  256. # main-loop usage only — sum the breakdown when available so overview
  257. # totals match the per-model table and aux spend isn't undercounted.
  258. rows = models or sessions
  259. total_input, total_output, total_cache_read, total_cache_write = (sum(int(r.get(k) or 0) for r in rows) for k in _TOKEN_KEYS)
  260. total_tokens = total_input + total_output + total_cache_read + total_cache_write
  261. total_tool_calls = sum(s.get("tool_call_count") or 0 for s in sessions)
  262. total_messages = sum(s.get("message_count") or 0 for s in sessions)
  263. total_cost = actual_cost = 0.0
  264. models_with_pricing, models_without_pricing, status_counts = set(), set(), Counter()
  265. for s in sessions:
  266. model = s.get("model") or ""
  267. estimated, status = _estimate_cost(s)
  268. total_cost += estimated
  269. actual_cost += s.get("actual_cost_usd") or 0.0
  270. status_counts[status] += 1
  271. known = has_known_pricing(model, s.get("billing_provider"), s.get("billing_base_url"))
  272. (models_with_pricing if known else models_without_pricing).add(_short_model(model))
  273. if models:
  274. total_cost = sum(float(m.get("cost") or 0.0) for m in models)
  275. # Guard against negative durations from clock drift.
  276. durations = [s["ended_at"] - s["started_at"] for s in sessions
  277. if s.get("started_at") and s.get("ended_at") and s["ended_at"] > s["started_at"]]
  278. started = [s["started_at"] for s in sessions if s.get("started_at")]
  279. n = len(sessions)
  280. return {
  281. "total_sessions": n, "total_messages": total_messages, "total_tool_calls": total_tool_calls,
  282. "total_input_tokens": total_input, "total_output_tokens": total_output,
  283. "total_cache_read_tokens": total_cache_read, "total_cache_write_tokens": total_cache_write,
  284. "total_tokens": total_tokens, "estimated_cost": total_cost, "actual_cost": actual_cost,
  285. "total_hours": sum(durations) / 3600 if durations else 0,
  286. "avg_session_duration": sum(durations) / len(durations) if durations else 0,
  287. "avg_messages_per_session": total_messages / n if sessions else 0,
  288. "avg_tokens_per_session": total_tokens / n if sessions else 0,
  289. "user_messages": message_stats.get("user_messages") or 0,
  290. "assistant_messages": message_stats.get("assistant_messages") or 0,
  291. "tool_messages": message_stats.get("tool_messages") or 0,
  292. "date_range_start": min(started) if started else None,
  293. "date_range_end": max(started) if started else None,
  294. "models_with_pricing": sorted(models_with_pricing),
  295. "models_without_pricing": sorted(models_without_pricing),
  296. "unknown_cost_sessions": status_counts["unknown"],
  297. "included_cost_sessions": status_counts["included"],
  298. }
  299. def _compute_model_breakdown(self, sessions: list[dict], cutoff: float, source: str | None = None) -> list[dict]:
  300. """Tokens/cost per model from session_model_usage, so a session that
  301. switched models via ``/model`` splits across every model it used.
  302. Sessions without per-model rows (pre-table data) fall back to their
  303. single recorded aggregate. Tool calls aren't tied to an API call, so
  304. they stay attributed to the session's recorded model."""
  305. count_keys = _TOKEN_KEYS + ("reasoning_tokens", "api_call_count")
  306. model_data = defaultdict(lambda: {"sessions": set(), **dict.fromkeys(_TOKEN_KEYS, 0), "reasoning_tokens": 0, "total_tokens": 0,
  307. "api_calls": 0, "tool_calls": 0, "cost": 0.0, "actual_cost": 0.0})
  308. def _accumulate(model, provider, base_url, session_id, counts: dict[str, int], *,
  309. stored_cost=None, actual_cost=None, cost_status=None):
  310. model = model or "unknown"
  311. d: dict[str, Any] = model_data[_short_model(model)]
  312. d["sessions"].add(session_id)
  313. for key in _TOKEN_KEYS + ("reasoning_tokens",):
  314. d[key] += counts[key]
  315. d["total_tokens"] += sum(counts[k] for k in _TOKEN_KEYS)
  316. d["api_calls"] += counts["api_call_count"]
  317. if stored_cost is None:
  318. estimate, status = _estimate_cost(model, counts["input_tokens"], counts["output_tokens"], cache_read_tokens=counts["cache_read_tokens"],
  319. cache_write_tokens=counts["cache_write_tokens"], provider=provider or None, base_url=base_url)
  320. else:
  321. estimate, status = float(stored_cost or 0.0), cost_status or "unknown"
  322. d["cost"] += estimate
  323. d["actual_cost"] += float(actual_cost or 0.0)
  324. d["cost_status"] = status
  325. d["has_pricing"] = has_known_pricing(model, provider or None, base_url) or d.get("has_pricing", False)
  326. usage_totals = defaultdict(lambda: dict.fromkeys(count_keys, 0) | {"estimated_cost_usd": 0.0, "actual_cost_usd": 0.0})
  327. for r in self._get_model_usage(cutoff, source):
  328. totals: dict[str, Any] = usage_totals[r["session_id"]]
  329. counts = {key: r[key] or 0 for key in count_keys}
  330. for key in count_keys:
  331. totals[key] += counts[key]
  332. totals["estimated_cost_usd"] += r["estimated_cost_usd"] or 0.0
  333. totals["actual_cost_usd"] += r["actual_cost_usd"] or 0.0
  334. _accumulate(r["model"], r["billing_provider"], r.get("billing_base_url"), r["session_id"], counts,
  335. stored_cost=r["estimated_cost_usd"] if r.get("cost_status") or r.get("cost_source") else None,
  336. actual_cost=r["actual_cost_usd"], cost_status=r.get("cost_status"))
  337. # Reconcile against the aggregate row: covers legacy sessions,
  338. # interrupted migrations, and absolute cumulative updates without
  339. # double-counting already-attributed route deltas.
  340. for s in sessions:
  341. totals = usage_totals[s["id"]]
  342. residual = {k: max(0, (s.get(k) or 0) - totals[k]) for k in _TOKEN_KEYS + ("api_call_count",)}
  343. residual["reasoning_tokens"] = 0
  344. residual_cost = max(0.0, float(s.get("estimated_cost_usd") or 0.0) - totals["estimated_cost_usd"])
  345. residual_actual = max(0.0, float(s.get("actual_cost_usd") or 0.0) - totals["actual_cost_usd"])
  346. if any(residual.values()) or residual_cost or residual_actual:
  347. _accumulate(s.get("model"), s.get("billing_provider"), s.get("billing_base_url"), s["id"], residual,
  348. stored_cost=residual_cost, actual_cost=residual_actual, cost_status=s.get("cost_status"))
  349. for s in sessions:
  350. if s.get("tool_call_count"):
  351. model_data[_short_model(s.get("model"))]["tool_calls"] += s["tool_call_count"]
  352. # Models seen only via tool-call attribution never hit _accumulate —
  353. # default has_pricing/cost_status so the output shape is uniform for JSON consumers.
  354. defaults = (("has_pricing", False), ("cost_status", "unknown"))
  355. result = [{"model": model, **data, "sessions": len(data["sessions"]), **{k: v for k, v in defaults if k not in data}}
  356. for model, data in model_data.items()]
  357. return sorted(result, key=lambda x: (x["total_tokens"], x["sessions"]), reverse=True)
  358. def _compute_platform_breakdown(self, sessions: list[dict]) -> list[dict]:
  359. platform_data = defaultdict(lambda: {"sessions": 0, "messages": 0, **dict.fromkeys(_TOKEN_KEYS, 0), "total_tokens": 0, "tool_calls": 0})
  360. for s in sessions:
  361. d = platform_data[s.get("source") or "unknown"]
  362. d["sessions"] += 1
  363. d["messages"] += s.get("message_count") or 0
  364. for k in _TOKEN_KEYS:
  365. d[k] += s.get(k) or 0
  366. d["total_tokens"] += s.get(k) or 0
  367. d["tool_calls"] += s.get("tool_call_count") or 0
  368. return sorted(({"platform": platform, **data} for platform, data in platform_data.items()), key=lambda x: x["sessions"], reverse=True)
  369. def _compute_tool_breakdown(self, tool_usage: list[dict]) -> list[dict]:
  370. """Ranked tool list with percentages."""
  371. total_calls = sum(t["count"] for t in tool_usage)
  372. return [{"tool": t["tool_name"], "count": t["count"], "percentage": (t["count"] / total_calls * 100) if total_calls else 0} for t in tool_usage]
  373. def _compute_skill_breakdown(self, skill_usage: list[dict]) -> dict[str, Any]:
  374. """Per-skill usage → summary + ranked list."""
  375. total_skill_loads = sum(s["view_count"] for s in skill_usage)
  376. total_skill_edits = sum(s["manage_count"] for s in skill_usage)
  377. total_skill_actions = total_skill_loads + total_skill_edits
  378. top_skills = [{
  379. "skill": skill["skill"], "view_count": skill["view_count"], "manage_count": skill["manage_count"], "total_count": total_count,
  380. "percentage": (total_count / total_skill_actions * 100) if total_skill_actions else 0, "last_used_at": skill.get("last_used_at"),
  381. } for skill in skill_usage for total_count in (skill["view_count"] + skill["manage_count"],)]
  382. top_skills.sort(key=lambda s: (s["total_count"], s["view_count"], s["manage_count"], s["last_used_at"] or 0, s["skill"]), reverse=True)
  383. return {
  384. "summary": {"total_skill_loads": total_skill_loads, "total_skill_edits": total_skill_edits,
  385. "total_skill_actions": total_skill_actions, "distinct_skills_used": len(skill_usage)},
  386. "top_skills": top_skills,
  387. }
  388. def _compute_activity_patterns(self, sessions: list[dict]) -> dict:
  389. """Activity by day of week, hour, and active-day streak."""
  390. day_counts, hour_counts, daily_counts = Counter(), Counter(), Counter() # weekday (0=Monday), hour, "YYYY-MM-DD"
  391. for s in sessions:
  392. ts = s.get("started_at")
  393. if not ts:
  394. continue
  395. dt = datetime.fromtimestamp(ts)
  396. day_counts[dt.weekday()] += 1
  397. hour_counts[dt.hour] += 1
  398. daily_counts[dt.strftime("%Y-%m-%d")] += 1
  399. day_names = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
  400. day_breakdown = [{"day": day_names[i], "count": day_counts.get(i, 0)} for i in range(7)]
  401. hour_breakdown = [{"hour": i, "count": hour_counts.get(i, 0)} for i in range(24)]
  402. max_streak = 0
  403. if daily_counts:
  404. dates = [datetime.strptime(d, "%Y-%m-%d") for d in sorted(daily_counts)]
  405. current_streak = max_streak = 1
  406. for prev, cur in itertools.pairwise(dates):
  407. current_streak = current_streak + 1 if (cur - prev).days == 1 else 1
  408. max_streak = max(max_streak, current_streak)
  409. return {"by_day": day_breakdown, "by_hour": hour_breakdown, "busiest_day": max(day_breakdown, key=lambda x: x["count"]),
  410. "busiest_hour": max(hour_breakdown, key=lambda x: x["count"]), "active_days": len(daily_counts), "max_streak": max_streak}
  411. _TOP_METRICS = (
  412. ("Most messages", lambda s: s.get("message_count") or 0, "{} msgs"),
  413. ("Most tokens", lambda s: (s.get("input_tokens") or 0) + (s.get("output_tokens") or 0), "{:,} tokens"),
  414. ("Most tool calls", lambda s: s.get("tool_call_count") or 0, "{} calls"),
  415. )
  416. def _compute_top_sessions(self, sessions: list[dict]) -> list[dict]:
  417. """Notable sessions (longest, most messages, most tokens, most tool calls)."""
  418. top = []
  419. timed = [s for s in sessions if s.get("started_at") and s.get("ended_at")]
  420. if timed:
  421. longest = max(timed, key=lambda s: s["ended_at"] - s["started_at"])
  422. top.append({"label": "Longest session", "session_id": longest["id"][:16],
  423. "value": format_duration_compact(longest["ended_at"] - longest["started_at"]), "date": _day(longest["started_at"])})
  424. for label, metric, fmt in self._TOP_METRICS:
  425. best = max(sessions, key=metric)
  426. value = metric(best)
  427. if value > 0:
  428. top.append({"label": label, "session_id": best["id"][:16], "value": fmt.format(value), "date": _day(best.get("started_at"))})
  429. return top
  430. # ------------------------------------------------------------- Formatting
  431. @staticmethod
  432. def _section(title: str) -> list[str]:
  433. return [f" {title}", " " + "─" * 56]
  434. @staticmethod
  435. def _cost_lines(o: dict, templates: tuple) -> list[str]:
  436. """One formatted line per non-zero cost bucket (estimated, included, unknown)."""
  437. # Cost breakdown — surface the three buckets so subscription-included and unknown-cost sessions are
  438. # visible instead of silently collapsing to $0. See #77223.
  439. est_cost = o.get("estimated_cost", 0.0)
  440. values = (_fmt_est_cost(est_cost) if est_cost > 0 else "", o.get("included_cost_sessions", 0), o.get("unknown_cost_sessions", 0))
  441. return [tpl.format(v) for tpl, v in zip(templates, values) if v]
  442. def format_terminal(self, report: dict) -> str:
  443. """Format the insights report for terminal display (CLI)."""
  444. if report.get("empty"):
  445. src = f" (source: {report['source_filter']})" if report.get("source_filter") else ""
  446. return f" No sessions found in the last {report.get('days', 30)} days{src}."
  447. o = report["overview"]
  448. period_label = f"Last {report['days']} days"
  449. if report.get("source_filter"):
  450. period_label += f" ({report['source_filter']})"
  451. padding = 58 - len(period_label) - 2
  452. left_pad = padding // 2
  453. lines = [
  454. "",
  455. " ╔══════════════════════════════════════════════════════════╗",
  456. " ║ 📊 Hermes Insights ║",
  457. f" ║{' ' * left_pad} {period_label} {' ' * (padding - left_pad)}║",
  458. " ╚══════════════════════════════════════════════════════════╝",
  459. "",
  460. ]
  461. if (start := coerce_epoch(o.get("date_range_start"))) is not None and (end := coerce_epoch(o.get("date_range_end"))) is not None:
  462. start_str = safe_strftime(datetime.fromtimestamp(start), "%b %d, %Y")
  463. end_str = safe_strftime(datetime.fromtimestamp(end), "%b %d, %Y")
  464. lines += [f" Period: {start_str} — {end_str}", ""]
  465. lines += self._section("📋 Overview") + [
  466. f" Sessions: {o['total_sessions']:<12} Messages: {o['total_messages']:,}",
  467. f" Tool calls: {o['total_tool_calls']:<12,} User messages: {o['user_messages']:,}",
  468. f" Input tokens: {o['total_input_tokens']:<12,} Output tokens: {o['total_output_tokens']:,}",
  469. f" Total tokens: {o['total_tokens']:,}",
  470. ]
  471. if o["total_hours"] > 0:
  472. lines.append(f" Active time: ~{format_duration_compact(o['total_hours'] * 3600):<11} Avg session: ~{format_duration_compact(o['avg_session_duration'])}")
  473. lines += [f" Avg msgs/session: {o['avg_messages_per_session']:.1f}", ""]
  474. # Cost buckets: show included/unknown sessions instead of collapsing to $0.
  475. cost_lines = self._cost_lines(o, (" Estimated: {}", " Included: {} session(s) (subscription — no provider invoice)",
  476. " Unknown: {} session(s) (no pricing data)"))
  477. if cost_lines:
  478. lines += self._section("💰 Cost") + cost_lines + [""]
  479. if report["models"]:
  480. lines += self._section("🤖 Models Used") + [f" {'Model':<30} {'Sessions':>8} {'Tokens':>12}"]
  481. lines += [f" {m['model'][:28]:<30} {m['sessions']:>8} {m['total_tokens']:>12,}" for m in report["models"]] + [""]
  482. platforms = report["platforms"]
  483. if len(platforms) > 1 or (platforms and platforms[0]["platform"] != "cli"):
  484. lines += self._section("📱 Platforms") + [f" {'Platform':<14} {'Sessions':>8} {'Messages':>10} {'Tokens':>14}"]
  485. lines += [f" {p['platform']:<14} {p['sessions']:>8} {p['messages']:>10,} {p['total_tokens']:>14,}" for p in platforms] + [""]
  486. if report["tools"]:
  487. lines += self._section("🔧 Top Tools") + [f" {'Tool':<28} {'Calls':>8} {'%':>8}"]
  488. lines += [f" {t['tool']:<28} {t['count']:>8,} {t['percentage']:>7.1f}%" for t in report["tools"][:15]]
  489. if len(report["tools"]) > 15:
  490. lines.append(f" ... and {len(report['tools']) - 15} more tools")
  491. lines.append("")
  492. skills = report.get("skills", {})
  493. top_skills = skills.get("top_skills", [])
  494. if top_skills:
  495. lines += self._section("🧠 Top Skills") + [f" {'Skill':<28} {'Loads':>7} {'Edits':>7} {'Last used':>11}"]
  496. for skill in top_skills[:10]:
  497. last_used = _day(skill.get("last_used_at")) if skill.get("last_used_at") else "—"
  498. lines.append(f" {skill['skill'][:28]:<28} {skill['view_count']:>7,} {skill['manage_count']:>7,} {last_used:>11}")
  499. summary = skills.get("summary", {})
  500. lines += [f" Distinct skills: {summary.get('distinct_skills_used', 0)} Loads: {summary.get('total_skill_loads', 0):,} "
  501. f"Edits: {summary.get('total_skill_edits', 0):,}", ""]
  502. act = report.get("activity", {})
  503. if act.get("by_day"):
  504. lines += self._section("📅 Activity Patterns")
  505. bars = _bar_chart([d["count"] for d in act["by_day"]], max_width=15)
  506. lines += [f" {d['day']} {bar:<15} {d['count']}" for bar, d in zip(bars, act["by_day"])] + [""]
  507. busy_hours = [h for h in sorted(act["by_hour"], key=lambda x: x["count"], reverse=True) if h["count"] > 0][:5]
  508. if busy_hours:
  509. hour_strs = [f"{_hour12(h['hour'])} ({h['count']})" for h in busy_hours]
  510. lines.append(f" Peak hours: {', '.join(hour_strs)}")
  511. if act.get("active_days"):
  512. lines.append(f" Active days: {act['active_days']}")
  513. if act.get("max_streak") and act["max_streak"] > 1:
  514. lines.append(f" Best streak: {act['max_streak']} consecutive days")
  515. lines.append("")
  516. if report.get("top_sessions"):
  517. lines += self._section("🏆 Notable Sessions")
  518. lines += [f" {ts['label']:<20} {ts['value']:<18} ({ts['date']}, {ts['session_id']})" for ts in report["top_sessions"]] + [""]
  519. return "\n".join(lines)
  520. def format_gateway(self, report: dict) -> str:
  521. """Format the insights report for gateway/messaging (shorter)."""
  522. if report.get("empty"):
  523. return f"No sessions found in the last {report.get('days', 30)} days."
  524. o = report["overview"]
  525. lines = [
  526. f"📊 **Hermes Insights** — Last {report['days']} days\n",
  527. f"**Sessions:** {o['total_sessions']} | **Messages:** {o['total_messages']:,} | **Tool calls:** {o['total_tool_calls']:,}",
  528. f"**Tokens:** {o['total_tokens']:,} (in: {o['total_input_tokens']:,} / out: {o['total_output_tokens']:,})",
  529. ]
  530. if o["total_hours"] > 0:
  531. lines.append(f"**Active time:** ~{format_duration_compact(o['total_hours'] * 3600)} | **Avg session:** ~{format_duration_compact(o['avg_session_duration'])}")
  532. lines.append("")
  533. cost_parts = self._cost_lines(o, ("{} estimated", "{} included (subscription)", "{} unknown"))
  534. if cost_parts:
  535. lines += [f"**Cost:** {' | '.join(cost_parts)}", ""]
  536. if report["models"]:
  537. lines += ["**🤖 Models:**"] + [f" {m['model'][:25]} — {m['sessions']} sessions, {m['total_tokens']:,} tokens" for m in report["models"][:5]] + [""]
  538. if len(report["platforms"]) > 1:
  539. lines += ["**📱 Platforms:**"] + [f" {p['platform']} — {p['sessions']} sessions, {p['messages']:,} msgs" for p in report["platforms"]] + [""]
  540. if report["tools"]:
  541. lines += ["**🔧 Top Tools:**"] + [f" {t['tool']} — {t['count']:,} calls ({t['percentage']:.1f}%)" for t in report["tools"][:8]] + [""]
  542. skills = report.get("skills", {})
  543. if skills.get("top_skills"):
  544. lines.append("**🧠 Top Skills:**")
  545. for skill in skills["top_skills"][:5]:
  546. suffix = f", last used {_day(skill['last_used_at'])}" if skill.get("last_used_at") else ""
  547. lines.append(f" {skill['skill']} — {skill['view_count']:,} loads, {skill['manage_count']:,} edits{suffix}")
  548. lines.append("")
  549. act = report.get("activity", {})
  550. if act.get("busiest_day") and act.get("busiest_hour"):
  551. lines.append(f"**📅 Busiest:** {act['busiest_day']['day']}s ({act['busiest_day']['count']} sessions), {_hour12(act['busiest_hour']['hour'])} ({act['busiest_hour']['count']} sessions)")
  552. if act.get("active_days"):
  553. lines.append(f"**Active days:** {act['active_days']}")
  554. if act.get("max_streak", 0) > 1:
  555. lines.append(f"**Best streak:** {act['max_streak']} consecutive days")
  556. return "\n".join(lines)