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:

Surface	Table / Column	What it stores
Economics	`input_tokens`, `output_tokens`, `cache_read_tokens`, `cache_write_tokens`, `model`, `provider` on `ctx_session_entries`	Per-entry token spend and model identity, extracted at write time from `usage_raw` / `model_raw` JSON
Turn aggregates	`ctx_turns`	One row per user-initiated turn: summed token costs, tool-call count, latency, entry range
Tool-call index	`ctx_tool_calls`	One row per tool call block within a message: tool name, call index, tool_use_id
Full-text search	`ctx_fts` (SQLite FTS5) / `content_text_tsv` (Postgres tsvector)	Searchable content text of each session entry

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.

Column	Type	Meaning
`trigger_source`	TEXT	`'user'`, `'heartbeat'`, `'cron'`, `'subagent'`, or `'acp'` — derived from session key format
`is_automated`	BOOLEAN	`1` when `trigger_source != 'user'`; `NULL` for legacy rows
`sender_display`	TEXT	Human-readable sender name from the channel connector (best-effort)
`sender_id_raw`	TEXT	Raw provider-level sender id (e.g. Telegram user id, Discord snowflake)

Derivation rules (see storage-normalize.ts:deriveTriggerSource): :heartbeat key suffix → 'heartbeat'; cron: key prefix → 'cron'; :subagent: segment (UUID-appended) → 'subagent'; :acp: segment (UUID-appended) → 'acp'; all other keys → 'user'. A partial index idx_ctx_session_entries_trigger_source covers trigger_source IS NOT NULL rows for efficient provenance filtering. 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

-- SQLite: count heartbeat turns per session in the last 7 days
SELECT session_id, COUNT(*) AS heartbeat_count
FROM ctx_session_entries
WHERE trigger_source = 'heartbeat'
  AND created_at >= datetime('now', '-7 days')
GROUP BY session_id
ORDER BY heartbeat_count DESC;

Economics columns

ctx_session_entries gains six pre-extracted columns populated at append time by sanitizeAppendEntry():

Column	Type	Source
`input_tokens`	INTEGER / BIGINT	`usage_raw.input_tokens` (via `normalizeUsage()`)
`output_tokens`	INTEGER / BIGINT	`usage_raw.output_tokens`
`cache_read_tokens`	INTEGER / BIGINT	`usage_raw.cache_read_input_tokens`
`cache_write_tokens`	INTEGER / BIGINT	`usage_raw.cache_creation_input_tokens`
`model`	TEXT	`model_raw.modelId ?? model_raw.model`
`provider`	TEXT	`model_raw.provider`

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

-- SQLite: total token spend per model this week
SELECT model, provider,
       SUM(input_tokens) AS in_tok,
       SUM(output_tokens) AS out_tok,
       SUM(cache_write_tokens) AS cache_write
FROM ctx_session_entries
WHERE input_tokens IS NOT NULL
  AND created_at >= datetime('now', '-7 days')
GROUP BY model, provider
ORDER BY in_tok DESC;

-- Postgres: same query
SELECT model, provider,
       SUM(input_tokens) AS in_tok,
       SUM(output_tokens) AS out_tok,
       SUM(cache_write_tokens) AS cache_write
FROM ctx_session_entries
WHERE input_tokens IS NOT NULL
  AND created_at >= NOW() - INTERVAL '7 days'
GROUP BY model, provider
ORDER BY in_tok DESC;

ctx_turns — turn-level aggregates

ctx_turns stores one row per completed user-initiated turn within a session.

Column	Type	Meaning
`session_id`	TEXT	Session identifier
`turn_idx`	INTEGER	1-based ordinal of the user message that opened this turn (over all session history)
`user_seq`	INTEGER	`seq` of the user message entry that opened the turn
`end_seq`	INTEGER	`seq` of the last assistant entry in the turn
`user_text`	TEXT	Cleaned text of the opening user message
`total_input_tokens`	INTEGER	Sum of `input_tokens` across all assistant entries in the turn
`total_output_tokens`	INTEGER	Sum of `output_tokens`
`tool_call_count`	INTEGER	Count of assistant messages within the turn that contained at least one tool-call block
`latency_ms`	INTEGER	Wall-clock milliseconds from the user message timestamp to when `upsertTurn` ran

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

