Incomex AI Portal
KB-2034

PIDX Codex Review Packet v0.2 — static re-review checklist + new-defect watch

7 min read Revision 1
workflow-manageprocedure-indexpidxcodex-reviewreview-packetstatic-re-reviewchecklistv0.22026-06-23

PIDX Codex Review Packet v0.2 (static re-review)

Path: knowledge/dev/laws-new/workflow-manage/design/pidx-codex-review-packet-v0.2.md Status: REVIEW PACKET · for Codex static re-review · build-design review, not a build. All SQL referenced is candidate text; nothing is executed. Date: 2026-06-23 Review these files: pidx-build-design-v0.2.md, pidx-ddl-candidate-v0.2.sql.md, pidx-readiness-logic-v0.2.md, pidx-seed-slice-v0.2.md, pidx-test-plan-v0.2.md. Patch map: reports/pidx-build-design-patch-round1-2026-06-23.md.

0. Scope of this re-review

Confirm the v0.1 BLOCK/HIGH findings are resolved in candidate SQL/logic, and that no new build-blocking defect was introduced. Do not re-open the accepted problem statement unless you find a build-blocking contradiction. Target DB directus (PostgreSQL 16.13), schema public. Object set: pidx_procedure, pidx_procedure_ingredient, v_pidx_inventory_current, v_pidx_procedure_readiness (2T2V, unchanged).

1. BLOCK/HIGH closure checklist (please rule each CLOSED / REOPEN)

Prior finding Where to verify Expected to see
P0-1 warning aggregation ddl §5 CTE proc_warn + roll LEFT JOIN grouped array_agg(DISTINCT …) in its own CTE, joined once; no grouped column inside a correlated subquery
P0-2 strict parser ddl §5 t1–t4; logic §1.1 prefix=kind + exact nseg arity + no empty seg, computed before found; INVALID_REF precedence; independent of ref_status
P0-3 collection logical/physical ddl §5 t5.coll_logical/t7.warns, §6 branch 2a/2b existence=physical; logical separate public-only; XOR mismatch; logical-only→MISSING; FULL OUTER JOIN inventory
P0-4 label facet grammar ddl §5 label branch, §6 branch 10 facet_id::text+code parse; bare resolves; AMBIGUOUS_LABEL on bare multi-match
P0-5 zero-required ddl §5 roll; logic §4 required_count=0 → UNMAPPED precedes READY
P1-1 lifecycle/usability ddl §5 t6.usable tri-state usable; required-not-usable→NOT_READY; catalog kinds unknown (not downgraded)
P1-2 warning completeness ddl §5 t7.warns; logic §5.1 all flags incl OPTIONAL_*; READY⇒empty, READY_WITH_WARNINGS⇒non-empty
P1-3 approval gating ddl §5 t6.usable approval required unimplemented⇒usable=false⇒NOT_READY
P1-4 identity/delete ddl §2,§3 RESTRICT/RESTRICT FK; surrogate id; immutable procedure_code
P1-5 Điều 33 build-design §11; ddl §2,§3 classification + canonical columns; one flagged owner decision
P1-6 rollback ddl §7.1–7.3 preflight + transactional guard + exact fingerprint
P1-7 seed/test seed §1–4; test-plan deterministic, no hidden DML, no >=0

2. New-defect watch (please scrutinize these v0.2 additions)

  1. string_to_array(ident,'.') + '' = ANY(seg) — confirm empty-segment detection (a..b, .a, a.) and nseg via array_length (0 when empty) behave as intended on PG16.
  2. event arity ≥2 with first-dot split — confirm substr(ident, length(s1)+2) recovers a dotted event_type (e.g. backfill.sweep_completed) and matches event_type_registry.
  3. has_schema_privilege(current_user, n.oid, 'USAGE') in a scalar subquery + COALESCE(...,true) — confirm a non-existent schema yields MISSING (not an error) and an existing-but-no-USAGE schema yields READ_BLOCKED. Confirm the OID form (not the name form) avoids the "schema does not exist" error.
  4. usable IS NOT FALSE satisfaction — confirm unknown(NULL) satisfies and only proven false blocks.
  5. cardinality(f.warns) / cardinality(warning_flags) — confirm array_remove(ARRAY[…],NULL) yields a {} (not NULL) array so cardinality is 0, not NULL, for unwarned ingredients.
  6. proc_warn LATERAL unnest — confirm procedures with no warned ingredients simply have no proc_warn row → COALESCE(...,ARRAY[]::text[]) → empty flags.
  7. readiness_drift COALESCE — confirm non-null boolean when declared_maturity IS NULL.
  8. Inventory FULL OUTER JOIN + GROUP BY branches (trigger/function) — confirm no duplicate object_ref, and that the function branch's array_agg(... ORDER BY p.oid) and count(*) are consistent.
  9. GENERATED ALWAYS AS IDENTITY + FK to a UNIQUE (non-PK) column — confirm pidx_procedure_ingredient.procedure_code REFERENCES pidx_procedure(procedure_code) is valid against the UNIQUE constraint.

Live-execution note: the resolver (CTEs t1–t7 and the proc_warn/rollup pattern) was executed read-only against live directus with a VALUES stand-in for pidx_procedure_ingredient; it parsed and matched 21/21 expected outcomes (patch-report §5). Only the final pidx_procedure join differs from what was run.

3. Live facts the SQL relies on (re-verified read-only 2026-06-23, role context_pack_readonly, DB directus)

  • pidx_* four objects absent. Extensions: btree_gist, pgcrypto, plpgsql, postgres_fdw (no pg_trgm/vector).
  • dot_tools=309 (DOT-001 active); apr_action_types=14 (impl: add_field, create_item, patch_ops_code, update_item; assign_governance_owner=unimplemented); event_type_registry=52 (active 30/inactive 22; event_type may contain .); taxonomy=58 (facet_id int; codes unique → 0 multi-facet); directus_collections=164.
  • Collection truth: both=dot_tools,collection_registry,taxonomy,apr_action_types; physical-only=directus_collections,event_type_registry; logical-only folders=billing,blocks,help,sales,website.
  • public.pgp_pub_decrypt=3 sigs; public.gen_random_uuid=1. Restricted schemas cutter_governance,sandbox_tac → no USAGE for the test role. workflows.status∈{active,draft}.

4. What Codex should return

  • A CLOSED/REOPEN verdict per row in §1, plus adjudication of §2.
  • Any build-blocking contradiction with the problem statement (else leave philosophy closed).
  • If all §1 rows are CLOSED and §2 raises no BLOCK, the package is cleared for Owner DDL authorization via the governed path (patch_ops_code APR or a registered migration DOT) — Codex does not authorize the build.