notifications — Module Spec

1. Purpose

notifications is Vrida's event-driven delivery orchestrator — module #29, schema-locked 2026-07-19, the first STALE-BUT-UNBUILT module revival (SCHEMA_DESIGN_RUNBOOK 2.1a): v1 was schema-locked 2026-06-10 and never migrated. Templates, transactional/marketing sends across email/SMS/push/in-app, a durable POS receipt-print signal, campaigns with pre-send audience snapshots, two-way inbound SMS, and a per-tenant + platform-wide suppression list. Full lineage — v1 → design review (validated against 6 current requirements + live codebase reality) → v2 proposal → 16 architect rulings → RULED design → an 11-finding independent adversarial verification pass (3 BLOCKERs, 4 MAJORs, 4 MINORs; 10 accepted-and-fixed, 1 deferred as a genuine architect decision) → this build. See PROJECT_DECISIONS (Notifications entry).

2. Ownership

Owns — 18 tables, 252 columns:

Table	Cols	Role
notification_template	16	Reusable per-channel copy + variables, versioned
notification	33	The convergence point — every send (transactional/campaign/journey/manual) lands here first
delivery_attempt	21	Per-channel dispatch attempt, monotonic status guard
notification_preference	13	Customer/staff opt-in/quiet-hours per channel+category
in_app_notification	20	The in-app inbox
notification_quota_usage	9	Per-channel/period send-volume counter
inbound_message	15	Two-way SMS/email replies
notification_journey / journey_step / journey_enrollment	11 / 14 / 15	Multi-step drip sequences — KEPT verbatim, dormant, out of v1 capability scope
campaign	21	Bulk-send definition, draft_only autonomy gate
campaign_recipient	8	NEW — pre-send audience snapshot + campaign-grain dedup
notification_frequency_tracker	8	NEW — atomic per-customer marketing-frequency cap
provider_event_log	9	NEW — mirrors payments.stripe_event_log exactly
provider_event_dead_letter	10	NEW — mirrors payments.stripe_event_dead_letter exactly
suppression	10	NEW — per-tenant hard-bounce/spam-complaint/manual/provider-block list
sender_identity	13	NEW — shared-domain-for-v1 sender registry, BYO a v1.1 path
platform_suppression	6	NEW — platform-wide, cross-tenant, service-role-only suppression list (architect ruling on Part B Finding #10)
Total	252

Does NOT own: delivery-provider connector logic (NotificationsService will talk to Resend/Twilio directly, mirroring PaymentsService's own established direct-to-Stripe precedent — the not-built integrations module is superseded here, per Part 3 Item 12 of the design review); the compliance-trail/audit role (the not-built audit module's role is served interimly by provider_event_log/delivery_attempt themselves).

3. Layer & Dependencies

Cross-cutting delivery-orchestration layer. Depends on: platform (tenant, outbox, processor_catalog), crm (customer, customer_group, customer_segment_definition), identity (actor), offers (offer), purchasing (vendor). Companion-reopen-only, no live FK from this schema: orders (order_fulfillment), billing (ar_statement). Depended on by: none yet — schema-only, no NotificationsService HTTP surface exists.

4. Capabilities — honest Part D framing

The two genuine agent surfaces: notification_template/in_app_notification/notification/campaign all carry the standard automation_source/review_status/decision_provenance autonomy pack. campaign is the flagship draft_only case — chk_campaign_draft_gate makes it structurally impossible for an agent-drafted campaign to leave status='draft' (i.e., reach 'scheduled'/'sending') while review_status != 'approved'. The one deliberate exception to the usual human-in-the-loop caution: suppression inserts must be may_act_alone, never gated — per the design's own Part D D7 answer, a human-in-the-loop delay on suppression additions IS the compliance risk, not a safety measure.

5. Service Contract — NotificationsService

Not built this pass. Binding future requirements: (1) NotificationsService is the sole writer of billing.ar_statement.sent_at/.delivery_method and approvals.approval_delivery's own send-tracking columns — both become thin caches it maintains, not independently-written duplicates (Ruling 15); (2) webhook signature verification must happen BEFORE any row is written to provider_event_log — the raw signed payload is retained specifically so it can be re-verified later, not just the parsed fields; (3) the actual numeric frequency cap read (currently admin.tenant_setting category='notifications'/key='marketing_frequency_cap_per_month') and the actual retry-sweep/campaign-scheduling pg_cron job definitions are both service-build work — the extension is installed, zero jobs exist.

6. The combined notification-insert guard (this build's central guard)

notifications.guard_notification_insert() is a single BEFORE INSERT trigger on notification, merging 3 structural checks in one pass, mirroring the atomic lock-then-check-then-increment idiom offers.check_and_sync_offer_budget() established:

1. Campaign-recipient presence guard — origin_type='campaign' requires a matching campaign_recipient row to already exist, else RAISE EXCEPTION. Closes Part B Finding #5: without this, nothing enforced that campaign_recipient — the whole justification for building the table — is populated before a send exists.
2. Suppression gate — fires unconditionally, checking BOTH the per-tenant suppression table AND the cross-tenant platform_suppression table (Finding #10's ruled fix); on a match, forces NEW.status := 'suppressed' and short-circuits the frequency-cap check below. Fires regardless of is_transactional — a transactional receipt bypasses marketing consent but never suppression.
3. Frequency cap — marketing-class only, excludes test sends. A real bug self-caught and fixed mid-build: the trigger as first written only demonstrated the atomic-increment mechanism, with no actual numeric cap check (the comment literally deferred the cap to "the service layer"). Recognizing this was insufficient given the task's own explicit "3-way genuine concurrent race at cap=2" test scenario, it was rewritten (20260719000009_notifications_frequency_cap_enforcement.sql) into a genuine lock-then-check-then-increment reading a per-tenant cap from admin.tenant_setting (defaulting to 999999 — no behavioral change — if unset). Live-verified via a real 3-way concurrent psql & race at cap=2: exactly 2 rows landed pending, 1 quota_blocked.

Monotonic status guard on delivery_attempt (trg_delivery_attempt_guard_status) — an ordinal scheme queued(0)→sending(1)→sent(2)→delivered(3)→{failed,bounced,complained}(4) with two independent guard clauses: (a) a duplicate/retried webhook redelivering the identical terminal status is a silent no-op, not an error (both Resend and Twilio are at-least-once delivery); (b) a genuine ordinal decrease (a late 'delivered' after 'bounced') is rejected. opened_at/clicked_at are first-write-wins, independent of the status transition, and are never status values themselves.

recipient_contact immutability (trg_notification_recipient_contact_immutable) — a BEFORE UPDATE OF recipient_contact trigger unconditionally rejects any post-insert change, closing a real suppression-bypass path an independent adversarial pass found (Part B Finding #2): without it, a row could be inserted with a clean address, then corrected to a suppressed one post-insert with zero re-check.

7. Design Rationale

• campaign.customer_segment_definition_id, a bare FK plus a trigger — not composite. crm.customer_segment_definition.tenant_id is nullable (genuine global "builtin" segments exist alongside tenant-owned ones); a composite (id, tenant_id) FK can't express "same-tenant OR global." notifications.validate_campaign_segment() mirrors offers.validate_offer_targeting_rule_segment() exactly.
• suppression.source_event_id/platform_suppression.source_event_id are bare FKs, not composite — identical reasoning applied to provider_event_log's own deliberately-nullable tenant_id (a webhook can arrive before tenant resolution). Confirmed live that zero FKs anywhere in this codebase reference payments.stripe_event_log compositely either, making this the established (if previously implicit) precedent.
• delivery_attempt/notification stay MUTABLE (Ruling 1) — no delivery_event split; the monotonic guard is the sole integrity backstop rather than an append-only ledger requiring aggregation to reconstruct current state.
• provider_event_log/provider_event_dead_letter genuinely carry ZERO triggers — confirmed live via pg_get_functiondef against the payments.stripe_event_log/stripe_event_dead_letter tables this design mirrors, honoring the task's own explicit instruction to verify live rather than assume the codebase's more common append-only pattern.
• A real, disclosed emergency 6th companion reopen. The RULED design's own Seams table named crm.customer_group's missing UNIQUE(id,tenant_id) prerequisite (Part B Finding #8), but this build's own Phase 1 planning missed actually executing it — the main 18-table migration failed mid-apply on exactly this missing constraint, requiring an emergency companion migration (20260719000007a_crm_reopen_customer_group_unique.sql) before the build could proceed. Disclosed honestly as a real Part-0/Part-1 planning gap, not silently patched over.
• A missing schema-wide GRANT, self-caught mid-build. The initial migration omitted the standard GRANT USAGE ON SCHEMA ... / GRANT ... ON ALL TABLES ... / ALTER DEFAULT PRIVILEGES ... blanket every new module schema needs — caught live during Phase 5 verification as a genuine permission denied for schema notifications Postgres error, fixed via a dedicated follow-up migration that also re-applies the platform_suppression REVOKE (the blanket grant would otherwise re-open it).

8. Agent Authority Mapping

No new authority mechanism — pure consumer of identity.agent_duty_grant. Full autonomy pack (automation_source/review_status/decision_provenance) on 4 tables: notification_template, notification, in_app_notification, campaign (the flagship draft_only gate). Zero autonomy columns on the other 14 — delivery_attempt/notification_quota_usage/notification_frequency_tracker/campaign_recipient are deterministic execution or mechanical counters; notification_preference/inbound_message are human/customer data capture; the 3 journey tables are dormant; provider_event_log/provider_event_dead_letter/sender_identity are external-evidence-driven, never agent-guessed; suppression/platform_suppression are deliberately may_act_alone (suppression) or not an autonomy surface at all (the platform-owned list).

9. Cross-Module Seams

• notifications → platform: notification.outbox_event_id → platform.outbox(id, tenant_id) (composite, the outbox-riding seam, Ruling 3); delivery_attempt.provider/provider_event_log.provider_code/provider_event_dead_letter.provider_code → platform.processor_catalog.code (Ruling 4, post-widen).
• notifications → crm: campaign.customer_group_id → crm.customer_group(id, tenant_id) (composite); campaign.customer_segment_definition_id → crm.customer_segment_definition.id (bare FK + trigger); notification/in_app_notification/journey_enrollment/notification_frequency_tracker/campaign_recipient/suppression/inbound_message's various *_customer_id → crm.customer.id.
• notifications → identity: every *_actor_id → identity.actor.id.
• notifications → offers: campaign.offer_id → offers.offer(id, tenant_id) (composite).
• notifications → purchasing: inbound_message.vendor_id → purchasing.vendor(id, tenant_id) (composite) — the vendor-reply-attribution seam, closing the PO-acknowledgement gap alongside purchasing.purchase_order.vendor_acknowledged_at/.vendor_acknowledgement_note (companion reopen, write-back half of the same seam).
• notifications internal: notification.reprint_of_notification_id → notification(id, tenant_id) (composite self-FK); delivery_attempt/in_app_notification/inbound_message.related_notification_id/campaign_recipient.notification_id → notification(id, tenant_id) (all composite, zero marginal cost since notification gains UNIQUE(id,tenant_id) in this same migration).
• notifications → orders / billing (companion-reopen-only, no live FK yet): orders.order_fulfillment and billing.ar_statement both gained UNIQUE(id, tenant_id) as prerequisites for a future composite-FK/thin-cache seam — orders' own pickup-ready notification link stays a polymorphic source_module/source_ref for now (no reciprocal column); billing.ar_statement.sent_at/.delivery_method become a NotificationsService-written thin cache (Ruling 15), no schema change on the billing side.
• notifications → approvals (convention note only, no schema change): approvals.approval_delivery's own send-tracking becomes a thin cache written by NotificationsService, the same treatment as billing.ar_statement (Ruling 15) — reconciles OPEN_ITEMS row 275's disclosed duplication.

10. Deferred / Future Items

NotificationsService (the HTTP controller + provider-connector layer) — the next build step, including the actual webhook ingestion endpoints for Resend/Twilio, the retry-sweep/campaign-scheduling pg_cron job definitions, and the source_module/provider_event_log.event_type CHECK vocabularies' Build-time finalization against real producers. A platform-level suppression list already exists (platform_suppression, this build); what remains deferred is the actual cross-tenant hard-bounce/complaint propagation logic that populates it automatically (currently schema-ready, service-layer work). See OPEN_ITEMS for the full deferred list carried from the RULED design.

11. v1 Exclusions Re-Confirmed

None — every v1 table/column survives in this build (11 tables preserved, 3 dormant journey tables kept verbatim with zero guard added since they remain unexercised). This is a pure additive build: +7 tables, +86 columns over the stale v1 placeholder.