SMS-Push Module¶

Validation legend: ✅ verified in code · 📄 from business/PM source · 💻 verified this review · ♻️ duplicate/overlapping · ⚰️ dead/legacy · ⚠️ needs verification / open bug

1. Overview¶

What: The SMS-Push module is the channel delivery layer that turns queued messages into real sends. It owns two of Prism's four channels: - SMS — DLT-compliant Indian SMS via Gupshup, physically dispatched through the MySQL outbox_shubham outbox table. - Push — Firebase Cloud Messaging (FCM) for App Push (Android "A", iOS "I") and Web Push ("w").

Why it matters: This layer is where the client's money is actually spent (sendSms debits the wallet) and where DLT / opt-in compliance is enforced. Delivery accuracy directly feeds the campaign MIS numbers flagged in E1.3 (WhatsApp volume) and E1.4 (CRM stats) — SMS/Push counts must be right for those to be right.

Producers vs consumers:

Role	SMS	Push
Producer (enqueue)	`smsCampaignQueue.js` → `sms_queue_{tomorrow}`; `sendSms.js` (debits + dispatches)	`pushCampaignQueue.js` → `push_noti_queue_{tomorrow}`
Queue	`sms_queue_YYYYMMDD` (`processed:false`)	`push_noti_queue_YYYYMMDD` (`processed:false`)
Consumer (cron)	`smsQueueCron.js` `*/3` (batch 8000) → `sendAutoCampaignSms`	`pushQueueCron.js` `*/5` (batch 1000) → `processNotiQueue`
Send transport	MySQL `outbox_shubham` (Gupshup SDK)	Firebase (Admin SDK + HTTP `fcm/send`)
Audit	`sms_summary`, `wallet_transaction`	`fcmjunk`, `campaign_mis`
Click tracking	—	`trackPushCampaignClicksCron.js` `*/10` → `addo_campaigns.total_click_count`

2. Business Flow¶

flowchart LR
    C[Campaign / Journey builder] --> P{Channel}
    P -->|SMS| SQ[(sms_queue_YYYYMMDD)]
    P -->|Push| PQ[(push_noti_queue_YYYYMMDD)]
    SQ --> SC[smsQueueCron */3]
    SC --> OB[(MySQL outbox_shubham)]
    OB --> GW[Gupshup SMS gateway]
    GW --> PHONE[Customer phone]
    PQ --> PC[pushQueueCron */5]
    PC --> FB[Firebase FCM]
    FB --> DEV[App / Browser]
    SC --> WAL[wallet debit + wallet_transaction]
    SC --> SS[(sms_summary)]
    PC --> FJ[(fcmjunk)]
    DEV -->|click| TC[track_push_clicks]
    TC --> TCC[trackPushCampaignClicksCron */10]
    TCC --> AC[addo_campaigns.total_click_count]

Example — a push blast: Campaign type 2 (app push) resolves os = "A,I"; type 5 (web push) resolves os = "w". Rows land in push_noti_queue_{tomorrow}. At the next */5 tick, pushQueueCron groups rows by (businessId, os, title, message, image, campaign_id, fcm_token) and calls processNotiQueue, which loads the business's Firebase cert_key and sends via Admin SDK. Errors and batches are logged to fcmjunk. Clicks arrive at track_push_clicks and are rolled into addo_campaigns.total_click_count every 10 min.

Edge cases: - DLT rejection → SMS sms_summary.status = 2 (failed). - Missing Firebase cert ✅ → processNotiQueue marks the row error:"Cert key not available", processed:true. - iOS special-case ✅ — parent_business_id == "26837" uses cert_key_ios for iOS instead of cert_key. - Web push tokens ⚠️ — NOT stored in fcm_tokens; web push uses separate infrastructure outside sendFcmNotification.js/processNotiQueue.js.

3. Technical Flow¶

