PIDX Test Plan v0.1 — SELECT-only post-build validation

Path: knowledge/dev/laws-new/workflow-manage/design/pidx-test-plan-v0.1.md Status: TEST PLAN · SELECT-ONLY · runs AFTER a governed build + seed. Every query below is read-only. Nothing here mutates PG. This macro did NOT run them (the objects do not exist yet). Date: 2026-06-23 Asserts: the expected outcomes in pidx-seed-slice-v0.1.md §3, using the views in pidx-ddl-candidate-v0.1.sql.md.

---

0. How to read this plan

• Run only after pidx_procedure + pidx_procedure_ingredient exist, the two views exist, and the 9-row seed slice is inserted.
• Per-ingredient status is exposed via the readiness view's ingredients jsonb array. The reusable extractor:

-- Per-ingredient unnest helper (read-only)
SELECT r.procedure_code, j->>'kind' AS kind, j->>'ref' AS ref,
       j->>'computed_status' AS computed_status, j->'warnings' AS warnings,
       j->>'required_level' AS required_level
FROM public.v_pidx_procedure_readiness r,
     LATERAL jsonb_array_elements(COALESCE(r.ingredients,'[]'::jsonb)) j;

---

1. The 13 required test cases

T1 — Procedure lookup by procedure_code

SELECT procedure_code, computed_readiness, readiness_drift, warning_flags
FROM public.v_pidx_procedure_readiness
WHERE procedure_code = 'PROC_CHECK_DOT_EXISTS';

Expect (1 row): computed_readiness='READY', readiness_drift=false, warning_flags={}.

T2 — Ingredient list for one procedure

SELECT j->>'kind' AS kind, j->>'ref' AS ref, j->>'computed_status' AS status, j->>'required_level' AS lvl
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code = 'PROC_CREATE_NEW_DOT'
ORDER BY ref;

Expect (4 rows): procedure:PROC_REUSE_FIRST_SEARCH=MISSING(required), collection:public.dot_tools=EXISTS(required), approval:patch_ops_code=EXISTS(required), template:dot_spec_template=UNKNOWN_SOURCE(nice_to_have).

T3 — dot: existing object → EXISTS

SELECT j->>'computed_status' AS status
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_CHECK_DOT_EXISTS' AND j->>'ref'='dot:DOT-001';

Expect: status='EXISTS'. (DOT-001 confirmed live in dot_tools.)

T4 — dot: fake object → MISSING

SELECT j->>'computed_status' AS status
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_RESOLVE_INVALID_REF' AND j->>'ref'='dot:DOT_PIDX_FAKE_0000';

Expect: status='MISSING'.

T5 — collection: existing table → EXISTS

SELECT j->>'computed_status' AS status
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_VERIFY_COLLECTION_EXISTS' AND j->>'ref'='collection:public.directus_collections';

Expect: status='EXISTS'.

T6 — field: existing column → EXISTS

SELECT j->>'computed_status' AS status
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_VERIFY_FIELD_EXISTS' AND j->>'ref'='field:public.dot_tools.code';

Expect: status='EXISTS'.

T7 — field: fake column → MISSING

SELECT j->>'computed_status' AS status
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_VERIFY_FIELD_EXISTS' AND j->>'ref'='field:public.dot_tools.does_not_exist';

Expect: status='MISSING'.

T8 — malformed ref → INVALID_REF

SELECT j->>'computed_status' AS status
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_RESOLVE_INVALID_REF' AND j->>'ref'='field:public.dot_tools';

Expect: status='INVALID_REF' (no column part + ref_status='UNNORMALIZED').

T9 — io/checker/template/report → UNKNOWN_SOURCE

SELECT j->>'ref' AS ref, j->>'computed_status' AS status
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE j->>'ref' IN ('template:dot_spec_template','report:dot-usage-handbook','report:ref-grammar-v0.1')
ORDER BY ref;

Expect (3 rows): all status='UNKNOWN_SOURCE'. (Add an io:/checker: ref to extend; same expectation.)

T10 — approval with unimplemented handler → warning flag

SELECT j->>'computed_status' AS status, j->'warnings' AS warnings
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_CLASSIFY_COLLECTION' AND j->>'ref'='approval:assign_governance_owner';

Expect: status='EXISTS', warnings contains APPROVAL_HANDLER_UNIMPLEMENTED. (Live: assign_governance_owner is unimplemented.) And the procedure rollup:

SELECT computed_readiness, warning_flags FROM public.v_pidx_procedure_readiness
WHERE procedure_code='PROC_CLASSIFY_COLLECTION';

Expect: computed_readiness='READY_WITH_WARNINGS', warning_flags ⊇ {APPROVAL_HANDLER_UNIMPLEMENTED}.

T11 — label ambiguous → warning flag (conditional on test data)

