TypeScript 55.4%
Python 43.2%
SQL 1.2%
1-- WebSensor 0.2 — one-time backfills after 0005 (idempotent, additive).23-- Provenance columns on existing events from their source.4update events e set first_party = s.first_party, country = coalesce(e.country, s.country), language = coalesce(e.language, s.language, 'en')5from sources s where s.id = e.source_id and (e.country is null or e.language is null);67-- Cluster statistics from the events they contain.8update event_clusters c set9 source_count = greatest(1, (select count(distinct e.source_id) from events e where e.cluster_id = c.id)),10 first_party_count = (select count(*) from events e where e.cluster_id = c.id and e.first_party),11 external_count = (select count(*) from events e where e.cluster_id = c.id and not e.first_party),12 first_party_at = (select min(e.detected_at) from events e where e.cluster_id = c.id and e.first_party),13 first_external_at = (select min(e.detected_at) from events e where e.cluster_id = c.id and not e.first_party)14where c.last_at >= now() - interval '10 days';15update event_clusters set lead_time_ms = extract(epoch from (first_external_at - first_party_at)) * 1000 where first_party_at is not null and first_external_at is not null and lead_time_ms is null;1617-- Initial breaking states for recent clusters (the engine maintains them from now on).18update event_clusters c set state = case19 when c.last_at < now() - interval '72 hours' then 'closed'20 when c.max_importance >= 85 and c.first_at >= now() - interval '6 hours' and (c.source_count >= 2 or c.event_count >= 2) and c.title not like '%new items in%' then 'breaking'21 when c.source_count >= 3 and c.last_at >= now() - interval '24 hours' then 'confirmed'22 when c.max_importance >= 65 and c.event_count >= 3 and c.first_at >= now() - interval '12 hours' then 'developing'23 else 'watching' end24where c.state = 'watching';2526-- Signal score for existing events (approximation: importance blended with confidence/novelty; the engine computes the full score for new events).27update events set signal_score = round((0.6 * importance + 0.2 * confidence + 0.2 * novelty + case when first_party then 4 else -6 end + case when evidence_label = 'CONFIRMED' then 6 else 0 end + case when silent_change then 3 else 0 end)::numeric, 1)28where signal_score is null;29update events set signal_score = least(100, greatest(0, signal_score)) where signal_score is not null and (signal_score > 100 or signal_score < 0);30