Codex Required Patches — PIDX v0.3.3

Focused static-review output only. No candidate design source was modified and no SQL was executed.

HIGH-1 — Identity sequence drift is not canonicalized

Defect

The exact object set proves only that pidx_procedure_id_seq and pidx_procedure_ingredient_id_seq exist as sequences with the expected owner. The fingerprint/assertion battery omits their static pg_sequence parameters, persistence, and correct identity-column ownership/dependency.

An ALTER SEQUENCE change can therefore preserve all current gates and still return PRESENT_MATCHING.

Required patch

• Add canonical facets for data type, start, increment, min, max, cache, cycle, and persistence for each expected sequence.
• Assert each sequence is the sequence attached to the intended GENERATED ALWAYS identity column and has the intended dependency/ownership link.
• Do not fingerprint current/last values, which legitimately change with inserts.
• Add a negative test that alters at least increment/cache/cycle or the ownership link and expects PARTIAL_OR_DRIFTED.

Acceptance

• clean 13-object build → PRESENT_MATCHING;
• missing/renamed/extra/wrong-kind sequence → PARTIAL_OR_DRIFTED;
• static sequence-definition or identity-link drift → PARTIAL_OR_DRIFTED;
• ordinary nextval/row inserts do not create false drift.

HIGH-2 — Guard A rejects expected PIDX internal dependencies

Defect

The generic pg_depend predicate rejects normal/auto dependents unless the dependent OID is one of the four core relations. Expected indexes and constraints are dependent on the PIDX tables/columns and are not allowlisted, so a clean rollback can abort before the seed/archive guard.

Required patch

• Define the reviewed internal dependency closure for 2T2V + expected indexes/sequences/constraints/identity machinery.
• Exclude only that closure from the generic external-dependent test.
• Continue to reject external rewrite/view, inbound FK, inheritance, and other normal/auto dependents.
• Keep DROP ... RESTRICT as the final backstop.
• Replace the prose-only R-PASS with an exact test proving clean Guard A passes and the selected external dependency fails.

Acceptance

• clean candidate with all expected indexes/constraints/identities → Guard A passes;
• external view, inbound FK, and one catalog-proven generic dependency → Guard A raises;
• any raise occurs before DROP and leaves all PIDX objects present.

HIGH-3 — Independent backup copy fails on GENERATED ALWAYS identity

Defect

The recipe uses CREATE TABLE ... (LIKE live INCLUDING ALL), which copies identity specifications, then INSERT INTO archive SELECT * FROM live. Both source tables include explicit id values and both archive identities are GENERATED ALWAYS; PostgreSQL requires OVERRIDING SYSTEM VALUE for that copy.

Required patch

• Use an explicit reviewed column list with INSERT ... OVERRIDING SYSTEM VALUE SELECT ..., or another independently verified base-table copy method.
• Preserve exact IDs and document/reset sequence state needed by the restore procedure.
• Add a positive backup-construction test before R-PASS; prove archive counts/hashes equal live and Guard B accepts it.

Acceptance

• non-seed data can be copied into two independent permanent archive tables without error;
• exact IDs and all business columns are preserved;
• archive OIDs remain distinct and independent;
• Guard B accepts only the valid pair and still rejects live/view aliases and stale/partial copies.

MEDIUM — Remaining verifier/test hardening

1. S11 must require a unique index to be valid, ready, non-partial, and based on the exact plain key columns; handle indnkeyatts so INCLUDE columns do not corrupt the comparison.
2. Replace delimiter-based concat_ws content serialization with an unambiguous canonical encoding that distinguishes field boundaries and NULL from empty string.

LOW — Evidence cleanup

1. Correct the claim that grep finds zero pidx_build_assertions( tokens; prose references exist, though the executable call is removed.
2. Prove R2b's exact catalog row against the final Guard A query rather than carrying an unexecuted assumption.

Re-review gate

Return with v0.3.4 only after all three HIGH patches are present as complete candidate SQL plus deterministic positive/negative tests. No build or Owner authorization is implied.