105 lines
4 KiB
Markdown
105 lines
4 KiB
Markdown
# 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`)
|
|
```sql
|
|
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`)
|
|
```sql
|
|
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`)
|
|
```sql
|
|
SELECT session_id, turn_count, last_activity, models
|
|
FROM session_summary ORDER BY last_activity DESC LIMIT ?
|
|
```
|
|
|
|
### Cleanup (`clean_expired_turns`)
|
|
```sql
|
|
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
|