Hermes Agent SQLite Memory Infrastructure

Source Excerpt

Overview

Hermes Agent implements a hybrid persistence architecture combining JSONL session transcripts with SQLite databases for structured indexing, full-text search, and task tracking. This infrastructure emerged organically during the Clark Farming Company Quad buildout (April–May 2026) and is maintained as the primary memory and session-recall system for all agent profiles.

The active system consists of two SQLite databases and a Markdown-based memory layer, managing session history, cross-session search, and per-agent persistent memory across 5 agent profiles (Hermes/CEO, Julius, Cypher, Reacher, Octavius).

Origin Story

Where the Idea Came From

The SQLite memory infrastructure was not designed upfront — it evolved from three converging pressures:

1. The previous platform's memory failure: The team's previous agent platform promised "learning and growing" agent personas but delivered no persistent memory. Sessions reset, context was lost, and agents had no continuity between conversations. This was the primary motivator for building something better.

1. Letta's architectural pattern: Research into AI memory architectures (May 2026) revealed Letta's 3-tier memory model: Core memory (RAM), Recall memory (SQLite/PostgreSQL log for episodic history), and Archival memory (cold storage). This demonstrated that lightweight SQL databases could serve as effective episodic memory backends for agents.

1. Practical necessity at scale: As the Quad grew from 1 to 4 agents (plus CEO), the team needed a way to:

• Search across thousands of past conversations
• Maintain per-agent memory with automatic aging
• Track delegation chains across nested subagent sessions
• Share knowledge between agents without file-system chaos

The Hermes Agent framework's hermes_state.py module implements the SessionDB class — a SQLite-backed session store with FTS5 full-text search. This was the foundational piece that made the rest possible.

What Made Our Implementation Unique

While Letta and others use SQLite for episodic recall, the Hermes implementation adds several distinctive layers:

• Dual FTS5 indexing: Standard tokenization (messages_fts) for exact/full-text search PLUS trigram indexing (messages_fts_trigram) for fuzzy/prefix matching. Most implementations use only one.
• Automated trigger-based indexing: FTS indexes update via AFTER INSERT/UPDATE/DELETE triggers — no background indexing jobs, no manual maintenance.
• Markdown-based persistent memory: Per-agent MEMORY.md and USER.md files with configurable character limits, injected directly into the system prompt each session. Simple, transparent, and immediately editable.
• Session chaining with delegation tracking: The parent_session_id foreign key enables tracing delegation chains across nested subagent sessions.
• Built-in Kanban task tracking: Native kanban.db for Multi-Agent Orchestration task decomposition, assignment, and lifecycle management — no external tool required.
• Zero external dependencies: Everything runs on SQLite3 and local Markdown files — no vector database, no Redis, no Elasticsearch. Single-file, offline-capable, ACID-compliant.

Active Architecture

Database Inventory

Markdown Memory Files

Each agent profile (Julius, Cypher, Reacher, Octavius) has its own memories/ directory under ~/.hermes/profiles/{name}/. The default profile uses ~/.hermes/memories/. These files are injected into the agent's system prompt at session start via the memory tool configuration.

Dual-Storage Pattern

The system uses a write-through dual storage pattern:

User Message → [Agent Loop]
                    ├─→ JSONL append (~/.hermes/sessions/*.jsonl)  [Raw transcript]
                    └─→ SQLite INSERT (state.db)                   [Queryable index]
                              ├─→ messages table (structured data)
                              ├─→ messages_fts (auto-trigger)
                              └─→ messages_fts_trigram (auto-trigger)

• JSONL files: Human-readable, append-only raw transcripts. One file per session. Used by session_search for full conversation reconstruction.
• SQLite: Structured, indexed, searchable. Used for metadata queries, cost tracking, FTS search, and session chaining.

Message counts match exactly between JSONL line counts and messages table rows — the system maintains consistency.

Schema Details

State Database (state.db)

#### Sessions Table

Tracks session lifecycle, token usage, costs, and delegation chains:

CREATE TABLE sessions (
    id TEXT PRIMARY KEY,
    source TEXT NOT NULL,              -- 'cli', 'cron', 'discord', 'telegram', 'tui'
    user_id TEXT,
    model TEXT,
    model_config TEXT,
    system_prompt TEXT,
    parent_session_id TEXT,            -- Delegation chain (self-referencing FK)
    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,
    handoff_state TEXT,
    handoff_platform TEXT,
    handoff_error TEXT,
    FOREIGN KEY (parent_session_id) REFERENCES sessions(id)
);

Indexes:

• idx_sessions_source — filter by platform
• idx_sessions_parent — trace delegation chains
• idx_sessions_started — chronological ordering
• idx_sessions_title_unique — deduplication

Current data: 671 sessions, 30,647 messages (as of May 20, 2026). First session: April 2, 2026.

Session sources breakdown:

#### Messages Table

Stores every message in structured form:

CREATE TABLE messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL REFERENCES sessions(id),
    role TEXT NOT NULL,                -- 'system', 'user', 'assistant', 'tool'
    content TEXT,
    tool_call_id TEXT,
    tool_calls TEXT,                   -- JSON
    tool_name TEXT,
    timestamp REAL NOT NULL,
    token_count INTEGER,
    finish_reason TEXT,
    reasoning TEXT,
    reasoning_details TEXT,
    codex_reasoning_items TEXT,
    reasoning_content TEXT,
    codex_message_items TEXT
);

Index: idx_messages_session(session_id, timestamp) — fast per-session retrieval.

#### FTS5 Virtual Tables

Two full-text search indexes with automatic trigger-based updates:

-- Standard tokenization (exact match, boolean operators)
CREATE VIRTUAL TABLE messages_fts USING fts5(content);

-- Trigram indexing (fuzzy/prefix matching)
CREATE VIRTUAL TABLE messages_fts_trigram USING fts5(
    content,
    tokenize='trigram'
);

Triggers (AFTER INSERT/UPDATE/DELETE on messages):

• Index content: COALESCE(content, ') || ' ' || COALESCE(tool_name, ') || ' ' || COALESCE(tool_calls, ')
• Both FTS tables update atomically with the parent insert — no lag, no orphaned entries

Search examples:

• "memory AND sqlite" → ~259 results (standard FTS5)
• "govern*" → ~3,484 results (trigram prefix)
• "Clark Farming" → ~137 results (phrase search)

#### State Meta Table

Simple key-value store for global state flags:

CREATE TABLE state_meta (
    key TEXT PRIMARY KEY,
    value TEXT
);

Current entries track completed migration operations (e.g., ghost_session_prune_v1, orphaned_compression_finalize_v1).

Kanban Database (kanban.db)

Built-in Hermes Agent Kanban system for Multi-Agent Orchestration task tracking:

CREATE TABLE tasks (
    -- Task definition, assignment, status
);
CREATE TABLE task_runs (
    -- Execution history per task
);
CREATE TABLE task_events (
    -- Lifecycle events (created, assigned, completed, etc.)
);
CREATE TABLE task_comments (
    -- Agent and user comments on tasks
);

Source excerpt truncated at 220 of 393 lines. Open the canonical wiki path above for the full page.