Codex Required Patches — PIDX v0.3.2

Focused static-review output only. No design source was modified and no DDL/DML was executed.

HIGH-1 — Canonical classifier is not an executable single artifact

Defect

ddl §7.1 calls pidx_build_assertions(), but no such function is defined. §7.4 is a standalone CTE query. The note offers two future alternatives—create a helper or inline the battery—but the reviewed candidate implements neither.

Creating the helper would also add a runtime function outside the stated 2T2V set, and the current canonical query would not fingerprint or reject that function.

Required patch

• Preferred: inline §7.4 as an assertions CTE in §7.1 and compute bool_and(passed) from that CTE.
• Deliver one complete verifier statement; remove alternative/prose-only composition.
• Define expected PIDX auxiliary objects explicitly, including identity sequences and indexes.
• Reject unexpected PIDX-namespaced objects across the relevant catalogs (pg_class, pg_proc, and any other allowed auxiliary class).
• Derive the pinned fingerprint only after this final verifier/artifact form is fixed.

Acceptance

• The verifier parses as one read-only statement with no helper function.
• Clean reviewed state → PRESENT_MATCHING.
• Any failed assertion, missing/extra runtime or auxiliary object, or facet drift → PARTIAL_OR_DRIFTED.
• No extra runtime verifier object is required.

HIGH-2 — Rollback guard does not receive pinned hashes

Defect

:'expected_seed_proc_hash' and :'expected_seed_ingredient_hash' occur inside DO $$...$$. psql variable interpolation is not performed within quoted SQL literals, including a dollar-quoted DO body. The PL/pgSQL parser receives invalid colon syntax.

Required patch

• Bind the reviewed values outside the DO body through a valid, explicit mechanism.
• One option is transaction-local custom settings populated before DO and read inside it with current_setting(..., true); the guard must validate presence and exact format before use.
• Alternatively generate a final immutable artifact with correctly quoted literal constants before review.
• Add a static/execution test proving the final DO block receives the expected values and fails closed when either is missing/malformed.

HIGH-3 — Backup manifest can point at the live PIDX tables

Defect

The manifest relation names need only resolve and match live counts/hashes. They can name public.pidx_procedure and public.pidx_procedure_ingredient themselves. Every current check passes, then DROP removes the only copy.

Required patch

Resolve both archive relation OIDs once and assert:

• namespace is the approved archive schema;
• relkind is an independent base table;
• each archive OID differs from both live PIDX table OIDs and from the other archive OID;
• neither archive relation depends on either live PIDX relation;
• expected column names/types are present;
• archive owner/ACL/retention meet the governed backup policy.

Use the validated OIDs/regclass values for dynamic count/hash queries. Do not interpolate unchecked text with format('%s', ...).

Acceptance

• manifest points to live tables → RAISE, no DROP;
• manifest points to a view over live tables → RAISE;
• wrong schema, foreign/partitioned/unlogged policy violation, same OID twice, or dependent relation → RAISE;
• valid independent two-table archive with matching counts/hashes → guard passes;
• every failure leaves all four PIDX runtime objects present.

MEDIUM — Manifest/test completeness

1. Use only fixed business columns in seed content hashes, or explicitly classify immutable artifact metadata such as source_ref.
2. Define a non-NULL deterministic hash for an empty set.
3. Replace invalid DELETE ... LIMIT 1 in R7 with valid PostgreSQL, for example deleting by a selected ctid in a subquery.
4. Add explicit tests for live-table archive aliasing, view-over-live aliasing, and ingredient UPDATE(id) denial.
5. Make R2b produce one exact, catalog-proven generic dependency.
6. Verify backing UNIQUE constraints for S11 keys, schema USAGE, and runtime-role membership.

Re-review gate

Return only after all three HIGH items have executable candidate SQL and deterministic negative tests. No build/dry-run authorization is implied.