PIDX Candidate DDL + View SQL + Rollback v0.2 — TEXT ONLY

Path: knowledge/dev/laws-new/workflow-manage/design/pidx-ddl-candidate-v0.2.sql.md Status: CANDIDATE SQL · TEXT ONLY · NOT EXECUTED · 0 PG objects created · 0 DDL/DML run. Every statement below is design text for Codex static re-review and a later Owner-authorized governed build. Nothing here was run. Date: 2026-06-23 · target DB observed: directus (PostgreSQL 16.13 on VPS) · schema public · read-only role used for fact verification: context_pack_readonly Patches addressed: P0-1, P0-2, P0-3, P0-4, P0-5 (BLOCK); P1-1, P1-2, P1-3, P1-4, P1-5, P1-6, P1-7 (HIGH); P2-1, P2-2, P2-3, P2-4, P2-5 (MEDIUM). Companions: pidx-build-design-v0.2.md, pidx-readiness-logic-v0.2.md, pidx-seed-slice-v0.2.md, pidx-test-plan-v0.2.md.

---

0. What changed from v0.1 (SQL-level)

#	v0.1 defect (Codex)	v0.2 SQL change
P0-1	warning_flags aggregated by a correlated scalar subquery over grouped columns s.w_fn/s.w_appr/s.w_label → view likely will not CREATE.	Per-ingredient warnings are emitted as a text[] (warns); a dedicated CTE proc_warn aggregates array_agg(DISTINCT w) keyed by procedure_code; the result is LEFT JOINed once into the rollup. No grouped-column reference inside a correlated subquery.
P0-2	No strict parser: ingredient_kind drove the probe but the prefix was never checked; split_part ignored extra/missing segments; ref_status trusted.	One resolver contract: parse prefix+ident on the FIRST colon, require prefix = ingredient_kind, enforce exact per-kind segment arity, reject empty segments, compute validity independently of ref_status. INVALID_REF is decided before any existence probe.
P0-3	Logical-collection probe ignored schema → false EXISTS; inventory started from physical tables only → logical-only rows missing; mismatch one-sided.	Collection existence = physical base table (information_schema.tables, BASE TABLE). Logical (Directus) presence computed separately, public-namespaced. LOGICAL_PHYSICAL_MISMATCH = true XOR in public. Logical-only → MISSING (no false green). Inventory branch rewritten as a FULL OUTER JOIN so logical-only folders appear.
P0-4	label:facet.code compared as taxonomy.code='facet.code'; facet_id never parsed; inventory emitted only bare refs.	Label parses facet vs bare: label:<facet_id>.<code> resolves facet_id::text + code; bare label:<code> resolves by code and warns AMBIGUOUS_LABEL only when the bare code matches > 1 taxonomy row.
P0-5	A procedure with only optional ingredients reached ELSE 'READY'.	Rollup computes required_count; required_count = 0 → UNMAPPED (never READY).
P1-1	Existence conflated with usability; inactive/retired objects satisfied readiness.	Per-ingredient usable tri-state (true/false/unknown) per lifecycle-bearing kind. A required found-but-not-usable ingredient is unsatisfied → NOT_READY; an optional one warns SOURCE_NOT_USABLE. Kinds without lifecycle → usable=unknown (not downgraded; no overclaim).
P1-2	Only 3 warnings produced; warning_flags could be empty under READY_WITH_WARNINGS.	All flags produced. Optional MISSING/UNKNOWN_SOURCE/INVALID_REF/READ_BLOCKED and untriaged required-level are surfaced as explicit flags. Invariant enforced: READY ⇒ empty warning_flags; READY_WITH_WARNINGS ⇒ non-empty.
P1-3	Required unimplemented approval handler → only READY_WITH_WARNINGS.	Required unimplemented handler ⇒ usable=false ⇒ NOT_READY (safe rule). Optional ⇒ READY_WITH_WARNINGS + APPROVAL_HANDLER_UNIMPLEMENTED.
P1-4	ON UPDATE CASCADE ON DELETE CASCADE; mutable text PK; no surrogate identity.	FK is ON UPDATE RESTRICT ON DELETE RESTRICT. Surrogate immutable id (GENERATED ALWAYS AS IDENTITY) added to both tables; procedure_code kept UNIQUE NOT NULL (immutable by policy; FK target).
P1-5	PIDX tables unclassified vs Điều 33.	Classified (Owner decision documented in build-design §11). Canonical column names adopted (name, status, date_created, date_updated, surrogate id, domain code) so either classification needs minimal change.
P1-6	Rollback unconditional; approximate source counts.	Hardened: preflight collision/dependency snapshot, transactional drop, fail-closed on unexpected dependents/non-seed data, exact catalog fingerprint before/after, source-readability proof.
P2-1	readiness_drift CASE duplicated the rollup.	computed_readiness computed once in CTE roll; readiness_drift derived from it in the outer SELECT.
P2-2	Inventory emitted one row per function overload / per trigger event.	Function branch groups by (schema,name) with overload count/signatures in metadata; trigger branch groups by canonical trigger ref with the event set in metadata.
P2-3	READ_BLOCKED hardcoded to two schema names.	Derived from has_schema_privilege(current_user, <schema_oid>, 'USAGE'), guarded so a non-existent schema is MISSING, not an error.
P2-4	missing_route never resolved; nullable readiness booleans.	missing_route_exists is probed and exposed per ingredient; readiness_drift is COALESCE-guarded to a non-null boolean.
P2-5	Build design claimed links live in non-existent links_jsonb.	No links_jsonb. Links remain deferred; procedure→procedure relations are expressed only as a procedure: ingredient or missing_route_procedure_code. manifest_jsonb is a non-authoritative note.

---

1. Constraint-strictness decision (unchanged direction, hardened)

Decision: SOFT storage of ingredient_ref + hard enum CHECKs + computed INVALID_REF in the view. A bad ref must be recordable (so it can be triaged and routed) and surfaced as INVALID_REF by the computed layer, not bounced at insert.