sequenceDiagram
    autonumber
    participant B as Campaign/Journey builder
    participant SQ as sms_queue_YYYYMMDD
    participant CRON as smsQueueCron (*/3)
    participant CONS as sendAutoCampaignSms
    participant OB as MySQL outbox_shubham
    participant GS as Gupshup
    participant WAL as sendSms (wallet)
    participant SS as sms_summary

    B->>SQ: insertMany(rows, execution_time)
    Note over CRON: every 3 min IST
    CRON->>SQ: find {processed:false, execution_time<=now} limit 8000
    CRON->>CONS: sendSmsCampaign(rows)
    CONS->>OB: bulk INSERT (campaignId, senderId, mobileNo, smsText...)
    OB->>GS: dispatch
    CONS->>SS: log delivery status
    Note over WAL: cost path (sendSms.js)
    WAL->>WAL: sub_total = sms_cost*units; +GST unless gst_deduction==0
    WAL->>WAL: business_configs.wallet_balance $inc -total
    WAL->>SS: wallet_transactions {service_id:1,"debit"}
    CONS->>SQ: mark processed / update addo_campaigns.sent_count

OS / channel mapping ✅:

`os` value	Channel	Campaign type
`"A"`	Android app push	`campaign_type == "2"`
`"I"`	iOS app push	`campaign_type == "2"`
`"w"`	Web push	`campaign_type == "5"`

4. Architecture Diagram¶

flowchart TD
    subgraph Producers
      SCQ[smsCampaignQueue.js]
      PCQ[pushCampaignQueue.js]
      SS0[sendSms.js<br/>debit + dispatch]
    end
    SCQ --> SQ[(sms_queue_YYYYMMDD)]
    PCQ --> PQ[(push_noti_queue_YYYYMMDD)]
    SQ --> SCRON[smsQueueCron */3<br/>batch 8000]
    SCRON --> SAC[sendAutoCampaignSms]
    SAC --> OB[(MySQL outbox_shubham)]
    SAC --> SMS_S[(sms_summary)]
    SS0 --> WT[(wallet_transaction)]
    SS0 --> BC[(business_configs.wallet_balance)]
    PQ --> PCRON[pushQueueCron */5<br/>batch 1000, group]
    PCRON --> PNQ[processNotiQueue]
    PNQ --> FADM[firebase-admin SDK]
    PNQ --> FCMJ[(fcmjunk)]
    SFN[sendFcmNotification.js] --> FHTTP[fcm.googleapis.com/fcm/send]
    SPN[sendPushNotification.js] --> CERT[business_configs.cert_key / cert_key_ios]
    TPC[track_push_clicks] --> TCRON[trackPushCampaignClicksCron */10]
    TCRON --> ADC[(addo_campaigns.total_click_count)]
    subgraph MIS
      CM[(campaign_mis:<br/>android/ios/web push counts)]
    end
    PNQ --> CM
    SAC --> CM

5. Folder Structure¶

uengage-crm/
├── commonFunctions/
│   ├── sendSms.js                 ✅ cost calc + wallet debit + outbox_shubham insert (Gupshup SDK)
│   ├── sendAutoCampaignSms.js     ✅ batch consumer for sms_queue_* (no debit); updates addo_campaigns + sms_cron_status
│   ├── smsCampaignQueue.js        ✅ insertMany → sms_queue_{tomorrow}
│   ├── pushCampaignQueue.js       ✅ insertMany → push_noti_queue_{tomorrow}
│   ├── sendFcmNotification.js     ✅ HTTP POST fcm/send; fcmjunk logging; A=high / I=notification
│   ├── sendPushNotification.js    ✅ loads cert_key (+cert_key_ios for 26837)
│   └── processNotiQueue.js        ✅ firebase-admin send; per-batch app init + delete; error handling
├── crons/
│   ├── smsQueueCron.js            ✅ "*/3 * * * *" batch 8000; sms_cron_status stuck-detect
│   ├── pushQueueCron.js           ✅ "*/5 * * * *" batch 1000; group by os/msg; push_cron_status
│   └── trackPushCampaignClicksCron.js ✅ "*/10 * * * *" batch 5000 → total_click_count
├── models/
│   ├── sms_summary.js             ✅ (index: mobile_number)
│   ├── notiJunk.js                ✅ collection fcmjunk
│   └── campaign_mis.js            ✅ android/ios/web push counters
└── (MySQL) outbox_shubham         ✅ physical SMS outbox

6. Database¶

MongoDB collections & queues¶