-- Probe whether any bare taxonomy code is multi-facet (only meaningful if such data exists):
SELECT code, count(DISTINCT facet_id) AS facets
FROM public.taxonomy GROUP BY code HAVING count(DISTINCT facet_id) > 1 LIMIT 5;

Expect: if ≥1 row, declare label:<that_code> (bare) on a scratch ingredient and assert warnings ⊇ {AMBIGUOUS_LABEL}, status EXISTS. If 0 rows, mark N/A — no ambiguous label in current data (do not fabricate one).

T12 — declared ready but computed not ready → READINESS_DRIFT

SELECT procedure_code, declared_maturity, computed_readiness, readiness_drift
FROM public.v_pidx_procedure_readiness
WHERE procedure_code='PROC_CREATE_NEW_DOT';

Expect: declared_maturity='dot_sequence_ready', computed_readiness='NOT_READY', readiness_drift=true.

T13 — zero-ingredient procedure → UNMAPPED, never silent READY

SELECT procedure_code, ingredient_count, computed_readiness
FROM public.v_pidx_procedure_readiness
WHERE procedure_code='PROC_TRIAGE_UNKNOWN_SOURCE';

Expect: ingredient_count=0, computed_readiness='UNMAPPED' (NOT READY).

---

2. Supplementary checks (warning flags not covered by the 9-seed slice)

S1 — READ_BLOCKED (restricted schema)

SELECT j->>'computed_status' AS status
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_RESOLVE_INVALID_REF' AND j->>'ref'='collection:cutter_governance.policy_state';

Expect: status='READ_BLOCKED'.

S2 — OVERLOADED_FUNCTION (inventory branch unit check)

SELECT object_ref, warning_flags
FROM public.v_pidx_inventory_current
WHERE object_kind='function' AND 'OVERLOADED_FUNCTION' = ANY(warning_flags)
LIMIT 5;

Expect: ≥0 rows; any row's object_ref is a function name with >1 signature. (Confirms the overload detector fires on real catalog data.)

S3 — full readiness snapshot (all 9 seeds at once)

SELECT procedure_code, computed_readiness, readiness_drift, warning_flags, ingredient_count
FROM public.v_pidx_procedure_readiness
WHERE procedure_code LIKE 'PROC_%'
ORDER BY procedure_code;

Expect: matches pidx-seed-slice-v0.1.md §3 exactly (READY×3, READY_WITH_WARNINGS×2, NOT_READY×3, UNMAPPED×1; drift true only for PROC_CREATE_NEW_DOT).

S4 — anti-false-green assertion (no silent READY)

-- A procedure may be READY only if it has >=1 required ingredient and ALL required are EXISTS:
SELECT procedure_code FROM public.v_pidx_procedure_readiness
WHERE computed_readiness='READY'
  AND (ingredient_count = 0 OR req_missing_count > 0 OR req_unknown_count > 0 OR req_dataq_count > 0);

Expect: 0 rows. Any row here is a false-green bug.

S5 — hot-path narrowness (cost sanity)

EXPLAIN (ANALYZE false)
SELECT * FROM public.v_pidx_procedure_readiness WHERE procedure_code = 'PROC_CHECK_DOT_EXISTS';

Expect: plan filters on pidx_procedure PK + pidx_proc_ingr_procedure_idx; it must not materialize the whole v_pidx_inventory_current / scan all of information_schema.columns to answer one procedure.

---

3. Output-shape contract (per-procedure row)

procedure_code     text        e.g. 'PROC_CHECK_DOT_EXISTS'
computed_readiness text        UNMAPPED | NOT_READY | READY_WITH_WARNINGS | READY
readiness_drift    boolean
warning_flags      text[]      subset of {OVERLOADED_FUNCTION, AMBIGUOUS_LABEL, LOGICAL_PHYSICAL_MISMATCH,
                                          APPROVAL_HANDLER_UNIMPLEMENTED, STALE_SOURCE, UNKNOWN_SOURCE_ACCEPTED}
ingredient_count   int
ingredients        jsonb[]     [{kind, ref, computed_status, required_level, missing_route, warnings[]}]

4. Coverage matrix (test → status proven)

status / flag	proven by
EXISTS	T3, T5, T6
MISSING	T4, T7
UNKNOWN_SOURCE	T9
INVALID_REF	T8
READ_BLOCKED	S1
READY	T1, S3
READY_WITH_WARNINGS	T10, S3 (PROC_VERIFY_FIELD_EXISTS)
NOT_READY	T12, S3
UNMAPPED	T13
READINESS_DRIFT	T12
APPROVAL_HANDLER_UNIMPLEMENTED	T10
OVERLOADED_FUNCTION	S2
AMBIGUOUS_LABEL	T11 (conditional)
anti-false-green invariant	S4
hot-path narrowness	S5

All 13 required cases + 5 supplementary checks are SELECT-only and run post-build.