GPT Review — 23-P3B Schema Prompt rev1

Date: 2026-05-07
Reviewer: GPT-5.5 Thinking / Incomex Hội đồng AI
Reviewed: knowledge/dev/laws/dieu44-trien-khai/prompts/23-p3b-iu-edit-draft-schema-prompt.md rev1

Verdict

Rev1 is directionally correct but NOT ready to dispatch. Rev2 required.

Opus correctly produced a schema-only prompt and kept P3B within scope. The two pushbacks are accepted:

1. Seed only two dot_config keys now:
   • iu_edit.policy.default_mode = auto_apply
   • iu_edit.schema.version = p3b-v1
2. Skip comment_kind and parent_comment_id indexes in Phase 1.

However, several execution risks remain: rollback after DDL failure, owner/grant sequencing, preflight strictness, direct row DML policy, and weak verification of constraints/index names.

Accepted decisions

• P3B schema-only scope is accepted.
• unit_edit_draft and unit_edit_comment schemas are broadly accepted.
• base_version_seq, stale_at, unit_id on comments, resolved_*, parent_comment_id, and btrim checks are accepted.
• sort_order integer NULL as core column is accepted.
• Minimal dot_config seed of 2 keys is accepted.
• No functions/triggers/gateway changes is accepted.

Required rev2 fixes

P1 — Use environment defaults, not hardcoded container/user/db only

Current prompt hardcodes:

CONTAINER="postgres"
DB="directus"
DBUSER="directus"

Patch to:

CONTAINER="${PG_CONTAINER:-postgres}"
DB="${PG_DB:-directus}"
DBUSER="${PG_USER:-directus}"

This follows prior prompts and avoids environment drift.

P2 — Initialize SORT_ORDER_EXISTS and other preflight vars

SORT_ORDER_EXISTS is echoed in final report but not initialized if preflight fails early.

Initialize:

SORT_ORDER_EXISTS="unknown"
POLICY_KEY_EXISTS=""
UV_LIFECYCLE_CONVENTION=""
DRAFT_EXISTS=""
COMMENT_EXISTS=""
SIMILAR=""

P3 — Preflight similar-table check should not fail on Directus/system comments unrelated to IU

Current pattern %comment% could flag unrelated CMS/comment tables if present. It currently checks %edit%comment%, which is safer, but also %proposal% could flag unrelated proposal tables.

Patch behavior:

• If similar tables found, report them and STOP only if they are IU/edit/draft/proposal-related.
• Better query:
  • tablename IN ('unit_proposal','unit_proposal_comment','unit_edit_draft','unit_edit_comment')
  • OR tablename LIKE 'unit%proposal%'
  • OR tablename LIKE 'unit%edit%'

Do not stop on generic unrelated comment/proposal tables.

P4 — Gate 5 schema sanity should be a hard gate, not comment-only

Rev1 logs IU/UV column counts but does not enforce expected shape.

Patch:

• expected IU columns: 16 after P3A? Actually P3A did not add columns; P3B pre-DDL should be 15 unless sort_order already exists from previous attempt.
• accept IU count = 15 if sort_order missing; 16 if sort_order already integer.
• expect UV columns = 9.
• if unexpected, STOP and report schema drift.

This protects against hidden schema changes before DDL.

P5 — Gate 11 existing iu_edit.% keys must reconcile, not ignore

Rev1 only logs existing keys.

Patch:

• if iu_edit.policy.default_mode exists and value != auto_apply, STOP;
• if iu_edit.schema.version exists and value != p3b-v1, STOP or explicitly report already-different schema version;
• if exact values exist, treat idempotent seed as OK;
• unrelated iu_edit.% keys should be reported but not necessarily block.

P6 — DDL should be all one transaction including owner changes and grants if possible

Rev1 runs DDL transaction, then owner changes outside it. If owner change fails, schema remains partially created.

Patch one of two ways:

Preferred: include owner changes in same transaction after table creation, using a DO block with discovered owner set through session GUC.

Simpler acceptable: if owner changes remain outside transaction, then failure must be CRITICAL and report partial schema exists. Do not call phase PASS.

GPT recommendation: include owner changes inside the DDL transaction using current_setting('app.p3b_owner').

P7 — Avoid hardcoding owner into unquoted ALTER TABLE shell SQL

Current:

ALTER TABLE public.unit_edit_draft OWNER TO $IU_OWNER;

This is unsafe if role name needs quoting.

Use SQL format('%I', owner) inside DO:

DO $$
DECLARE v_owner text := current_setting('app.p3b_owner');
BEGIN
  EXECUTE format('ALTER TABLE public.unit_edit_draft OWNER TO %I', v_owner);
  EXECUTE format('ALTER TABLE public.unit_edit_comment OWNER TO %I', v_owner);
END $$;

P8 — Add table-level permission policy explicitly

