notifications — Phase 10

Schema locked 2026-06-10. 11 tables, 166 cols: notification_template (13), notification (26), delivery_attempt (19), notification_preference (13), in_app_notification (17), notification_quota_usage (9), inbound_message (14), notification_journey (11), journey_step (14), journey_enrollment (15), campaign (15).

Notifications is the event-driven delivery orchestrator. It consumes events from every module, decides who gets notified on which channel and in what shape, enforces preferences and quotas, and dispatches via IntegrationsService. All tenant-scoped; all 11 tables carry RLS on tenant_id.

Design principles (document at every session touching this schema):

• Consent is CRM's, queried at send-time — Notifications has zero consent tables. Marketing consent is read from crm.customer_consent / crm.customer.marketing_opt_in at dispatch. Opt-out events are written to crm.customer_consent via CRMService. notification_preference is product preference (channel/category/quiet-hours), NOT legal marketing consent. This distinction is legally load-bearing — do not merge them.
• notification is the convergence point. 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 channel. One pipeline; origin discriminated by origin_type + source fields. There is no separate "campaign send" table or "journey send" table — everything converges here.
• Providers are Integrations'. Notifications stores provider message refs and status; actual delivery executes via IntegrationsService (Resend for email, Twilio for SMS, FCM/APNS for push). The Integrations module is not yet built — provider refs are text seams. Notifications does NOT rebuild delivery infrastructure.
• Quota: Platform owns the LIMIT; Notifications owns the USAGE. notification_quota_usage tracks sent counts per channel per period. At dispatch, NotificationsService reads the tier entitlement from PlatformService (platform.tenant_entitlement), compares to sent_count, gates if over, and increments on successful send.
• Transactional vs. marketing via is_transactional. Transactional notifications (receipts, order-ready, operational alerts) skip the marketing-consent check. Marketing notifications require CRM consent. The is_transactional flag is denormalized onto both notification_template and notification so the dispatch pipeline can gate without a join.
• delivery_attempt is mutable and covers provider-delivered channels only. A notification's requested_channels may include 'in_app', but in-app fans out to an in_app_notification row (the persistent inbox item), NOT a delivery_attempt. delivery_attempt is for provider-delivered channels (email/sms/push) — rows with provider refs + engagement timestamps that update as provider webhooks arrive. Journey branches read opened_at/clicked_at from here. This is NOT append-only.
• in_app_notification is a persistent inbox, not a delivery log. It carries is_read, read_at, and action buttons — it is stateful per recipient. It exists separately from delivery_attempt because in-app is a UI feed, not a fire-and-forget send. In-app has no provider, no delivery_attempt row, and is not metered against quota.
• No campaign_recipient table in v1. Campaign recipients = the notification rows with campaign_id = this. A campaign_recipient table (for pre-send audience snapshots / suppression reporting) is a documented deferred item.
• AuditService logs sends. Each dispatch emits to audit.audit_log via AuditService. Notifications does not own a separate compliance log for sends — that's audit's cross-cutting trail.
• Event isolation. notifications.* events are not subscribed by operational modules (no event cycles). See audit module for the same principle.

Cross-Phase FK seams:

