hermes-trace/docs/database.md

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