-- ============================================================================ -- OBJECTIVE A — AXIS / TOPIC SUBSTRATE BUILD (CLONE, PERSISTENT COMMIT) -- Target DB: directus_gov_test_20260602 (NON-PRODUCTION CLONE) -- Marker: every row created_by LIKE 'TEST_CLONE_AXIS_%' (DROP-reversible)

---

-- Advances the proven rollback-only prototype to a PERSISTENT clone build, -- ENRICHED with hardened governance columns (zone/confidence/provenance/ -- lifecycle/parent-child) and a PARALLEL axis_object_ownership table so the -- existing governance_object_ownership (210->0) pipeline stays byte-intact. -- Single BEGIN..COMMIT with ON_ERROR_STOP; clone-guarded; zero-drift asserted. -- (Full annotated version with column rationale on disk; this is the canonical -- build. See doc 02 for design narrative.) -- ============================================================================ \set ON_ERROR_STOP on \pset pager off \echo ====================AXIS_BUILD_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.', v_db; END IF; END $$;

CREATE TABLE axis_registry ( axis_code text PRIMARY KEY, axis_name text NOT NULL, axis_family text NOT NULL CHECK (axis_family IN ('structural','label','semantic','pivot','system')), axis_kind text NOT NULL CHECK (axis_kind IN ('deterministic','uncertain','system')), value_domain text NOT NULL, owner_scope_ref text, status text NOT NULL DEFAULT 'candidate' CHECK (status IN ('candidate','active','deprecated','retired')), lifecycle_status text NOT NULL DEFAULT 'register' CHECK (lifecycle_status IN ('birth','register','active','deprecate','retire')), created_at timestamptz NOT NULL DEFAULT now(), created_by text NOT NULL); COMMENT ON TABLE axis_registry IS 'TEST_CLONE axis substrate — DROP-reversible. Built 2026-06-03 on clone only.';

CREATE TABLE axis_value ( axis_code text NOT NULL REFERENCES axis_registry(axis_code), value_code text NOT NULL, description text, parent_value_code text, status text NOT NULL DEFAULT 'candidate' CHECK (status IN ('candidate','active','deprecated','retired')), provenance jsonb, created_at timestamptz NOT NULL DEFAULT now(), created_by text NOT NULL, PRIMARY KEY (axis_code, value_code), FOREIGN KEY (axis_code, parent_value_code) REFERENCES axis_value(axis_code, value_code));

CREATE TABLE axis_assignment ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), object_type text NOT NULL, object_ref text NOT NULL, axis_code text NOT NULL, value_code text NOT NULL, zone text NOT NULL DEFAULT 'candidate' CHECK (zone IN ('approved','candidate','quarantine')), status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','superseded','retracted')), match_score numeric(5,4), confidence numeric(5,4), evidence jsonb, provenance jsonb, assigned_kind text NOT NULL CHECK (assigned_kind IN ('dot','agent','human','rule')), assigned_by text NOT NULL, assigned_at timestamptz NOT NULL DEFAULT now(), valid_from timestamptz NOT NULL DEFAULT now(), valid_to timestamptz, revision integer NOT NULL DEFAULT 1, superseded_by uuid REFERENCES axis_assignment(id), FOREIGN KEY (axis_code, value_code) REFERENCES axis_value(axis_code, value_code), CONSTRAINT chk_provenance_or_quarantine CHECK (provenance IS NOT NULL OR zone='quarantine')); CREATE UNIQUE INDEX uq_axis_assignment_active ON axis_assignment (object_type,object_ref,axis_code,value_code) WHERE status='active'; CREATE INDEX ix_axis_assignment_axis_zone ON axis_assignment (axis_code, zone);

