Session Analytics — Economics, Turns, and FTS
The ctx storage layer (both SQLite and Postgres backends) ships four analytics surfaces alongside the corectx_session_entries table:
Provenance columns
ctx_session_entries records four turn-provenance columns that identify who or what triggered each entry. These are populated at write time by normalizeSessionEntryForStorage() and are used to filter automated turns from recall and analytics queries.
Derivation rules (see
storage-normalize.ts:deriveTriggerSource): the writer passes the run’s explicit provenance, and a user row’s own __openclaw.ingress carrier trigger decides first — a cron/heartbeat carrier (including a system turn steered into another run) persists as that trigger, a human steer carrier stays 'user'. Inside a system run every row takes the run’s cron/heartbeat provenance, synthesized queue and memory prompts included, except a human message steered into it. Without an explicit value, session-key derivation applies: :heartbeat key suffix → 'heartbeat'; an isolated cron key (cron:<jobId> or agent:<id>:cron:<uuid>, matched by isCronTriggerSessionKey so a direct peer id that merely contains :cron: stays 'user') → 'cron'; :subagent: segment (UUID-appended) → 'subagent'; :acp: segment (UUID-appended) → 'acp'; all other keys → 'user'. An explicit human run (a chat.send into an existing cron or heartbeat session key) stays 'user'.
On carrier-bearing user rows the sender columns come only from the structured __openclaw.ingress carrier (trusted.senderId, untrusted.sender) — never parsed out of the message text, so a sender label typed into the message body cannot set them. Legacy rows fall back to parsing the Sender (untrusted metadata) text block.
A partial index idx_ctx_session_entries_trigger_source covers trigger_source IS NOT NULL rows for efficient provenance filtering.
Not to be confused withImportant: the production recall query (task_runs.trigger_source, a different column on the task-pipeline run table with its own values (task-dispatcher,idle,schedule) — see Task pipeline.
readByKey) and the last-user-turn analytics query both apply AND (trigger_source = 'user' OR trigger_source IS NULL) — automated turns (heartbeat, cron, subagent, acp) are excluded automatically. Rows written before this schema version have trigger_source = NULL and are treated as user turns via the IS NULL coalescing. See Session History Hygiene for full details.
Example provenance query
Economics columns
ctx_session_entries gains six pre-extracted columns populated at append time by sanitizeAppendEntry():
All six columns are
NULL when the entry has no associated model call (e.g. user messages, tool results). A partial index ctx_session_entries_economics_idx covers rows where input_tokens IS NOT NULL for efficient analytics queries.
Backfill
For pre-existing databases (entries written before this schema version), WednesdayAI runsbackfillTokenCounts() once at startup as a background fire-and-forget operation. It paginates through all rows where usage_raw IS NOT NULL AND input_tokens IS NULL (or equivalent for model_raw) in 1 000-row batches. For a DB with 500 k entries, this adds ~10–30 s of background CPU at startup; the gateway serves requests normally while the backfill runs.
Sentinel values prevent infinite re-scanning:
input_tokens = 0(not NULL) whenusage_rawis present but its format is unrecognised.model = ''(empty string, not NULL) whenmodel_rawis present but yields no extractable model name.
WHERE input_tokens IS NULL reliably identifies un-backfilled rows on subsequent startups.
Example queries
ctx_turns — turn-level aggregates
ctx_turns stores one row per completed user-initiated turn within a session.
PRIMARY KEY (session_id, turn_idx). The upsert uses ON CONFLICT … DO UPDATE so calling upsertTurn(sessionId) repeatedly is idempotent.
turn_idx semantics: turn_idx is the 1-based count of human-initiated role='user' AND entry_type='message' entries over all session history. Automated turns (heartbeat, cron, subagent, acp) are excluded from the count — getUserTurnCount() applies the same trigger_source = 'user' OR IS NULL filter so automated upsertTurn calls do not inflate turn_idx or create phantom rows. On sessions that predate the ctx-storage-phase3 schema, earlier turns have no ctx_turns row (ADR 0019: forward-only population, no backfill). Queries across mixed-era sessions must account for gaps.
Example queries (turns)
ctx_tool_calls — tool-call covering table
ctx_tool_calls indexes every tool-call block in every assistant message entry.
PRIMARY KEY (session_id, seq, call_idx). Covering index ctx_tool_calls_tool_name_idx ON (tool_name, session_id) supports bare tool-name queries.
Tool-call blocks are recognised by type value: toolCall (primary), toolUse (alternative), functionCall (OpenAI), or tool_use (raw Anthropic API — stored verbatim by primary-write-interceptor).
Example queries (tool calls)
Full-text search
SQLite — FTS5
WednesdayAI uses a FTS5 external-content virtual table backed byctx_session_entries. The ctx_fts table indexes content_text (the plain-text content extracted from each entry). content_text is a derived, length-capped search field, not a transcript: it is extracted at write time from the stored message text with injected-context patterns stripped and truncated at 4000 characters. On carrier-bearing user rows the indexed text is the raw message as received.
Guardrail (ADR 0020): all FTS queries must include a session_id equality filter. A global ctx_fts MATCH ? without scoping to a session returns results across all sessions and can surface stale entries when the FTS shadow table lags behind bulk maintenance. Scope all reads:
Postgres — tsvector
Postgres uses a generatedcontent_text_tsv tsvector column (GIN index content_text_tsv_gin_idx) on ctx_session_entries, plus a content_blocks JSONB GIN index.
Storage size implications
For operators with large deployments:ctx_turns: small — O(turns per session), typically kilobytes per session.ctx_tool_calls: O(tool calls) — for agentic sessions with many tool calls, size is proportional toctx_session_entriesbut much smaller (one row per tool call block vs. one row per entry).ctx_fts(SQLite FTS5 shadow table): proportional tocontent_textlength across all entries. Typically 5–20% of the mainctx_session_entriestable size.- Economics columns: 6 integer/text columns added to the existing wide
ctx_session_entriesrows — minimal overhead.
ctx_tool_calls and ctx_turns rows persist for the life of the session, including sessions whose index entry has been retired.
Related: Session Pruning · Session History Hygiene · Gateway Configuration · Context