Collection	Purpose	Key fields	Index
`sms_queue_YYYYMMDD` ✅	Daily SMS send queue.	`message`/`smsText`, `sender_id`, `campaign_id`, `mobile_number`, `execution_time`, `processed` (bool)	⚠️ add `(processed, execution_time)`
`push_noti_queue_YYYYMMDD` ✅	Daily push send queue.	`title`, `message`, `image`, `campaign_id`, `fcm_token`, `businessId`, `parentBusinessId`, `os`, `execution_time`, `processed`	⚠️ same
`sms_summary` ✅	Per-SMS delivery log.	`campaign_id`, `mobile_number` (indexed), `status` (0 queued/1 delivered/2 failed), `delivered_at`, `inserted_date`	`mobile_number`
`fcmjunk` (`notiJunk.js`) ✅	FCM batch/error log.	`os`, `tokens_batch`, `tokens_array`, `output`, `error`, `campaign_id`, `parent_business_id`, `mobile_numbers`, `status`	none
`track_push_clicks` ✅	Push click events.	`campaign_id`, `processed`	⚠️ add `processed`
`campaign_mis` ✅	Push counters by OS.	`total_android_notifications`, `total_ios_notifications`, `total_web_push_campaigns`, `total_web_push_notifications`, `total_push_noti_campaigns`	none
`sms_cron_status` / `push_cron_status` ✅	Stuck-cron guards.	`status`, `start_time`, `end_time`	none

MySQL¶

Table	Purpose	Columns (verified)
`outbox_shubham` ✅	Physical SMS outbox → Gupshup.	`campaignId`, `routeId`, `userId`, `senderId`, `mobileNo`, `smsText`, `smsTextCredits`, `insertedAt`, `status`
`business_configs` (Mongo) ✅	Wallet + cert keys.	`wallet_balance`, `cert_key`, `cert_key_ios`, `gst_deduction`, `sms_cost`
`wallet_config` (Mongo) ✅	Pricing.	`sms_cost` (default 0.145), `sms_gst`

Common queries¶

// SMS cron pickup (verified)
db["sms_queue_"+YYYYMMDD].find({ processed:false, execution_time:{ $lte: now } }).limit(8000)

// Push grouping (verified)
db["push_noti_queue_"+YYYYMMDD].aggregate([
  { $match:{ processed:false, execution_time:{ $lte: now } } },
  { $group:{ _id:{ businessId:"$businessId", os:"$os", title:"$title",
              message:"$message", image:"$image", campaign_id:"$campaign_id",
              fcm_token:"$fcm_token" }, ids:{ $push:"$_id" } } }
])

// Delivery lookup
db.sms_summary.find({ campaign_id: cid, status: 2 })   // failures

7. APIs¶

This module is mostly internal (crons + helper functions). The HTTP surface is thin; the campaign/journey builders are the callers. Representative endpoints (✅ mounted in routes/crm.js, base /crm_api):

Method	Path	Fn	Auth	Notes
POST	`/crm_api/send/track/push`	`campaignController.sendTestPush`	none ⚠️	Test push send
POST	`/crm_api/track/push/clicks`	`campaignController.trackPushCampaignClicks`	verify	Record push click → `track_push_clicks`
POST	`/crm_api/track/push/campaign/orders`	`campaignController.trackPushCampaignOrders`	none ⚠️	Push→order attribution
POST	`/crm_api/get/sms/summary`	`campaignController.getSmsSummary`	authMiddleware	SMS delivery log (page 20)
POST	`/crm_api/send/test/email`	`campaignController.sendTestEmail`	none	(email test — adjacent)
POST	`/crm_api/upload_certificate` (verify path)	`campaignController.certificateUploader`	verify	Upload FCM cert

Example: click-tracking hit (representative — ⚠️ verify body)¶

POST /crm_api/track/push/clicks
Content-Type: application/json

{ "campaign_id": "665f...", "mobile_number": "98XXXXXXXX" }

The cron later increments addo_campaigns.total_click_count for matched campaign_id.

Failure cases: invalid campaign_id → no match, row still marked processed:true by the cron (no error); missing cert → push row marked errored; DLT template mismatch → sms_summary.status:2.

8. Code Walkthrough¶