CREATE TABLE coverage_rule ( rule_code text PRIMARY KEY, axis_code text NOT NULL REFERENCES axis_registry(axis_code), applies_to text NOT NULL DEFAULT '*', coverage_mode text NOT NULL CHECK (coverage_mode IN ('cross_product','per_assignment')), required_owner_kind text NOT NULL DEFAULT 'accountable', confidence_threshold numeric(5,4) NOT NULL DEFAULT 0.0, staleness_days integer NOT NULL DEFAULT 0, status text NOT NULL DEFAULT 'candidate' CHECK (status IN ('candidate','active','deprecated','retired')), created_at timestamptz NOT NULL DEFAULT now(), created_by text NOT NULL);

CREATE TABLE axis_object_ownership ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, object_type text NOT NULL, object_ref text NOT NULL, axis_code text NOT NULL, value_code text NOT NULL, owner_kind text NOT NULL DEFAULT 'accountable' CHECK (owner_kind IN ('accountable','responsible','consulted','informed')), owner_gov_code text NOT NULL, status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','superseded','retracted')), provenance jsonb, approval_ref text, created_at timestamptz NOT NULL DEFAULT now(), created_by text NOT NULL, FOREIGN KEY (axis_code, value_code) REFERENCES axis_value(axis_code, value_code)); CREATE UNIQUE INDEX uq_axis_obj_accountable ON axis_object_ownership (object_type,object_ref,axis_code,value_code) WHERE owner_kind='accountable' AND status='active';

CREATE TABLE axis_candidate_finding ( finding_type text NOT NULL, object_type text NOT NULL DEFAULT '-', object_ref text NOT NULL DEFAULT '-', axis_code text NOT NULL DEFAULT '-', value_code text NOT NULL DEFAULT '-', severity text NOT NULL DEFAULT 'info' CHECK (severity IN ('info','warning','critical')), detail jsonb, first_seen timestamptz NOT NULL DEFAULT now(), last_seen timestamptz NOT NULL DEFAULT now(), scan_count integer NOT NULL DEFAULT 1, PRIMARY KEY (finding_type, object_type, object_ref, axis_code, value_code));

-- Generalized coverage engine CREATE VIEW v_axis_required_cell AS SELECT inv.object_type, inv.object_ref, cr.axis_code, av.value_code, cr.rule_code, cr.required_owner_kind FROM coverage_rule cr JOIN axis_value av ON av.axis_code=cr.axis_code AND av.status='active' JOIN v_governance_object_inventory inv ON (cr.applies_to='' OR cr.applies_to=inv.object_type) WHERE cr.status='active' AND cr.coverage_mode='cross_product' AND inv.requires_owner=true AND inv.born=true UNION ALL SELECT aa.object_type, aa.object_ref, cr.axis_code, aa.value_code, cr.rule_code, cr.required_owner_kind FROM coverage_rule cr JOIN axis_assignment aa ON aa.axis_code=cr.axis_code AND aa.status='active' JOIN axis_value av ON av.axis_code=aa.axis_code AND av.value_code=aa.value_code AND av.status='active' WHERE cr.status='active' AND cr.coverage_mode='per_assignment'; CREATE VIEW v_axis_effective_owner AS SELECT eo.object_type, eo.object_ref, 'responsibility'::text AS axis_code, eo.scope AS value_code, eo.owner_gov_code, eo.owner_kind FROM v_object_effective_owner eo UNION ALL SELECT aoo.object_type, aoo.object_ref, aoo.axis_code, aoo.value_code, aoo.owner_gov_code, aoo.owner_kind FROM axis_object_ownership aoo WHERE aoo.status='active'; CREATE VIEW v_axis_coverage_gap AS SELECT r.object_type, r.object_ref, r.axis_code, r.value_code, r.rule_code FROM v_axis_required_cell r LEFT JOIN v_axis_effective_owner eo ON eo.axis_code=r.axis_code AND eo.object_type=r.object_type AND eo.object_ref=r.object_ref AND eo.value_code=r.value_code AND eo.owner_kind=r.required_owner_kind WHERE eo.owner_gov_code IS NULL; CREATE VIEW v_axis_assignment_quality AS SELECT aa.id, aa.object_type, aa.object_ref, aa.axis_code, aa.value_code, aa.zone, aa.confidence, aa.match_score, aa.assigned_kind, aa.assigned_at, cr.confidence_threshold, cr.staleness_days, CASE WHEN aa.zone='quarantine' THEN 'quarantine' WHEN aa.provenance IS NULL THEN 'missing_provenance' WHEN cr.confidence_threshold>0 AND COALESCE(aa.confidence,0) < cr.confidence_threshold THEN 'low_confidence' WHEN cr.staleness_days>0 AND aa.assigned_at < now() - make_interval(days => cr.staleness_days) THEN 'stale' ELSE 'ok' END AS quality_flag FROM axis_assignment aa LEFT JOIN coverage_rule cr ON cr.axis_code=aa.axis_code AND cr.status='active' WHERE aa.status='active'; CREATE VIEW v_axis_registry_coverage AS SELECT ar.axis_code, ar.axis_name, ar.axis_family, ar.axis_kind, ar.status, (SELECT count() FROM axis_value av WHERE av.axis_code=ar.axis_code AND av.status='active') AS active_values, (SELECT count() FROM axis_value av WHERE av.axis_code=ar.axis_code AND av.status='candidate') AS candidate_values, (SELECT count() FROM axis_assignment aa WHERE aa.axis_code=ar.axis_code AND aa.status='active') AS active_assignments, (SELECT count(*) FROM coverage_rule cr WHERE cr.axis_code=ar.axis_code AND cr.status='active') AS active_rules FROM axis_registry ar;