What changed: the detection is no longer soft. In v0.1 the view trusted ref_status and split_part; in v0.2 the readiness view runs the strict parser (§5) and decides INVALID_REF independently of ref_status. So liberal write-side storage is preserved while the computed layer is strict. The optional write-side normalizer (below) only sets ref_status/canonical form; it never gates the row.

Hard enum CHECKs retained (cheap, closed sets): ingredient_kind, required_level, usage_role, ref_status, and declared_maturity, automation_mode, safety_class, status on the procedure table.

---

2. Candidate table — pidx_procedure

-- CANDIDATE — DO NOT EXECUTE. Governed/owner-approved build only.
CREATE TABLE public.pidx_procedure (
    id                 bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- surrogate, immutable identity
    procedure_code     text        NOT NULL UNIQUE,                            -- stable natural key; immutable by policy; FK target & join key
    name               text        NOT NULL,                                   -- canonical label (Điều 33 'name'); was v0.1 'title'
    short_note         text,
    intent             text,
    domain_group       text        NOT NULL DEFAULT 'UNMAPPED',
    assembly_layer     text        NOT NULL DEFAULT 'UNMAPPED',
    declared_maturity  text        CHECK (declared_maturity IS NULL OR declared_maturity IN
                                     ('known','text_ready','machine_readable',
                                      'checklist_ready','dot_sequence_ready','one_button_ready')),
    automation_mode    text        CHECK (automation_mode IS NULL OR automation_mode IN
                                     ('manual','checklist','dot_sequence','one_button','agent_assisted')),
    safety_class       text        CHECK (safety_class IS NULL OR safety_class IN
                                     ('read_only','ai_delegatable','owner_gated')),
    status             text        NOT NULL DEFAULT 'draft'
                                   CHECK (status IN ('draft','active','retired')),
    manifest_jsonb     jsonb       NOT NULL DEFAULT '{}'::jsonb,                -- light structured NOTE/cache only, never truth
    source_ref         text,                                                   -- provenance (KB path / WF-* / DOT chain)
    date_created       timestamptz NOT NULL DEFAULT now(),
    date_updated       timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE  public.pidx_procedure IS
  'PG-native Procedure Index: one row = one known procedure (DECLARED hints/cache; readiness is computed by v_pidx_procedure_readiness, never stored here). Classification: PG-native technical index table (see build-design v0.2 §11).';
COMMENT ON COLUMN public.pidx_procedure.procedure_code IS
  'Stable natural key. IMMUTABLE BY POLICY: never UPDATE it. To replace a procedure, set status=retired and insert a new code. FK target of pidx_procedure_ingredient.';
COMMENT ON COLUMN public.pidx_procedure.declared_maturity IS
  'HINT only. computed_readiness in v_pidx_procedure_readiness overrides; a ready-tier hint contradicting NOT_READY/UNMAPPED raises readiness_drift.';
COMMENT ON COLUMN public.pidx_procedure.manifest_jsonb IS
  'Light structured note. NEVER a source of truth and NEVER able to set READY. There is no links_jsonb column; procedure links are deferred (build-design v0.2 §1.4).';
COMMENT ON COLUMN public.pidx_procedure.date_updated IS
  'Declared row-update time, maintained by the governed write/seed path. NOT a PG-catalog freshness fact; inventory freshness for catalog kinds is computed live in the views.';

Notes:

• assembly_layer / domain_group default UNMAPPED — the survey proved procedures carry no layer metadata today; they are never a blocker.
• No computed columns. computed_readiness, existence, usable, freshness are produced only by the readiness/inventory views at query time.
• id (surrogate) gives an immutable identity even though procedure_code is the join key, resolving the P1-4 identity question and the Điều 33 canonical-id expectation.

---

3. Candidate table — pidx_procedure_ingredient

-- CANDIDATE — DO NOT EXECUTE.
CREATE TABLE public.pidx_procedure_ingredient (
    id                            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    procedure_code                text  NOT NULL
                                        REFERENCES public.pidx_procedure(procedure_code)
                                        ON UPDATE RESTRICT ON DELETE RESTRICT,   -- v0.1 was CASCADE/CASCADE
    step_code                     text,                          -- optional step grouping (declared)
    ingredient_kind               text  NOT NULL
                                        CHECK (ingredient_kind IN
                                          ('dot','collection','view','field','trigger','function',
                                           'approval','event','procedure','label',
                                           'io','checker','template','report')),
    ingredient_ref                text  NOT NULL,                -- normalized ref, grammar v0.2 (declared). NO regex CHECK (see §1)
    usage_role                    text  CHECK (usage_role IS NULL OR usage_role IN
                                          ('reads','writes','calls','requires','produces')),
    required_level                text  NOT NULL DEFAULT 'required'
                                        CHECK (required_level IN
                                          ('required','optional','nice_to_have','UNKNOWN','NEEDS_TRIAGE')),
    source_probe_kind             text,                          -- declared hint: which inventory branch should verify
    missing_route_procedure_code  text,                          -- SOFT ref (NOT a FK — see note); where to go if MISSING
    ref_status                    text  NOT NULL DEFAULT 'NORMALIZED'
                                        CHECK (ref_status IN ('NORMALIZED','UNNORMALIZED')),
    note                          text,
    date_created                  timestamptz NOT NULL DEFAULT now(),
    date_updated                  timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE  public.pidx_procedure_ingredient IS
  'Ingredient Map: one row = one ingredient a procedure DECLARES it needs. Lazy human/Agent cache; NEVER truth. Existence/usability/readiness are computed by v_pidx_procedure_readiness.';
COMMENT ON COLUMN public.pidx_procedure_ingredient.ingredient_ref IS
  'Ref grammar v0.2 join key. Validity is computed independently of ref_status by the readiness view; a malformed ref yields INVALID_REF, never a guessed probe.';
COMMENT ON COLUMN public.pidx_procedure_ingredient.ref_status IS
  'Write-side normalization HINT only. UNNORMALIZED forces INVALID_REF; NORMALIZED is NOT trusted to mean valid — the view re-parses.';
COMMENT ON COLUMN public.pidx_procedure_ingredient.missing_route_procedure_code IS
  'SOFT reference only. Deliberately NOT a FK: the routed-to procedure may not be seeded yet. The readiness view exposes missing_route_exists.';

FK is RESTRICT/RESTRICT (P1-4). A pidx_procedure row cannot be deleted while it has ingredient rows, and procedure_code cannot be updated while referenced. Deletion is therefore explicit and lifecycle-safe: retire (status='retired'), do not delete/rename. This prevents the silent ingredient erasure CASCADE allowed.

---

4. Candidate indexes

-- CANDIDATE — DO NOT EXECUTE.
-- pidx_procedure: PRIMARY KEY (id) and UNIQUE (procedure_code) are auto-indexed.
CREATE INDEX pidx_procedure_group_layer_idx
    ON public.pidx_procedure (domain_group, assembly_layer);          -- treeview: assembly_layer x domain_group

CREATE INDEX pidx_proc_ingr_procedure_idx
    ON public.pidx_procedure_ingredient (procedure_code);             -- HOT PATH: fetch one procedure's ingredients

CREATE INDEX pidx_proc_ingr_kind_ref_idx
    ON public.pidx_procedure_ingredient (ingredient_kind, ingredient_ref);  -- reverse lookup: who uses object X

-- OPTIONAL anti-duplicate guard (PG15+ NULLS NOT DISTINCT so a NULL step_code still de-dupes):
-- CREATE UNIQUE INDEX pidx_proc_ingr_uniq
--   ON public.pidx_procedure_ingredient (procedure_code, step_code, ingredient_kind, ingredient_ref) NULLS NOT DISTINCT;

The optional unique index is commented out for v0.2 to avoid over-constraining liberal declarations; enable once seeding settles (PG16 supports NULLS NOT DISTINCT).

---

5. Candidate view — v_pidx_procedure_readiness (the computed-truth layer)

Grain = one row per procedure (LEFT JOIN ingredients so zero-ingredient procedures still surface as UNMAPPED). The strict parser/resolver is the heart of this view. Per-ingredient detail is embedded as a jsonb array for debugging, keeping the core at 2 views. Full semantics in pidx-readiness-logic-v0.2.md.

-- CANDIDATE — DO NOT EXECUTE. Pure read-only. One strict parser/resolver; narrow correlated EXISTS per ref.
CREATE VIEW public.v_pidx_procedure_readiness AS
WITH
-- (t1) tokenize on the FIRST colon: prefix + identifier
t1 AS (
  SELECT
    i.id, i.procedure_code,
    i.ingredient_kind                                       AS kind,
    i.ingredient_ref                                        AS ref,
    i.ref_status, i.required_level, i.usage_role,
    i.missing_route_procedure_code,
    (strpos(i.ingredient_ref, ':') > 0)                     AS has_colon,
    CASE WHEN strpos(i.ingredient_ref, ':') = 0 THEN NULL
         ELSE split_part(i.ingredient_ref, ':', 1) END      AS prefix,
    CASE WHEN strpos(i.ingredient_ref, ':') = 0 THEN NULL
         ELSE substr(i.ingredient_ref, strpos(i.ingredient_ref, ':') + 1) END AS ident
  FROM public.pidx_procedure_ingredient i
),
-- (t2) split the identifier on literal '.' (string_to_array, NOT regex — no escaping ambiguity)
t2 AS (
  SELECT t1.*,
    CASE WHEN t1.ident IS NULL THEN NULL ELSE string_to_array(t1.ident, '.') END AS seg
  FROM t1
),
-- (t3) segment metrics + decomposed parts
t3 AS (
  SELECT t2.*,
    COALESCE(array_length(t2.seg, 1), 0)                    AS nseg,
    (t2.seg IS NOT NULL AND '' = ANY(t2.seg))               AS has_empty_seg,
    (t2.prefix IS NOT NULL AND t2.prefix = t2.kind)         AS kind_matches_prefix,
    t2.seg[1]                                               AS s1,
    t2.seg[2]                                               AS s2,
    t2.seg[3]                                               AS s3
  FROM t2
),
-- (t4) THE STRICT PARSE: structurally_valid is decided here, independent of ref_status.
t4 AS (
  SELECT t3.*,
    ( t3.has_colon
      AND t3.ident IS NOT NULL AND length(t3.ident) > 0
      AND NOT t3.has_empty_seg
      AND t3.kind_matches_prefix
      AND CASE t3.kind                                       -- exact per-kind arity
            WHEN 'dot'        THEN t3.nseg = 1
            WHEN 'approval'   THEN t3.nseg = 1
            WHEN 'procedure'  THEN t3.nseg = 1
            WHEN 'event'      THEN t3.nseg >= 2              -- domain + type (type may contain '.')
            WHEN 'label'      THEN t3.nseg IN (1, 2)         -- bare code, or facet.code
            WHEN 'collection' THEN t3.nseg = 2               -- schema.table (schema REQUIRED)
            WHEN 'view'       THEN t3.nseg = 2
            WHEN 'function'   THEN t3.nseg = 2               -- schema.name (name-only; argtypes deferred)
            WHEN 'field'      THEN t3.nseg = 3
            WHEN 'trigger'    THEN t3.nseg = 3
            WHEN 'io'         THEN t3.nseg >= 1              -- shape-ok; resolves UNKNOWN_SOURCE
            WHEN 'checker'    THEN t3.nseg >= 1
            WHEN 'template'   THEN t3.nseg >= 1
            WHEN 'report'     THEN t3.nseg >= 1
            ELSE false
          END
    )                                                        AS structurally_valid,
    -- event type = everything after the FIRST dot
    CASE WHEN t3.kind = 'event' AND t3.nseg >= 2
         THEN substr(t3.ident, length(t3.s1) + 2) END        AS e_type
  FROM t3
),
-- (t5) probe: READ_BLOCKED, logical-collection presence, existence (found). Probes run ONLY when valid.
t5 AS (
  SELECT t4.*,
    -- READ_BLOCKED: schema exists but querying role lacks USAGE (privilege-derived; OID form avoids errors on missing schema)
    ( t4.kind IN ('collection','view','field','trigger','function')
      AND t4.structurally_valid
      AND NOT COALESCE(
            (SELECT has_schema_privilege(current_user, n.oid, 'USAGE')
             FROM pg_namespace n WHERE n.nspname = t4.s1),
            true) )                                          AS read_blocked,
    -- logical (Directus) presence for collection, public-namespaced only
    CASE WHEN t4.kind = 'collection' AND t4.structurally_valid AND t4.s1 = 'public'
         THEN EXISTS (SELECT 1 FROM public.directus_collections dc WHERE dc.collection = t4.s2)
         ELSE false END                                      AS coll_logical,
    -- existence probe (found) — false unless structurally_valid; io/checker/template/report -> false (UNKNOWN_SOURCE later)
    CASE WHEN NOT t4.structurally_valid THEN false
      ELSE
        CASE t4.kind
          WHEN 'dot'        THEN EXISTS (SELECT 1 FROM public.dot_tools d WHERE d.code = t4.ident)
          WHEN 'approval'   THEN EXISTS (SELECT 1 FROM public.apr_action_types a WHERE a.action_code = t4.ident)
          WHEN 'procedure'  THEN EXISTS (SELECT 1 FROM public.pidx_procedure pp WHERE pp.procedure_code = t4.ident)
                                 OR EXISTS (SELECT 1 FROM public.workflows w WHERE w.process_code = t4.ident)
          WHEN 'event'      THEN EXISTS (SELECT 1 FROM public.event_type_registry e
                                          WHERE e.event_domain = t4.s1 AND e.event_type = t4.e_type)
          WHEN 'label'      THEN CASE WHEN t4.nseg = 2
                                   THEN EXISTS (SELECT 1 FROM public.taxonomy tx
                                                WHERE tx.facet_id::text = t4.s1 AND tx.code = t4.s2)
                                   ELSE EXISTS (SELECT 1 FROM public.taxonomy tx WHERE tx.code = t4.ident) END
          WHEN 'collection' THEN EXISTS (SELECT 1 FROM information_schema.tables t
                                          WHERE t.table_schema = t4.s1 AND t.table_name = t4.s2
                                            AND t.table_type = 'BASE TABLE')
          WHEN 'view'       THEN EXISTS (SELECT 1 FROM information_schema.views vw
                                          WHERE vw.table_schema = t4.s1 AND vw.table_name = t4.s2)
                                 OR EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
                                          WHERE c.relkind = 'm' AND n.nspname = t4.s1 AND c.relname = t4.s2)
          WHEN 'field'      THEN EXISTS (SELECT 1 FROM information_schema.columns c
                                          WHERE c.table_schema = t4.s1 AND c.table_name = t4.s2
                                            AND c.column_name = t4.s3)
          WHEN 'trigger'    THEN EXISTS (SELECT 1 FROM information_schema.triggers tr
                                          WHERE tr.trigger_schema = t4.s1 AND tr.event_object_table = t4.s2
                                            AND tr.trigger_name = t4.s3)
          WHEN 'function'   THEN EXISTS (SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
                                          WHERE n.nspname = t4.s1 AND p.proname = t4.s2)
          ELSE false
        END
    END                                                      AS found
  FROM t4
),
-- (t6) enrich: usability (tri-state), overload count, label multiplicity; then computed_status
t6 AS (
  SELECT t5.*,
    CASE WHEN NOT t5.found THEN NULL
      ELSE
        CASE t5.kind
          WHEN 'dot' THEN
            (SELECT CASE WHEN d.status IN ('active','published') THEN true
                         WHEN d.status IN ('retired','deprecated','disabled','inactive','archived') THEN false
                         ELSE NULL END
             FROM public.dot_tools d WHERE d.code = t5.ident LIMIT 1)
          WHEN 'approval' THEN
            (SELECT CASE WHEN a.handler_ref IS NULL OR a.handler_ref = 'unimplemented' THEN false
                         WHEN a.status IN ('retired','disabled','inactive') THEN false
                         WHEN a.status = 'active' THEN true
                         ELSE NULL END
             FROM public.apr_action_types a WHERE a.action_code = t5.ident LIMIT 1)
          WHEN 'event' THEN
            (SELECT e.active
             FROM public.event_type_registry e
             WHERE e.event_domain = t5.s1 AND e.event_type = t5.e_type LIMIT 1)
          WHEN 'procedure' THEN COALESCE(
            (SELECT CASE WHEN pp.status = 'active' THEN true
                         WHEN pp.status IN ('retired','draft') THEN false ELSE NULL END
             FROM public.pidx_procedure pp WHERE pp.procedure_code = t5.ident LIMIT 1),
            (SELECT CASE WHEN w.status = 'active' THEN true
                         WHEN w.status = 'draft' THEN false ELSE NULL END
             FROM public.workflows w WHERE w.process_code = t5.ident LIMIT 1))
          WHEN 'label' THEN
            (SELECT CASE WHEN tx.status IN ('active','published') AND tx.replaced_by IS NULL THEN true
                         WHEN tx.status IN ('retired','deprecated','replaced') OR tx.replaced_by IS NOT NULL THEN false
                         ELSE NULL END
             FROM public.taxonomy tx
             WHERE (t5.nseg = 2 AND tx.facet_id::text = t5.s1 AND tx.code = t5.s2)
                OR (t5.nseg = 1 AND tx.code = t5.ident)
             LIMIT 1)
          ELSE NULL   -- CATALOG kinds (collection/view/field/trigger/function): no lifecycle -> unknown
        END
    END                                                      AS usable,
    CASE WHEN t5.kind = 'function' AND t5.found
         THEN (SELECT count(*) FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
               WHERE n.nspname = t5.s1 AND p.proname = t5.s2)
         ELSE 0 END                                          AS fn_count,
    CASE WHEN t5.kind = 'label' AND t5.nseg = 1 AND t5.found
         THEN (SELECT count(*) FROM public.taxonomy tx WHERE tx.code = t5.ident)
         ELSE 0 END                                          AS lbl_count,
    CASE
      WHEN t5.ref_status = 'UNNORMALIZED' OR NOT t5.structurally_valid     THEN 'INVALID_REF'
      WHEN t5.kind IN ('io','checker','template','report')                 THEN 'UNKNOWN_SOURCE'
      WHEN t5.read_blocked                                                 THEN 'READ_BLOCKED'
      WHEN t5.found                                                        THEN 'EXISTS'
      ELSE 'MISSING'
    END                                                      AS computed_status,
    (t5.required_level = 'required')                         AS is_required
  FROM t5
),
-- (t7) per-ingredient warning set + satisfaction + route resolution
t7 AS (
  SELECT t6.*,
    array_remove(ARRAY[
      CASE WHEN t6.kind = 'function' AND t6.fn_count > 1 THEN 'OVERLOADED_FUNCTION' END,
      CASE WHEN t6.kind = 'label' AND t6.nseg = 1 AND t6.lbl_count > 1 THEN 'AMBIGUOUS_LABEL' END,
      CASE WHEN t6.kind = 'collection' AND t6.structurally_valid AND t6.s1 = 'public'
                AND (t6.found <> t6.coll_logical) THEN 'LOGICAL_PHYSICAL_MISMATCH' END,
      CASE WHEN t6.kind = 'approval' AND t6.found AND t6.usable IS FALSE THEN 'APPROVAL_HANDLER_UNIMPLEMENTED' END,
      CASE WHEN t6.kind <> 'approval' AND t6.computed_status = 'EXISTS' AND t6.usable IS FALSE THEN 'SOURCE_NOT_USABLE' END,
      CASE WHEN NOT t6.is_required AND t6.computed_status = 'MISSING' THEN 'OPTIONAL_MISSING' END,
      CASE WHEN NOT t6.is_required AND t6.computed_status = 'UNKNOWN_SOURCE' THEN 'OPTIONAL_UNKNOWN_SOURCE' END,
      CASE WHEN NOT t6.is_required AND t6.computed_status = 'INVALID_REF' THEN 'OPTIONAL_INVALID_REF' END,
      CASE WHEN NOT t6.is_required AND t6.computed_status = 'READ_BLOCKED' THEN 'OPTIONAL_READ_BLOCKED' END,
      CASE WHEN t6.required_level IN ('UNKNOWN','NEEDS_TRIAGE') THEN 'REQUIRED_LEVEL_UNTRIAGED' END
    ], NULL)                                                 AS warns,
    (t6.computed_status = 'EXISTS' AND t6.usable IS NOT FALSE) AS req_satisfied,
    CASE WHEN t6.missing_route_procedure_code IS NULL THEN NULL
         ELSE EXISTS (SELECT 1 FROM public.pidx_procedure pr
                      WHERE pr.procedure_code = t6.missing_route_procedure_code) END AS missing_route_exists
  FROM t6
),
-- (pw) per-procedure DISTINCT warning flags — P0-1 fix: clean grouped CTE, joined ONCE
proc_warn AS (
  SELECT t7.procedure_code, array_agg(DISTINCT w ORDER BY w) AS warning_flags
  FROM t7, LATERAL unnest(t7.warns) AS w
  GROUP BY t7.procedure_code
),
-- (agg) per-procedure counts + embedded ingredient detail
agg AS (
  SELECT
    p.procedure_code, p.name, p.domain_group, p.assembly_layer,
    p.declared_maturity, p.automation_mode, p.safety_class, p.status,
    count(f.id)                                                         AS ingredient_count,
    count(f.id) FILTER (WHERE f.is_required)                            AS required_count,
    count(f.id) FILTER (WHERE f.is_required AND NOT f.req_satisfied)    AS req_unsatisfied_count,
    count(f.id) FILTER (WHERE f.is_required AND f.computed_status = 'MISSING')        AS req_missing_count,
    count(f.id) FILTER (WHERE f.is_required AND f.computed_status = 'UNKNOWN_SOURCE') AS req_unknown_count,
    count(f.id) FILTER (WHERE f.is_required AND f.computed_status = 'INVALID_REF')    AS req_invalid_count,
    count(f.id) FILTER (WHERE f.is_required AND f.computed_status = 'READ_BLOCKED')   AS req_blocked_count,
    count(f.id) FILTER (WHERE f.is_required AND f.computed_status = 'EXISTS' AND f.usable IS FALSE) AS req_notusable_count,
    count(f.id) FILTER (WHERE f.computed_status = 'EXISTS')             AS exists_count,
    count(f.id) FILTER (WHERE cardinality(f.warns) > 0)                AS warned_count,
    jsonb_agg(jsonb_build_object(
        'id', f.id, 'kind', f.kind, 'ref', f.ref,
        'computed_status', f.computed_status, 'usable', f.usable,
        'required_level', f.required_level, 'is_required', f.is_required,
        'warnings', f.warns,
        'missing_route', f.missing_route_procedure_code,
        'missing_route_exists', f.missing_route_exists)
        ORDER BY f.id) FILTER (WHERE f.id IS NOT NULL)                AS ingredients
  FROM public.pidx_procedure p
  LEFT JOIN t7 f ON f.procedure_code = p.procedure_code
  GROUP BY p.procedure_code, p.name, p.domain_group, p.assembly_layer,
           p.declared_maturity, p.automation_mode, p.safety_class, p.status
),
-- (roll) compute computed_readiness ONCE (P2-1); attach warning_flags
roll AS (
  SELECT a.*,
    COALESCE(pw.warning_flags, ARRAY[]::text[])                         AS warning_flags,
    CASE
      WHEN a.ingredient_count = 0                                       THEN 'UNMAPPED'
      WHEN a.required_count   = 0                                       THEN 'UNMAPPED'   -- P0-5: zero-required != READY
      WHEN a.req_unsatisfied_count > 0                                 THEN 'NOT_READY'
      WHEN COALESCE(cardinality(pw.warning_flags), 0) > 0             THEN 'READY_WITH_WARNINGS'
      ELSE 'READY'
    END                                                                AS computed_readiness
  FROM agg a
  LEFT JOIN proc_warn pw ON pw.procedure_code = a.procedure_code
)
SELECT
  r.procedure_code, r.name, r.domain_group, r.assembly_layer,
  r.declared_maturity, r.automation_mode, r.safety_class, r.status,
  r.ingredient_count, r.required_count, r.req_unsatisfied_count,
  r.req_missing_count, r.req_unknown_count, r.req_invalid_count,
  r.req_blocked_count, r.req_notusable_count, r.exists_count, r.warned_count,
  r.computed_readiness,
  r.warning_flags,
  ( COALESCE(r.declared_maturity IN ('checklist_ready','dot_sequence_ready','one_button_ready'), false)
    AND r.computed_readiness IN ('NOT_READY','UNMAPPED') )             AS readiness_drift,   -- non-null boolean (P2-4)
  COALESCE(r.ingredients, '[]'::jsonb)                                  AS ingredients
FROM roll r;

5.1 Why each BLOCK can no longer false-green (read the SQL)

• P0-1: warning_flags comes from proc_warn (a GROUP BY procedure_code CTE), LEFT-JOINed once in roll. No grouped column is referenced inside a correlated subquery, so the grouped-SELECT rule cannot reject it.
• P0-2: structurally_valid in t4 requires kind_matches_prefix and exact nseg arity and no empty segment, all decided before found is computed (found=false unless structurally_valid). INVALID_REF wins in computed_status precedence. ref_status='UNNORMALIZED' also forces INVALID_REF, but NORMALIZED is not trusted — the parse re-runs.
• P0-3: collection found = physical base table only; coll_logical is separate and public-namespaced; LOGICAL_PHYSICAL_MISMATCH = found <> coll_logical in public. A logical-only Directus folder has found=false → MISSING (+ mismatch warn), never EXISTS.
• P0-4: label parses nseg=2 as facet_id::text + code; bare nseg=1 resolves by code and warns AMBIGUOUS_LABEL only when lbl_count > 1.
• P0-5: required_count = 0 → UNMAPPED precedes the READY branch.

---

6. Candidate view — v_pidx_inventory_current (browsing / treeview; NOT the hot path)

A UNION ALL over EXISTING sources, normalized to the 14-column contract (pidx-build-design-v0.2.md §4.1). The readiness view does not read this view. Function/trigger branches are de-duplicated (P2-2); the collection branch surfaces logical-only rows via FULL OUTER JOIN (P0-3).

-- CANDIDATE — DO NOT EXECUTE. Pure read-only view; copies nothing.
CREATE VIEW public.v_pidx_inventory_current AS

-- 1) dot  <- dot_tools (authoritative_registry); usability from status
SELECT 'dot'::text                          AS object_kind,
       'dot:' || d.code                     AS object_ref,
       d.code                               AS object_code,
       d.name                               AS object_name,
       'public'::text                       AS source_schema,
       'dot_tools'::text                    AS source_object,
       d.code                               AS source_pk,
       true                                 AS exists_bool,
       'authoritative_registry'::text       AS authority_level,
       jsonb_build_object('status', d.status,
                          'usable', (d.status IN ('active','published'))) AS metadata_jsonb,
       'fresh'::text                        AS freshness_status,
       d.date_updated                       AS last_seen_at,
       NULL::text                           AS notes,
       CASE WHEN d.status NOT IN ('active','published')
            THEN ARRAY['SOURCE_NOT_USABLE']::text[] ELSE ARRAY[]::text[] END AS warning_flags
