KB-2BB4
PIDX Build Design — Patch Round 1 (Codex BLOCK/HIGH/MEDIUM resolution map) 2026-06-23
14 min read Revision 1
workflow-manageprocedure-indexpidxpatch-reportcodex-resolutionblockhighmediumlive-validationv0.22026-06-23
PIDX Build Design — Patch Round 1 (Codex resolution map) — 2026-06-23
Path:
knowledge/dev/laws-new/workflow-manage/reports/pidx-build-design-patch-round1-2026-06-23.mdStatus: PATCH REPORT · NON-AUTHORIZING · 0 DDL/DML · 0 PG objects created. Maps every Codex required patch to a v0.2 resolution. Date: 2026-06-23 Inputs:reports/codex-pidx-full-review-2026-06-23.md,reports/codex-pidx-required-patches-2026-06-23.md. Outputs (v0.2):design/pidx-build-design-v0.2.md,design/pidx-ddl-candidate-v0.2.sql.md,design/pidx-readiness-logic-v0.2.md,design/pidx-seed-slice-v0.2.md,design/pidx-test-plan-v0.2.md,design/pidx-codex-review-packet-v0.2.md,reports/pidx-build-design-go-no-go-v0.2.md.
0. Live re-verification before patching (SELECT-only, role context_pack_readonly, DB directus, PG 16.13, 2026-06-23)
Every new negative test is grounded in verified facts, so v0.2 expectations are deterministic:
pidx_*objects present = 0 (safe to create). Extensions =btree_gist, pgcrypto, plpgsql, postgres_fdw(nopg_trgm/vector).dot_tools=309 (DOT-001 active).apr_action_types=14 (4 implemented inclpatch_ops_code; 10unimplementedinclassign_governance_owner).event_type_registry=52 (30 active / 22 inactive;event_domainnever dotted,event_typedotted in 27/52).taxonomy=58 (facet_id int 1..7; codesLBL-NNNglobally unique → 0 multi-facet codes).directus_collections=164.- Collection physical/logical 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. - Functions:
public.pgp_pub_decrypt= 3 sigs (overloaded);public.gen_random_uuid= 1. - Restricted schemas
cutter_governance,sandbox_tacpresent;has_schema_privilege(context_pack_readonly, …, 'USAGE')= false → privilege-derived READ_BLOCKED. workflows.status∈ {active, draft}.
1. BLOCK items (must all be RESOLVED or escalate to HOLD/NO-GO)
| Codex item | Sev | v0.1 issue | v0.2 resolution | File / section | Remaining caveat | Status |
|---|---|---|---|---|---|---|
| P0-1 warning_flags aggregation | BLOCK | warning_flags = correlated scalar subquery over grouped cols s.w_fn/s.w_appr/s.w_label → grouped-SELECT rule rejects it; view likely won't CREATE. |
Per-ingredient warnings emitted as text[] (t7.warns); CTE proc_warn does array_agg(DISTINCT w ORDER BY w) grouped by procedure_code; LEFT-JOINed once in roll. No grouped column inside a correlated subquery. |
ddl §5 (CTE proc_warn,roll); logic §5 |
none | RESOLVED |
| P0-2 strict ref parser/resolver | BLOCK | prefix never compared to kind; split_part ignored extra/missing segments; ref_status trusted. |
One parser (t1–t4): tokenize on first colon, prefix=kind, exact per-kind arity, reject empty segments, validity independent of ref_status; found=false unless valid; INVALID_REF wins precedence. Prefix-mismatch dot:patch_ops_code (kind=approval) now INVALID_REF (was false-EXISTS). |
ddl §5 (t4); logic §1.1, §3.1; tests T4,T5 |
function still name-only (overload warned, non-authorizing) | RESOLVED |
| P0-3 collection logical/physical false EXISTS | BLOCK | logical probe ignored schema; inventory omitted logical-only; mismatch one-sided. | Existence = physical base table only; coll_logical separate + public-namespaced; LOGICAL_PHYSICAL_MISMATCH = found XOR coll_logical in public; logical-only → MISSING (no false green). Inventory collection branch = FULL OUTER JOIN → logical-only folders appear. Verified fixtures: directus_collections (physical-only), sales (logical-only). |
ddl §5 (t5.coll_logical,t7.warns), §6 branch 2a/2b; logic §6; tests T8,T9,S4 |
mismatch is high-volume at inventory grain for public infra tables (informational; documented) | RESOLVED |
| P0-4 label facet grammar | BLOCK | taxonomy.code='facet.code'; facet_id never parsed; inventory emitted bare refs only. |
label:<facet_id>.<code> parses facet+code (taxonomy.facet_id::text,code); bare label:<code> resolves by code; AMBIGUOUS_LABEL only when bare matches >1 row. Inventory emits facet-qualified canonical ref. |
ddl §5 (t5.found label branch), §6 branch 10; logic §6; test T13 |
AMBIGUOUS_LABEL armed but inert (0 multi-facet codes verified) — deterministic 0-row pre-check provided |
RESOLVED |
| P0-5 zero-required false green | BLOCK | optional-only procedure reached ELSE 'READY'. |
Rollup computes required_count; required_count=0 → UNMAPPED before the READY branch. S1 invariant asserts required_count>0 for READY (not merely ingredient_count>0). |
ddl §5 (roll); logic §4; seed #13; tests T15,S1 |
none | RESOLVED |
2. HIGH items (must be RESOLVED or clearly justified non-blocking)
| Codex item | Sev | v0.1 issue | v0.2 resolution | File / section | Remaining caveat | Status |
|---|---|---|---|---|---|---|
| P1-1 usability/lifecycle predicates | HIGH | existence == usability; inactive/retired satisfied readiness. | Per-ingredient usable tri-state per kind (dot/approval/event/procedure/label have lifecycle; CATALOG kinds → unknown, not downgraded). Required found-but-not-usable → unsatisfied → NOT_READY; optional → SOURCE_NOT_USABLE. Live fixture: inactive event. |
ddl §5 (t6.usable); logic §2,§3.2; tests T11,T10 |
dot has no retired rows today (dot SOURCE_NOT_USABLE inert); event lifecycle is live | RESOLVED |
| P1-2 warning production + propagation | HIGH | only 3 flags produced; READY_WITH_WARNINGS could have empty warning_flags. |
All 11 flags produced incl OPTIONAL_MISSING/UNKNOWN_SOURCE/INVALID_REF/READ_BLOCKED, REQUIRED_LEVEL_UNTRIAGED, SOURCE_NOT_USABLE. Invariant: READY⇒empty, READY_WITH_WARNINGS⇒non-empty. |
ddl §5 (t7.warns); logic §5,§5.1; tests T17,S2 |
NOT_READY surfaces hard-gate causes via req_*_count columns, not warning_flags (documented) |
RESOLVED |
| P1-3 approval-handler gating | HIGH | required unimplemented handler → only READY_WITH_WARNINGS. | Safe rule: unimplemented handler ⇒ usable=false ⇒ required ⇒ NOT_READY; optional ⇒ READY_WITH_WARNINGS + flag. READY_WITH_WARNINGS documented non-authorizing. |
ddl §5 (t6.usable approval); logic §6; seed #6; test T10 |
none | RESOLVED |
| P1-4 identity & delete safety | HIGH | ON UPDATE/DELETE CASCADE; mutable text PK; no surrogate. |
FK → RESTRICT/RESTRICT; surrogate id GENERATED ALWAYS AS IDENTITY on both tables; procedure_code UNIQUE NOT NULL, immutable-by-policy (retire, don't rename/delete). |
ddl §2,§3 | deletion now requires explicit ingredient removal first (intended discipline) | RESOLVED |
| P1-5 Điều 33 table conformance | HIGH | tables unclassified vs Điều 33. | Classified: PG-native technical index (recommended) with both paths specified; canonical column shape adopted (id,code=procedure_code,name,status,date_created,date_updated). Only deviation if governed = plural naming + Directus registration. date_updated not presented as catalog freshness (no trigger). |
build-design §11; ddl §2,§3 | plural-name + Directus-registration choice is NEEDS_OWNER_DECISION (governance gate, not a SQL blocker) |
RESOLVED (1 owner decision flagged) |
| P1-6 rollback hardening | HIGH | unconditional drops; approximate source counts. | Preflight collision + unexpected-dependent (pg_depend) snapshot; transactional BEGIN…COMMIT with DO guard that RAISEs on unexpected dependents; drop only pidx_*/v_pidx_*; exact catalog fingerprint (md5 of object-name set) before/after; non-seed-data guard; source-readability proof. |
ddl §7.1–7.3 | fingerprint baseline must be captured at build time (noted in §8 build-order) | RESOLVED |
| P1-7 expand seed/test plan | HIGH | conditional/>=0/hidden DML; missing negative cases. |
13-proc seed covers prefix-mismatch, extra/missing segment, no-schema, zero-required, lifecycle, facet label, collection XOR/wrong-schema (READ_BLOCKED), overload, approval-handler, warning completeness, route target. 19 deterministic tests + 6 invariants; hidden-DML T11 removed (facet test on real data + 0-row pre-check); >=0 replaced with exact expectations. |
seed §1–4; test-plan §1–2 | AMBIGUOUS_LABEL remains data-inert (proven by pre-check) |
RESOLVED |
3. MEDIUM items (addressed)
| Codex item | Sev | v0.2 resolution | File / section | Status |
|---|---|---|---|---|
| P2-1 hoist rollup & drift | MED | computed_readiness computed once in CTE roll; readiness_drift derived from it in outer SELECT. |
ddl §5 | RESOLVED |
| P2-2 normalize duplicate inventory refs | MED | function branch GROUP BY (schema,name) + overload_count/signatures; trigger branch GROUP BY (schema,table,name) + event set. |
ddl §6 branch 5,6; test S5 | RESOLVED |
| P2-3 derive READ_BLOCKED from PG access | MED | has_schema_privilege(current_user, <oid>, 'USAGE'), OID-guarded so missing schema → MISSING not error. |
ddl §5 (t5.read_blocked); logic §3.1; test T6 |
RESOLVED |
| P2-4 routes & null/default behavior | MED | missing_route_exists probed + exposed; readiness_drift COALESCE-guarded non-null; declared_maturity/automation_mode/safety_class remain nullable hints but never produce null readiness. |
ddl §5; test T19 | RESOLVED |
| P2-5 deferred-link wording | MED | No links_jsonb column; links deferred; relations via procedure: ingredient or missing_route_procedure_code; manifest_jsonb = non-authoritative note. |
build-design §1.4; ddl §2 comment | RESOLVED |
4. Re-review acceptance gate (Codex patch list §"Re-review acceptance gate")
| Gate requirement | Met by |
|---|---|
| 1. complete candidate SQL, not abbreviated branches | ddl §2–7 — every branch written out; no "same as above"/"etc." |
| 2. one strict parser/resolver contract | logic §1; ddl §5 t1–t7 |
| 3. deterministic expected outputs for all new negative tests | test-plan §1–2 (live-verified fixtures; 0 >=0 assertions) |
4. no bare READY path without ≥1 required, usable, unambiguous PG-proven ingredient |
rollup precedence + S1 invariant + usability gating |
| 5. idempotent governed apply/verify + rollback consistent with enacted laws | build-design §9,§11 (Cấp-B apply/Cấp-A verify, Điều 33 E1); ddl §7 hardened rollback |
5. Live-execution validation evidence (read-only; 0 objects created)
The candidate resolver is not only static text — it was executed read-only against live directus (role context_pack_readonly, no DDL/DML, no objects created) by inlining a VALUES list in place of pidx_procedure_ingredient (the procedure: branch and the final join to pidx_procedure were omitted, since that table does not exist yet and PG resolves table refs at parse time). This proves the patched SQL parses and computes the expected outcomes on real catalog data, not merely "looks parseable."
Per-ingredient resolver — 17/17 rows matched expected (proves P0-2, P0-3, P0-4, P1-1, P1-2 warns, P1-3, P2-3):
| ref (kind) | computed_status | usable | warns |
|---|---|---|---|
dot:DOT-001 (dot) |
EXISTS | true | — |
dot:patch_ops_code (approval) |
INVALID_REF | — | — (v0.1 false-EXISTS killed: prefix≠kind) |
field:public.dot_tools.code.extra (field) |
INVALID_REF | — | — (arity 4≠3) |
event:collection_created (event) |
INVALID_REF | — | — (arity 1<2) |
collection:dot_tools (collection) |
INVALID_REF | — | — (no schema) |
collection:public.directus_collections |
EXISTS | — | LOGICAL_PHYSICAL_MISMATCH (physical-only) |
collection:public.sales |
MISSING | — | LOGICAL_PHYSICAL_MISMATCH (logical-only → no false green) |
collection:public.collection_registry |
EXISTS | — | — |
approval:assign_governance_owner |
EXISTS | false | APPROVAL_HANDLER_UNIMPLEMENTED |
approval:patch_ops_code |
EXISTS | true | — |
event:governance.backfill.sweep_completed |
EXISTS | false | SOURCE_NOT_USABLE (multi-dot type split correctly) |
function:public.pgp_pub_decrypt |
EXISTS | — | OVERLOADED_FUNCTION |
label:1.LBL-001 |
EXISTS | true | — (facet-qualified) |
label:LBL-001 |
EXISTS | true | — (bare, unique → no AMBIGUOUS_LABEL) |
collection:cutter_governance.policy_state |
READ_BLOCKED | — | — |
field:public.dot_tools (UNNORMALIZED) |
INVALID_REF | — | — |
report:x |
UNKNOWN_SOURCE | — | — |
Per-procedure rollup — 4/4 matched expected (proves P0-1 aggregation + P0-5 + green invariant):
| procedure | ingredient_count | required_count | req_unsatisfied | warning_flags | computed_readiness |
|---|---|---|---|---|---|
P_NONGATING |
2 | 0 | 0 | {} |
UNMAPPED (zero-required, not READY) |
P_NOTREADY |
1 | 1 | 1 | {APPROVAL_HANDLER_UNIMPLEMENTED} |
NOT_READY (required unimplemented handler) |
P_READY |
2 | 2 | 0 | {} |
READY |
P_RWW |
2 | 1 | 0 | {OPTIONAL_MISSING} |
READY_WITH_WARNINGS |
This is the eye reading PG: the resolver was confirmed live, not asserted. (The full readiness view, which adds the pidx_procedure join and proc_warn/agg/roll assembly, is identical in shape; only the source relation differs.)
6. Verdict
All 5 BLOCK items RESOLVED; all 7 HIGH items RESOLVED (P1-5 carries one flagged NEEDS_OWNER_DECISION that is a governance-classification choice, not a SQL/logic blocker); all 5 MEDIUM items RESOLVED. No BLOCK item remains unresolved. The resolver was executed read-only and matched 21/21 outcomes. → READY_FOR_CODEX_STATIC_REVIEW_V0_2 (see reports/pidx-build-design-go-no-go-v0.2.md).