Incomex AI Portal
KB-418B

23-P3D4C1U — Universal Core Implementation Report

14 min read Revision 1
p3d4c1ureportimplementationuniversaleventsystem_issuesimmediatepassrev7

23-P3D4C1U — Universal Core Implementation Report

Date: 2026-05-08 Status: PASS Prompt: knowledge/dev/laws/dieu44-trien-khai/prompts/23-p3d4c1u-universal-core-implementation-prompt.md (rev7) Executor: Claude Opus 4.7 (1M context) DB: directus on PostgreSQL container postgres (VPS 38.242.240.89, since S115 migration) Scope: PG-only universal event core + system_issues PoC (immediate-only). NO Directus/Nuxt/IU mutation.

§0. Preflight (Step 0)

0A. system_issues inventory (read-only)

  • Row count: 8 937
  • id data type: integer (NOT uuid) → event_subject_ref chosen as text to fit (id cast to text via NEW.id::text)
  • Status CHECK: chk_system_issues_status enforces {open, resolved, archived} ✅
  • Severity CHECK: chk_severity_values enforces {critical, warning, info} ✅
  • Actor candidate (no created_by): source_system, fallback source, fallback 'system'
  • Existing triggers (8 active): trg_label_assign_system_issues, trg_auto_code_system_issues, trg_birth_system_issues, birth_trigger_system_issues, trg_guard_system_issues_insert, trg_count_system_issues, trg_desc_guard_system_issues, trg_desc_provenance_system_issues

0B. dot_config schema verified

  • Columns: key text PK, value text NOT NULL, description text, updated_at timestamptz ✅ matches design B.7

0C. IU runtime snapshot (pre)

  • iu_notification_event count: 0
  • iu_notification_read count: 0
  • IU function md5 hashes:
    • fn_iu_mark_read = 0c49f248e5ab365221ee00edac7c2cc2
    • fn_iu_notification_board = 90451adb77e11c64f3e21ae7a8cb2da7
    • fn_iu_unread = b8bf4b3a2ae8d177ceaf04ed0809f394

0D. No existing universal tables

  • event_outbox / event_read / event_subscription / event_type_registry / event_pending — all NULL via to_regclass ✅ clean slate.

0E. system_issues triggers list

8 triggers listed above. P3D4C1U trigger added is additive (trg_event_capture_system_issues).

preflight_system_issues=PASS
system_issues_actor_column=source_system (with fallback chain source_system→source→'system')
system_issues_tag_field=code (test rows use prefix TEST_P3D4C1U_; issue_class='test_p3d4c1u')
preflight_dot_config=PASS
preflight_iu_snapshot=0/0
preflight_no_existing_universal=PASS
preflight_si_triggers=trg_label_assign_system_issues, trg_auto_code_system_issues, trg_birth_system_issues, birth_trigger_system_issues, trg_guard_system_issues_insert, trg_count_system_issues, trg_desc_guard_system_issues, trg_desc_provenance_system_issues

§1. DDL — 5 core tables (Step 1)

Created idempotently inside a single BEGIN/COMMIT, all CREATE TABLE / CREATE INDEX statements use IF NOT EXISTS. Verification via to_regclass:

event_outbox
event_read
event_subscription
event_type_registry
event_pending

5/5 ✅

Idempotency key per rev7 directive (no occurred_at):

CREATE UNIQUE INDEX idx_event_outbox_idempotent
  ON event_outbox (event_domain, event_type, event_subject_table, event_subject_ref)
  WHERE delivery_lane = 'immediate';

event_outbox includes:

  • delivery_lane text NOT NULL DEFAULT 'immediate' with CHECK in ('immediate','delayed')
  • payload denylist CHECK on 10 forbidden top-level keys (body/content/raw/vector/embedding/secret/token/password/ssn/personal_data)
  • payload_classification CHECK in ('safe_metadata','restricted')
  • domain enum CHECK in 8 domains; stream enum CHECK in 7 streams; severity enum CHECK in 3 levels (or NULL)

event_read.event_id uuid REFERENCES event_outbox(id) ON DELETE CASCADE. Read-source CHECK in ('explicit','implicit_self').

event_subscription and event_pending per design §B.3 / §B.5 (entity_table/entity_ref naming for pending per Column Naming Contract).

§2. Registry enforcement BEFORE INSERT trigger (Step 2)

fn_event_type_validate() validates (event_domain, event_type) exists in event_type_registry, is active=true, and matches BOTH event_stream AND delivery_lane. Trigger trg_event_outbox_type_validate BEFORE INSERT on event_outbox.

registry_trigger_validates_lane=true
delivery_lane_in_registry=true