Column	References	Status
*.tenant_id	platform.tenant	Locked — enforced
notification.recipient_customer_id, notification_preference.customer_id, in_app_notification.recipient_customer_id, inbound_message.customer_id, journey_enrollment.recipient_customer_id	crm.customer	Locked — enforced (nullable where recipient may be staff/system)
notification.recipient_user_id, notification_preference.user_id, notification.created_by_user_id, delivery_attempt (via notification), in_app_notification.recipient_user_id, inbound_message.handled_by_user_id, journey_enrollment.recipient_user_id, campaign.created_by_user_id	identity.identity_user	Locked — enforced (nullable)
notification.template_id, journey_step.template_id, campaign.template_id	notifications.notification_template	Intra-schema — enforced (nullable; manual/system sends may have no template)
delivery_attempt.notification_id	notifications.notification	Intra-schema — enforced
in_app_notification.notification_id	notifications.notification	Intra-schema — enforced (nullable; in-app items may be created directly)
notification.campaign_id, campaign.id referenced by notification	notifications.campaign	Intra-schema — enforced (nullable)
notification.journey_enrollment_id, notification.journey_step_id	notifications.journey_enrollment, notifications.journey_step	Intra-schema — enforced (nullable)
journey_step.journey_id	notifications.notification_journey	Intra-schema — enforced
journey_enrollment.journey_id, journey_enrollment.current_step_id	notifications.notification_journey, notifications.journey_step	Intra-schema — enforced (nullable)
journey_step.next_step_id, journey_step.fallback_step_id	notifications.journey_step (self-ref)	Intra-schema — enforced (nullable)
inbound_message.related_notification_id	notifications.notification	Intra-schema — enforced (nullable)
notification.source_ref	Any module's triggering record (e.g. pos.sale, orders.order_header, audit.data_subject_request)	Polymorphic — NOT enforced FK; source_module + source_type are the discriminators; loose by design
NotificationsService reads crm.customer_consent + crm.customer.marketing_opt_in	crm schema	Read seam at dispatch — not a stored FK; queried per send to gate marketing consent
NotificationsService reads platform.tenant_entitlement	platform schema	Read seam at dispatch — quota limit lookup
NotificationsService emits sends to audit.audit_log via AuditService	audit schema	Write seam — documented; not a FK
Provider message refs (delivery_attempt.provider_message_ref)	Integrations module (Resend/Twilio/FCM/APNS)	NOT CLOSED — Integrations not built; text seams only

Deferred items:

• campaign_recipient table — when pre-send audience snapshots / suppression reporting (targeted / excluded / suppressed-by-consent) are needed.
• message_conversation (threaded inbox, assignment, SLA) — when staff need threaded two-way conversations; v1 inbound_message is single-message intake only.
• journey_branch / journey_step_condition child tables — branch_condition_json covers v1 branching; add child tables only if branching outgrows JSONB.
• All 9 AI features (predictive send-time, cost-optimized channel, spam-score, template AI assist, etc.) — depend on the AI module (not built); add when AI module ships.
• Advanced analytics (open/click dashboards, campaign ROI, cross-channel aggregate reports) — v1.1; Reporting module integration. v1 tracks raw engagement on delivery_attempt.
• Loyalty notifications (7.7) — depend on Consumer Layer rewards module (not built).

---

notifications.notification_template — 13 cols

Template definitions per tenant with per-channel copy variants and versioning. A template declares which channels it supports via channel_variants; is_transactional gates whether the consent check fires at dispatch.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
name	text	NOT NULL	—	Human-readable template name (e.g. 'Order Ready — Email')
notification_type	text	NOT NULL	—	Free-text type code (e.g. 'receipt', 'order_ready', 'order_confirmation', 'low_stock_alert', 'marketing_promo', 'sar_deadline'). Transactional vs. marketing classification is via is_transactional, not this field.
is_transactional	boolean	NOT NULL	true	true = skips marketing-consent check at dispatch; false = requires CRM consent. Denormalized onto notification for dispatch gating without a join.
audience	text	NOT NULL	'customer'	CHECK IN ('customer','staff','system')
channel_variants	JSONB	NOT NULL	—	Per-channel copy — only the channels this template supports. Example: {"email":{"subject":"Your order is ready","body":"Hi {{name}}..."},"sms":{"body":"Your order is ready. Reply STOP to opt out."},"push":{"title":"Order Ready","body":"Tap to view."}}
variables	JSONB	nullable	—	Declared substitution variables for this template. Example: ["name","order_number","pickup_date"]
version	integer	NOT NULL	1	Integer version number. Incremented on template edits.
is_active	boolean	NOT NULL	true	Only active templates are selectable for new sends.

Indexes:

• PK on id
• on (tenant_id)
• on (notification_type)
• UNIQUE on (tenant_id, name, version) WHERE deleted_at IS NULL

---

notifications.notification — 26 cols

The convergence envelope — one row per send intent per recipient, regardless of origin (transactional event, campaign, journey, manual). Fans out to delivery_attempt rows per channel. Status reflects the overall dispatch outcome.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
template_id	UUID	nullable	—	FK → notifications.notification_template; NULL for system/manual sends without a template
notification_type	text	NOT NULL	—	Denormalized from template or set at origin (e.g. 'order_ready', 'marketing_promo'). Enables dispatch routing without template join.
is_transactional	boolean	NOT NULL	—	Denormalized from template. Drives consent-check skip at dispatch. true = transactional (no consent gate); false = marketing (CRM consent required).
audience	text	NOT NULL	—	CHECK IN ('customer','staff','system')
origin_type	text	NOT NULL	—	CHECK IN ('transactional_event','campaign','journey','manual')
source_module	text	nullable	—	For transactional_event origin — the module that emitted the trigger (e.g. 'pos', 'orders', 'audit'). NULL for campaign/manual.
source_type	text	nullable	—	The event or entity type within source_module (e.g. 'sale', 'order_header')
source_ref	UUID	nullable	—	The triggering record's id. Polymorphic — not enforced FK; source_module + source_type are the discriminators.
campaign_id	UUID	nullable	—	FK → notifications.campaign; set when origin_type = 'campaign'
journey_enrollment_id	UUID	nullable	—	FK → notifications.journey_enrollment; set when origin_type = 'journey'
journey_step_id	UUID	nullable	—	FK → notifications.journey_step; the specific step that generated this send
recipient_customer_id	UUID	nullable	—	FK → crm.customer; the customer recipient
recipient_user_id	UUID	nullable	—	FK → identity.identity_user; the staff recipient
recipient_contact	JSONB	nullable	—	Resolved email/phone/push-token snapshot at send-time. Example: {"email":"jane@example.com","phone":"+15005550006","push_token":"abc123"}. Snapshot so the send is tied to the address actually used, not the current address.
requested_channels	JSONB	NOT NULL	—	Which channels to attempt. Example: ["email","sms","push"]
status	text	NOT NULL	'pending'	CHECK IN ('pending','consent_blocked','preference_blocked','quota_blocked','queued','sent','partially_sent','failed','cancelled'). Consent check is done at dispatch — if blocked, status → 'consent_blocked'; the decision is NOT stored as a durable consent record (that's CRM's).
dedup_key	text	nullable	—	Idempotency key — same logical notification not sent twice to the same recipient in the same context.
scheduled_for	timestamptz	nullable	—	NULL = deliver immediately; non-NULL = deliver at this time
priority	text	NOT NULL	'normal'	CHECK IN ('low','normal','high')
template_data	JSONB	nullable	—	Variable values for template substitution at render time. Example: {"name":"Jane","order_number":"ORD-1042","pickup_date":"June 12"}
created_by_user_id	UUID	nullable	—	FK → identity.identity_user; the staff member who triggered a manual send. NULL for automated.

Table-level CHECKs:

• CHECK (num_nonnulls(recipient_customer_id, recipient_user_id) = 1) — exactly one recipient: either a customer or a staff user, never both, never neither
• CHECK ( (origin_type = 'campaign' AND campaign_id IS NOT NULL AND journey_enrollment_id IS NULL AND journey_step_id IS NULL AND source_ref IS NULL) OR (origin_type = 'journey' AND journey_enrollment_id IS NOT NULL AND journey_step_id IS NOT NULL AND campaign_id IS NULL) OR (origin_type = 'transactional_event' AND source_module IS NOT NULL AND source_ref IS NOT NULL AND campaign_id IS NULL AND journey_enrollment_id IS NULL) OR (origin_type = 'manual' AND campaign_id IS NULL AND journey_enrollment_id IS NULL AND journey_step_id IS NULL AND source_ref IS NULL) ) — the convergence point's origin_type cannot lie about which refs are populated

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status IN ('pending','queued')
• on (campaign_id) WHERE campaign_id IS NOT NULL
• on (journey_enrollment_id) WHERE journey_enrollment_id IS NOT NULL
• on (recipient_customer_id)
• on (source_module, source_ref) WHERE source_ref IS NOT NULL
• on (scheduled_for) WHERE scheduled_for IS NOT NULL
• UNIQUE on (tenant_id, dedup_key) WHERE dedup_key IS NOT NULL AND deleted_at IS NULL

