Skip to content

Database Overview

Prism CRM runs on a dual datastore model. There is no single database — every feature touches one or both of two very different stores, and the seam between them is where most of the operational complexity (and tech debt) lives.

Store Engine Database name Holds Access shape
MongoDB MongoDB (replica set) uengage queues, ingestion dumps, campaign MIS/journeys, ratings/NPS, dashboard MIS, config, ops logs high-write buffers + aggregation reads
MySQL MySQL 8 (mysql2/promise) addo_* schema orders, order items, users/roles/auth, customers, wallet + ledger, promo engine, menus, forms transactional, joined relational reads

Validation legend: ✅ verified in code · 📄 Excel-only · 💻 code-only · ♻️ duplicate · ⚰️ dead · ⚠️ tech debt/risk

1. Why two stores?

The split is historical and deliberate, and it maps cleanly onto the data plane vs control plane split:

  • MongoDB is the "data-in" and "aggregate-out" store. Anything that arrives fast, has a flexible/unknown shape, or is a pre-computed rollup lives in Mongo. The crm_queue collection is the ingestion seam between the serverless tier and the monolith (see High-Level Architecture §6). Dump collections (order_dump, customer_injestion_dump, fcm_injestion_dump) are staging buffers. MIS collections (campaign_mis, dashboard_mis, mis_rating) are denormalized rollups optimised for dashboard reads.
  • MySQL is the transactional system of record. Customers, orders, order line items, the wallet ledger, users/roles/auth, and the promo engine need joins, foreign-key-style integrity, and read-after-write consistency. These live in the relational addo_* schema, inherited from the older "Addo" platform.

The trade-off: loyalty points and balances exist in both stores and are updated non-atomically — a known correctness risk. See Technical Debt.

2. Connection facts

MongoDB — utilities/mongodb.js ✅

