AI Knowledge & LogicAI Analytics & Insights

Analytics SQL

The tables, template variables and rules behind execute_index_sql and custom report charts.

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 30 second timeout. Add LIMIT and WHERE clauses rather than post-filtering.
  • Only the tables below are accessible. Anything else fails with permission denied.
  • execute_index_sql allows 3 concurrent queries per organization. Report charts and preview_metric_query share a separate limit of 4. Extra queries wait up to 15 seconds for a free slot, then are rejected. Run authoring previews one after another rather than firing many in parallel.
  • execute_index_sql and uncached previews share a limit of 60 requests per organization per minute.

Writing fast queries

Large organizations have millions of conversations and tens of millions of messages. Queries that scan an organization's whole history time out. The filters that use an index are:

  • A created_at window. Always bound conversation_index and message_index by created_at >= ... and created_at < ..., and keep the window as short as the question allows (days for message_index, weeks to a few months for conversation_index). min(), max() or count(*) over all time is a full scan.
  • business_id = '...' together with the created_at window.
  • conversation_id on message_index, for example where conversation_id in (...). This is the fastest way to read transcripts.
  • public_id = '...' or public_id in (...) on conversation_index.
  • is_hidden = false on conversation_index. Most of its indexes only cover non-hidden conversations.

Filters that never use an index, and so must be combined with the filters above:

  • text ilike '%...%' on message_index. Bound it by created_at to a few days, or first find the conversations in conversation_index and filter message_index by their conversation_id.
  • Array filters such as 'x' = any(tag_names), tag_names @> array['x'] and &&, and jsonb filters.
  • channel = '...' without business_id.

When a query times out, check its filters and aggregation before retrying. A narrower date range or business selection can help investigate, but changes the question: do not silently save those changes as a performance fix.

Report ownership and the Metrics layout

A report is either shared with the organization or personal to the user who created it. Pass personal: true to create_metric_report for a personal report. Anyone who can view metrics can create and edit their own personal reports; shared reports also need the Modify settings permission. Other users' personal reports are never listed or editable. Shared reports and their chart data are also available over the REST API for exporting metrics to other systems.

Tab order and hidden tabs on the Metrics page belong to each user. Use get_metrics_layout to see every available tab, including built-in tabs such as builtin:overview, and update_metrics_layout to reorder, hide or show them. Hiding a tab does not delete it. delete_metric_report and delete_metric_chart delete permanently; reorder_metric_charts sets the chart order inside a report for everyone who opens it.

Checking a report before saving

Use preview_metric_query with the SQL template and intended toolbar parameters before calling save_metric_chart or changing a chart's SQL. It executes once through the same tenant permissions, conversation cutoffs and query limits as the saved chart, bypassing the metric result cache. It returns the rows, concrete parameters, execution timestamp and performance feedback:

  • durationMs measures server elapsed time, including queue wait, template resolution and result processing. It is not database execution time or browser rendering time. A request taking at least 5 seconds receives an advisory slow_query warning.
  • resultCacheHit identifies cached results from run_saved_metric_chart. Cached timing does not establish query performance. A preview bypasses this result cache; database pages may still be warm.
  • rowLimitReached means the result contains 1,000 rows and may be incomplete. Do not treat the sum of a capped breakdown as the full total.

Preview the intended period (30 days by default), plus longer ranges and selected businesses relevant to the report. Reuse the exact returned parameters when comparing revisions. Inspect the values and preview the chart with render_chart before saving; an empty result does not verify a calculation on populated data. A fast single query does not establish the speed of a whole report loading its charts concurrently.

For slow or capped queries, make a small number of revisions that preserve the question:

  • Aggregate directly to the chart's grain. A period-total chart does not need a daily grouping followed by a second sum.
  • Apply date, business and visibility filters before expanding JSON. Add a JSON column's IS NOT NULL predicate only when missing values contribute no rows to the original calculation; do not remove zero-value observations from an average or denominator.
  • For category summaries, compute period totals first, then keep top categories and combine the rest into an Other bucket. Retain all categories' contribution to the total. An arbitrary LIMIT on daily category rows silently loses data.
  • Verify the rewritten results over the same period and selection. Live data can change between requests; do not claim exact equivalence from different snapshots.

If two sensible revisions still leave slow or incomplete results, record the concrete query and timings for an engineering review. Do not loosen permissions, change cutoff behavior, hide slow execution behind cached timings, or keep retrying the same expensive query. Query plans and database tuning remain an engineering diagnostic; the authoring tools do not expose raw plans or settings.

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:

