SQLite State Database & WAL
Relevant source files
The following files were used as context for generating this wiki page:
Hermes Agent persists session metadata, full message history, and model configurations in a central SQLite database. This system replaces legacy JSONL files to enable concurrent access and fast full-text searching across all historical interactions.
The state system utilizes Write-Ahead Logging (WAL) to support simultaneous read and write operations from multiple Hermes processes, such as CLI sessions and gateway workers. It ensures data isolation through a profile-based filesystem structure and provides safety guards to prevent accidental data corruption during testing.
Sources: session-storage.md:1-20, hermes_state.py
Filesystem Architecture & Isolation
Hermes resolves the database location through a hierarchical path resolution system. The primary database file, state.db, typically resides in the ~/.hermes/ directory.
Path Resolution
The get_hermes_home() function determines the active state directory by checking:
- Context-local overrides.
- The
HERMES_HOMEenvironment variable. - Platform-specific defaults (
~/.hermesfor Linux/macOS,%LOCALAPPDATA%/hermesfor Windows).
Profile Isolation
Named profiles create isolated environments. For example, a profile named research stores its state in ~/.hermes/profiles/research/state.db. Processes must preserve the HERMES_HOME variable to maintain isolation. If a subprocess loses this environment variable, it risks falling back to the default profile and corrupting state.
Sources: session-storage.md:23-45, hermes_state_dbfile.py
This diagram shows how the system resolves the physical path for the state database.
Sources: session-storage.md:23-35
Database Schema
The database consists of five main tables and several virtual tables for searching. The schema_version table tracks the current migration state, which is currently version 11.
Sources: session-storage.md:65-75, hermes_state.py
Core Tables
| Table | Purpose | Key Fields |
|---|---|---|
sessions | Metadata for agent conversations | id, source, model, token_count, cost |
messages | History of messages within sessions | id, session_id, role, content, tool_calls |
state_meta | Key-value store for session metadata | key, value |
schema_version | Tracks database migration version | version |
The Messages Entity Relationship
The relationship between sessions and messages follows a standard one-to-many pattern.
This diagram illustrates the relational structure of the core session storage.
Sources: session-storage.md:85-125
Full-Text Search (FTS5)
Hermes uses SQLite FTS5 to enable rapid searching across message content.
Search Mechanisms
- messages_fts: A virtual table using the default FTS5 tokenizer for standard content,
tool_name, andtool_calls. - messages_fts_trigram: A virtual table using a trigram tokenizer to support CJK (Chinese, Japanese, Korean) characters and substring matching.
- Automatic Synchronization: SQLite triggers automatically update the FTS indexes whenever rows are inserted, updated, or deleted in the
messagestable.
Search Filtering
The search_messages() method allows users to filter results by source (e.g., cli, telegram), role (e.g., user, assistant), and specific time ranges.
Sources: session-storage.md:130-155, hermes_state.py
Concurrency and Write-Ahead Logging (WAL)
To handle multiple Hermes processes writing to the same database file, the system implements robust concurrency controls.
WAL Mode
The database operates in WAL (Write-Ahead Logging) mode. This permits multiple processes to read the database simultaneously while a single process writes, preventing common SQLite "Database Locked" errors.
Write Retry Logic
The SessionDB class handles write contention through the following logic:
- Short Timeout: Uses a 1-second SQLite timeout instead of the default 30 seconds.
- Immediate Transactions: Uses
BEGIN IMMEDIATEto acquire a write lock at the start of a transaction. - Random Jitter: Retries failed writes up to 15 times with a random delay between 20ms and 150ms.
- WAL Checkpoints: Executes a checkpoint every 50 successful writes to manage the size of the WAL file.
Sources: session-storage.md:185-205, hermes_state_wal.py
This diagram demonstrates the retry loop used to handle concurrent write requests.
Sources: session-storage.md:195-205
Session Management & Lineage
The database tracks the history and lineage of conversations. When a session becomes too large and undergoes context compression, the gateway creates a new session linked to the original via parent_session_id.
Title Resolution
Users can assign unique titles to sessions. The resolve_session_by_title() method identifies the correct session ID, prioritizing the most recent session in a specific lineage line if titles are duplicated across branches.
Sources: session-storage.md:250-265, hermes_state.py
Maintenance Operations
The system provides tools for database maintenance:
- Pruning:
prune_sessions()removes sessions older than a specified number of days. - Exporting:
export_session()andexport_all()convert database records into JSON-serializable dictionaries for backup or transfer.
Sources: session-storage.md:310-325
Summary
The SQLite State Database & WAL system provides the backbone for persistence in Hermes Agent. By leveraging WAL mode, FTS5 searching, and strict profile isolation, it ensures that session data remains consistent, searchable, and accessible across various interfaces and execution environments.
Sources: session-storage.md:1-20, hermes_state.py