PIDX Test Plan v0.2 — SELECT-only post-build validation (deterministic)

Path: knowledge/dev/laws-new/workflow-manage/design/pidx-test-plan-v0.2.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). All expectations are deterministic — no >=0 rows, no hidden DML. Date: 2026-06-23 Asserts: pidx-seed-slice-v0.2.md §3 outcomes, using the views in pidx-ddl-candidate-v0.2.sql.md. Run as a role lacking USAGE on cutter_governance/sandbox_tac (e.g. context_pack_readonly) so the READ_BLOCKED case is deterministic.

0. How to read this plan

Run only after pidx_procedure + pidx_procedure_ingredient exist, the two views exist, and the 13-row seed slice is inserted.
Per-ingredient status is exposed via the readiness view's ingredients jsonb array. 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->>'usable' AS usable,
       j->'warnings' AS warnings, j->>'required_level' AS required_level,
       j->>'missing_route' AS missing_route, j->>'missing_route_exists' AS missing_route_exists
FROM public.v_pidx_procedure_readiness r,
     LATERAL jsonb_array_elements(COALESCE(r.ingredients,'[]'::jsonb)) j;

1. Required test cases (deterministic)

T1 — EXISTS (`dot:` existing) → status + usable

SELECT j->>'computed_status' AS status, j->>'usable' AS usable
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', usable='true'.

T2 — MISSING (`dot:` fake)

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'.

T3 — UNKNOWN_SOURCE (`template:`/`report:`)

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'.

T4 — INVALID_REF, four distinct malformations (P0-2 core)

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

Expect (4 rows), all status='INVALID_REF':

collection:dot_tools (no schema, arity 1≠2)
dot:patch_ops_code (declared kind=approval; prefix dot≠approval) — the v0.1 false-EXISTS case (patch_ops_code is a real approval); v0.2 rejects it pre-probe.
event:collection_created (missing domain, arity 1<2)
field:public.dot_tools.code.extra (extra segment, arity 4≠3)

T5 — INVALID_REF independent of `ref_status` (P0-2: stored flag is only a hint)

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' (arity 2≠3). (Seeded ref_status='UNNORMALIZED'; but T4's four are NORMALIZED and still INVALID_REF — proving validity is computed, not trusted.)

