KB-6D99 rev 7

23-P3D4C1U — Universal Core Implementation Prompt (rev7)

9 min read Revision 7

p3d4c1upromptimplementationuniversaleventsystem_issuesimmediateidempotentrev7

23-P3D4C1U — Universal Core Implementation Prompt (rev7)

Date: 2026-05-08 Status: PROMPT rev7 — chờ GPT/User final review. CHƯA dispatch. Report: knowledge/dev/laws/dieu44-trien-khai/reports/23-p3d4c1u-universal-core-implementation-report.md Scope: PG implementation — universal event core + system_issues PoC (immediate-only). NO Directus/Nuxt. Rev6→Rev7: Fix idempotency key (remove occurred_at), capture trigger SELECT on conflict for self-read, rollback guard explicit.

Decisions Locked

PoC: system_issues (Đ22), ALL IMMEDIATE
Registry enforcement: B (BEFORE INSERT trigger) — validates event_type + event_stream + delivery_lane
IU: ZERO changes
Seed: 3 active immediate + 1 inactive delayed

Hard Boundaries

❌ KHÔNG mutate Directus / Nuxt / Hermes / Codex
❌ KHÔNG touch iu_notification_*
❌ KHÔNG install/query pg_cron
❌ KHÔNG create worker function
❌ KHÔNG expose body/vector/secret
❌ KHÔNG activity log
✅ PG DDL/DML (reviewed, idempotent, with guarded rollback)
✅ Report MUST be created even on FAIL/BLOCKED

Delivery Model

Lane A — IMMEDIATE (PoC): trigger → event_outbox + self-read. O(1).
Lane B — DELAYED (seam only): event_pending table empty. Worker deferred.

Column Naming Contract

Table	Subject ref
event_outbox	event_subject_table, event_subject_ref
event_pending	entity_table, entity_ref
event_read	event_id FK → event_outbox(id)

Step 0: Preflight — Same as rev6 (0A-0E). No pg_cron.

Step 1: DDL

Order: registry → outbox → read → subscription → pending (empty).

1A. event_type_registry — Same as rev6.

1B. event_outbox

Same as rev6 EXCEPT idempotency index:

-- DETERMINISTIC idempotency key (no occurred_at — it changes on retry)
CREATE UNIQUE INDEX IF NOT EXISTS idx_event_outbox_idempotent
  ON event_outbox (event_domain, event_type, event_subject_table, event_subject_ref)
  WHERE delivery_lane = 'immediate';

Note: This means 1 immediate event per (domain, type, subject) at a time. If system_issues status changes twice (open→resolved→archived), each is a different event_type so no conflict. If same status somehow re-triggers, it's idempotent.

1C-1E: Same as rev6.

Step 2: Registry Enforcement Trigger — Same as rev6.

Step 3: Seed — Same as rev6.

Step 4: Capture Trigger — IMMEDIATE (O(1), idempotent, self-read safe)

CREATE OR REPLACE FUNCTION fn_event_capture_system_issues()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE
  v_type text; v_stream text; v_actor text; v_eid uuid;
BEGIN
  v_actor := COALESCE(NEW.<actual_actor_column>, 'system');

  IF TG_OP = 'INSERT' THEN
    v_type := 'issue_opened'; v_stream := 'alert';
  ELSIF TG_OP = 'UPDATE' AND OLD.status IS DISTINCT FROM NEW.status THEN
    CASE NEW.status
      WHEN 'resolved' THEN v_type := 'issue_resolved'; v_stream := 'update';
      WHEN 'archived' THEN v_type := 'issue_archived'; v_stream := 'update';
      ELSE RETURN NEW;
    END CASE;
  ELSE
    RETURN NEW;
  END IF;

  -- Idempotent INSERT
  INSERT INTO event_outbox
    (event_domain, event_type, event_stream, delivery_lane,
     event_subject_table, event_subject_ref, canonical_address,
     actor_ref, source_system, occurred_at)
  VALUES
    ('system', v_type, v_stream, 'immediate',
     'system_issues', NEW.id, 'system://issues/' || NEW.id::text,
     v_actor, 'trigger', now())
  ON CONFLICT DO NOTHING
  RETURNING id INTO v_eid;

  -- If conflict (duplicate), SELECT existing event_id for self-read
  IF v_eid IS NULL THEN
    SELECT id INTO v_eid FROM event_outbox
    WHERE event_domain = 'system' AND event_type = v_type
      AND event_subject_table = 'system_issues' AND event_subject_ref = NEW.id
      AND delivery_lane = 'immediate'
    LIMIT 1;
  END IF;

  -- Implicit self-read (safe whether new or existing event)
  IF v_eid IS NOT NULL THEN
    INSERT INTO event_read (event_id, actor_ref, read_status_source)
    VALUES (v_eid, v_actor, 'implicit_self')
    ON CONFLICT (event_id, actor_ref) DO NOTHING;
  END IF;

  RETURN NEW;
END; $$;

