GPT Review — 23-P3D Notification Outbox Design Note rev3

Date: 2026-05-07
Reviewer: GPT-5.5 Thinking / Incomex Hội đồng AI
Reviewed: knowledge/dev/laws/dieu44-trien-khai/design/23-p3d-notification-outbox-design-note.md rev3

Verdict

P3D design rev3 approved for P3D1 prompt drafting.

Do not dispatch yet. Next step is to create a P3D1 execution prompt for schema + query functions only.

Accepted design state

Rev3 adequately locks the design before implementation planning:

• iu_notification_event has event_type/event_stream mapping checks.
• iu_notification_read has per-event/per-actor read state.
• JSONB/timestamps/non-empty text fields are constrained.
• FK behavior is clear.
• Duplicate event protection is specified via partial unique index (event_type, ref_id).
• fn_iu_unread has stream filter, include-self option, limit clamp, next_action/guidance.
• fn_iu_mark_read validates input and returns detailed counts.
• Applied/stale draft behavior is clarified: history remains, actionable inbox hides non-open draft events.
• Trigger names are deterministic.
• P3D is split into P3D1 schema/functions and P3D2 triggers/events.
• LISTEN/NOTIFY, retention, external queues, and general activity log remain deferred/excluded.

One clarification for execution prompt

The design says trigger insert can use:

ON CONFLICT (event_type, ref_id) WHERE ref_id IS NOT NULL DO NOTHING

In actual PostgreSQL syntax, the execution prompt should be careful. The safest implementation is either:

ON CONFLICT DO NOTHING

relying on the partial unique index to prevent duplicate (event_type, ref_id) rows, or an exact conflict target matching the partial index if supported by the runtime syntax. Since P3D2 is later, this is not a P3D1 blocker. Mention it in P3D2 prompt when triggers are designed.

Directive to Opus — create P3D1 prompt, do not dispatch

Create execution prompt:

knowledge/dev/laws/dieu44-trien-khai/prompts/23-p3d1-notification-schema-functions-prompt.md

Report path:

knowledge/dev/laws/dieu44-trien-khai/reports/23-p3d1-notification-schema-functions-report.md

P3D1 scope

Create only:

1. iu_notification_event table;
2. iu_notification_read table;
3. indexes/constraints from design rev3;
4. fn_iu_unread(p_actor text, p_stream text DEFAULT NULL, p_include_self boolean DEFAULT false, p_limit integer DEFAULT 50);
5. fn_iu_mark_read(p_event_ids uuid[], p_actor text).

No triggers in P3D1.

P3D1 function behavior

fn_iu_unread:

• validate actor non-empty;
• validate stream is NULL or one of comment, review, update;
• clamp limit to 1..500;
• return rows ordered created_at DESC;
• exclude self by default;
• filter by stream if provided;
• for draft_created, only return if referenced draft is still open;
• return JSON per event with next_action and guidance.

fn_iu_mark_read:

• validate actor non-empty;
• validate event id array non-null/non-empty;
• de-duplicate input ids;
• insert read rows for existing events;
• ON CONFLICT (event_id, actor_ref) DO NOTHING;
• return JSON with:
  • requested_count;
  • distinct_requested_count;
  • existing_count;
  • newly_marked_count;
  • already_marked_count;
  • unknown_count;
  • actor_ref.

P3D1 preflight

STOP unless:

• Pack 23 functions exist and protected function count is 10;
• iu_notification_event and iu_notification_read do not already exist, unless prompt explicitly implements idempotent already-installed verification;
• required source tables exist:
  • information_unit;
  • unit_edit_draft;
  • unit_edit_comment;
  • unit_version;
• extension/function for UUID default exists (gen_random_uuid()), or use the same UUID default convention as existing tables;
• owner/grantee pattern discovered, likely directus.

P3D1 tests required

1. Tables created with expected columns/types/defaults.
2. CHECK constraints exist for event_type/event_stream mapping and non-empty fields.
3. FK behavior exists as designed.
4. Indexes exist:
   • event stream created;
   • event unit created;
   • event created;
   • unique event type/ref partial index;
   • unique read event/actor;
   • read actor/event.
5. Functions created with exact signatures.
6. Security: owner, search_path, PUBLIC revoked, grantee execute.
7. fn_iu_unread('gpt') returns empty when no events exist.
8. Insert manual test events directly into notification tables for function tests only:
   • one comment event;
   • one review/draft event referencing an open draft if available, or a synthetic test draft created through Pack 23 functions if needed;
   • one update event.
9. fn_iu_unread stream filter works.
10. fn_iu_unread include_self=false hides self event; include_self=true shows it.
11. fn_iu_mark_read marks events, returns detailed counts, idempotent on second call.
12. Unknown event id counted as unknown.
13. Limit parameter clamps/works.
14. Pack 23 protected function hashes unchanged.
15. No triggers created in P3D1.

Test data policy

• Prefer dedicated P3D1 test rows.
• If manual insert into iu_notification_event is used, use clear canonical_address='test/p3d1/...' or existing pilot IU refs.
• Test rows may be retained for debug; report them.
• Do not mutate legacy vector or unrelated Pack 23 rows.

Hard boundaries

• Do not create triggers.
• Do not alter Pack 23 functions.
• Do not alter gateway/birth triggers.
• Do not mutate vector.
• Do not implement LISTEN/NOTIFY.
• Do not implement retention/archival.
• No external queue.
• No global read flag.

Next after P3D1

If P3D1 PASS, then create P3D2 prompt for triggers + event generation tests.

Summary

Design rev3 is good enough. Proceed to P3D1 prompt drafting only, not execution. P3D1 should build the durable notification schema and query API without changing behavior yet.