Incomex AI Portal
KB-356F rev 4

23-P3B — IU Edit Draft Schema-Only — Execution Prompt (rev4)

26 min read Revision 4
pack-23p3bpromptrev4schemaedit-draftcomment

23-P3B — IU Edit Draft Schema-Only — Execution Prompt (rev4)

Date: 2026-05-07 Author: Opus (Claude) Status: PROMPT rev4 — chờ GPT/User final review. CHƯA dispatch. Rev4: 5 fixes (fn count SQL, V14 policy keys, partial key-specific, detail output, sort_order_action clarified).

Mục tiêu

Schema-only: CREATE unit_edit_draft + unit_edit_comment, ALTER IU ADD sort_order, seed 2 dot_config keys. Không functions, triggers, gateway changes, IU/UV row mutations.

Report: knowledge/dev/laws/dieu44-trien-khai/reports/23-p3b-iu-edit-draft-schema-report.md

§0. Pre-read

Agent đọc: design rev5, P3A report, 23-P2 inspection report.

Setup

#!/usr/bin/env bash
CONTAINER="${PG_CONTAINER:-postgres}"
DB="${PG_DB:-directus}"
DBUSER="${PG_USER:-directus}"
PSQL=(docker exec -i "$CONTAINER" psql -U "$DBUSER" -d "$DB" -v ON_ERROR_STOP=1)
PSQL_NOSTOP=(docker exec -i "$CONTAINER" psql -U "$DBUSER" -d "$DB")
TS=$(date +%Y%m%d-%H%M%S)
LOG="/tmp/23-p3b.${TS}.log"
exec > >(tee -a "$LOG") 2>&1

PREFLIGHT_STATUS=""
DDL_STATUS="NOT_RUN"; DDL_EXIT=""
VERIFY_FAIL=0
PHASE_STATUS=""; P3C_READINESS="BLOCKED"
IU_COUNT_BEFORE=""; UV_COUNT_BEFORE=""
IU_COUNT_AFTER=""; UV_COUNT_AFTER=""
IU_OWNER=""
UV_LIFECYCLE_CONVENTION=""
SORT_ORDER_EXISTS="unknown"; SORT_ORDER_ACTION="NOT_RUN"
SORT_ORDER_INDEX_EXISTS="unknown"
DRAFT_TABLE_ACTION="NOT_RUN"; COMMENT_TABLE_ACTION="NOT_RUN"
POLICY_KEYS_ACTION="NOT_RUN"
POLICY_KEYS_BEFORE_COUNT=""
MODE_VAL_BEFORE=""; VER_VAL_BEFORE=""
FN_COUNT_BEFORE=""; FN_COUNT_AFTER=""
FN_LIST_BEFORE=""; FN_LIST_AFTER=""
V1="NOT_RUN"; V2="NOT_RUN"; V3="NOT_RUN"; V4="NOT_RUN"
V5="NOT_RUN"; V6="NOT_RUN"; V7="NOT_RUN"; V8="NOT_RUN"
V9="NOT_RUN"; V10="NOT_RUN"; V11="NOT_RUN"; V12="NOT_RUN"
V13="NOT_RUN"; V14="NOT_RUN"

echo "=== P3B START $TS ==="

§1. Preflight

echo "=== PREFLIGHT ==="