FROM public.dot_tools d

UNION ALL
-- 2a) collection (public): physical base tables FULL OUTER JOIN Directus logical -> surfaces logical-only folders
SELECT 'collection',
       'collection:public.' || COALESCE(t.table_name, dc.collection),
       COALESCE(t.table_name, dc.collection),
       COALESCE(t.table_name, dc.collection),
       'public', 'information_schema.tables+directus_collections',
       'public.' || COALESCE(t.table_name, dc.collection),
       (t.table_name IS NOT NULL),                                   -- exists_bool = physical present
       'authoritative_catalog',
       jsonb_build_object('physical', (t.table_name IS NOT NULL),
                          'logical',  (dc.collection IS NOT NULL)),
       'fresh', now(), NULL,
       CASE WHEN (t.table_name IS NOT NULL) <> (dc.collection IS NOT NULL)
            THEN ARRAY['LOGICAL_PHYSICAL_MISMATCH']::text[] ELSE ARRAY[]::text[] END
FROM (SELECT table_name FROM information_schema.tables
      WHERE table_schema = 'public' AND table_type = 'BASE TABLE') t
FULL OUTER JOIN public.directus_collections dc ON dc.collection = t.table_name

UNION ALL
-- 2b) collection (non-public physical base tables; no Directus logical counterpart possible)
SELECT 'collection',
       'collection:' || t.table_schema || '.' || t.table_name,
       t.table_name, t.table_name, t.table_schema, 'information_schema.tables',
       t.table_schema || '.' || t.table_name, true, 'authoritative_catalog',
       jsonb_build_object('physical', true, 'logical', false),
       'fresh', now(), NULL, ARRAY[]::text[]
