File size: 14,850 Bytes

645efc2

# Session Storage

Hermes Agent uses a SQLite database (`~/.hermes/state.db`) to persist session
metadata, full message history, and model configuration across CLI and gateway
sessions. This replaces the earlier per-session JSONL file approach.

Source file: `hermes_state.py`


## Architecture Overview

```

~/.hermes/state.db (SQLite, WAL mode)

├── sessions              — Session metadata, token counts, billing

├── messages              — Full message history per session

├── session_model_usage   — Per-model/per-task usage attribution rows

├── messages_fts          — FTS5 virtual table (content + tool_name + tool_calls)

├── messages_fts_trigram  — FTS5 virtual table with trigram tokenizer (CJK / substring search)

├── messages_fts_cjk      — FTS5 virtual table with cjk_unicode61 tokenizer

├── state_meta            — Key/value metadata table

├── gateway_routing       — Gateway routing metadata

├── compression_locks     — Cross-process compression locking

├── async_delegations     — Async delegation bookkeeping

└── schema_version        — Single-row table tracking migration state

```

Key design decisions:
- **WAL mode** for concurrent readers + one writer (gateway multi-platform)
- **FTS5 virtual table** for fast text search across all session messages
- **Session lineage** via `parent_session_id` chains (compression-triggered splits)
- **Source tagging** (`cli`, `telegram`, `discord`, etc.) for platform filtering
- Batch runner and RL trajectories are NOT stored here (separate systems)


## SQLite Schema

### Sessions Table

Abridged — see `SCHEMA_SQL` in `hermes_state.py` for the full current column list
(which also includes gateway routing metadata such as `session_key`, `chat_id`,
`chat_type`, `thread_id`, `display_name`, `origin_json`, `expiry_finalized`,
workspace fields `cwd` / `git_branch` / `git_repo_root`, handoff and
compression-failure fields, `profile_name`, `rewind_count`, `archived`, and
`pinned`):

```sql

CREATE TABLE IF NOT EXISTS sessions (

    id TEXT PRIMARY KEY,

    source TEXT NOT NULL,

    user_id TEXT,

    model TEXT,

    model_config TEXT,

    system_prompt TEXT,

    parent_session_id TEXT,

    started_at REAL NOT NULL,

    ended_at REAL,

    end_reason TEXT,

    message_count INTEGER DEFAULT 0,

    tool_call_count INTEGER DEFAULT 0,

    input_tokens INTEGER DEFAULT 0,

    output_tokens INTEGER DEFAULT 0,

    cache_read_tokens INTEGER DEFAULT 0,

    cache_write_tokens INTEGER DEFAULT 0,

    reasoning_tokens INTEGER DEFAULT 0,

    billing_provider TEXT,

    billing_base_url TEXT,

    billing_mode TEXT,

    estimated_cost_usd REAL,

    actual_cost_usd REAL,

    cost_status TEXT,

    cost_source TEXT,

    pricing_version TEXT,

    title TEXT,

    api_call_count INTEGER DEFAULT 0,

    -- ... additional gateway/workspace/handoff/compression columns ...

    FOREIGN KEY (parent_session_id) REFERENCES sessions(id)

);



CREATE INDEX IF NOT EXISTS idx_sessions_source ON sessions(source);

CREATE INDEX IF NOT EXISTS idx_sessions_parent ON sessions(parent_session_id);

CREATE INDEX IF NOT EXISTS idx_sessions_started ON sessions(started_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS idx_sessions_title_unique

    ON sessions(title) WHERE title IS NOT NULL;

```

### Messages Table

Abridged — the full schema also includes `effect_disposition`,
`platform_message_id`, `observed`, `active`, `compacted`, `api_content`,
`display_kind`, and `display_metadata`:

```sql

CREATE TABLE IF NOT EXISTS messages (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    session_id TEXT NOT NULL REFERENCES sessions(id),

    role TEXT NOT NULL,

    content TEXT,

    tool_call_id TEXT,

    tool_calls TEXT,

    tool_name TEXT,

    timestamp REAL NOT NULL,

    token_count INTEGER,

    finish_reason TEXT,

    reasoning TEXT,

    reasoning_content TEXT,

    reasoning_details TEXT,

    codex_reasoning_items TEXT,

    codex_message_items TEXT

    -- ... additional display/compaction columns ...

);



CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id, timestamp);

CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages(session_id, id);

```

Notes:
- `tool_calls` is stored as a JSON string (serialized list of tool call objects)
- `reasoning_details`, `codex_reasoning_items`, and `codex_message_items` are stored as JSON strings
- `reasoning` stores the raw reasoning text for providers that expose it
- `api_content` is a byte-fidelity sidecar: the exact content string sent to the API for this message when it differs from `content` (ephemeral memory/plugin injections, persist overrides). It preserves the wire bytes for prompt-cache-stable replay — stored as sent, except lone surrogates, which sqlite3 cannot bind and which the conversation loop scrubs from every outgoing payload anyway. `NULL` means `content` was sent verbatim.
- Timestamps are Unix epoch floats (`time.time()`)

