4 KiB
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_DAYSenv var (default: 7) - Cleanup runs every hour in a daemon thread
- No size limit — retention is the only guard