Analytics SQL

Custom report charts and the execute_index_sql MCP tool run read-only SQL against your organization's analytics tables. Access is scoped by Postgres Row Level Security: you only ever see your own organization's rows.

Rules

One SELECT (or WITH ... SELECT) statement, no trailing semicolon.
Write snake_case in SQL. Returned row keys are camelCased (conversation_id → conversationId), which is what chart series keys use.
Results are capped at 1,000 rows, ~1 MB of JSON and a 10 second timeout. Add LIMIT and WHERE clauses rather than post-filtering.
Only the tables below are accessible. Anything else fails with permission denied.

Template variables (report charts only)

A saved chart's SQL may use these placeholders. They are substituted with SQL literals from the report toolbar, so write them without quotes:

Variable	Resolves to
`{{from}}`, `{{to}}`	Start and end of the selected period, as `timestamptz`
`{{tz}}`	Selected IANA timezone, e.g. `'Europe/Vilnius'`
`{{bucket}}`	`'day'`, `'week'` or `'month'`
`{{business_ids}}`	Selected businesses as a `uuid[]`

select date_trunc({{bucket}}, created_at at time zone {{tz}}) as bucket,
  count(*) as conversations
from conversation_index
where created_at >= {{from}} and created_at < {{to}}
  and business_id = any({{business_ids}})
group by 1 order by 1

A chart that does not use {{from}}/{{to}} ignores the date range control.

Conversation tables

Table	One row per	Notes
`conversation_index`	conversation	Volume, handoffs, channels, tags, ratings, sentiment, orders. Exclude `is_spam` / `is_hidden` for customer-facing numbers.
`agent_index`	agent × conversation	Message and note counts, reply times.
`agent_index_daily`	agent × day	Message counts.
`message_index`	message	`conversation_id`, `created_at`, `sender`, `text`.
`conversation_purchase_index`	purchase × conversation	Attributed purchases: `purchase_at`, `total_price`, `currency_code`, `order_id`, `provider`, `line_items`, `seconds_after_conversation`, `conversation_rank`.

A purchase attributes to every conversation that preceded it in the same browser session. When summing revenue filter conversation_rank = 1 or you double-count, and exclude provider values ending in -staging (test orders). seconds_after_conversation <= N * 86400 applies an N-day attribution window (the dashboard default is 7 days).

Storefront search tables

All four are keyed by business_id; there is no organization_id column.

Table	One row per	Kept
`search_daily_stats`	business × UTC day × surface	forever
`search_query_daily_stats`	business × UTC day × normalized query	forever
`search_query_logs`	search	14 days
`search_click_logs`	product opened from a search	14 days

search_daily_stats columns: date, surface (panel, fullSearch, voice, image, restApi, …), searches, zero_result_searches, relaxed_searches, clicked_searches, clicks, sum_result_count, sessions, understanding_cache, understanding_llm, understanding_lexicon_only, median_total_ms, p95_total_ms, sum_total_ms, timed_searches.

search_query_daily_stats columns: date, normalized_query, sample_query (a readable spelling), searches, zero_result_searches, clicked_searches, clicks, sum_result_count. This is the source for top and zero-result query reports.

search_query_logs columns: query, normalized_query, surface, session_id, result_count, shown_product_ids, query_understanding, understanding_source, relaxed, total_ms, created_at. search_click_logs columns: search_id, search_created_at, product_id, position, query, normalized_query, surface, session_id, created_at.

Guidance:

Prefer the daily stats tables for anything charted; the raw logs are gone after a fortnight.
The daily tables have a date column instead of created_at. Filter with date >= {{from}}::date and date < {{to}}::date and bucket with date_trunc({{bucket}}, date).
Counts re-aggregate across days with sum(). median_total_ms and p95_total_ms do not; chart them per day or use sum(sum_total_ms) / sum(timed_searches) for a mean.
Click-through rate is clicked_searches / searches. clicks / searches exceeds 1 and is an engagement measure, not a rate.

Virtual try-on tables

Keyed by business_id; there is no organization_id column.

Table	One row per	Kept
`virtual_tryon_daily_stats`	business × UTC day × surface	forever
`virtual_tryon_generations`	try-on attempt	forever

virtual_tryon_daily_stats columns: date, surface, attempts, successes, failures, failures_by_reason (a {reason: count} jsonb bag), unique_product_urls, median_duration_ms, p95_duration_ms, sum_duration_ms, timed_successes.

virtual_tryon_generations columns: created_at, product_url, product_id, config_id (which try-on variant matched the page), status (success / failure), failure_reason (productNotFound, productHasNoImage, imageFetchFailed, baseImageUnavailable, noImageFromModel, unexpectedError), duration_ms, surface.