WITH law_summary AS ( SELECT count() FILTER (WHERE status = 'enacted') AS law_count_enacted, COALESCE( jsonb_agg( jsonb_build_object( 'code', code, 'article_number', article_number, 'name', name, 'version', version, 'kb_path', kb_path ) ORDER BY enacted_at DESC NULLS LAST, code ) FILTER (WHERE status = 'enacted'), '[]'::jsonb ) AS laws_json FROM normative_registry ), dot_agg AS ( SELECT count() AS dot_count_total, count() FILTER (WHERE status = 'active') AS dot_count_active, COALESCE( jsonb_agg( jsonb_build_object( 'code', code, 'tier', tier, 'domain', domain, 'operation', operation, 'status', status, 'trigger_type', trigger_type ) ORDER BY tier, code ) FILTER (WHERE status = 'active'), '[]'::jsonb ) AS dots_json FROM dot_tools ), entity_agg AS ( SELECT count() AS entity_count_total, count(DISTINCT species_code) AS species_count_live, COALESCE( jsonb_agg( jsonb_build_object( 'species_code', species_code, 'composition_level', composition_level, 'entity_count', entity_count ) ORDER BY entity_count DESC, species_code ), '[]'::jsonb ) AS entities_json FROM ( SELECT species_code, min(composition_level) AS composition_level, count() AS entity_count FROM birth_registry GROUP BY species_code ) s ), db_agg AS ( SELECT count() AS db_count_total, jsonb_agg( jsonb_build_object( 'db_name', datname, 'size_bytes', pg_database_size(datname), 'role_label', CASE datname WHEN 'directus' THEN 'warehouse' WHEN 'incomex_metadata' THEN 'brain-store' WHEN 'workflow' THEN 'engine' WHEN 'postgres' THEN 'cluster-admin' ELSE 'other' END ) ORDER BY datname ) AS databases_json FROM pg_database WHERE datistemplate = false ), issue_agg AS ( SELECT COALESCE( jsonb_agg( jsonb_build_object('severity', severity, 'status', status, 'issue_count', cnt) ORDER BY severity, status ), '[]'::jsonb ) AS issues_json, COALESCE(sum(cnt) FILTER (WHERE lower(severity) = 'critical' AND status = 'open'), 0)::bigint AS critical_open_count FROM ( SELECT severity, status, count(*) AS cnt FROM system_issues GROUP BY severity, status ) s ), summary AS ( SELECT jsonb_build_object( 'law_count_enacted', l.law_count_enacted, 'dot_count_active', d.dot_count_active, 'dot_count_total', d.dot_count_total, 'entity_count_total', e.entity_count_total, 'species_count_live', e.species_count_live, 'db_count_total', db.db_count_total, 'critical_open_count', i.critical_open_count )::text AS summary_json FROM law_summary l CROSS JOIN dot_agg d CROSS JOIN entity_agg e CROSS JOIN db_agg db CROSS JOIN issue_agg i ) SELECT s.summary_json, l.laws_json::text AS laws_json, d.dots_json::text AS dots_json, e.entities_json::text AS entities_json, db.databases_json::text AS databases_json, i.issues_json::text AS issues_json FROM summary s CROSS JOIN law_summary l CROSS JOIN dot_agg d CROSS JOIN entity_agg e CROSS JOIN db_agg db CROSS JOIN issue_agg i