Design Rationale — Notifications
Non-obvious design choices for the notifications module — the WHY behind each decision.
Notifications Module (Locked 2026-06-10)
Consent queries CRM — notifications has zero consent tables
Decision: notifications owns zero legal marketing consent tables. Marketing consent is read from crm.customer_consent / crm.customer.marketing_opt_in at dispatch via CRMService. Opt-out events are written to crm.customer_consent via CRMService. notification_preference is product preference only (channel/category/quiet-hours). notification.status = 'consent_blocked' is a dispatch outcome flag — it records that the consent check failed, not the consent decision itself.
Why: Legal marketing consent and product channel preferences are categorically different. Consent is subject to GDPR/CCPA/TCPA/CAN-SPAM recordkeeping requirements with specific fields (consent_given_at, opt_in_source, consent_version, ip_address). Product preferences are UI settings with no legal retention obligations. Merging them creates two sources of truth for the consent record — any consent table in notifications would diverge from crm.customer_consent and expose the business to TCPA/CAN-SPAM liability.
Rejected: A notification_consent table in notifications (duplicates crm ownership, creates divergence risk). A consent-flag column on notification_preference (schema leak: the preference table would gain legally significant fields outside CRM's control).
Guard: notification_preference must NEVER gain the following fields: consent_given_at, opt_in_source, consent_version, ip_address, legal_basis, or any other field that belongs on a marketing consent record. If you find yourself adding consent metadata to notification_preference, stop — those fields go on crm.customer_consent.
notification is the convergence point — origin_type CHECK-enforced
Decision: Every send origin (transactional event, campaign, journey step, manual) creates exactly ONE notification row per recipient. That row fans out to delivery_attempt rows per provider channel. origin_type CHECK enforces that each origin type populates exactly the right refs (campaign_id for campaigns, journey_enrollment_id + journey_step_id for journey, source_module + source_ref for transactional events, no origin refs for manual).
Why: A single dispatch pipeline prevents the code duplication and divergence that per-origin send tables create. Without a DB-level CHECK, a campaign-origin notification can be inserted without campaign_id set — the dispatch pipeline would route it incorrectly and the bug would surface at delivery time, not at write time. The CHECK enforces the contract at the database layer.
Rejected: Per-origin send tables (campaign_send, journey_send, transactional_event_send) — each would require its own dispatch pipeline, its own delivery_attempt FK, and its own consent-gating code. Three pipelines doing the same thing. Also rejected: a single meta JSONB column for origin refs instead of named FK columns (prevents FK enforcement and makes query conditions unindexable).
delivery_attempt is mutable, not append-only
Decision: delivery_attempt has updated_at and deleted_at. Status, engagement timestamps (sent_at, delivered_at, opened_at, clicked_at, failed_at, bounced_at), and retry_count all update post-insert as provider webhooks arrive. Journey branch conditions evaluate opened_at/clicked_at as they are set.
Why: Provider delivery is asynchronous. Resend, Twilio, FCM/APNS all report delivery status and engagement events via webhooks that arrive after the send. Modeling these as new rows (append-only log of delivery events) would require the journey engine to find the latest event for a given (notification, channel) pair on every branch evaluation — a join per step evaluation instead of a single column read. The mutable row is simpler and faster for the read path.
Rejected: Append-only delivery event log (correct for audit trails; wrong for the journey engine's engagement-evaluation read pattern). Insert-once row with a separate engagement-event child table (adds a join on every journey branch check).
Guard: Do NOT relabel delivery_attempt as append-only. This is the opposite of the audit_log pattern. The audit module's audit_log is insert-then-link-once (append-only after the hash chain step) because it is an immutable record of what happened. delivery_attempt is a mutable state machine row that reflects the current delivery state as reported by the provider.
In-app notifications bypass delivery_attempt — they fan out to in_app_notification
Decision: When a notification's requested_channels includes 'in_app', the dispatch creates an in_app_notification row (the persistent inbox item) directly — it does NOT create a delivery_attempt row. delivery_attempt.channel is CHECK-constrained to ('email','sms','push') only. notification_quota_usage.channel also excludes 'in_app' — in-app delivery is unmetered (no external provider cost).
Why: In-app notifications have no external provider, no provider message ref, no webhook engagement tracking, and no per-message cost. delivery_attempt exists to track the provider-side lifecycle of an outbound message. Creating a delivery_attempt for in-app would require a fake provider value, would never receive webhook updates, and would permanently be in 'sent' status with no engagement signal. The in_app_notification table is purpose-built for the inbox lifecycle: is_read, read_at, action_url, expires_at.
Rejected: delivery_attempt row with channel = 'in_app' (architecturally incoherent: no provider, no ref, no webhooks — the row would be immediately stale). A unified event table for all channel deliveries including in-app (would require nullable columns for every in-app-specific field and every provider-specific field — a wide nullable table with no row ever filling all columns).
Guard: delivery_attempt.channel must remain CHECK IN ('email','sms','push'). In-app is not a delivery_attempt channel. If a new channel is added (e.g., WhatsApp), it belongs in delivery_attempt. If an inbox-type channel is added (e.g., in-app v2 with threads), it belongs in a purpose-built inbox table, not delivery_attempt.
Branch conditions in JSONB, not child tables
Decision: journey_step.branch_condition_json stores branch logic as JSONB. No journey_branch or journey_step_condition child tables exist in v1. The journey engine evaluates opened_at/clicked_at from delivery_attempt against the JSONB condition at step-advance time.
Why: v1 branch logic is uniformly simple: "if the previous send was opened/clicked within N time units, take next_step_id; otherwise take fallback_step_id." A JSONB document handles this without FK complexity or join overhead. journey_step_condition child tables would add two tables and a join for no expressiveness gain at v1 scope.
Rejected: Child branch tables at v1 (premature; adds schema complexity for conditions that are trivially expressible as JSONB at current scope).
Guard: Migrate to child tables only if branching grows to AND/OR condition trees with multiple predicates per branch. The deferred-item trigger is: a branch condition that cannot be expressed as a single JSONB document without nesting that the engine finds difficult to evaluate. Single-condition branches: JSONB. Multi-condition trees: child tables.
Quota: platform owns the LIMIT; notifications owns the USAGE
Decision: notification_quota_usage.sent_count is a maintained counter. At dispatch, NotificationsService reads the tier entitlement from PlatformService (platform.tenant_entitlement), compares against sent_count, gates if over-quota, and increments atomically on successful send. Platform is the single source of truth for quota limits. Only provider-delivered channels (email/sms/push) are metered.
Why: Quota limits are a platform/tier concern — they change when a tenant upgrades their subscription. Duplicating them into notification_quota_usage would require a sync mechanism and create divergence risk. The counter-in-notifications pattern (usage here, limit in platform) is the same as other metered resources in the system.
Rejected: Store quota limits in notification_quota_usage (duplicates platform ownership, requires sync). Count sent notifications by querying delivery_attempt rows at dispatch time (correct in principle, but a count query per-send under contention is slow and will cause lock contention in high-volume scenarios; the maintained counter is O(1) to check).
Guard: sent_count is a cache of usage — platform is authoritative for the limit value. Never read the limit from notification_quota_usage. Never skip the PlatformService call.
Campaign recipients are notification rows — no campaign_recipient table in v1
Decision: Campaign send recipients are identified by querying notification rows WHERE campaign_id = this. No separate campaign_recipient snapshot table exists in v1.
Why: The notification table already records every send intent, including campaign_id. A campaign_recipient table would duplicate this: a snapshot of who was targeted, who was suppressed, and why. In v1, where campaigns are synchronous fan-outs to a CRM-queried audience, the notification rows ARE the recipient list. Adding a campaign_recipient table pre-send would require populating it before sends are created, creating a two-phase operation with a consistency risk.
Rejected: campaign_recipient table at v1 (redundant with notification rows; adds a two-phase send setup with no v1 use case that justifies it).
Guard (deferred trigger): Add campaign_recipient when: (a) pre-send audience snapshots are needed for suppression auditing (who was excluded and why before the campaign ran), or (b) post-send suppression reporting requires knowing who was targeted vs. who received a notification vs. who was consent-suppressed — at that point, the recipient list must be recorded before the send, not derived from post-send notification rows.
Discriminator-integrity pattern: origin_type, step_type, and exactly-one-recipient CHECKs
Decision: Three DB-level CHECK constraints enforce the convergence design at the database layer: (1) notification.origin_type implies exactly the right origin-ref columns are set; (2) journey_step.step_type implies the required config column is non-null; (3) four tables enforce num_nonnulls(recipient_col_a, recipient_col_b) = 1 instead of the weaker (col_a IS NOT NULL OR col_b IS NOT NULL).
Why: The weaker OR pattern allows both discriminator-dependent columns to be set simultaneously (origin_type='campaign' with both campaign_id AND journey_enrollment_id set; a preference row with both customer_id AND user_id set). The stronger num_nonnulls = 1 / discriminator-implies-refs CHECKs make the invariant enforceable at write time rather than relying on application discipline. Bugs caught at the DB layer are cheaper than bugs caught in production routing logic.
Cross-reference: This pattern appears across multiple modules. The OR NOT NULL → num_nonnulls = 1 fix was applied in the notifications audit pass. Apply the same fix to any new table with mutually-exclusive discriminated columns. See also: audit.compliance_task (related_module/related_ref both-or-neither CHECK), billing.ar_charge (source-ref discriminator CHECKs).