-- ============================================================================ -- OBJECTIVE E — UI / API READ CONTRACT (CLONE, PURE READ-ONLY) -- Target DB: directus_gov_test_20260602 (NON-PRODUCTION CLONE) -- Produces the exact JSON shapes a coverage dashboard/API would return, -- against the realistic committed clone state (100% covered). No DDL/writes. -- ============================================================================ \set ON_ERROR_STOP on \pset pager off \t on

\echo ====================E1_GET_/coverage/summary==================== SELECT jsonb_pretty(jsonb_build_object( 'total_cells', tot.total, 'covered_cells', tot.total - g.gap, 'gap_cells', g.gap, 'coverage_pct', round(100.0*(tot.total-g.gap)/NULLIF(tot.total,0),2), 'conflicts', cf.conf, 'objects', io.objs, 'scopes', sc.scopes, 'ownership_anchors', an.anchors)) FROM (SELECT (SELECT count() FROM v_governance_object_inventory WHERE requires_owner AND born) * (SELECT count() FROM governance_responsibility_scope WHERE status='active') AS total) tot, (SELECT count() AS gap FROM v_object_owner_gap) g, (SELECT count() AS conf FROM v_object_owner_conflict) cf, (SELECT count() AS objs FROM v_governance_object_inventory WHERE requires_owner AND born) io, (SELECT count() AS scopes FROM governance_responsibility_scope WHERE status='active') sc, (SELECT count(*) AS anchors FROM governance_object_ownership WHERE owner_kind='accountable' AND lifecycle_status='active') an;

\echo ====================E2_GET_/coverage/by-scope==================== SELECT jsonb_pretty(jsonb_agg(row_to_json(t))) FROM (SELECT s.scope_code AS scope, s.description, (SELECT count() FROM v_governance_object_inventory inv WHERE inv.requires_owner AND inv.born) AS objects, (SELECT count() FROM v_governance_object_inventory inv WHERE inv.requires_owner AND inv.born) - (SELECT count() FROM v_object_owner_gap g WHERE g.scope=s.scope_code) AS covered, (SELECT count() FROM v_object_owner_gap g WHERE g.scope=s.scope_code) AS gap FROM governance_responsibility_scope s WHERE s.status='active' ORDER BY s.scope_code) t;

\echo ====================E3_GET_/coverage/by-owner==================== SELECT jsonb_pretty(jsonb_agg(row_to_json(t))) FROM (SELECT eo.owner_gov_code AS owner, gr.name AS owner_name, count() AS accountable_cells, count() FILTER (WHERE eo.resolution='direct') AS direct, count(*) FILTER (WHERE eo.resolution='inherited') AS inherited FROM v_object_effective_owner eo LEFT JOIN governance_registry gr ON gr.code=eo.owner_gov_code WHERE eo.owner_kind='accountable' GROUP BY eo.owner_gov_code, gr.name ORDER BY accountable_cells DESC, owner) t;

\echo ====================E4_GET_/coverage/by-object_(first_5)==================== SELECT jsonb_pretty(jsonb_agg(row_to_json(t))) FROM (SELECT inv.object_type, inv.object_ref, (SELECT count() FROM governance_responsibility_scope WHERE status='active') AS scopes_required, (SELECT count() FROM v_object_effective_owner eo WHERE eo.object_ref=inv.object_ref AND eo.owner_kind='accountable') AS scopes_covered, (SELECT count(*) FROM v_object_owner_gap g WHERE g.object_ref=inv.object_ref) AS scopes_gap FROM v_governance_object_inventory inv WHERE inv.requires_owner AND inv.born ORDER BY inv.object_ref LIMIT 5) t;

\echo ====================E5_GET_/coverage/gaps_(empty_when_100pct)==================== SELECT COALESCE(jsonb_pretty(jsonb_agg(row_to_json(t))), '[]') FROM (SELECT object_type, object_ref, scope FROM v_object_owner_gap ORDER BY object_ref, scope LIMIT 50) t;

\echo ====================E6_GET_/coverage/ownership-detail_(object=agents)==================== SELECT jsonb_pretty(jsonb_agg(row_to_json(t))) FROM (SELECT object_ref, scope, owner_gov_code, owner_kind, resolution, source_anchor_type, source_anchor_ref, depth FROM v_object_effective_owner WHERE object_ref='agents' AND owner_kind='accountable' ORDER BY scope) t;

\echo ====================E7_GET_/coverage/scan-status==================== SELECT jsonb_pretty(jsonb_build_object( 'last_run', (SELECT row_to_json(r) FROM ( SELECT run_id, scan_mode, status, groups_scanned, objects_materialized, finished_at FROM candidate_scan_run ORDER BY finished_at DESC NULLS LAST LIMIT 1) r), 'total_runs', (SELECT count() FROM candidate_scan_run), 'candidate_states', (SELECT count() FROM governance_candidate_state), 'candidate_objects', (SELECT count() FROM governance_candidate_object), 'rulesets', (SELECT count() FROM governance_ruleset)));

\echo ====================E8_GET_/coverage/conflicts_(empty=healthy)==================== SELECT COALESCE(jsonb_pretty(jsonb_agg(row_to_json(t))), '[]') FROM (SELECT * FROM v_object_owner_conflict LIMIT 50) t; \t off \echo ====================DONE==================== -- Executed result (clone): summary 210/210/0/100%; by-owner GOV-COUNCIL 102, GOV-KG-SYS 90, GOV-DOT 30 (12 direct), GOV-MOIT 18 (sum 240); gaps=[]; conflicts=[].