Skip to content

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.

14.0 KB
Raw Blame
-- Action Engine (#818): the reads behind the V1 definition library.
--
-- The V1 specification asks for 62 action definitions. Each needs a signal
-- that can trigger it, and each signal needs a read over data the product
-- already collects — prompt visibility per day, competitor presence per
-- prompt, citation sources, fan-out queries, Search Console queries, AI
-- landing-page traffic.
--
-- Doing those reads from the server row by row is how the nightly pass died on
-- the largest brand twice (#829, #834): a query that scans a brand's history
-- crosses the 8s statement timeout once the brand is large enough. So every
-- read here aggregates in Postgres and returns a compact summary — one row per
-- prompt, per competitor, per domain, per query, per page — measured on the
-- largest brand before being written down:
--
--   ae_citation_sources  1.5s   (was 15s as a naive join)
--   ae_fanout_gaps       0.45s
--
-- All are service-role only. They take a brand id and aggregate across it, and
-- nothing in the web calls them; granting them to authenticated users would
-- only widen the surface for no reader.

-- ─── Prompts: presence per day, current window against the previous one ─────

create or replace function public.ae_prompt_stats(
  p_brand_id uuid, p_cur_from date, p_cur_to date, p_prev_from date, p_prev_to date
) returns jsonb
language sql stable
as $$
  with days as (
    select prompt_id, day, bool_or(has_mention) as m, bool_or(has_citation) as c
    from public.insights_prompt_daily
    where brand_id = p_brand_id and day between p_prev_from and p_cur_to
    group by prompt_id, day
  ),
  active as (
    select p.id, p.topic_id
    from public.prompts p
    join public.prompt_sets s on s.id = p.prompt_set_id
    where s.brand_id = p_brand_id and p.is_active
  )
  select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
    select a.id as prompt_id, a.topic_id,
      count(d.day) filter (where d.day between p_cur_from and p_cur_to) as cur_days,
      count(d.day) filter (where d.day between p_cur_from and p_cur_to and d.m) as cur_mention_days,
      count(d.day) filter (where d.day between p_cur_from and p_cur_to and d.c) as cur_citation_days,
      count(d.day) filter (where d.day between p_prev_from and p_prev_to) as prev_days,
      count(d.day) filter (where d.day between p_prev_from and p_prev_to and d.m) as prev_mention_days,
      count(d.day) filter (where d.day between p_prev_from and p_prev_to and d.c) as prev_citation_days,
      max(d.day) filter (where d.m) as last_mention_day,
      max(d.day) filter (where d.c) as last_citation_day,
      (select coalesce(max(v.est_ai_volume), 0) from public.prompt_volumes v where v.prompt_id = a.id) as volume,
      exists (select 1 from public.prompt_target_urls t where t.prompt_id = a.id) as has_target_url
    from active a
    left join days d on d.prompt_id = a.id
    group by a.id, a.topic_id
  ) x
$$;

-- ─── Competitors: presence per prompt ───────────────────────────────────────

create or replace function public.ae_competitor_prompt_stats(
  p_brand_id uuid, p_cur_from date, p_cur_to date, p_prev_from date, p_prev_to date
) returns jsonb
language sql stable
as $$
  with days as (
    select prompt_id, competitor_id, day
    from public.insights_competitor_prompt_daily
    where brand_id = p_brand_id and day between p_prev_from and p_cur_to
    group by prompt_id, competitor_id, day
  )
  select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
    select prompt_id, competitor_id,
      count(*) filter (where day between p_cur_from and p_cur_to) as cur_days,
      count(*) filter (where day between p_prev_from and p_prev_to) as prev_days
    from days
    group by prompt_id, competitor_id
  ) x
$$;

-- ─── Dimensions: visibility by platform, by region, and per competitor ───────

