Database Layer

Note

Generated from ONBOARDING.md — the design authority for this project. Edit the source file, not this page.

Design choices

SQLite, not Postgres/external: The plugin runs inside the gateway process with no server setup. SQLite is the only zero-dependency option that survives gateway restarts and has ACID guarantees. No network hop.

WAL mode: Hermes cron jobs run in a ThreadPoolExecutor, so multiple jobs write concurrently from different threads. WAL allows one writer + concurrent readers simultaneously. This is the standard SQLite high-concurrency pattern.

Per-thread connections: threading.local() — each thread opens its own connection to the same WAL DB file. The alternative (a single connection protected by a threading.Lock) would serialize all writes and bottleneck parallel cron jobs.

_schema_lock DDL guard: Even with per-thread connections, SQLite DDL (CREATE TABLE, ALTER TABLE) raises SQLITE_LOCKED if two threads try to migrate simultaneously on a fresh DB. busy_timeout does NOT retry SQLITE_LOCKED errors. Holding a Python threading.Lock() while running _ensure_schema is cheap and eliminates the race.

busy_timeout=30000 before PRAGMA journal_mode=WAL: Switching an empty (or freshly opened) DB to WAL requires a brief exclusive lock. If busy_timeout is not set first and another thread connects at the same time, the WAL switch fails immediately with “database is locked”. The 30s timeout covers CI environments with slow or network-backed filesystems.

synchronous=NORMAL: Safe for WAL mode (SQLite docs confirm this). Faster than FULL while maintaining durability on unclean shutdown at the WAL checkpoint level.

Schema evolution

Migrations run on first connect per thread. Idempotent: check schema_version table before applying.