### FTS5 Full-Text Search

```sql

CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(

    content,

    tool_name,

    tool_calls,

    content='messages',

    content_rowid='id'

);

```

The FTS5 table is kept in sync via three triggers that fire on INSERT, UPDATE,
and DELETE of the `messages` table. The current triggers are gated on the
`fts_rebuild_high_water` / `fts_rebuild_progress` markers in `state_meta` (so a
background FTS rebuild can proceed without double-indexing) and cover all three
indexed columns — see `SCHEMA_SQL` in `hermes_state.py` for the exact SQL.


## Schema Version and Migrations

Current schema version: **23**

The `schema_version` table stores a single integer. Simple column additions are handled declaratively by `_reconcile_columns()` (which diffs live columns against `SCHEMA_SQL` and ADDs any missing ones). The version-gated chain is reserved for data migrations and index/FTS changes that can't be expressed declaratively:

| Version | Change |
|---------|--------|
| 1 | Initial schema (sessions, messages, FTS5) |
| 2 | Add `finish_reason` column to messages |
| 3 | Add `title` column to sessions |
| 4 | Add unique index on `title` (NULLs allowed, non-NULL must be unique) |
| 5 | Add billing columns: `cache_read_tokens`, `cache_write_tokens`, `reasoning_tokens`, `billing_provider`, `billing_base_url`, `billing_mode`, `estimated_cost_usd`, `actual_cost_usd`, `cost_status`, `cost_source`, `pricing_version` |
| 6 | Add reasoning columns to messages: `reasoning`, `reasoning_details`, `codex_reasoning_items` |
| 7 | Add `reasoning_content` column to messages |
| 8 | Add `api_call_count` column to sessions |
| 9 | Add `codex_message_items` column to messages for Codex Responses message id/phase replay |
| 10 | Add `messages_fts_trigram` virtual table (trigram tokenizer for CJK / substring search) and backfill existing rows |
| 11 | Re-index `messages_fts` and `messages_fts_trigram` to cover `tool_name` + `tool_calls` and switch from external-content to inline mode; drop old triggers and backfill every message row |
| 16 | Tag delegate subagent rows in `model_config` (`$._delegate_from`) so session pickers stay clean after parent deletes orphan them |
| 18 | Gateway metadata consolidation — backfill `display_name` / `origin_json` / `expiry_finalized` from `sessions.json` |
| 20 | Per-model usage attribution — seed `session_model_usage` rows from historical per-session aggregate totals |
| 22 | Task-dimension usage attribution — rebuild `session_model_usage` so the `task` column participates in the PRIMARY KEY |
| 23 | FTS storage redesign — external-content FTS tables replacing the v11 inline-mode copies (opt-in transition for existing DBs) |

Versions not listed above were declarative column additions handled by `_reconcile_columns()` (version bump only, no data migration).

Declarative column adds use `ALTER TABLE ADD COLUMN` wrapped in try/except to handle the column-already-exists case (idempotent). The version number is bumped after each successful migration block.


## Write Contention Handling

Multiple hermes processes (gateway + CLI sessions + worktree agents) share one
`state.db`. The `SessionDB` class handles write contention with:

- **Short SQLite timeout** (1 second) instead of the default 30s
- **Application-level retry** with random jitter (20-150ms, up to 15 retries)
- **BEGIN IMMEDIATE** transactions to surface lock contention at transaction start
- **Periodic WAL checkpoints** every 50 successful writes (PASSIVE mode)

This avoids the "convoy effect" where SQLite's deterministic internal backoff
causes all competing writers to retry at the same intervals.

```

_WRITE_MAX_RETRIES = 15

_WRITE_RETRY_MIN_S = 0.020   # 20ms

_WRITE_RETRY_MAX_S = 0.150   # 150ms

_CHECKPOINT_EVERY_N_WRITES = 50

```


## Common Operations

### Initialize

```python

from hermes_state import SessionDB



db = SessionDB()                           # Default: ~/.hermes/state.db

db = SessionDB(db_path=Path("/tmp/test.db"))  # Custom path

```

### Create and Manage Sessions

```python

# Create a new session

db.create_session(

    session_id="sess_abc123",

    source="cli",

    model="anthropic/claude-sonnet-4.6",

    user_id="user_1",

    parent_session_id=None,  # or previous session ID for lineage

)



# End a session

db.end_session("sess_abc123", end_reason="user_exit")



# Reopen a session (clear ended_at/end_reason)

db.reopen_session("sess_abc123")

```

### Store Messages

```python

msg_id = db.append_message(

    session_id="sess_abc123",

    role="assistant",

    content="Here's the answer...",

    tool_calls=[{"id": "call_1", "function": {"name": "terminal", "arguments": "{}"}}],

    token_count=150,

    finish_reason="stop",

    reasoning="Let me think about this...",

)

```