create or replace function public.ae_dimension_stats(
  p_brand_id uuid, p_cur_from date, p_cur_to date, p_prev_from date, p_prev_to date
) returns jsonb
language sql stable
as $$
  select jsonb_build_object(
    'platforms', (
      select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
        select platform as key,
          sum(answer_count) filter (where day between p_cur_from and p_cur_to) as cur_answers,
          sum(mention_answers) filter (where day between p_cur_from and p_cur_to) as cur_mentions,
          sum(answer_count) filter (where day between p_prev_from and p_prev_to) as prev_answers,
          sum(mention_answers) filter (where day between p_prev_from and p_prev_to) as prev_mentions
        from public.insights_brand_daily
        where brand_id = p_brand_id and day between p_prev_from and p_cur_to
        group by platform
      ) x
    ),
    'regions', (
      select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
        select region as key,
          sum(answer_count) filter (where day between p_cur_from and p_cur_to) as cur_answers,
          sum(mention_answers) filter (where day between p_cur_from and p_cur_to) as cur_mentions,
          sum(answer_count) filter (where day between p_prev_from and p_prev_to) as prev_answers,
          sum(mention_answers) filter (where day between p_prev_from and p_prev_to) as prev_mentions
        from public.insights_brand_daily
        where brand_id = p_brand_id and day between p_prev_from and p_cur_to
        group by region
      ) x
    ),
    'competitors', (
      select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
        select c.competitor_id as key, max(co.name) as name,
          sum(c.mention_answers) filter (where c.day between p_cur_from and p_cur_to) as cur_mentions,
          sum(c.mention_answers) filter (where c.day between p_prev_from and p_prev_to) as prev_mentions,
          sum(c.citation_answers) filter (where c.day between p_cur_from and p_cur_to) as cur_citations
        from public.insights_competitor_daily c
        left join public.competitors co on co.id::text = c.competitor_id
        where c.brand_id = p_brand_id and c.day between p_prev_from and p_cur_to
        group by c.competitor_id
      ) x
    )
  )
$$;

-- ─── Citation sources: which domains answers cite, and whose answers ────────

create or replace function public.ae_citation_sources(p_brand_id uuid, p_from timestamptz)
returns jsonb
language sql stable
as $$
  with owned as (
    select regexp_replace(lower(domain), '^www\.', '') as d
    from public.brand_domains where brand_id = p_brand_id
  ),
  r as materialized (
    select id, prompt_id, coalesce(mention_count, 0) > 0 as bm,
           coalesce(competitor_mentions @? '$[*] ? (@.mention_count > 0)', false) as cm
    from public.prompt_results
    where brand_id = p_brand_id and created_at >= p_from
  ),
  rd as materialized (
    select distinct prc.prompt_result_id as rid, regexp_replace(lower(u.domain), '^www\.', '') as domain
    from public.prompt_result_citations prc
    join public.citation_urls u on u.id = prc.url_id
    where prc.brand_id = p_brand_id and prc.created_at >= p_from
  )
  select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
    select rd.domain,
      exists (select 1 from owned o where rd.domain = o.d or rd.domain like '%.' || o.d) as owned,
      count(*) as results,
      count(distinct r.prompt_id) as prompts,
      count(*) filter (where r.bm) as with_brand,
      count(*) filter (where r.cm and not r.bm) as competitor_only,
      count(*) filter (where not r.bm) as without_brand
    from rd join r on r.id = rd.rid
    group by rd.domain
    order by count(*) desc
    limit 300
  ) x
$$;

-- ─── Owned URLs: how often each owned page was cited, now and before ────────

create or replace function public.ae_owned_citations(
  p_brand_id uuid, p_cur_from timestamptz, p_prev_from timestamptz
) returns jsonb
language sql stable
as $$
  with owned as (
    select regexp_replace(lower(domain), '^www\.', '') as d
    from public.brand_domains where brand_id = p_brand_id
  ),
  cites as materialized (
    select prc.created_at, u.url, regexp_replace(lower(u.domain), '^www\.', '') as domain
    from public.prompt_result_citations prc
    join public.citation_urls u on u.id = prc.url_id
    where prc.brand_id = p_brand_id and prc.created_at >= p_prev_from
  )
  select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
    select c.url,
      count(*) filter (where c.created_at >= p_cur_from) as cur_citations,
      count(*) filter (where c.created_at < p_cur_from) as prev_citations
    from cites c
    where exists (select 1 from owned o where c.domain = o.d or c.domain like '%.' || o.d)
    group by c.url
    order by count(*) desc
    limit 300
  ) x
$$;

-- ─── Fan-out queries the engines ran where the brand never appeared ─────────

create or replace function public.ae_fanout_gaps(p_brand_id uuid, p_from timestamptz)
returns jsonb
language sql stable
as $$
  with r as materialized (
    select id, prompt_id, coalesce(mention_count, 0) > 0 as bm,
           coalesce(competitor_mentions @? '$[*] ? (@.mention_count > 0)', false) as cm,
           search_queries
    from public.prompt_results
    where brand_id = p_brand_id and created_at >= p_from
      and jsonb_typeof(search_queries) = 'array'
  )
  select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
    select lower(trim(q->>'query')) as query,
      count(*) as results,
      count(distinct r.prompt_id) as prompts,
      count(*) filter (where r.cm and not r.bm) as competitor_only
    from r cross join lateral jsonb_array_elements(r.search_queries) q
    where coalesce(trim(q->>'query'), '') <> ''
    group by 1
    having count(*) filter (where r.bm) = 0
    order by count(distinct r.prompt_id) desc, count(*) desc
    limit 300
  ) x
