Codex Focused Static Review — PIDX v0.3.3

1. Executive verdict

PASS_WITH_CAVEATS_REQUIRES_PATCH

v0.3.3 resolves the literal undefined-helper call, fixes psql hash binding inside DO, and blocks both live-table and view-over-live archive aliases. It is not ready for Owner build authorization discussion because three HIGH executable/idempotency defects remain:

the two identity sequences are name/kind checked but their static definition and identity ownership links are not fingerprinted, so sequence drift can still return PRESENT_MATCHING;
rollback Guard A treats expected auto-dependencies such as PIDX indexes/constraints as external dependents, so the clean rollback path can abort before Guard B;
the new backup recipe copies into LIKE ... INCLUDING ALL tables without OVERRIDING SYSTEM VALUE; because INCLUDING ALL copies GENERATED ALWAYS identity specifications, the copy is not executable as written.

Focused answers:

Undefined pidx_build_assertions() problem: the executable call is removed and no helper function is introduced. Prose references remain, so the packet's “grep finds zero tokens” evidence is inaccurate, but no undefined invocation remains.
One standalone read-only verifier: yes, structurally one WITH ... SELECT; however it is not a complete canonical verifier because identity-sequence facets are omitted.
psql variables inside DO: fixed. No :'identifier' occurs inside either dollar-quoted guard body.
Missing/malformed hash: fails closed through current_setting(..., true) plus strict lowercase 32-hex validation.
Live-table archive alias: rejected by approved-schema/base-table/OID-distinct checks.
View-over-live alias: rejected by relkind='r' before dynamic SQL; the dependency check is additional defense.
New BLOCK/HIGH regression: yes. The v0.3.3 switch from CTAS to LIKE INCLUDING ALL + plain INSERT ... SELECT * introduces a non-executable backup copy for the GENERATED ALWAYS identities.
Ready for Owner build authorization discussion: no; patch v0.3.3 and focused re-review first.

No DDL/DML, build, dry-run, production query, role/grant change, or design-source mutation was performed.

2. Files read

Rules/laws queried directly through Agent Data in the main process:

.claude/skills/incomex-rules.md — all 276 lines, 36 rules / 8 steps;
knowledge/dev/ssot/operating-rules.md v7.58;
knowledge/dev/ssot/vps/vps-operating-rules.md v1.0;
knowledge/dev/laws/constitution.md current v4.6.3 (v4.0 lineage);
knowledge/dev/laws/law-01-foundation-principles.md v3.3;
knowledge/dev/laws/dieu33-postgresql-law.md v2.1;
mission-related PIDX/build-gate/backup governance search results.

Required v0.3.2 evidence:

reports/codex-pidx-v0.3.2-focused-static-review-2026-06-23.md — 271 lines;
reports/codex-pidx-v0.3.2-required-patches-2026-06-23.md — 79 lines.

Required v0.3.3 package:

design/pidx-ddl-candidate-v0.3.3.sql.md — 522 lines;
design/pidx-build-access-idempotency-contract-v0.3.3.md — 96 lines;
design/pidx-test-plan-v0.3.3.md — 218 lines;
design/pidx-codex-review-packet-v0.3.3.md — 46 lines;
reports/pidx-v0.3.3-patch-from-codex-2026-06-23.md — 73 lines;
reports/pidx-build-design-go-no-go-v0.3.3.md — 37 lines.

Comparison-only v0.3.2 files were searched at the relevant identity, rollback, grant, helper, and archive sites. PostgreSQL behavior was checked against official PG16 documentation for CREATE TABLE LIKE/identity, INSERT identity override, pg_depend, pg_sequence, and ALTER SEQUENCE.

3. HIGH-1 single executable verifier review

Resolved

§7.1 is one read-only CTE/SELECT and contains the assertion battery inline.
No CREATE FUNCTION or executable call to pidx_build_assertions() exists.
to_regclass, role lookup through pg_roles, and per-assertion COALESCE(..., false) make the ABSENT path safe.
PRESENT_MATCHING requires both the pinned core fingerprint and all inline assertions.
Exact name/kind checks cover 4 core relations, 7 indexes, 2 identity sequences, and zero PIDX-namespaced functions.
Identity sequence names are deterministic for this candidate because the clean-slate preflight rejects a pre-existing PIDX-namespaced collision and the table/column names are fixed.

HIGH — identity sequence drift can false-match

The exact object-set CTE checks each identity sequence only by relname, relkind='S', and common owner. The canonical facets cover the four core relations and attached indexes, but do not include pg_sequence fields or the sequence-to-identity-column dependency.

Therefore changes such as sequence INCREMENT, MINVALUE, MAXVALUE, CACHE, CYCLE, persistence, or ownership-link drift can leave:

all 13 expected names/kinds present;
the core fingerprint unchanged;
all current assertions true;
build_state='PRESENT_MATCHING'.

PG16 exposes these static fields in pg_sequence, and ALTER SEQUENCE can change them independently. This violates the contract that any drifted expected object becomes PARTIAL_OR_DRIFTED. Add deterministic sequence-definition and identity-link facets/assertions; do not fingerprint the mutable current/last value.