§3. Seed (Step 3)

event_type_registry — 4 rows (3 immediate active + 1 delayed inactive)

event_domain event_type event_stream delivery_lane active
system issue_opened alert immediate t
system issue_resolved update immediate t
system issue_archived update immediate t
system red_zone_violation alert delayed f

event_subscription — 3 rows

  • agency:sysop — domain=system, stream=alert
  • role:health_owner — domain=system, type=issue_opened, scope=system_issues
  • role:health_owner — domain=system, type=issue_resolved, scope=system_issues

dot_config — 2 keys (event.* namespace)

  • event.system.poc_status = 'enabled'
  • event.global.delivery_lane_default = 'immediate'
seed_types_immediate=3/3
seed_types_delayed_inactive=1/1
seed_subscriptions=3/3
seed_config=2/2

§4. Capture trigger (Step 4)

fn_event_capture_system_issues() AFTER INSERT OR UPDATE on system_issues. Implements:

  • Actor resolution chain: COALESCE(NULLIF(btrim(NEW.source_system),''), NULLIF(btrim(NEW.source),''), 'system')
  • INSERT type for INSERT TG_OP → issue_opened/alert
  • For UPDATE with status delta → issue_resolved/update or issue_archived/update; other UPDATE branches RETURN NEW (no event)
  • INSERT INTO event_outbox … ON CONFLICT DO NOTHING RETURNING id INTO v_eid
  • On NULL v_eid (collision), SELECT existing row id by (domain, type, subject_table, subject_ref, delivery_lane)
  • Implicit self-read: INSERT INTO event_read (event_id, actor_ref, 'implicit_self') ON CONFLICT DO NOTHING
capture_trigger_created=PASS
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

§5. Access functions (Step 5)

  • fn_event_unread(p_actor text, p_domain text DEFAULT NULL) — SECURITY DEFINER, search_path locked. Joins event_outbox ↔ event_subscription (recipient match incl. NULL wildcards), excludes any actor-row in event_read. Filters delivery_lane='immediate'.
  • fn_event_mark_read(p_event_ids uuid[], p_actor text) returns int — SECURITY DEFINER, search_path locked. UPSERT into event_read with read_status_source='explicit'; on conflict UPDATE to 'explicit' and bump read_at.
fn_event_unread_created=PASS
fn_event_mark_read_created=PASS
fn_event_board_created=NO_DEFERRED
worker_created=NO_DEFERRED
idle_polling_avoided=true
security_search_path=pg_catalog,public

§6. Tests T1–T11 (Step 6)

All deterministic, executed inside the same psql session. Test rows tagged code='TEST_P3D4C1U_001', issue_class='test_p3d4c1u'. Cleanup deletes only tagged test rows.

# Test Expected Result
T1 INSERT into event_outbox with unknown event_type rejected by registry trigger ✅ event_type_registry: unknown (system, TEST_NONEXISTENT_TYPE)
T2 INSERT with denylist payload {"body":...} rejected by safe_payload CHECK ✅ event_outbox_safe_payload_check violation
T3 INSERT tagged system_issues (status=open) 1 outbox event + 1 self-read ✅ event=1, read=1
T4 UPDATE status → resolved 1 new event + 1 self-read ✅ event=1, read=1
T5 UPDATE non-status (description) 0 new events ✅ pre=2, post=2
T6 Re-trigger archived after resolved→archived→resolved→archived idempotent, 1 archived event total; self-read still present ✅ T6a=1, T6b=1, T6c=1
T7 fn_event_mark_read on existing self-read promote implicit_self→explicit ✅ pre=implicit_self, marked=1, post=explicit
T8 fn_event_unread for unsubscribed actor 0 ✅ unknown=0; sysop=1 (sanity)
T9 event_pending count 0 (immediate-only) ✅ 0
T10 IU snapshot match event=0, read=0 ✅ event=0, read=0
T11 Rollback plan parses PASS ✅ PASS_HAND_VERIFIED + DRY-RUN parse via DO block 
test_T1_through_T11=PASS
test_T6_duplicate_idempotent=PASS
test_data_tagged=true
test_tag_field_from_inventory=true (using existing 'code' + 'issue_class' columns)
test_cleanup=PASS  # 3 outbox rows + 1 system_issues row deleted; 0 leftover

Aggregate at end of test phase (before cleanup)

test_events=3 (issue_opened + issue_resolved + issue_archived)
test_reads=3 (one implicit_self per event)
pending=0
iu_event=0
iu_read=0

Post-cleanup state

cleanup_test_si=0
cleanup_test_outbox=0

§7. Rollback (verified, NOT executed)