$$;

-- ─── Search Console: per query, now against before ──────────────────────────

create or replace function public.ae_gsc_queries(
  p_brand_id uuid, p_cur_from date, p_cur_to date, p_prev_from date, p_prev_to date
) returns jsonb
language sql stable
as $$
  select coalesce(jsonb_agg(row_to_json(x)), '[]'::jsonb) from (
    select query,
      sum(clicks) filter (where date between p_cur_from and p_cur_to) as cur_clicks,
      sum(impressions) filter (where date between p_cur_from and p_cur_to) as cur_impressions,
      avg(position) filter (where date between p_cur_from and p_cur_to) as cur_position,
      sum(clicks) filter (where date between p_prev_from and p_prev_to) as prev_clicks,
      sum(impressions) filter (where date between p_prev_from and p_prev_to) as prev_impressions
    from public.gsc_query_stats
    where brand_id = p_brand_id and date between p_prev_from and p_cur_to
    group by query
    order by sum(impressions) filter (where date between p_cur_from and p_cur_to) desc nulls last
    limit 500
  ) x
$$;

-- ─── AI landing pages: analytics and the tracking snippet, per page ─────────

create or replace function public.ae_ai_traffic_pages(
  p_brand_id uuid, p_cur_from date, p_cur_to date, p_prev_from date, p_prev_to date
) returns jsonb
language sql stable
as $$
  with ga as (
    select landing_page as page,
      sum(sessions) filter (where date between p_cur_from and p_cur_to) as cur_sessions,
      sum(sessions) filter (where date between p_prev_from and p_prev_to) as prev_sessions,
      sum(engaged_sessions) filter (where date between p_cur_from and p_cur_to) as cur_engaged,
      sum(key_events) filter (where date between p_cur_from and p_cur_to) as cur_key_events,
      sum(key_events) filter (where date between p_prev_from and p_prev_to) as prev_key_events
    from public.ga_ai_traffic_stats
    where brand_id = p_brand_id and date between p_prev_from and p_cur_to
    group by landing_page
  ),
  snippet as (
    select url as page,
      count(*) filter (where created_at::date between p_cur_from and p_cur_to) as cur_sessions,
      count(*) filter (where created_at::date between p_prev_from and p_prev_to) as prev_sessions
    from public.ai_traffic_logs
    where brand_id = p_brand_id and created_at >= p_prev_from
    group by url
  )
  select jsonb_build_object(
    'analytics', (select coalesce(jsonb_agg(row_to_json(g)), '[]'::jsonb) from (select * from ga order by cur_sessions desc nulls last limit 300) g),
    'snippet', (select coalesce(jsonb_agg(row_to_json(s)), '[]'::jsonb) from (select * from snippet order by cur_sessions desc nulls last limit 300) s)
  )
$$;

-- Service-role only.
revoke all on function public.ae_prompt_stats(uuid, date, date, date, date) from public, anon, authenticated;
revoke all on function public.ae_competitor_prompt_stats(uuid, date, date, date, date) from public, anon, authenticated;
revoke all on function public.ae_dimension_stats(uuid, date, date, date, date) from public, anon, authenticated;
revoke all on function public.ae_citation_sources(uuid, timestamptz) from public, anon, authenticated;
revoke all on function public.ae_owned_citations(uuid, timestamptz, timestamptz) from public, anon, authenticated;
revoke all on function public.ae_fanout_gaps(uuid, timestamptz) from public, anon, authenticated;
revoke all on function public.ae_gsc_queries(uuid, date, date, date, date) from public, anon, authenticated;
revoke all on function public.ae_ai_traffic_pages(uuid, date, date, date, date) from public, anon, authenticated;

grant execute on function public.ae_prompt_stats(uuid, date, date, date, date) to service_role;
grant execute on function public.ae_competitor_prompt_stats(uuid, date, date, date, date) to service_role;
grant execute on function public.ae_dimension_stats(uuid, date, date, date, date) to service_role;
grant execute on function public.ae_citation_sources(uuid, timestamptz) to service_role;
grant execute on function public.ae_owned_citations(uuid, timestamptz, timestamptz) to service_role;
grant execute on function public.ae_fanout_gaps(uuid, timestamptz) to service_role;
grant execute on function public.ae_gsc_queries(uuid, date, date, date, date) to service_role;
grant execute on function public.ae_ai_traffic_pages(uuid, date, date, date, date) to service_role;