Version	What was added
v1	Base tables: runs, llm_calls, tool_calls, schema_version
v2	llm_calls: cache_read_tokens, cache_write_tokens, reasoning_tokens, estimated. runs: parent_session_id, estimated_llm_calls
v3	runs.sender_id. New table: budget_alerts (anti-spam ledger)
v4	runs: cache_read_tokens, cache_write_tokens (per-session/cron cache breakdown)
v5	New table: known_free_models (free→paid transition tracking)
v6	New table: free_paid_transitions (historical free→paid flips for the widget rendered inside TelemetryPage)
v7	llm_calls.provider_assumed flag + runs.provider_assumed_calls counter (provider-assumed pricing visibility, issue #42)
v8	New table: model_unavailable_alerts (404s captured via api_request_error — model removed/deprecated by provider)
v9	Repair pass — re-adds any column from v2/v3/v4/v7 that was silently skipped by the old blanket except OperationalError: pass pattern when an ALTER TABLE hit a transient SQLITE_LOCKED (cross-process cron contention). Uses _add_column_if_missing; idempotent.
v10	MoA (Mixture-of-Agents) attribution: llm_calls.moa_preset (preset name for a MoA aggregator call; NULL otherwise) + runs.moa_calls (per-session counter). See § Mixture of Agents (MoA).
v11	New table subagent_edges (parent→child delegation tree) + idx_subagent_edges_parent. Attributes async/nested subagent cost to per_cron_job via a recursive CTE at query time. (#49)
v12	Add runs.profile (per-profile cost attribution) + idx_runs_profile. Captured from ctx.profile_name at on_session_start, backfilled in pre_llm_call (first non-null wins). Adds the per_profile budget scope.
v13	Repair pass — re-creates subagent_edges (+ idx_subagent_edges_parent) via CREATE TABLE IF NOT EXISTS. Heals DBs upgraded from a build that numbered a different migration as v11: the per-profile branch shipped runs.profile as v11 before #56 settled subagent_edges on v11, so those DBs have version=11 applied but no table, and v11’s early-return skips creation forever. No-op on clean v11 DBs.
v14	New table: pricing_snapshots (append-per-change history of the tariffs Hermes core resolves per (provider, model), with provenance: source / source_url / pricing_version / fetched_at). Captured from agent.usage_pricing.get_pricing_entry via core_pricing.py in the post_api_request hook; storage-only (no surface yet). + idx_pricing_snapshots_model.
v15	pricing_snapshots.resolved_model — Canonical model name captured when a dated id was normalized to resolve against the core /models catalog; NULL when the raw name resolved directly.
v16	New tables: endpoint_payload_cache and model_efficiency_cache for standalone-dashboard caches; cache writers share the plugin DB’s WAL + 30s busy-timeout posture.
v17	Data-only repair (issue #89): deletes known_free_models rows with no evidence of being genuinely free (no wildcard provider='', and no llm_calls row for the same model+provider with cost_usd = 0 AND tokens > 0), plus their free_paid_transitions rows. No shape change.
v18	Turn identity + failure ledger. llm_calls: turn_id, task_id, api_request_id. tool_calls: turn_id, tool_call_id. New table llm_call_errors (+ idx_llm_call_errors_session), plus idx_llm_turn on llm_calls(turn_id) and idx_tool_turn on tool_calls(turn_id) — the two query shapes turn identity exists for. See § Turn identity and the failure ledger.

_SCHEMA_VERSION in db.py is the latest applied version — keep it in lockstep with the highest _migrate_vN. test_schema_idempotent asserts the count of schema_version rows equals _SCHEMA_VERSION, so adding a migration without bumping the constant (or vice versa) fails CI.

Turn identity and the failure ledger (schema v18)

Two related additions, both fed by hooks that already fired — no new hook, no new LLM call, no content capture.

1. Turn identity on the observation rows. turn_id ({session_id}:{task_id}:{8hex}, built in agent/turn_context.py) has always been on every per-call hook payload; the plugin stored it only in subagent_edges.parent_turn_id. v18 persists it (plus task_id and api_request_id on llm_calls, and tool_call_id on tool_calls) so a turn’s calls and tools are recoverable with a plain GROUP BY turn_id:

SELECT turn_id, COUNT(*), SUM(cost_usd), SUM(tokens_in + tokens_out)
FROM llm_calls WHERE session_id = ? GROUP BY turn_id;

Deliberately no materialized rollup table: per-turn aggregation is a query, not a write path. All five columns are nullable and historical rows keep NULL — turn ids were never captured for them, and NULL is the honest value. Empty turn ids from the host are normalised to NULL by the handlers (turn_id or None); the ledger’s text columns (model, provider, error_type, error_message, reason) store '' when the payload omits them.

The delegation tree joins on the same key:

SELECT se.child_session_id, lc.turn_id
FROM subagent_edges se JOIN llm_calls lc ON lc.turn_id = se.parent_turn_id;

Assert the precondition before reading that join as a health check. parent_turn_id is populated on only some edges (2 of the 4 rows in the live dataset at v0.9.0); an edge whose parent_turn_id is '' can never join, so a bare COUNT(*) over every edge is a false negative, not a pass. Filter the left side (WHERE se.parent_turn_id > '') — > 0 is not valid for a TEXT column.

2. Per-call failure ledger (llm_call_errors). llm_calls only ever saw the success path; failures were reduced to the 404-class aggregate in model_unavailable_alerts (keyed model+provider, no session, no per-attempt row). v18 adds an append-only ledger written from api_request_error for every firing — any status, retryable or not — before the 404-specific branch. One row per failed attempt: no dedupe, N attempts → N rows.

The two writes for a 404 are intentional: history (llm_call_errors) and the alert (model_unavailable_alerts + the one-shot pre_llm_call injection). The alert’s filter, upsert, and queueing are unchanged.

Retention: this table has no cap and no prune. One row per failed attempt means a 429 retry storm, or a cron job failing auth on every run, grows llm_call_errors without bound — on a long-lived instance it accumulates rows faster than any other table the plugin writes. Left deliberate for now: the ledger exists to make failures visible, and choosing what to discard (and whether the answer differs for 429 vs auth vs 404, or should be a count rather than an age) is its own decision rather than something to guess at in the same change that adds the table. Tracked as a follow-up; until it lands, pruning is yours:

DELETE FROM llm_call_errors WHERE ts < '2026-01-01T00:00:00+00:00';

Read the payload as it actually arrives. The emitter (agent/api_request_hooks.py::_invoke_api_request_error_hook) sends:

error   = {"type": error_type, "message": error_message}   # not top-level
request = {"method": "POST", "body": {...api kwargs...}}   # model in body["model"]
api_duration                                           # SECONDS, not ms

There is no top-level error_type / error_message / api_kwargs / api_start_time. The handler declares the flat names anyway as a fallback so an older host keeps working, but reads the nested dict first — reading only the flat names is what made the pre-v18 404 path store an empty model_unavailable_alerts.error_message for a live-shaped 404. error_message is truncated to 500 chars at the write boundary (db.record_llm_call_error), so every caller inherits the bound the column advertises.

Two payload-integrity rules in that handler, both easy to break:

• api_duration is untrusted input. A non-finite float (inf / nan) or a non-numeric string raises in float() / round(). Converted outside the ledger’s own try it killed the ledger row and the 404 alert — a failure that leaves no trace, the class this table exists to close. The conversion has its own except (TypeError, ValueError, OverflowError) and leaves duration_ms NULL, which is the honest value for a duration we could not read.
• retryable defaults to None, and the 404 alert guard is if retryable is not False: — not truthiness. None means “the host did not say” and is stored as NULL, not 1: a 1 asserts a classification the payload never made. The guard has to be written that way: with the default None, if retryable: is falsy for unknown, so the alert would fire on every unclassified 404. is not False leaves the alert identical for every payload that states the field (True → no alert, False → alert) and keeps unknown out of the alert path exactly as the old True default did.

Adding a column or table — mandatory checklist

Any PR that adds, removes, renames, or retypes a column or table MUST follow this checklist. Skipping a step has already wedged user DBs in production once (the v7 provider_assumed incident); the rules below exist specifically to prevent the recurrence.

1. Write a new _migrate_vN. Never edit _ensure_schema to alter the shape of an existing table — that only runs on fresh DBs, so every upgrading user keeps the old shape. New shape ⇒ new migration function.
2. Bump _SCHEMA_VERSION to N in db.py (kept in lockstep — test_schema_idempotent enforces this).
3. Use _add_column_if_missing for ALTER TABLE ADD COLUMN. Do not write a raw ALTER with a try/except OperationalError: pass — that is exactly the pattern that caused the v7 incident. The helper is idempotent (re-running it on an already-migrated DB is a no-op) and lets transient errors propagate so the migration is not marked applied prematurely.
4. Guard CREATE TABLE with IF NOT EXISTS. Same idempotency reason.
5. Reads must tolerate the column being absent if the code path can run before _ensure_schema() (rare, but _get_conn is the only entry — audit any new entry point that bypasses it).
6. Writes that reference the new column must come AFTER the migration is registered. If you add the column in v9 but the matching INSERT/UPDATE was deployed in the same release, you are fine — _ensure_schema runs at connect, before any write. But if you reference the column from a SELECT query in stats.py or a dashboard endpoint and the migration silently skipped, the query returns nothing or errors — write a test that exercises the upgrade path from the previous schema version (see test_migrate_v9_repairs_missing_column_from_wedged_v7 as the template).
7. Add a row to the migration table above with What was added filled in and a link to the issue / PR.
8. Add a per-version assertion test: test_schema_vN_columns or test_schema_vN_recorded (see existing tests as the pattern).
9. Never drop or rename a column in a single migration. SQLite before 3.35 has no DROP COLUMN, and even modern SQLite forbids it inside a transaction with foreign keys. If a column truly must go, do it across two releases: deprecate (stop writing) in vN, then remove (table-rebuild migration) in vN+1 only after verifying no installed version still writes it.
10. Never reuse a _SCHEMA_VERSION number. Forward-only. If a migration was buggy in production, write _migrate_v(N+1) to repair it (the v9 repair pass is the canonical example) — never edit _migrate_vN in-place to fix the bug, because users who already ran the buggy version have schema_version=N and will skip the corrected code.

Migration ALTER contract (post-v9)

Never wrap an ALTER TABLE ADD COLUMN in a blanket except sqlite3.OperationalError: pass followed by an unconditional INSERT INTO schema_version. OperationalError also covers SQLITE_LOCKED (which busy_timeout does not retry across processes — the _schema_lock only protects threads of the same process; cron jobs run in separate processes and will contend on first connect after an upgrade) and I/O errors. Swallowing those and then marking the version applied permanently wedges the DB without raising. Use _add_column_if_missing instead — it checks pragma_table_info first and lets any real OperationalError propagate, so the surrounding migration is NOT marked applied and the next connect retries. This was the root cause of the v7 provider_assumed silent-skip incident.

_ensure_run_row — lazy insert (added v0.3.1)

Problem: For sessions that start on a running gateway BEFORE the plugin is enabled (or on session continuation where on_session_start doesn’t fire), the UPDATE runs WHERE session_id = ? calls in record_llm_call and end_run were silent no-ops — no row existed. /stats and /budget under-reported.

Solution: _ensure_run_row(session_id, ts) does INSERT OR IGNORE INTO runs (session_id, started_at, status) VALUES (?, ?, 'running') before every UPDATE. If the row already exists (happy path from on_session_start), it’s a no-op.

Called by: record_llm_call and end_run. Not needed in start_run (it already uses INSERT OR IGNORE).

runs table key columns

Column	Notes
session_id	PK. CLI: {YYYYMMDD_HHMMSS}_{uuid6}. Cron: cron_{job_id}_{YYYYMMDD_HHMMSS}.
cron_job_id	Extracted from session_id via anchored regex (see Cron regex below). NULL for non-cron.
estimated_llm_calls	Count of calls where provider returned usage=None. Used for budget degradation.
parent_session_id	Dormant by design — kept in the schema but never populated. Parent→child attribution is handled by the separate subagent_edges table (v11) instead; see § Subagent Architecture.
sender_id	Set from pre_llm_call.sender_id. First non-null wins (COALESCE(sender_id, ?)).
profile	Set from ctx.profile_name (the only profile-identity surface Hermes exposes to plugins — NOT encoded in session_id, no HERMES_PROFILE env var). Captured at on_session_start, backfilled in pre_llm_call via db.set_profile (COALESCE(profile, ?), first non-null wins). Powers the per_profile budget scope. NULL for pre-v12 rows and sessions whose profile could not be resolved.
model / provider	Set from first record_llm_call. COALESCE(model, ?) so they’re never overwritten.