WITH base AS ( SELECT code, name, tier, domain, operation, classification, trigger_type, cron_schedule, file_path, coverage_status 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') ), domain_counts AS ( SELECT COALESCE(domain,'unclassified') AS domain, count() AS dot_count FROM base GROUP BY COALESCE(domain,'unclassified') ) SELECT (SELECT count() FROM base)::bigint AS active_count, (SELECT count() FROM dot_tools)::bigint AS total_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('domain',domain,'dot_count',dot_count) ORDER BY dot_count DESC, domain) FROM domain_counts), '[]'::jsonb) AS domain_counts, COALESCE((SELECT jsonb_agg(jsonb_build_object('code',code,'name',name,'tier',tier,'domain',domain,'operation',operation,'classification',classification,'trigger_type',trigger_type,'cron_schedule',cron_schedule,'file_path',file_path,'coverage_status',coverage_status) ORDER BY tier, code) FROM base), '[]'::jsonb) AS items