Codex Required Patches — PG-native Procedure Index v0.1

Scope: design/SQL corrections required before Owner build authorization. This file does not authorize or execute a build.

BLOCK patches

P0-1 — Make warning aggregation valid PostgreSQL

• Move per-ingredient warning expansion into a dedicated CTE.
• Aggregate distinct flags by procedure_code there.
• Join the one-row-per-procedure warning result into the final rollup.
• Add a static parse/review assertion for the complete CREATE VIEW statement.

P0-2 — Implement one strict ref parser

• Parse and compare the ref prefix with ingredient_kind.
• Enforce exact segment count per kind; reject missing and extra segments.
• Detect validity independently of stored ref_status; the stored flag is only a hint.
• Implement one explicit schema-default policy.
• Return INVALID_REF before any existence probe.

P0-3 — Correct collection resolution

• Define Directus logical collections as public-schema logical objects or encode an explicit namespace.
• Build the inventory branch from a full physical/logical set so logical-only rows are present.
• Compute true XOR mismatch.
• Reuse the same semantics in readiness and propagate LOGICAL_PHYSICAL_MISMATCH.
• Add wrong-schema negative tests.

P0-4 — Correct label facet grammar

• Parse bare code versus facet-qualified code.
• Resolve facet identity plus code, not taxonomy.code='facet.code'.
• Emit facet-qualified inventory refs.
• Preserve bare-code ambiguity as a warning and prevent bare ambiguous refs from bare READY.

P0-5 — Prevent zero-required false green

• Compute required_count.
• If required_count=0, return UNMAPPED (smallest v0.1 change) or another explicitly non-ready status.
• Update S4 to assert required_count > 0, not merely ingredient_count > 0.

HIGH patches

P1-1 — Add usability/lifecycle predicates

• Define per-kind usable state for DOT, approval, event, procedure/workflow, label, and any other lifecycle-bearing source.
• An existing but inactive/retired/disabled required object must not yield bare READY.
• Add lifecycle fixtures and expected results.

P1-2 — Complete warning production and propagation

• Implement all specified flags or remove unsupported flags from the v0.1 contract.
• Add explicit flags for optional MISSING, UNKNOWN_SOURCE, INVALID_REF, and READ_BLOCKED, or otherwise make the cause visible at top level.
• Require READY_WITH_WARNINGS to have a non-empty, causally complete warning_flags array.

P1-3 — Decide approval-handler gating

• Recommended safe rule: required approval with null/unimplemented handler => NOT_READY.
• If Owner chooses declaration-only readiness, document that READY_WITH_WARNINGS is non-authorizing and cannot trigger execution.

P1-4 — Preserve identity and dependencies

• Replace ON UPDATE CASCADE ON DELETE CASCADE with restrictive behavior.
• Make procedure identity immutable; retire instead of delete/reuse.
• Resolve whether a surrogate immutable id is required by the enacted PostgreSQL/entity laws while keeping procedure_code unique.

P1-5 — Resolve Điều 33 table conformance

• Classify PIDX tables as PG-only technical index tables or governed Directus entities.
• If governed, align/approve deviations for plural naming, canonical id/code/name/status/date_created/date_updated, Directus registration, and lifecycle metadata.
• Specify the governed, idempotent Cấp-B apply DOT and paired Cấp-A verifier before Owner authorization.

P1-6 — Harden rollback

• Add preflight collision/dependency checks and exact schema fingerprints.
• Use a transaction and fail closed on unexpected dependents or non-seed data.
• Require backup or seed-only proof before table drops.
• Verify exact post-rollback catalog delta, not approximate mutable source counts.

P1-7 — Expand the seed/test plan

• Add prefix mismatch, segment arity, zero-required, lifecycle, facet label, collection XOR/wrong-schema, overload, approval-handler, warning completeness, and route-target tests.
• Remove the hidden DML requirement from the “SELECT-only” T11 or pre-seed its fixture through the authorized seed step.
• Replace >=0 rows assertions with deterministic expectations.

MEDIUM patches

P2-1 — Hoist rollup and drift

• Compute computed_readiness once.
• Compute readiness_drift in the next CTE from that value.

P2-2 — Normalize duplicate inventory refs

• Emit one function row per schema/name with overload count/signatures in metadata.
• Emit one trigger row per canonical trigger ref even when it handles multiple events.

P2-3 — Derive READ_BLOCKED from PG access facts

• Replace the fixed two-schema list with privilege/source-access checks or a PG-backed source policy.

P2-4 — Resolve routes and null/default behavior

• Surface whether missing_route_procedure_code exists.
• Make readiness booleans non-null.
• Add defaults/not-null rules for declared maturity, automation mode, and safety class as appropriate.
• Ensure update timestamps are maintained or stop presenting them as freshness facts.

P2-5 — Reconcile deferred-link wording

• Remove the claim that links live in links_jsonb, add that column, or explicitly designate manifest_jsonb as the temporary non-authoritative note location.

Re-review acceptance gate

Re-review only after the revised package includes:

1. complete candidate SQL, not abbreviated branches;
2. one strict parser/resolver contract;
3. deterministic expected outputs for all new negative tests;
4. no bare READY path without at least one required, usable, unambiguous PG-proven ingredient;
5. an idempotent governed apply/verify and rollback design consistent with the enacted laws.