FROM information_schema.tables t
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('pg_catalog','information_schema','public')

UNION ALL
-- 3) view <- information_schema.views (relkind 'v') + matviews (relkind 'm'); disjoint, no dup
SELECT 'view', 'view:' || v.table_schema || '.' || v.table_name,
       v.table_name, v.table_name, v.table_schema, 'information_schema.views',
       v.table_schema || '.' || v.table_name, true, 'authoritative_catalog',
       jsonb_build_object('relkind','v'), 'fresh', now(), NULL, ARRAY[]::text[]
FROM information_schema.views v
WHERE v.table_schema NOT IN ('pg_catalog','information_schema')
UNION ALL
SELECT 'view', 'view:' || n.nspname || '.' || c.relname,
       c.relname, c.relname, n.nspname, 'pg_class',
       n.nspname || '.' || c.relname, true, 'authoritative_catalog',
       jsonb_build_object('relkind','m'), 'fresh', now(), NULL, ARRAY[]::text[]
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'm' AND n.nspname NOT IN ('pg_catalog','information_schema')

UNION ALL
-- 4) field <- information_schema.columns (HEAVY — exposed, never on the readiness hot path)
SELECT 'field',
       'field:' || c.table_schema || '.' || c.table_name || '.' || c.column_name,
       c.column_name, c.column_name, c.table_schema, 'information_schema.columns',
       c.table_schema || '.' || c.table_name || '.' || c.column_name, true,
       'authoritative_catalog', jsonb_build_object('data_type', c.data_type),
       'fresh', now(), NULL, ARRAY[]::text[]
