-- ============================================================================ -- OBJECTIVE A — CLONE COVERAGE REGRESSION SUITE (RE-RUNNABLE, RESIDUE-FREE) -- Target DB: directus_gov_test_20260602 (NON-PRODUCTION CLONE) -- Channel: ssh contabo -> docker exec -i postgres psql -U workflow_admin
-- -d directus_gov_test_20260602 -f -

---

-- PURPOSE -- A single, idempotent regression suite that proves the full Phase-1 -- governance coverage pipeline still holds on the committed clone state, -- WITHOUT leaving any residue. The whole run is one BEGIN..ROLLBACK. -- Mutating checks undo their own DB changes via inner PL/pgSQL -- subtransactions (variable values survive a caught exception; DB changes -- do not), so results are recorded in the outer scope and survive to print.

-- WHAT IT VERIFIES (7 dimensions from mission Objective A) -- R1 ownership coverage - committed own=30, gap=0, conflict=0, 100% -- R2 scanner read - effective-owner = 30 direct + 210 inherited -- R3 scanner write - candidate materialization shape (5 state/35 obj) -- R4 candidate idempotency - re-upsert does NOT grow rows -- R5 issue/event fail-closed - inactive + unknown gov emit are REJECTED -- R6 worker/cursor monotone - stale=no-op, newer=advance -- R7 ownership uniqueness - duplicate accountable owner is REJECTED -- R8 rollback verification - DELETE seed -> gap 210; reversal is clean

-- SAFETY -- * Hard DB-name guard: aborts unless current_database() LIKE -- 'directus_gov_test_%'. -- * Single outer transaction ended by ROLLBACK -> zero persistent change. -- * Mutating checks roll back inside the block via RAISE EXCEPTION 'UNDO_*'. -- ============================================================================ \set ON_ERROR_STOP on \pset pager off

\echo ====================REGRESSION_SUITE_ENTRY==================== SELECT current_database() AS db, (current_database() LIKE 'directus_gov_test_%') AS is_clone;

BEGIN;

DO $$ DECLARE v_db text; BEGIN SELECT current_database() INTO v_db; IF v_db NOT LIKE 'directus_gov_test_%' THEN RAISE EXCEPTION 'REFUSING TO RUN: % is not a clone (expected directus_gov_test_*).', v_db; END IF; END $$;

CREATE TEMP TABLE rg_results ( seq int, check_name text, expected text, actual text, pass boolean ) ON COMMIT DROP;

-- R1 — OWNERSHIP COVERAGE (read-only, committed state) DO $$ DECLARE v_own int; v_gap int; v_conf int; v_total int; v_pct numeric; BEGIN SELECT count() INTO v_own FROM governance_object_ownership WHERE created_by='CLONE_TEST_F6_SEED'; SELECT count() INTO v_gap FROM v_object_owner_gap; SELECT count() INTO v_conf FROM v_object_owner_conflict; SELECT (SELECT count() FROM v_governance_object_inventory) * (SELECT count(*) FROM governance_responsibility_scope WHERE status='active') INTO v_total; v_pct := round(100.0 * (v_total - v_gap) / NULLIF(v_total,0), 2); INSERT INTO rg_results VALUES (101,'R1 ownership seed rows','30',v_own::text, v_own=30), (102,'R1 coverage gap','0',v_gap::text, v_gap=0), (103,'R1 ownership conflicts','0',v_conf::text, v_conf=0), (104,'R1 coverage pct','100.00',v_pct::text, v_pct=100.00); END $$;

-- R2 — SCANNER READ (effective-owner resolution; direct + inherited) DO $$ DECLARE v_direct int; v_inherited int; v_total int; BEGIN SELECT count() FILTER (WHERE resolution='direct'), count() FILTER (WHERE resolution='inherited'), count(*) INTO v_direct, v_inherited, v_total FROM v_object_effective_owner; INSERT INTO rg_results VALUES (201,'R2 effective-owner total (30 direct + 210 inherited)','240',v_total::text, v_total=240), (202,'R2 direct anchors','30',v_direct::text, v_direct=30), (203,'R2 inherited resolutions','210',v_inherited::text, v_inherited=210); END $$;

-- R3 — SCANNER WRITE shape (committed candidate materialization) DO $$ DECLARE v_state int; v_obj int; v_runs int; v_rs int; BEGIN SELECT count() INTO v_state FROM governance_candidate_state; SELECT count() INTO v_obj FROM governance_candidate_object; SELECT count() INTO v_runs FROM candidate_scan_run; SELECT count() INTO v_rs FROM governance_ruleset; INSERT INTO rg_results VALUES (301,'R3 candidate_state rows','5',v_state::text, v_state=5), (302,'R3 candidate_object rows','35',v_obj::text, v_obj=35), (303,'R3 scan_run ledger rows','2',v_runs::text, v_runs=2), (304,'R3 ruleset rows','1',v_rs::text, v_rs=1); END $$;