commonFunctions/sendSms.js ✅ (💻 — the money path): 1. sub_total = wallet_config.sms_cost (default 0.145) × units. 2. GST: tax = wallet_config.sms_gst × sub_total / 100, applied unless business_config.gst_deduction == 0. 3. total = sub_total + tax; debit business_configs.wallet_balance via $inc:{wallet_balance:-total} (parent vs child by is_parent). 4. Insert wallet_transactions { service_id:1, service_name:"sms", transaction_type:"debit", amount:total, sub_total, gst, total, units:1, service_base_cost:sms_cost, description:"amount deducted for sms message", parent_business_id, child_business_id, inserted_date }. 5. Insert the SMS into MySQL outbox_shubham (campaignId, routeId, userId, senderId, mobileNo, smsText, smsTextCredits, status). This is the actual dispatch — Gupshup drains the outbox. DLT template_id/entity_id are NOT in this function — only sender_id; DLT template mapping lives upstream in campaign config / queue rows ⚠️.

commonFunctions/sendAutoCampaignSms.js ✅ (the queue consumer, no debit): batch-processes rows from sms_queue_YYYYMMDD, bulk-inserts into outbox_shubham via a pooled prepared statement, increments addo_campaigns.sent_count + sets completed_at, and flips sms_cron_status.status:true with end_time in a finally (so the cron unlocks even on error). Invoked by smsQueueCron as sendSmsCampaign.

crons/smsQueueCron.js ✅ (💻): "*/3 * * * *" IST. Query {processed:false, execution_time:{$lte:now}}, .limit(8000). Stuck detection: if the prior run's sms_cron_status.status:false and it started >20 min ago, mark "crashed" and email prabhpreet@uengage.in, hani@uengage.in via POST https://www.uengage.in/addoapi/sendCRMMail.

commonFunctions/sendFcmNotification.js ✅ (💻): HTTP POST https://fcm.googleapis.com/fcm/send with Authorization: key=<serverKey>. Android ("A") → priority:"high" + data payload (tg, url, title, message, image, campaign_id). iOS ("I") → notification + data payload (collapse_key, sound, badge). Loads cert_key (Android) / cert_key_ios (iOS) from business_configs; parent_business_id=="26837" forces cert_key_ios for iOS. Every batch is logged to fcmjunk.

commonFunctions/sendPushNotification.js ✅: sendPushForBusiness(parentId, os) returns the correct cert object (cert_key/cert_key_ios), normalizing \\n→newline in private_key for the Admin SDK.

crons/pushQueueCron.js ✅ (💻): "*/5 * * * *". Query {processed:false, execution_time:{$lte:now}} .limit(1000); $group by (businessId, parentBusinessId, os, title, message, image, campaign_id, fcm_token) collecting ids; hands groups to processNotiQueue. Same 20-min stuck-detect on push_cron_status.

commonFunctions/processNotiQueue.js ✅ (💻): uses firebase-admin. Builds per-OS message — Android android.priority:"high" + apns.payload.aps.contentAvailable:true, priority:10; iOS notification{title,body,image}. Initializes a fresh Firebase app per batch (appName = app-${Date.now()}) and deletes it after sending (⚠️ per-message init is a known memory-leak risk — map-04 §5.9). On success sets processed:true; on error stores error:err.stack, processed:true; on missing cert sets error:"Cert key not available".

crons/trackPushCampaignClicksCron.js ✅ (💻): "*/10 * * * *", batch 5000 with last_id scrolling; matches track_push_clicks{processed:false} to addo_campaigns by campaign_id, $inc total_click_count:1, then marks clicks processed:true. No cron_status guard for this one ⚠️.

Call hierarchy:

builders → smsCampaignQueue / pushCampaignQueue → *_queue_{tomorrow}
smsQueueCron (*/3)  → sendAutoCampaignSms → outbox_shubham + sms_summary + addo_campaigns
sendSms (cost)      → wallet_transaction + business_configs.wallet_balance
pushQueueCron (*/5) → processNotiQueue → firebase-admin + fcmjunk + campaign_mis
sendFcmNotification → fcm.googleapis.com/fcm/send (+fcmjunk)
sendPushNotification→ cert_key/cert_key_ios
trackPushCampaignClicksCron (*/10) → addo_campaigns.total_click_count

9. Business Rules¶