Evidence note

The package repeatedly contains the prose token pidx_build_assertions(), contrary to its claimed zero-token grep result. This is LOW evidence-quality drift, not the former undefined-function execution defect.

4. HIGH-2 DO block hash binding review

The v0.3.2 binding defect is resolved.

Hash values are assigned through top-level SET LOCAL before either DO block.
Guard B reads them through current_setting('pidx....', true).
Missing values become NULL and malformed values fail ^[0-9a-f]{32}$; B0 raises before seed/archive authorization.
No executable psql-variable expression occurs in a dollar-quoted body. One explanatory comment inside Guard B contains the text NOT :'var'; it has no SQL/PLpgSQL effect.
Both hashes use fixed business-column projections and md5(COALESCE(string_agg(...), '')), giving deterministic non-NULL md5('') for an empty set.

Non-blocking integrity caveat: delimiter-based concat_ws('|', ...) is not collision-safe when data can contain delimiters/newlines and collapses NULL/empty for explicitly coalesced fields. Prefer an unambiguous canonical encoding (for example, ordered JSON/length-prefixed fields) before pinning hashes.

5. HIGH-3 backup archive independence review

Resolved alias trust boundary

Guard B now:

resolves both manifest relation texts once to regclass;
requires schema pidx_archive, permanent ordinary base tables, and distinct archive/live/pair OIDs;
rejects view aliases before dynamic SQL and additionally checks rewrite/direct dependencies;
compares ordered column names/types and owners;
runs count/hash dynamic SQL only with validated regclass values;
requires archive = live = manifest;
raises on missing/weak proof before DROP.

The archive remains a governed build/rollback artifact and is not read by either PIDX runtime view, so 2T2V runtime truth is preserved.

HIGH — backup construction cannot copy the identity columns as written

§10.1 P-4 creates archive tables with LIKE public.<live> INCLUDING ALL, then uses plain INSERT INTO archive SELECT * FROM live. PG16 specifies that INCLUDING ALL includes INCLUDING IDENTITY, creating new identity sequences, and that explicit values for a GENERATED ALWAYS identity require OVERRIDING SYSTEM VALUE. Both live tables define id GENERATED ALWAYS AS IDENTITY.

The proposed backup copy therefore errors instead of producing the archive required for non-seed rollback. Patch both copy statements with an explicit reviewed column list and OVERRIDING SYSTEM VALUE (or use another proven independent-copy construction), then add a positive construction/R-PASS test.

HIGH — Guard A catches expected internal dependencies

Guard A's generic branch rejects every pg_depend row with deptype IN ('n','a') referencing a PIDX core relation unless classid='pg_class' and objid is one of the four core relation OIDs. It does not allow the expected PIDX indexes or table constraints.

PostgreSQL records auto dependencies for objects such as indexes/constraints on their table/columns. The candidate itself requires seven indexes and several constraints, so a clean build can satisfy Guard A's predicate and abort before Guard B. This makes R-PASS unproven and potentially impossible. The generic dependency guard must distinguish the reviewed internal object/dependency closure from external dependents, while retaining DROP ... RESTRICT as the final backstop.

Owner/ACL/retention is only partially concrete: owner and approved schema/REVOKE-PUBLIC posture are stated; archive retention duration/pruning/restore procedure remains an Owner decision.

6. MEDIUM cleanup review

Resolved:

seed hashes exclude source_ref, IDs, and dates;
empty-set hashes are deterministic/non-NULL;
R7 uses a valid ctid subquery rather than DELETE ... LIMIT 1;
R9/R10 cover live-table/view aliasing;
A8 covers ingredient UPDATE(id) denial;
A12 covers schema USAGE and runtime membership.

Remaining:

MEDIUM: S11 accepts any indisunique index without requiring indisvalid, indisready, a non-partial predicate (indpred IS NULL), and plain key columns. A partial/invalid unique index does not prove global uniqueness. Use only live, valid, ready, non-partial unique key columns and account for indnkeyatts/INCLUDE columns.
MEDIUM: seed/archive hash encoding is delimiter-ambiguous as noted in §4.
LOW: R2b asserts an exact inheritance pg_depend row but was not executed; after fixing Guard A, prove the chosen external dependency against the exact PG16 catalog predicate.
LOW: the packet's claimed zero-token helper grep is contradicted by the package's prose references.

7. Regression review

2T2V runtime surface remains intact.
No helper function, workflow engine, scheduler/event bus, RAG/vector/pg_trgm, Nuxt logic, auto-execution, auto-fix, auto-approval, or Directus runtime dependency was introduced.
Readiness SQL is inherited unchanged; no weakening of lifecycle/usability, false-READY protection, UNKNOWN_SOURCE, or READ_BLOCKED handling was found.
The 18-row expected readiness totals remain inherited and no contradictory total appears in v0.3.3.
Rollback remains fail-closed against data loss, but is not operationally executable for the reasons in §5.
Idempotency remains fail-closed for missing/extra names, but can false-match identity-sequence definition drift.
No DDL/DML instruction was executed during this review.

