Codex Required Patches — PIDX v0.3.1

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

HIGH-1 — Canonical idempotency verifier accepts material drift

Defect

ddl §7.1 hashes only relation name/relkind, column name/type/not-null, and view body. It omits constraints, defaults/identity attributes, indexes, reloptions, owners, grants, and role/source-access state.

Consequently PRESENT_MATCHING can be returned after safety-critical drift, and the runner treats that state as no-op success.

Required patch

• Define a deterministic canonical verifier for all four objects and the four required indexes.
• Bind at minimum: object kind/name, ordered columns, defaults, attidentity/identity generation, PK/UNIQUE/CHECK/FK definitions and FK actions, indexes, view definitions, security_invoker reloptions, owners, table/column/view ACLs, required role membership, and reader access to every source relation/schema used by the views.
• Either include those dimensions in a stable artifact-derived fingerprint or combine the current hash with exact boolean assertions.
• Classify any failed assertion as PARTIAL_OR_DRIFTED.
• Run the complete verifier on every PRESENT_MATCHING no-op path.
• Derive/store the expected artifact identity from the reviewed migration, not an arbitrary first resulting state.

Acceptance tests

Independently alter/drop one CHECK, FK action, explicit index, identity mode/default, security_invoker, owner, grant, and required role/source privilege. Every case must return PARTIAL_OR_DRIFTED; none may return PRESENT_MATCHING.

HIGH-2 — Rollback backup/data guard is bypassable

Defect

source_ref is controlled by pidx_writer: parent INSERT covers all columns, parent UPDATE explicitly includes source_ref, and ingredient INSERT/UPDATE is unrestricted. Real rows can therefore be tagged as seed:pidx-seed-slice-v0.3 and excluded from the non-seed count.

For counted non-seed rows, Guard B accepts any non-empty pidx.rollback_backup_ref. PostgreSQL accepts arbitrary two-part custom setting names; the GUC is an operator assertion, not proof that archive tables exist or contain recoverable data.

Required patch

Choose one fail-closed design:

1. Preferred/simple: if either PIDX table has any rows, abort unless a machine-verifiable archive manifest proves both tables are backed up; or
2. make seed provenance immutable and system-authored, then still validate the backup artifact for every non-seed row.

The guard must verify, inside the rollback transaction:

• both named archive relations exist;
• expected columns/types are present;
• archived row counts equal live row counts;
• a deterministic content checksum/manifest matches for both tables;
• the manifest is tied to this rollback token/run and cannot be satisfied by a free-form string alone.

Remove source_ref from ordinary writer UPDATE/INSERT authority if it remains security-relevant.

Acceptance tests

• real row retagged with seed literal → rollback aborts;
• fake non-empty GUC, no archive → aborts;
• only one archive table → aborts;
• count/checksum mismatch → aborts;
• valid two-table archive manifest → guard passes;
• any failure leaves all four PIDX objects present.

MEDIUM-1 — Explicit GENERATED ALWAYS identity remains insertable

Defect

GRANT INSERT ON ... TO pidx_writer grants INSERT on every column. PostgreSQL permits an explicit value for a GENERATED ALWAYS identity when the statement says OVERRIDING SYSTEM VALUE; this is syntax, not a separately grantable capability.

Required patch

• Replace table-level INSERT with explicit column-level INSERT lists excluding id on both tables.
• Exclude immutable/system provenance fields as appropriate.
• Add negative behavior tests using OVERRIDING SYSTEM VALUE for both IDs.

MEDIUM-2 — Generic dependency guard is not transactional

Defect

Preflight P-2c queries generic normal/auto pg_depend dependencies, but Guard A inside §10.2 only checks rewrite dependents and inbound FKs while claiming to recheck P-2a/b/c.

Required patch

Add the P-2c predicate to the in-transaction Guard A. Keep default DROP RESTRICT as the final backstop. Add a non-rewrite, non-FK normal-dependency test.

MEDIUM-3 — Reader role contract is incomplete

Defect

security_invoker=true requires the actual reader to hold privileges on all underlying sources. The candidate grants pidx_reader only PIDX tables/views and relies on pre-existing direct privileges of context_pack_readonly.

Required patch

Enumerate the source schemas/relations and either grant them through the governed reader role or verify the mapped login's existing direct privileges. Execute the full view/read-blocked test suite under the exact runtime role.

LOW — Final build artifact and uniqueness assumptions

• Materialize the section-concatenation recipe into one immutable migration artifact paired with its verifier.
• Verify uniqueness constraints or explicit multiplicity behavior for DOT, approval, workflow, and event lifecycle probes that use LIMIT 1.

Re-review gate

Return for focused static review only after both HIGH items and their negative tests are present. No build/dry-run authorization is implied.