Open-source agentic AI Search Execution Platform. Track AI visibility, find opportunities, and let agents turn signals into prioritized actions and drafted fixes across ChatGPT, Claude, Gemini, Google AI Overviews, Google AI Mode, Perplexity, Grok, and Microsoft Copilot.
TypeScript aeoai-agentai-searchanswer-engine-optimizationbrand-monitoringchatgptclaudecopilotdockergeminigeogoogle-ai-overviewsgrokmcpnextjsopen-sourceperplexityseosupabasetypescript
-- Topics overview, aggregated in Postgres (#721).
--
-- The page downloaded the brand's entire 30-day result set to compute twenty
-- table rows: 33 sequential requests, 73 MB of JSON, ~19 s on the largest
-- brand. Two thirds of that was one column — competitor_mentions, averaging
-- eleven entries per row — fetched to derive a single top competitor per
-- topic. The rest was pagination: .range() becomes LIMIT/OFFSET, so page k
-- re-walked k×1000 rows, ~563k buffer touches to read 32k rows once.
--
-- Everything below is what the client was doing in JavaScript, moved to where
-- the data already is. The AI Visibility Score itself stays in JS
-- (lib/visibility-score) over ~20 rows, so the Topics page and the Insights
-- headline keep computing it from one implementation.
--
-- Windows are resolved from now() inside the function rather than passed in:
-- the caller's clock and the database's would otherwise disagree about which
-- answers fall in "the last 7 days", and the page has no reason to care.
CREATE FUNCTION public.topics_overview_aggregates(p_brand_id uuid)
RETURNS TABLE (
topic_id uuid,
answers bigint,
mention_answers bigint,
citation_answers bigint,
pos_sum double precision,
pos_n bigint,
cur_answers bigint,
cur_mention_answers bigint,
cur_citation_answers bigint,
cur_pos_sum double precision,
cur_pos_n bigint,
prev_answers bigint,
prev_mention_answers bigint,
prev_citation_answers bigint,
prev_pos_sum double precision,
prev_pos_n bigint,
total_mentions bigint,
total_citations bigint,
comp_mentions bigint,
active_prompts bigint,
visible_prompts bigint,
last_run_at timestamptz,
competitors jsonb,
daily jsonb
)
LANGUAGE sql
STABLE
SECURITY INVOKER
SET search_path = public
AS $$
WITH bounds AS (
SELECT now() - interval '30 days' AS since_30d,
now() - interval '7 days' AS cur_from,
now() - interval '14 days' AS prev_from,
-- Start of the 14th day back in UTC, so the earliest bucket the page
-- draws is complete rather than clipped at the current time of day.
date_trunc('day', (now() AT TIME ZONE 'UTC') - interval '13 days')
AT TIME ZONE 'UTC' AS spark_from
),
-- Answers in the window, carrying the topic their prompt belongs to. The
-- prompt_sets join is what scopes prompts to this brand; prompt_results is
-- filtered on brand_id as well, matching what the page did client-side.
rows AS (
SELECT p.topic_id,
pr.prompt_id,
pr.created_at,
COALESCE(pr.mention_count, 0) AS mentions,
COALESCE(pr.citation_count, 0) AS citations,
pr.mention_position,
(COALESCE(pr.mention_count, 0) > 0 OR COALESCE(pr.citation_count, 0) > 0) AS visible
FROM public.prompt_results pr
JOIN public.prompts p ON p.id = pr.prompt_id
JOIN public.prompt_sets ps ON ps.id = p.prompt_set_id
CROSS JOIN bounds b
WHERE pr.brand_id = p_brand_id
AND ps.brand_id = p_brand_id
AND p.topic_id IS NOT NULL
AND pr.platform <> 'chatgpt-shopping' -- #155 - isolate from analytics
AND pr.created_at >= b.since_30d
),
-- Competitor share per topic. Summed straight from the array: the client
-- folded duplicates within a row before adding them up, which reaches the
-- same total.
--
-- Scans prompt_results again rather than reusing `rows`. `rows` is referenced
-- several times so Postgres materialises it, and carrying competitor_mentions
-- through that materialisation spills 32k detoasted jsonb values to temp
-- files — 4.5 s and 7,500 temp blocks, measured. Reading the column only
-- where it is needed keeps the shared CTE lean.
comps AS (
SELECT p.topic_id,
cm.value ->> 'competitor_id' AS competitor_id,
MIN(cm.value ->> 'name') AS name,
SUM(COALESCE((cm.value ->> 'mention_count')::bigint, 0)) AS mentions
FROM public.prompt_results pr
JOIN public.prompts p ON p.id = pr.prompt_id
JOIN public.prompt_sets ps ON ps.id = p.prompt_set_id
CROSS JOIN bounds b
CROSS JOIN LATERAL jsonb_array_elements(COALESCE(pr.competitor_mentions, '[]'::jsonb)) cm
WHERE pr.brand_id = p_brand_id
AND ps.brand_id = p_brand_id
AND p.topic_id IS NOT NULL
AND pr.platform <> 'chatgpt-shopping'
AND pr.created_at >= b.since_30d
GROUP BY p.topic_id, cm.value ->> 'competitor_id'
),
comp_totals AS (
SELECT topic_id,
SUM(mentions) AS comp_mentions,
jsonb_object_agg(competitor_id, jsonb_build_object('name', name, 'sov', mentions)) AS competitors
FROM comps
GROUP BY topic_id
),
-- Fourteen daily buckets for the sparkline. Only the days the page draws.
--
-- Keyed in UTC, because the client builds the same keys from
-- `Date.toISOString()` — reading them in the database's session timezone
-- would shift every bucket by the offset and silently redraw the trend.
daily AS (
SELECT d.topic_id,
jsonb_object_agg(d.day, jsonb_build_object('visible', d.visible, 'count', d.count)) AS daily
FROM (
SELECT r2.topic_id,
to_char(r2.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD') AS day,
COUNT(*) FILTER (WHERE r2.visible) AS visible,
COUNT(*) AS count
FROM rows r2
CROSS JOIN bounds b
WHERE r2.created_at >= b.spark_from
GROUP BY r2.topic_id, to_char(r2.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD')
) d
GROUP BY d.topic_id
),
main AS (
SELECT r.topic_id,
COUNT(*)::bigint AS answers,
COUNT(*) FILTER (WHERE r.mentions > 0)::bigint AS mention_answers,
COUNT(*) FILTER (WHERE r.citations > 0)::bigint AS citation_answers,
COALESCE(SUM(1.0 / r.mention_position)
FILTER (WHERE r.mention_position > 0), 0)::double precision AS pos_sum,
COUNT(*) FILTER (WHERE r.mention_position > 0)::bigint AS pos_n,
COUNT(*) FILTER (WHERE r.created_at >= b.cur_from)::bigint AS cur_answers,
COUNT(*) FILTER (WHERE r.created_at >= b.cur_from AND r.mentions > 0)::bigint
AS cur_mention_answers,
COUNT(*) FILTER (WHERE r.created_at >= b.cur_from AND r.citations > 0)::bigint
AS cur_citation_answers,
COALESCE(SUM(1.0 / r.mention_position)
FILTER (WHERE r.created_at >= b.cur_from AND r.mention_position > 0), 0)::double precision
AS cur_pos_sum,
COUNT(*) FILTER (WHERE r.created_at >= b.cur_from AND r.mention_position > 0)::bigint
AS cur_pos_n,
COUNT(*) FILTER (WHERE r.created_at < b.cur_from AND r.created_at >= b.prev_from)::bigint
AS prev_answers,
COUNT(*) FILTER (WHERE r.created_at < b.cur_from AND r.created_at >= b.prev_from
AND r.mentions > 0)::bigint AS prev_mention_answers,
COUNT(*) FILTER (WHERE r.created_at < b.cur_from AND r.created_at >= b.prev_from
AND r.citations > 0)::bigint AS prev_citation_answers,
COALESCE(SUM(1.0 / r.mention_position)
FILTER (WHERE r.created_at < b.cur_from AND r.created_at >= b.prev_from
AND r.mention_position > 0), 0)::double precision AS prev_pos_sum,
COUNT(*) FILTER (WHERE r.created_at < b.cur_from AND r.created_at >= b.prev_from
AND r.mention_position > 0)::bigint AS prev_pos_n,
COALESCE(SUM(r.mentions), 0)::bigint AS total_mentions,
COALESCE(SUM(r.citations), 0)::bigint AS total_citations,
COUNT(DISTINCT r.prompt_id)::bigint AS active_prompts,
COUNT(DISTINCT r.prompt_id) FILTER (WHERE r.visible)::bigint AS visible_prompts,
MAX(r.created_at) AS last_run_at
FROM rows r
CROSS JOIN bounds b
GROUP BY r.topic_id
)
-- The two jsonb rollups join on at the end rather than riding through the
-- GROUP BY: Postgres has no max(jsonb), and carrying them through an
-- aggregate would need a wrapper that buys nothing here.
SELECT m.topic_id,
m.answers, m.mention_answers, m.citation_answers, m.pos_sum, m.pos_n,
m.cur_answers, m.cur_mention_answers, m.cur_citation_answers, m.cur_pos_sum, m.cur_pos_n,
m.prev_answers, m.prev_mention_answers, m.prev_citation_answers, m.prev_pos_sum, m.prev_pos_n,
m.total_mentions, m.total_citations,
COALESCE(ct.comp_mentions, 0)::bigint,
m.active_prompts, m.visible_prompts, m.last_run_at,
COALESCE(ct.competitors, '{}'::jsonb),
COALESCE(dl.daily, '{}'::jsonb)
FROM main m
LEFT JOIN comp_totals ct ON ct.topic_id = m.topic_id
LEFT JOIN daily dl ON dl.topic_id = m.topic_id
$$;
GRANT EXECUTE ON FUNCTION public.topics_overview_aggregates(uuid) TO authenticated;