Event Delivery Architecture

Application Code
    │
    ▼
publishEvent(event_type, payload, org_id)
    │
    ▼
INSERT → fw_domain_events table
    │
    ▼
fw_process_domain_event() AFTER INSERT trigger
    │
    ├── Pattern-match against fw_automation_rules
    ├── Pattern-match against fw_workflow_definitions (trigger config)
    │
    ▼
INSERT → fw_workflow_executions (status = 'pending')
    │
    ▼
Enqueue → workflow_execution_queue (pgmq)
    │
    ▼
pg_cron worker picks up message (10-30s poll)
    │
    ▼
Execute workflow steps → update execution status

Application Code (browser or server)
    │
    ▼
supabase.functions.invoke('event-consumer', {
  body: { event_type, payload }
})
    │
    ▼
Edge Function: event-consumer
    │
    ├── Validate event
    ├── Route to handler
    ├── Execute side effect (send email, call API, update record)
    │
    ▼
Synchronous HTTP response (success/failure)
    │
    ▼
Caller handles response

Database INSERT/UPDATE/DELETE
    │
    ▼
Supabase Realtime (Postgres Changes listener)
    │
    ▼
WebSocket broadcast to subscribed clients
    │
    ▼
Client callback → React state update → UI re-render

Application Code (any client)
    │
    ▼
supabase.channel('room').send({ type: 'broadcast', event, payload })
    │
    ▼
Supabase Realtime relay
    │
    ▼
WebSocket broadcast to other clients in channel
    │
    ▼
Client callback → React state update → UI re-render

fw_domain_events INSERT
    │
    ▼
fw_process_domain_event() trigger
    │
    ├── Check pf_event_subscriptions for matching external subscribers
    ├── Apply glob pattern matching on event_type
    │
    ▼
Match found
    │
    ├── 42 CFR Part 2 consent guard check (substance abuse treatment data)
    ├── Apply JSONPath payload transformation (strip internal fields)
    │
    ▼
Enqueue → event_forwarding_queue (pgmq)
    │
    ▼
Delivery worker picks up message
    │
    ├── POST to subscriber webhook URL
    ├── Verify response (2xx = success)
    │
    ▼
    ├── Success → ACK message, log delivery
    └── Failure → Retry (up to 3 attempts, exponential backoff)
              │
              └── Max retries exceeded → fw_dead_letter_queue

Application Code (Edge Function, trigger, or worker)
    │
    ▼
SELECT pgmq.send('queue_name', message_json)
    │
    ▼
Message persisted in pgmq queue table
    │
    ▼
Consumer: SELECT pgmq.read('queue_name', visibility_timeout, batch_size)
    │
    ├── Message becomes invisible to other consumers
    ├── Process message
    │
    ▼
    ├── Success → SELECT pgmq.archive('queue_name', msg_id)  -- ACK
    └── Failure → Message becomes visible again after timeout  -- implicit NACK
              │
              └── Max retries exceeded → move to DLQ

    "I need to react to something happening in the system"
                          │
                          ▼
              ┌───────────────────────┐
              │ Is it a UI-only       │
              │ update? (badge count, │    YES    ┌──────────────────────────┐
              │ live list, presence)  │──────────>│ Path 3: Supabase         │
              └───────────┬───────────┘           │ Realtime                 │
                          │ NO                    │ (WebSocket, best-effort) │
                          ▼                       └──────────────────────────┘
              ┌───────────────────────┐
              │ Must it complete      │
              │ synchronously in the  │    YES    ┌──────────────────────────┐
              │ current request       │──────────>│ Path 2: HTTP Event       │
              │ cycle?                │           │ Consumer                 │
              └───────────┬───────────┘           │ (Edge Function, sync)    │
                          │ NO                    └──────────────────────────┘
                          ▼
              ┌───────────────────────┐
              │ Does it trigger a     │
              │ business workflow or  │    YES    ┌──────────────────────────┐
              │ automation rule?      │──────────>│ Path 1: Table-Driven     │
              └───────────┬───────────┘           │ Domain Events            │
                          │ NO                    │ (fw_domain_events, pgmq) │
                          ▼                       └──────────────────────────┘
              ┌───────────────────────┐
              │ Does it need to reach │
              │ an external system?   │    YES    ┌──────────────────────────┐
              │ (EHR, payer, partner) │──────────>│ Path 4: External Event   │
              └───────────┬───────────┘           │ Forwarding               │
                          │ NO                    │ (webhook + DLQ)          │
                          ▼                       └──────────────────────────┘
              ┌───────────────────────┐
              │ Is it async internal  │
              │ work? (embedding,     │    YES    ┌──────────────────────────┐
              │ notification, batch)  │──────────>│ Path 5: pgmq Direct     │
              └───────────┬───────────┘           │ Queue                    │
                          │ NO                    │ (internal async work)    │
                          ▼                       └──────────────────────────┘
              ┌───────────────────────┐
              │ Re-evaluate: you may  │
              │ need a combination of │
              │ paths. See "Composed  │
              │ Patterns" below.      │
              └───────────────────────┘

Resident record updated (status = 'active')
    │
    ├── Path 1: publishEvent('resident.moved_in') → triggers billing workflow,
    │           creates admission documentation tasks, updates census
    │
    ├── Path 3: Realtime Postgres Changes → dashboard census widget updates live
    │
    └── Path 5: pgmq notification_queue → send welcome packet email + SMS

Claim finalized by billing staff
    │
    ├── Path 1: publishEvent('claim.submitted') → audit trail, status tracking
    │
    ├── Path 4: External forwarding → clearinghouse receives claim via webhook
    │
    └── Path 2: HTTP consumer → real-time eligibility verification response
                shown to user before submission completes

// Subscribing to Realtime to trigger a billing workflow
supabase.channel('billing')
  .on('postgres_changes', { table: 'invoices' }, (payload) => {
    createBillingRecord(payload.new);  // BUG: missed if client disconnects
  })

// Calling an Edge Function that runs a 15-step approval workflow
const { data } = await supabase.functions.invoke('run-full-workflow', {
  body: { workflowId, data }
});
// User waits 45 seconds... Edge Function times out

-- Direct INSERT in a trigger instead of going through domain events
CREATE TRIGGER create_billing_on_admission
AFTER INSERT ON rh_residents
FOR EACH ROW
EXECUTE FUNCTION create_billing_record_directly();

-- Making an HTTP call inside a trigger
CREATE TRIGGER notify_ehr_on_discharge
AFTER UPDATE ON rh_residents
FOR EACH ROW
WHEN (NEW.status = 'discharged')
EXECUTE FUNCTION http_post_to_ehr();  -- blocks transaction if EHR is slow

// Polling pgmq from the browser to check if a task completed
setInterval(async () => {
  const result = await checkQueueForMyResult();
  if (result) updateUI(result);
}, 1000);