Event-driven notification system spanning PostgreSQL (rules, templates, recipients) and MongoDB (delivery tracking, push logs, SMS logs). Covers email, SMS, push, in-app, and webhook channels.
The notification system uses a dual-database architecture. Rules and templates live in PostgreSQL (system.notification_rule, master_data.notification_template) for referential integrity. Delivery logs live in MongoDB (notification_delivery, push_notification_log, sms_delivery_log) for high-write throughput and TTL auto-cleanup. When an application event fires, the Notification Engine evaluates matching rules, renders templates, and dispatches to the appropriate channel — tracking delivery status in MongoDB.
| Status | Description | Allowed Actions | Next States |
|---|---|---|---|
| Queued | Notification created and waiting for dispatch | Send, Cancel | Sending, Cancelled |
| Sending | Being dispatched to delivery channel | Retry on failure | Sent, Failed |
| Sent | Successfully handed to delivery provider | Await delivery confirmation | Delivered, Bounced |
| Delivered | Confirmed delivery to recipient device/inbox | Track read status | Read |
| Read | Recipient opened/acknowledged the notification | Archive | — |
| Failed | Delivery failed after all retry attempts | Manual retry, Investigate | Queued |
| Bounced | Email bounced or phone number invalid | Update contact info | — |
HTML/text email via SMTP or transactional provider (SendGrid, SES). Supports templates with variables, CC/BCC, attachments. Tracks: sent, delivered, opened, bounced, clicked.
Text messages via Twilio, MSG91, or similar gateway. 160-char limit per segment. Tracks: sent, delivered, failed. Supports OTP, alerts, and reminders.
Mobile push via Firebase Cloud Messaging (FCM) / APNs. Supports data payloads, deep links, and rich media. Tracks: sent, delivered, dismissed.
Real-time in-app notifications via WebSocket. Displayed in notification center with read/unread status. Supports priority levels and action buttons.
rule_id (PK) — Unique identifier for the notification ruletenant_id (FK) — Multi-tenant isolation — rules scoped per tenantevent_type — The triggering event (e.g., po_approved, invoice_overdue, grn_received)module — Source module (procurement, finance, hse, etc.)channel — Delivery channel: email, sms, push, in_app, webhookrecipient_type — Who receives: role, user, group, dynamic_fieldcondition_expr — Optional JSON condition (e.g., amount > 100000)template_id (FK) — Links to master_data.notification_templatetemplate_id (PK) — Unique identifier for the templatetemplate_code — Short code for programmatic reference (e.g., PO_APPROVED_EMAIL)channel — Channel this template is designed for (email, sms, push)subject — Email subject or push title with {{variable}} placeholdersbody_template — Full body content with Handlebars/Jinja2 template syntaxvariables — JSON array of expected variables with types and defaultsmodule_code — Module identifier (e.g., procurement, finance, hse)is_enabled — Whether the module is active for this tenantconfig_json — Module-specific configuration including notification preferencesnotification_id — Unique delivery tracking IDrule_id — Reference to RDBMS notification_rule.rule_idrecipient_id — Target user/group IDchannel — Delivery channel usedstatus — Current status: queued → sending → sent → delivered → read / failedsent_at / delivered_at / read_at — Timestamps for each status transitionerror — Error message if delivery failed — TTL: 90 dayspush_id — Unique push delivery IDdevice_token — FCM/APNs device tokenplatform — ios, android, webprovider_response — Raw response from FCM/APNs gatewaystatus — sent, delivered, failed — TTL: 90 dayssms_id — Unique SMS delivery IDphone_number — Recipient phone number (masked for privacy)gateway — SMS gateway used (Twilio, MSG91, etc.)gateway_message_id — Provider-assigned message ID for trackingdelivery_status — submitted, sent, delivered, failed — TTL: 90 daysAn application event occurs (e.g., PO approved, invoice overdue). The event is published to the Notification Engine with event_type, module, and context payload (entity_id, amounts, user references).
The Notification Engine queries system.notification_rule where event_type and module match. Evaluates condition_expr against the event payload. Multiple rules may match (e.g., email to approver + push to requester).
SELECT r.*, t.subject, t.body_template FROM system.notification_rule r JOIN master_data.notification_template t ON r.template_id = t.template_id WHERE r.event_type = 'po_approved' AND r.is_active = true AND r.tenant_id = :tenant_id;
Resolve recipient_type to actual user IDs. For role-based: query admin.user_role. For group-based: expand group membership. For dynamic_field: extract from event payload (e.g., po.created_by).
Load the notification_template and render with event context variables. For email: render HTML subject + body. For SMS: render plain text within 160-char limit. For push: render title + body + data payload.
Route the rendered notification to the appropriate delivery channel. Email → SMTP/SendGrid. SMS → Twilio/MSG91. Push → FCM/APNs. In-App → WebSocket broadcast. Webhook → HTTP POST to configured URL.
Create a notification_delivery document in MongoDB with status "queued". Update status as delivery progresses: sending → sent → delivered → read. Log channel-specific details to push_notification_log or sms_delivery_log.
// Insert delivery tracking document db.notification_delivery.insertOne({ notification_id: ObjectId(), rule_id: "rule_123", recipient_id: "user_456", channel: "email", template: "PO_APPROVED_EMAIL", payload: { subject: "PO #1234 Approved", body: "..." }, status: "queued", sent_at: null, delivered_at: null, read_at: null, error: null });
Failed deliveries are retried with exponential backoff (1min, 5min, 30min). After 3 failed attempts, move to dead letter queue. Alert operations team for persistent failures (e.g., invalid device tokens, bounced emails).
async def process_event(event_type, module, tenant_id, context): # 1. Find matching rules rules = await db.execute( """SELECT r.*, t.subject, t.body_template, t.variables FROM system.notification_rule r JOIN master_data.notification_template t ON r.template_id = t.template_id WHERE r.event_type = :evt AND r.module = :mod AND r.tenant_id = :tid AND r.is_active = true""", {"evt": event_type, "mod": module, "tid": tenant_id} ) for rule in rules: # 2. Evaluate condition if rule.condition_expr and not evaluate_condition(rule.condition_expr, context): continue # 3. Resolve recipients recipients = await resolve_recipients(rule.recipient_type, rule.recipient_value, context) # 4. Render template rendered = render_template(rule.subject, rule.body_template, context) # 5. Dispatch to channel for recipient in recipients: await dispatch(rule.channel, recipient, rendered)
// Get user's notification preferences from MongoDB db.user_preference.findOne( { user_id: "user_456" }, { projection: { notification_channels: 1, quiet_hours: 1, dnd_enabled: 1 } } ) // Check user alert config for custom thresholds db.user_alert_config.find({ user_id: "user_456", module: "procurement", is_active: true })