Wallet debit per SMS ✅ — sub_total = sms_cost(0.145)×units, GST unless gst_deduction==0; wallet_transaction service_id:1. Push cost tracked separately (push_noti_cost).
DLT compliance 📄✅ — Indian SMS requires DLT header + template mapping (📄 go-live step 3). sendSms carries sender_id; template_id/entity_id are configured upstream — ⚠️ verify where DLT template id is attached to the queue row.
App vs Web push ✅ — os "A"/"I" (type 2) vs "w" (type 5); counted separately in campaign_mis. Web push uses separate infra (not FCM helpers).
iOS cert override ✅ — parent_business_id "26837" → cert_key_ios.
Batch sizes ✅ — SMS 8000/tick, Push 1000/tick (then grouped), click-track 5000/tick.
Opt-in / DND 📄 — SMS respects dnd_flag=0, push requires FCM token presence (crons/segments_count.js).
Idempotency gap ⚠️ — sendSms debits wallet then inserts to outbox; a crash between them can debit without dispatch (no distributed txn — map-04 §5.7).
Impacted bugs 📄 — E1.3 (WhatsApp volume) and E1.4 (CRM stats) depend on accurate counts here.

10. Performance¶

Polling cadence ✅ — SMS */3 (8000/batch), Push */5 (1000/batch), click-track */10 (5000/batch). WhatsApp (separate module) polls */1s/*/3s — the hottest loop.
Bottlenecks: (1) processNotiQueue creates+deletes a Firebase app per batch → memory pressure and init latency (map-04 §5.9 — recommend singleton per parent_business_id); (2) SMS via a single MySQL outbox_shubham table can contend under bulk; (3) date-partitioned queues require knowing today/tomorrow date; (4) no rate limiting toward FCM/Gupshup.
Grouping win ✅ — push aggregation collapses identical (title,message,os) rows to minimize send calls.

11. Logging¶

SMS: sms_summary (per message), wallet_transaction (money), sms_cron_status (run health). Crashed-cron emails to prabhpreet@uengage.in, hani@uengage.in.
Push: fcmjunk (batch tokens, Firebase output, errors), push_cron_status.
Clicks: track_push_clicks → addo_campaigns.total_click_count.
Correlate by campaign_id across sms_summary/fcmjunk.

12. Monitoring¶

Queue depth: db["sms_queue_"+YYYYMMDD].countDocuments({processed:false}); db["push_noti_queue_"+YYYYMMDD].countDocuments({processed:false}).
Cron health: latest sms_cron_status / push_cron_status {status, start_time, end_time}; status:false older than 20 min = stuck.
Delivery failures: db.sms_summary.countDocuments({status:2}) trend.
Push errors: db.fcmjunk.find({error:{$exists:true}}).sort({_id:-1}).
Click ingestion lag: unprocessed track_push_clicks {processed:false} count.

13. Troubleshooting¶

Issue	Root cause	Resolution	Command
SMS queued, never sent	`smsQueueCron` stuck / `execution_time` future	Check `sms_cron_status`; inspect queue	`db.sms_cron_status.find().sort({_id:-1}).limit(1)`
Wallet debited, no SMS	crash between debit and outbox insert	Reconcile `wallet_transaction` vs `outbox_shubham`	`db.wallet_transaction.find({campaign_id:id})` + `SELECT * FROM outbox_shubham WHERE campaignId=?`
Push not delivered	missing/invalid cert	Check `fcmjunk.error`; verify `cert_key`	`db.fcmjunk.find({campaign_id:ObjectId(id)}).sort({_id:-1})`
iOS push fails, Android ok	wrong cert for iOS	Confirm `cert_key_ios` (or 26837 rule)	`db.business_configs.find({...},{cert_key:1,cert_key_ios:1})`
Web push counts zero	web uses separate infra	Confirm `os:"w"` path, not FCM helpers	inspect campaignController web-push branch
Click count not rising	click-track cron / no `cron_status` guard	Check unprocessed clicks; verify cron running	`db.track_push_clicks.countDocuments({processed:false})`
SMS `status:2`	DLT/provider rejection	Verify DLT template + sender mapping	`db.sms_summary.find({campaign_id:id,status:2})`

14. FAQs¶

How is SMS actually sent? Not a direct API call — rows are inserted into MySQL outbox_shubham; Gupshup drains it.
Where does the wallet get debited? In sendSms.js (business_configs.wallet_balance + wallet_transaction), not in the queue consumer.
App vs web push? os "A"/"I" (FCM, type 2) vs "w" (separate web-push infra, type 5).
Why per-batch Firebase init? Current design (app-${Date.now()}) — a known leak risk; singleton recommended.
Where are DLT template ids? Upstream in campaign/queue config, not in sendSms (⚠️ verify).

15. Cheat Sheet¶

SMS: sms_queue_YYYYMMDD(processed:false) → smsQueueCron "*/3" batch 8000 → sendAutoCampaignSms → outbox_shubham(Gupshup)
  cost: sms_cost(0.145)*units + GST(unless gst_deduction==0) → wallet_transaction service_id:1 + business_configs.wallet_balance