-- R4 — CANDIDATE IDEMPOTENCY (re-upsert must NOT grow rows; mutation undone) DO $$ DECLARE v_state0 int; v_obj0 int; v_state1 int; v_obj1 int; BEGIN SELECT count() INTO v_state0 FROM governance_candidate_state; SELECT count() INTO v_obj0 FROM governance_candidate_object; BEGIN INSERT INTO governance_candidate_state (group_key, source_snapshot_ref, ruleset_version, scan_time, candidate_verdict, dirty, risk_class, coverage_required, lifecycle_status, last_run_id) SELECT c.parent_ref, 1, 'RS-CLONE-TEST-1', now(), 'relevant', false, 'standard', true, 'active', 'SCAN-CLONE-TEST-2' FROM (SELECT DISTINCT parent_ref FROM v_governance_object_containment) c ON CONFLICT (group_key, ruleset_version) DO UPDATE SET scan_time=excluded.scan_time, last_run_id=excluded.last_run_id; INSERT INTO governance_candidate_object (candidate_key, group_key, source_snapshot_ref, ruleset_version, scan_time, candidate_verdict, dirty, risk_class, coverage_required, materialization_reason, lifecycle_status, last_run_id) SELECT inv.object_ref, cont.parent_ref, 1, 'RS-CLONE-TEST-1', now(), 'relevant', false, 'standard', true, 'indep_authoritative', 'active', 'SCAN-CLONE-TEST-2' FROM v_governance_object_inventory inv JOIN v_governance_object_containment cont ON cont.object_ref=inv.object_ref ON CONFLICT (candidate_key, ruleset_version) DO UPDATE SET scan_time=excluded.scan_time, last_run_id=excluded.last_run_id; SELECT count() INTO v_state1 FROM governance_candidate_state; SELECT count() INTO v_obj1 FROM governance_candidate_object; RAISE EXCEPTION 'UNDO_R4'; EXCEPTION WHEN OTHERS THEN NULL; -- mutations rolled back; v_state1/v_obj1 survive END; INSERT INTO rg_results VALUES (401,'R4 candidate_state unchanged after re-upsert', v_state0::text, v_state1::text, v_state0=v_state1), (402,'R4 candidate_object unchanged after re-upsert', v_obj0::text, v_obj1::text, v_obj0=v_obj1); END $$;

-- R5 — ISSUE / EVENT FAIL-CLOSED (inactive + unknown emit must be REJECTED) DO $$ DECLARE v_rejected boolean; BEGIN v_rejected := false; BEGIN INSERT INTO event_outbox (id,event_domain,event_type,event_stream,delivery_lane,event_severity, event_subject_table,event_subject_ref,safe_payload,occurred_at) SELECT gen_random_uuid(),'governance','coverage.scan_completed',event_stream,delivery_lane,'info', 'v_object_owner_gap','regress-test','{"regress":true}'::jsonb, now() FROM event_type_registry WHERE event_domain='governance' AND event_type='coverage.scan_completed'; EXCEPTION WHEN OTHERS THEN v_rejected := true; END; INSERT INTO rg_results VALUES (501,'R5 inactive gov emit rejected','rejected', CASE WHEN v_rejected THEN 'rejected' ELSE 'ACCEPTED!' END, v_rejected); v_rejected := false; BEGIN INSERT INTO event_outbox (id,event_domain,event_type,event_stream,delivery_lane,event_severity,safe_payload,occurred_at) VALUES (gen_random_uuid(),'governance','totally.made_up','s','l','info','{}'::jsonb,now()); EXCEPTION WHEN OTHERS THEN v_rejected := true; END; INSERT INTO rg_results VALUES (502,'R5 unknown gov emit rejected','rejected', CASE WHEN v_rejected THEN 'rejected' ELSE 'ACCEPTED!' END, v_rejected); END $$; DO $$ DECLARE v_emit int; BEGIN SELECT count(*) INTO v_emit FROM event_outbox WHERE event_domain='governance'; INSERT INTO rg_results VALUES (503,'R5 governance emit count','0',v_emit::text, v_emit=0); END $$;

