Five tables in core: channels, routes, personal
preferences, an event journal, and delivery. One dispatcher writes the
event, splits it across two addressing models and journals delivery —
the mechanics live
in the dispatcher,
the feed views live in
Web App
and
Admin Panel.
1
Tables
Channels · routes · personal preferences · feed · delivery.
notification_channels
where we deliver
seed: in_app · email
| id |
BigInteger |
PK |
— |
| kind |
Text |
NOT NULLCHECK
|
channel type · → Slack — a preset |
| preset |
Text |
NULLCHECK
|
webhook payload format · NULL for built-ins |
| name |
Text |
NOT NULL |
channel pill label in the matrix |
| url_enc |
Text |
NULL |
webhook endpoint · write-only, AES-256-GCM · NULL for built-ins |
| secret_enc |
Text |
NULL |
HMAC secret for body signing (generic) · write-only, AES-256-GCM · optional, NULL for built-ins and Slack |
| is_builtin |
Boolean |
NOT NULLDEFAULT
|
true for in_app / email · undeletable |
| enabled |
Boolean |
NOT NULLDEFAULTCHECK
|
false = channel paused: routes intact, delivery held · in_app — CHECK always true |
| last_test_ok |
Boolean |
NULL |
result of the “Test” button · NULL = never tested |
| last_test_at |
DateTime(tz) |
NULL |
— |
| meta |
JSONB |
NOT NULLDEFAULT
|
preset specifics (Slack channel, headers) |
| created_at |
DateTime(tz) |
DEFAULT |
now() |
| updated_at |
DateTime(tz) |
DEFAULT |
now() + trigger |
notification_routes
type × channel matrix · defaults
FK → notification_channels
UNIQUE(event_type, channel_id)
| id |
BigInteger |
PK |
— |
| event_type |
Text |
NOT NULLUNIQUE*CHECK
|
category · → Matrix |
| channel_id |
BigInteger |
FK→channelsIDX
|
matrix column |
| enabled |
Boolean |
NOT NULLDEFAULTCHECK
|
channel pill in the matrix · orthogonal to channel pause · → Lock |
| locked |
Boolean |
NOT NULLDEFAULT
|
in-app × security · sync — true: this route cannot be disabled |
| created_at |
DateTime(tz) |
DEFAULT |
now() |
| updated_at |
DateTime(tz) |
DEFAULT |
now() + trigger |
notification_prefs
personal narrowing · profile
FK → users
UNIQUE(user_id, event_type)
| id |
BigInteger |
PK |
— |
| user_id |
BigInteger |
FK→usersIDX
|
any user · users |
| event_type |
Text |
NOT NULLUNIQUE*CHECK
|
category |
| in_app_enabled |
Boolean |
NOT NULLDEFAULT
|
personal in-app toggle per type · free opt-out |
| email_enabled |
Boolean |
NOT NULL
|
personal email toggle per type · the value at INSERT is written by code from the type catalog, not a column DEFAULT (personal types are opt-in false) |
| created_at |
DateTime(tz) |
DEFAULT |
now() |
| updated_at |
DateTime(tz) |
DEFAULT |
now() + trigger · no row = catalog default |
notifications
event · in-app feed · audit
journal · series dedup within a window
| id |
BigInteger |
PK |
— |
| event_type |
Text |
NOT NULLCHECK
|
category · catalog |
| severity |
Text |
NOT NULLCHECK
|
severity chip · orthogonal to type |
| target_user_id |
BigInteger |
FK→usersNULLIDX
|
recipient of a targeted event (agent · account) · NULL = broadcast by role · → Two models |
| title |
Text |
NOT NULL |
title · i18n key (agent.run_failed), not a rendered string |
| body |
Text |
NULL |
body · i18n key · NULL = title only |
| title_params |
JSONB |
NOT NULLDEFAULT
|
substitution params for title / body ({agent:"Sales watch"}) · rendered in the reader’s language |
| source |
Text |
NULL |
source module (harvester · auth · agent_engine · knowledge_store · admin) |
| source_ref |
Text |
NULL |
deep link to an entity (agent/7 · run/42 · source/17) |
| meta |
JSONB |
NOT NULLDEFAULT
|
action payload (Discovery: container for “into scope”) |
| dedup_key |
Text |
NULLIDX |
series collapse key · NULL = not deduped · → Throttling |
| dedup_count |
Integer |
NOT NULLDEFAULT
|
series counter “×N” · grows on a repeat within the window |
| last_seen_at |
DateTime(tz) |
NULL |
timestamp of the latest event in the series · compared against the dedup window |
| created_at |
DateTime(tz) |
DEFAULT |
now() · first event of the series |
| updated_at |
DateTime(tz) |
DEFAULT |
now() + trigger · mutates on a dedup increment |
notification_deliveries
per-channel delivery · read-state
FK → notifications · channels · users
UNIQUE(notification_id, channel_id, user_id)
UNIQUE(notification_id, channel_id) WHERE user_id IS NULL
| id |
BigInteger |
PK |
— |
| notification_id |
BigInteger |
FK→notificationsUNIQUE*IDX
|
which event · delivery idempotency key |
| channel_id |
BigInteger |
FK→channelsNULLIDX
|
in_app · email · webhook · webhook channel deleted → NULL, delivery row intact (audit) |
| user_id |
BigInteger |
FK→usersNULLIDX
|
personal recipient (in_app / email) · NULL for webhook |
| state |
Text |
NOT NULLCHECK
|
delivery status · read only for in_app |
| error |
Text |
NULL |
reason for failed |
| sent_at |
DateTime(tz) |
NULL |
send timestamp |
| read_at |
DateTime(tz) |
NULL |
in_app: when read · unread = NULL |
| created_at |
DateTime(tz) |
DEFAULT |
now() |
| updated_at |
DateTime(tz) |
DEFAULT |
now() + trigger · state mutates |
Two addressing models — split by one column, target_user_id
→ Dispatcher
broadcast target_user_id = NULL
Recipientsa role slice: users with owner/admin and status=active, computed by query — promote someone to Admin → they’re a recipient, revoke the role → they drop out
Typessync · security · budget · system · discovery
Webhookyes — delivery has user_id = NULL
targeted target_user_id = N
Recipientone user, any — including a member
Typesagent (run, governance) · account (role, temporary password)
Webhookno — personal types don’t go over webhooks
The column is additive: old types write
NULL and follow the previous path,
the targeted branch is a single condition in the dispatcher;
deliveries.user_id
is already per-user, so the delivery schema didn’t change. There is no separate
recipients table — personal narrowing lives in
notification_prefs
(per-user × type, flags
in_app and
email). We deliberately keep
no external human recipient — that role is played by a webhook channel,
not a recipient row outside the role system
(
personal settings).
A single notification_channels holds all channels — the
“Slack” and “any tool” scenarios converge into one table, and the
notification_routes matrix references channels uniformly by
channel_id.
- in_app · email —
is_builtin rows from the migration: undeletable, in_app can’t be disabled
- webhook — an ordinary row with a
preset (slack · generic) that selects the payload format; Slack has no dedicated column
- webhook — a broadcast-only channel: it has
user_id = NULL, personal types don’t fan out over it
- these are outbound alerts into a channel — the conversational Slack bot is a separate entity: inbound and personal
Channel default lives in the type catalog; email for user types is opt-in
→ Catalog
per-channel delivery = routes.enabled ∧ (prefs.<channel>_enabled or the catalog default)
— one formula for broadcast and targeted
- platform
- sync · security · budget · system · discovery → in-app + email
- agent · account
- in-app only; email opt-in — the user turns it on in their profile
With no
notification_prefs row, the value falls back to the
type catalog default, not a
global “both on”. So a member sees the notification bell by default but
receives no emails until they choose to.
What cannot be disabled centrally
One route is guaranteed — in-app for
security and sync: a critical type is always visible at
least in the feed, so a single admin can’t leave the organization without a signal.
- Lock: the in_app × security and in_app × sync routes are seeded
locked=true; CHECK (enabled OR NOT locked) prevents disabling them
- The level is organizational, in-app only: email and webhook for these types, like all channels for other types, stay free
- Personal opt-out is always available (
notification_prefs); agent · account are not held by the lock
Event is a journal, delivery is state
notifications — a journal, ~append-only; the only mutation is series dedup within a window (same dedup_key → dedup_count++, last_seen_at), immutable past the window- Text is i18n keys
title · body + title_params, rendered in the reader’s language, not a frozen string
notification_deliveries — state (queued → sent → read); the in-app feed = notifications ⋈ own deliveries, unread = read_at IS NULL- Deletion: event → cascades deliveries; user → their personal rows (the broadcast fact stays intact); webhook channel →
channel_id = NULL (audit lives on)
2
Migration
Creates the tables and seeds channels and routes.
alembic/versions/NNN_core_notifications.py
Creates the five tables in dependency order:
notification_channels →
notification_routes · notification_prefs ·
notifications → notification_deliveries.
INSERT the two built-in channels (is_builtin: in_app, email)- Seeds the full
routes matrix — a row for every type × built-in channel cell (enabled=true); webhook for the targeted types agent · account is not seeded
- The in_app × security and in_app × sync cells —
locked=true (what cannot be disabled)
downgrade() drops the tables in reverse order