const options = {
  useNewUrlParser: true,
  useUnifiedTopology: true,
  readPreference: ReadPreference.SECONDARY_PREFERRED,  // reads may hit a secondary
  maxPoolSize: 400,                                     // single large pool
};
// client.db('uengage')  ← the one and only Mongo database
  • One shared MongoClient for the whole process, initialised once via connectMongoDB(). There is no per-module pooling — everyone shares the same 400-connection pool.
  • SECONDARY_PREFERRED ⚠️ — reads may be served by a replica secondary, so freshly-written docs can be momentarily stale on read. Fine for MIS/aggregation reads; a footgun for read-after-write logic.
  • Both the native driver (getMongoDB() / getMongoClient()) and Mongoose (mongoose.connect) connect to the same URI. Models under models/*.js use Mongoose; some hot paths use the raw driver.
  • Connection string comes from DB_URL_STRING (throws at boot if unset).

MySQL — config/mysqlPool.js ✅

Two pools, created once at module load, sharing the same config/mysql.js credentials:

Pool Env var (default) Used by
webPool WEB_CONNECTION_LIMIT (15) middlewares + controllers (request-scoped, short queries)
jobsPool JOBS_CONNECTION_LIMIT (10) crons, processLibrary, commonFunctions queue workers 
module.exports = webPool;            // default export = webPool (legacy callers unchanged)
module.exports.webPool = webPool;
module.exports.jobsPool = jobsPool;
  • Why two pools? Web traffic and 91 crons run in the same PM2 process. Separate caps mean a heavy cron can exhaust jobsPool without starving authMiddleware/controllers. See Queues.
  • Use pool.query(...) directly (auto acquire/release). Only use pool.getConnection() (with try/finally release()) for transactions — never hold a connection across a long job.
  • Both pools log usage periodically: grep logs for pool_stats (via utilities/poolMonitor.js) to see real demand before retuning limits.

3. Universal linking keys

Two key families thread through both stores and make cross-store joins possible in application code (there are no real DB-level foreign keys across engines):

3.1 Customer key — the 10-digit mobile number

mobile_number (Mongo) / mobileNo (MySQL) is the universal customer identifier: a 10-digit Indian phone number, no country code. It is denormalized nearly everywhere.

mobileNo (10-digit)
 └─ MySQL addo_contacts.mobileNo (dedup master)
     └─ addo_contacts_mapping.contactId + businessId  (customer↔business pair)
         └─ wallet.contactMappingId / wallet_history.contactMappingId
 └─ Mongo sms_summary.mobile_number · rating_review.mobile_number
    order_dump.mobile_number · customer_checkin.mobile_number · nps_comm_request.mobileNo

3.2 Tenant keys — parent/child business

Every operation is scoped by the business hierarchy for multi-tenant isolation:

  • parent_business_id — the parent org (franchise brand / chain).
  • child_business_id (Mongo) = addo_business.id (MySQL) — the individual outlet.
  • addo_business.parentId = parent business ID on the MySQL side.
  • is_parent (Boolean) flags whether a Mongo doc is scoped at parent or child level.

⚠️ Type inconsistency. These IDs are String in most Mongo collections (campaign_mis, wallet_transaction, rating_review) but Number in a few (dashboard_mis, nps_config.parentBusinessId, Parent_Id.parent_business_id), and INT in MySQL. Cross-store comparisons need explicit coercion or they silently miss. See Common-Queries → Optimization notes.

4. Core entity relationships

The ~10 central entities and how they link. MySQL entities are relational; Mongo entities join by shared parent_business_id / mobile_number values (dashed conceptual links, enforced only in app code).

erDiagram
    PARENT_BUSINESS ||--o{ ADDO_BUSINESS : "parentId"
    ADDO_BUSINESS ||--o{ ADDO_CONTACTS_MAPPING : "businessId"
    ADDO_CONTACTS ||--o{ ADDO_CONTACTS_MAPPING : "contactId (mobileNo)"
    ADDO_CONTACTS_MAPPING ||--|| WALLET : "contactMappingId"
    WALLET ||--o{ WALLET_HISTORY : "contactMappingId"
    ADDO_BUSINESS ||--o{ ADDO_ORDERS : "businessId"
    ADDO_ORDERS ||--o{ ADDO_ORDER_ITEMS : "orderId"
    ADDO_ITEMS_MASTER ||--o{ ADDO_ORDER_ITEMS : "itemId"
    ADDO_PROMO_CODE_ENGINE ||--o{ PROMO_CONTACT_USAGE_MAPPING : "promoCodeId"
    ADDO_CONTACTS_MAPPING ||--o{ PROMO_CONTACT_USAGE_MAPPING : "contactMappingId"
    USERS ||--o{ ADDO_ACCOUNT_MAPPING : "userId"
    ADDO_BUSINESS ||--o{ ADDO_ACCOUNT_MAPPING : "businessId"

    ADDO_BUSINESS ||..o{ CRM_QUEUE : "child_business_id (app-level)"
    ADDO_BUSINESS ||..o{ DASHBOARD_MIS : "business_id (app-level)"
    ADDO_BUSINESS ||..o{ CAMPAIGN_MIS : "business_id (app-level)"
    ADDO_ORDERS ||..o{ ORDER_DUMP : "order_id (ingestion buffer)"
    ADDO_CONTACTS ||..o{ RATING_REVIEW : "mobile_number (app-level)"
    ADDO_CONTACTS ||..o{ SMS_SUMMARY : "mobile_number (app-level)"

Solid lines = MySQL relational links. Dashed (||..o{) = cross-store links resolved by shared key values in application code, not by any DB constraint.

5. Master index

Catalog Count What's inside
MongoDB Collections ~28 collections Queues/buffers, campaigns, feedback/NPS, config/master, ops logs
MySQL Tables ~39 addo_* + related tables Business/auth, customer, orders, menu, wallet/loyalty, campaign/promo, integration, forms, ops
Common Queries patterns Stuck-order lookup, wallet ledger, campaign MIS, RFM counts, tenant-scoped queries, indexing gaps

6. Data modeling posture (at a glance)

  • No hard deletes — records are soft-deleted via status / processed flags (0/false = inactive). Purging is manual.
  • Denormalization is intentional — mobile_number, campaign_id, business IDs are copied across collections to avoid cross-store joins on hot paths. Update triggers are not always documented → stale-data risk.
  • Dump collections have no TTL ⚠️ — crm_queue, order_dump, customer_injestion_dump, fcm_injestion_dump grow unbounded; only a processed/status flag, no auto-expiry. See Common-Queries → Optimization notes.
  • Sparse indexing ⚠️ — most Mongo collections define no indexes (only order_dump.order_id and sms_summary.mobile_number are indexed in the models). High-cardinality filters on parent_business_id / business_id / created_at do collection scans.