---

notifications.delivery_attempt — 19 cols

Provider-channel execution record for each send attempt. One notification → N delivery_attempt rows, one per provider-delivered channel (email, sms, push). In-app notifications do NOT create a delivery_attempt row — they fan out to in_app_notification (the persistent inbox item) directly; in-app has no provider, no ref, and no engagement webhooks. Status and engagement timestamps mutate post-insert as provider webhooks arrive. Journey branches read opened_at/clicked_at here to evaluate conditions.

NOT append-only — status, engagement timestamps (sent_at, delivered_at, opened_at, clicked_at, bounced_at, failed_at), and retry_count all mutate after insert.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
notification_id	UUID	NOT NULL	—	FK → notifications.notification
channel	text	NOT NULL	—	CHECK IN ('email','sms','push') — provider-delivered channels only; 'in_app' fans out to in_app_notification, never a delivery_attempt
provider	text	nullable	—	Which provider executed the send (e.g. 'resend', 'twilio', 'fcm'). Set by IntegrationsService at send-time.
provider_message_ref	text	nullable	—	Provider's message ID (Resend email ID, Twilio SID, etc.). Text seam — Integrations module not yet built.
status	text	NOT NULL	'queued'	CHECK IN ('queued','sending','sent','delivered','failed','bounced')
sent_at	timestamptz	nullable	—	When the provider accepted the message
delivered_at	timestamptz	nullable	—	When the provider confirmed delivery to the endpoint
opened_at	timestamptz	nullable	—	When the recipient opened the message (email pixel / push open event). Journey branches evaluate this.
clicked_at	timestamptz	nullable	—	When the recipient clicked a tracked link. Journey branches evaluate this.
failed_at	timestamptz	nullable	—	When this attempt permanently failed
bounced_at	timestamptz	nullable	—	When a hard or soft bounce was reported
failure_reason	text	nullable	—	Provider error message or failure code
retry_count	integer	NOT NULL	0	Number of retry attempts made so far
idempotency_key	text	nullable	—	Outgoing-send dedup to provider — prevents double-send on retry

Indexes:

• PK on id
• on (tenant_id)
• on (notification_id, channel) — covers both "all attempts for notification X" and "specific-channel attempt for notification X" (leftmost-prefix covers notification-only queries; composite serves journey branch evaluation directly)
• on (status) WHERE status IN ('queued','sending','failed')
• on (provider_message_ref) WHERE provider_message_ref IS NOT NULL
• on (opened_at) WHERE opened_at IS NOT NULL — journey engagement-branch lookup
• UNIQUE on (tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL AND deleted_at IS NULL

---

notifications.notification_preference — 13 cols

Per-recipient product preference — channel opt-in/out, category opt-in/out, and per-recipient quiet-hours. This is NOT legal marketing consent (crm.customer_consent owns that). A customer disabling push notifications is a product preference; their GDPR marketing consent is a separate, CRM-managed record.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
customer_id	UUID	nullable	—	FK → crm.customer; set for customer preferences
user_id	UUID	nullable	—	FK → identity.identity_user; set for staff preferences
channel	text	NOT NULL	—	CHECK IN ('email','sms','push','in_app','all') — 'all' = global opt-out of all channels
category	text	nullable	—	Notification type group this preference applies to (e.g. 'marketing', 'order_updates'). NULL = applies to all categories on this channel.
is_enabled	boolean	NOT NULL	true	false = opted out of this channel/category combination
quiet_hours_start	time	nullable	—	Per-recipient quiet-hours window start (e.g. 22:00)
quiet_hours_end	time	nullable	—	Per-recipient quiet-hours window end (e.g. 07:00)
timezone	text	nullable	—	IANA timezone for quiet-hours evaluation (e.g. 'America/New_York'). Falls back to tenant timezone if NULL.

Table-level CHECKs:

• CHECK (num_nonnulls(customer_id, user_id) = 1) — exactly one recipient type: customer preference or staff preference, never both and never neither

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id)
• on (user_id)
• UNIQUE on (tenant_id, customer_id, channel, category) WHERE customer_id IS NOT NULL AND deleted_at IS NULL — two-index NULL-safe uniqueness for customer prefs
• UNIQUE on (tenant_id, user_id, channel, category) WHERE user_id IS NOT NULL AND deleted_at IS NULL — two-index NULL-safe uniqueness for staff prefs

