IU Core MVP — 07 PG-Native Two-Way Trigger/Route Model

Date: 2026-05-21 · Authored in 002_iu_event_routes.sql + 005_trigger_contracts_and_guards.sql. NO trigger attached.

Inbound: SQL event → IU / workflow

SQL row/function/trigger event
  -> (later) PG trigger fn_iu_sql_link_inbound_capture()
  -> resolve an enabled iu_sql_event_route (schema+table+sql_event)
  -> write a metadata-only iu_route_attempt row (status dry_run|pending)
  -> route worker / DOT consumes -> workflow/review/draft action
  -> safety-net DOT scan reconciles source change count vs attempt count

• Registry: iu_sql_event_route (route_code UNIQUE; source_object_kind, sql_event, target_event_*, enabled, dry_run, fail_closed, idempotency_policy, payload_contract).
• fn_iu_sql_link_inbound_capture() authored, NOT attached. It writes metadata only — never calls HTTP/external services.

Outbound: IU lifecycle/version/link → SQL / workflow / vector / report

fn_iu_create / fn_iu_enact / unit_version insert / iu_sql_link change
  -> fn_iu_emit_event() -> event_outbox row (event_domain='iu')
  -> route worker reads iu_outbound_route -> approved target
  -> iu_route_attempt records delivery evidence

• Registry: iu_outbound_route (route_code UNIQUE; event_domain, event_type, target_kind, target_ref, enabled, dry_run, fail_closed, retry_policy, ...).
• fn_iu_outbound_on_version() authored, NOT attached — bridges unit_version inserts via fn_iu_emit_event.
• iu_notification_event left intact; the outbox bridge is additive and stays disabled.

Required behavior — encoded

• Enable/disable switch: every route has enabled (default false). Disabling = a route row UPDATE; no trigger drop required.
• Master kill-switch: fn_iu_core_routes_enabled() reads dot_config key iu_core.routes_master_enabled; absent ⇒ false. Every event/route function is a no-op while the gate is closed.
• Fail-closed: no route ⇒ no event, no error; disabled route ⇒ no downstream action; ambiguous link ⇒ attempt row failed/skipped, no workflow mutation; unregistered event type ⇒ no event_outbox insert (fn_iu_emit_event checks event_type_registry first).
• Retry/idempotency: PG trigger never retries — retry belongs to the worker/DOT. iu_route_attempt UNIQUE (idempotency_key, attempt_no) makes duplicate firing harmless; fn_iu_emit_event uses ON CONFLICT DO NOTHING against the existing event_outbox immediate-idempotency index.
• Dead-letter / error state: iu_route_attempt.status ∈ {pending, dry_run, sent, skipped, failed, disabled} with error_code/error_detail — failed rows are the dead-letter queue for worker reconciliation.
• Disabled-by-default with side effects: route CHECK enabled=false OR dry_run=true makes an enabled+live route impossible in this first package.
• No secret logging: trigger functions write only schema/table/op markers and safe_payload; no DSN/credential surface.

IU event vocabulary (proposed for event_type_registry — NOT seeded here)

Required: IU_CHILD_ADDED, IU_SPLIT, IU_MERGED, IU_DEPRECATED, IU_REPARENTED, IU_SQL_LINK_CHANGED. Optional: IU_REPLACED, IU_ROOT_ADDED, IU_CHILD_ORDER_CHANGED, IU_LINEAGE_CHANGED. Plus lifecycle events (unit_created, unit_enacted, version_applied, draft_created, comment_added, sql_link_changed). Seeding is a DML candidate for a separate DOT/governance macro (NEEDS_RULING R1).

Trigger guard compatibility

The live evt_trigger_guard_ddl event trigger polices CREATE TRIGGER. This package attaches no trigger; the sandbox macro must author its CREATE TRIGGER against that guard (commented SANDBOX-ONLY block at the bottom of 005).