Incomex AI Portal
KB-466E rev 7

23-P3C1 — Edit Draft Safe Functions — Execution Prompt (rev7)

31 min read Revision 7
pack-23p3c1promptrev7functionssafeshell-fixed

23-P3C1 — Edit Draft Safe Functions — Execution Prompt (rev7)

Date: 2026-05-07 Status: PROMPT rev7 — chờ GPT/User final review. CHƯA dispatch. Report: knowledge/dev/laws/dieu44-trien-khai/reports/23-p3c1-iu-edit-draft-safe-functions-report.md Companion: knowledge/dev/laws/dieu44-trien-khai/design/23-p3c1-ux-state-foundation-notes.md (rev2) Rev7: shell comparison spaces, psql vars in T19/T20, report UX hooks.

§0. Pre-read

  1. design/23-p3-iu-proposal-merge-implementation-design.md (rev5)
  2. reports/23-p3a-iu-gateway-allow-list-patch-report.md
  3. reports/23-p3b-iu-edit-draft-schema-report.md
  4. design/23-p3c1-ux-state-foundation-notes.md (UX state hooks)

Setup

#!/usr/bin/env bash
# All shell test comparisons MUST use spaces around '=':
#   CORRECT: [ "$A" = "$B" ]
#   WRONG:   [ "$A"="$B" ]    ← always true, silent false PASS
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-p3c1.${TS}.log"
exec > >(tee -a "$LOG") 2>&1
PREFLIGHT_STATUS=""; FN_STATUS="NOT_RUN"; TEST_FAIL=0
PHASE_STATUS=""; P3C2_READINESS="BLOCKED"
IU_BEFORE=""; UV_BEFORE=""; DRAFT_BEFORE=""; COMMENT_BEFORE=""
IU_NOW=""; UV_NOW=""; DRAFT_NOW=""; COMMENT_NOW=""
FN_OWNER=""; FN_GRANTEES=""; TEST_ADDR_A=""; TEST_ADDR_B=""
DRAFT_A_ID=""; DRAFT_B_ID=""; UV_LC=""
DROP_FAIL="0"; CLEANUP_STATUS="NOT_RUN"
echo "=== P3C1 START $TS ==="

§1. Preflight