-- SQLite: average tokens per turn for the last 30 days
SELECT AVG(total_input_tokens + total_output_tokens) AS avg_tok_per_turn
FROM ctx_turns
WHERE session_id IN (
  SELECT DISTINCT session_id FROM ctx_session_entries
  WHERE created_at >= datetime('now', '-30 days')
);

-- Postgres: tool-heavy turns (sessions that made > 5 tool calls in one turn)
SELECT session_id, turn_idx, tool_call_count, total_input_tokens
FROM ctx_turns
WHERE tool_call_count > 5
ORDER BY total_input_tokens DESC
LIMIT 20;

ctx_tool_calls — tool-call covering table

ctx_tool_calls indexes every tool-call block in every assistant message entry.

Column	Meaning
`session_id`	Session identifier
`seq`	Entry sequence number within the session
`call_idx`	Position of the tool-call block within the full `contentBlocks` array (0-based; may be sparse if text blocks precede tool-call blocks)
`tool_name`	Tool name as specified in the block
`tool_use_id`	Anthropic `tool_use_id` (nullable for non-Anthropic providers)

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

-- SQLite: count bash tool calls this month
SELECT COUNT(*) FROM ctx_tool_calls
WHERE tool_name = 'bash'
  AND session_id IN (
    SELECT DISTINCT session_id FROM ctx_session_entries
    WHERE created_at >= datetime('now', '-30 days')
  );

-- Postgres: most-used tools across all sessions
SELECT tool_name, COUNT(*) AS call_count
FROM ctx_tool_calls
GROUP BY tool_name
ORDER BY call_count DESC
LIMIT 10;

Full-text search

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). 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 will produce stale entries for sessions that have been pruned (the FTS shadow table lags behind pruneSessionsBefore on very large prunes). Scope all reads:

-- Correct: session_id-scoped FTS query
SELECT e.*
FROM ctx_fts
JOIN ctx_session_entries e ON e.rowid = ctx_fts.rowid
WHERE ctx_fts MATCH 'error'
  AND ctx_fts.session_id = ?
ORDER BY e.seq DESC
LIMIT 20;

-- UNSAFE: do not use without session_id scoping
SELECT * FROM ctx_fts WHERE ctx_fts MATCH 'error'; -- may return stale rows

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.

-- Postgres: full-text search within a session
SELECT *
FROM ctx_session_entries
WHERE session_id = $1
  AND content_text_tsv @@ to_tsquery('english', 'error & connection')
ORDER BY seq DESC
LIMIT 20;

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.

pruneSessionsBefore deletes rows from ctx_tool_calls and ctx_turns for sessions whose entries are pruned, so these tables do not grow unboundedly. Related: Session Pruning · Session History Hygiene · Gateway Configuration · Context

Getting started

Extension points

Channel references

SDK reference

Contributing

Core concepts (developer)

More plugins

Developer reference

Prompt templates

Shohin

Session Analytics — Economics, Turns, and FTS

Session Analytics — Economics, Turns, and FTS

Provenance columns

Example provenance query

Economics columns

Backfill

Example queries

ctx_turns — turn-level aggregates

Example queries

ctx_tool_calls — tool-call covering table

Example queries

Full-text search

SQLite — FTS5

Postgres — tsvector

Storage size implications

​Session Analytics — Economics, Turns, and FTS

​Provenance columns

​Example provenance query

​Economics columns

​Backfill

​Example queries

​ctx_turns — turn-level aggregates

​Example queries

​ctx_tool_calls — tool-call covering table

​Example queries

​Full-text search

​SQLite — FTS5

​Postgres — tsvector

​Storage size implications

Session Analytics — Economics, Turns, and FTS

Provenance columns

Example provenance query

Economics columns

Backfill

Example queries

ctx_turns — turn-level aggregates

Example queries

ctx_tool_calls — tool-call covering table

Example queries

Full-text search

SQLite — FTS5

Postgres — tsvector

Storage size implications