VariableResolves to
{{from}}, {{to}}Start and end of the selected period, as timestamptz
{{tz}}Selected IANA timezone, e.g. 'Europe/Vilnius'
{{bucket}}'hour', 'day', 'week' or 'month'
{{business_ids}}Selected businesses as a uuid[]
{{conversation_filters:c}}The toolbar's conversation filters as a boolean predicate
{{your_filter_key}}A report's custom filter: the selected values as text[], or NULL
select date_trunc({{bucket}}, c.created_at at time zone {{tz}}) as bucket,
  count(*) as conversations
from conversation_index c
where c.created_at >= {{from}} and c.created_at < {{to}}
  and c.business_id = any({{business_ids}})
  and {{conversation_filters:c}}
group by 1 order by 1

A chart that does not use {{from}}/{{to}} ignores the date range control. Keep the business predicate even when the toolbar selects all businesses. The toolbar resolves the current reportable businesses; removing that predicate can change results after a business moves or its configuration changes. Tenant isolation does not replace the report's business selection.

Toolbar filters

Every report shares one filter bar. A report only offers the filters its charts use, and a chart that does not use a selected filter shows that it is not filtered by it. Wire each chart to the filters that make sense for it.

Built-in conversation filters. Add {{conversation_filters:c}} to any chart over conversation_index, where c is that table's alias (write {{conversation_filters}} when it is not aliased). It expands to the same conditions the built-in Metrics tabs use for channel, tag, assignee, team, language, type, sender, priority, handed off, closed, snoozed, topic, workflow, variant, task, tool, handoff reason, data collection and order filters, and to true when none are set. It adds no date, business, spam or hidden-conversation conditions; keep those in the chart. The language filter matches the languages the conversation's messages were detected in (languages), not language, which is the language of the store or page the conversation started on. For charts over other conversation tables, join conversation_index and filter there:

select a.agent_id, sum(a.message_count) as messages
from agent_index a
join conversation_index c using (conversation_id)
where c.created_at >= {{from}} and c.created_at < {{to}}
  and c.business_id = any({{business_ids}})
  and {{conversation_filters:c}}
group by 1

Tables without conversations, such as search or try-on statistics, cannot use these filters; leave the variable out.

Custom filters. When a report needs a dimension the built-in filters lack, such as a search surface or try-on product, define a custom filter on the report (create_metric_report or update_metric_report):

{
  "key": "surface",
  "label": "Surface",
  "options": {
    "type": "query",
    "sql": "select distinct surface as value from search_daily_stats where business_id = any({{business_ids}}) and date >= {{from}}"
  }
}

Options are either static ({"type": "static", "values": [{"value": "web", "label": "Website"}]}) or a query returning a value column and an optional label column. Option queries may use {{from}}, {{to}}, {{tz}} and {{business_ids}}, but no filters. A chart references the filter by its key. The key is NULL when nothing is selected, so always guard it:

and ({{surface}} is null or surface = any({{surface}}))

Preview such a chart with preview_metric_query and the report's reportId. A filter cannot be removed while a chart still references it.

Conversation tables

TableOne row perNotes
conversation_indexconversationVolume, handoffs, channels, tags, ratings, sentiment, orders. Exclude is_spam / is_hidden for customer-facing numbers.
agent_indexagent × conversationMessage and note counts, reply times.
agent_index_dailyagent × dayMessage counts.
message_indexmessageconversation_id, created_at, sender, text.
conversation_metadataconversation × keyid, organization_id, conversation_id, key, value, created_at, updated_at. Join conversation_index for conversation dates and business filters.
conversation_purchase_indexpurchase × conversationAttributed purchases: purchase_at, total_price, currency_code, order_id, provider, line_items, seconds_after_conversation, conversation_rank.

For phone calls, conversation_index.ai_call_duration_seconds is the time in seconds callers spent with the voice AI on the conversation's calls. Time with a person after a transfer is not included. It is NULL for conversations without a phone call and 0 when the call had no voice AI or its length is not reported yet (for example, a call still running), so filter ai_call_duration_seconds > 0 before averaging. Use it for call duration rather than closed_at - created_at, which also counts the time before an automatic close and any reopening. Right after the column was added, older conversations read NULL until the index backfill reaches them.

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

Conversation metadata

conversation_metadata contains the current string value for each conversation and key. All columns, keys and values are available without masking. Report SQL controls what appears in chart results: select aggregates or derived labels when metadata contains credentials or other values that should not be displayed.

Rows are scoped to your organization using the table's organization_id. They also require a visible parent in conversation_index, so the same conversation date cutoffs apply even when querying metadata directly. Rows awaiting an organization ID or a parent index row are unavailable until the existing background processing fills them.

