WITH base AS ( SELECT * FROM dot_tools WHERE status = 'active' ), tier_counts AS ( SELECT COALESCE(tier, 'unknown') AS tier, count() AS dot_count FROM base GROUP BY COALESCE(tier, 'unknown') ), trigger_counts AS ( SELECT COALESCE(trigger_type, 'unknown') AS trigger_type, count() AS dot_count FROM base GROUP BY COALESCE(trigger_type, 'unknown') ) SELECT (SELECT count() FROM base)::bigint AS active_dot_count, (SELECT count() FROM dot_tools)::bigint AS total_dot_count, COALESCE( (SELECT jsonb_agg( jsonb_build_object('tier', tier, 'dot_count', dot_count) ORDER BY tier ) FROM tier_counts), '[]'::jsonb ) AS tier_counts, COALESCE( (SELECT jsonb_agg( jsonb_build_object('trigger_type', trigger_type, 'dot_count', dot_count) ORDER BY dot_count DESC, trigger_type ) FROM trigger_counts), '[]'::jsonb ) AS trigger_counts, COALESCE( (SELECT jsonb_agg( jsonb_build_object( 'code', code, 'tier', tier, 'domain', domain, 'operation', operation, 'trigger_type', trigger_type, 'paired_dot', paired_dot, 'coverage_status', coverage_status, 'cron_schedule', cron_schedule, 'last_executed', to_char(last_executed, 'YYYY-MM-DD"T"HH24:MI:SSOF'), 'file_path', file_path ) ORDER BY tier, code ) FROM base), '[]'::jsonb ) AS dots