Prompt says report grants, but does not decide grants.

For P3B, because functions do not exist yet, keep default owner-only privileges. Do not grant PUBLIC. Do not grant broad DML to Agent roles yet.

Report:

• owner;
• any PUBLIC grants count, expected 0 unless default privileges say otherwise.

If PUBLIC grants exist, WARN/FAIL? GPT recommendation: FAIL if PUBLIC has INSERT/UPDATE/DELETE on new edit tables.

P9 — Add stronger FK and status/timestamp consistency constraints, but keep safe

Add safe consistency checks:

For unit_edit_draft:

• applied_version_ref IS NULL OR draft_status='applied'
• applied_at IS NULL OR draft_status='applied'
• applied_by IS NULL OR draft_status='applied'
• stale_at IS NULL OR draft_status='stale_base'

But avoid constraints that require applied_at when applied, because P3C function may set order and we do not want schema too strict prematurely.

P10 — Consider denormalized canonical_address consistency caveat

canonical_address is denormalized and can drift from information_unit.canonical_address. That is acceptable for Phase 1 snapshot/history, but design should state it.

Add comment or report note:

• canonical_address on draft is snapshot/readability field; canonical FK is unit_id.
• P3C function must populate it from current IU, not caller-trusted text.

P11 — Add CHECK for base_version_seq > 0

Safe and aligned with current versioning.

CONSTRAINT chk_ued_base_seq CHECK (base_version_seq > 0)

P12 — Add CHECK for draft_content_hash and base_content_hash non-empty

CONSTRAINT chk_ued_base_hash CHECK (btrim(base_content_hash) <> '')
CONSTRAINT chk_ued_draft_hash CHECK (btrim(draft_content_hash) <> '')

P13 — Use more precise verification than column counts

V4/V5 only count columns. Add verification for required column names/types using information_schema.columns.

At minimum, query and report missing required columns:

WITH expected(table_name, column_name, data_type) AS (...)
SELECT * FROM expected e
LEFT JOIN information_schema.columns c ...
WHERE c.column_name IS NULL OR c.data_type <> e.data_type;

Expected 0 mismatches.

P14 — Constraint verification should check names/types, not count only

V6 checks count >=4. Better check required constraint names exist:

• chk_ued_status
• chk_ued_created_by
• chk_ued_body
• chk_ued_title
• chk_ued_address
• new checks from P9/P11/P12
• comment checks: chk_uec_author_type, chk_uec_kind, etc.

Counts can be report-only.

P15 — Index verification should check names, not count only

Verify expected index names:

• idx_ued_unit_status_created
• idx_ued_address_status_created
• idx_ued_base_version
• idx_ued_applied_version
• idx_uec_draft_created
• idx_uec_unit_created
• idx_iu_parent_sort if sort_order was added or exists and index intended.

P16 — JSONB default check should cover both tables

Check:

• unit_edit_draft.metadata
• unit_edit_comment.metadata

Do not leave V9 as warn-only for one table. Missing JSONB default should be FAIL because P3C functions rely on it.

P17 — Verify no new functions by before/after count

P3B hard boundary says no functions. Add before/after function count or list matching fn_iu_%edit%, fn_iu_%draft%, fn_iu_%comment%.

Expected no new functions.

P18 — Transaction rollback verification for DDL failure

If DDL fails, report whether partial objects exist:

• unit_edit_draft exists?
• unit_edit_comment exists?
• sort_order exists?
• policy keys exist?

If partial schema exists, phase_status=CRITICAL, not FAIL.

P19 — Owner/grants verification should be part of V tests

Add verification:

• both new tables owner = IU_OWNER;
• PUBLIC INSERT/UPDATE/DELETE count = 0;
• grants reported.

P20 — Report should include exact DDL applied and skipped state

Need to know whether sort_order was added or already existed. Add variables:

• SORT_ORDER_ACTION=ADDED/SKIPPED_ALREADY_EXISTS
• DRAFT_TABLE_ACTION=CREATED
• COMMENT_TABLE_ACTION=CREATED
• POLICY_KEYS_ACTION=SEEDED/SKIPPED_EXISTING

Directive to Opus

Patch P3B prompt to rev2 with P1–P20.

Path:

knowledge/dev/laws/dieu44-trien-khai/prompts/23-p3b-iu-edit-draft-schema-prompt.md

Do not dispatch after patch. Return for GPT/User review.

Hard boundaries remain

• No functions.
• No trigger changes.
• No gateway changes.
• No IU/UV row mutations.
• No vector mutation.
• No cleanup.
• No retry / no improvise.

Summary

Rev1 has the right schema and scope, and Opus's simplification choices are accepted. Rev2 should harden execution: exact schema drift gates, owner changes inside transaction, no broad PUBLIC grants, stronger constraints, and verification by required names/types rather than counts.