-- Seed responsibility axis ACTIVE (mirror 6 live scopes) INSERT INTO axis_registry (axis_code, axis_name, axis_family, axis_kind, value_domain, owner_scope_ref, status, lifecycle_status, created_by) VALUES ('responsibility','Responsibility scopes (existing model)','system','system','responsibility_scope', NULL, 'active','active','TEST_CLONE_AXIS_BUILD'); INSERT INTO axis_value (axis_code, value_code, description, status, provenance, created_by) SELECT 'responsibility', scope_code, description, 'active', jsonb_build_object('source','governance_responsibility_scope','designated_by','existing_model'), 'TEST_CLONE_AXIS_BUILD' FROM governance_responsibility_scope WHERE status='active'; INSERT INTO coverage_rule (rule_code, axis_code, applies_to, coverage_mode, required_owner_kind, status, created_by) VALUES ('CR-RESP-ALL','responsibility','collection','cross_product','accountable','active','TEST_CLONE_AXIS_BUILD');

-- Zero-drift gate, then COMMIT DO $$ DECLARE v_req int; v_gap int; v_canon int; v_tables int; BEGIN SELECT count() INTO v_tables FROM information_schema.tables WHERE table_schema='public' AND table_name IN ('axis_registry','axis_value','axis_assignment','coverage_rule','axis_object_ownership','axis_candidate_finding'); SELECT count() INTO v_req FROM v_axis_required_cell; SELECT count() INTO v_gap FROM v_axis_coverage_gap; SELECT count() INTO v_canon FROM v_object_owner_gap; IF v_tables<>6 THEN RAISE EXCEPTION 'BUILD FAIL: expected 6 substrate tables, got %', v_tables; END IF; IF v_req<>210 THEN RAISE EXCEPTION 'BUILD FAIL: required cells = % (expected 210)', v_req; END IF; IF v_gap<>0 THEN RAISE EXCEPTION 'BUILD FAIL: coverage gap = % (expected 0)', v_gap; END IF; IF v_gap<>v_canon THEN RAISE EXCEPTION 'BUILD FAIL: generalized gap % != canonical %', v_gap, v_canon; END IF; RAISE NOTICE 'BUILD OK: 6 tables, required=210, gap=0, canonical=0 (zero drift). Committing.'; END $$; COMMIT; \echo ====================AXIS_BUILD_POST_COMMIT==================== SELECT * FROM v_axis_registry_coverage ORDER BY axis_code; \echo ====================DONE====================