Skip to main content

Session Analytics — Economics, Turns, and FTS

The ctx storage layer (both SQLite and Postgres backends) ships four analytics surfaces alongside the core ctx_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 with task_runs.trigger_source, a different column on the task-pipeline run table with its own values (task-dispatcher, idle, schedule) — see Task pipeline.
Important: the production recall query (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 runs backfillTokenCounts() 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) when usage_raw is present but its format is unrecognised.
  • model = '' (empty string, not NULL) when model_raw is present but yields no extractable model name.
This means 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)

SQLite — FTS5

WednesdayAI uses a FTS5 external-content virtual table backed by ctx_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:
FTS5 availability depends on the Node.js SQLite build. On builds without FTS5, init logs a warning and continues without FTS — all other storage features work normally.

Postgres — tsvector

Postgres uses a generated content_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 to ctx_session_entries but much smaller (one row per tool call block vs. one row per entry).
  • ctx_fts (SQLite FTS5 shadow table): proportional to content_text length across all entries. Typically 5–20% of the main ctx_session_entries table size.
  • Economics columns: 6 integer/text columns added to the existing wide ctx_session_entries rows — minimal overhead.
Core never deletes conversation rows (ADR 0098): 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