FROM information_schema.columns c
WHERE c.table_schema NOT IN ('pg_catalog','information_schema')

UNION ALL
-- 5) trigger <- information_schema.triggers, DE-DUPLICATED to one canonical ref (P2-2); event set in metadata
SELECT 'trigger',
       'trigger:' || tr.trigger_schema || '.' || tr.event_object_table || '.' || tr.trigger_name,
       tr.trigger_name, tr.trigger_name, tr.trigger_schema, 'information_schema.triggers',
       tr.trigger_schema || '.' || tr.event_object_table || '.' || tr.trigger_name, true,
       'authoritative_catalog',
       jsonb_build_object('events', array_agg(DISTINCT tr.event_manipulation ORDER BY tr.event_manipulation)),
       'fresh', now(), NULL, ARRAY[]::text[]
FROM information_schema.triggers tr
WHERE tr.trigger_schema NOT IN ('pg_catalog','information_schema')
GROUP BY tr.trigger_schema, tr.event_object_table, tr.trigger_name

UNION ALL
-- 6) function <- pg_proc, DE-DUPLICATED to one row per (schema,name) (P2-2); overload count/signatures in metadata
SELECT 'function', 'function:' || n.nspname || '.' || p.proname,
       p.proname, p.proname, n.nspname, 'pg_proc',
       n.nspname || '.' || p.proname, true, 'authoritative_catalog',
       jsonb_build_object('overload_count', count(*),
                          'signatures', array_agg(pg_get_function_identity_arguments(p.oid) ORDER BY p.oid)),
       'fresh', now(), NULL,
       CASE WHEN count(*) > 1 THEN ARRAY['OVERLOADED_FUNCTION']::text[] ELSE ARRAY[]::text[] END
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
GROUP BY n.nspname, p.proname

