GPT Review — 22-P2 Main Functions Prompt rev4

Date: 2026-05-06 Reviewer: GPT-5.5 Thinking / Incomex Hội đồng AI Reviewed: knowledge/dev/laws/dieu44-trien-khai/prompts/22-p2-iu-native-create-main-functions-prompt.md rev4

Verdict

Rev4 is very close, but do not dispatch yet. Rev5 required.

Rev4 fixed the important writer-phase risks from rev3: specific unique_violation diagnostics, full column type gates, deterministic addresses, explicit ON_ERROR_STOP, and post-COMMIT critical handling. However, a few last writer-phase safety gaps remain.

Required rev5 patches

P1 — Main function must hard-fail if canonical unique guard cannot be discovered

Rev4 discovers v_ca_unique_guard inside fn_iu_create, but does not immediately fail if it is still NULL.

Although §1 preflight discovers the guard, the function itself should be self-protecting because it is a durable native contract.

Patch after discovery inside fn_iu_create:

IF v_ca_unique_guard IS NULL THEN
  RAISE EXCEPTION 'canonical_address unique guard not found';
END IF;

This avoids a future schema drift case where the function continues without its idempotency correctness boundary.

P2 — Dynamic SET CONSTRAINTS should be schema-qualified or explicitly justified

Rev4 uses:

EXECUTE format('SET CONSTRAINTS %I DEFERRED', v_fk_name);

Constraint names can collide across schemas/tables. The discovered FK belongs to public.information_unit.

Patch to schema-qualified form if PostgreSQL accepts it in the target environment:

EXECUTE format('SET CONSTRAINTS public.%I DEFERRED', v_fk_name);

If testing shows this form is not accepted, keep the unqualified command but add a preflight check/report that no other visible constraint has the same name. Do not silently rely on unqualified constraint resolution.

P3 — Add hard-gate DO block for main function name conflicts

§1 lists main functions and comments “Any row → STOP.” For writer-phase, make it an executable hard gate:

DO $$
DECLARE v_count int;
BEGIN
  SELECT count(*) INTO v_count
  FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
  WHERE n.nspname='public'
    AND p.proname IN ('fn_iu_create','fn_iu_create_plan');
  IF v_count <> 0 THEN
    RAISE EXCEPTION 'main function name conflict: % existing', v_count;
  END IF;
END $$;

No overload workaround.

P4 — Replace placeholder substitution with psql variables or add hard placeholder guard

Rev4 still uses literal placeholders in SQL blocks:

• $PILOT_ADDRESS
• $PLAN_ADDRESS_1
• $PLAN_ADDRESS_2

This is acceptable only if the Agent substitutes exact validated values. But if it forgets, the placeholder strings may be treated as real values and tests can mislead.

Preferred patch:

• Use psql variables and :'PILOT_ADDRESS' only after setting them safely from captured values; or
• Add a hard guard before tests:

DO $$
BEGIN
  IF '$PILOT_ADDRESS' LIKE '$%' OR '$PLAN_ADDRESS_1' LIKE '$%' OR '$PLAN_ADDRESS_2' LIKE '$%' THEN
    RAISE EXCEPTION 'address placeholder not substituted';
  END IF;
END $$;

Because generated addresses are regex-limited to [a-z0-9.-], literal insertion is acceptable after this guard. Without the guard, it is not.

P5 — Post-COMMIT critical verification must still upload report

Rev4 correctly raises exception on post-COMMIT verify fail. But with ON_ERROR_STOP=1, Agent may stop before uploading the report if not explicitly instructed.

Patch §5/§6:

• If post-COMMIT verify raises exception, Agent must catch/record it at shell/session level, then upload report marked CRITICAL.
• Report must include pilot address, verify JSON/error, and state that cleanup is not approved.

The phase should fail, but reporting must still happen.

P6 — Permission check should explicitly verify PUBLIC absent

Rev4 displays routine privileges. Add explicit report instruction:

• PUBLIC must not have EXECUTE on either function.
• If PUBLIC EXECUTE is present → P2 FAIL / rollback if before COMMIT; if discovered post-COMMIT → CRITICAL permission blocker.

Because permission is a production security boundary, do not leave it as visual inspection only.

P7 — Report must include exact generated address capture method

Add to report:

• how PILOT_ADDRESS, PLAN_ADDRESS_1, PLAN_ADDRESS_2 were generated/captured;
• regex validation result;
• confirmation no placeholder remained in executed SQL.

This protects against operator error and rerun confusion.

Non-blocking note

The directus grant policy is acceptable as current-environment candidate: commit allowed if missing, but adapter readiness blocked. This is fine because P2 is PG contract creation, not adapter enablement.

Directive to Opus

Patch P2 prompt to rev5 with P1–P7. Do not dispatch after patch; return for GPT/User final review.

Hard boundaries remain

• no dispatch yet;
• no raw birth_registry insert;
• no DOT adapter;
• no dot_tools registration;
• no default seeding;
• no cleanup pilot;
• no retry/improvise on SQL failure.

Summary

Rev4 is close. The remaining items are last-mile durability: self-protect if idempotency guard disappears, avoid ambiguous SET CONSTRAINTS, make name conflict a hard executable gate, prevent placeholder execution, ensure report upload after post-COMMIT failure, and verify PUBLIC permissions explicitly.