ClickHouse and Subtext: From a Query to the Session

ClickHouse is a columnar analytics database: you query billions of clickstream events with SQL and get back the shape of what happened at scale — a spiking error event, a funnel drop, a cohort that stopped converting. It can’t tell you what one of those users actually experienced. Subtext can. Store the Subtext session URL as a column on your events table and every result row deep-links straight into that person’s real session — your agent reads the subtext_url, opens the session, and walks the decisive moments with screenshots, the component tree, network, and console. ClickHouse detects; Subtext diagnoses.

This guide assumes the Subtext capture snippet is installed and capturing sessions, and that your clickstream events are already flowing into a ClickHouse table carrying a subtext_url property. The value is stamped onto events at capture time by whatever collector feeds your warehouse (Snowplow, Hightouch, Segment, or your own inserts). ClickHouse is the store, not the source — it holds the link, it doesn’t generate it.

Attach the session URL

The job here is to land subtext_url as a first-class column and keep it fast to filter on, so any SQL result row carries a clickable session alongside it.

Give the link its own column

Add a plain String column. The value is a long, high-cardinality signed URL, so never wrap it in LowCardinality — that builds a dictionary the size of your session count and wins nothing.

CREATE TABLE events
(
    event_time  DateTime,
    user_id     String,
    event       String,
    subtext_url String DEFAULT ''   -- Subtext session link, stamped at capture
)
ENGINE = MergeTree
ORDER BY (event, event_time);

ALTER TABLE events ADD COLUMN subtext_url String DEFAULT '';

When you land raw JSON

If you land whole event payloads as a JSON string and shred fields later, pull subtext_url into its own column with a MATERIALIZED expression so it is computed once at insert and indexes like any other column:

ALTER TABLE events
    ADD COLUMN subtext_url String
    MATERIALIZED JSONExtractString(properties, 'subtext_url');

Adding the column is instant — no data is rewritten. For rows written before the ALTER, ClickHouse computes the expression on the fly at read time from properties; run ALTER TABLE events MATERIALIZE COLUMN subtext_url to physically write the values into existing parts so reads stop paying the extraction cost.

Query for sessions

Every filter you already run to find interesting behavior now returns a clickable session alongside it:

SELECT user_id, subtext_url, event, event_time
FROM events
WHERE event = 'checkout_error'
  AND subtext_url != ''
ORDER BY event_time DESC
LIMIT 20;

Don’t declare subtext_url as LowCardinality(String) — it is unique per session, so the dictionary just bloats memory. Don’t expose the column to roles or shared views that untrusted users can read: the signed URL grants replay access, so treat it like a credential and keep it in internal, authenticated surfaces. Don’t assume historical rows carry it — rows written before the collector started stamping subtext_url have no value to store, so backfill from your event archive if you need older sessions. And don’t try to generate the URL inside ClickHouse: it originates in the browser via FS('getSession', { format: 'url.now' }) in your collector — ClickHouse only stores and serves it.

From signal to session

However the query first surfaces a problem — a dashboard tile, an ad-hoc triage, a scheduled model — the path is the same: get from the result row to the subtext_url, then hand it to your agent for a Detect-vs-Diagnose read. The query says these users errored or dropped; the session shows what they actually hit.

A dashboard tile spikes

An error-event or drop-off tile on a ClickHouse-backed dashboard (Grafana, Metabase, or an internal panel) jumps above trend for a specific event.Pull the offending sessions straight from the events table:

SELECT user_id, subtext_url, event_time
FROM events
WHERE event = 'checkout_error'
  AND event_time > now() - INTERVAL 1 HOUR
  AND subtext_url != ''
ORDER BY event_time DESC
LIMIT 20;

Grab a few links, then hand them to your agent:

Our ClickHouse dashboard shows a spike in `checkout_error` in the last hour. Here are session URLs
for users who hit it:
<PASTE 3-4 subtext_urls>

Review them and find the common failure point. Detect-vs-Diagnose: the data says they errored —
what does each session actually show at that moment? One synthesized hypothesis at the end.