UNION ALL
-- 7) approval <- apr_action_types; flag unimplemented handlers + usability
SELECT 'approval', 'approval:' || a.action_code,
       a.action_code, a.description, 'public', 'apr_action_types',
       a.action_code, true, 'authoritative_registry',
       jsonb_build_object('risk_level', a.risk_level, 'handler_ref', a.handler_ref, 'status', a.status,
                          'usable', (a.handler_ref IS NOT NULL AND a.handler_ref <> 'unimplemented' AND a.status = 'active')),
       'fresh', a.created_at, NULL,
       CASE WHEN a.handler_ref IS NULL OR a.handler_ref = 'unimplemented'
            THEN ARRAY['APPROVAL_HANDLER_UNIMPLEMENTED']::text[] ELSE ARRAY[]::text[] END
FROM public.apr_action_types a

UNION ALL
-- 8) event <- event_type_registry; flag inactive (usability)
SELECT 'event', 'event:' || e.event_domain || '.' || e.event_type,
       e.event_type, e.description, 'public', 'event_type_registry',
       e.event_domain || '.' || e.event_type, true, 'authoritative_registry',
       jsonb_build_object('active', e.active, 'severity', e.default_severity),
       'fresh', e.created_at, NULL,
       CASE WHEN NOT e.active THEN ARRAY['SOURCE_NOT_USABLE']::text[] ELSE ARRAY[]::text[] END