REVOKE ALL ON FUNCTION fn_event_capture_system_issues() FROM PUBLIC;
DROP TRIGGER IF EXISTS trg_event_capture_system_issues ON system_issues;
CREATE TRIGGER trg_event_capture_system_issues
  AFTER INSERT OR UPDATE ON system_issues FOR EACH ROW
  EXECUTE FUNCTION fn_event_capture_system_issues();

Hot path: 2 INSERTs (or 1 INSERT + 1 SELECT on conflict + 1 INSERT). Still O(1).

Step 5: Access Functions — Same as rev6.

Step 6: Tests

#	Test	Expected
T1	INSERT unknown type → event_outbox	REJECTED
T2	INSERT denylist payload → event_outbox	REJECTED
T3	INSERT tagged issue → event_outbox	1 event + 1 self-read
T4	UPDATE status→resolved	1 event + 1 self-read
T5	UPDATE non-status → NO event	0 new
T6	Duplicate: re-trigger same status on same issue	No new event (idempotent). Self-read still works via SELECT fallback.
T7	fn_event_mark_read implicit→explicit	Updated
T8	Unsubscribed actor → filtered	0
T9	No pending rows	0
T10	IU snapshot match	Exact
T11	Rollback plan syntax	PASS

Step 7: Rollback (GUARDED — explicit conditions)

-- ROLLBACK IS GUARDED. Each destructive step has a precondition.
-- Agent MUST check precondition BEFORE executing each step.
-- If precondition FAILS → SKIP that step and WARN user.

-- 1. Triggers (always safe to drop)
DROP TRIGGER IF EXISTS trg_event_capture_system_issues ON system_issues;
DROP TRIGGER IF EXISTS trg_event_outbox_type_validate ON event_outbox;

-- 2. Functions (always safe to drop)
DROP FUNCTION IF EXISTS fn_event_capture_system_issues();
DROP FUNCTION IF EXISTS fn_event_type_validate();
DROP FUNCTION IF EXISTS fn_event_unread(text, text);
DROP FUNCTION IF EXISTS fn_event_mark_read(uuid[], text);

-- 3. Tables — GUARDED per table:
-- PRECONDITION: Agent runs SELECT count(*) FROM <table>;
-- If count = 0 → safe to DROP
-- If count > 0 → check if all rows are test-tagged
--   If all test → DELETE test rows, then DROP
--   If any non-test → DO NOT DROP, WARN user, report table_retained=<name>

DROP TABLE IF EXISTS event_pending;      -- should be 0 rows (PoC immediate-only)
DROP TABLE IF EXISTS event_read;         -- guard: check count
DROP TABLE IF EXISTS event_subscription; -- guard: check count
DROP TABLE IF EXISTS event_outbox;       -- guard: check count (FK cascade from read)
DROP TABLE IF EXISTS event_type_registry;-- guard: check count

-- 4. Config (safe — only event.* keys)
DELETE FROM dot_config WHERE key LIKE 'event.%';

-- NEVER touch iu_notification_* or system_issues production data

rollback_executed=NO_ON_SUCCESS|YES_ON_FAIL|USER_APPROVED_DRILL

Step 8: Post-Implementation IU Verification — Snapshot MUST match 0C.

Verification

preflight_system_issues=PASS|FAIL
system_issues_actor_column=<actual>
system_issues_tag_field=<actual>
preflight_dot_config=PASS|FAIL
preflight_iu_snapshot=<event_count>/<read_count>
preflight_no_existing_universal=PASS|ALREADY_PRESENT
preflight_si_triggers=<list>
ddl_tables_created=5/5
ddl_idempotent=true
idempotency_key=domain+type+subject_table+subject_ref (no occurred_at)
delivery_lane_in_registry=true
registry_trigger_validates_lane=true
seed_types_immediate=3/3
seed_types_delayed_inactive=1/1
seed_subscriptions=3/3
seed_config=2/2
capture_trigger_created=PASS|FAIL
capture_uses_actual_columns=true
capture_idempotent=true
capture_conflict_select_for_self_read=true
capture_implicit_self_read=true
capture_hot_path=O(1)
capture_no_pending=true
worker_created=NO_DEFERRED
idle_polling_avoided=true
fn_event_unread_created=PASS|FAIL
fn_event_mark_read_created=PASS|FAIL
fn_event_board_created=NO_DEFERRED
security_search_path=pg_catalog,public
iu_fn_unchanged=true
test_T1_through_T11=PASS|FAIL
test_T6_duplicate_idempotent=PASS|FAIL
test_data_tagged=true
test_tag_field_from_inventory=true
test_cleanup=PASS|ARCHIVED_NOT_DELETED
rollback_plan=PASS|FAIL
rollback_guarded_per_table=true
rollback_executed=NO_ON_SUCCESS
iu_post_snapshot_match=PASS|FAIL
no_directus_mutation=true
no_nuxt_code=true
no_iu_change=true
phase_status=PASS|FAIL|BLOCKED
next_required_pack=P3D4C2U_DIRECTUS_DOT_READONLY_EXPOSURE_PROMPT_REVIEW