PIDX Codex Review Packet v0.1

Path: knowledge/dev/laws-new/workflow-manage/design/pidx-codex-review-packet-v0.1.md Status: REVIEW PACKET · for Codex · 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.1.md, pidx-ddl-candidate-v0.1.sql.md, pidx-readiness-logic-v0.1.md, pidx-seed-slice-v0.1.md, pidx-test-plan-v0.1.md.

---

0. Scope of this review

Codex reviews build-readiness of the candidate SQL and logic — not the philosophy or problem statement. Do not re-open the accepted problem statement unless you find a build-blocking contradiction. Target DB: directus (PostgreSQL on VPS), schema public. Object set under review: pidx_procedure, pidx_procedure_ingredient, v_pidx_inventory_current, v_pidx_procedure_readiness (2T2V).

---

1. Review checklist (please rule each PASS / CONCERN / BLOCK)

#	Dimension	What to check	Pointer
1	SQL syntax safety	Do the two CREATE TABLE, three CREATE INDEX, and two CREATE VIEW statements parse on this PG version? Identity column, CHECK enums, jsonb_build_object, window funcs, FILTER, array_agg DISTINCT usage all valid?	pidx-ddl-candidate-v0.1.sql.md §1–5
2	Object name collision	Confirm pidx_* / v_pidx_* are free (live scan returned (none)); confirm no clash with the existing wf_* scanner family or 49 v_*readiness* views.	build-design §1.1
3	DDL reversibility	Is the rollback complete and correctly ordered (views → child table → parent table)? Any object left behind (sequence, default, comment)?	sql §6
4	Constraint strictness	Is "soft ingredient_ref + ref_status + view-detected INVALID_REF" the right call vs a regex CHECK? Are the enum CHECKs on kind/level/mode/class/status correct and complete?	sql §0, §2
5	Ref grammar soundness	Are the two normalization classes (CATALOG lowercase+schema-default vs CODE case-sensitive) sound? Any kind whose probe could false-MISS or false-EXISTS?	readiness-logic §2; build-design §3
6	False-READY risk	Can any declaration/manifest/note/seed status produce READY? Does READY_WITH_WARNINGS correctly intercept warnings? Zero-ingredient → UNMAPPED? UNKNOWN_SOURCE required → NOT_READY?	readiness-logic §4, §7; test S4
7	Performance hot path	Does the per-procedure_code path use PK + pidx_proc_ingr_procedure_idx + narrow EXISTS, cost O(#ingredients)?	build-design §6; test S5
8	Scan-all-PG avoidance	Does answering one procedure ever materialize the full v_pidx_inventory_current or scan all information_schema.columns (the heavy field branch)?	build-design §4.3, §6.3
9	Read-only / source-truth discipline	Are both views pure SELECT (no writes/side effects)? Is PG the only truth, declarations only hints?	readiness-logic §1, §7
10	Rollback completeness	Do the verification queries prove absence of the 4 objects AND non-damage to sources?	sql §6.1; test plan

---

2. Known open items the author already flagged (please adjudicate)

1. Aggregated warning_flags subselect in v_pidx_procedure_readiness uses a correlated UNION ALL + array_agg(DISTINCT …) FILTER. Confirm valid in a grouped view, or refactor to a LATERAL. (author: review item, not a blocker)
2. Duplicated readiness_drift CASE repeats the rollup expression. Recommend hoisting the rollup into a CTE column and referencing it twice. (cosmetic / maintainability)
3. collection branch logical/physical join matches directus_collections.collection = table_name. Confirm that is the right join key (vs schema-qualified) and that LOGICAL_PHYSICAL_MISMATCH fires only on a true XOR.
4. function overload detection uses a window count in the inventory view and a scalar subquery in the resolver — confirm both agree and neither is O(catalog) on the hot path.
5. view matview branch reads pg_class relkind='m'; confirm the information_schema.views + matview union has no duplicate object_ref.
6. split_part parsing for CATALOG kinds assumes exactly the right number of dot-segments. Confirm a malformed segment count is caught as INVALID_REF (rule 1) and never silently probes the wrong schema/table.
7. Identity vs serial — bigint GENERATED ALWAYS AS IDENTITY chosen for the ingredient PK; confirm it fits the substrate's conventions (vs Directus-style integer PK).

---

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

• pidx_procedure, pidx_procedure_ingredient, v_pidx_inventory_current, v_pidx_procedure_readiness → absent (safe to create).
• dot_tools = 309 (codes DOT-001..); apr_action_types = 14 (4 implemented: add_field, create_item, patch_ops_code, update_item; 10 unimplemented incl. assign_governance_owner); event_type_registry = 52; collection_registry = 168.
• Extensions: btree_gist, pgcrypto, plpgsql, postgres_fdw (no pg_trgm, no vector) → v0.1 fuzzy = ILIKE only.
• Restricted schemas cutter_governance, sandbox_tac → READ_BLOCKED (used as the negative fixture).

Note: T3 survey cited event_type_registry=40 and "3 implemented / 11 unimplemented"; live re-check shows 52 and 4/10. The drift is benign (registries grow); the design reads these live, never hardcodes counts.

---

4. What Codex should return

• A PASS/CONCERN/BLOCK verdict per row in §1.
• Adjudication of the §2 open items (accept / change-required).
• Any build-blocking contradiction with the problem statement (else leave philosophy closed).
• If all §1 rows are PASS/CONCERN (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.