FROM public.event_type_registry e

UNION ALL
-- 9) procedure <- pidx_procedure (self) UNION workflows.process_code (legacy BPMN)
SELECT 'procedure', 'procedure:' || pr.procedure_code,
       pr.procedure_code, pr.name, 'public', 'pidx_procedure',
       pr.procedure_code, true, 'authoritative_registry',
       jsonb_build_object('declared_maturity', pr.declared_maturity, 'status', pr.status),
       'fresh', pr.date_updated, NULL,
       CASE WHEN pr.status <> 'active' THEN ARRAY['SOURCE_NOT_USABLE']::text[] ELSE ARRAY[]::text[] END
FROM public.pidx_procedure pr
UNION ALL
SELECT 'procedure', 'procedure:' || w.process_code,
       w.process_code, w.title, 'public', 'workflows',
       w.process_code, true, 'authoritative_registry',
       jsonb_build_object('source','bpmn','status', w.status), 'fresh', w.date_updated, NULL,
       CASE WHEN w.status <> 'active' THEN ARRAY['SOURCE_NOT_USABLE']::text[] ELSE ARRAY[]::text[] END
FROM public.workflows w
WHERE w.process_code IS NOT NULL

UNION ALL
-- 10) label <- taxonomy; emit facet-qualified ref + flag bare-code multi-facet ambiguity (P0-4); usability
SELECT 'label', 'label:' || tx.facet_id::text || '.' || tx.code,
       tx.code, tx.name, 'public', 'taxonomy',
       tx.id::text, true, 'authoritative_registry',
       jsonb_build_object('facet_id', tx.facet_id, 'depth', tx.depth, 'status', tx.status,
                          'usable', (tx.status IN ('active','published') AND tx.replaced_by IS NULL)),
       'fresh', tx.date_created, NULL,
       array_remove(ARRAY[
         CASE WHEN count(*) OVER (PARTITION BY tx.code) > 1 THEN 'AMBIGUOUS_LABEL' END,
         CASE WHEN NOT (tx.status IN ('active','published') AND tx.replaced_by IS NULL) THEN 'SOURCE_NOT_USABLE' END
       ], NULL)
FROM public.taxonomy tx;

