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
-- refresh_insights_daily (00131) finds a day range's answers by range.
--
-- Its inserts picked the answers by (created_at at time zone 'utc')::date,
-- which no index serves, so refreshing one day read every answer the brand
-- has: about 1 s warm and 12 s cold for the largest brand's 222k answers.
-- That ran on every completed run and in the nightly sweep, and a
-- sentiment correction (00160) runs it inside the person's 8 s request,
-- where it timed out. The same answers are now found by created_at range
-- through (brand_id, created_at): 0.66 s for that day, the same rows.
create or replace function public.refresh_insights_daily(
p_brand_id uuid,
p_day_from date,
p_day_to date
) returns void
language plpgsql
set search_path to 'public'
as $$
declare
-- The range's bounds as instants, so the answers are found through
-- (brand_id, created_at) rather than by reading all of the brand's.
v_from timestamptz := p_day_from::timestamp at time zone 'utc';
v_to timestamptz := (p_day_to + 1)::timestamp at time zone 'utc';
begin
delete from public.insights_brand_daily
where brand_id = p_brand_id and day between p_day_from and p_day_to;
delete from public.insights_competitor_daily
where brand_id = p_brand_id and day between p_day_from and p_day_to;
delete from public.insights_prompt_daily
where brand_id = p_brand_id and day between p_day_from and p_day_to;
delete from public.insights_competitor_prompt_daily
where brand_id = p_brand_id and day between p_day_from and p_day_to;
insert into public.insights_brand_daily (
brand_id, day, model_used, platform, region,
answer_count, mention_answers, citation_answers, mentioning_answers,
sum_visibility, sum_visibility_visible, total_mentions, total_citations,
positive_count, sum_inv_position, position_count, max_created_at,
negative_count, sum_position)
select
p_brand_id,
(pr.created_at at time zone 'utc')::date,
pr.model_used, pr.platform, pr.region,
count(*),
count(*) filter (where pr.mention_count > 0),
count(*) filter (where pr.citation_count > 0),
count(*) filter (where pr.mention_count > 0 or pr.citation_count > 0),
coalesce(sum(pr.visibility_score), 0),
coalesce(sum(pr.visibility_score)
filter (where pr.mention_count > 0 or pr.citation_count > 0), 0),
coalesce(sum(pr.mention_count), 0),
coalesce(sum(pr.citation_count), 0),
count(*) filter (where pr.sentiment = 'positive'),
sum(1.0 / pr.mention_position) filter (where pr.mention_position is not null),
count(*) filter (where pr.mention_position is not null),
max(pr.created_at),
count(*) filter (where pr.sentiment = 'negative'),
sum(pr.mention_position) filter (where pr.mention_position is not null)
from public.prompt_results pr
where pr.brand_id = p_brand_id
and pr.platform <> 'chatgpt-shopping' -- #155 — isolate from Insights
and pr.created_at >= v_from
and pr.created_at < v_to
group by 2, pr.model_used, pr.platform, pr.region;
insert into public.insights_prompt_daily (
brand_id, day, prompt_id, model_used, platform, region,
answer_count, has_mention, has_citation,
mention_answers, citation_answers, mentioning_answers, positive_count,
total_mentions, total_citations, sum_inv_position, position_count,
negative_count, sum_position)
select
p_brand_id,
(pr.created_at at time zone 'utc')::date,
pr.prompt_id, pr.model_used, pr.platform, pr.region,
count(*),
bool_or(pr.mention_count > 0),
bool_or(pr.citation_count > 0),
count(*) filter (where pr.mention_count > 0),
count(*) filter (where pr.citation_count > 0),
count(*) filter (where pr.mention_count > 0 or pr.citation_count > 0),
count(*) filter (where pr.sentiment = 'positive'),
coalesce(sum(pr.mention_count), 0),
coalesce(sum(pr.citation_count), 0),
sum(1.0 / pr.mention_position) filter (where pr.mention_position is not null),
count(*) filter (where pr.mention_position is not null),
count(*) filter (where pr.sentiment = 'negative'),
sum(pr.mention_position) filter (where pr.mention_position is not null)
from public.prompt_results pr
where pr.brand_id = p_brand_id
and pr.prompt_id is not null
and pr.platform <> 'chatgpt-shopping'
and pr.created_at >= v_from
and pr.created_at < v_to
group by 2, pr.prompt_id, pr.model_used, pr.platform, pr.region;
insert into public.insights_competitor_daily (
brand_id, day, competitor_id, model_used, platform, region,
answer_count, sum_visibility, total_mentions, total_citations,
mention_answers, citation_answers, sum_inv_position, position_count)
select
p_brand_id,
(pr.created_at at time zone 'utc')::date,
cm.value->>'competitor_id',
pr.model_used, pr.platform, pr.region,
count(*),
sum((cm.value->>'visibility_score')::numeric),
coalesce(sum(coalesce((cm.value->>'mention_count')::int, 0)), 0),
coalesce(sum(coalesce((cm.value->>'citation_count')::int, 0)), 0),
count(*) filter (where coalesce((cm.value->>'mention_count')::int, 0) > 0),
count(*) filter (where coalesce((cm.value->>'citation_count')::int, 0) > 0),
sum(1.0 / (cm.value->>'mention_position')::numeric)
filter (where (cm.value->>'mention_position') is not null),
count(*) filter (where (cm.value->>'mention_position') is not null)
from public.prompt_results pr,
lateral jsonb_array_elements(coalesce(pr.competitor_mentions, '[]'::jsonb)) cm
where pr.brand_id = p_brand_id
and pr.platform <> 'chatgpt-shopping'
and pr.created_at >= v_from
and pr.created_at < v_to
and cm.value ? 'competitor_id'
group by 2, cm.value->>'competitor_id', pr.model_used, pr.platform, pr.region;
insert into public.insights_competitor_prompt_daily (
brand_id, day, competitor_id, prompt_id, model_used, platform, region, mention_count,
mention_answers, citation_answers, sum_inv_position, position_count)
select
p_brand_id,
(pr.created_at at time zone 'utc')::date,
cm.value->>'competitor_id',
pr.prompt_id, pr.model_used, pr.platform, pr.region,
sum(coalesce((cm.value->>'mention_count')::int, 0)),
count(*) filter (where coalesce((cm.value->>'mention_count')::int, 0) > 0),
count(*) filter (where coalesce((cm.value->>'citation_count')::int, 0) > 0),
sum(1.0 / (cm.value->>'mention_position')::numeric)
filter (where (cm.value->>'mention_position') is not null),
count(*) filter (where (cm.value->>'mention_position') is not null)
from public.prompt_results pr,
lateral jsonb_array_elements(coalesce(pr.competitor_mentions, '[]'::jsonb)) cm
where pr.brand_id = p_brand_id
and pr.prompt_id is not null
and pr.platform <> 'chatgpt-shopping'
and pr.created_at >= v_from
and pr.created_at < v_to
and cm.value ? 'competitor_id'
and (coalesce((cm.value->>'mention_count')::int, 0) > 0
or coalesce((cm.value->>'citation_count')::int, 0) > 0
or coalesce((cm.value->>'visibility_score')::numeric, 0) > 0)
group by 2, cm.value->>'competitor_id', pr.prompt_id, pr.model_used, pr.platform, pr.region;
end;
$$;
revoke execute on function public.refresh_insights_daily(uuid, date, date)
from public, anon, authenticated;