-- ============================================================================ -- G_prod_01 — PRODUCTION AXIS DDL + RESPONSIBILITY SEED (GATED, NOT EXECUTED) -- Run on production directus. Single BEGIN..COMMIT, self-guarding: -- * refuses unless current_database()='directus' -- * refuses unless os_proposal_approvals >= 1 (ratification gate) -- * refuses if axis tables already present (idempotency guard) -- * asserts generalized gap == canonical 210 (ZERO drift) before COMMIT -- This is the SAME proven clone DDL (sql/A_…), mirrored with the PARALLEL -- axis_object_ownership table so governance_object_ownership is NEVER altered. -- Seeds ONLY the responsibility axis ACTIVE -> effective coverage UNCHANGED -- (gap stays 210; ownership seed is the separate, already-gated F_prod packet). -- Topic/containment are NOT seeded here (they enter later as candidate, governed). -- ============================================================================ \set ON_ERROR_STOP on \pset pager off BEGIN;

DO $$ DECLARE v_db text; v_ospa int; v_axis int; BEGIN SELECT current_database() INTO v_db; IF v_db <> 'directus' THEN RAISE EXCEPTION 'REFUSING: % is not production directus', v_db; END IF; SELECT count() INTO v_ospa FROM os_proposal_approvals; IF v_ospa < 1 THEN RAISE EXCEPTION 'REFUSING: ratification gate not met (os_proposal_approvals=%)', v_ospa; END IF; SELECT count() INTO v_axis 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'); IF v_axis <> 0 THEN RAISE EXCEPTION 'REFUSING: axis substrate already present (% tables)', v_axis; END IF; END $$;

-- ---- DDL (identical to clone build A_…; see that file for column rationale) -- 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);

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));

-- ---- Views (identical to clone) --------------------------------------------- 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 axis_code, eo.scope 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;

-- ---- Seed responsibility axis ACTIVE (mirror 6 live scopes) ------------------ INSERT INTO axis_registry (axis_code,axis_name,axis_family,axis_kind,value_domain,status,lifecycle_status,created_by) VALUES ('responsibility','Responsibility scopes (existing model)','system','system','responsibility_scope','active','active','AXIS_ROLLOUT'); 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'),'AXIS_ROLLOUT' 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','AXIS_ROLLOUT');

-- ---- ZERO-DRIFT assertion: generalized gap must equal canonical gap ---------- DO $$ DECLARE v_req int; v_gap int; v_canon int; BEGIN 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_req <> 210 THEN RAISE EXCEPTION 'ROLLOUT FAIL: required cells = % (expected 210)', v_req; END IF; IF v_gap <> v_canon THEN RAISE EXCEPTION 'ROLLOUT FAIL: generalized gap % != canonical %', v_gap, v_canon; END IF; RAISE NOTICE 'ROLLOUT OK: required=%, generalized gap=% == canonical=% (zero drift). Committing.', v_req, v_gap, v_canon; END $$;

COMMIT; \echo ==================== AXIS_ROLLOUT_COMMITTED ====================