io, checker, template, report are not branches — they have no clean PG SSOT in v0.2 and resolve to UNKNOWN_SOURCE inside the readiness view, emitting no inventory rows. The label branch emits the facet-qualified canonical ref label:<facet_id>.<code> (P0-4). AMBIGUOUS_LABEL is armed but inert on current data (0 multi-facet codes verified 2026-06-23).

---

7. Rollback / drop script (Task 9 — hardened, text only)

Hardened per P1-6/H5: preflight collision/dependency snapshot, transactional drop, fail-closed on unexpected dependents or non-seed data, exact catalog fingerprint, source-readability proof.

7.1 Preflight (read-only; run and inspect BEFORE the transaction)

-- (P-1) Which of the four PIDX objects currently exist? (expect exactly the set the build created)
SELECT n.nspname, c.relname, c.relkind
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname IN ('pidx_procedure','pidx_procedure_ingredient',
                    'v_pidx_inventory_current','v_pidx_procedure_readiness')
ORDER BY c.relname;

-- (P-2) Unexpected dependents: any object OUTSIDE the four PIDX objects that depends on them.
--       STOP (do not drop) if this returns any row.
SELECT DISTINCT dn.nspname AS dependent_schema, dc.relname AS dependent_object, dc.relkind
FROM pg_depend d
JOIN pg_class sc ON sc.oid = d.refobjid
JOIN pg_namespace sn ON sn.oid = sc.relnamespace
JOIN pg_rewrite rw ON rw.oid = d.objid
JOIN pg_class dc ON dc.oid = rw.ev_class
JOIN pg_namespace dn ON dn.oid = dc.relnamespace
WHERE sn.nspname = 'public'
  AND sc.relname IN ('pidx_procedure','pidx_procedure_ingredient',
                     'v_pidx_inventory_current','v_pidx_procedure_readiness')
  AND dc.relname NOT IN ('pidx_procedure','pidx_procedure_ingredient',
                         'v_pidx_inventory_current','v_pidx_procedure_readiness');
-- expect: 0 rows. Any row = an external dependent -> STOP and reconcile before rollback.

-- (P-3) Non-seed / data-present proof: count rows whose source_ref is NOT a known seed marker.
--       If > 0, BACK UP before dropping (the table holds non-seed declarations).
SELECT count(*) AS non_seed_procedures
FROM public.pidx_procedure
WHERE source_ref IS DISTINCT FROM 'seed:pidx-seed-slice-v0.2';
-- (and dump both tables if non_seed_procedures > 0 or any ingredient rows are not seed-derived)

7.2 Transactional drop (fail-closed)

-- CANDIDATE ROLLBACK — DO NOT EXECUTE NOW.
BEGIN;

-- Guard: abort if any UNEXPECTED dependent exists (re-checks P-2 inside the transaction).
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM pg_depend d
    JOIN pg_class sc ON sc.oid = d.refobjid
    JOIN pg_namespace sn ON sn.oid = sc.relnamespace
    JOIN pg_rewrite rw ON rw.oid = d.objid
    JOIN pg_class dc ON dc.oid = rw.ev_class
    JOIN pg_namespace dn ON dn.oid = dc.relnamespace
    WHERE sn.nspname = 'public'
      AND sc.relname IN ('pidx_procedure','pidx_procedure_ingredient',
                         'v_pidx_inventory_current','v_pidx_procedure_readiness')
      AND dc.relname NOT IN ('pidx_procedure','pidx_procedure_ingredient',
                             'v_pidx_inventory_current','v_pidx_procedure_readiness')
  ) THEN
    RAISE EXCEPTION 'pidx rollback aborted: unexpected external dependent present';
  END IF;
END $$;

-- Safe order: views before tables, child before parent. NO CASCADE (unexpected deps fail closed).
DROP VIEW  IF EXISTS public.v_pidx_procedure_readiness;
DROP VIEW  IF EXISTS public.v_pidx_inventory_current;
DROP TABLE IF EXISTS public.pidx_procedure_ingredient;   -- child (FK to pidx_procedure)
DROP TABLE IF EXISTS public.pidx_procedure;              -- parent
-- Indexes and the IDENTITY sequences drop automatically with their tables.

COMMIT;

7.3 Post-rollback verification (read-only; exact, not approximate)

-- (a) confirm all four pidx objects are absent (exact catalog fingerprint)
SELECT count(*) AS pidx_objects_remaining
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname IN ('pidx_procedure','pidx_procedure_ingredient',
                    'v_pidx_inventory_current','v_pidx_procedure_readiness');
-- expect: 0

-- (b) confirm NO non-pidx object name changed: compare a hash of the public-object name set
--     against the pre-build fingerprint captured in the build runbook.
SELECT md5(string_agg(c.relname, ',' ORDER BY c.relname)) AS public_object_fingerprint
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r','v','m')
  AND c.relname NOT LIKE 'pidx_%' AND c.relname NOT LIKE 'v_pidx_%';
-- expect: identical to the pre-build fingerprint (proves only PIDX objects changed)

-- (c) confirm source catalogs/tables still readable (no permission/lock damage)
SELECT 1 FROM information_schema.tables  LIMIT 1;
SELECT 1 FROM information_schema.columns LIMIT 1;
SELECT 1 FROM pg_proc                    LIMIT 1;
SELECT count(*) AS dot_tools_rows        FROM public.dot_tools;
SELECT count(*) AS apr_action_types_rows FROM public.apr_action_types;

---

8. Build-order note (for the eventual governed build, not this macro)

1. CREATE TABLE pidx_procedure → 2. CREATE TABLE pidx_procedure_ingredient (FK needs parent first) → 3. indexes → 4. CREATE VIEW v_pidx_inventory_current → 5. CREATE VIEW v_pidx_procedure_readiness. Capture the §7.3(b) pre-build fingerprint first. Seed rows (pidx-seed-slice-v0.2.md) only after the two tables exist. All of this is Owner-gated, governed, idempotent (Điều 33 E1: paired Cấp-B apply / Cấp-A verify DOT), and out of scope for this design macro.