Methodology.

SELECT COUNT(*)                          AS total_signals,
       COUNT(DISTINCT DATE(ingested_at))  AS active_days,
       COUNT(*) FILTER (WHERE ingested_at >= now() - interval '24 hours') AS last_24h
FROM v3_signals;

SELECT traffic_light, COUNT(*) AS n
FROM v3_signals
GROUP BY traffic_light
ORDER BY n DESC;

SELECT
  COUNT(*) FILTER (WHERE relevance_score >= 40) AS review_tier,
  COUNT(*) FILTER (WHERE relevance_score >= 50) AS actionable,
  COUNT(*) FILTER (WHERE relevance_score >= 60) AS decision_grade
FROM v3_signal_scores;

SELECT primary_node_id, COUNT(*) AS signals
FROM v3_signals
GROUP BY primary_node_id
ORDER BY signals DESC;

SELECT s.title, s.contracting_authority, s.value_gbp,
       s.traffic_light, s.signal_type, ss.relevance_score
FROM v3_signals s
JOIN v3_signal_scores ss ON ss.signal_id = s.id
                         AND ss.node_id = s.primary_node_id
WHERE s.traffic_light IN ('GREEN','BLUE')
  AND ss.relevance_score >= 65
  AND s.source_lane <> 'web_intelligence'
ORDER BY ss.relevance_score DESC, s.value_gbp DESC NULLS LAST;

SELECT
  SUM(array_length(keywords_high, 1))     AS keywords_high,
  SUM(array_length(keywords_medium, 1))   AS keywords_medium,
  SUM(array_length(keywords_negative, 1)) AS keywords_negative,
  SUM(array_length(authority_tier1, 1))   AS tier1_patterns
FROM v3_ref_context_scoring_rules
WHERE active = true;

SELECT COUNT(*) AS runs,
       SUM(signals_evaluated) AS signals_evaluated,
       SUM(packs_produced)    AS packs_produced
FROM production_run_log
WHERE component_id = 'produce_pack';