-- R6 — WORKER / CURSOR MONOTONICITY (stale=no-op, newer=advance; undone) DO $$ DECLARE v_wm text; v_seen bigint; v_wm2 text; v_seen2 bigint; BEGIN BEGIN INSERT INTO gov_worker_cursor (worker_name, source_name, event_domain, last_watermark_ts, last_watermark_id, last_run_at, events_seen, attempts_written, phase) VALUES ('clone-test-worker','collection_registry','governance','2026-06-02 09:00:00+00','wm-stale', now(), 99, 99, 'scan') ON CONFLICT (worker_name, source_name) DO UPDATE SET last_watermark_ts=excluded.last_watermark_ts, last_watermark_id=excluded.last_watermark_id, events_seen=gov_worker_cursor.events_seen+excluded.events_seen WHERE excluded.last_watermark_ts > gov_worker_cursor.last_watermark_ts; SELECT last_watermark_id, events_seen INTO v_wm, v_seen FROM gov_worker_cursor WHERE worker_name='clone-test-worker'; INSERT INTO gov_worker_cursor (worker_name, source_name, event_domain, last_watermark_ts, last_watermark_id, last_run_at, events_seen, attempts_written, phase) VALUES ('clone-test-worker','collection_registry','governance','2026-06-02 12:00:00+00','wm-3', now(), 10, 10, 'scan') ON CONFLICT (worker_name, source_name) DO UPDATE SET last_watermark_ts=excluded.last_watermark_ts, last_watermark_id=excluded.last_watermark_id, events_seen=gov_worker_cursor.events_seen+excluded.events_seen WHERE excluded.last_watermark_ts > gov_worker_cursor.last_watermark_ts; SELECT last_watermark_id, events_seen INTO v_wm2, v_seen2 FROM gov_worker_cursor WHERE worker_name='clone-test-worker'; RAISE EXCEPTION 'UNDO_R6'; EXCEPTION WHEN OTHERS THEN NULL; END; INSERT INTO rg_results VALUES (601,'R6 stale watermark no-op (id stays wm-2)','wm-2',v_wm, v_wm='wm-2'), (602,'R6 stale watermark no-op (events stay 20)','20',v_seen::text, v_seen=20), (603,'R6 newer watermark advances (id -> wm-3)','wm-3',v_wm2, v_wm2='wm-3'), (604,'R6 newer watermark increments (events -> 30)','30',v_seen2::text, v_seen2=30); END $$;

-- R7 — OWNERSHIP UNIQUENESS (duplicate accountable owner must be REJECTED) DO $$ DECLARE v_rejected boolean := false; BEGIN BEGIN INSERT INTO governance_object_ownership (object_type, object_ref, scope, owner_kind, owner_gov_code, is_inherited_anchor, lifecycle_status, approval_ref, source_design_ref, created_by) SELECT 'group','GRP-GOVERNANCE', s.scope_code, 'accountable','GOV-COUNCIL', true,'active','REGRESS-DUP','regress','REGRESS_TEST' FROM governance_responsibility_scope s WHERE s.status='active' LIMIT 1; EXCEPTION WHEN OTHERS THEN v_rejected := true; END; INSERT INTO rg_results VALUES (701,'R7 duplicate accountable owner rejected','rejected', CASE WHEN v_rejected THEN 'rejected' ELSE 'ACCEPTED!' END, v_rejected); END $$;

-- R8 — ROLLBACK VERIFICATION (DELETE seed -> gap 210; mutation undone) DO $$ DECLARE v_del int; v_gap int; v_own int; BEGIN BEGIN DELETE FROM governance_object_ownership WHERE created_by='CLONE_TEST_F6_SEED'; GET DIAGNOSTICS v_del = ROW_COUNT; SELECT count() INTO v_gap FROM v_object_owner_gap; SELECT count() INTO v_own FROM governance_object_ownership; RAISE EXCEPTION 'UNDO_R8'; EXCEPTION WHEN OTHERS THEN NULL; END; INSERT INTO rg_results VALUES (801,'R8 seed delete removes 30','30',v_del::text, v_del=30), (802,'R8 gap returns to 210 after delete','210',v_gap::text, v_gap=210), (803,'R8 ownership returns to 0 after delete','0',v_own::text, v_own=0); END $$; DO $$ DECLARE v_gap int; v_own int; BEGIN SELECT count() INTO v_gap FROM v_object_owner_gap; SELECT count() INTO v_own FROM governance_object_ownership WHERE created_by='CLONE_TEST_F6_SEED'; INSERT INTO rg_results VALUES (804,'R8 committed gap intact after undo','0',v_gap::text, v_gap=0), (805,'R8 committed seed intact after undo','30',v_own::text, v_own=30); END $$;

\echo ====================REGRESSION_RESULTS==================== SELECT seq, check_name, expected, actual, CASE WHEN pass THEN 'PASS' ELSE 'FAIL' END AS verdict FROM rg_results ORDER BY seq; \echo ====================REGRESSION_SUMMARY==================== SELECT count() AS checks, count() FILTER (WHERE pass) AS passed, count() FILTER (WHERE NOT pass) AS failed, CASE WHEN count() FILTER (WHERE NOT pass)=0 THEN 'SUITE PASS' ELSE 'SUITE FAIL' END AS suite_verdict FROM rg_results;

ROLLBACK; -- residue-free: nothing persisted by this suite

\echo ====================POST_SUITE_COMMITTED_STATE_UNCHANGED==================== SELECT (SELECT count() FROM governance_object_ownership) AS own, (SELECT count() FROM v_object_owner_gap) AS gap, (SELECT count() FROM governance_candidate_object) AS cand_obj, (SELECT count() FROM gov_worker_cursor WHERE worker_name='clone-test-worker') AS cursor_rows, (SELECT count(*) FROM event_outbox WHERE event_domain='governance') AS gov_emit; \echo ====================DONE==================== -- Executed result: 26 checks / 26 passed / 0 failed -> SUITE PASS; committed state unchanged.