hermes-trace/docs/database.md

4 KiB

hermes-trace Database Schema

Tables

turns

One row per LLM API call (including background review forks and subagents).

Column Type Description
id INTEGER PK Auto-increment
session_id TEXT NOT NULL Hermes session identifier
model TEXT Model name (e.g. deepseek-v4-flash)
provider TEXT LLM provider name when supplied by the hook
turn_type TEXT Reserved — currently always main; planned: background_review, subagent
api_call_count INTEGER Which call in this turn (per-model counter)
request_preview TEXT First 300 chars of request
response_preview TEXT First 300 chars of assistant response
reasoning TEXT Reasoning content (deepseek, etc.)
finish_reason TEXT stop, tool_calls, length, or error
input_tokens INTEGER Prompt tokens
output_tokens INTEGER Completion tokens
total_tokens INTEGER Total
cache_read_tokens INTEGER Cache hit tokens
cache_write_tokens INTEGER Cache write tokens
reasoning_tokens INTEGER Reasoning/thinking tokens
elapsed_ms INTEGER API call duration
tool_calls_count INTEGER Number of tool calls in this turn
full_request TEXT Normalized LLM request transcript JSON
full_response TEXT Sanitized LLM response snapshot JSON
created_at TEXT ISO timestamp

session_summary

Cached aggregate of session activity for fast homepage listing — avoids GROUP BY on the turns table.

Column Type Description
session_id TEXT PK Session identifier
turn_count INTEGER Number of turns in this session
last_activity TEXT ISO timestamp of most recent turn
models TEXT Comma-separated unique model names

tool_calls

One row per tool invocation within a turn.

Column Type Description
id INTEGER PK Auto-increment
turn_id INTEGER FK References turns(id) with ON DELETE CASCADE
call_order INTEGER Order within the turn
name TEXT Tool name (e.g. terminal, patch, read_file)
args TEXT Tool arguments as JSON
result TEXT Tool result as JSON
duration_ms INTEGER Execution time
error TEXT Error message if tool raised
created_at TEXT ISO timestamp

Indexes

Index Columns Purpose
idx_turns_session_created session_id, created_at DESC, id DESC Session filtering and newest-first turn ordering
idx_toolcalls_turn turn_id Tool call lookup per turn
idx_summary_activity last_activity DESC Session list ordering (summary cache)

Key Queries

Batch-load turns with tool calls (get_turns_with_tool_calls)

SELECT * FROM turns WHERE session_id = ? ORDER BY created_at DESC, id DESC LIMIT ?
-- Then batch-load tool calls for all matching turn IDs
SELECT * FROM tool_calls WHERE turn_id IN (?, ?, ...) ORDER BY turn_id, call_order ASC

Batch-load listing previews (get_turns_with_preview)

SELECT id, session_id, created_at, ..., substr(request_preview, 1, 80),
       substr(response_preview, 1, 80)
FROM turns
WHERE session_id = ?
ORDER BY created_at DESC, id DESC
-- Optional LIMIT ? only when the caller passes a limit; the session page loads all turns.
-- Then batch-load projected tool call metadata and 80-character previews.

Aggregate session list (get_sessions)

SELECT session_id, turn_count, last_activity, models
FROM session_summary ORDER BY last_activity DESC LIMIT ?

Cleanup (clean_expired_turns)

DELETE FROM turns WHERE created_at < datetime('now', '-N days')
-- tool_calls deleted via ON DELETE CASCADE

Retention

  • Default: 7 days
  • Configurable via HERMES_TRACE_RETENTION_DAYS env var (default: 7)
  • Cleanup runs every hour in a daemon thread
  • No size limit — retention is the only guard