Metadata timestamps describe when the metadata row was created or updated, not when the conversation started. Join to conversation_index and bound c.created_at and c.business_id for period reports. For example, count conversations with and without a nonempty customer reference, including those that have no metadata row:

select
  count(*) filter (where nullif(btrim(m.value), '') is not null) as with_reference,
  count(*) filter (where nullif(btrim(m.value), '') is null) as without_reference
from conversation_index c
left join conversation_metadata m
  on m.conversation_id = c.conversation_id and m.key = 'customer_reference'
where c.created_at >= {{from}} and c.created_at < {{to}}
  and c.business_id = any({{business_ids}})
  and c.is_hidden = false and coalesce(c.is_spam, false) = false
  and {{conversation_filters:c}}

These are conversation counts. Counting unique users requires a stable user identifier and a defined rule for deduplicating it. A conversation can have many metadata keys, so joining without restricting the key can multiply its rows; use exists, one-key joins or pre-aggregation for conversation totals.

Tool and workflow totals for a period

tool_call_counts stores {tool_name: count}. In contrast, received_workflow_variant_counts stores {workflow_name: {variant_name: count}}, so it needs two JSON expansions. Aggregate these counts directly for a period-total chart; daily intermediate groups add work without changing that chart's result.

These examples use the hard-coded tool/workflow metrics' default customer filters: is_hidden = false, customer or unclassified conversations, and the same seven excluded handoff reasons. Dates select the conversation's creation time, not each tool call's execution time. Apply any additional requested channel, language or other filters explicitly; these are not universal definitions for every metric.

select t.key as tool_name, sum(t.value::int) as count
from conversation_index c
cross join lateral jsonb_each_text(c.tool_call_counts) t
where c.created_at >= {{from}} and c.created_at < {{to}}
  and c.business_id = any({{business_ids}})
  and c.is_hidden = false
  and c.tool_call_counts is not null
  and (c.classification is null or c.classification = 'customer')
  and (c.handoff_reason is null or c.handoff_reason not in (
    'spam', 'unknown-external-thread', 'other', 'government',
    'lawyer', 'auto-handoff-address', 'journalist'
  ))
group by t.key
order by count desc, tool_name
select w.key as workflow_name, v.key as variant_name,
  sum(v.value::int) as count
from conversation_index c
cross join lateral jsonb_each(c.received_workflow_variant_counts) w
cross join lateral jsonb_each_text(w.value) v
where c.created_at >= {{from}} and c.created_at < {{to}}
  and c.business_id = any({{business_ids}})
  and c.is_hidden = false
  and c.received_workflow_variant_counts is not null
  and (c.classification is null or c.classification = 'customer')
  and (c.handoff_reason is null or c.handoff_reason not in (
    'spam', 'unknown-external-thread', 'other', 'government',
    'lawyer', 'auto-handoff-address', 'journalist'
  ))
group by w.key, v.key
order by count desc, workflow_name, variant_name

Check completeness before charting these breakdowns. For top-N plus Other, rank the complete period totals in SQL, then sum all remaining categories into Other; do not truncate rows before calculating it. Keep workflow and variant as separate identity columns when grouping and ranking: concatenated names can collide. Use a separate is_other flag so a real category named "Other" remains distinct from the combined remainder.

Storefront search tables

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

TableOne row perKept
search_daily_statsbusiness × UTC day × surfaceforever
search_query_daily_statsbusiness × UTC day × normalized queryforever
search_query_logssearch14 days
search_click_logsproduct opened from a search14 days