T6 — READ_BLOCKED (privilege-derived; 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' (role lacks USAGE on cutter_governance; object existence is irrelevant — the schema is unreadable, so MISSING would be a lie).

T7 — EXISTS for `collection:` (physical+logical, no mismatch)

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_VERIFY_COLLECTION_EXISTS' AND j->>'ref'='collection:public.collection_registry';

Expect: status='EXISTS', warnings=[] (physical ✓ and Directus ✓).

T8 — LOGICAL_PHYSICAL_MISMATCH, physical-only (P0-3)

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_FIXTURE_LIFECYCLE_AND_WARN' AND j->>'ref'='collection:public.directus_collections';

Expect: status='EXISTS', warnings ⊇ ["LOGICAL_PHYSICAL_MISMATCH"] (physical table present, not a Directus collection).

T9 — LOGICAL_PHYSICAL_MISMATCH, logical-only → MISSING, no false green (P0-3)

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_FIXTURE_LOGICAL_ONLY' AND j->>'ref'='collection:public.sales';

Expect: status='MISSING', warnings ⊇ ["LOGICAL_PHYSICAL_MISMATCH"] (Directus folder sales has no physical table). Procedure rollup = NOT_READY (see T18 row 12).

T10 — APPROVAL_HANDLER_UNIMPLEMENTED, required → NOT_READY (P1-3 safe rule)

SELECT j->>'computed_status' AS status, j->>'usable' AS usable, 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', usable='false', warnings ⊇ ["APPROVAL_HANDLER_UNIMPLEMENTED"]. Procedure rollup:

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

Expect: computed_readiness='NOT_READY', warning_flags={APPROVAL_HANDLER_UNIMPLEMENTED}.

T11 — SOURCE_NOT_USABLE, lifecycle (inactive event) (P1-1)

SELECT j->>'computed_status' AS status, j->>'usable' AS usable, j->'warnings' AS warnings
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_FIXTURE_LIFECYCLE_AND_WARN' AND j->>'ref'='event:governance.backfill.sweep_completed';

Expect: status='EXISTS', usable='false', warnings ⊇ ["SOURCE_NOT_USABLE"] (event active=false; it is non-gating here, so it warns rather than blocks). Companion (required-not-usable blocks): T10 already proves a required unusable ingredient → NOT_READY.

T12 — OVERLOADED_FUNCTION (H3)

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_FIXTURE_LIFECYCLE_AND_WARN' AND j->>'ref'='function:public.pgp_pub_decrypt';

Expect: status='EXISTS', warnings ⊇ ["OVERLOADED_FUNCTION"] (3 signatures). Still satisfied (overload is a warning, not unusable).

SELECT j->>'ref' AS ref, j->>'computed_status' AS status, j->'warnings' AS warnings
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE j->>'ref'='label:1.LBL-001';

Expect: status='EXISTS', warnings=[] (facet_id=1 + code LBL-001). Deterministic AMBIGUOUS_LABEL pre-check (proves the flag is armed, data is inert):

SELECT count(*) AS multi_facet_codes
FROM (SELECT code FROM public.taxonomy GROUP BY code HAVING count(DISTINCT facet_id) > 1) z;

Expect: multi_facet_codes = 0 → AMBIGUOUS_LABEL cannot fire on current data; N/A is proven, not assumed. (If this ever returns > 0, a bare label:<that_code> must carry AMBIGUOUS_LABEL and must not be bare-READY.)

T14 — zero-INGREDIENT procedure → UNMAPPED

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

Expect: ingredient_count=0, required_count=0, computed_readiness='UNMAPPED'.

T15 — zero-REQUIRED procedure (has optional ingredients) → UNMAPPED, never silent READY (P0-5)

SELECT ingredient_count, required_count, exists_count, computed_readiness, warning_flags
FROM public.v_pidx_procedure_readiness WHERE procedure_code='PROC_FIXTURE_NONGATING';

Expect: ingredient_count=2, required_count=0, exists_count=2, computed_readiness='UNMAPPED' (NOT READY), warning_flags={}.

T16 — READINESS_DRIFT (declared ready-tier vs computed not-ready)

SELECT procedure_code, declared_maturity, computed_readiness, readiness_drift
FROM public.v_pidx_procedure_readiness
WHERE procedure_code IN ('PROC_CREATE_NEW_DOT','PROC_FIXTURE_LOGICAL_ONLY','PROC_FIXTURE_NONGATING')
ORDER BY procedure_code;

Expect: all three readiness_drift=true; PROC_CREATE_NEW_DOT (dot_sequence_ready→NOT_READY), PROC_FIXTURE_LOGICAL_ONLY (one_button_ready→NOT_READY), PROC_FIXTURE_NONGATING (one_button_ready→UNMAPPED).

T17 — READY_WITH_WARNINGS has non-empty causally-complete `warning_flags` (P1-2)

SELECT procedure_code, computed_readiness, warning_flags
FROM public.v_pidx_procedure_readiness
WHERE procedure_code IN ('PROC_VERIFY_FIELD_EXISTS','PROC_FIXTURE_LIFECYCLE_AND_WARN')
ORDER BY procedure_code;

Expect:

PROC_FIXTURE_LIFECYCLE_AND_WARN: READY_WITH_WARNINGS, warning_flags={LOGICAL_PHYSICAL_MISMATCH,OVERLOADED_FUNCTION,REQUIRED_LEVEL_UNTRIAGED,SOURCE_NOT_USABLE}.
PROC_VERIFY_FIELD_EXISTS: READY_WITH_WARNINGS, warning_flags={OPTIONAL_MISSING}.

T18 — full readiness snapshot (all 13 seeds at once)

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

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

T19 — missing-route resolution (P2-4)

SELECT j->>'ref' AS ref, j->>'missing_route' AS route, j->>'missing_route_exists' AS route_exists
FROM public.v_pidx_procedure_readiness r, LATERAL jsonb_array_elements(r.ingredients) j
WHERE r.procedure_code='PROC_CREATE_NEW_DOT' AND j->>'ref'='procedure:PROC_REUSE_FIRST_SEARCH';

Expect: route='PROC_DESIGN_NEW_PROCEDURE', route_exists='false' (route target not seeded → routing to "create it" is the point; the boolean is surfaced, not null).

2. Invariant + safety checks

S1 — anti-false-green invariant: no READY without ≥1 required, all-satisfied (P0-5/P1-1/P1-3)

SELECT procedure_code FROM public.v_pidx_procedure_readiness
WHERE computed_readiness='READY'
  AND (required_count = 0
       OR req_missing_count > 0 OR req_unknown_count > 0
       OR req_invalid_count > 0 OR req_blocked_count > 0 OR req_notusable_count > 0);

Expect: 0 rows. Any row is a false-green bug. (Covers zero-required, required-missing/unknown/invalid/blocked, and required-not-usable in one assertion.)

S2 — green invariant: `READY` ⇒ empty flags; `READY_WITH_WARNINGS` ⇒ non-empty flags (P1-2)

SELECT procedure_code, computed_readiness, warning_flags FROM public.v_pidx_procedure_readiness
WHERE (computed_readiness='READY'               AND cardinality(warning_flags) <> 0)
   OR (computed_readiness='READY_WITH_WARNINGS' AND cardinality(warning_flags) =  0);

Expect: 0 rows.

S3 — OVERLOADED_FUNCTION fires on real catalog data (deterministic, not `>=0`)

SELECT 'function:public.pgp_pub_decrypt' = ANY(
   SELECT object_ref FROM public.v_pidx_inventory_current
   WHERE object_kind='function' AND 'OVERLOADED_FUNCTION' = ANY(warning_flags)) AS overload_detected;

Expect: overload_detected = true (pgp_pub_decrypt has 3 signatures, verified 2026-06-23).

S4 — inventory collection branch surfaces logical-only folders (P0-3)

SELECT object_ref, metadata_jsonb->>'physical' AS physical, metadata_jsonb->>'logical' AS logical, warning_flags
FROM public.v_pidx_inventory_current
WHERE object_ref='collection:public.sales';

Expect (1 row): physical='false', logical='true', warning_flags={LOGICAL_PHYSICAL_MISMATCH} (logical-only folder now appears via FULL OUTER JOIN — v0.1 omitted it).

S5 — inventory de-dup: one row per function name (P2-2)

SELECT object_ref, count(*) AS rows, (metadata_jsonb->>'overload_count') AS overload_count
FROM public.v_pidx_inventory_current
WHERE object_ref='function:public.pgp_pub_decrypt'
GROUP BY object_ref, metadata_jsonb->>'overload_count';

Expect: rows=1, overload_count='3' (one row, not three).

S6 — 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/UNIQUE) + pidx_proc_ingr_procedure_idx; it must not materialize v_pidx_inventory_current or scan all of information_schema.columns.

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