# Gate 1–3: Tables must not exist, no similar tables
DRAFT_EXISTS=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND tablename='unit_edit_draft';" | tr -d ' ')
[ "$DRAFT_EXISTS" = "0" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: unit_edit_draft exists"; }

if [ -z "$PREFLIGHT_STATUS" ]; then
  COMMENT_EXISTS=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND tablename='unit_edit_comment';" | tr -d ' ')
  [ "$COMMENT_EXISTS" = "0" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: unit_edit_comment exists"; }
fi

if [ -z "$PREFLIGHT_STATUS" ]; then
  SIMILAR=$("${PSQL[@]}" -t -A -c "SELECT tablename FROM pg_tables WHERE schemaname='public' AND (tablename LIKE 'unit%proposal%' OR tablename LIKE 'unit%edit%');")
  [ -z "$SIMILAR" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: similar=$SIMILAR"; }
fi

# Gate 4: sort_order + index
if [ -z "$PREFLIGHT_STATUS" ]; then
  SORT_COL=$("${PSQL[@]}" -t -A -c "SELECT data_type FROM information_schema.columns WHERE table_schema='public' AND table_name='information_unit' AND column_name='sort_order';")
  if [ -n "$SORT_COL" ]; then
    [ "$SORT_COL" = "integer" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: sort_order type=$SORT_COL"; }
    SORT_ORDER_EXISTS="true"
    SORT_IDX=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_indexes WHERE schemaname='public' AND indexname='idx_iu_parent_sort';" | tr -d ' ')
    [ "$SORT_IDX" != "0" ] && SORT_ORDER_INDEX_EXISTS="true" || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: sort_order exists but idx_iu_parent_sort missing"; }
  else
    SORT_ORDER_EXISTS="false"
  fi
fi

# Gate 5: Schema sanity
if [ -z "$PREFLIGHT_STATUS" ]; then
  IU_COL_COUNT=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='information_unit';" | tr -d ' ')
  UV_COL_COUNT=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_version';" | tr -d ' ')
  EXPECTED_IU=15; [ "$SORT_ORDER_EXISTS" = "true" ] && EXPECTED_IU=16
  echo "IU_COLS=$IU_COL_COUNT(expect=$EXPECTED_IU) UV_COLS=$UV_COL_COUNT(expect=9)"
  [ "$IU_COL_COUNT" = "$EXPECTED_IU" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: IU drift"; }
  [ -z "$PREFLIGHT_STATUS" ] && [ "$UV_COL_COUNT" != "9" ] && { PREFLIGHT_STATUS="FAIL"; echo "FAIL: UV drift"; }
fi

# Gate 6: dot_config unique(key)
if [ -z "$PREFLIGHT_STATUS" ]; then
  KEY_UNIQUE=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_index i WHERE i.indrelid='dot_config'::regclass AND i.indisunique=true AND i.indnatts=1 AND (SELECT a.attname FROM pg_attribute a WHERE a.attrelid=i.indrelid AND a.attnum=i.indkey[0])='key';" | tr -d ' ')
  [ "$KEY_UNIQUE" != "0" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: no unique(key)"; }
fi

# Gate 7: Owner
if [ -z "$PREFLIGHT_STATUS" ]; then
  IU_OWNER=$("${PSQL[@]}" -t -A -c "SELECT tableowner FROM pg_tables WHERE schemaname='public' AND tablename='information_unit';")
  echo "IU_OWNER=$IU_OWNER"
  [ -n "$IU_OWNER" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: no IU owner"; }
fi

# Gate 8: P3A gateway
if [ -z "$PREFLIGHT_STATUS" ]; then
  ALLOW_LIST=$("${PSQL[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_create.gateway.allowed_marker_values';")
  [ "$ALLOW_LIST" = "fn_iu_create,fn_iu_apply_edit_draft" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: gateway=$ALLOW_LIST"; }
fi

# Gate 9: UV lifecycle convention
if [ -z "$PREFLIGHT_STATUS" ]; then
  UV_LIFECYCLE_CONVENTION=$("${PSQL[@]}" -t -A -c "
    SELECT prosrc FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid
    WHERE n.nspname='public' AND p.proname='fn_iu_create';
  " | grep -oP "lifecycle_status[^']*'[^']+'" | head -1 || true)
  echo "UV_LIFECYCLE=$UV_LIFECYCLE_CONVENTION"
fi

# Gate 10: Policy keys reconcile (capture before values for partial check)
if [ -z "$PREFLIGHT_STATUS" ]; then
  POLICY_KEYS_BEFORE_COUNT=$("${PSQL[@]}" -t -c "SELECT count(*) FROM dot_config WHERE key IN ('iu_edit.policy.default_mode','iu_edit.schema.version');" | tr -d ' ')
  MODE_VAL_BEFORE=$("${PSQL_NOSTOP[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_edit.policy.default_mode';" 2>/dev/null)
  VER_VAL_BEFORE=$("${PSQL_NOSTOP[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_edit.schema.version';" 2>/dev/null)
  echo "POLICY_BEFORE=$POLICY_KEYS_BEFORE_COUNT mode=$MODE_VAL_BEFORE ver=$VER_VAL_BEFORE"
  [ -z "$MODE_VAL_BEFORE" ] || [ "$MODE_VAL_BEFORE" = "auto_apply" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: mode=$MODE_VAL_BEFORE"; }
  [ -z "$VER_VAL_BEFORE" ] || [ "$VER_VAL_BEFORE" = "p3b-v1" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: ver=$VER_VAL_BEFORE"; }
fi

# Gate 11: Function count via SQL (not grep -c)
if [ -z "$PREFLIGHT_STATUS" ]; then
  FN_COUNT_BEFORE=$("${PSQL[@]}" -t -c "
    SELECT count(*) FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid
    WHERE n.nspname='public' AND (p.proname LIKE 'fn_iu_%edit%' OR p.proname LIKE 'fn_iu_%draft%' OR p.proname LIKE 'fn_iu_%comment%');
  " | tr -d ' ')
  FN_LIST_BEFORE=$("${PSQL[@]}" -t -A -c "
    SELECT p.proname FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid
    WHERE n.nspname='public' AND (p.proname LIKE 'fn_iu_%edit%' OR p.proname LIKE 'fn_iu_%draft%' OR p.proname LIKE 'fn_iu_%comment%') ORDER BY p.proname;
  ")
  echo "FN_BEFORE=$FN_COUNT_BEFORE list=$FN_LIST_BEFORE"
fi

# Gate 12: Counts
if [ -z "$PREFLIGHT_STATUS" ]; then
  IU_COUNT_BEFORE=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_unit;" | tr -d ' ')
  UV_COUNT_BEFORE=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_version;" | tr -d ' ')
  echo "IU=$IU_COUNT_BEFORE UV=$UV_COUNT_BEFORE"
fi

if [ -z "$PREFLIGHT_STATUS" ]; then PREFLIGHT_STATUS="PASS"; fi
echo "PREFLIGHT_STATUS=$PREFLIGHT_STATUS"
if [ "$PREFLIGHT_STATUS" != "PASS" ]; then
  DDL_STATUS="NOT_RUN"; PHASE_STATUS="FAIL"; P3C_READINESS="BLOCKED"
fi

§2. DDL (single transaction, owner GUC inside same session)

if [ "$PREFLIGHT_STATUS" = "PASS" ]; then
echo "=== DDL ==="

DDL_EXIT=0
"${PSQL[@]}" -v p3b_owner="$IU_OWNER" <<'DDL_SQL' || DDL_EXIT=$?
BEGIN;
SELECT set_config('app.p3b_owner', :'p3b_owner', true);

CREATE TABLE public.unit_edit_draft (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  unit_id uuid NOT NULL,
  canonical_address text NOT NULL,
  base_version_ref uuid NOT NULL,
  base_version_seq integer NOT NULL,
  base_content_hash text NOT NULL,
  draft_title text NULL,
  draft_body text NOT NULL,
  draft_content_hash text NOT NULL,
  draft_status text NOT NULL DEFAULT 'open',
  created_by text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  applied_by text NULL,
  applied_at timestamptz NULL,
  applied_version_ref uuid NULL,
  stale_at timestamptz NULL,
  reason text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  CONSTRAINT pk_unit_edit_draft PRIMARY KEY (id),
  CONSTRAINT fk_ued_unit FOREIGN KEY (unit_id) REFERENCES public.information_unit(id),
  CONSTRAINT fk_ued_base_version FOREIGN KEY (base_version_ref) REFERENCES public.unit_version(id),
  CONSTRAINT fk_ued_applied_version FOREIGN KEY (applied_version_ref) REFERENCES public.unit_version(id),
  CONSTRAINT chk_ued_status CHECK (draft_status IN ('open','applied','stale_base','withdrawn')),
  CONSTRAINT chk_ued_created_by CHECK (btrim(created_by) <> ''),
  CONSTRAINT chk_ued_body CHECK (btrim(draft_body) <> ''),
  CONSTRAINT chk_ued_title CHECK (draft_title IS NULL OR btrim(draft_title) <> ''),
  CONSTRAINT chk_ued_address CHECK (btrim(canonical_address) <> ''),
  CONSTRAINT chk_ued_base_seq CHECK (base_version_seq > 0),
  CONSTRAINT chk_ued_base_hash CHECK (btrim(base_content_hash) <> ''),
  CONSTRAINT chk_ued_draft_hash CHECK (btrim(draft_content_hash) <> '')
);
CREATE INDEX idx_ued_unit_status_created ON public.unit_edit_draft (unit_id, draft_status, created_at DESC);
CREATE INDEX idx_ued_address_status_created ON public.unit_edit_draft (canonical_address, draft_status, created_at DESC);
CREATE INDEX idx_ued_base_version ON public.unit_edit_draft (base_version_ref);
CREATE INDEX idx_ued_applied_version ON public.unit_edit_draft (applied_version_ref) WHERE applied_version_ref IS NOT NULL;

CREATE TABLE public.unit_edit_comment (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  draft_id uuid NOT NULL,
  unit_id uuid NOT NULL,
  author_ref text NOT NULL,
  author_type text NOT NULL DEFAULT 'agent',
  comment_body text NOT NULL,
  comment_kind text NOT NULL DEFAULT 'general',
  created_at timestamptz NOT NULL DEFAULT now(),
  resolved_at timestamptz NULL,
  resolved_by text NULL,
  parent_comment_id uuid NULL,
  target_path text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  CONSTRAINT pk_unit_edit_comment PRIMARY KEY (id),
  CONSTRAINT fk_uec_draft FOREIGN KEY (draft_id) REFERENCES public.unit_edit_draft(id),
  CONSTRAINT fk_uec_unit FOREIGN KEY (unit_id) REFERENCES public.information_unit(id),
  CONSTRAINT fk_uec_parent FOREIGN KEY (parent_comment_id) REFERENCES public.unit_edit_comment(id),
  CONSTRAINT chk_uec_author_type CHECK (author_type IN ('user','agent','system')),
  CONSTRAINT chk_uec_kind CHECK (comment_kind IN ('general','review','change_request','approval','system')),
  CONSTRAINT chk_uec_author CHECK (btrim(author_ref) <> ''),
  CONSTRAINT chk_uec_body CHECK (btrim(comment_body) <> ''),
  CONSTRAINT chk_uec_resolved CHECK (resolved_by IS NULL OR btrim(resolved_by) <> '')
);
CREATE INDEX idx_uec_draft_created ON public.unit_edit_comment (draft_id, created_at);
CREATE INDEX idx_uec_unit_created ON public.unit_edit_comment (unit_id, created_at);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema='public' AND table_name='information_unit' AND column_name='sort_order'
  ) THEN
    ALTER TABLE public.information_unit ADD COLUMN sort_order integer NULL;
    CREATE INDEX idx_iu_parent_sort ON public.information_unit (parent_or_container_ref, sort_order) WHERE parent_or_container_ref IS NOT NULL;
  END IF;
END;
$$;

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;
$$;

REVOKE INSERT, UPDATE, DELETE ON public.unit_edit_draft FROM PUBLIC;
REVOKE INSERT, UPDATE, DELETE ON public.unit_edit_comment FROM PUBLIC;

INSERT INTO dot_config (key, value) VALUES
  ('iu_edit.policy.default_mode', 'auto_apply'),
  ('iu_edit.schema.version', 'p3b-v1')
ON CONFLICT (key) DO NOTHING;

COMMIT;
DDL_SQL

echo "DDL_EXIT=$DDL_EXIT"
if [ "$DDL_EXIT" != "0" ]; then
  DDL_STATUS="FAIL"
  P_DRAFT=$("${PSQL_NOSTOP[@]}" -t -c "SELECT count(*) FROM pg_tables WHERE tablename='unit_edit_draft';" 2>/dev/null | tr -d ' ')
  P_COMMENT=$("${PSQL_NOSTOP[@]}" -t -c "SELECT count(*) FROM pg_tables WHERE tablename='unit_edit_comment';" 2>/dev/null | tr -d ' ')
  P_SORT=$("${PSQL_NOSTOP[@]}" -t -A -c "SELECT data_type FROM information_schema.columns WHERE table_name='information_unit' AND column_name='sort_order';" 2>/dev/null)
  P_MODE=$("${PSQL_NOSTOP[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_edit.policy.default_mode';" 2>/dev/null)
  P_VER=$("${PSQL_NOSTOP[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_edit.schema.version';" 2>/dev/null)
  echo "PARTIAL: draft=$P_DRAFT comment=$P_COMMENT sort=$P_SORT mode=$P_MODE ver=$P_VER"
  PARTIAL_NEW="false"
  [ "$P_DRAFT" != "0" ] && PARTIAL_NEW="true"
  [ "$P_COMMENT" != "0" ] && PARTIAL_NEW="true"
  [ "$SORT_ORDER_EXISTS" = "false" ] && [ -n "$P_SORT" ] && PARTIAL_NEW="true"
  [ -z "$MODE_VAL_BEFORE" ] && [ -n "$P_MODE" ] && PARTIAL_NEW="true"
  [ -z "$VER_VAL_BEFORE" ] && [ -n "$P_VER" ] && PARTIAL_NEW="true"
  [ "$PARTIAL_NEW" = "true" ] && PHASE_STATUS="CRITICAL" || PHASE_STATUS="FAIL"
else
  DDL_STATUS="OK"
  DRAFT_TABLE_ACTION="CREATED"
  COMMENT_TABLE_ACTION="CREATED"
  if [ "$SORT_ORDER_EXISTS" = "true" ]; then
    SORT_ORDER_ACTION="SKIPPED_ALREADY_EXISTS_WITH_INDEX"
  else
    SORT_ORDER_ACTION="ADDED"
  fi
  POLICY_KEYS_AFTER_COUNT=$("${PSQL[@]}" -t -c "SELECT count(*) FROM dot_config WHERE key IN ('iu_edit.policy.default_mode','iu_edit.schema.version');" | tr -d ' ')
  if [ "$POLICY_KEYS_BEFORE_COUNT" = "0" ] && [ "$POLICY_KEYS_AFTER_COUNT" = "2" ]; then POLICY_KEYS_ACTION="SEEDED"
  elif [ "$POLICY_KEYS_BEFORE_COUNT" = "2" ]; then POLICY_KEYS_ACTION="SKIPPED_EXISTING"
  elif [ "$POLICY_KEYS_BEFORE_COUNT" = "1" ] && [ "$POLICY_KEYS_AFTER_COUNT" = "2" ]; then POLICY_KEYS_ACTION="PARTIAL_SEEDED"
  else POLICY_KEYS_ACTION="SEEDED_${POLICY_KEYS_BEFORE_COUNT}_TO_${POLICY_KEYS_AFTER_COUNT}"; fi
fi

fi # PREFLIGHT

§3. Verification

if [ "$DDL_STATUS" = "OK" ]; then
echo "=== VERIFICATION ==="

# V1–V3: Existence
V1=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND tablename='unit_edit_draft';" | tr -d ' ')
[ "$V1" = "1" ] && V1="PASS" || { V1="FAIL"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }
V2=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND tablename='unit_edit_comment';" | tr -d ' ')
[ "$V2" = "1" ] && V2="PASS" || { V2="FAIL"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }
V3=$("${PSQL[@]}" -t -A -c "SELECT data_type FROM information_schema.columns WHERE table_schema='public' AND table_name='information_unit' AND column_name='sort_order';")
[ "$V3" = "integer" ] && V3="PASS" || { V3="FAIL=$V3"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }

# V4–V5: Columns by name/type
V4=$("${PSQL[@]}" -t -c "
  WITH expected(col,typ) AS (VALUES
    ('id','uuid'),('unit_id','uuid'),('canonical_address','text'),('base_version_ref','uuid'),('base_version_seq','integer'),('base_content_hash','text'),('draft_title','text'),('draft_body','text'),('draft_content_hash','text'),('draft_status','text'),('created_by','text'),('created_at','timestamp with time zone'),('applied_by','text'),('applied_at','timestamp with time zone'),('applied_version_ref','uuid'),('stale_at','timestamp with time zone'),('reason','text'),('metadata','jsonb')
  ), actual AS (SELECT column_name col, data_type typ FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_edit_draft')
  SELECT count(*) FROM expected e LEFT JOIN actual a ON e.col=a.col AND e.typ=a.typ WHERE a.col IS NULL;
" | tr -d ' ')
[ "$V4" = "0" ] && V4="PASS" || { V4="FAIL=$V4"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }

V5=$("${PSQL[@]}" -t -c "
  WITH expected(col,typ) AS (VALUES
    ('id','uuid'),('draft_id','uuid'),('unit_id','uuid'),('author_ref','text'),('author_type','text'),('comment_body','text'),('comment_kind','text'),('created_at','timestamp with time zone'),('resolved_at','timestamp with time zone'),('resolved_by','text'),('parent_comment_id','uuid'),('target_path','text'),('metadata','jsonb')
  ), actual AS (SELECT column_name col, data_type typ FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_edit_comment')
  SELECT count(*) FROM expected e LEFT JOIN actual a ON e.col=a.col AND e.typ=a.typ WHERE a.col IS NULL;
" | tr -d ' ')
[ "$V5" = "0" ] && V5="PASS" || { V5="FAIL=$V5"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }

# V6–V7: Constraints by name
V6=$("${PSQL[@]}" -t -c "
  WITH expected(cname) AS (VALUES ('chk_ued_status'),('chk_ued_created_by'),('chk_ued_body'),('chk_ued_title'),('chk_ued_address'),('chk_ued_base_seq'),('chk_ued_base_hash'),('chk_ued_draft_hash'),('fk_ued_unit'),('fk_ued_base_version'),('fk_ued_applied_version'))
  SELECT count(*) FROM expected e LEFT JOIN information_schema.table_constraints tc ON tc.constraint_name=e.cname AND tc.table_schema='public' AND tc.table_name='unit_edit_draft' WHERE tc.constraint_name IS NULL;
" | tr -d ' ')
[ "$V6" = "0" ] && V6="PASS" || { V6="FAIL=$V6"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }

V7=$("${PSQL[@]}" -t -c "
  WITH expected(cname) AS (VALUES ('chk_uec_author_type'),('chk_uec_kind'),('chk_uec_author'),('chk_uec_body'),('chk_uec_resolved'),('fk_uec_draft'),('fk_uec_unit'),('fk_uec_parent'))
  SELECT count(*) FROM expected e LEFT JOIN information_schema.table_constraints tc ON tc.constraint_name=e.cname AND tc.table_schema='public' AND tc.table_name='unit_edit_comment' WHERE tc.constraint_name IS NULL;
" | tr -d ' ')
[ "$V7" = "0" ] && V7="PASS" || { V7="FAIL=$V7"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }

# V8: Indexes by name
V8_MM=$("${PSQL[@]}" -t -c "
  WITH expected(iname) AS (VALUES ('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'))
  SELECT count(*) FROM expected e LEFT JOIN pg_indexes pi ON pi.indexname=e.iname AND pi.schemaname='public' WHERE pi.indexname IS NULL;
" | tr -d ' ')
if [ "$SORT_ORDER_ACTION" = "ADDED" ]; then
  SORT_V=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_indexes WHERE schemaname='public' AND indexname='idx_iu_parent_sort';" | tr -d ' ')
  [ "$SORT_V" = "1" ] || V8_MM=$((V8_MM+1))
fi
[ "$V8_MM" = "0" ] && V8="PASS" || { V8="FAIL=$V8_MM"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }

# V9: JSONB defaults
V9_D=$("${PSQL[@]}" -t -A -c "SELECT column_default FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_edit_draft' AND column_name='metadata';")
V9_C=$("${PSQL[@]}" -t -A -c "SELECT column_default FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_edit_comment' AND column_name='metadata';")
if echo "$V9_D" | grep -q "'{}'" && echo "$V9_C" | grep -q "'{}'"; then V9="PASS"; else V9="FAIL"; VERIFY_FAIL=$((VERIFY_FAIL+1)); fi

# V10: IU/UV counts unchanged
IU_COUNT_AFTER=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_unit;" | tr -d ' ')
UV_COUNT_AFTER=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_version;" | tr -d ' ')
if [ "$IU_COUNT_AFTER" = "$IU_COUNT_BEFORE" ] && [ "$UV_COUNT_AFTER" = "$UV_COUNT_BEFORE" ]; then V10="PASS"; else V10="FAIL"; VERIFY_FAIL=$((VERIFY_FAIL+1)); fi

# V11: P3A gateway intact
V11=$("${PSQL[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_create.gateway.allowed_marker_values';")
[ "$V11" = "fn_iu_create,fn_iu_apply_edit_draft" ] && V11="PASS" || { V11="FAIL"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }

# V12: No new functions (SQL count)
FN_COUNT_AFTER=$("${PSQL[@]}" -t -c "
  SELECT count(*) FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid
  WHERE n.nspname='public' AND (p.proname LIKE 'fn_iu_%edit%' OR p.proname LIKE 'fn_iu_%draft%' OR p.proname LIKE 'fn_iu_%comment%');
" | tr -d ' ')
FN_LIST_AFTER=$("${PSQL[@]}" -t -A -c "
  SELECT p.proname FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid
  WHERE n.nspname='public' AND (p.proname LIKE 'fn_iu_%edit%' OR p.proname LIKE 'fn_iu_%draft%' OR p.proname LIKE 'fn_iu_%comment%') ORDER BY p.proname;
")
if [ "$FN_COUNT_AFTER" = "$FN_COUNT_BEFORE" ]; then V12="PASS"; else V12="FAIL_${FN_COUNT_BEFORE}→${FN_COUNT_AFTER}_list=${FN_LIST_AFTER}"; VERIFY_FAIL=$((VERIFY_FAIL+1)); fi

# V13: Owner + PUBLIC no write
V13="PASS"
D_OWN=$("${PSQL[@]}" -t -A -c "SELECT tableowner FROM pg_tables WHERE tablename='unit_edit_draft';")
C_OWN=$("${PSQL[@]}" -t -A -c "SELECT tableowner FROM pg_tables WHERE tablename='unit_edit_comment';")
[ "$D_OWN" = "$IU_OWNER" ] || { V13="FAIL_draft=$D_OWN"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }
[ "$C_OWN" = "$IU_OWNER" ] || { V13="FAIL_comment=$C_OWN"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }
PUB_W=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_schema.table_privileges WHERE table_schema='public' AND table_name IN ('unit_edit_draft','unit_edit_comment') AND grantee='PUBLIC' AND privilege_type IN ('INSERT','UPDATE','DELETE');" | tr -d ' ')
[ "$PUB_W" = "0" ] || { V13="FAIL_pub=$PUB_W"; VERIFY_FAIL=$((VERIFY_FAIL+1)); }

# V14: Policy keys correct
V14_MODE=$("${PSQL[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_edit.policy.default_mode';")
V14_VER=$("${PSQL[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_edit.schema.version';")
if [ "$V14_MODE" = "auto_apply" ] && [ "$V14_VER" = "p3b-v1" ]; then V14="PASS"; else V14="FAIL_mode=${V14_MODE}_ver=${V14_VER}"; VERIFY_FAIL=$((VERIFY_FAIL+1)); fi

echo "V1=$V1 V2=$V2 V3=$V3 V4=$V4 V5=$V5 V6=$V6 V7=$V7 V8=$V8 V9=$V9 V10=$V10 V11=$V11 V12=$V12 V13=$V13 V14=$V14"
echo "VERIFY_FAIL=$VERIFY_FAIL"

# Post-DDL detail output (read-only, for Opus/GPT review)
echo "=== DETAIL OUTPUT ==="
echo "--- unit_edit_draft columns ---"
"${PSQL_NOSTOP[@]}" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_edit_draft' ORDER BY ordinal_position;"
echo "--- unit_edit_comment columns ---"
"${PSQL_NOSTOP[@]}" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_edit_comment' ORDER BY ordinal_position;"
echo "--- constraints ---"
"${PSQL_NOSTOP[@]}" -c "SELECT table_name, constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_schema='public' AND table_name IN ('unit_edit_draft','unit_edit_comment') ORDER BY table_name, constraint_type, constraint_name;"
echo "--- indexes ---"
"${PSQL_NOSTOP[@]}" -c "SELECT tablename, indexname FROM pg_indexes WHERE schemaname='public' AND tablename IN ('unit_edit_draft','unit_edit_comment') ORDER BY tablename, indexname;"
echo "--- iu_edit.% dot_config ---"
"${PSQL_NOSTOP[@]}" -c "SELECT key, value FROM dot_config WHERE key LIKE 'iu_edit.%' ORDER BY key;"
echo "--- grants ---"
"${PSQL_NOSTOP[@]}" -c "SELECT table_name, grantee, privilege_type FROM information_schema.table_privileges WHERE table_schema='public' AND table_name IN ('unit_edit_draft','unit_edit_comment') ORDER BY table_name, grantee;"

fi # DDL OK

§4. Report

echo "=== FINAL VERDICT ==="
if [ "$PREFLIGHT_STATUS" != "PASS" ]; then
  PHASE_STATUS="FAIL"; P3C_READINESS="BLOCKED"
elif [ "$DDL_STATUS" != "OK" ]; then
  PHASE_STATUS="${PHASE_STATUS:-FAIL}"; P3C_READINESS="BLOCKED"
elif [ "$VERIFY_FAIL" = "0" ]; then
  PHASE_STATUS="PASS"; P3C_READINESS="READY"
else
  PHASE_STATUS="FAIL"; P3C_READINESS="BLOCKED"
fi

IU_UV_ROWS_UNCHANGED="UNKNOWN"
[ "$V10" = "PASS" ] && IU_UV_ROWS_UNCHANGED="PASS" || IU_UV_ROWS_UNCHANGED="FAIL"

echo "phase_status=$PHASE_STATUS"
echo "p3c_readiness=$P3C_READINESS"
echo "preflight=$PREFLIGHT_STATUS ddl=$DDL_STATUS verify_fail=$VERIFY_FAIL"
echo "draft_action=$DRAFT_TABLE_ACTION comment_action=$COMMENT_TABLE_ACTION"
echo "sort_order_action=$SORT_ORDER_ACTION policy_keys_action=$POLICY_KEYS_ACTION"
echo "iu_uv_rows_unchanged=$IU_UV_ROWS_UNCHANGED"
echo "iu=$IU_COUNT_BEFORE→$IU_COUNT_AFTER uv=$UV_COUNT_BEFORE→$UV_COUNT_AFTER"
echo "uv_lifecycle=$UV_LIFECYCLE_CONVENTION owner=$IU_OWNER"
echo "V1=$V1 V2=$V2 V3=$V3 V4=$V4 V5=$V5 V6=$V6 V7=$V7 V8=$V8 V9=$V9 V10=$V10 V11=$V11 V12=$V12 V13=$V13 V14=$V14"
echo "LOG=$LOG"

echo ""
echo "=== AGENT: UPLOAD REPORT ==="
echo "Path: knowledge/dev/laws/dieu44-trien-khai/reports/23-p3b-iu-edit-draft-schema-report.md"
echo "Include: full verdict, V1-V14, detail output (columns/constraints/indexes/grants), actions, lifecycle."
echo "Upload even on FAIL/CRITICAL."

Hard Boundaries

  • ❌ No functions / No trigger changes / No gateway changes
  • ❌ No IU/UV row mutations / No vector / No cleanup / No retry

23-P3B Prompt rev4 | 2026-05-07 | 5 final fixes | Chờ GPT/User final review