# Design Summary — Analytics Reads & Event Pipeline A concept-level summary of the whole enhancement backlog (E1–E7). No code. Two independent tracks; review each on its own merits. > **Status (2026-10-06): Track B E4 is implemented + deployed.** The DB > `notify_process()` trigger is **gone** — the app writer `emit_event()` > (`common/dori_utils/events.py`) now inserts the event and, in one transaction, > writes the durable `dori.event_outbox` row; DIM's relay drains it. Each event > type carries **two independent, editable lanes**: `event_type.is_notification` > (UI/websocket) and `event_type.is_alert` (CIM alert — SMS/MQTT/email/webhook > via per-company subscriptions, or the DORI_ADMIN fallback for system events). > `is_trigger`/`is_message` were removed. See `docs/IMPLEMENTATION-PLAN-outbox.md` > and the live data model at `/docs/database-data-model.html`. The notes below > are the original concept; where they say "the trigger decides" or name > `is_trigger`, read `emit_event` and `is_alert`. --- ## The two tracks - **Track A — Read/query performance (E1, E2, E3).** Make analytics queries on the time-series (hypertable) data cheap, reliable, and safe. Nothing to do with notifications. - **Track B — Event notification pipeline (E4, E6, E7; E5 folded into E4).** Move event routing out of the database trigger into the app, make alert delivery durable, and modernize the subscriber model. They can be scheduled separately. Track B is where the recent discussion lives. --- ## Track A — Read path (E1–E3) One sentence each. - **E1 — Stamp tenant on the data.** Put `company_id` directly on the time-series rows so a tenant query is one `WHERE`, not a 4-table join (`event → job → device → location`). - **E2 — Make the join key real.** `request_id` is a plain string with no enforcement; if its format drifts, joins silently return nothing. Validate it (or add a surrogate integer FK) so breakage fails loudly instead of quietly. - **E3 — Always bound reads by time.** Timescale prunes by the time column; a query without a time range scans every chunk. Make it structurally hard to write a hypertable query with no `time_sec` window. **Theme:** *scope by tenant cheaply, join reliably, always bound by time.* --- ## Track B — Event pipeline (E4–E7) ### How it works today 1. A **writer** (monitor for system events, analytics ingest for customer events) does a bare `INSERT` into `inference_event`. 2. A **DB trigger** fires on **every** insert, reads the event-type flags, decides routing, and — only when a flag matches — emits `pg_notify`. 3. **DIM** listens, and on each notification does two things: push to the **UI websocket** and forward to **CIM**. 4. **CIM** resolves recipients and sends the **SMS/email/etc.** ### What's wrong with it - **Routing logic is trapped in the trigger** (PL/pgSQL): hard to test/observe, re-applied on every table rename, and runs inside the write transaction on every insert. - **`pg_notify` is at-most-once:** if DIM is down during a deploy/crash, the notification is **silently lost** — unacceptable for a critical alert. - **Subscriber model is awkward:** the destination address is split across two tables, and "does it alert" needs *both* a flag and a subscription row (double-config footgun); system events are special-cased to one hardcoded path. ### The target: two clean lanes, decided by the *writer* The writer stops being "just an insert." It becomes the single decision point and lights up either/both/neither of two lanes, in **one transaction**: | Lane | Purpose | Nature | Gate | |---|---|---|---| | **Notification** | update the dashboard | ephemeral, loss-tolerant, sub-ms | `is_notification` | | **Outbox** | send an alert (SMS/email/webhook/MQTT) | durable, at-least-once, retried | "needs alert" | - **Notification lane:** writer emits a lightweight signal → DIM → websocket → UI. - **Outbox lane:** writer inserts a durable **outbox row** in the same transaction as the event; a separate **relay** reliably delivers it. If the consumer is down, the row waits and retries — nothing is lost. The **database trigger goes away** — the writer now does what the trigger did, in app code, with the event-type flags cached in memory. > **Note on naming:** removing *the trigger* (the DB mechanism) does **not** > remove the `is_trigger` *flag* (config data meaning "this system event should > alert ops"). The writer keeps reading that flag. ### The backlog items in Track B - **E4 — Outbox + producer-in-writer (NEXT).** The core change above: writer writes the event + a conditional outbox row atomically and emits the notification; a relay drains the outbox durably; the trigger is retired. (The former **E5**, "move routing out of the trigger," is the writer-side of this and is folded in — not worth doing alone.) First slice: monitor's MLC up/down. - **E6 — `event_config` cleanup.** Delete dead references to a table that was already dropped. Pure hygiene, independent of everything else. - **E7 — Contact points + routing policies.** Replace `subscriber` + `subscriber_event` + the `is_message` flag with a reusable **contact-point** (one address, one channel) + **route** (one rule: "this event in this company /scope → this contact point") model. Then **"does it alert" = "a route matches"** — no flag to keep in sync. `is_notification` stays as the UI flag. System events stop being special-cased (the Dori root company gets routes like anyone else). New channels become plug-ins, not dispatch edits. --- ## End state (Track B) - **One decision point:** the writer. Always insert the event; add an outbox row if an alert is needed; emit a notification if the UI needs updating; all atomic. - **No DB trigger.** Routing is testable app code reading cached flags/routes. - **Alerts are durable** (outbox, at-least-once, retries, dead-letter). - **UI stays low-latency** (lightweight notification path). - **Routing is configuration** (routes), with per-company precision and no double-config. --- ## Suggested sequencing 1. **E4** — outbox + producer-in-writer, **monitor MLC up/down first** (lowest risk; also kills a live dual-write in the monitor status-watch path). Removes the trigger for system events. 2. Extend the E4 writer path to **customer events** (analytics ingest). 3. **E7** — contact points + routes, built on top of the outbox; retires `is_message` and the subscriber/subscription tables. 4. **E6** — cleanup; do anytime, independent. 5. **Track A (E1–E3)** — independent of Track B; schedule on its own. --- ## What's decided vs open - **Decided in principle:** remove the trigger; writer decides; outbox for durable alerts; `pg_notify` kept for the low-latency UI lane. - **Open for review:** whether to do E7's full contact-point/route redesign now or start with smaller incremental fixes; timing of Track A; how aggressively to consolidate `is_trigger` + `is_message` before the full E7 route model lands.