Skip to content

Common Queries & Optimization Notes

Copy-paste query patterns an on-call engineer actually needs, grounded in the real collection/table/column names from the MongoDB and MySQL catalogs. Mongo examples are mongosh against the uengage db; SQL runs against the addo_* schema.

Validation legend: ✅ verified names · ⚠️ tech debt/risk (unindexed → will scan)

⚠️ Read-preference gotcha. App reads use SECONDARY_PREFERRED, so a freshly-written doc may not appear on an immediate read. When debugging "missing" data, query the primary: db.getMongo().setReadPref('primary') in mongosh.

⚠️ ID type gotcha. Most collections store parent_business_id/business_id as String, but dashboard_mis, nps_config.parentBusinessId, and Parent_Id.parent_business_id store them as Number. Match the type or the query silently returns nothing.

1. Find a stuck order in crm_queue

The ingestion seam. Undrained items have status: 0 (see MongoDB Collections).

// mongosh — pending items for a source, oldest first
db.crm_queue.find({ status: 0, source: 6 })
  .sort({ insertedAt: 1 })
  .limit(20);

// count the backlog by source
db.crm_queue.aggregate([
  { $match: { status: 0 } },
  { $group: { _id: "$source", pending: { $sum: 1 } } },
  { $sort: { pending: -1 } }
]);

// inspect one payload (data is Mixed — shape varies by source)
db.crm_queue.findOne({ status: 0, source: 6 }, { data: 1, insertedAt: 1 });

// find a specific order that landed in the buffer but never processed
db.order_dump.findOne({ order_id: "ORD123456" });   // order_id IS indexed ✅

⚠️ crm_queue has no index, so filtering {status, source} is a collection scan — slow once the backlog grows. Watch for duplicate source:6 rows (a fixed bug — see commit 8934dcc8 and Technical Debt).

2. Customer wallet balance & ledger

Customer balance lives in MySQL (wallet + wallet_history), keyed off contactMappingId. Start from the mobile number.

-- resolve mobile → contactMappingId(s) for a business
SELECT m.id AS contactMappingId, m.businessId
FROM addo_contacts c
JOIN addo_contacts_mapping m ON m.contactId = c.contactId
WHERE c.mobileNo = '9876543210';

-- current balance for that customer-business pair
SELECT w.contactMappingId, w.mobileNo, w.available, w.validTillDate
FROM wallet w
WHERE w.contactMappingId = 12345;

-- full ledger (audit trail of credits/debits)
SELECT type, amount, date
FROM wallet_history
WHERE contactMappingId = 12345
ORDER BY date DESC;

// business-level campaign wallet ledger (Mongo, different from customer wallet)
db.wallet_transaction.find({
  child_business_id: "4567",
  transaction_type: "debit"
}).sort({ inserted_date: -1 }).limit(10);

Reconcile the customer ledger against the balance snapshot with care — points/balance exist across MySQL and Mongo and are updated non-atomically ⚠️. See MySQL Tables → Wallet & loyalty.

3. Campaign MIS lookup

Cumulative campaign stats are pre-aggregated in Mongo campaign_mis; per-hour raw metrics are in MySQL mis_reports.

// mongosh — a business's cumulative campaign totals (business_id is String)
db.campaign_mis.findOne({ business_id: "4567", is_parent: false });

// parent-level rollup across a date (created_at is a String date)
db.campaign_mis.find({
  parent_business_id: "1001",
  created_at: "2026-07-03"
});

-- MySQL mis_reports: campaign sends for an owner in a time window
SELECT year, month, date, hour, count
FROM mis_reports
WHERE userId = 555 AND year = 2026 AND month = 7 AND date = 3
ORDER BY hour;

// daily business KPIs from dashboard_mis — NOTE: business_id is a NUMBER here ⚠️
db.dashboard_mis.findOne({ business_id: 4567, created_at: "2026-07-03" });

⚠️ In dashboard_mis, business_id/parent_business_id are Number — do not quote them.

4. RFM / segment counts

Cohort aggregates are pre-computed in Mongo business_aggreator (note the misspelling). Live audience extraction hits MySQL wallet.

// mongosh — lifecycle cohort snapshot for a business
db.business_aggreator.findOne(
  { businessId: "4567", is_parent: false },
  { active_user: 1, lost_users: 1, totalUser: 1,
    customers_with_orders_lifetime: 1, new_customers_ordered: 1 }
);

-- live campaign audience: active loyalty holders for a parent org
-- (this is the hot query from automatedCampaignQueueNew.js)
SELECT id, mobileNo
FROM wallet
WHERE parentBusinessId = 1001
  AND validTillDate = '2026-07-03'