---

notifications.in_app_notification — 17 cols

Persistent per-recipient in-app inbox items. Unlike delivery_attempt (a send-log), these rows represent the notification as it lives in the recipient's feed — with read state, action buttons, and expiry. Stateful: is_read and read_at update when the recipient dismisses or views the notification.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
notification_id	UUID	nullable	—	FK → notifications.notification; the send that generated this inbox item. NULL for directly-created in-app items (admin announcements, etc.).
recipient_customer_id	UUID	nullable	—	FK → crm.customer
recipient_user_id	UUID	nullable	—	FK → identity.identity_user
title	text	NOT NULL	—	Notification title (rendered from template at creation time)
body	text	NOT NULL	—	Notification body (rendered from template at creation time)
action_url	text	nullable	—	Deep-link or URL for the primary action button
action_label	text	nullable	—	Label for the primary action button (e.g. 'View Order')
is_read	boolean	NOT NULL	false	Whether the recipient has read/dismissed this notification
read_at	timestamptz	nullable	—	When is_read was set to true
category	text	nullable	—	Category label for filtering/grouping in the feed
priority	text	NOT NULL	'normal'	CHECK IN ('low','normal','high')
expires_at	timestamptz	nullable	—	If set, this in-app notification is hidden/expired after this timestamp (e.g. "order ready — valid 4 hours")

Table-level CHECKs:

• CHECK (num_nonnulls(recipient_customer_id, recipient_user_id) = 1) — exactly one recipient

Indexes:

• PK on id
• on (tenant_id)
• on (recipient_customer_id) WHERE is_read = false — unread feed query for customer
• on (recipient_user_id) WHERE is_read = false — unread feed query for staff
• on (expires_at) WHERE expires_at IS NOT NULL — expiry sweep

---

notifications.notification_quota_usage — 9 cols

Per-tenant per-channel per-period usage counter for provider-delivered channels (email, sms, push). Platform owns the tier limit (read from platform.tenant_entitlement at dispatch via PlatformService); this table owns the count. Dispatch gates on sent_count >= limit and increments on successful send. 'in_app' is intentionally excluded — in-app delivery has no external provider cost and is unmetered.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
channel	text	NOT NULL	—	CHECK IN ('email','sms','push') — 'in_app' intentionally excluded: no provider cost, unmetered
period_start	date	NOT NULL	—	The billing / quota period start date
period_end	date	NOT NULL	—	The billing / quota period end date
sent_count	integer	NOT NULL	0	Running count of messages sent on this channel in this period. Incremented atomically at dispatch.

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, channel, period_start) WHERE deleted_at IS NULL

---

notifications.inbound_message — 14 cols

