Incomex AI Portal
KB-3AE5

PIDX Build Design v0.2 — PG-native Procedure Index (Codex patch round 1)

14 min read Revision 1
workflow-manageprocedure-indexpidxbuild-designdesign2T2Vcodex-patch-round1legono-hardcodedot-disciplinedieu33v0.22026-06-23codex-review-ready

PIDX Build Design v0.2 — PG-native Procedure Index (Codex patch round 1)

Path: knowledge/dev/laws-new/workflow-manage/design/pidx-build-design-v0.2.md Status: BUILD DESIGN · v0.2 · DESIGN ONLY · NON-AUTHORIZING · 0 DDL executed · 0 PG objects created · 0 PG/Directus/DOT mutation Date: 2026-06-23 Macro: T3 — PIDX Build Design Patch Round 1 (addresses Codex PASS_WITH_CAVEATS_REQUIRES_PATCH) Patched from: pidx-build-design-v0.1.md + companions, per reports/codex-pidx-full-review-2026-06-23.md and reports/codex-pidx-required-patches-2026-06-23.md. Companions (this package): 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, pidx-codex-review-packet-v0.2.md, reports/pidx-build-design-patch-round1-2026-06-23.md, reports/pidx-build-design-go-no-go-v0.2.md. Principle locked (unchanged): PG reads PG. PG is the truth. The Procedure Index is the eye, not the hand. The Ingredient Map is lazy declaration/cache only. Readiness is computed from PG/SQL facts. Vector/RAG/fuzzy may suggest candidates only; SQL confirms truth.

0. What this macro is and is NOT

This is a design patch of v0.1 into a Codex-re-reviewable v0.2. It resolves every Codex BLOCK and HIGH finding in candidate SQL/logic — not by prose, but by patched SQL.

  • NOT a build macro. No DDL/DML executed. No tables/views/functions/triggers created. No migration, Directus collection, DOT registration, scheduler/bus, or PG/Directus/DOT/governance/birth/KG/production mutation.
  • All SQL in this package is candidate text only. The only writes performed are KB design/report documents.
  • Read-only PG inspection WAS used (SELECT-only, role context_pack_readonly) to verify the live facts the seed/tests assert — this is the "eye" reading PG, explicitly allowed, and it makes every new negative test deterministic. No mutation occurred.

1. What changed from v0.1

1.1 Summary table