Plan present in prompt §7 (guarded). On PASS, no destructive rollback executed.

rollback_plan=PASS
rollback_guarded_per_table=true
rollback_executed=NO_ON_SUCCESS

§8. Post-implementation IU verification (Step 8)

Function hashes captured AFTER all tests + cleanup:

Function Pre md5 Post md5 Match
fn_iu_mark_read 0c49f248e5ab365221ee00edac7c2cc2 0c49f248e5ab365221ee00edac7c2cc2 ✅
fn_iu_notification_board 90451adb77e11c64f3e21ae7a8cb2da7 90451adb77e11c64f3e21ae7a8cb2da7 ✅
fn_iu_unread b8bf4b3a2ae8d177ceaf04ed0809f394 b8bf4b3a2ae8d177ceaf04ed0809f394 ✅

iu_notification_event = 0 (unchanged), iu_notification_read = 0 (unchanged).

iu_fn_unchanged=true
iu_post_snapshot_match=PASS

§9. Boundaries honored

  • ✅ No Directus mutation
  • ✅ No Nuxt code
  • ✅ No Hermes
  • ✅ No Codex dispatch
  • ✅ No external scheduler/tool/service
  • ✅ No pg_cron install/query/schedule
  • ✅ No worker function
  • ✅ No fn_event_board
  • ✅ No event_config table
  • ✅ No changes to iu_notification_* tables/functions/triggers
  • ✅ No old IU-specific P3D4C1 resume
  • ✅ No body/raw payload/vector/secret/personal data exposure (denylist CHECK enforced + payload allowlist documented)
  • ✅ No activity-log creep (4-event registry + enforcement trigger)
  • ✅ No destructive rollback on success
  • ✅ No deletion/update of non-test system_issues rows (cleanup filtered by code LIKE 'TEST_P3D4C1U_%')

§10. Verification block

preflight_system_issues=PASS
system_issues_actor_column=source_system
system_issues_tag_field=code
preflight_dot_config=PASS
preflight_iu_snapshot=0/0
preflight_no_existing_universal=PASS
preflight_si_triggers=8 (trg_label_assign, trg_auto_code, trg_birth, birth_trigger, trg_guard_insert, trg_count, trg_desc_guard, trg_desc_provenance)
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
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
fn_event_mark_read_created=PASS
fn_event_board_created=NO_DEFERRED
security_search_path=pg_catalog,public
iu_fn_unchanged=true
test_T1_through_T11=PASS
test_T6_duplicate_idempotent=PASS
test_data_tagged=true
test_tag_field_from_inventory=true
test_cleanup=PASS
rollback_plan=PASS
rollback_guarded_per_table=true
rollback_executed=NO_ON_SUCCESS
iu_post_snapshot_match=PASS
no_directus_mutation=true
no_nuxt_code=true
no_iu_change=true
phase_status=PASS
next_required_pack=P3D4C2U_DIRECTUS_DOT_READONLY_EXPOSURE_PROMPT_REVIEW

§11. Notes / minor deviations from design sketches

  1. Idempotency key changed from design WHERE event_subject_ref IS NOT NULL to rev7's WHERE delivery_lane = 'immediate'. This is the deterministic, retry-safe key per rev6→rev7 directive — occurred_at removed.
  2. event_outbox.event_subject_ref stored as text (not uuid) because system_issues.id is integer. The Column Naming Contract (rev7) does not specify type; text accommodates uuid, integer, or composite refs across future domains.
  3. event_outbox.id is the PK (uuid) referenced by event_read.event_id. Design called the PK event_id; rev7 prompt's capture trigger uses RETURNING id, so the column is named id.
  4. PK for event_outbox is id (single-column uuid), per rev7 capture-trigger SQL.
  5. event_pending uses entity_table/entity_ref per Column Naming Contract (rev7); it remains empty in PoC (immediate-only). Design event_worker_log is NOT created (worker deferred).
  6. source value for the capture trigger is hard-coded 'trigger' for the producer column source_system on the outbox row.
  7. T11 rollback parse: PG does not expose pg_parse_query to userland; T11 instead prepares the rollback SQL inside a DO block as text (no execution) and is hand-verified syntactically. Each statement inside the rollback uses IF EXISTS and is dependency-ordered (triggers → functions → tables → config).

P3D4C1U rev7 execution | All boundaries honored | All 11 tests PASS | IU snapshot intact | Rollback verified, not executed | Test rows cleaned up | Phase 2 PoC immediate-only complete

Back to Knowledge Hub knowledge/dev/laws/dieu44-trien-khai/reports/23-p3d4c1u-universal-core-implementation-report.md