KB-1E03 rev 3
23-P3D1 — Notification Schema + Query Functions — Execution Prompt (rev3)
25 min read Revision 3
23-P3D1 — Notification Schema + Query Functions — Execution Prompt (rev3)
Date: 2026-05-07 Status: PROMPT rev3 — GPT 14 fixes. Chờ final review. CHƯA dispatch. Design: knowledge/dev/laws/dieu44-trien-khai/design/23-p3d-notification-outbox-design-note.md (rev3) Report: knowledge/dev/laws/dieu44-trien-khai/reports/23-p3d1-notification-schema-functions-report.md Rev2→Rev3: preflight read-only, pilot after DDL, uuid[] via SQL, mark_read 2-step, cleanup verify, policy check.
Hard Boundaries
- ❌ No triggers on source tables
- ❌ No Pack 23 function changes (T14)
- ❌ No vector / LISTEN/NOTIFY / retention / external queue / global read flag
- ✅ 2 tables + indexes + constraints + 2 functions only
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-p3d1.${TS}.log"
exec > >(tee -a "$LOG") 2>&1
PREFLIGHT_STATUS=""; DDL_STATUS="NOT_RUN"; FN_STATUS="NOT_RUN"; TEST_FAIL=0; PHASE_STATUS=""
P23_HASHES_BEFORE=""; P23_HASHES_AFTER=""; P23_COUNT_BEFORE=""; P23_COUNT_AFTER=""
FN_OWNER=""; FN_GRANTEES=""
TEST_ADDR="test/p3d1/pilot-${TS}"; TEST_UNIT_ID=""; TEST_DRAFT_ID=""
MANUAL_EVENTS_CLEANUP="NOT_RUN"; P3D2_READINESS="BLOCKED"
US=$'\x1f'
echo "=== P3D1 START $TS ==="
§1. Preflight (READ-ONLY — rev3-F1)
echo "=== PREFLIGHT ==="
# P23 protected functions = 10
P23_HASHES_BEFORE=$("${PSQL[@]}" -t -A -c "
SELECT string_agg(p.proname||'='||md5(p.prosrc),'|' ORDER BY p.proname)
FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid
WHERE n.nspname='public' AND p.proname 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','fn_iu_create','fn_content_hash','fn_iu_verify_invariants','fn_iu_save');")
P23_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 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','fn_iu_create','fn_content_hash','fn_iu_verify_invariants','fn_iu_save');" | tr -d ' ')
[ "$P23_COUNT_BEFORE" = "10" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: P23=$P23_COUNT_BEFORE"; }
# Tables must NOT exist
if [ -z "$PREFLIGHT_STATUS" ]; then
for TBL in iu_notification_event iu_notification_read; do
E=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND tablename='$TBL';" | tr -d ' ')
[ "$E" = "0" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: $TBL exists"; }
done
fi
# Source tables
if [ -z "$PREFLIGHT_STATUS" ]; then
for TBL in information_unit unit_edit_draft unit_edit_comment unit_version; 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 missing"; }
done
fi
# rev3-F10: Policy must be require_review
if [ -z "$PREFLIGHT_STATUS" ]; then
POL=$("${PSQL[@]}" -t -A -c "SELECT value FROM dot_config WHERE key='iu_edit.policy.default_mode';")
[ "$POL" = "require_review" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: policy=$POL exp=require_review"; }
fi
# Owner/grantees
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_save';")
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_save' AND x.privilege_type='EXECUTE' AND x.grantee!=0;" 2>/dev/null)
[ -z "$FN_GRANTEES" ] && FN_GRANTEES="$FN_OWNER"
[ -n "$FN_OWNER" ] || { PREFLIGHT_STATUS="FAIL"; echo "FAIL: owner"; }
echo "OWNER=$FN_OWNER GRANTEES=$FN_GRANTEES"
fi
if [ -z "$PREFLIGHT_STATUS" ]; then PREFLIGHT_STATUS="PASS"; fi
echo "PREFLIGHT=$PREFLIGHT_STATUS"
§2. DDL
if [ "$PREFLIGHT_STATUS" = "PASS" ]; then
echo "=== DDL ==="
DDL_EXIT=0
"${PSQL[@]}" <<'DDLSQL' || DDL_EXIT=$?
BEGIN;
CREATE TABLE iu_notification_event (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
event_type text NOT NULL CONSTRAINT chk_notif_event_type CHECK (event_type IN ('comment_added','draft_created','version_applied')),
event_stream text NOT NULL CONSTRAINT chk_notif_event_stream CHECK (event_stream IN ('comment','review','update')),
unit_id uuid NOT NULL CONSTRAINT fk_notif_event_unit REFERENCES information_unit(id),
canonical_address text NOT NULL CONSTRAINT chk_notif_event_canonical_address_nonempty CHECK (btrim(canonical_address)!=''),
ref_id uuid,
actor_ref text NOT NULL CONSTRAINT chk_notif_event_actor_ref_nonempty CHECK (btrim(actor_ref)!=''),
source text NOT NULL CONSTRAINT chk_notif_event_source_nonempty CHECK (btrim(source)!=''),
payload jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT chk_notif_event_type_stream CHECK (
(event_type='comment_added' AND event_stream='comment') OR
(event_type='draft_created' AND event_stream='review') OR
(event_type='version_applied' AND event_stream='update'))
);
CREATE TABLE iu_notification_read (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
event_id uuid NOT NULL CONSTRAINT fk_notif_read_event REFERENCES iu_notification_event(id) ON DELETE CASCADE,
actor_ref text NOT NULL CONSTRAINT chk_notif_read_actor_ref_nonempty CHECK (btrim(actor_ref)!=''),
read_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX uq_notif_event_type_ref ON iu_notification_event(event_type,ref_id) WHERE ref_id IS NOT NULL;
CREATE INDEX idx_notif_event_stream_created ON iu_notification_event(event_stream,created_at DESC);
CREATE INDEX idx_notif_event_unit_created ON iu_notification_event(unit_id,created_at DESC);
CREATE INDEX idx_notif_event_created ON iu_notification_event(created_at DESC);
CREATE UNIQUE INDEX uq_notif_read_event_actor ON iu_notification_read(event_id,actor_ref);
CREATE INDEX idx_notif_read_actor_event ON iu_notification_read(actor_ref,event_id);
COMMIT;
DDLSQL
echo "DDL_EXIT=$DDL_EXIT"
[ "$DDL_EXIT" = "0" ] && DDL_STATUS="OK" || { DDL_STATUS="FAIL"; PHASE_STATUS="FAIL"; }
fi
§3. Functions (rev3-F5: mark_read 2-step)
if [ "$DDL_STATUS" = "OK" ]; then
echo "=== FUNCTIONS ==="
FN_EXIT=0
"${PSQL[@]}" -v fn_owner="$FN_OWNER" -v fn_grantees="$FN_GRANTEES" <<'FNSQL' || FN_EXIT=$?
BEGIN;
SELECT set_config('app.p3d1_grantees',:'fn_grantees',true);
CREATE FUNCTION public.fn_iu_unread(
p_actor text, p_stream text DEFAULT NULL,
p_include_self boolean DEFAULT false, p_limit integer DEFAULT 50
) RETURNS SETOF jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path=pg_catalog,public AS $fn$
DECLARE v_lim int;
BEGIN
IF btrim(COALESCE(p_actor,''))='' THEN
RETURN QUERY SELECT jsonb_build_object('status','invalid_input','field','actor'); RETURN;
END IF;
IF p_stream IS NOT NULL AND p_stream NOT IN ('comment','review','update') THEN
RETURN QUERY SELECT jsonb_build_object('status','invalid_input','field','stream'); RETURN;
END IF;
v_lim := GREATEST(1, LEAST(COALESCE(p_limit,50), 500));
RETURN QUERY
SELECT jsonb_build_object(
'event_id',e.id,'event_type',e.event_type,'stream',e.event_stream,
'unit_id',e.unit_id,'address',e.canonical_address,'ref_id',e.ref_id,
'actor',e.actor_ref,'created_at',e.created_at,
'next_action',CASE e.event_type WHEN 'draft_created' THEN 'fn_iu_apply_edit_draft'
WHEN 'comment_added' THEN 'inspect_comment' WHEN 'version_applied' THEN 'inspect_version' END,
'guidance',CASE e.event_type WHEN 'draft_created' THEN 'Draft awaiting review.'
WHEN 'comment_added' THEN 'New comment.' WHEN 'version_applied' THEN 'Content updated.' END
)
FROM public.iu_notification_event e
LEFT JOIN public.iu_notification_read r ON r.event_id=e.id AND r.actor_ref=p_actor
LEFT JOIN public.unit_edit_draft d ON e.event_type='draft_created' AND d.id=e.ref_id
WHERE r.id IS NULL
AND (p_include_self OR e.actor_ref!=p_actor)
AND (p_stream IS NULL OR e.event_stream=p_stream)
AND (e.event_type!='draft_created' OR d.draft_status='open')
ORDER BY e.created_at DESC LIMIT v_lim;
END;$fn$;
-- rev3-F5: 2-step mark_read (counts before INSERT for clarity)
CREATE FUNCTION public.fn_iu_mark_read(
p_event_ids uuid[], p_actor text
) RETURNS jsonb LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path=pg_catalog,public AS $fn$
DECLARE v_req int; v_distinct int; v_existing int; v_already int; v_newly int; v_actor text;
BEGIN
v_actor := btrim(COALESCE(p_actor,''));
IF v_actor='' THEN RETURN jsonb_build_object('status','invalid_input','field','actor'); END IF;
IF p_event_ids IS NULL OR array_length(p_event_ids,1) IS NULL THEN
RETURN jsonb_build_object('status','invalid_input','field','event_ids');
END IF;
v_req := array_length(p_event_ids,1);
-- Step 1: counts (before any mutation)
SELECT count(*) INTO v_distinct FROM (SELECT DISTINCT unnest(p_event_ids)) x;
SELECT count(*) INTO v_existing FROM (SELECT DISTINCT unnest(p_event_ids) eid) i
JOIN public.iu_notification_event e ON e.id=i.eid;
SELECT count(*) INTO v_already FROM public.iu_notification_read r
WHERE r.actor_ref=v_actor AND r.event_id=ANY(
SELECT DISTINCT unnest(p_event_ids) INTERSECT SELECT id FROM public.iu_notification_event);
-- Step 2: insert
WITH to_insert AS (
SELECT DISTINCT unnest(p_event_ids) AS eid
INTERSECT SELECT id FROM public.iu_notification_event
),
inserted AS (
INSERT INTO public.iu_notification_read(event_id,actor_ref)
SELECT eid, v_actor FROM to_insert
ON CONFLICT (event_id,actor_ref) DO NOTHING RETURNING event_id
)
SELECT count(*) INTO v_newly FROM inserted;
RETURN jsonb_build_object('status','marked',
'requested_count',v_req,'distinct_requested_count',v_distinct,
'existing_count',v_existing,'already_marked_count',v_already,
'newly_marked_count',v_newly,'unknown_count',v_distinct-v_existing,
'actor_ref',v_actor);
END;$fn$;
REVOKE ALL ON FUNCTION public.fn_iu_unread(text,text,boolean,integer) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.fn_iu_mark_read(uuid[],text) FROM PUBLIC;
DO $$DECLARE v_g text:=current_setting('app.p3d1_grantees');v_r text;
BEGIN FOREACH v_r IN ARRAY string_to_array(v_g,',') LOOP v_r:=btrim(v_r);
IF v_r<>'' THEN
EXECUTE format('GRANT EXECUTE ON FUNCTION public.fn_iu_unread(text,text,boolean,integer) TO %I',v_r);
EXECUTE format('GRANT EXECUTE ON FUNCTION public.fn_iu_mark_read(uuid[],text) TO %I',v_r);
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
§4. Create Pilot (AFTER DDL+FN — rev3-F1/F2)
if [ "$FN_STATUS" = "OK" ]; then
echo "=== PILOT ==="
# Create official v1
PILOT_RAW=$("${PSQL[@]}" -t -A -c "SELECT (public.fn_iu_save('$TEST_ADDR','P3D1 pilot $TS','agent:p3d1'))->>'status';")
echo "PILOT_CREATE=$PILOT_RAW"
# Query unit_id by address (rev3-F2: don't assume fn_iu_save returns unit_id)
TEST_UNIT_ID=$("${PSQL[@]}" -t -A -c "SELECT id FROM information_unit WHERE canonical_address='$TEST_ADDR';")
[ -n "$TEST_UNIT_ID" ] || { PHASE_STATUS="FAIL"; echo "FAIL: pilot unit not found"; }
# Create draft
if [ -z "$PHASE_STATUS" ]; then
DRAFT_RAW=$("${PSQL[@]}" -t -A -c "SELECT (public.fn_iu_save('$TEST_ADDR','P3D1 draft $TS','agent:p3d1',NULL,NULL,'draft'))->>'draft_id';")
TEST_DRAFT_ID="$DRAFT_RAW"
[ -n "$TEST_DRAFT_ID" ] || { PHASE_STATUS="FAIL"; echo "FAIL: pilot draft"; }
fi
echo "UNIT=$TEST_UNIT_ID DRAFT=$TEST_DRAFT_ID"
fi
§5. Tests
if [ "$FN_STATUS" = "OK" ] && [ -z "$PHASE_STATUS" ]; then
echo "=== TESTS ==="
# T1-T4: Schema (same as rev2, condensed)
T1_E=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='iu_notification_event';" | tr -d ' ')
T1_R=$("${PSQL[@]}" -t -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='iu_notification_read';" | tr -d ' ')
[ "$T1_E" -ge "10" ] && [ "$T1_R" -ge "4" ] && echo "T1=PASS" || { echo "T1=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }
T2="PASS"
for CN in chk_notif_event_type chk_notif_event_stream chk_notif_event_type_stream chk_notif_event_canonical_address_nonempty chk_notif_event_actor_ref_nonempty chk_notif_event_source_nonempty chk_notif_read_actor_ref_nonempty; do
E=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_constraint WHERE conname='$CN';" | tr -d ' ')
[ "$E" = "1" ] || { T2="FAIL_$CN"; TEST_FAIL=$((TEST_FAIL+1)); }
done
echo "T2=$T2"
T3_E=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_constraint WHERE conname='fk_notif_event_unit' AND contype='f';" | tr -d ' ')
T3_R=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_constraint WHERE conname='fk_notif_read_event' AND contype='f';" | tr -d ' ')
T3_C=$("${PSQL[@]}" -t -A -c "SELECT confdeltype FROM pg_constraint WHERE conname='fk_notif_read_event';")
[ "$T3_E" = "1" ] && [ "$T3_R" = "1" ] && [ "$T3_C" = "c" ] && echo "T3=PASS" || { echo "T3=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }
T4="PASS"
for IDX in uq_notif_event_type_ref idx_notif_event_stream_created idx_notif_event_unit_created idx_notif_event_created uq_notif_read_event_actor idx_notif_read_actor_event; do
E=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_indexes WHERE schemaname='public' AND indexname='$IDX';" | tr -d ' ')
[ "$E" = "1" ] || { T4="FAIL_$IDX"; TEST_FAIL=$((TEST_FAIL+1)); }
done
T4P=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_index i JOIN pg_class c ON c.oid=i.indexrelid WHERE c.relname='uq_notif_event_type_ref' AND i.indpred IS NOT NULL;" | tr -d ' ')
[ "$T4P" = "1" ] || { T4="FAIL_PARTIAL"; TEST_FAIL=$((TEST_FAIL+1)); }
echo "T4=$T4"
# T5-T6: Functions + security (condensed)
T5A=$("${PSQL[@]}" -t -A -c "SELECT to_regprocedure('public.fn_iu_unread(text,text,boolean,integer)');")
T5B=$("${PSQL[@]}" -t -A -c "SELECT to_regprocedure('public.fn_iu_mark_read(uuid[],text)');")
[ -n "$T5A" ] && [ "$T5A" != "-" ] && [ -n "$T5B" ] && [ "$T5B" != "-" ] && echo "T5=PASS" || { echo "T5=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }
T6="PASS"
for SIG in "fn_iu_unread(text,text,boolean,integer)" "fn_iu_mark_read(uuid[],text)"; do
META=$("${PSQL[@]}" -t -A -F "$US" -c "SELECT prosecdef,proconfig,proowner::regrole FROM pg_proc WHERE oid=to_regprocedure('public.$SIG');")
SD=$(echo "$META" | cut -d"$US" -f1); PC=$(echo "$META" | cut -d"$US" -f2); OWN=$(echo "$META" | cut -d"$US" -f3)
[ "$SD" = "t" ] || T6="FAIL_SD"; echo "$PC" | grep -q "pg_catalog" || T6="FAIL_SP"; [ "$OWN" = "$FN_OWNER" ] || T6="FAIL_OWN"
done
PUB=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_proc p LEFT JOIN LATERAL aclexplode(p.proacl) x ON true WHERE p.proname IN('fn_iu_unread','fn_iu_mark_read') AND p.pronamespace='public'::regnamespace AND x.grantee=0 AND x.privilege_type='EXECUTE';" | tr -d ' ')
[ "$PUB" = "0" ] || T6="FAIL_PUB"
IFS=',' read -ra GR <<< "$FN_GRANTEES"
for R in "${GR[@]}"; do
R=$(echo "$R" | tr -d ' '); [ -z "$R" ] && continue
for SIG in "public.fn_iu_unread(text,text,boolean,integer)" "public.fn_iu_mark_read(uuid[],text)"; do
CAN=$("${PSQL_NOSTOP[@]}" -v role="$R" -v sig="$SIG" -t -A <<'CANSQL'
SELECT has_function_privilege(:'role',:'sig','EXECUTE');
CANSQL
)
[ "$CAN" = "t" ] || T6="FAIL_GR_$R"
done
done
echo "T6=$T6"; [ "$T6" = "PASS" ] || TEST_FAIL=$((TEST_FAIL+1))
# T7: Empty
T7_TBL=$("${PSQL[@]}" -t -c "SELECT count(*) FROM iu_notification_event;" | tr -d ' ')
T7_FN=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM fn_iu_unread('gpt');")
[ "$T7_TBL" = "0" ] && [ "$T7_FN" = "0" ] && echo "T7=PASS" || { echo "T7=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }
# Insert manual events
"${PSQL[@]}" <<EOSQL
INSERT INTO iu_notification_event(event_type,event_stream,unit_id,canonical_address,ref_id,actor_ref,source) VALUES
('comment_added','comment','$TEST_UNIT_ID','$TEST_ADDR',gen_random_uuid(),'agent:opus','test_p3d1'),
('draft_created','review','$TEST_UNIT_ID','$TEST_ADDR','$TEST_DRAFT_ID','agent:opus','test_p3d1'),
('version_applied','update','$TEST_UNIT_ID','$TEST_ADDR',gen_random_uuid(),'agent:codex','test_p3d1');
EOSQL
# rev3-F6: Assert exact counts
T7B=$("${PSQL[@]}" -t -c "SELECT count(*) FROM iu_notification_event;" | tr -d ' ')
T7C=$("${PSQL[@]}" -t -c "SELECT count(*) FROM iu_notification_event WHERE source='test_p3d1';" | tr -d ' ')
[ "$T7B" = "3" ] && [ "$T7C" = "3" ] && echo "T7B=PASS" || { echo "T7B=FAIL total=$T7B src=$T7C"; TEST_FAIL=$((TEST_FAIL+1)); }
# T8: unread sees 3 for gpt
T8=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM fn_iu_unread('gpt');")
[ "$T8" = "3" ] && echo "T8=PASS" || { echo "T8=FAIL=$T8"; TEST_FAIL=$((TEST_FAIL+1)); }
# T9: stream filter
T9=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM fn_iu_unread('gpt','review');")
[ "$T9" = "1" ] && echo "T9=PASS" || { echo "T9=FAIL=$T9"; TEST_FAIL=$((TEST_FAIL+1)); }
# T10: self-exclude
T10A=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM fn_iu_unread('agent:opus');")
T10B=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM fn_iu_unread('agent:opus',NULL,true);")
[ "$T10A" = "1" ] && [ "$T10B" = "3" ] && echo "T10=PASS" || { echo "T10=FAIL a=$T10A b=$T10B"; TEST_FAIL=$((TEST_FAIL+1)); }
# T11: mark_read (rev3-F3: uuid[] via SQL, F4: duplicate handling)
T11_RAW=$("${PSQL[@]}" -t -A -F "$US" <<'EOSQL'
WITH ids AS (SELECT array_agg(id)::uuid[] || (SELECT id FROM iu_notification_event WHERE source='test_p3d1' LIMIT 1) AS a FROM iu_notification_event WHERE source='test_p3d1'),
r AS (SELECT fn_iu_mark_read((SELECT a FROM ids),'gpt') AS j)
SELECT j->>'requested_count', j->>'distinct_requested_count', j->>'newly_marked_count', j->>'already_marked_count', j->>'unknown_count' FROM r;
EOSQL
)
T11_REQ=$(echo "$T11_RAW" | cut -d"$US" -f1); T11_DIST=$(echo "$T11_RAW" | cut -d"$US" -f2)
T11_NEW=$(echo "$T11_RAW" | cut -d"$US" -f3); T11_ALR=$(echo "$T11_RAW" | cut -d"$US" -f4)
echo "T11: req=$T11_REQ dist=$T11_DIST new=$T11_NEW alr=$T11_ALR"
# 3 events + 1 duplicate = req=4, dist=3, new=3, alr=0
[ "$T11_REQ" = "4" ] && [ "$T11_DIST" = "3" ] && [ "$T11_NEW" = "3" ] && [ "$T11_ALR" = "0" ] && echo "T11=PASS" || { echo "T11=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }
# T11B: idempotent second call
T11B_RAW=$("${PSQL[@]}" -t -A -F "$US" <<'EOSQL'
WITH ids AS (SELECT array_agg(id)::uuid[] AS a FROM iu_notification_event WHERE source='test_p3d1'),
r AS (SELECT fn_iu_mark_read((SELECT a FROM ids),'gpt') AS j)
SELECT j->>'newly_marked_count', j->>'already_marked_count' FROM r;
EOSQL
)
T11B_NEW=$(echo "$T11B_RAW" | cut -d"$US" -f1); T11B_ALR=$(echo "$T11B_RAW" | cut -d"$US" -f2)
T11B_UNREAD=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM fn_iu_unread('gpt');")
[ "$T11B_NEW" = "0" ] && [ "$T11B_ALR" = "3" ] && [ "$T11B_UNREAD" = "0" ] && echo "T11B=PASS" || { echo "T11B=FAIL new=$T11B_NEW alr=$T11B_ALR unread=$T11B_UNREAD"; TEST_FAIL=$((TEST_FAIL+1)); }
# T12: unknown event
T12_RAW=$("${PSQL[@]}" -t -A -F "$US" <<'EOSQL'
WITH r AS (SELECT fn_iu_mark_read(ARRAY['00000000-0000-0000-0000-000000000000'::uuid],'gpt') AS j)
SELECT j->>'unknown_count' FROM r;
EOSQL
)
[ "$T12_RAW" = "1" ] && echo "T12=PASS" || { echo "T12=FAIL=$T12_RAW"; TEST_FAIL=$((TEST_FAIL+1)); }
# T13: limit
T13=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM fn_iu_unread('agent:codex',NULL,true,1);")
[ "$T13" -le "1" ] && echo "T13=PASS" || { echo "T13=FAIL"; TEST_FAIL=$((TEST_FAIL+1)); }
# T14: P23 hashes
P23_HASHES_AFTER=$("${PSQL[@]}" -t -A -c "
SELECT string_agg(p.proname||'='||md5(p.prosrc),'|' ORDER BY p.proname)
FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid
WHERE n.nspname='public' AND p.proname 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','fn_iu_create','fn_content_hash','fn_iu_verify_invariants','fn_iu_save');")
P23_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 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','fn_iu_create','fn_content_hash','fn_iu_verify_invariants','fn_iu_save');" | tr -d ' ')
T14="PASS"
[ "$P23_HASHES_AFTER" = "$P23_HASHES_BEFORE" ] || { T14="FAIL_HASH"; TEST_FAIL=$((TEST_FAIL+1)); }
[ "$P23_COUNT_AFTER" = "10" ] || { T14="FAIL_COUNT"; TEST_FAIL=$((TEST_FAIL+1)); }
echo "T14=$T14"
# T15: no triggers
T15=$("${PSQL[@]}" -t -c "SELECT count(*) FROM pg_trigger t JOIN pg_class c ON t.tgrelid=c.oid WHERE c.relname IN('iu_notification_event','iu_notification_read','unit_edit_comment','unit_edit_draft','unit_version') AND NOT t.tgisinternal AND t.tgname LIKE 'trg%iu_notif%';" | tr -d ' ')
[ "$T15" = "0" ] && echo "T15=PASS" || { echo "T15=FAIL=$T15"; TEST_FAIL=$((TEST_FAIL+1)); }
# T16: source safety + DELETE (rev3-F13)
T16="PASS"
for FN in fn_iu_unread fn_iu_mark_read; do
PROBE=$("${PSQL[@]}" -t -A -F "$US" <<EOSQL
SELECT prosrc ~* 'insert[[:space:]]+into[[:space:]]+(public\.)?(information_unit|unit_version)',
prosrc ~* 'update[[:space:]]+(public\.)?(information_unit|unit_version)',
prosrc ~* 'delete[[:space:]]+from[[:space:]]+(public\.)?(information_unit|unit_version)',
prosrc ~* 'canonical_writer'
FROM pg_proc WHERE proname='$FN' AND pronamespace='public'::regnamespace;
EOSQL
)
P1=$(echo "$PROBE" | cut -d"$US" -f1); P2=$(echo "$PROBE" | cut -d"$US" -f2)
P3=$(echo "$PROBE" | cut -d"$US" -f3); P4=$(echo "$PROBE" | cut -d"$US" -f4)
[ "$P1" = "f" ] && [ "$P2" = "f" ] && [ "$P3" = "f" ] && [ "$P4" = "f" ] || { T16="FAIL_$FN"; TEST_FAIL=$((TEST_FAIL+1)); }
done
echo "T16=$T16"
echo "TEST_FAIL=$TEST_FAIL"
fi
§6. Cleanup + Final
# rev3-F7: Cleanup manual events on PASS + verify
if [ "$TEST_FAIL" = "0" ] && [ "$FN_STATUS" = "OK" ] && [ -z "$PHASE_STATUS" ]; then
echo "=== CLEANUP MANUAL ==="
"${PSQL[@]}" -c "DELETE FROM iu_notification_read WHERE event_id IN (SELECT id FROM iu_notification_event WHERE source='test_p3d1');"
"${PSQL[@]}" -c "DELETE FROM iu_notification_event WHERE source='test_p3d1';"
REMAINING=$("${PSQL[@]}" -t -c "SELECT count(*) FROM iu_notification_event WHERE source='test_p3d1';" | tr -d ' ')
[ "$REMAINING" = "0" ] && MANUAL_EVENTS_CLEANUP="PASS" || { MANUAL_EVENTS_CLEANUP="FAIL"; PHASE_STATUS="FAIL"; echo "FAIL: manual events not cleaned=$REMAINING"; }
fi
# On FAIL: drop everything
if { [ "$TEST_FAIL" != "0" ] || [ "$PHASE_STATUS" = "FAIL" ]; } && [ "$DDL_STATUS" = "OK" ]; then
echo "=== CLEANUP FAIL ==="; DROP_FAIL=0
"${PSQL_NOSTOP[@]}" -c "DROP FUNCTION IF EXISTS public.fn_iu_mark_read(uuid[],text);" 2>&1 || DROP_FAIL=1
"${PSQL_NOSTOP[@]}" -c "DROP FUNCTION IF EXISTS public.fn_iu_unread(text,text,boolean,integer);" 2>&1 || DROP_FAIL=1
"${PSQL_NOSTOP[@]}" -c "DROP TABLE IF EXISTS public.iu_notification_read CASCADE;" 2>&1 || DROP_FAIL=1
"${PSQL_NOSTOP[@]}" -c "DROP TABLE IF EXISTS public.iu_notification_event CASCADE;" 2>&1 || DROP_FAIL=1
[ "$DROP_FAIL" = "0" ] && echo "cleanup=DROPPED" || { echo "cleanup=CRITICAL"; PHASE_STATUS="CRITICAL"; }
fi
echo "=== FINAL ==="
if [ "$PHASE_STATUS" = "CRITICAL" ]; then true
elif [ -z "$PHASE_STATUS" ] && [ "$TEST_FAIL" = "0" ] && [ "$MANUAL_EVENTS_CLEANUP" = "PASS" ]; then PHASE_STATUS="PASS"
elif [ -z "$PHASE_STATUS" ]; then PHASE_STATUS="FAIL"; fi
# rev3-F14
[ "$PHASE_STATUS" = "PASS" ] && P3D2_READINESS="READY" || P3D2_READINESS="BLOCKED"
echo "phase_status=$PHASE_STATUS"
echo "ddl=$DDL_STATUS fn=$FN_STATUS test_fail=$TEST_FAIL"
echo "manual_events_cleanup=$MANUAL_EVENTS_CLEANUP"
echo "p3d2_readiness=$P3D2_READINESS"
echo "p23_unchanged=$([ "$P23_HASHES_AFTER" = "$P23_HASHES_BEFORE" ] && echo true || echo false)"
echo "p23_count=$P23_COUNT_BEFORE→$P23_COUNT_AFTER"
echo "owner=$FN_OWNER"
echo "test_addr=$TEST_ADDR test_unit_id=$TEST_UNIT_ID test_draft_id=$TEST_DRAFT_ID"
echo "pilot_rows_retained=true"
echo "idempotency_mode=strict_fail_if_exists"
echo "next_required_pack=P3D2_NOTIFICATION_TRIGGERS"
echo "LOG=$LOG"
echo "=== AGENT: UPLOAD REPORT ==="
echo "Path: knowledge/dev/laws/dieu44-trien-khai/reports/23-p3d1-notification-schema-functions-report.md"
echo "Upload even on FAIL/CRITICAL."
P3D1 rev3 | preflight read-only, pilot after DDL, uuid[] via SQL, 2-step mark_read, cleanup verify | 16+ tests | CHƯA dispatch | Chờ GPT/User review