8. Scope / no-new-truth review

PIDX remains an index over existing PG truth: it does not invent classifications, green UNKNOWN_SOURCE, create a workflow engine, or become a runtime source of truth. pidx_archive is confined to build/rollback recovery and is not part of the 2T2V read path.

3 câu Tuyên ngôn

Vĩnh viễn: the patch direction fixes root trust boundaries, but canonical sequence facets and a genuinely executable rollback/backup path are still required before the design is durable.
Nhầm được không: currently yes—the verifier can accept altered identity-sequence semantics, and the candidate backup command can be selected even though it cannot copy the IDs. Catalog gates/tests must make both mistakes impossible.
100% tự động: not yet. Clean rollback and positive archive construction are not proven end-to-end; manual interpretation would still be required.

Bảo toàn / Assembly / data flow

Bảo toàn: no ID, relation, metadata, or source truth was mutated in this review. The required backup patch must preserve exact IDs and recovery semantics.
Assembly Gate: PG-native catalog/read-only verifier is appropriate; Directus classification remains an Owner decision; Nuxt stays display-only.
Five design questions: model=2T2V index; closed flow=governed apply→verify→read→fail-closed rollback; tools=paired migration/verifier plus backup guard; environment=PG16/public + governed archive; golden rule=PG facts/metadata over application code.
Data flow: PG sources → PIDX tables/views → read-only consumers; no reverse write and no bypass.

9. Owner-decision items before build

Điều 33 classification.
PG-native prototype versus Directus registration.
Governed build route and paired Cấp-B/Cấp-A DOT/APR authorization.
Concrete owner/writer/reader/runtime role mapping and provisioning.
Canonical expected fingerprint pinning after the sequence-facet patch.
Verified backup/archive schema, ACL, retention duration, pruning, restore, and sequence-state policy.
Fixture persistence versus build-test-only fixtures.
UNKNOWN_SOURCE policy.
Auxiliary-object policy, including exact indexes, deterministic identity sequence names, static sequence parameters, and ownership links.

These are decisions only; this report does not request or grant authorization.

10. Required patches

BLOCK: none.

HIGH:

Extend the canonical verifier to fingerprint/assert both identity sequences' static pg_sequence definition, persistence, and correct identity-column ownership/dependency; add a sequence-drift negative test.
Repair Guard A's generic dependency predicate so expected indexes, constraints, identity machinery, and other reviewed internal dependencies pass, while external view/FK/inheritance/generic dependents still abort; execute a precise clean R-PASS test in the later governed test phase.
Make the independent archive copy executable for GENERATED ALWAYS identities using explicit columns plus OVERRIDING SYSTEM VALUE (or a proven equivalent), and add a positive archive-construction test.

MEDIUM:

Harden S11 to accept only valid, ready, non-partial, plain-key unique indexes/constraints with correct key-column handling.
Replace delimiter-ambiguous seed/archive hash serialization with an unambiguous canonical encoding.

LOW:

Correct the zero-token grep/evidence claim and prove R2b against the final Guard A predicate.

Detailed acceptance criteria are in reports/codex-pidx-v0.3.3-required-patches-2026-06-23.md.

11. Final recommendation

Patch v0.3.3 then re-review.

Exactly one next step: produce v0.3.4 resolving the three HIGH findings and run another focused static review before any Owner build authorization discussion.

INCOMEX step disposition:

Step 0: skill, OR, Constitution, foundation/PG law read through the required main-process KB route.
Steps 1–2: mission and all required v0.3.2/v0.3.3 evidence reviewed; static design questions answered.
Step 3: only review reports written; candidate design sources untouched.
Steps 4–5: N/A by mission—no build/deploy/production verification authorized.
Step 6: main report + required-patches handoff written. OR update not required because no new operating rule was introduced.

Actual static evidence (workspace output, no SQL executed):

$ wc -l -c main-report required-patches
240 16351 codex-pidx-v0.3.3-focused-static-review-2026-06-23.md
 78  4451 codex-pidx-v0.3.3-required-patches-2026-06-23.md

$ rg required main-report headings
## 1 through ## 11: all 11 headings present in order

$ rg critical candidate sites
297/298: LIKE ... INCLUDING ALL
299/300: INSERT INTO pidx_archive... SELECT * (no OVERRIDING SYSTEM VALUE)
346: d.deptype IN ('n','a') generic Guard A predicate
358/359: current_setting(...) hash binding; only line 358's comment contains NOT :'var'
pg_sequence / seqincrement canonical facets: zero matches

$ rg 'pidx_build_assertions\(' across v0.3.3 package
10 matches, all explanatory/evidence prose; zero executable FROM/CREATE call

$ rg required-patches headings
HIGH-1, HIGH-2, HIGH-3, MEDIUM, LOW, Re-review gate: present

$ Agent Data upload/read-back (first close pass)
main: status=created, revision=1; read-back verdict=PASS_WITH_CAVEATS_REQUIRES_PATCH
patches: status=created, revision=1; read-back heading=HIGH-1 — Identity sequence drift is not canonicalized