23-P3D1 — Notification Schema + Query Functions — Execution Report

Date: 2026-05-08 00:51 UTC Prompt: knowledge/dev/laws/dieu44-trien-khai/prompts/23-p3d1-notification-schema-functions-prompt.md (rev3) Executor: Opus 4.7 Phase status: PASS

Result Matrix

Phase	Status
Preflight	PASS
DDL	OK
Functions	OK
Pilot creation	OK
Tests	16/16 PASS (TEST_FAIL=0)
Manual events cleanup	PASS
Phase status	PASS
P3D2 readiness	READY

Key Outputs

• phase_status=PASS
• ddl=OK fn=OK test_fail=0
• manual_events_cleanup=PASS
• p23_unchanged=true
• p23_count=10→10
• owner=directus
• test_addr=test/p3d1/pilot-20260508-005109
• test_unit_id=60c0017d-4d5d-4003-a403-76e85f869557
• test_draft_id=40381e69-12c1-42b0-965d-ce68ca7c11a8
• pilot_rows_retained=true
• idempotency_mode=strict_fail_if_exists
• next_required_pack=P3D2_NOTIFICATION_TRIGGERS
• LOG=/tmp/23-p3d1.20260508-005109.log

Test Detail (16 groups)

Test	Description	Result
T1	Schema column counts (event ≥10, read ≥4)	PASS
T2	7 CHECK constraints present	PASS
T3	FK fk_notif_event_unit + fk_notif_read_event (ON DELETE CASCADE)	PASS
T4	6 indexes including partial unique uq_notif_event_type_ref	PASS
T5	fn_iu_unread + fn_iu_mark_read both registered	PASS
T6	SECURITY DEFINER + search_path + owner + grantees + no PUBLIC EXECUTE	PASS
T7	Empty-state: tables empty, fn_iu_unread returns 0	PASS
T7B	Manual seed: 3 events inserted (1 per stream), source='test_p3d1'	PASS
T8	fn_iu_unread('gpt') returns 3	PASS
T9	fn_iu_unread('gpt','review') returns 1	PASS
T10	self-exclude default; include_self=true returns all 3	PASS
T11	mark_read with 4 ids (1 dup) → req=4 dist=3 new=3 alr=0	PASS
T11B	second mark_read idempotent: new=0 alr=3, unread=0	PASS
T12	unknown uuid → unknown_count=1	PASS
T13	limit=1 honored	PASS
T14	P23 hashes unchanged, count=10	PASS
T15	No triggers on notification or source tables	PASS
T16	fn_iu_unread / fn_iu_mark_read have no writes to information_unit / unit_version	PASS

DDL Created

Tables: iu_notification_event, iu_notification_read.

Constraints: chk_notif_event_type, chk_notif_event_stream, chk_notif_event_type_stream, 4× *_nonempty CHECKs, fk_notif_event_unit, fk_notif_read_event (ON DELETE CASCADE).

Indexes: uq_notif_event_type_ref (UNIQUE partial WHERE ref_id IS NOT NULL), idx_notif_event_stream_created, idx_notif_event_unit_created, idx_notif_event_created, uq_notif_read_event_actor (UNIQUE), idx_notif_read_actor_event.

Functions Created

• 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 — STABLE SECURITY DEFINER search_path=pg_catalog,public.
• public.fn_iu_mark_read(p_event_ids uuid[], p_actor text) RETURNS jsonb — VOLATILE SECURITY DEFINER search_path=pg_catalog,public. 2-step (count then insert), idempotent via unique (event_id, actor_ref).

REVOKE FROM PUBLIC; GRANT EXECUTE TO directus.

Hard Boundaries Verified

• No triggers on source tables (T15)
• No Pack 23 function changes (T14)
• No vector / LISTEN/NOTIFY / retention / external queue / global read flag
• 2 tables + 6 indexes + 2 functions only

Notes

• Pilot IU creation emitted L1 PILOT-ONLY birth gate warnings ("P-pub1/P-pub2 missing — production sẽ BLOCK"). Expected for test/p3d1/pilot-* addresses; does not affect outcome. Pilot rows retained per spec for P3D2 reuse.
• fn_iu_save returns status (not unit_id) on create — rev3-F2 fix correctly queries information_unit by canonical_address afterward.
• Manual test events (3) and their read rows cleaned on PASS path; pilot IU + draft retained.

Next Steps

• P3D2 — Notification Triggers (next required pack): wire AFTER INSERT triggers on unit_edit_comment, unit_edit_draft, unit_version to populate iu_notification_event.
• Pilot artifacts retained for P3D2 reuse: unit_id=60c0017d-4d5d-4003-a403-76e85f869557, draft_id=40381e69-12c1-42b0-965d-ce68ca7c11a8, address=test/p3d1/pilot-20260508-005109.

---

P3D1 rev3 | PASS | 16/16 tests | P23 unchanged | LOG=/tmp/23-p3d1.20260508-005109.log on VPS