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

# ClickHouse and Subtext: From a Query to the Session

> Keep the Subtext session URL as a queryable column in ClickHouse, then jump from any SQL result — a spiking error event, a dropped cohort — straight into the user's real 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.

<Note>
  This guide assumes the [Subtext capture snippet](/docs/install/overview) 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.
</Note>

## 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.

<CodeGroup>
  ```sql New table theme={null}
  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);
  ```

  ```sql Existing table theme={null}
  ALTER TABLE events ADD COLUMN subtext_url String DEFAULT '';
  ```
</CodeGroup>

### 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:

```sql theme={null}
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:

```sql theme={null}
SELECT user_id, subtext_url, event, event_time
FROM events
WHERE event = 'checkout_error'
  AND subtext_url != ''
ORDER BY event_time DESC
LIMIT 20;
```

<Warning>
  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.
</Warning>

## 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.

<AccordionGroup>
  <Accordion title="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:

    ```sql theme={null}
    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:

    ```text theme={null}
    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.
    ```
  </Accordion>

  <Accordion title="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:

    ```sql theme={null}
    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;
    ```

    ```text theme={null}
    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?
    ```
  </Accordion>

  <Accordion title="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:

    ```sql theme={null}
    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;
    ```

    ```text theme={null}
    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.
    ```
  </Accordion>
</AccordionGroup>

<Tip>
  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.
</Tip>

## 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.

<Steps>
  <Step title="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:

    ```sql theme={null}
    -- 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;
    ```
  </Step>

  <Step title="Extract the session URL">
    `run_query` does discover and extract at once — the filter that finds the behavior also returns the link:

    ```sql theme={null}
    -- 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;
    ```
  </Step>

  <Step title="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.
  </Step>
</Steps>

<Note>
  `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.
</Note>

## Related

* [Session Review overview](/docs/session-review/overview) — what your agent does once a session is open.
* [Install the capture snippet](/docs/install/overview) — required before any session URL exists to attach.