echo "=== PREFLIGHT ==="
for TBL in unit_edit_draft unit_edit_comment; do
  E=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND tablename='$TBL';" | tr -d ' ')
  [ "$E" = "1" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: $TBL"; }
done
if [ -z "$PREFLIGHT_STATUS" ]; then
  CC=$("${PSQL[@]}" -t -c "
    WITH req(t,c,y) AS (VALUES ('information_unit','id','uuid'),('information_unit','canonical_address','text'),('information_unit','version_anchor_ref','uuid'),('information_unit','identity_profile','jsonb'),('unit_version','id','uuid'),('unit_version','version_seq','integer'),('unit_version','body','text'),('unit_version','content_hash','text'),('unit_edit_draft','id','uuid'),('unit_edit_draft','unit_id','uuid'),('unit_edit_draft','canonical_address','text'),('unit_edit_draft','draft_status','text'),('unit_edit_draft','draft_body','text'),('unit_edit_draft','draft_content_hash','text'),('unit_edit_draft','base_version_ref','uuid'),('unit_edit_draft','base_version_seq','integer'),('unit_edit_draft','base_content_hash','text'),('unit_edit_draft','created_by','text'),('unit_edit_comment','id','uuid'),('unit_edit_comment','draft_id','uuid'),('unit_edit_comment','unit_id','uuid'),('unit_edit_comment','author_ref','text'),('unit_edit_comment','comment_body','text'),('unit_edit_comment','comment_kind','text')),
    act AS (SELECT table_name t,column_name c,data_type y FROM information_schema.columns WHERE table_schema='public')
    SELECT count(*) FROM req r LEFT JOIN act a ON r.t=a.t AND r.c=a.c AND r.y=a.y WHERE a.c IS NULL;" | tr -d ' ')
  [ "$CC" = "0" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: cols=$CC"; }
fi
if [ -z "$PREFLIGHT_STATUS" ]; then
  GW=$("${PSQL[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_create.gateway.allowed_marker_values';")
  [ "$GW" = "fn_iu_create,fn_iu_apply_edit_draft" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: gw"; }
fi
if [ -z "$PREFLIGHT_STATUS" ]; then
  CH=$("${PSQL[@]}" -t -A -c "SELECT to_regprocedure('public.fn_content_hash(text)');")
  [ -n "$CH" ] && [ "$CH" != "-" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: hash"; }
fi
if [ -z "$PREFLIGHT_STATUS" ]; then
  for FN in fn_iu_edit_plan fn_iu_create_edit_draft fn_iu_comment_edit_draft fn_iu_comment fn_iu_apply_edit_draft fn_iu_edit; do
    FC=$("${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='$FN';" | tr -d ' ')
    [ "$FC" = "0" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: $FN exists"; }
  done
fi
if [ -z "$PREFLIGHT_STATUS" ]; then
  FN_OWNER=$("${PSQL[@]}" -t -A -c "SELECT proowner::regrole FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid WHERE n.nspname='public' AND p.proname='fn_iu_create';")
  [ -n "$FN_OWNER" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: owner"; }
  FN_GRANTEES=$("${PSQL_NOSTOP[@]}" -t -A -c "SELECT string_agg(DISTINCT x.grantee::regrole::text,',') FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid LEFT JOIN LATERAL aclexplode(p.proacl) x ON true WHERE n.nspname='public' AND p.proname='fn_iu_create' AND x.privilege_type='EXECUTE' AND x.grantee!=0;" 2>/dev/null)
  [ -z "$FN_GRANTEES" ] && FN_GRANTEES="$FN_OWNER"
  echo "OWNER=$FN_OWNER GRANTEES=$FN_GRANTEES"
fi
if [ -z "$PREFLIGHT_STATUS" ]; then
  UV_LC=$("${PSQL[@]}" -t -A -c "SELECT DISTINCT lifecycle_status FROM unit_version;")
  echo "UV_LC=$UV_LC"
fi
if [ -z "$PREFLIGHT_STATUS" ]; then
  TEST_ADDR_A=$("${PSQL[@]}" -t -A -c "SELECT canonical_address FROM information_unit ORDER BY canonical_address LIMIT 1;")
  TEST_ADDR_B=$("${PSQL[@]}" -v addr_a="$TEST_ADDR_A" -t -A -c "SELECT iu.canonical_address FROM information_unit iu WHERE iu.canonical_address!=:'addr_a' AND NOT EXISTS(SELECT 1 FROM unit_edit_draft d WHERE d.unit_id=iu.id AND d.draft_status='open') ORDER BY iu.canonical_address LIMIT 1;")
  echo "A=$TEST_ADDR_A B=$TEST_ADDR_B"
  [ -n "$TEST_ADDR_A" ] && [ -n "$TEST_ADDR_B" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: addrs"; }
fi
if [ -z "$PREFLIGHT_STATUS" ]; then
  IU_BEFORE=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_unit;" | tr -d ' ')
  UV_BEFORE=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_version;" | tr -d ' ')
  DRAFT_BEFORE=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_edit_draft;" | tr -d ' ')
  COMMENT_BEFORE=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_edit_comment;" | tr -d ' ')
  echo "IU=$IU_BEFORE UV=$UV_BEFORE D=$DRAFT_BEFORE C=$COMMENT_BEFORE"
fi
if [ -z "$PREFLIGHT_STATUS" ]; then PREFLIGHT_STATUS="PASS"; fi
echo "PREFLIGHT=$PREFLIGHT_STATUS"
if [ "$PREFLIGHT_STATUS" != "PASS" ]; then FN_STATUS="NOT_RUN"; PHASE_STATUS="FAIL"; P3C2_READINESS="BLOCKED"; fi

§2. Create Functions

if [ "$PREFLIGHT_STATUS" = "PASS" ]; then
echo "=== CREATE ==="
FN_EXIT=0
"${PSQL[@]}" -v fn_owner="$FN_OWNER" -v fn_grantees="$FN_GRANTEES" <<'FNSQL' || FN_EXIT=$?
BEGIN;
SELECT set_config('app.p3c1_fn_owner',:'fn_owner',true);
SELECT set_config('app.p3c1_grantees',:'fn_grantees',true);

CREATE FUNCTION public.fn_iu_edit_plan(p_address text,p_body text,p_actor text,p_title text DEFAULT NULL) RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path=pg_catalog,public AS $fn$
DECLARE v_iu record;v_uv record;v_hash text;v_policy text;v_open int;v_cur_title text;
BEGIN
  IF btrim(COALESCE(p_address,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','address','guidance','Address required.','next_action','fix_input'); END IF;
  IF btrim(COALESCE(p_body,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','body','guidance','Body required.','next_action','fix_input'); END IF;
  IF btrim(COALESCE(p_actor,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','actor','guidance','Actor required.','next_action','fix_input'); END IF;
  IF p_title IS NOT NULL AND btrim(p_title)='' THEN RETURN jsonb_build_object('status','invalid_input','field','title','guidance','Title non-empty or NULL.','next_action','fix_input'); END IF;
  SELECT * INTO v_iu FROM public.information_unit WHERE canonical_address=btrim(p_address);
  IF NOT FOUND THEN RETURN jsonb_build_object('status','iu_not_found','address',btrim(p_address),'guidance','Check canonical_address.','next_action','verify_address'); END IF;
  IF v_iu.version_anchor_ref IS NULL THEN RETURN jsonb_build_object('status','no_head_version','unit_id',v_iu.id,'guidance','Use fn_iu_create first.','next_action','fn_iu_create'); END IF;
  SELECT * INTO v_uv FROM public.unit_version WHERE id=v_iu.version_anchor_ref;
  v_hash:=public.fn_content_hash(p_body);v_cur_title:=v_iu.identity_profile->>'title';
  IF v_hash=v_uv.content_hash AND(p_title IS NULL OR p_title=v_cur_title) THEN RETURN jsonb_build_object('status','no_change','guidance','Body and title identical.','next_action','none'); END IF;
  SELECT count(*) INTO v_open FROM public.unit_edit_draft WHERE unit_id=v_iu.id AND draft_status='open';
  v_policy:=COALESCE(v_iu.identity_profile->>'edit_policy',(SELECT value FROM public.dot_config WHERE key='iu_edit.policy.default_mode'),'auto_apply');
  RETURN jsonb_build_object('status','plan_ok','unit_id',v_iu.id,'current_version_seq',v_uv.version_seq,'base_content_hash',v_uv.content_hash,'draft_content_hash',v_hash,'open_draft_count',v_open,'policy',v_policy,'auto_apply_eligible',(v_policy='auto_apply'),'guidance','Ready.','next_action','fn_iu_create_edit_draft');
END;$fn$;

CREATE FUNCTION public.fn_iu_create_edit_draft(p_address text,p_body text,p_actor text,p_reason text DEFAULT NULL,p_title text DEFAULT NULL) RETURNS jsonb LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path=pg_catalog,public AS $fn$
DECLARE v_iu record;v_uv record;v_hash text;v_draft_id uuid;v_cur_title text;
BEGIN
  IF btrim(COALESCE(p_address,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','address','guidance','Address required.','next_action','fix_input'); END IF;
  IF btrim(COALESCE(p_body,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','body','guidance','Body required.','next_action','fix_input'); END IF;
  IF btrim(COALESCE(p_actor,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','actor','guidance','Actor required.','next_action','fix_input'); END IF;
  IF p_title IS NOT NULL AND btrim(p_title)='' THEN RETURN jsonb_build_object('status','invalid_input','field','title','guidance','Title non-empty or NULL.','next_action','fix_input'); END IF;
  SELECT * INTO v_iu FROM public.information_unit WHERE canonical_address=btrim(p_address);
  IF NOT FOUND THEN RETURN jsonb_build_object('status','iu_not_found','address',btrim(p_address),'guidance','Check canonical_address.','next_action','verify_address'); END IF;
  IF v_iu.version_anchor_ref IS NULL THEN RETURN jsonb_build_object('status','no_head_version','unit_id',v_iu.id,'guidance','Use fn_iu_create first.','next_action','fn_iu_create'); END IF;
  SELECT * INTO v_uv FROM public.unit_version WHERE id=v_iu.version_anchor_ref;
  v_hash:=public.fn_content_hash(p_body);v_cur_title:=v_iu.identity_profile->>'title';
  IF v_hash=v_uv.content_hash AND(p_title IS NULL OR p_title=v_cur_title) THEN RETURN jsonb_build_object('status','no_change','guidance','Identical.','next_action','none'); END IF;
  INSERT INTO public.unit_edit_draft(unit_id,canonical_address,base_version_ref,base_version_seq,base_content_hash,draft_body,draft_content_hash,draft_title,draft_status,created_by,reason)
  VALUES(v_iu.id,v_iu.canonical_address,v_uv.id,v_uv.version_seq,v_uv.content_hash,p_body,v_hash,p_title,'open',btrim(p_actor),p_reason) RETURNING id INTO v_draft_id;
  RETURN jsonb_build_object('status','draft_created','draft_id',v_draft_id,'unit_id',v_iu.id,'canonical_address',v_iu.canonical_address,'base_version_seq',v_uv.version_seq,'guidance','To apply: fn_iu_apply_edit_draft(draft_id,actor).','next_action','fn_iu_apply_edit_draft');
END;$fn$;

CREATE FUNCTION public.fn_iu_comment_edit_draft(p_draft_id uuid,p_author text,p_body text,p_kind text DEFAULT 'general',p_author_type text DEFAULT 'agent') RETURNS jsonb LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path=pg_catalog,public AS $fn$
DECLARE v_draft record;v_comment_id uuid;
BEGIN
  IF p_draft_id IS NULL THEN RETURN jsonb_build_object('status','invalid_input','field','draft_id','guidance','Required.','next_action','fix_input'); END IF;
  IF btrim(COALESCE(p_author,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','author','guidance','Required.','next_action','fix_input'); END IF;
  IF btrim(COALESCE(p_body,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','body','guidance','Required.','next_action','fix_input'); END IF;
  IF p_kind NOT IN('general','review','change_request','approval','system') THEN RETURN jsonb_build_object('status','invalid_input','field','kind','guidance','general/review/change_request/approval/system.','next_action','fix_input'); END IF;
  IF p_author_type NOT IN('user','agent','system') THEN RETURN jsonb_build_object('status','invalid_input','field','author_type','guidance','user/agent/system.','next_action','fix_input'); END IF;
  SELECT * INTO v_draft FROM public.unit_edit_draft WHERE id=p_draft_id;
  IF NOT FOUND THEN RETURN jsonb_build_object('status','draft_not_found','draft_id',p_draft_id,'guidance','Check draft_id.','next_action','verify_draft_id'); END IF;
  INSERT INTO public.unit_edit_comment(draft_id,unit_id,author_ref,author_type,comment_body,comment_kind)
  VALUES(p_draft_id,v_draft.unit_id,btrim(p_author),p_author_type,p_body,p_kind) RETURNING id INTO v_comment_id;
  RETURN jsonb_build_object('status','comment_added','comment_id',v_comment_id,'draft_id',p_draft_id,'unit_id',v_draft.unit_id);
END;$fn$;

CREATE FUNCTION public.fn_iu_comment(p_address text,p_author text,p_body text,p_kind text DEFAULT 'general',p_author_type text DEFAULT 'agent',p_context jsonb DEFAULT '{}'::jsonb) RETURNS jsonb LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path=pg_catalog,public AS $fn$
DECLARE v_iu record;v_draft record;v_drafts jsonb;v_open_count int;v_comment_id uuid;v_target_id uuid;v_selected_by text;v_ctx_draft text;v_ctx_select text;
BEGIN
  IF btrim(COALESCE(p_address,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','address','guidance','Required.','next_action','fix_input'); END IF;
  IF btrim(COALESCE(p_author,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','author','guidance','Required.','next_action','fix_input'); END IF;
  IF btrim(COALESCE(p_body,''))='' THEN RETURN jsonb_build_object('status','invalid_input','field','body','guidance','Required.','next_action','fix_input'); END IF;
  IF p_kind NOT IN('general','review','change_request','approval','system') THEN RETURN jsonb_build_object('status','invalid_input','field','kind','guidance','general/review/change_request/approval/system.','next_action','fix_input'); END IF;
  IF p_author_type NOT IN('user','agent','system') THEN RETURN jsonb_build_object('status','invalid_input','field','author_type','guidance','user/agent/system.','next_action','fix_input'); END IF;
  SELECT * INTO v_iu FROM public.information_unit WHERE canonical_address=btrim(p_address);
  IF NOT FOUND THEN RETURN jsonb_build_object('status','iu_not_found','address',btrim(p_address),'guidance','Check address.','next_action','verify_address'); END IF;
  v_ctx_draft:=p_context->>'draft_id';v_ctx_select:=p_context->>'select';
  IF v_ctx_draft IS NOT NULL THEN
    BEGIN v_target_id:=v_ctx_draft::uuid;EXCEPTION WHEN invalid_text_representation THEN RETURN jsonb_build_object('status','invalid_input','field','context.draft_id','guidance','Not UUID.','next_action','fix_input');END;
    SELECT * INTO v_draft FROM public.unit_edit_draft WHERE id=v_target_id AND unit_id=v_iu.id;
    IF NOT FOUND THEN RETURN jsonb_build_object('status','draft_not_for_address','draft_id',v_ctx_draft,'address',btrim(p_address),'guidance','Wrong IU.','next_action','verify_draft_id');END IF;
    v_selected_by:='context_draft_id';
  ELSIF v_ctx_select='latest' THEN
    SELECT * INTO v_draft FROM public.unit_edit_draft WHERE unit_id=v_iu.id AND draft_status='open' ORDER BY created_at DESC LIMIT 1;
    IF NOT FOUND THEN RETURN jsonb_build_object('status','no_open_draft','guidance','No draft. Use fn_iu_edit.','next_action','fn_iu_edit');END IF;
    v_target_id:=v_draft.id;v_selected_by:='latest';
  ELSE
    SELECT count(*) INTO v_open_count FROM public.unit_edit_draft WHERE unit_id=v_iu.id AND draft_status='open';
    IF v_open_count=0 THEN RETURN jsonb_build_object('status','no_open_draft','guidance','No draft. Use fn_iu_edit.','next_action','fn_iu_edit');
    ELSIF v_open_count=1 THEN
      SELECT * INTO v_draft FROM public.unit_edit_draft WHERE unit_id=v_iu.id AND draft_status='open';v_target_id:=v_draft.id;v_selected_by:='only_open_draft';
    ELSE
      SELECT jsonb_agg(jsonb_build_object('draft_id',d.id,'created_by',d.created_by,'created_at',d.created_at,'base_version_seq',d.base_version_seq,'reason',left(d.reason,100),'title_preview',d.draft_title,'hash_preview',left(d.draft_content_hash,16)) ORDER BY d.created_at DESC) INTO v_drafts FROM public.unit_edit_draft d WHERE d.unit_id=v_iu.id AND d.draft_status='open';
      RETURN jsonb_build_object('status','multiple_open_drafts','drafts',v_drafts,'open_count',v_open_count,'guidance','Choose draft_id or {"select":"latest"}.','next_action','fn_iu_comment_edit_draft');
    END IF;
  END IF;
  INSERT INTO public.unit_edit_comment(draft_id,unit_id,author_ref,author_type,comment_body,comment_kind) VALUES(COALESCE(v_target_id,v_draft.id),v_iu.id,btrim(p_author),p_author_type,p_body,p_kind) RETURNING id INTO v_comment_id;
  RETURN jsonb_build_object('status','comment_added','comment_id',v_comment_id,'draft_id',COALESCE(v_target_id,v_draft.id),'unit_id',v_iu.id,'selected_by',v_selected_by,'draft_author',v_draft.created_by);
END;$fn$;

REVOKE ALL ON FUNCTION public.fn_iu_edit_plan(text,text,text,text) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.fn_iu_create_edit_draft(text,text,text,text,text) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.fn_iu_comment_edit_draft(uuid,text,text,text,text) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.fn_iu_comment(text,text,text,text,text,jsonb) FROM PUBLIC;
DO $$DECLARE v_g text:=current_setting('app.p3c1_grantees');v_r text;v_sigs text[]:=ARRAY['public.fn_iu_edit_plan(text,text,text,text)','public.fn_iu_create_edit_draft(text,text,text,text,text)','public.fn_iu_comment_edit_draft(uuid,text,text,text,text)','public.fn_iu_comment(text,text,text,text,text,jsonb)'];v_s text;
BEGIN FOREACH v_r IN ARRAY string_to_array(v_g,',') LOOP v_r:=btrim(v_r);IF v_r<>'' THEN FOREACH v_s IN ARRAY v_sigs LOOP EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO %I',v_s,v_r);END LOOP;END IF;END LOOP;END;$$;
COMMIT;
FNSQL
echo "FN_EXIT=$FN_EXIT"
[ "$FN_EXIT" = "0" ] && FN_STATUS="OK" || { FN_STATUS="FAIL"; PHASE_STATUS="FAIL"; }
fi

§3. Tests (all comparisons have spaces around =)

if [ "$FN_STATUS" = "OK" ]; then
echo "=== TESTS ==="

T1=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "WITH cur AS (SELECT uv.body FROM unit_version uv JOIN information_unit iu ON iu.version_anchor_ref=uv.id WHERE iu.canonical_address=:'addr') SELECT (public.fn_iu_edit_plan(:'addr',cur.body,'tester'))->>'status' FROM cur;")
[ "$T1" = "no_change" ] && echo "T1=PASS" || { echo "T1=FAIL=$T1"; TEST_FAIL=$((TEST_FAIL+1)); }

T2=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "SELECT (public.fn_iu_edit_plan(:'addr','new body p3c1 $TS','tester'))->>'status';")
[ "$T2" = "plan_ok" ] && echo "T2=PASS" || { echo "T2=FAIL=$T2"; TEST_FAIL=$((TEST_FAIL+1)); }

T3=$("${PSQL[@]}" -t -A -c "SELECT (public.fn_iu_edit_plan('nonexistent/p3c1','x','t'))->>'status';")
[ "$T3" = "iu_not_found" ] && echo "T3=PASS" || { echo "T3=FAIL=$T3"; TEST_FAIL=$((TEST_FAIL+1)); }

T4=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "SELECT (public.fn_iu_edit_plan(:'addr','','t'))->>'status';")
[ "$T4" = "invalid_input" ] && echo "T4=PASS" || { echo "T4=FAIL=$T4"; TEST_FAIL=$((TEST_FAIL+1)); }

T5_P=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "WITH r AS (SELECT public.fn_iu_create_edit_draft(:'addr','draft body p3c1 $TS','agent:test','test reason') AS j) SELECT j->>'status',j->>'draft_id' FROM r;")
T5_S=$(echo "$T5_P" | cut -d'|' -f1); DRAFT_A_ID=$(echo "$T5_P" | cut -d'|' -f2)
[ "$T5_S" = "draft_created" ] && echo "T5=PASS A=$DRAFT_A_ID" || { echo "T5=FAIL=$T5_S"; TEST_FAIL=$((TEST_FAIL+1)); }

T6=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "WITH cur AS (SELECT uv.body FROM unit_version uv JOIN information_unit iu ON iu.version_anchor_ref=uv.id WHERE iu.canonical_address=:'addr') SELECT (public.fn_iu_create_edit_draft(:'addr',cur.body,'agent:test'))->>'status' FROM cur;")
[ "$T6" = "no_change" ] && echo "T6=PASS" || { echo "T6=FAIL=$T6"; TEST_FAIL=$((TEST_FAIL+1)); }

T7=$("${PSQL[@]}" -t -A -c "SELECT (public.fn_iu_comment_edit_draft('$DRAFT_A_ID','agent:reviewer','looks good','review'))->>'status';")
[ "$T7" = "comment_added" ] && echo "T7=PASS" || { echo "T7=FAIL=$T7"; TEST_FAIL=$((TEST_FAIL+1)); }

T8=$("${PSQL[@]}" -t -A -c "SELECT (public.fn_iu_comment_edit_draft('00000000-0000-0000-0000-000000000001','x','x'))->>'status';")
[ "$T8" = "draft_not_found" ] && echo "T8=PASS" || { echo "T8=FAIL=$T8"; TEST_FAIL=$((TEST_FAIL+1)); }

T9_P=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "WITH r AS (SELECT public.fn_iu_comment(:'addr','agent:opus','I agree') AS j) SELECT j->>'status',j->>'selected_by' FROM r;")
T9_S=$(echo "$T9_P" | cut -d'|' -f1); T9_SB=$(echo "$T9_P" | cut -d'|' -f2)
[ "$T9_S" = "comment_added" ] && [ "$T9_SB" = "only_open_draft" ] && echo "T9=PASS" || { echo "T9=FAIL s=$T9_S sb=$T9_SB"; TEST_FAIL=$((TEST_FAIL+1)); }

T10_P=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "WITH r AS (SELECT public.fn_iu_create_edit_draft(:'addr','alt body p3c1 $TS','agent:gemini','alt') AS j) SELECT j->>'status',j->>'draft_id' FROM r;")
T10_S=$(echo "$T10_P" | cut -d'|' -f1); DRAFT_B_ID=$(echo "$T10_P" | cut -d'|' -f2)
[ "$T10_S" = "draft_created" ] && echo "T10=PASS B=$DRAFT_B_ID" || { echo "T10=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }

COMMENT_MID=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_edit_comment;" | tr -d ' ')

T11=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "SELECT (public.fn_iu_comment(:'addr','agent:opus','which?'))->>'status';")
[ "$T11" = "multiple_open_drafts" ] && echo "T11=PASS" || { echo "T11=FAIL=$T11"; TEST_FAIL=$((TEST_FAIL+1)); }
CM_T11=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_edit_comment;" | tr -d ' ')
[ "$CM_T11" = "$COMMENT_MID" ] && echo "T11b=PASS" || { echo "T11b=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }

T12_P=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "WITH r AS (SELECT public.fn_iu_comment(:'addr','agent:opus','for A','general','agent','{\"draft_id\":\"$DRAFT_A_ID\"}') AS j) SELECT j->>'status',j->>'selected_by' FROM r;")
T12_S=$(echo "$T12_P" | cut -d'|' -f1); T12_SB=$(echo "$T12_P" | cut -d'|' -f2)
[ "$T12_S" = "comment_added" ] && [ "$T12_SB" = "context_draft_id" ] && echo "T12=PASS" || { echo "T12=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }

T13_P=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "WITH r AS (SELECT public.fn_iu_comment(:'addr','agent:opus','latest','general','agent','{\"select\":\"latest\"}') AS j) SELECT j->>'status',j->>'selected_by' FROM r;")
T13_S=$(echo "$T13_P" | cut -d'|' -f1); T13_SB=$(echo "$T13_P" | cut -d'|' -f2)
[ "$T13_S" = "comment_added" ] && [ "$T13_SB" = "latest" ] && echo "T13=PASS" || { echo "T13=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }

T14=$("${PSQL[@]}" -v addr="$TEST_ADDR_B" -t -A -c "SELECT (public.fn_iu_comment(:'addr','agent:x','hello'))->>'status';")
[ "$T14" = "no_open_draft" ] && echo "T14=PASS" || { echo "T14=FAIL=$T14"; TEST_FAIL=$((TEST_FAIL+1)); }

CM_PRE15=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_edit_comment;" | tr -d ' ')
T15=$("${PSQL[@]}" -v addr="$TEST_ADDR_B" -t -A -c "SELECT (public.fn_iu_comment(:'addr','agent:x','mm','general','agent','{\"draft_id\":\"$DRAFT_A_ID\"}'))->>'status';")
[ "$T15" = "draft_not_for_address" ] && echo "T15=PASS" || { echo "T15=FAIL=$T15"; TEST_FAIL=$((TEST_FAIL+1)); }
CM_POST15=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_edit_comment;" | tr -d ' ')
[ "$CM_POST15" = "$CM_PRE15" ] && echo "T15b=PASS" || { echo "T15b=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }

DRAFT_NOW=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_edit_draft;" | tr -d ' ')
[ "$DRAFT_NOW" = "$((DRAFT_BEFORE+2))" ] && echo "T16=PASS" || { echo "T16=FAIL=$DRAFT_NOW"; TEST_FAIL=$((TEST_FAIL+1)); }

COMMENT_NOW=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_edit_comment;" | tr -d ' ')
[ "$COMMENT_NOW" = "$((COMMENT_BEFORE+4))" ] && echo "T17=PASS" || { echo "T17=FAIL=$COMMENT_NOW"; TEST_FAIL=$((TEST_FAIL+1)); }

IU_NOW=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_unit;" | tr -d ' ')
UV_NOW=$("${PSQL[@]}" -t -c "SELECT count(*) FROM unit_version;" | tr -d ' ')
[ "$IU_NOW" = "$IU_BEFORE" ] && [ "$UV_NOW" = "$UV_BEFORE" ] && echo "T18=PASS" || { echo "T18=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }

# T19: Security (psql vars for grantee checks)
T19="PASS"
SIGS=("fn_iu_edit_plan(text,text,text,text)" "fn_iu_create_edit_draft(text,text,text,text,text)" "fn_iu_comment_edit_draft(uuid,text,text,text,text)" "fn_iu_comment(text,text,text,text,text,jsonb)")
for SIG in "${SIGS[@]}"; do
  META=$("${PSQL[@]}" -t -A -c "SELECT prosecdef,proconfig FROM pg_proc WHERE oid=to_regprocedure('public.$SIG');")
  SD=$(echo "$META" | cut -d'|' -f1); PC=$(echo "$META" | cut -d'|' -f2)
  [ "$SD" = "t" ] || { T19="FAIL_DEF_$SIG"; TEST_FAIL=$((TEST_FAIL+1)); }
  echo "$PC" | grep -q "pg_catalog" || { T19="FAIL_SP_$SIG"; TEST_FAIL=$((TEST_FAIL+1)); }
done
PUB=$("${PSQL[@]}" -t -c "SELECT count(*) FROM (SELECT (aclexplode(proacl)).grantee g,(aclexplode(proacl)).privilege_type p FROM pg_proc WHERE proname IN('fn_iu_edit_plan','fn_iu_create_edit_draft','fn_iu_comment_edit_draft','fn_iu_comment') AND pronamespace='public'::regnamespace) x WHERE x.g=0 AND x.p='EXECUTE';" | tr -d ' ')
[ "$PUB" = "0" ] || { T19="FAIL_PUB=$PUB"; TEST_FAIL=$((TEST_FAIL+1)); }
IFS=',' read -ra GR <<< "$FN_GRANTEES"
for R in "${GR[@]}"; do
  R=$(echo "$R" | tr -d ' '); [ -z "$R" ] && continue
  for SIG in "${SIGS[@]}"; do
    CAN=$("${PSQL_NOSTOP[@]}" -v role="$R" -v sig="public.$SIG" -t -A -c "SELECT has_function_privilege(:'role',:'sig','EXECUTE');" 2>/dev/null)
    [ "$CAN" = "t" ] || { T19="FAIL_GR_${R}_$SIG"; TEST_FAIL=$((TEST_FAIL+1)); }
  done
done
echo "T19=$T19"

# T20: Guidance (psql vars)
T20="PASS"
G1=$("${PSQL[@]}" -t -A -c "SELECT (fn_iu_edit_plan('x/x','x','t'))->>'guidance';")
G2=$("${PSQL[@]}" -v addr="$TEST_ADDR_A" -t -A -c "SELECT (fn_iu_comment(:'addr','x','x'))->>'guidance';")
G3=$("${PSQL[@]}" -v addr="$TEST_ADDR_B" -t -A -c "SELECT (fn_iu_comment(:'addr','x','x'))->>'guidance';")
for G in "$G1" "$G2" "$G3"; do [ -n "$G" ] || { T20="FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }; done
echo "T20=$T20"

# T21: Source safety
T21=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_proc WHERE proname IN('fn_iu_edit_plan','fn_iu_create_edit_draft','fn_iu_comment_edit_draft','fn_iu_comment') AND pronamespace='public'::regnamespace AND(prosrc~*'app\.canonical_writer' OR prosrc~*'insert\s+into\s+(public\.)?unit_version' OR prosrc~*'update\s+(public\.)?information_unit' OR prosrc~*'insert\s+into\s+(public\.)?information_unit' OR prosrc~*'update\s+(public\.)?unit_version' OR prosrc~*'delete\s+from\s+(public\.)?information_unit' OR prosrc~*'delete\s+from\s+(public\.)?unit_version');" | tr -d ' ')
[ "$T21" = "0" ] && echo "T21=PASS" || { echo "T21=FAIL=$T21"; TEST_FAIL=$((TEST_FAIL+1)); }

echo "TEST_FAIL=$TEST_FAIL"
fi

§4–§5. Cleanup + Report

if [ "$TEST_FAIL" != "0" ] && [ "$FN_STATUS" = "OK" ]; then
  echo "=== CLEANUP ==="; CLEANUP_STATUS="ATTEMPTING"; DROP_FAIL=0
  for SIG in "fn_iu_comment(text,text,text,text,text,jsonb)" "fn_iu_comment_edit_draft(uuid,text,text,text,text)" "fn_iu_create_edit_draft(text,text,text,text,text)" "fn_iu_edit_plan(text,text,text,text)"; do
    "${PSQL_NOSTOP[@]}" -c "DROP FUNCTION IF EXISTS public.$SIG;" 2>&1 || DROP_FAIL=1
  done
  [ "$DROP_FAIL" = "0" ] && CLEANUP_STATUS="FUNCTIONS_DROPPED" || { CLEANUP_STATUS="CRITICAL"; PHASE_STATUS="CRITICAL"; }
fi
echo "=== FINAL ==="
if [ "$PHASE_STATUS" = "CRITICAL" ]; then P3C2_READINESS="BLOCKED"
elif [ "$PREFLIGHT_STATUS" != "PASS" ]; then PHASE_STATUS="FAIL"; P3C2_READINESS="BLOCKED"
elif [ "$FN_STATUS" != "OK" ]; then PHASE_STATUS="FAIL"; P3C2_READINESS="BLOCKED"
elif [ "$TEST_FAIL" = "0" ]; then PHASE_STATUS="PASS"; P3C2_READINESS="READY"
else PHASE_STATUS="${PHASE_STATUS:-FAIL}"; P3C2_READINESS="BLOCKED"; fi
echo "phase_status=$PHASE_STATUS p3c2_readiness=$P3C2_READINESS"
echo "IU=$IU_BEFORE→$IU_NOW UV=$UV_BEFORE→$UV_NOW D=$DRAFT_BEFORE→$DRAFT_NOW C=$COMMENT_BEFORE→$COMMENT_NOW"
echo "owner=$FN_OWNER grantees=$FN_GRANTEES lifecycle=$UV_LC"
echo "pilot: A=$DRAFT_A_ID B=$DRAFT_B_ID ADDR_A=$TEST_ADDR_A ADDR_B=$TEST_ADDR_B"
echo "test_rows_retained=true cleanup=${CLEANUP_STATUS:-N/A}"
echo "ux_notes_read=23-p3c1-ux-state-foundation-notes.md_rev2"
echo "ux_hooks=timestamp_ok,actor_card_guc_deferred,per_actor_watermark_deferred"
echo "LOG=$LOG"
echo "=== UX EVIDENCE ==="
"${PSQL_NOSTOP[@]}" -v addr="$TEST_ADDR_A" -t -A -c "SELECT public.fn_iu_edit_plan(:'addr','ux','t');" 2>/dev/null
echo "---"
"${PSQL_NOSTOP[@]}" -v addr="$TEST_ADDR_B" -t -A -c "SELECT public.fn_iu_comment(:'addr','x','hi');" 2>/dev/null
echo "---"
"${PSQL_NOSTOP[@]}" -v addr="$TEST_ADDR_A" -t -A -c "SELECT public.fn_iu_comment(:'addr','x','m');" 2>/dev/null
echo ""
echo "=== AI INTERFACE ==="
echo "fn_iu_edit_plan(address,body,actor)         -- xem truoc"
echo "fn_iu_create_edit_draft(address,body,actor)  -- tao nhap"
echo "fn_iu_comment(address,actor,comment)         -- gop y"
echo "fn_iu_apply/fn_iu_edit -> P3C2"
echo ""
echo "=== AGENT: UPLOAD REPORT ==="
echo "Path: knowledge/dev/laws/dieu44-trien-khai/reports/23-p3c1-iu-edit-draft-safe-functions-report.md"
echo "Include: verdict, T1-T21, sigs, security, UX evidence, counts, pilots, ux_hooks status."
echo "Upload even on FAIL/CRITICAL."

Hard Boundaries

  • ❌ No table DDL / trigger / gateway / IU-UV writes / vector / cleanup / retry

23-P3C1 rev7 | Shell comparisons fixed | psql vars in T19/T20 | UX hooks in report | Chờ GPT/User final review