procedure_code      text
name                text
domain_group        text
assembly_layer      text
declared_maturity   text (nullable hint)
automation_mode     text (nullable hint)
safety_class        text (nullable hint)
status              text  draft|active|retired
ingredient_count    int
required_count      int
req_unsatisfied_count int
req_missing_count / req_unknown_count / req_invalid_count / req_blocked_count / req_notusable_count  int
exists_count        int
warned_count        int
computed_readiness  text  UNMAPPED | NOT_READY | READY_WITH_WARNINGS | READY
warning_flags       text[]  (DISTINCT, sorted; subset of the 11-flag catalog)
readiness_drift     boolean (NON-NULL)
ingredients         jsonb   [{id,kind,ref,computed_status,usable,required_level,is_required,warnings[],missing_route,missing_route_exists}]

ingredients is a jsonb array value (one column holding a JSON array), not SQL type jsonb[] — corrected from the v0.1 contract per Codex §11.

4. Coverage matrix (test → proven)

status / flag / property	proven by
EXISTS	T1, T7, T12, T13
MISSING	T2, T9
UNKNOWN_SOURCE	T3
INVALID_REF (prefix/arity/extra/missing/no-schema)	T4, T5
READ_BLOCKED (privilege-derived)	T6
READY	T18, S1
READY_WITH_WARNINGS (causally complete)	T17, S2
NOT_READY	T10, T18
UNMAPPED (zero-ingredient + zero-required)	T14, T15
READINESS_DRIFT	T16
LOGICAL_PHYSICAL_MISMATCH (physical-only + logical-only)	T8, T9, S4
AMBIGUOUS_LABEL (armed; deterministic 0-row pre-check)	T13
APPROVAL_HANDLER_UNIMPLEMENTED (required→NOT_READY)	T10
SOURCE_NOT_USABLE / lifecycle	T11
OVERLOADED_FUNCTION	T12, S3
zero-required not green	T15, S1
anti-false-green invariant	S1
green/warning invariant	S2
inventory de-dup (function/trigger)	S5
missing-route resolution	T19
hot-path narrowness	S6

All 19 required cases + 6 supplementary checks are SELECT-only, deterministic, and run post-build.