### Retrieve Messages

```python

# Raw messages with all metadata

messages = db.get_messages("sess_abc123")



# OpenAI conversation format (for API replay)

conversation = db.get_messages_as_conversation("sess_abc123")

# Returns: [{"role": "user", "content": "..."}, {"role": "assistant", ...}]

```

### Session Titles

```python

# Set a title (must be unique among non-NULL titles)

db.set_session_title("sess_abc123", "Fix Docker Build")



# Resolve by title (returns most recent in lineage)

session_id = db.resolve_session_by_title("Fix Docker Build")



# Auto-generate next title in lineage

next_title = db.get_next_title_in_lineage("Fix Docker Build")

# Returns: "Fix Docker Build #2"

```


## Full-Text Search

The `search_messages()` method supports FTS5 query syntax with automatic
sanitization of user input.

### Basic Search

```python

results = db.search_messages("docker deployment")

```

### FTS5 Query Syntax

| Syntax | Example | Meaning |
|--------|---------|---------|
| Keywords | `docker deployment` | Both terms (implicit AND) |
| Quoted phrase | `"exact phrase"` | Exact phrase match |
| Boolean OR | `docker OR kubernetes` | Either term |
| Boolean NOT | `python NOT java` | Exclude term |
| Prefix | `deploy*` | Prefix match |

### Filtered Search

```python

# Search only CLI sessions

results = db.search_messages("error", source_filter=["cli"])



# Exclude gateway sessions

results = db.search_messages("bug", exclude_sources=["telegram", "discord"])



# Search only user messages

results = db.search_messages("help", role_filter=["user"])

```

### Search Results Format

Each result includes:
- `id`, `session_id`, `role`, `timestamp`
- `snippet` — FTS5-generated snippet with `>>>match<<<` markers
- `context` — 1 message before and after the match (content truncated to 200 chars)
- `source`, `model`, `session_started` — from the parent session

The `_sanitize_fts5_query()` method handles edge cases:
- Strips unmatched quotes and special characters
- Wraps hyphenated terms in quotes (`chat-send` → `"chat-send"`)
- Removes dangling boolean operators (`hello AND` → `hello`)


## Session Lineage

Sessions can form chains via `parent_session_id`. This happens when context
compression triggers a session split in the gateway.

### Query: Find Session Lineage

```sql

-- Find all ancestors of a session

WITH RECURSIVE lineage AS (

    SELECT * FROM sessions WHERE id = ?

    UNION ALL

    SELECT s.* FROM sessions s

    JOIN lineage l ON s.id = l.parent_session_id

)

SELECT id, title, started_at, parent_session_id FROM lineage;



-- Find all descendants of a session

WITH RECURSIVE descendants AS (

    SELECT * FROM sessions WHERE id = ?

    UNION ALL

    SELECT s.* FROM sessions s

    JOIN descendants d ON s.parent_session_id = d.id

)

SELECT id, title, started_at FROM descendants;

```

### Query: Recent Sessions with Preview

```sql

SELECT s.*,

    COALESCE(

        (SELECT SUBSTR(m.content, 1, 63)

         FROM messages m

         WHERE m.session_id = s.id AND m.role = 'user' AND m.content IS NOT NULL

         ORDER BY m.timestamp, m.id LIMIT 1),

        ''

    ) AS preview,

    COALESCE(

        (SELECT MAX(m2.timestamp) FROM messages m2 WHERE m2.session_id = s.id),

        s.started_at

    ) AS last_active

FROM sessions s

ORDER BY s.started_at DESC

LIMIT 20;

```

### Query: Token Usage Statistics

```sql

-- Total tokens by model

SELECT model,

       COUNT(*) as session_count,

       SUM(input_tokens) as total_input,

       SUM(output_tokens) as total_output,

       SUM(estimated_cost_usd) as total_cost

FROM sessions

WHERE model IS NOT NULL

GROUP BY model

ORDER BY total_cost DESC;



-- Sessions with highest token usage

SELECT id, title, model, input_tokens + output_tokens AS total_tokens,

       estimated_cost_usd

FROM sessions

ORDER BY total_tokens DESC

LIMIT 10;

```


## Export and Cleanup

```python

# Export a single session with messages

data = db.export_session("sess_abc123")



# Export all sessions (with messages) as list of dicts

all_data = db.export_all(source="cli")



# Delete old sessions (only ended sessions)

deleted_count = db.prune_sessions(older_than_days=90)

deleted_count = db.prune_sessions(older_than_days=30, source="telegram")



# Clear messages but keep the session record

db.clear_messages("sess_abc123")



# Delete session and all messages

db.delete_session("sess_abc123")

```


## Database Location

Default path: `~/.hermes/state.db`

This is derived from `hermes_constants.get_hermes_home()` which resolves to
`~/.hermes/` by default, or the value of `HERMES_HOME` environment variable.

The database file, WAL file (`state.db-wal`), and shared-memory file
(`state.db-shm`) are all created in the same directory.