ORDER BY id ASC
LIMIT 1000;

-- coupon-redeemed cohort size
SELECT COUNT(DISTINCT p.contactMappingId) AS redeemers
FROM promo_contact_usage_mapping p
JOIN addo_promo_code_engine e ON e.id = p.promoCodeId
WHERE e.parent_business_id = 1001;

5. Tenant-scoped queries (multi-tenancy)

Every query should be scoped by the tenant keys. Never query a collection/table without a parent_business_id / businessId filter in production.

// mongosh — always scope, and match the stored ID type
db.rating_review.find({ parent_business_id: "1001", business_id: "4567" });   // String
db.nps_config.findOne({ parentBusinessId: 1001 });                            // Number ⚠️

-- MySQL — scope by businessId (= addo_business.id = child_business_id)
SELECT COUNT(*) FROM addo_orders
WHERE businessId = 4567 AND DATE(orderDate) = '2026-07-03'
GROUP BY source;

-- parent-level: all outlets under a parent
SELECT id, name FROM addo_business WHERE parentId = 1001;

// cross-store join done in app code: get a customer's ratings + wallet
// 1) Mongo for feedback
db.rating_review.find({ mobile_number: "9876543210", parent_business_id: "1001" });
// 2) MySQL for balance (join on mobile → contactMappingId, see §2)

Optimization notes

The data layer works but carries real, quantifiable debt. Each item below is ⚠️ and cross-linked to Technical Debt.

⚠️ Missing indexes on tenant/time keys

Almost every Mongo collection defines no indexes — only order_dump.order_id and sms_summary.mobile_number exist (verified in models). High-cardinality filters therefore do full collection scans.

Collection / Table Filter that scans Suggested index
crm_queue {status, source} (dequeue) { status: 1, source: 1, insertedAt: 1 }
campaign_mis {parent_business_id, created_at} { parent_business_id: 1, created_at: 1 }
dashboard_mis {business_id, created_at} { business_id: 1, created_at: 1 }
rating_review {parent_business_id, business_id} { parent_business_id: 1, business_id: 1 }
customer_checkin {mobile_number, checkin_time} { mobile_number: 1, checkin_time: 1 }
MySQL wallet (parentBusinessId, validTillDate) (parentBusinessId, validTillDate, id)
MySQL addo_orders (businessId, orderDate) (businessId, orderDate)
MySQL addo_contacts_mapping (businessId, status) (businessId, status) 
// example remediation (run against primary, off-peak — builds can lock)
db.campaign_mis.createIndex({ parent_business_id: 1, created_at: 1 });
db.crm_queue.createIndex({ status: 1, source: 1, insertedAt: 1 });

⚠️ No TTL on dump/queue collections

crm_queue, order_dump, customer_injestion_dump, fcm_injestion_dump only carry a processed/status flag — nothing expires drained docs, so they grow unbounded (bloats scans, backups, RAM working set).

// suggested: TTL index to auto-purge processed docs after 30 days.
// Requires a real Date field (insertedAt is currently a String in crm_queue ⚠️ —
// migrate to Date first, or add a dedicated createdAt:Date).
db.order_dump.createIndex({ invoiced_at: 1 }, { expireAfterSeconds: 60*60*24*30 });

Until then, a scheduled purge cron is the interim mitigation. See Queues.

⚠️ String vs Number ID inconsistency

Business IDs are String in most collections but Number in dashboard_mis, nps_config.parentBusinessId, and Parent_Id.parent_business_id, and INT in MySQL. Consequences:

  • Cross-collection $lookup / app-level joins silently miss without explicit coercion.
  • A String filter against a Number field returns zero rows with no error.
  • Recommended fix: standardize all Mongo business IDs to String and back-migrate existing data; add a validation layer at write time.

⚠️ Soft deletes without audit

No hard deletes anywhere — records go inactive via status/processed (0/false). There is no deleted_at and no historical audit, so compliance purges are manual. Consider adding deleted_at timestamps.

Pool discipline (operational)

  • Web reads → default export (webPool, cap 15); cron/batch → require(...).jobsPool (cap 10). A cron accidentally on webPool can starve request handlers.
  • Grep logs for pool_stats before retuning WEB_CONNECTION_LIMIT / JOBS_CONNECTION_LIMIT. See connection facts.
  • For any multi-statement wallet/loyalty update, use pool.getConnection() + transaction + try/finally release() — never plain pool.query across steps.