Ad-hoc triage of a suspicious state

A customer says “the page just froze,” and you want to see everyone who reached the same state.Filter to the affected users and grab the links:

SELECT user_id, subtext_url, event, event_time
FROM events
WHERE user_id IN (<affected users>)
  AND subtext_url != ''
ORDER BY event_time DESC
LIMIT 50;

A user reported the app froze on this flow. Here's a session where it happened:
<PASTE subtext_url>

Open it and tell me what the UI, console, and network were doing at the freeze. Was it a bug,
a slow request, or expected behavior?

A scheduled retention or funnel query flags a cohort

A cohort converts or retains below trend in your weekly model, and you want the human story before you write it up.Join the cohort back to the events table for their session links:

SELECT user_id, any(subtext_url) AS subtext_url
FROM events
WHERE user_id IN (<dropped cohort>)
  AND subtext_url != ''
GROUP BY user_id
LIMIT 20;

This cohort dropped out of the activation funnel. Here are their sessions:
<PASTE 3-4 subtext_urls>

Review them and tell me whether the drop was a deliberate choice or something broke/confused them.
Consolidate into one hypothesis I can put in the retention readout.

For a cohort investigation, ask your agent to roll the findings into one proof document (doc-create) — a screenshot per user and one shareable link — then drop the summary into the dashboard annotation or the ticket so the next person sees the human story behind the number. If the “drop” turns out to be a deliberate user choice with no bug, say so — that redirects effort away from engineering.

For agents

An autonomous agent can run the whole loop against ClickHouse directly: discover the friction with SQL, extract the subtext_url from the same rows, and hand each session to the Subtext MCP. Unlike most tools, ClickHouse ships a first-party data MCP — mcp-clickhouse (ClickHouse/mcp-clickhouse, on PyPI). It connects to your cluster with CLICKHOUSE_HOST / CLICKHOUSE_USER / CLICKHOUSE_PASSWORD and is read-only by default (CLICKHOUSE_ALLOW_WRITE_ACCESS=false), which is exactly what a review agent wants: it can select session links but cannot mutate data.

Discover who is affected

list_databases and list_tables enumerate the cluster and find the events table (paginated, with like / not_like filters). Then run_query aggregates to find the worst hotspot:

-- step-to-step counts to find the worst drop
SELECT event, count(DISTINCT user_id) AS users
FROM events
WHERE event_time > now() - INTERVAL 7 DAY
GROUP BY event
ORDER BY users DESC;

Extract the session URL

run_query does discover and extract at once — the filter that finds the behavior also returns the link:

-- worst recent error events, with a session per user
SELECT user_id, subtext_url, event, event_time
FROM events
WHERE event = 'checkout_error'
  AND event_time > now() - INTERVAL 24 HOUR
  AND subtext_url != ''
ORDER BY event_time DESC
LIMIT 25;

Hand off to Subtext

For each URL, call review-open(session_url=<subtext_url>) on the Subtext MCP and step through the session for a Detect-vs-Diagnose read: the query says they errored; the session shows what they actually hit.

subtext_url only round-trips if the collector attached it at capture time — rows written before the collector started stamping subtext_url carry an empty string, so always filter subtext_url != ''. Keep run_query in read-only mode; a review agent never needs write access. And because it is a high-cardinality column, always LIMIT and order your queries — don’t pull every session.

Session Review overview — what your agent does once a session is open.
Install the capture snippet — required before any session URL exists to attach.

Get Started

Install by Framework

Session Review

Privacy

Integrations

Recipes

API reference

ClickHouse and Subtext: From a Query to the Session

Attach the session URL

Give the link its own column

When you land raw JSON

Query for sessions

From signal to session

For agents

​Attach the session URL

​Give the link its own column

​When you land raw JSON

​Query for sessions

​From signal to session

​For agents

​Related

Attach the session URL

Give the link its own column

When you land raw JSON

Query for sessions

From signal to session

For agents

Related