KB-130B rev 12
22-P2 — IU Native Create Main Functions Prompt (rev12)
27 min read Revision 12
pack-22p2fn-iu-createmain-functionspromptrev12
22-P2 — IU Native Create Main Functions (rev12)
Date: 2026-05-06 | Status: PROMPT rev12 — chờ GPT final yes/no Controlling: GPT rev11 review (5 fixes: verdict POST_EXIT, FCHECK captured, POST_COMMIT normalized, init_error field, existing-state semantics) Prior: rev11 had correct architecture; rev12 only tightens shell verdict to prevent false PASS. SELF-CONTAINED. Birth+invariants=EXCEPTION. No global set -e (intentional: always report). Philosophy: fn_iu_create = complete-or-nothing. Auxiliary engine for bypass/legacy/drift only.
#!/usr/bin/env bash
# 22-P2 rev12 — IU Native Create Main Functions
# No global set -e: intentional — errors captured by explicit status variables to ensure report always uploads.
set -uo pipefail # strict for address gen; NO set -e
PG_CONTAINER="${PG_CONTAINER:-postgres}"
PG_USER="${PG_USER:-directus}"
PG_DB="${PG_DB:-directus}"
PSQL_CMD=(docker exec -i "$PG_CONTAINER" psql -v ON_ERROR_STOP=1 -U "$PG_USER" -d "$PG_DB")
PSQL_RO=(docker exec -i "$PG_CONTAINER" psql -U "$PG_USER" -d "$PG_DB" -tA)
make_uuid() {
if command -v uuidgen >/dev/null 2>&1; then uuidgen | tr 'A-F' 'a-f'
elif [ -r /proc/sys/kernel/random/uuid ]; then cat /proc/sys/kernel/random/uuid
else "${PSQL_RO[@]}" -c "SELECT gen_random_uuid()" 2>/dev/null; fi
}
# === Addresses (no early exit — capture error, skip SQL, still report) ===
INIT_EXIT=0
INIT_ERROR=""
PILOT_ADDRESS="pilot.p2.$(date -u +%Y%m%d-%H%M%S).$(make_uuid | cut -c1-8)"
PLAN_ADDRESS_1="test.p2.plan.$(make_uuid | cut -c1-8)"
PLAN_ADDRESS_2="test.p2.plan.$(make_uuid | cut -c1-8)"
if ! [[ "$PILOT_ADDRESS" =~ ^pilot\.p2\.[0-9]{8}-[0-9]{6}\.[0-9a-f]{8}$ ]]; then
INIT_EXIT=1; INIT_ERROR="pilot format: $PILOT_ADDRESS"; fi
if ! [[ "$PLAN_ADDRESS_1" =~ ^test\.p2\.plan\.[0-9a-f]{8}$ ]]; then
INIT_EXIT=1; INIT_ERROR="plan1 format: $PLAN_ADDRESS_1"; fi
if ! [[ "$PLAN_ADDRESS_2" =~ ^test\.p2\.plan\.[0-9a-f]{8}$ ]]; then
INIT_EXIT=1; INIT_ERROR="plan2 format: $PLAN_ADDRESS_2"; fi
echo "PILOT=$PILOT_ADDRESS"
echo "PLAN1=$PLAN_ADDRESS_1"
echo "PLAN2=$PLAN_ADDRESS_2"
LOG_PATH="/tmp/22-p2.$(date -u +%Y%m%d-%H%M%S).log"
PSQL_EXIT=0
POST_COMMIT_STATUS="NOT_RUN"
POST_EXIT=0
DIAG_EXIT=0
FCHECK_STATUS="NOT_RUN"
FCHECK_EXIT=0
FCHECK_RESULT=""
# === Skip SQL if init failed ===
if [ $INIT_EXIT -ne 0 ]; then
echo "INIT FAILED: $INIT_ERROR" | tee "$LOG_PATH"
PSQL_EXIT=99
else
# === Main psql ===
set +e
"${PSQL_CMD[@]}" \
-v pilot_addr="$PILOT_ADDRESS" \
-v plan_addr_1="$PLAN_ADDRESS_1" \
-v plan_addr_2="$PLAN_ADDRESS_2" \
> "$LOG_PATH" 2>&1 <<'EOSQL'
SELECT set_config('app.p2_pilot_address',:'pilot_addr',false);
SELECT set_config('app.p2_plan_address_1',:'plan_addr_1',false);
SELECT set_config('app.p2_plan_address_2',:'plan_addr_2',false);
DO $$ BEGIN
IF current_setting('app.p2_pilot_address','t') !~ '^pilot\.p2\.[0-9]{8}-[0-9]{6}\.[0-9a-f]{8}$' THEN RAISE EXCEPTION 'pilot'; END IF;
IF current_setting('app.p2_plan_address_1','t') !~ '^test\.p2\.plan\.[0-9a-f]{8}$' THEN RAISE EXCEPTION 'plan1'; END IF;
IF current_setting('app.p2_plan_address_2','t') !~ '^test\.p2\.plan\.[0-9a-f]{8}$' THEN RAISE EXCEPTION 'plan2'; END IF;
END $$;
DO $$ DECLARE v text; BEGIN
FOR v IN SELECT unnest(ARRAY['public.fn_content_hash(text)','public.fn_iu_resolve_default(text,text,text)','public.fn_iu_classify_existing(text)','public.fn_iu_create_preflight()','public.fn_iu_verify_invariants(text)']) LOOP
IF to_regprocedure(v) IS NULL THEN RAISE EXCEPTION 'helper: %',v; END IF; END LOOP;
END $$;
DO $$ DECLARE v_c int; BEGIN
SELECT count(*) INTO v_c 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_c!=0 THEN RAISE EXCEPTION 'conflict: %',v_c; END IF;
END $$;
DO $$ DECLARE v jsonb; BEGIN v:=public.fn_iu_create_preflight(); IF v->>'status'!='pass' THEN RAISE EXCEPTION 'pf: %',v; END IF; RAISE NOTICE 'PREFLIGHT_JSON=%',v; END $$;
DO $$
DECLARE v_exp jsonb:='{"information_unit.id":"uuid","information_unit.canonical_address":"text","information_unit.unit_kind":"text","information_unit.owner_ref":"text","information_unit.created_by":"text","information_unit.updated_by":"text","information_unit.identity_profile":"jsonb","information_unit.parent_or_container_ref":"uuid","information_unit.version_anchor_ref":"uuid","information_unit.content_anchor_ref":"text","unit_version.id":"uuid","unit_version.unit_id":"uuid","unit_version.body":"text","unit_version.content_hash":"text","unit_version.version_seq":"int4","unit_version.created_by":"text"}'::jsonb;
v_key text;v_e text;v_a text;v_t text;v_c text;
BEGIN FOR v_key,v_e IN SELECT * FROM jsonb_each_text(v_exp) LOOP
v_t:=split_part(v_key,'.',1);v_c:=split_part(v_key,'.',2);
SELECT udt_name INTO v_a FROM information_schema.columns WHERE table_schema='public' AND table_name=v_t AND column_name=v_c;
IF v_a IS NULL THEN RAISE EXCEPTION '%.% missing',v_t,v_c; END IF;
IF v_e='text' THEN IF v_a NOT IN ('text','varchar','bpchar') THEN RAISE EXCEPTION '%.% %',v_t,v_c,v_a; END IF;
ELSIF v_a!=v_e THEN RAISE EXCEPTION '%.% exp % got %',v_t,v_c,v_e,v_a; END IF;
END LOOP; END $$;
DO $$ DECLARE v_c int; BEGIN
SELECT count(*) INTO v_c FROM pg_constraint c WHERE c.conrelid='public.information_unit'::regclass AND c.contype='u'
AND (SELECT count(*) FROM unnest(c.conkey))=1 AND (SELECT count(*) FROM unnest(c.conkey) k JOIN pg_attribute a ON a.attrelid=c.conrelid AND a.attnum=k WHERE a.attname='canonical_address')=1;
IF v_c!=1 THEN RAISE EXCEPTION 'unique=%',v_c; END IF;
END $$;
DO $$ DECLARE v_fk text;v_dup int;v_c int; BEGIN
SELECT count(*) INTO v_c FROM pg_constraint WHERE conrelid='public.information_unit'::regclass AND confrelid='public.unit_version'::regclass AND contype='f' AND condeferrable;
IF v_c!=1 THEN RAISE EXCEPTION 'FK=%',v_c; END IF;
SELECT conname INTO v_fk FROM pg_constraint WHERE conrelid='public.information_unit'::regclass AND confrelid='public.unit_version'::regclass AND contype='f' AND condeferrable;
SELECT count(*) INTO v_dup FROM pg_constraint WHERE conname=v_fk;
IF v_dup>1 THEN RAISE EXCEPTION 'FK dup %',v_dup; END IF;
END $$;
SELECT 'information_unit' AS tbl,count(*) FROM public.information_unit UNION ALL SELECT 'unit_version',count(*) FROM public.unit_version UNION ALL SELECT 'birth_iu',count(*) FROM public.birth_registry WHERE collection_name='information_unit' UNION ALL SELECT 'birth_total',count(*) FROM public.birth_registry;
DO $$ BEGIN IF EXISTS(SELECT 1 FROM public.information_unit WHERE canonical_address IN (current_setting('app.p2_pilot_address'),current_setting('app.p2_plan_address_1'),current_setting('app.p2_plan_address_2'))) THEN RAISE EXCEPTION 'collision'; END IF; END $$;
BEGIN;
CREATE FUNCTION public.fn_iu_create_plan(
p_canonical_address text,p_title text,p_body text,p_actor text,
p_unit_kind text DEFAULT NULL,p_section_type text DEFAULT NULL,
p_owner_ref text DEFAULT NULL,p_publication_type text DEFAULT NULL,
p_parent_ref uuid DEFAULT NULL
) RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path=pg_catalog,public AS $$
DECLARE v_issues text[]:='{}';v_uk jsonb;v_st jsonb;v_pt jsonb;v_owner text;v_existing jsonb;v_existing_status text;v_hash_preview text;v_preflight jsonb;v_status text;
BEGIN
IF p_canonical_address IS NULL OR btrim(p_canonical_address)='' THEN v_issues:=array_append(v_issues,'canonical_address empty'); END IF;
IF p_title IS NULL OR btrim(p_title)='' THEN v_issues:=array_append(v_issues,'title empty'); END IF;
IF p_body IS NULL THEN v_issues:=array_append(v_issues,'body null'); END IF;
IF p_actor IS NULL OR btrim(p_actor)='' THEN v_issues:=array_append(v_issues,'actor empty'); END IF;
BEGIN v_preflight:=public.fn_iu_create_preflight(); EXCEPTION WHEN OTHERS THEN v_issues:=array_append(v_issues,'preflight: '||SQLERRM);v_preflight:=jsonb_build_object('status','fail'); END;
v_uk:=public.fn_iu_resolve_default(p_unit_kind,'iu_create.default_unit_kind','vocab.unit_kind.');
IF v_uk->>'status' NOT IN ('explicit','default','auto_single') THEN v_issues:=array_append(v_issues,'unit_kind: '||COALESCE(v_uk->>'message',v_uk->>'status')); END IF;
v_st:=public.fn_iu_resolve_default(p_section_type,'iu_create.default_section_type','vocab.section_type.');
IF v_st->>'status' NOT IN ('explicit','default','auto_single') THEN v_issues:=array_append(v_issues,'section_type: '||COALESCE(v_st->>'message',v_st->>'status')); END IF;
IF p_publication_type IS NOT NULL AND btrim(p_publication_type)!='' THEN
v_pt:=public.fn_iu_resolve_default(p_publication_type,'__no_default__','vocab.publication_type.');
IF v_pt->>'status'!='explicit' THEN v_issues:=array_append(v_issues,'publication_type: '||COALESCE(v_pt->>'message','invalid')); END IF; END IF;
v_owner:=COALESCE(NULLIF(btrim(COALESCE(p_owner_ref,'')),''),p_actor);
IF p_canonical_address IS NOT NULL AND btrim(p_canonical_address)!='' THEN
v_existing:=public.fn_iu_classify_existing(btrim(p_canonical_address));v_existing_status:=COALESCE(v_existing->>'status','not_found');
ELSE v_existing_status:='not_found'; END IF;
IF p_body IS NOT NULL THEN v_hash_preview:=left(public.fn_content_hash(p_body),16); END IF;
IF array_length(v_issues,1)>0 THEN
IF EXISTS(SELECT 1 FROM unnest(v_issues) x WHERE x IN ('canonical_address empty','title empty','body null','actor empty')) THEN v_status:='invalid_input';
ELSIF EXISTS(SELECT 1 FROM unnest(v_issues) x WHERE x LIKE 'publication_type:%') THEN v_status:='invalid_publication_type';
ELSIF EXISTS(SELECT 1 FROM unnest(v_issues) x WHERE x LIKE 'unit_kind:%' OR x LIKE 'section_type:%') THEN v_status:='unresolved_vocab';
ELSIF EXISTS(SELECT 1 FROM unnest(v_issues) x WHERE x LIKE 'preflight:%') THEN v_status:='preflight_failed';
ELSE v_status:='invalid_input'; END IF;
ELSIF v_existing_status!='not_found' THEN v_status:=v_existing_status;
ELSE v_status:='plan_ok'; END IF;
RETURN jsonb_build_object('mode','plan','status',v_status,'would_create',v_status='plan_ok',
'existing',CASE WHEN v_existing_status!='not_found' THEN v_existing END,
'resolved_unit_kind',v_uk->>'value','resolved_section_type',v_st->>'value','resolved_owner_ref',v_owner,
'body_length',CASE WHEN p_body IS NOT NULL THEN length(p_body) END,
'content_hash_preview',v_hash_preview,'preflight',v_preflight->>'status',
'fk_initially_deferred',v_preflight->'fk_initially_deferred','issues',to_jsonb(v_issues));
END;$$;
CREATE FUNCTION public.fn_iu_create(
p_canonical_address text,p_title text,p_body text,p_actor text,
p_unit_kind text DEFAULT NULL,p_section_type text DEFAULT NULL,
p_owner_ref text DEFAULT NULL,p_publication_type text DEFAULT NULL,
p_parent_ref uuid DEFAULT NULL
) RETURNS jsonb LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path=pg_catalog,public AS $$
DECLARE
v_iu_id uuid;v_uv_id uuid;v_hash text;v_identity jsonb;v_birth_ok boolean;
v_uk jsonb;v_st jsonb;v_pt jsonb;v_unit_kind text;v_section_type text;v_owner text;
v_existing jsonb;v_existing_status text;
v_preflight jsonb;v_fk_name text;v_fk_deferred boolean;
v_constraint_name text;v_ca_unique_guard text;v_guard_count int;
v_verify jsonb; -- full invariant verify
BEGIN
IF p_canonical_address IS NULL OR btrim(p_canonical_address)='' THEN RAISE EXCEPTION 'canonical_address required'; END IF;
IF p_title IS NULL OR btrim(p_title)='' THEN RAISE EXCEPTION 'title required'; END IF;
IF p_body IS NULL THEN RAISE EXCEPTION 'body required'; END IF;
IF p_actor IS NULL OR btrim(p_actor)='' THEN RAISE EXCEPTION 'actor required'; END IF;
v_preflight:=public.fn_iu_create_preflight();
IF v_preflight->>'status'!='pass' THEN RAISE EXCEPTION 'preflight: %',v_preflight; END IF;
v_fk_name:=v_preflight->>'fk_name';v_fk_deferred:=(v_preflight->>'fk_initially_deferred')::boolean;
SELECT count(*) INTO v_guard_count FROM pg_constraint c WHERE c.conrelid='public.information_unit'::regclass AND c.contype='u'
AND (SELECT count(*) FROM unnest(c.conkey))=1 AND (SELECT count(*) FROM unnest(c.conkey) k JOIN pg_attribute a ON a.attrelid=c.conrelid AND a.attnum=k WHERE a.attname='canonical_address')=1;
IF v_guard_count!=1 THEN RAISE EXCEPTION 'unique=%',v_guard_count; END IF;
SELECT conname INTO v_ca_unique_guard FROM pg_constraint c WHERE c.conrelid='public.information_unit'::regclass AND c.contype='u'
AND (SELECT count(*) FROM unnest(c.conkey))=1 AND (SELECT count(*) FROM unnest(c.conkey) k JOIN pg_attribute a ON a.attrelid=c.conrelid AND a.attnum=k WHERE a.attname='canonical_address')=1;
IF v_ca_unique_guard IS NULL THEN RAISE EXCEPTION 'guard name'; END IF;
v_uk:=public.fn_iu_resolve_default(p_unit_kind,'iu_create.default_unit_kind','vocab.unit_kind.');
IF v_uk->>'status' NOT IN ('explicit','default','auto_single') THEN RAISE EXCEPTION 'unit_kind: %',v_uk->>'message'; END IF;
v_unit_kind:=v_uk->>'value';
v_st:=public.fn_iu_resolve_default(p_section_type,'iu_create.default_section_type','vocab.section_type.');
IF v_st->>'status' NOT IN ('explicit','default','auto_single') THEN RAISE EXCEPTION 'section_type: %',v_st->>'message'; END IF;
v_section_type:=v_st->>'value';
IF p_publication_type IS NOT NULL AND btrim(p_publication_type)!='' THEN
v_pt:=public.fn_iu_resolve_default(p_publication_type,'__no_default__','vocab.publication_type.');
IF v_pt->>'status'!='explicit' THEN RAISE EXCEPTION 'publication_type: %',v_pt->>'message'; END IF; END IF;
v_owner:=COALESCE(NULLIF(btrim(COALESCE(p_owner_ref,'')),''),p_actor);
PERFORM pg_advisory_xact_lock(hashtext(btrim(p_canonical_address)));
v_existing:=public.fn_iu_classify_existing(btrim(p_canonical_address));
v_existing_status:=COALESCE(v_existing->>'status','not_found');
IF v_existing_status!='not_found' THEN
IF v_existing_status IN ('exists_missing_birth','exists_missing_version','exists_anchor_invalid','exists_duplicate_version','exists_unknown_state') THEN
RETURN v_existing||jsonb_build_object('action_required',true,'health_signal_needed',true); END IF;
RETURN v_existing; END IF;
IF NOT v_fk_deferred THEN EXECUTE format('SET CONSTRAINTS %I DEFERRED',v_fk_name); END IF;
v_iu_id:=gen_random_uuid();v_uv_id:=gen_random_uuid();v_hash:=public.fn_content_hash(p_body);
v_identity:=jsonb_build_object('title',btrim(p_title),'owner_lookup_ref',v_owner,'primary_section_type_ref',v_section_type);
IF p_publication_type IS NOT NULL AND btrim(p_publication_type)!='' THEN
v_identity:=v_identity||jsonb_build_object('publication_type_ref',btrim(p_publication_type)); END IF;
BEGIN
INSERT INTO public.information_unit(id,canonical_address,unit_kind,owner_ref,created_by,updated_by,identity_profile,parent_or_container_ref)
VALUES(v_iu_id,btrim(p_canonical_address),v_unit_kind,v_owner,btrim(p_actor),btrim(p_actor),v_identity,p_parent_ref);
EXCEPTION WHEN unique_violation THEN
GET STACKED DIAGNOSTICS v_constraint_name=CONSTRAINT_NAME;
IF v_constraint_name=v_ca_unique_guard THEN
v_existing:=public.fn_iu_classify_existing(btrim(p_canonical_address));v_existing_status:=COALESCE(v_existing->>'status','not_found');
IF v_existing_status IN ('exists_missing_birth','exists_missing_version','exists_anchor_invalid','exists_duplicate_version','exists_unknown_state') THEN
RETURN v_existing||jsonb_build_object('action_required',true,'health_signal_needed',true); END IF;
RETURN v_existing;
ELSE RAISE; END IF; END;
INSERT INTO public.unit_version(id,unit_id,body,content_hash,version_seq,created_by) VALUES(v_uv_id,v_iu_id,p_body,v_hash,1,btrim(p_actor));
UPDATE public.information_unit SET version_anchor_ref=v_uv_id,content_anchor_ref=v_uv_id::text WHERE id=v_iu_id;
-- FULL INVARIANT VERIFY (not just birth — complete-or-nothing)
v_verify:=public.fn_iu_verify_invariants(btrim(p_canonical_address));
IF NOT COALESCE((v_verify->>'all_pass')::boolean,false) THEN
RAISE EXCEPTION 'fn_iu_create invariant fail: % — canonical path rollback',v_verify;
END IF;
RETURN jsonb_build_object('status','created','iu_id',v_iu_id,'uv_id',v_uv_id,'canonical_address',btrim(p_canonical_address),
'content_hash',v_hash,'birth_verified',true,'invariants_verified',true,
'unit_kind',v_unit_kind,'section_type',v_section_type,'version_seq',1);
END;$$;
REVOKE ALL ON FUNCTION public.fn_iu_create_plan(text,text,text,text,text,text,text,text,uuid) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.fn_iu_create(text,text,text,text,text,text,text,text,uuid) FROM PUBLIC;
DO $$ BEGIN
IF EXISTS(SELECT 1 FROM pg_roles WHERE rolname='directus') THEN
EXECUTE 'GRANT EXECUTE ON FUNCTION public.fn_iu_create(text,text,text,text,text,text,text,text,uuid) TO directus';
EXECUTE 'GRANT EXECUTE ON FUNCTION public.fn_iu_create_plan(text,text,text,text,text,text,text,text,uuid) TO directus';
ELSE RAISE WARNING 'directus missing — ADAPTER=BLOCKED'; END IF;
END $$;
DO $$ DECLARE v_c int; BEGIN
SELECT count(*) INTO v_c FROM information_schema.routine_privileges WHERE routine_schema='public' AND routine_name IN ('fn_iu_create','fn_iu_create_plan') AND grantee='PUBLIC';
IF v_c>0 THEN RAISE EXCEPTION 'PUBLIC %',v_c; END IF;
END $$;
-- TESTS
DO $$ DECLARE v jsonb; BEGIN v:=public.fn_iu_create_plan(current_setting('app.p2_plan_address_1'),'Plan','body','agent:test'); IF v->>'status'!='plan_ok' THEN RAISE EXCEPTION 'plan: %',v->>'status'; END IF; END $$;
DO $$ DECLARE v jsonb; BEGIN v:=public.fn_iu_create_plan(NULL,NULL,NULL,NULL); IF v->>'status'!='invalid_input' THEN RAISE EXCEPTION 'bad: %',v->>'status'; END IF; END $$;
DO $$ DECLARE v jsonb;v_a text; BEGIN SELECT canonical_address INTO v_a FROM public.information_unit ORDER BY canonical_address LIMIT 1; IF v_a IS NULL THEN RAISE NOTICE 'existing: SKIP'; RETURN; END IF; v:=public.fn_iu_create_plan(v_a,'T','b','a'); IF (v->>'would_create')::boolean THEN RAISE EXCEPTION 'existing'; END IF; END $$;
DO $$ DECLARE v_c bigint; BEGIN SELECT count(*) INTO v_c FROM public.information_unit WHERE canonical_address IN (current_setting('app.p2_plan_address_1'),current_setting('app.p2_plan_address_2')); IF v_c>0 THEN RAISE EXCEPTION 'plan rows'; END IF; END $$;
DO $$ DECLARE v_c int; BEGIN SELECT count(*) INTO v_c FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname='public' AND proname IN ('fn_iu_create','fn_iu_create_plan'); IF v_c!=2 THEN RAISE EXCEPTION 'compile: %',v_c; END IF; END $$;
DO $$ DECLARE v_a text;v_r jsonb;v_v jsonb;v_i jsonb;v_c bigint; BEGIN
v_a:=current_setting('app.p2_pilot_address');
IF (public.fn_iu_classify_existing(v_a)->>'status')!='not_found' THEN RAISE EXCEPTION 'exists'; END IF;
v_r:=public.fn_iu_create(v_a,'P2 Pilot Test','Content by 22-P2.','agent:p2-test');
IF v_r->>'status'!='created' THEN RAISE EXCEPTION 'create: %',v_r->>'status'; END IF;
IF NOT COALESCE((v_r->>'birth_verified')::boolean,false) THEN RAISE EXCEPTION 'birth'; END IF;
IF NOT COALESCE((v_r->>'invariants_verified')::boolean,false) THEN RAISE EXCEPTION 'inv_flag'; END IF;
v_v:=public.fn_iu_verify_invariants(v_a);
IF NOT COALESCE((v_v->>'all_pass')::boolean,false) THEN RAISE EXCEPTION 'inv: %',v_v; END IF;
SELECT count(*) INTO v_c FROM public.information_unit WHERE canonical_address=v_a; IF v_c!=1 THEN RAISE EXCEPTION 'IU=%',v_c; END IF;
SELECT count(*) INTO v_c FROM public.unit_version WHERE unit_id=(SELECT id FROM public.information_unit WHERE canonical_address=v_a); IF v_c!=1 THEN RAISE EXCEPTION 'UV=%',v_c; END IF;
SELECT count(*) INTO v_c FROM public.birth_registry WHERE collection_name='information_unit' AND entity_code='information_unit::'||(SELECT id FROM public.information_unit WHERE canonical_address=v_a)::text; IF v_c!=1 THEN RAISE EXCEPTION 'br=%',v_c; END IF;
v_i:=public.fn_iu_create(v_a,'X','X','a');
IF v_i->>'status'!='exists_complete' THEN RAISE EXCEPTION 'idem: %',v_i->>'status'; END IF;
SELECT count(*) INTO v_c FROM public.information_unit WHERE canonical_address=v_a; IF v_c!=1 THEN RAISE EXCEPTION 'dbl'; END IF;
RAISE NOTICE 'ALL PASS: %',v_a;
END $$;
SELECT routine_schema,routine_name,grantee,privilege_type FROM information_schema.routine_privileges WHERE routine_schema='public' AND routine_name IN ('fn_iu_create','fn_iu_create_plan') ORDER BY routine_name,grantee;
SELECT proname,proowner::regrole AS owner,prosecdef,provolatile FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname='public' AND proname IN ('fn_iu_create','fn_iu_create_plan') ORDER BY proname;
COMMIT;
SELECT CASE WHEN COALESCE((v.vj->>'all_pass')::boolean,false) THEN 'PASS' ELSE 'CRITICAL' END AS post_commit_status,
v.vj AS verify_json,current_setting('app.p2_pilot_address') AS pilot_address
FROM (SELECT public.fn_iu_verify_invariants(current_setting('app.p2_pilot_address')) AS vj) v;
SELECT 'information_unit' AS tbl,count(*) FROM public.information_unit UNION ALL SELECT 'unit_version',count(*) FROM public.unit_version UNION ALL SELECT 'birth_iu',count(*) FROM public.birth_registry WHERE collection_name='information_unit' UNION ALL SELECT 'birth_total',count(*) FROM public.birth_registry;
EOSQL
PSQL_EXIT=$?
echo "PSQL_EXIT=$PSQL_EXIT"
fi # end INIT_EXIT check
# === Post-COMMIT verification (only if PSQL success) ===
if [ $PSQL_EXIT -eq 0 ]; then
set +e
# Post-commit invariant verify
POST_RESULT=$("${PSQL_RO[@]}" -v pilot_addr="$PILOT_ADDRESS" <<'POSTQL'
SELECT CASE WHEN COALESCE((v.vj->>'all_pass')::boolean,false) THEN 'PASS' ELSE 'CRITICAL' END FROM (SELECT public.fn_iu_verify_invariants(:'pilot_addr') AS vj) v;
POSTQL
)
POST_EXIT=$?
POST_COMMIT_STATUS=$(echo "$POST_RESULT" | tr -d '[:space:]')
# [rev12 Fix 3] Normalize: only PASS/CRITICAL valid; anything else = INVALID_OUTPUT
if [ "$POST_COMMIT_STATUS" != "PASS" ] && [ "$POST_COMMIT_STATUS" != "CRITICAL" ]; then
POST_COMMIT_STATUS="INVALID_OUTPUT"
fi
# [rev12 Fix 2] Function existence check — captured as machine-readable status
FCHECK_RESULT=$("${PSQL_RO[@]}" <<'FCHECK'
SELECT CASE WHEN bool_and(e) THEN 'PASS' ELSE 'CRITICAL' END FROM (
SELECT to_regprocedure('public.fn_iu_create(text,text,text,text,text,text,text,text,uuid)') IS NOT NULL AS e
UNION ALL
SELECT to_regprocedure('public.fn_iu_create_plan(text,text,text,text,text,text,text,text,uuid)') IS NOT NULL
) x;
FCHECK
)
FCHECK_EXIT=$?
FCHECK_STATUS=$(echo "$FCHECK_RESULT" | tr -d '[:space:]')
if [ "$FCHECK_STATUS" != "PASS" ] && [ "$FCHECK_STATUS" != "CRITICAL" ]; then
FCHECK_STATUS="INVALID_OUTPUT"
fi
set -uo pipefail
fi
# === Diagnostics (only if PSQL failed) ===
if [ $PSQL_EXIT -ne 0 ] && [ $PSQL_EXIT -ne 99 ]; then
echo "=== DIAGNOSTICS ==="
set +e
"${PSQL_RO[@]}" -v pilot_addr="$PILOT_ADDRESS" <<'DIAG'
SELECT 'fn_iu_create' AS c, to_regprocedure('public.fn_iu_create(text,text,text,text,text,text,text,text,uuid)') IS NOT NULL AS e
UNION ALL SELECT 'fn_iu_create_plan', to_regprocedure('public.fn_iu_create_plan(text,text,text,text,text,text,text,text,uuid)') IS NOT NULL
UNION ALL SELECT 'pilot_iu', EXISTS(SELECT 1 FROM public.information_unit WHERE canonical_address=:'pilot_addr')
UNION ALL SELECT 'pilot_birth', EXISTS(SELECT 1 FROM public.birth_registry WHERE collection_name='information_unit' AND entity_code IN (SELECT 'information_unit::'||id::text FROM public.information_unit WHERE canonical_address=:'pilot_addr'));
DIAG
DIAG_EXIT=$?
set -uo pipefail
fi
echo "LOG=$LOG_PATH"
cat "$LOG_PATH"
# === FINAL VERDICT ===
# [rev12 Fix 1] Requires ALL five conditions: INIT_EXIT=0, PSQL_EXIT=0, POST_EXIT=0, POST_COMMIT_STATUS=PASS, FCHECK_STATUS=PASS
# [rev12 Fix 4] init_error emitted as own field
echo "=== FINAL VERDICT ==="
echo "init_exit=$INIT_EXIT"
echo "init_error=$INIT_ERROR"
echo "sql_exit=$PSQL_EXIT"
echo "post_exit=$POST_EXIT"
echo "post_commit_status=$POST_COMMIT_STATUS"
echo "fcheck_exit=$FCHECK_EXIT"
echo "fcheck_status=$FCHECK_STATUS"
echo "diagnostics_exit=$DIAG_EXIT"
if [ $INIT_EXIT -eq 0 ] && [ $PSQL_EXIT -eq 0 ] && [ $POST_EXIT -eq 0 ] && [ "$POST_COMMIT_STATUS" = "PASS" ] && [ "$FCHECK_STATUS" = "PASS" ]; then
echo "phase_status=PASS"; echo "p3_readiness=READY"
else
echo "phase_status=FAIL"; echo "p3_readiness=BLOCKED"
[ $INIT_EXIT -ne 0 ] && echo "reason=INIT_FAIL: $INIT_ERROR"
[ $PSQL_EXIT -ne 0 ] && echo "reason=SQL_FAIL"
[ $POST_EXIT -ne 0 ] && echo "reason=POST_VERIFY_QUERY_FAIL"
[ "$POST_COMMIT_STATUS" = "CRITICAL" ] && echo "reason=POST_COMMIT_CRITICAL"
[ "$POST_COMMIT_STATUS" = "INVALID_OUTPUT" ] && echo "reason=POST_COMMIT_INVALID_OUTPUT"
[ "$FCHECK_STATUS" != "PASS" ] && [ "$FCHECK_STATUS" != "NOT_RUN" ] && echo "reason=FCHECK_$FCHECK_STATUS"
fi
echo "=== UPLOAD REPORT NOW ==="
echo "PILOT=$PILOT_ADDRESS PLAN1=$PLAN_ADDRESS_1 PLAN2=$PLAN_ADDRESS_2"
Report (MUST upload)
Include: init_exit, init_error, sql_exit, post_exit, post_commit_status, fcheck_exit, fcheck_status, diag_exit, phase_status, p3_readiness, LOG excerpt, addresses, preflight JSON, column types, unique/FK, functions (owner/secdef/volatile), plan tests, pilot (3-layer verify: fn_iu_create internal invariant verify → pilot post-create verify → post-commit verify), permissions, publication_type explicit-only, pilot retained, auxiliary engine note.
Existing State Semantics (rev12 Fix 5)
exists_complete= idempotent success.exists_missing_birth,exists_missing_version,exists_anchor_invalid,exists_duplicate_version,exists_unknown_state= health/remediation states, NOT success.- Adapters must NOT treat these as successful creation.
- These states require health/remediation review;
fn_iu_createdoes not call backfill or repair them. - The main/auxiliary boundary:
fn_iu_createis the main engine (complete-or-nothing). Backfill, repair, orphan remediation belong to auxiliary engines (DOT-118, birth health checks, etc.).
Hard Boundaries
- ❌ No raw birth / No OR REPLACE / No ASSERT / No manual substitution
- ❌ No global set -e (intentional) / No bare unique-index / No created_birth_missing
- ❌ No early exit before report / No post-verify if SQL failed
- ❌ No DOT/adapter/seed/Pack 2C / No retry / No cleanup
22-P2 rev12 | 2026-05-06 | 5 GPT fixes applied. Shell verdict strict. Chờ GPT final yes/no.