search_daily_stats columns: date, surface (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. Its surface can also be typeahead or preload: searches the caller declared as not submitted by a shopper. Those appear only in the raw logs, not in either daily table. search_click_logs columns: search_id, search_created_at, product_id, position, query, normalized_query, surface, session_id, created_at. A click takes its surface from its search, so the same two values appear here. position is the clicked product's 0-based rank in the search's own result order, before any filtering in the shopper's browser. shown_product_ids keeps only the first 24 of that order, so a click at position 24 or later is not in it.

Guidance:

  • Prefer the daily stats tables for anything charted; the raw logs are gone after a fortnight.
  • A chart of shoppers' searches or clicks over search_query_logs or search_click_logs must exclude declared traffic, or it disagrees with the daily tables: surface not in ('typeahead', 'preload'). surface <> 'restApi' is not enough. It leaves both in, including REST calls that declared an intent, because those are logged as typeahead or preload, not restApi. Do not pin to surface = 'fullSearch' either: that drops voice and image searches.
  • The daily tables have a date column instead of created_at. Filter with date >= ({{from}} at time zone {{tz}})::date and date < ({{to}} at time zone {{tz}})::date and bucket with date_trunc({{bucket}}, date). Do not write {{from}}::date: it takes the UTC date of the period start, so east of UTC (Athens, Vilnius) "Today" selects yesterday's row.
  • Each row is a UTC day, so in other time zones day boundaries are approximate. In Athens, searches between 00:00 and 02:59 local time count towards the previous day.
  • 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.

TableOne row perKept
virtual_tryon_daily_statsbusiness × UTC day × surfaceforever
virtual_tryon_generationstry-on attemptforever

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.

Guidance:

  • surface is storefront (a real shopper) or preview (a merchant testing a prompt in the dashboard, against one product URL, repeatedly). Filter surface = 'storefront' for anything describing usage, especially "most tried-on products".
  • Shopper usage is status = 'success'; the success rate is successes / attempts from the daily table.
  • Unlike search, the raw table is never pruned, so product-level history goes back to the first try-on. Use it for "top products" over long windows.
  • Rows written before 2026-09-22 all read as successful storefront try-ons — that is what the table recorded then — and have no duration_ms, config_id or product_id. Filter duration_ms is not null for latency, or use timed_successes as the denominator.
  • Latency covers successes only, and counts the whole attempt (catalog lookup, image picker, generation) — i.e. what the shopper waited.
  • unique_product_urls does not re-aggregate across days; count distinct on the raw table for a window-wide figure.
  • Outfit-builder variants distort "top products". A look is several products but one row: product_url is the builder page (the same URL for every attempt) and product_id is only the first slot's product. Exclude those variants' config_ids from product-level reporting, or read the numbers as attempts-per-page rather than per-product.

Trustpilot reviews table

trustpilot_reviews has one row per Trustpilot review, keyed by business_id (there is no organization_id column). It covers every review on a connected Trustpilot business unit, including reviews left before the connection, and is kept forever.

Columns from the review itself, as Trustpilot reported them:

  • review_id, review_url, rating (1-5), title, text, language
  • reviewer_name
  • review_created_at (when the review was posted; use this for date filters), experienced_at, review_updated_at
  • order_reference (whatever the reviewer typed into Trustpilot's reference field; often junk)
  • is_verified, verification_level, source (e.g. BasicLink, InvitationApi), status

Columns filled in later, as we learn more:

  • reviewer_email: from Trustpilot's payload or the reviewer's answer to a "find reviewer" request.
  • order_number: an order we verified against the store; set when the private outbound email goes out.
  • country, country_source (order or displayLocation): mostly null, because Trustpilot rarely sends a location.
  • concern, concern_category, concern_extracted_at: see Guidance below.
  • contact_requested_at: when we asked Trustpilot for the reviewer's contact details.
  • outbound_email_sent_at: when the private outbound email was sent.
  • replied_at, reply_text: the latest public reply we posted.
  • conversation_id: the review conversation in the inbox.

concern_category is one of delivery, refund, product_quality, not_as_described, billing_or_subscription, customer_service, scam_or_trust, website_or_ordering or other.

Guidance:

  • The brand is the business. Group or filter on business_id, and use business_id = any({{business_ids}}) in report charts.
  • concern is a short English summary of the main complaint, written by an LLM shortly after the review arrives. It is only produced for 1-3 star reviews. concern_extracted_at is null means it is still pending. A null concern with concern_extracted_at set means the review states no concern.
  • reviewer_email and order_number are null for most reviews. Most reviewers stay anonymous unless they answer a find-reviewer request.
  • To link to the review in the inbox, join conversation_index using (conversation_id) and select its public_id. Table charts link publicId columns automatically. review_url opens the review on Trustpilot.
  • Edits a reviewer makes on Trustpilot are not picked up continuously, so rating and text can be stale for edited reviews.
select review_created_at, rating, reviewer_name, reviewer_email,
  order_number, language, concern_category, concern, review_url
from trustpilot_reviews
where rating <= 3
  and review_created_at >= {{from}} and review_created_at < {{to}}
  and business_id = any({{business_ids}})
order by review_created_at desc
limit 500

Table charts

For table charts, chartConfig.tableLinks maps a result column to a link behaviour: {type:"url"} uses the cell value as the URL, {type:"conversation"} treats it as a conversation public ID and opens https://app.octocom.ai/organization/{organizationSlug}/conversation/{publicId}, and {type:"template", template:"..."} builds a URL from {{value}}, {{organizationSlug}} and other result-column names. Plain http(s) URL cells and publicId columns are linked automatically.

On this page