fix(db): drop deleted competitors from competitor_aggregates - #560

Summary

Deleting a competitor stops future tracking runs from scoring it, but the Insights Leaderboard, the provider breakdown and the Competitors comparison kept showing it until the selected date window aged past its last scraped result — the competitor_aggregates RPC aggregates the historical competitor_mentions JSON on prompt_results and never checked whether the competitor still exists.

New migration 00035_competitor_aggregates_live_only.sql redefines the function (CREATE OR REPLACE, return type unchanged) with a liveness check in the mentions_flat CTE: a mention row is kept only if its competitor_id resolves to a live competitors row of the same brand. Removed competitors drop out of every competitor-side CTE at once, for every date range.

• The function body is otherwise byte-identical to 00029 — the only functional change is the 5-line EXISTS clause.
• SECURITY INVOKER kept; grants from 00006 carry over with CREATE OR REPLACE.
• Brand-side metrics and the remaining competitors' rates are unaffected: the brand aggregates from the filtered CTE, and each remaining competitor's rate uses its own visible_prompts over the shared brand_prompt_count.
• Already-generated reports are stored snapshots and stay as they are.
• Consolidated schema.sql regenerated via bash supabase/build-schema.sh.

Migration will be applied to the hosted database after merge.

Related issue

Closes #538

Type of change

• feat — New feature
• fix — Bug fix
• chore — Maintenance / dependencies
• docs — Documentation only
• refactor — Code change that neither fixes a bug nor adds a feature
• test — Adding or updating tests

How to test

1. Pick a brand with tracked results where a competitor appears on the Insights leaderboard.
2. Delete that competitor from the Competitors page.
3. Before this migration: the deleted competitor remains on the leaderboard, the provider chart and the comparison for any date range covering its old results. After: it disappears immediately from all three, while the brand's own numbers and the other competitors' rates stay identical.

Checklist

• Branch follows the naming convention (feature/, fix/, chore/, docs/) — see CONTRIBUTING.md
• Commits follow Conventional Commits
• yarn lint passes (run from web/)
• yarn typecheck passes (run from web/)
• yarn format:check passes (run from web/)
• Changes are focused — one concern per PR