Area v0.1 v0.2
Scope 2T2V 2T2V (unchanged)
Names pidx_* / v_pidx_* unchanged
Ref parser kind-driven probe, prefix unchecked, split_part arity leak one strict parser: prefix=kind, exact arity, no empty segments, validity independent of ref_status
Collections logical probe ignored schema → false EXISTS physical=truth; logical separate; XOR mismatch; logical-only=MISSING
Labels taxonomy.code='facet.code' (broken) facet-qualified label:<facet_id>.<code> + bare, AMBIGUOUS_LABEL on bare multi-match
Zero-required could reach READY required_count=0 → UNMAPPED
Lifecycle existence == usability usable tri-state; required-not-usable → NOT_READY
Approval handler required unimplemented → READY_WITH_WARNINGS required unimplemented → NOT_READY (safe rule)
warning_flags correlated subquery over grouped cols (won't build) per-proc proc_warn CTE, joined once; causally complete
FK CASCADE/CASCADE RESTRICT/RESTRICT + surrogate id + immutable procedure_code
Điều 33 unclassified classified (PG-native technical index) + canonical column names
Rollback unconditional drops preflight + transactional + fail-closed + exact fingerprint
Tests conditional/>=0/hidden DML deterministic expected outputs; SELECT-only; live-verified fixtures

1.2 Final 2T2V scope (unchanged)

2 tables:   pidx_procedure, pidx_procedure_ingredient
2 views:    v_pidx_inventory_current, v_pidx_procedure_readiness

pidx_procedure_link remains deferred. Nothing added: no engine, scheduler, event bus, materialized view, RAG/vector, pg_trgm, Nuxt, or governance/birth/KG dependency.

1.3 pidx_* naming (unchanged)

pidx_procedure, pidx_procedure_ingredient, v_pidx_inventory_current, v_pidx_procedure_readiness. Verified absent 2026-06-23 (0 of 4 present). pidx_ reads as "Procedure Index" (an eye), avoiding collision with the live wf_* scanner/snapshot family.

v0.1 claimed links live in links_jsonb; no such column exists. v0.2 is explicit: there is no links_jsonb. pidx_procedure_link is deferred. Until built, a procedure→procedure relationship is expressed only as a procedure: ingredient or via missing_route_procedure_code. manifest_jsonb is a non-authoritative note and is never read to set readiness.

2. Truth model (unchanged principle, hardened implementation)

  • Declared layer (pidx_procedure, pidx_procedure_ingredient): what a procedure says it is and needs. Never stores computed existence/usability/readiness.
  • Computed layer (v_pidx_*): what PG can prove. v_pidx_inventory_current normalizes existing PG sources; v_pidx_procedure_readiness runs the strict resolver and rolls up.
  • Join key = the ref grammar (ingredient_ref ≡ object_ref). Frozen in §3 and pidx-readiness-logic-v0.2.md.

Codex's truth-model verdict (concept PASS, implementation CONCERN) is resolved: the parser, lifecycle, collection, warning, and zero-required fixes mean PG can no longer compute a wrong truth from a malformed or incomplete probe.

3. Ref grammar v0.2 (strict parser/resolver)

Canonical shape <kind>:<identifier>. Two normalization classes (CATALOG lowercase+schema-qualified; CODE case-sensitive). The single change of substance from v0.1 is that the readiness view now enforces the grammar strictly (prefix=kind, exact arity, no empty segment), independent of ref_status. Full per-kind table and arity in pidx-readiness-logic-v0.2.md §1–2. Key decisions:

  • Schema-default policy (one, explicit): CATALOG refs are fully schema-qualified; a schema-less CATALOG ref is INVALID_REF (no silent public default). Write-side normalization may add public.; the view never guesses.
  • event: arity is ≥2 (domain + possibly-dotted type; split on first dot) — verified: event_type can contain ..
  • label: is 1 or 2 segments (bare code, or facet_id.code).
  • The four resolution states (MISSING / UNKNOWN_SOURCE / INVALID_REF / READ_BLOCKED) are unchanged; READ_BLOCKED is now privilege-derived (P2-3).

4. Inventory View contract (v_pidx_inventory_current)

A UNION ALL of per-kind sub-selects over existing sources. 14-column contract (unchanged):

object_kind · object_ref · object_code · object_name · source_schema · source_object · source_pk ·
exists_bool · authority_level · metadata_jsonb · freshness_status · last_seen_at · notes · warning_flags

4.1 v0.2 branch changes

# kind v0.2 change
2 collection FULL OUTER JOIN physical(public) ⋈ Directus logical → logical-only folders now appear; XOR mismatch flag; non-public physical tables in a separate branch
5 trigger de-duplicated to one canonical ref per (schema,table,name); event set in metadata (P2-2)
6 function de-duplicated to one row per (schema,name); overload count + signatures in metadata (P2-2)
10 label emits facet-qualified label:<facet_id>.<code>; AMBIGUOUS_LABEL via window count; usability flag
1,7,8,9 dot/approval/event/procedure usability surfaced (SOURCE_NOT_USABLE / APPROVAL_HANDLER_UNIMPLEMENTED)

io/checker/template/report remain non-branches (resolve UNKNOWN_SOURCE in readiness; no inventory rows).

4.2 Anti-monster guard (unchanged)

v_pidx_inventory_current is for browsing/treeview/debugging, never the readiness hot path. The readiness view does not reference it. The heavy field branch is exposed, not eagerly scanned.

5. Performance model (unchanged; verification still required at build)

Agent asks readiness for ONE procedure_code
  → PK/UNIQUE lookup on pidx_procedure
  → index scan pidx_proc_ingr_procedure_idx for its few ingredient rows
  → per ingredient_ref: ONE narrow correlated EXISTS / point-probe
  → roll up → return
Cost = O(#ingredients of that procedure), NOT O(catalog).

The readiness view does not read v_pidx_inventory_current, so answering one procedure does not materialize the catalog. Build acceptance must still include EXPLAIN (ANALYZE, BUFFERS) for one procedure and a multi-procedure browse (test plan S6). No new scheduler/bus; pure views are always fresh; force_refresh is a no-op in v0.2.

6. LEGO separation / no monolithic engine

Procedure, ingredient, ref, source probe, readiness status, warning flag, and missing route remain separate, reusable pieces (each a table or a CTE; see pidx-readiness-logic-v0.2.md §0). PIDX indexes and computes visibility; it does not execute procedures and does not become a workflow engine. The 2T2V shape is preserved precisely to keep the pieces from collapsing into an engine.

7. No-hardcoded-truth discipline

v0.2 hardcodes no operational truth. It does not hardcode that any DOT/table/field/label/procedure is ready, nor row counts, object lists, readiness outcomes, or lifecycle truth. Readiness never depends on a note/manifest/seed/RAG/Agent memory. What v0.2 defines are minimal contracts that let PG verify truth from PG: the ref grammar, the object_kind list, the per-kind source-probe contract, the readiness status list, and the warning-flag list. The live counts in this package (309 DOTs, 14 approvals, …) are verification evidence as of 2026-06-23, not hardcoded logic — every view reads these live and never embeds a count. The one privilege list that v0.1 hardcoded (cutter_governance, sandbox_tac) is now computed from has_schema_privilege (P2-3).

8. Automation scope for v0.2 = seeing/checking/routing only

v0.2 automates visibility and readiness computation: ref parsing, PG probing, existence/missing/unknown/invalid/blocked detection, usability detection, warning computation, readiness rollup, next-route resolution. v0.2 does not automate or imply procedure execution, auto-fix, auto-register DOT, auto-create collection/schema, auto-approval, or one-button runtime execution. READY/READY_WITH_WARNINGS are read-only signals that authorize nothing.

9. DOT / governed-path discipline for future mutation

  • Read-only PG inspection is the eye and is allowed for design and readiness evidence (and was used here, SELECT-only).
  • Candidate DDL/DML is text only in this macro.
  • Any future CREATE/ALTER/DROP/INSERT/UPDATE/DELETE must go through an Owner-authorized governed path (patch_ops_code APR or a registered migration DOT), idempotent and paired with a Cấp-A verifier (Điều 33 E1). Read-only inspection and future mutation/build are different authority levels; this package never conflates them.

10. Non-goals (unchanged)

No workflow engine; no governance/KG/birth integration as a prerequisite; no all_objects mega-registry; no auto-fix; no new scheduler/bus; no manifest/RAG/note as truth; no requirement that all 39 procedures be mapped first; no DDL executed; pidx_procedure_link deferred. The Procedure Index is the eye, not the hand.

11. Điều 33 classification (P1-5) — Owner decision documented

Recommended classification: PIDX tables are PG-native technical index tables, NOT governed Directus entities. Rationale: they store only declared hints + computed-by-view truth, are the "eye" not a governed business entity, and must stay thin. Under this classification, singular names (pidx_procedure) and a natural join key (procedure_code) are conventional for a technical index, and Directus registration / lifecycle metadata are not required.

To make the other classification cheap if the Owner prefers it, v0.2 already adopts the Điều 33 canonical column shape:

Điều 33 canonical v0.2 column note
id id bigint GENERATED ALWAYS AS IDENTITY surrogate immutable identity (also resolves P1-4)
code procedure_code text UNIQUE NOT NULL stable domain key, immutable by policy
name name (was title) canonical label
status status draft/active/retired
date_created / date_updated date_created / date_updated (was created_at/updated_at) canonical timestamps

Remaining deviation if governed: plural table naming (pidx_procedures) and Directus collection registration. These are a one-line rename + a registration step at build time. This is the single item flagged NEEDS_OWNER_DECISION — it does not block Codex static re-review of the SQL/logic; it is a governance-classification choice for the build gate. date_updated is maintained by the governed write path, not presented as a catalog-freshness fact (Codex's updated_at concern), so no trigger is created in v0.2.

12. Open decisions carried to Owner/Codex (none block Codex static re-review)

  1. DDL authorization — net-new objects → governed create (patch_ops_code APR or registered migration DOT). This design does NOT authorize it.
  2. Điều 33 classification (§11) — recommended PG-native technical index; governed-entity path fully specified as the alternative. NEEDS_OWNER_DECISION.
  3. template: source — iu_collection_template_registry vs design_templates vs none → NEEDS_OWNER_DECISION; stays UNKNOWN_SOURCE.
  4. io/checker/report — no clean PG SSOT → UNKNOWN_SOURCE in v0.2; Owner confirms acceptable.
  5. pg_trgm/vector — deferred; owner-gated CREATE EXTENSION later only if scale demands, two-step suggest→confirm only.

Verdict for this macro: READY_FOR_CODEX_STATIC_REVIEW_V0_2 (see reports/pidx-build-design-go-no-go-v0.2.md).