Deleting a competitor doesn't remove it from the Insights leaderboard / provider chart #538

Problem

Deleting a competitor stops future tracking runs from scoring it (the worker builds the mention list from the live competitors table), but the competitor keeps appearing in the Insights Leaderboard, the provider breakdown, and the Competitors comparison until the selected date window ages past its last scraped result.

Cause: the competitor_aggregates RPC (introduced in 00029_provider_visible_prompts.sql) aggregates the historical competitor_mentions JSON stored on prompt_results and never checks whether the competitor still exists.

Fix

New migration 00035_competitor_aggregates_live_only.sql:

• CREATE OR REPLACE the function (return type is unchanged jsonb, so no DROP needed) with a liveness check in the mentions_flat CTE:

WHERE cm.value ? 'competitor_id'
  AND EXISTS (
    SELECT 1 FROM public.competitors c
    WHERE c.id::text = cm.value->>'competitor_id'
      AND c.brand_id = p_brand_id
  )

• Keep SECURITY INVOKER and the existing GRANT ... TO authenticated exactly as in 00029.
• Regenerate the consolidated schema: bash supabase/build-schema.sh (CI checks it).

Expected behavior after the fix

• A deleted competitor disappears from the Leaderboard, the provider chart, and the Competitors comparison immediately, for every date range.
• The brand's own metrics and the remaining competitors' numbers do not change: the brand side aggregates from the filtered CTE, and each remaining competitor's rate uses its own visible_prompts over the shared brand_prompt_count — neither touches the removed rows.
• Already-generated reports are stored snapshots and stay as they are.

Notes

Maintainers apply migrations to the hosted database after merge — no action needed there from the contributor.