> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wednesdayai.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Session Analytics — Economics, Turns, and FTS

# 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'` — from the run's explicit provenance, else the session key format |
| `is_automated` | BOOLEAN | `1` when `trigger_source != 'user'`; `NULL` for legacy rows |
| `sender_display` | TEXT | Human-readable sender name — from the `__openclaw.ingress` carrier when present, else best-effort from message text |
| `sender_id_raw` | TEXT | Raw provider-level sender id (e.g. Telegram user id, Discord snowflake) — from the carrier when present, else best-effort |

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](https://docs.wednesdayai.dev/next/developers/concepts/tasks).

**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](https://docs.wednesdayai.dev/next/developers/concepts/session-history-hygiene.md) for full details.

### Example provenance query

```sql theme={"dark"}
-- 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

```sql theme={"dark"}
-- 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 | Derived `content_text` of the opening user message (trimmed, injected-context patterns stripped, 4000-char cap) |
| `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 (turns)

```sql theme={"dark"}
-- 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 (tool calls)

```sql theme={"dark"}
-- 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](https://www.sqlite.org/fts5.html) 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:

```sql theme={"dark"}
-- 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.

```sql theme={"dark"}
-- 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.

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](https://docs.wednesdayai.dev/next/developers/concepts/session-pruning.md) · [Session History Hygiene](https://docs.wednesdayai.dev/next/developers/concepts/session-history-hygiene.md) · [Gateway Configuration](https://docs.wednesdayai.dev/next/admin/gateway/configuration.md) · [Context](https://docs.wednesdayai.dev/next/developers/concepts/context.md)*


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.