hermes-trace/db.py

782 lines
27 KiB
Python

from __future__ import annotations
import json
import os
import sqlite3
import threading
from datetime import UTC, datetime
from typing import Any
def get_default_db_path() -> str:
"""Return the default telemetry DB path based on ``$HERMES_HOME``.
Reads ``HERMES_HOME`` env var (stripped), falls back to ``~/.hermes`` —
matching the same resolution logic as
``hermes_constants.get_hermes_home()``. This lets the database follow
Hermes profile switching:
``$HERMES_HOME/telemetry/telemetry.db`` maps to the active profile.
"""
hermes_home = os.environ.get("HERMES_HOME", "").strip()
if not hermes_home:
hermes_home = os.path.expanduser("~/.hermes")
return os.path.join(hermes_home, "telemetry", "telemetry.db")
_SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS turns (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT NOT NULL,
-- Call metadata
model TEXT,
provider TEXT,
turn_type TEXT DEFAULT 'main',
api_call_count INTEGER,
-- Previews (for fast listing)
request_preview TEXT,
response_preview TEXT,
reasoning TEXT,
reasoning_content TEXT,
finish_reason TEXT,
-- Token usage (from post_api_request `usage` dict)
input_tokens INTEGER,
output_tokens INTEGER,
total_tokens INTEGER,
cache_read_tokens INTEGER,
cache_write_tokens INTEGER,
reasoning_tokens INTEGER,
-- Performance
elapsed_ms INTEGER,
tool_calls_count INTEGER DEFAULT 0,
-- Full payload (always stored)
full_request TEXT,
full_response TEXT,
-- Timestamp
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS tool_calls (
id INTEGER PRIMARY KEY AUTOINCREMENT,
turn_id INTEGER NOT NULL REFERENCES turns(id) ON DELETE CASCADE,
call_order INTEGER,
name TEXT NOT NULL,
args TEXT,
result TEXT,
duration_ms INTEGER,
error TEXT,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS session_summary (
session_id TEXT PRIMARY KEY,
turn_count INTEGER NOT NULL DEFAULT 0,
last_activity TEXT NOT NULL DEFAULT '',
models TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_turns_session_created ON turns(session_id, created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_toolcalls_turn ON tool_calls(turn_id);
CREATE INDEX IF NOT EXISTS idx_summary_activity ON session_summary(last_activity DESC);
"""
class TelemetryDB:
"""Persistent SQLite connection to the telemetry database.
Holds one long-lived ``sqlite3.Connection`` with WAL mode and a
per-instance ``threading.Lock`` for write serialisation. Read-only
queries (via WAL) do not need the lock.
"""
def __init__(self, db_path: str) -> None:
self._db_path = db_path
self._conn = sqlite3.connect(db_path, check_same_thread=False)
self._conn.row_factory = sqlite3.Row
self._lock = threading.Lock()
def init_db(self) -> None:
"""Create tables and indexes (IF NOT EXISTS) with WAL mode."""
with self._conn:
self._conn.execute("PRAGMA foreign_keys=ON")
self._conn.execute("PRAGMA journal_mode=WAL")
self._conn.execute("PRAGMA wal_autocheckpoint=10000")
self._conn.execute("PRAGMA synchronous=NORMAL")
self._conn.execute("PRAGMA busy_timeout=5000")
self._conn.executescript(_SCHEMA_SQL)
# Migration v1: add reasoning_content column for old databases + backfill
# session_summary for databases created before the summary table existed.
with self._conn:
self._conn.execute("BEGIN IMMEDIATE")
version = self._conn.execute("PRAGMA user_version").fetchone()[0]
if version < 1:
cols = {
row[1]
for row in self._conn.execute("PRAGMA table_info(turns)").fetchall()
}
if "reasoning_content" not in cols:
self._conn.execute(
"ALTER TABLE turns ADD COLUMN reasoning_content TEXT"
)
self._rebuild_summary()
self._conn.execute("PRAGMA user_version = 1")
# Migration v2: backfill reasoning from reasoning_content.
# The reasoning_content column is no longer actively written;
# reasoning is now the single canonical field.
with self._conn:
self._conn.execute("BEGIN IMMEDIATE")
version = self._conn.execute("PRAGMA user_version").fetchone()[0]
if version < 2:
self._conn.execute(
"UPDATE turns SET reasoning = reasoning_content "
"WHERE (reasoning IS NULL OR reasoning = '') "
"AND (reasoning_content IS NOT NULL AND reasoning_content != '')"
)
self._conn.execute("PRAGMA user_version = 2")
# Migration v3: add provider metadata for projected listing queries.
with self._conn:
self._conn.execute("BEGIN IMMEDIATE")
version = self._conn.execute("PRAGMA user_version").fetchone()[0]
if version < 3:
cols = {
row[1]
for row in self._conn.execute("PRAGMA table_info(turns)").fetchall()
}
if "provider" not in cols:
self._conn.execute("ALTER TABLE turns ADD COLUMN provider TEXT")
self._conn.execute("PRAGMA user_version = 3")
# Migration v4: replace separate filtering and ordering indexes with
# one composite index created by _SCHEMA_SQL above.
with self._conn:
self._conn.execute("BEGIN IMMEDIATE")
version = self._conn.execute("PRAGMA user_version").fetchone()[0]
if version < 4:
self._conn.execute("DROP INDEX IF EXISTS idx_turns_session")
self._conn.execute("DROP INDEX IF EXISTS idx_turns_created")
self._conn.execute("PRAGMA user_version = 4")
# ------------------------------------------------------------------
# Write helpers
# ------------------------------------------------------------------
def write_turn(
self,
session_id: str,
model: str,
api_call_count: int,
request_preview: str,
response_preview: str,
reasoning: str,
finish_reason: str,
input_tokens: int | None,
output_tokens: int | None,
total_tokens: int | None,
cache_read_tokens: int | None,
cache_write_tokens: int | None,
reasoning_tokens: int | None,
elapsed_ms: int | None,
tool_calls_count: int,
full_request: str,
full_response: str,
provider: str | None = None,
) -> int:
"""Write one API call turn into the database.
Args:
session_id: Hermes session identifier.
model: Model name.
api_call_count: API call sequence number.
request_preview: Truncated request text for listings.
response_preview: Truncated response text for listings.
reasoning: Model reasoning content.
finish_reason: Provider finish reason.
input_tokens: Input token count, or None.
output_tokens: Output token count, or None.
total_tokens: Total token count, or None.
cache_read_tokens: Cache read token count, or None.
cache_write_tokens: Cache write token count, or None.
reasoning_tokens: Reasoning token count, or None.
elapsed_ms: API call duration in milliseconds, or None.
tool_calls_count: Number of tool calls in the response.
full_request: Complete request payload as JSON text.
full_response: Complete response payload as JSON text.
provider: LLM provider name, or None when unavailable.
Returns:
The inserted turn's id (INTEGER PRIMARY KEY).
"""
columns = (
"session_id, model, provider, api_call_count, request_preview, "
"response_preview, reasoning, finish_reason, input_tokens, "
"output_tokens, total_tokens, cache_read_tokens, cache_write_tokens, "
"reasoning_tokens, elapsed_ms, tool_calls_count, full_request, "
"full_response"
)
placeholders = ", ".join(["?"] * 18)
values = (
session_id,
model,
provider,
api_call_count,
request_preview,
response_preview,
reasoning,
finish_reason,
input_tokens,
output_tokens,
total_tokens,
cache_read_tokens,
cache_write_tokens,
reasoning_tokens,
elapsed_ms,
tool_calls_count,
full_request,
full_response,
)
with self._lock, self._conn:
cursor = self._conn.execute(
f"INSERT INTO turns ({columns}) VALUES ({placeholders})",
values,
)
lastrowid = cursor.lastrowid
assert lastrowid is not None, "lastrowid should not be None after INSERT"
# Update session summary cache
if session_id:
now_iso = datetime.now(UTC).strftime("%Y-%m-%d %H:%M:%S")
existing = self._conn.execute(
"SELECT models FROM session_summary WHERE session_id = ?",
(session_id,),
).fetchone()
if existing:
models_set = set(existing[0].split(",")) if existing[0] else set()
if model:
models_set.add(model)
self._conn.execute(
"UPDATE session_summary SET turn_count = turn_count + 1, last_activity = ?, models = ? WHERE session_id = ?",
(now_iso, ",".join(sorted(models_set)), session_id),
)
else:
self._conn.execute(
"INSERT INTO session_summary (session_id, turn_count, last_activity, models) VALUES (?, 1, ?, ?)",
(session_id, now_iso, model or ""),
)
return lastrowid
def write_tool_call(
self,
turn_id: int,
call_order: int,
name: str,
args: str,
result: str,
duration_ms: int | None,
error: str | None,
) -> None:
"""Write one tool call record into the database.
Args:
turn_id: Foreign key referencing turns(id).
call_order: Order of this tool call within the turn.
name: Tool name.
args: JSON string of the tool arguments.
result: JSON string of the tool result.
duration_ms: Execution duration in milliseconds, or None.
error: Error message if the tool raised, or None.
"""
with self._lock, self._conn:
self._conn.execute(
"INSERT INTO tool_calls (turn_id, call_order, name, args, result, duration_ms, error) "
"VALUES (?, ?, ?, ?, ?, ?, ?)",
(turn_id, call_order, name, args, result, duration_ms, error),
)
def clean_expired_turns(self, retention_days: int) -> int:
"""Delete turns older than *retention_days* ago.
Associated tool_calls are deleted automatically via ON DELETE CASCADE.
Args:
retention_days: Number of days of data to retain.
Returns:
Number of deleted turn rows.
"""
with self._lock, self._conn:
cursor = self._conn.execute(
"DELETE FROM turns WHERE created_at < datetime('now', ?)",
(f"-{retention_days} days",),
)
if cursor.rowcount > 0:
self._conn.execute("PRAGMA optimize")
self._rebuild_summary()
return cursor.rowcount
def _rebuild_summary(self) -> None:
"""Rebuild session_summary from remaining turns data.
Called after retention cleanup deletes expired turns.
"""
self._conn.execute("DELETE FROM session_summary")
self._conn.execute("""
INSERT INTO session_summary (session_id, turn_count, last_activity, models)
SELECT
session_id,
COUNT(*) AS turn_count,
MAX(created_at) AS last_activity,
COALESCE(GROUP_CONCAT(DISTINCT model), '') AS models
FROM turns
GROUP BY session_id
""")
# ------------------------------------------------------------------
# Read helpers (no lock needed under WAL for pure reads)
# ------------------------------------------------------------------
def get_sessions(self, limit: int | None = 50) -> list[dict[str, Any]]:
"""Return the most recent sessions with aggregated metadata.
Each dict contains:
- session_id (str)
- turn_count (int)
- last_activity (str, ISO datetime)
- models (str, comma-separated unique model names)
Args:
limit: Maximum number of sessions to return (default 50).
Pass ``None`` for no limit.
Returns:
List of session summary dicts ordered by most recent activity first.
"""
query = (
"SELECT session_id, turn_count, last_activity, models "
"FROM session_summary ORDER BY last_activity DESC, session_id"
)
params: tuple[int, ...] = ()
if limit is not None:
query += " LIMIT ?"
params = (limit,)
cursor = self._conn.execute(query, params)
return [dict(row) for row in cursor.fetchall()]
def get_session_turn_count(self, session_id: str) -> int:
"""Return the total turn count for a session from the summary cache.
Args:
session_id: The session identifier.
Returns:
Total turns for the session, or 0 if unknown.
"""
cursor = self._conn.execute(
"SELECT turn_count FROM session_summary WHERE session_id = ?",
(session_id,),
)
row = cursor.fetchone()
return row[0] if row else 0
def get_session_stats(self, session_id: str) -> dict[str, int | float | None]:
"""Return aggregate performance statistics for all turns in a session.
Args:
session_id: The session identifier.
Returns:
Aggregated token, elapsed-time, tool-call, throughput, and latency
statistics, including the number of turns with known duration.
Throughput and latency are None when their required denominator is
zero.
"""
cursor = self._conn.execute(
"""
SELECT
COALESCE(SUM(output_tokens), 0) AS total_output_tokens,
COALESCE(SUM(elapsed_ms), 0) AS total_elapsed_ms,
COUNT(elapsed_ms) AS known_duration_turns
FROM turns
WHERE session_id = ?
""",
(session_id,),
)
row = cursor.fetchone()
total_output_tokens = int(row["total_output_tokens"])
total_elapsed_ms = int(row["total_elapsed_ms"])
known_duration_turns = int(row["known_duration_turns"])
total_tool_calls = int(
self._conn.execute(
"SELECT COUNT(*) FROM tool_calls tc "
"JOIN turns t ON tc.turn_id = t.id "
"WHERE t.session_id = ?",
(session_id,),
).fetchone()[0]
)
avg_tps = (
total_output_tokens / (total_elapsed_ms / 1000)
if total_output_tokens > 0 and total_elapsed_ms > 0
else None
)
avg_latency = (
total_elapsed_ms / known_duration_turns / 1000
if known_duration_turns > 0
else None
)
return {
"total_output_tokens": total_output_tokens,
"total_elapsed_ms": total_elapsed_ms,
"total_tool_calls": total_tool_calls,
"avg_tps": avg_tps,
"avg_latency": avg_latency,
"known_duration_turns": known_duration_turns,
}
def get_turns_for_session(
self, session_id: str, limit: int = 100
) -> list[dict[str, Any]]:
"""Return all turns for a given session, newest first.
Args:
session_id: The session identifier.
limit: Maximum number of turns to return (default 100).
Returns:
List of turn dicts ordered by created_at DESC (newest first).
"""
cursor = self._conn.execute(
"SELECT * FROM turns WHERE session_id = ? ORDER BY created_at DESC, id DESC LIMIT ?",
(session_id, limit),
)
return [dict(row) for row in cursor.fetchall()]
def get_turns_with_tool_calls(
self, session_id: str, limit: int = 100
) -> list[dict[str, Any]]:
"""Return all turns for a session, each with nested tool_calls, newest first.
Args:
session_id: The session identifier.
limit: Maximum number of turns to return (default 100).
Returns:
List of turn dicts each with a 'tool_calls' list, ordered by
created_at DESC, id DESC.
"""
cursor = self._conn.execute(
"SELECT * FROM turns WHERE session_id = ? ORDER BY created_at DESC, id DESC LIMIT ?",
(session_id, limit),
)
turns = [dict(row) for row in cursor.fetchall()]
# Batch-load tool calls for all turns
if turns:
turn_ids = [t["id"] for t in turns]
placeholders = ",".join(["?"] * len(turn_ids))
tc_cursor = self._conn.execute(
f"SELECT * FROM tool_calls WHERE turn_id IN ({placeholders}) ORDER BY turn_id, call_order ASC",
turn_ids,
)
tc_rows = tc_cursor.fetchall()
tc_by_turn: dict[int, list[dict[str, Any]]] = {}
for row in tc_rows:
d = dict(row)
tc_by_turn.setdefault(d["turn_id"], []).append(d)
for t in turns:
t["tool_calls"] = tc_by_turn.get(t["id"], [])
# Assign display order (newest first = 1 at the top)
total = len(turns)
for i, t in enumerate(turns):
t["_order"] = total - i
return turns
def get_turns_with_preview(
self, session_id: str, limit: int | None = None
) -> list[dict[str, Any]]:
"""Return projected turn and tool-call previews for a session.
Full request, response, reasoning, tool arguments, and tool results are
excluded so listing pages do not load potentially multi-megabyte text.
Args:
session_id: The session identifier.
limit: Maximum number of turns to return. ``None`` (default)
returns all turns in the session.
Returns:
List of turn dicts each with a projected ``tool_calls`` list,
ordered by created_at DESC, id DESC.
"""
query = """
SELECT
id,
session_id,
created_at,
input_tokens,
output_tokens,
total_tokens,
cache_read_tokens,
cache_write_tokens,
reasoning_tokens,
elapsed_ms,
model,
provider,
finish_reason,
api_call_count,
tool_calls_count,
substr(request_preview, 1, 80) AS request_preview,
substr(response_preview, 1, 80) AS response_preview
FROM turns
WHERE session_id = ?
ORDER BY created_at DESC, id DESC
"""
params: tuple[Any, ...] = (session_id,)
if limit is not None:
query += " LIMIT ?"
params = (session_id, limit)
cursor = self._conn.execute(query, params)
turns = [dict(row) for row in cursor.fetchall()]
if turns:
turn_ids = [turn["id"] for turn in turns]
placeholders = ",".join(["?"] * len(turn_ids))
tc_cursor = self._conn.execute(
f"""
SELECT
id,
turn_id,
call_order,
name,
duration_ms,
error,
substr(args, 1, 80) AS args,
substr(result, 1, 80) AS result
FROM tool_calls
WHERE turn_id IN ({placeholders})
ORDER BY turn_id, call_order ASC
""",
turn_ids,
)
tc_by_turn: dict[int, list[dict[str, Any]]] = {}
for row in tc_cursor.fetchall():
tool_call = dict(row)
tc_by_turn.setdefault(tool_call["turn_id"], []).append(tool_call)
for turn in turns:
turn["tool_calls"] = tc_by_turn.get(turn["id"], [])
total = len(turns)
for index, turn in enumerate(turns):
turn["_order"] = total - index
return turns
def get_turn_preview(self, turn_id: int) -> dict[str, Any] | None:
"""Return one turn's detail metadata without loading full payloads.
Full request and response payloads are intentionally omitted. Tool
call arguments and results are limited to 80-character previews so the
Preview tab does not load large payloads into memory.
Args:
turn_id: The turn's primary key.
Returns:
A turn dict with projected tool calls, or None if not found.
"""
cursor = self._conn.execute(
"""
SELECT
id,
session_id,
model,
provider,
turn_type,
api_call_count,
request_preview,
response_preview,
reasoning,
reasoning_content,
finish_reason,
input_tokens,
output_tokens,
total_tokens,
cache_read_tokens,
cache_write_tokens,
reasoning_tokens,
elapsed_ms,
tool_calls_count,
created_at
FROM turns
WHERE id = ?
""",
(turn_id,),
)
row = cursor.fetchone()
if row is None:
return None
turn = dict(row)
tc_cursor = self._conn.execute(
"""
SELECT
id,
turn_id,
call_order,
name,
substr(args, 1, 80) AS args,
substr(result, 1, 80) AS result,
duration_ms,
error,
created_at
FROM tool_calls
WHERE turn_id = ?
ORDER BY call_order ASC
""",
(turn_id,),
)
turn["tool_calls"] = [dict(tc_row) for tc_row in tc_cursor.fetchall()]
return turn
def get_turn_tool_calls(self, turn_id: int) -> list[dict[str, Any]]:
"""Return all tool calls for a turn with full args and result.
Args:
turn_id: The turn's primary key.
Returns:
List of tool call dicts ordered by call_order ASC, or an empty
list if the turn has no tool calls.
"""
cursor = self._conn.execute(
"SELECT * FROM tool_calls WHERE turn_id = ? ORDER BY call_order ASC",
(turn_id,),
)
return [dict(row) for row in cursor.fetchall()]
def get_turn_payload(
self, turn_id: int, payload_type: str
) -> dict[str, Any] | None:
"""Return only one complete payload column for a turn.
Args:
turn_id: The turn's primary key.
payload_type: Either ``request`` or ``response``.
Returns:
A dict containing the selected full payload, or None if the turn
does not exist.
Raises:
ValueError: If *payload_type* is not ``request`` or ``response``.
"""
payload_columns = {
"request": "full_request",
"response": "full_response",
}
try:
column = payload_columns[payload_type]
except KeyError as exc:
raise ValueError(f"unsupported payload type: {payload_type}") from exc
cursor = self._conn.execute(
f"SELECT {column} FROM turns WHERE id = ?",
(turn_id,),
)
row = cursor.fetchone()
return dict(row) if row is not None else None
def get_turn_user_input(self, turn_id: int) -> str:
"""Return the last user message from a turn's full request.
Only the full request column is selected so this lookup does not load
turn metadata or nested tool calls.
Args:
turn_id: The turn's primary key.
Returns:
The last user message, or an empty string when the turn has no
readable user message.
"""
cursor = self._conn.execute(
"SELECT full_request FROM turns WHERE id = ?",
(turn_id,),
)
row = cursor.fetchone()
if row is None or not row[0]:
return ""
try:
messages = json.loads(row[0])
except (json.JSONDecodeError, TypeError):
return ""
if not isinstance(messages, list):
return ""
for message in reversed(messages):
if not isinstance(message, dict) or message.get("role") != "user":
continue
content = message.get("content", "") or ""
if isinstance(content, str):
return content
return str(content)
return ""
def get_turn_detail(self, turn_id: int) -> dict[str, Any] | None:
"""Return a single turn with its tool calls, or None if not found.
The returned dict contains all turn columns plus a 'tool_calls' key
holding a list of tool call dicts (ordered by call_order ASC).
Args:
turn_id: The turn's primary key.
Returns:
Turn dict with nested 'tool_calls' list, or None if the turn does
not exist.
"""
cursor = self._conn.execute(
"SELECT * FROM turns WHERE id = ?",
(turn_id,),
)
row = cursor.fetchone()
if row is None:
return None
turn = dict(row)
tc_cursor = self._conn.execute(
"SELECT * FROM tool_calls WHERE turn_id = ? ORDER BY call_order ASC",
(turn_id,),
)
turn["tool_calls"] = [dict(tc_row) for tc_row in tc_cursor.fetchall()]
return turn
# Module-level singleton — created by __init__.py and app.py at startup.
telemetry_db: TelemetryDB | None = None
def get_db() -> TelemetryDB:
"""Return the module-level TelemetryDB singleton.
Raises:
RuntimeError: If telemetry_db has not been initialised.
"""
if telemetry_db is None:
raise RuntimeError("telemetry_db has not been initialised")
return telemetry_db