P3D — IU Notification Outbox + Per-Actor Read State — Design Note (rev3)

Date: 2026-05-07 Status: DESIGN rev3 — GPT 10 schema/integrity fixes. Chờ review. Chưa implement. Rev2→Rev3: CHECK constraints, FK behavior, idempotency index, next_action, limit, validation, trigger naming, hash boundary.

---

1. Table 1: iu_notification_event

CREATE TABLE iu_notification_event (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type        text NOT NULL,
  event_stream      text NOT NULL,
  unit_id           uuid NOT NULL REFERENCES information_unit(id),  -- NO ACTION default
  canonical_address text NOT NULL CHECK (btrim(canonical_address) != ''),
  ref_id            uuid,  -- polymorphic: draft/version/comment id. No FK Phase 1
  actor_ref         text NOT NULL CHECK (btrim(actor_ref) != ''),
  source            text NOT NULL CHECK (btrim(source) != ''),
  payload           jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at        timestamptz NOT NULL DEFAULT now(),

  -- rev3-F1: CHECK constraints
  CHECK (event_type IN ('comment_added','draft_created','version_applied')),
  CHECK (event_stream IN ('comment','review','update')),
  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')
  )
);

-- rev3-F4: Idempotency — prevent duplicate events from trigger re-fires
CREATE UNIQUE INDEX uq_notif_event_type_ref
ON iu_notification_event(event_type, ref_id) WHERE ref_id IS NOT NULL;

-- Indexes
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);

---

2. Table 2: iu_notification_read

CREATE TABLE iu_notification_read (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id  uuid NOT NULL REFERENCES iu_notification_event(id) ON DELETE CASCADE,  -- rev3-F3
  actor_ref text NOT NULL CHECK (btrim(actor_ref) != ''),
  read_at   timestamptz NOT NULL DEFAULT now()
);

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);

---

3. fn_iu_unread (rev3-F5 next_action, F6 limit)

fn_iu_unread(
  p_actor        text,
  p_stream       text    DEFAULT NULL,
  p_include_self boolean DEFAULT false,
  p_limit        integer DEFAULT 50       -- min 1, max 500
) RETURNS SETOF jsonb

Return shape per row:

{
  "event_id": "...",
  "event_type": "draft_created",
  "stream": "review",
  "unit_id": "...",
  "address": "law/dieu-44/khoan-1",
  "ref_id": "...",
  "actor": "agent:opus",
  "created_at": "...",
  "next_action": "fn_iu_apply_edit_draft",
  "guidance": "Draft awaiting review."
}

next_action mapping (rev3-F5):

• draft_created → fn_iu_apply_edit_draft
• comment_added → inspect_comment
• version_applied → inspect_version

Actionable filter:

• draft_created: chỉ hiện nếu draft open (LEFT JOIN unit_edit_draft)
• comment_added / version_applied: hiện cho tới mark read
• Self-exclude default; p_include_self=true cho debug

Validation: p_limit clamped GREATEST(1, LEAST(p_limit, 500))

---

4. fn_iu_mark_read (rev3-F7 validation)

fn_iu_mark_read(
  p_event_ids uuid[],
  p_actor     text
) RETURNS jsonb

Validation:

• p_actor non-empty after btrim
• p_event_ids NOT NULL, array_length >= 1
• Duplicate event_ids handled via DISTINCT

Return (rev3-F5 detail):

{
  "distinct_requested_count": 5,
  "existing_count": 4,
  "newly_marked_count": 3,
  "already_marked_count": 1,
  "unknown_count": 1,
  "actor_ref": "gpt"
}

---

5. Three Triggers (rev3-F9 naming)

trg_aa_iu_notif_comment

AFTER INSERT ON unit_edit_comment
  WHEN NEW.comment_kind != 'system'
→ INSERT ... ON CONFLICT (event_type, ref_id) WHERE ref_id IS NOT NULL DO NOTHING
  source='trg_aa_iu_notif_comment'

trg_aa_iu_notif_draft

AFTER INSERT ON unit_edit_draft
  WHEN NEW.draft_status = 'open'
→ INSERT ... ON CONFLICT DO NOTHING
  source='trg_aa_iu_notif_draft'

trg_aa_iu_notif_version

AFTER INSERT ON unit_version
  WHEN NEW.version_seq > 1
→ INSERT ... ON CONFLICT DO NOTHING
  source='trg_aa_iu_notif_version'

All triggers use ON CONFLICT DO NOTHING for idempotency (rev3-F4).

Preflight (rev3-F7): Verify column existence before trigger creation:

• unit_edit_comment: author_ref, comment_kind, unit_id
• unit_edit_draft: created_by, draft_status, canonical_address, unit_id
• unit_version: created_by, version_seq, unit_id

---

6. Applied/stale draft behavior (rev3-F8)

• draft_created event stays in history permanently
• Actionable inbox hides it when draft_status != 'open'
• No auto-insert read row on apply/stale (Phase 1)
• Event không bị xóa — chỉ filter khỏi fn_iu_unread
• Future history function có thể hiện lại tất cả

---

7. Implementation: P3D1 + P3D2

P3D1 — Schema + Functions (low risk)

• 2 tables + constraints + indexes
• fn_iu_unread + fn_iu_mark_read
• Tests: tables exist, columns correct, functions handle empty/edge, indexes exist

P3D2 — Triggers + Event Tests (behavior-changing)

• 3 triggers
• Tests: event creation, self-exclude, per-actor read, actionable filter, idempotency

Hash boundary (rev3-F10):

• P3D1: Pack 23 10 functions unchanged. fn_iu_unread + fn_iu_mark_read newly created.
• P3D2: P3D1 functions (12 total) unchanged. 3 triggers added.

---

8. Audience Model

Phase 1: Broadcast — all actors except creator see events. Future (không implement): target_role, subscription table, actor assignment.

---

9. Deferred

• ❌ LISTEN/NOTIFY (Phase 2)
• ❌ Retention/archival (Phase 2)
• ❌ External queue
• ❌ Global read flag
• ❌ General activity log

---

P3D Design Note rev3 | CHECK constraints, idempotency, next_action, limit, trigger naming | Chờ GPT/User review