Push: push_noti_queue_YYYYMMDD → pushQueueCron "*/5" batch 1000 (group by os/title/msg) → processNotiQueue (firebase-admin)
  send: fcm.googleapis.com/fcm/send (key=serverKey) · A=priority:high · I=notification · fcmjunk log
  cert: business_configs.cert_key / cert_key_ios  (26837 → ios)
os: A=Android(type2) I=iOS(type2) w=Web(type5)
Clicks: track_push_clicks → trackPushCampaignClicksCron "*/10" batch 5000 → addo_campaigns.total_click_count
Queue producers: smsCampaignQueue / pushCampaignQueue → *_queue_{TOMORROW}
Stuck-cron: *_cron_status status:false >20min → email prabhpreet,hani
Audit: sms_summary(mobile_number idx) · fcmjunk · campaign_mis(android/ios/web counts)

Campaigns — produces SMS/Push queue rows; owns MIS
Automated-Journeys — journey sends via same channels
WhatsApp — sibling channel (Gupshup/Meta, faster polling)
Queues · Database · Third-Party (Firebase/Gupshup) · Roadmap E1.3/E1.4

Knowledge Tests¶

Level 1 — MCQs¶

How is SMS physically dispatched? A) Twilio API B) MySQL outbox_shubham C) Firebase D) Gupshup REST call → B (Gupshup drains the outbox).
SMS cron cadence & batch? A) */5, 1000 B) */1s C) */3, 8000 D) */10, 5000 → C.
os:"w" means… A) Android B) iOS C) Web push D) WhatsApp → C.
Where is the wallet debited? A) smsQueueCron B) sendAutoCampaignSms C) sendSms.js D) processNotiQueue → C.
What does pushQueueCron group rows by? A) mobile only B) (businessId, os, title, message, image, campaign_id, fcm_token) C) campaign only D) nothing → B.

Level 2 — Scenarios¶

A client's wallet dropped ₹1,000 but customers got no SMS and sms_summary is empty. Which two stores do you reconcile and what failure mode is this? (wallet_transaction vs outbox_shubham; debit-without-dispatch idempotency gap.)
Android pushes work, iOS silent, for one big brand. What's the first config to check? (cert_key_ios, plus the parent_business_id=="26837" override.)

Level 3 — Code Reading¶

Open commonFunctions/sendSms.js. (a) Write the exact GST condition. (b) List the wallet_transactions fields inserted. (c) Which MySQL table and columns receive the SMS?

Level 4 — Architecture¶

processNotiQueue creates and deletes a Firebase Admin app per batch. Redesign for a credential pool/singleton keyed by parent_business_id. Address multi-tenant cert isolation, concurrent sends, and cleanup on cert rotation.

Level 5 — Production Debugging¶

Push delivery dropped to ~0 last night; fcmjunk shows rising error entries but push_cron_status shows recent successful runs. Diagnose: (1) is the error a cert issue or FCM auth (key=)? (2) did a cert rotate? (3) is the queue draining (processed:true) despite errors (note: errors still set processed:true)? (4) how would you replay failed sends? Give commands.

Practical Assignments¶

Reconciliation script (design): given a campaign_id, compare counts across sms_queue_* (processed), outbox_shubham, sms_summary, and wallet_transaction; flag any debit without a matching outbox row.
Trace a push: enqueue a test push (type 2), watch push_noti_queue_* → pushQueueCron grouping → fcmjunk, and confirm campaign_mis android/ios counters increment.
DLT audit: locate where the DLT template_id/entity_id is attached to an SMS queue row (or confirm it is missing) and document the compliance path end-to-end.