Incoming message replies from customers or external contacts (SMS STOP/HELP/REPLY, email replies). Simple single-message intake for v1. A message_conversation table (threaded inbox, assignment, SLA tracking) is a documented future deferred item.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
channel	text	NOT NULL	—	CHECK IN ('sms','email')
from_contact	text	NOT NULL	—	Phone number or email address the reply came from
body	text	nullable	—	Message content (NULL for empty or non-text messages)
customer_id	UUID	nullable	—	FK → crm.customer; resolved from from_contact if a matching customer is found. NULL if unmatched.
related_notification_id	UUID	nullable	—	FK → notifications.notification; the outbound message this replies to, if determinable.
status	text	NOT NULL	'new'	CHECK IN ('new','handled','ignored')
handled_by_user_id	UUID	nullable	—	FK → identity.identity_user; staff who acted on this message
handled_at	timestamptz	nullable	—	When status moved to 'handled' or 'ignored'
received_at	timestamptz	NOT NULL	—	When the message was received from the provider

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status = 'new' — staff inbox queue
• on (customer_id)
• on (related_notification_id)

---

notifications.notification_journey — 11 cols

Journey definitions: multi-step automated notification workflows triggered by events, manual enrollment, or segment criteria. A journey is the blueprint; journey_enrollment holds per-recipient state.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
name	text	NOT NULL	—	Journey name (e.g. 'Post-Purchase Drip', 'Re-Engagement')
description	text	nullable	—	Internal description
trigger_type	text	NOT NULL	—	CHECK IN ('event','manual','segment') — what causes enrollment
trigger_config	JSONB	nullable	—	Trigger criteria. Example for event: {"event":"orders.order_fulfilled","filter":{"order_type":"special_order"}}
status	text	NOT NULL	'draft'	CHECK IN ('draft','active','paused','archived')
audience	text	NOT NULL	'customer'	CHECK IN ('customer','staff')

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status = 'active'
• on (trigger_type)
• UNIQUE on (tenant_id, name) WHERE deleted_at IS NULL

---

notifications.journey_step — 14 cols

Ordered steps within a journey. Supports send, wait, branch, and exit steps. Branch conditions are stored as JSONB (evaluated at enrollment-advance time by reading delivery_attempt.opened_at/clicked_at). Child tables for branching (journey_branch, journey_step_condition) are a documented deferred item — branch_condition_json covers v1 needs.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
journey_id	UUID	NOT NULL	—	FK → notifications.notification_journey
step_order	integer	NOT NULL	—	Execution order within the journey (1-based)
step_type	text	NOT NULL	—	CHECK IN ('send','wait','branch','exit')
template_id	UUID	nullable	—	FK → notifications.notification_template; used for step_type = 'send'
channel_policy	JSONB	nullable	—	Which channels and priority for this step's send. Example: {"channels":["email","sms"],"priority":"email_first"}
delay_config	JSONB	nullable	—	For step_type = 'wait'. Example: {"duration":3,"unit":"days"}
branch_condition_json	JSONB	nullable	—	For step_type = 'branch'. Evaluated by reading delivery_attempt engagement. Example: {"if":"opened","within":"3d","within_step":"previous"} — if the prior send was opened within 3 days, take next_step_id; otherwise take fallback_step_id.
next_step_id	UUID	nullable	—	FK → notifications.journey_step (self-ref); the default next step (or the branch-true path)
fallback_step_id	UUID	nullable	—	FK → notifications.journey_step (self-ref); the branch-else path. NULL if no fallback (enrollment exits or waits).

Table-level CHECKs:

• CHECK ( (step_type = 'send' AND template_id IS NOT NULL) OR (step_type = 'wait' AND delay_config IS NOT NULL) OR (step_type = 'branch' AND branch_condition_json IS NOT NULL) OR step_type = 'exit' ) — each step type requires its defining config to be non-null; 'exit' needs nothing

Indexes:

• PK on id
• on (tenant_id)
• on (journey_id)
• UNIQUE on (journey_id, step_order) WHERE deleted_at IS NULL — no two steps may share the same order within a journey; deterministic engine ordering

---

notifications.journey_enrollment — 15 cols

Per-recipient journey state — the stateful half of the journey engine. One row per recipient per journey enrollment. next_action_at is the scheduler hook: a job sweeps status = 'active' AND next_action_at <= now() and advances each enrollment to its next step.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
journey_id	UUID	NOT NULL	—	FK → notifications.notification_journey
recipient_customer_id	UUID	nullable	—	FK → crm.customer
recipient_user_id	UUID	nullable	—	FK → identity.identity_user
current_step_id	UUID	nullable	—	FK → notifications.journey_step; where this recipient currently is in the journey. NULL = not yet started.
status	text	NOT NULL	'active'	CHECK IN ('active','completed','exited','paused')
enrolled_at	timestamptz	NOT NULL	—	When the recipient was enrolled
entered_step_at	timestamptz	nullable	—	When the recipient reached current_step_id — the clock for wait and branch evaluation
next_action_at	timestamptz	nullable	—	When the journey engine should next advance this enrollment (for wait steps and scheduled sends). The scheduler sweep index targets this column.
completed_at	timestamptz	nullable	—	When the journey reached its final step or exit
exit_reason	text	nullable	—	Why the enrollment exited early (e.g. 'opted_out', 'manual_exit', 'journey_archived')

Table-level CHECKs:

• CHECK (num_nonnulls(recipient_customer_id, recipient_user_id) = 1) — exactly one recipient type per enrollment row

Indexes:

• PK on id
• on (tenant_id)
• on (journey_id)
• on (status) WHERE status = 'active'
• on (next_action_at) WHERE status = 'active' AND next_action_at IS NOT NULL — the scheduler sweep
• on (recipient_customer_id)
• on (recipient_user_id)
• UNIQUE on (tenant_id, journey_id, recipient_customer_id) WHERE recipient_customer_id IS NOT NULL AND deleted_at IS NULL — one enrollment per customer per journey; re-enrollment requires explicit delete-and-re-enroll
• UNIQUE on (tenant_id, journey_id, recipient_user_id) WHERE recipient_user_id IS NOT NULL AND deleted_at IS NULL — parallel staff-recipient uniqueness; same re-enrollment rule applies

---

notifications.campaign — 15 cols

Bulk send to a tenant-defined segment. Campaign recipients are the notification rows with campaign_id = this.id — no separate campaign_recipient table in v1 (documented deferred item when pre-send audience snapshots or suppression reporting are needed).

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
name	text	NOT NULL	—	Campaign name (e.g. 'Spring Sale 2026 — Email')
description	text	nullable	—	Internal description
template_id	UUID	nullable	—	FK → notifications.notification_template; the template used for this campaign
audience_criteria	JSONB	nullable	—	Segment definition — who to target. Example: {"customer_group_id":"uuid","tags":["high_value"],"opted_in_channel":"email"}. Evaluated at send time by NotificationsService querying CRM.
channels	JSONB	NOT NULL	—	Which channels to send on. Example: ["email","sms"]
status	text	NOT NULL	'draft'	CHECK IN ('draft','scheduled','sending','sent','paused','cancelled')
scheduled_for	timestamptz	nullable	—	When to start sending; NULL = send immediately on launch
sent_at	timestamptz	nullable	—	When the campaign send actually started
recipient_count	integer	nullable	—	Number of recipients resolved at send time
created_by_user_id	UUID	nullable	—	FK → identity.identity_user; the staff member who created or launched the campaign

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status IN ('scheduled','sending')
• on (scheduled_for) WHERE scheduled_for IS NOT NULL
• UNIQUE on (tenant_id, name) WHERE deleted_at IS NULL

Column counts: notification_template(13) + notification(26) + delivery_attempt(19) + notification_preference(13) + in_app_notification(17) + notification_quota_usage(9) + inbound_message(14) + notification_journey(11) + journey_step(14) + journey_enrollment(15) + campaign(15) = 166

---