Dashboard & Analytics¶

One-line: The presentation and business-intelligence layer of Prism — it turns the raw orders / customers / campaign_mis collections into the widgets a restaurant sees (Customer Overview, Where Orders Come From, When Do They Buy, Star Ratings, Effective Offers, Loyalty Analysis…), served either through the legacy per-request dashboard or the modern pre-aggregated /stats metrics API.

Validation legend¶

Symbol	Meaning
✅	Verified directly in code
📄	From PM Excel / business source of truth
💻	Code-only (no Excel/business spec)
♻️	Dual / duplicated implementation (migration in progress)
⚰️	Legacy / being deprecated
⚠️	Known bug / risk / caveat

1. Overview¶

Dashboard & Analytics is the "data out" / presentation plane of the Prism architecture (see High-Level Architecture §3). It does not own transactional data — it reads what ingestion, loyalty, campaigns and feedback have already written, and shapes it into KPIs and charts.

The module has two co-existing implementations ♻️ — this duality is the single most important thing to understand:

	Legacy dashboard ⚰️	Modern dashboard
Controller	`controllers/dashboardController.js` ✅	`controllers/dashboardControllerNew.js` ✅ + `controllers/metricsController.js` ✅
Read strategy	Per-request live queries over `dashboard_mis`, `rating_mis`, `checkin_mis`, `addo_campaigns`	Pre-aggregated — a nightly cron (`aggregationProcess.js`) rolls `orders`/`order_items` into ~14 ETL collections; the API only reads those
API surface	`/crm_api/*` endpoints (mixed into `routes/crm.js`)	`/stats/*` metrics endpoints (`routes/stats.js`) ✅ + `dashboardControllerNew` checklist/graph endpoints
Real-time	none	Redis cache + Socket.io ✅
Status	Being replaced 📄 (map-02)	Current target

Why two? Prism is mid-migration to Next.js (Roadmap §A). The legacy monolithic dashboardController (50+ functions) computed everything on the fly, which does not scale. The modern path moves the heavy GROUP BY work to a nightly aggregation cron so read endpoints stay fast — this is the classic materialized-view / MIS pattern.

📄 The PM Excel "Dashboard & Analytics" sheet defines the widgets and their math. This doc maps each widget to the code path and data source that (should) feed it, and flags the widgets currently affected by known bugs (E1.1, E1.4, E1.9, E1.10).

2. Business Flow¶

2.1 Example journey — a franchise owner opens their dashboard¶

Owner of a 12-outlet chain logs in; the frontend calls POST /stats/dashboard/summary with { parent_business_id, business_id, start, end }. ✅
authMiddleware validates the staff user + business, sets req.body.created_by. ✅
metricsController.getDashboardSummary fires 10 aggregations in parallel (Promise.all) against the pre-built ETL collections. ✅
Widgets render: Customer Overview, New vs Returning, Where Orders Come From, Orders Overview, When Do They Buy (hour heatmap), Star Rating Distribution, Digital Bill Insights, Feedback Insights, Loyalty Analysis, Most Selling Item, Your Effective Offers. 📄
Owner switches the Filter by Outlet to one outlet → the same endpoints are re-called scoped to a business_id.
Smart Alert Banners surface anomalies (e.g. "Orders down 30% vs last week", low wallet balance) computed from the summary + business_configs. 📄

2.2 Edge cases 📄 (from Business-Flow "Edge Cases & Gotchas")¶

Channel integration inactive → the widget must still show the channel with 0, never omit it (Dashboard 3.5). ✅ dashboardMIS seeds all channel counters with default: 0.
Cancelled / refunded orders → excluded from revenue, AOV, most-selling-item; earned points are clawed back.
Never-ordered customers → counted under Non-Transacting, not dropped.
Timezone → "When Do They Buy" / hour heatmap use IST day boundaries (+05:30) in the modern path ✅ (parseDateStrict), and the outlet's local timezone per the Excel.
Period-filter inconsistency ⚠️ → widgets do not all share one default period/outlet filter today (Roadmap A4.6, open).

3. Technical Flow¶

3.1 Modern read path (source → API → validation → logic → DB → response)¶

Frontend
  │  POST /stats/dashboard/summary { parent_business_id, business_id, start, end }
  ▼
authMiddleware ✅  (validates user+business, sets created_by)
  ▼
metricsController.getDashboardSummary ✅
  │  metricsHelper.validateRangeParams(req,res)  ← date & param validation
  ▼
Promise.all([                                    ← logic (10 parallel aggregations)
  aggregateOrdersRange, aggregateFulfillmentRange, aggregateOutletsRange,
  aggregateHourHeatmapRange, aggregateMealBreakdownRange, aggregateItemsRange,
  aggregateCampaignsRange, aggregateGeoRange, aggregateSpecialDaysRange,
  aggregateUnderperformRange ])
  ▼
MongoDB ETL collections ✅  (orders_by_day_business, fulfillment_breakdown_by_parent, …)
  ▼
compute totals: totalOrders, totalRevenue, AOV = totalRevenue/totalOrders, peakHours ✅
  + business_aggreator lookup for customer block
  ▼
JSON response { orders, fulfillment, outlets, customers, items, campaigns, geo, … }

No events / queues / notifications are emitted by the read path — it is pure read. The write path is the cron (§10).

3.2 Modern write path (aggregation cron) ✅¶

node-cron "0 0 * * *" (midnight IST)  →  aggregationController.js
  ▼
insert aggregation_process { status:false }   ← run tracking
  ▼
find business_configs { is_parent:true, business_status:1, prism_active:1 }
  ▼
aggregationProcess.processAggregationParent(parents)   ← parent-level rollups
aggregationProcess.processAggregationChild(childIds)    ← outlet-level rollups
  │  reads: orders, order_items, customers, customers_outlets, business_configs, promotions
  │  writes (replaceOne / bulkWrite): 14 ETL collections (see §6)
  ▼
update aggregation_process { status:true, completed_at }
  ▼
sendAggregationMail("Aggregation cron completed", …)   ← email notification

3.3 Legacy read path ⚰️¶

dashboardController endpoints query dashboard_mis, rating_mis, checkin_mis, feedback_configs, google_reviews and addo_campaigns per request and compute widget values inline. dashboardMIS (the daily summary collection) is populated by processLibrary/dashboardMIS.js. This path also owns operational endpoints like whatsappTrack (WhatsApp link-click tracking), businessStatus, and syncCRMToken.

4. Architecture Diagram¶

flowchart TB
    subgraph write["Write plane — nightly cron (0 0 * * * IST)"]
        AGG[aggregationController.js ✅]
        AP[processLibrary/aggregationProcess.js ✅]
        DM[processLibrary/dashboardMIS.js]
        CM[processLibrary/campaignMis.js<br/>campaignAggregation.js]
    end

    subgraph src["Source collections"]
        ORD[(orders)]
        OI[(order_items)]
        CUST[(customers / customers_outlets)]
        CFG[(business_configs)]
        PROMO[(promotions)]
        CMIS[(campaign_mis)]
        RMIS[(rating_mis)]
        DBILL[(digital_bill_mis)]
    end

    subgraph etl["ETL / MIS collections (materialized views)"]
        E1[(orders_by_day_business)]
        E2[(fulfillment_breakdown_by_parent)]
        E3[(hour_of_day_heatmap_by_parent)]
        E4[(top_selling_items_by_day)]
        E5[(… 10 more, see §6)]
        DMIS[(dashboard_mis)]
        BAGG[(business_aggreator)]
    end

    subgraph read["Read plane — APIs"]
        MC[metricsController ✅]
        MH[commonFunctions/metricsHelper ✅]
        DCN[dashboardControllerNew ✅<br/>checklist + graphs + Redis + Socket.io]
        DC[dashboardController ⚰️ legacy]
    end

    ORD & OI & CUST & CFG & PROMO --> AP --> E1 & E2 & E3 & E4 & E5 & BAGG
    ORD --> DM --> DMIS
    CMIS --> CM
    MC --> MH --> E1 & E2 & E3 & E4 & E5 & BAGG
    DC --> DMIS & RMIS & DBILL
    Frontend([Frontend]) -->|/stats/*| MC
    Frontend -->|checklist/graphs| DCN
    Frontend -->|/crm_api/* legacy| DC
    DCN -.Redis cache + Socket.io.-> Frontend

5. Folder Structure (real files)¶

controllers/
  dashboardControllerNew.js   ✅ modern: checklist, KPIs, graph/canvas gen, Redis, Socket.io (~2560 lines)
  dashboardController.js       ⚰️ legacy monolith: whatsappTrack, businessStatus, syncCRMToken, past-campaign views (~50 fns)
  metricsController.js         ✅ thin controller for /stats/* — delegates to metricsHelper
  aggregationController.js     ✅ node-cron trigger for the nightly aggregation
  order360Controller.js        360° order view
  invoiceInsightsController.js  digital-bill click/feedback/download tracking

commonFunctions/
  metricsHelper.js             ✅ all /stats aggregation + validation logic (ETL COLLECTIONS map, ~486 lines)

processLibrary/
  aggregationProcess.js        ✅ ETL engine → 14 materialized collections (~1378 lines)
  dashboardMIS.js              daily dashboard_mis rollup
  campaignAggregation.js       campaign send rollups → campaign_aggregation
  campaignMis.js               campaign ROI/conversion → campaign_mis

routes/
  stats.js                     ✅ /stats/* (12 endpoints, authMiddleware)
  crmv2.js                     ✅ /crm_api/v2/dashboarData (businessController)  ⚠️ no auth middleware
  crm.js                        legacy dashboard endpoints

models/
  dashboardMIS.js              ✅ collection: dashboard_mis
  dashboard_modules.js         ✅ collection: dashboard_module (widget registry)
  campaign_mis.js              ✅ collection: campaign_mis
  buisness_aggregator.js       ✅ collection: business_aggreator (customer cohort counts)

6. Database¶

6.1 ETL / materialized-view collections ✅ (written by `aggregationProcess.js`, `ETL_COLLS` map)¶

Collection	Feeds widget	Purpose
`orders_by_day_business`	Orders Overview	Daily orders + revenue per business
`fulfillment_breakdown_by_parent`	Where Orders Come From / order-type split	dinein / delivery / pickup / incar / other
`outlet_metrics_by_day`	Filter by Outlet	Per-outlet daily metrics
`outlet_toplist_by_day`	Outlet leaderboard	Top outlets by revenue/orders
`state_city_distribution_by_day`	Geo widget	State/city order distribution
`hour_of_day_heatmap_by_parent`	When Do They Buy	Orders by hour bucket
`meal_time_breakfast_by_parent` → `meal_time_breakdown_by_parent`	Meal-time split	Breakfast/lunch/snacks/dinner
`campaign_metrics_by_day`	Campaign block	Campaign sends/metrics by day
`top_selling_items_by_day`	Most Selling Item	Top items by qty/revenue
`item_revenue_contribution_by_day`	Item revenue mix	% revenue per item
`product_pairing_by_day`	Basket analysis	Frequently-bought-together
`category_performance_by_day`	Category widget	Category revenue
`underperforming_items_by_day`	Underperforming items	Bottom-decile items (ETL_UNDERPERF_PCT = 0.10)
`daily_summary_by_parent`	Summary / Smart Alerts	One consolidated daily doc per parent

✅ The read side (metricsHelper.COLLECTIONS) references the same collection names — this is the contract between cron and API. If the cron does not run, the /stats widgets go stale/empty (see §13).

6.2 Legacy / summary collections¶

Collection	Model	Purpose	Key fields
`dashboard_mis`	`models/dashboardMIS.js` ✅	Daily KPI rollup per business-day	`created_at`, `business_id`, `parent_business_id`, `total_order_count`, `total_order_amount`, channel counters (`total_uen_`, `total_swig_`, `total_zom_`, `total_pos_`, prepaid/COD), `new_customer_count`, salon/service breakdowns, `valid_numbers`/`invalid_numbers`
`business_aggreator`	`buisness_aggregator.js` ✅	Customer cohort counts (used by Customer Overview)	`totalUser`, `active_user`, `lost_users`, `customers_with_orders_lifetime`, `customers_with_no_orders_lifetime`, `new_customers_ordered`, `new_customers_not_ordered`
`campaign_mis`	`campaign_mis.js` ✅	Campaign delivery/ROI MIS	`total_campaigns_sent`, `total_sms_sent`, `total_sms_price`, push/android/ios counters
`dashboard_module`	`dashboard_modules.js` ✅	Widget registry	`display_name`, `value`
`aggregation_process`	💻	Cron run log	`started_at`, `completed_at`, `status`
`rating_mis` / `mis_rating`	`rating_mis.js`	Star Rating Distribution	`ratings_done`, `avg_rating`
`digital_bill_mis`	—	Digital Bill Insights	click/view/download counters

6.3 Relationships, keys, indexes¶

Tenant keys: every collection is scoped by parent_business_id (+ business_id/child_business_id). ⚠️ Type inconsistency — some docs store these as Number, others as String. metricsHelper.parentQuery() handles this by trying Number(x) first, and the summary does findOne({businessId:String(x)}) || findOne({businessId:Number(x)}). ✅
Customer key: mobile_number (10-digit) links orders↔customers↔ratings.
Indexes: ⚠️ Most ETL/MIS collections have no explicit indexes (map-03 §5.1). ETL reads filter on a date field range + parentBusinessId; add compound indexes on (parentBusinessId, date) if aggregation queries slow down.

6.4 Common queries¶

// Modern: fetch a day-range ETL slice (metricsHelper.fetchRange)
db.collection("orders_by_day_business").find({
  date: { $gte: qStart, $lte: qEnd },
  parentBusinessId: Number(parentBusinessId)
}).toArray();

// Cron write (aggregationProcess) — idempotent upsert of a materialized row
db.collection("hour_of_day_heatmap_by_parent").replaceOne(
  { parentBusinessId, date }, doc, { upsert: true });

// Customer cohort block for Customer Overview
db.collection("business_aggreator").findOne({ businessId: String(parentBusinessId) });

7. APIs¶

All /stats/* endpoints are POST, guarded by authMiddleware ✅, and take { parent_business_id, business_id, start, end } (or date for single-day). Header: token: <staff token> + body userId, business_id.

Route	Method	Auth	Body	Feeds	Verify
`/stats/dashboard/summary`	POST	authMiddleware	`parent_business_id, business_id, start, end, date`	All summary widgets	✅ representative
`/stats/metrics/orders`	POST	authMiddleware	`…start,end`	Orders Overview	✅
`/stats/metrics/fulfillment`	POST	authMiddleware	`…start,end`	Where Orders Come From	✅
`/stats/metrics/outlets`	POST	authMiddleware	`…start,end`	Filter by Outlet	✅
`/stats/metrics/hour-heatmap`	POST	authMiddleware	`…date`	When Do They Buy	✅
`/stats/metrics/meal-breakdown`	POST	authMiddleware	`…start,end`	Meal-time split	✅
`/stats/metrics/items`	POST	authMiddleware	`…start,end,top`	Item table	✅
`/stats/metrics/campaigns`	POST	authMiddleware	`…start,end`	Campaign block	✅
`/stats/metrics/geo`	POST	authMiddleware	`…start,end`	Geo distribution	✅
`/stats/metrics/top-items`	POST	authMiddleware	`…start,end,top`	Most Selling Item	✅
`/stats/metrics/special-days`	POST	authMiddleware	`…start,end`	Special-days perf	✅
`/stats/metrics/underperforming`	POST	authMiddleware	`…start,end`	Underperforming items	✅
`/crm_api/v2/dashboarData`	POST	none ⚠️	(business params)	Legacy v2 dashboard	✅ (typo route, `businessController.dashboardData`, no auth) — see Admin-Business

7.1 Representative payload — `POST /stats/dashboard/summary`¶

Request

POST /stats/dashboard/summary
Content-Type: application/json
token: <staff-api-token>

{ "userId": "9812345678", "business_id": 501, "parent_business_id": 42,
  "start": "2026-06-01", "end": "2026-06-30" }

Response (shape ✅ — fields verified in getDashboardSummary)

{
  "orders":   { "totalOrders": 1240, "totalRevenue": 486000.5, "AOV": 391.94, "series": [...] },
  "fulfillment": { "dinein": 0, "delivery": 0, "pickup": 0, "incar": 0, "other": 0 },
  "customers": { "total": 8400, "new": null, "returning": null, "active": null, "lost": null, "recovered": null },
  "hour":     { "peakHours": [13, 20, 21], "buckets": [...] },
  "items":    [...], "campaigns": {...}, "geo": [...], "specialDays": [...], "underperforming": [...]
}

7.2 Validation & failures¶

metricsHelper.validateRangeParams rejects missing/malformed dates (YYYY-MM-DD) and missing parent_business_id. ✅
Dates parsed as IST (T00:00:00+05:30); a range end is extended to end + 24h - 1ms. ✅
authMiddleware → 403 on bad token, 500 fail-closed. ✅
Empty ETL collection → widget returns zeros/empty arrays, not an error (so a missing cron run looks like "no data", not a crash) ⚠️.

8. Code Walkthrough¶

8.1 Read path (`/stats`)¶

routes/stats.js
  → metricsController.getDashboardSummary(req,res)          ✅
      → metricsHelper.validateRangeParams(req,res)
      → metricsHelper.connectMongo()
      → Promise.all([
          aggregateOrdersRange, aggregateFulfillmentRange, aggregateOutletsRange,
          aggregateHourHeatmapRange, aggregateMealBreakdownRange, aggregateItemsRange,
          aggregateCampaignsRange, aggregateGeoRange, aggregateSpecialDaysRange,
          aggregateUnderperformRange ])                     ← each calls fetchRange(COLLECTIONS.x, …)
      → totalOrders / totalRevenue / AOV / peakHours
      → business_aggreator lookup → customersBlock

Each single-metric endpoint (getOrders, getHourHeatmap, getTopItems, …) delegates to the matching metricsHelper.aggregate*Range function and returns just that slice. ✅

metricsHelper key functions (all ✅ exported): parseDateStrict, dateRangeFromParams, parentQuery, fetchRange, enrichOutletsWithConfigs (joins outlet ids → names from business_configs), and the aggregate*Range family.

8.2 Write path (cron)¶

aggregationController.js  cron "0 0 * * *"
  → mark aggregation_process { status:false }
  → business_configs.find({ is_parent:true, business_status:1, prism_active:1 })
  → aggregationProcess.processAggregationParent(parents)   ✅
  → aggregationProcess.processAggregationChild(childIds)   ✅
  → mark aggregation_process { status:true } + email

aggregationProcess reads orders, order_items, customers, customers_outlets, business_configs, promotions and writes 14 collections via replaceOne(upsert) / bulkWrite (idempotent — re-runnable for the same day). ✅ IST date math throughout (ETL_DATE_FORMAT = "%Y-%m-%d %H:%M:%S").

8.3 Modern dashboard controller¶

dashboardControllerNew.js ✅ owns the business checklist / onboarding progress (checkListAPI), KPI cards, graph/canvas image generation (AWS), and uses Redis for caching hot dashboard payloads and Socket.io for pushing real-time updates to the open dashboard. (Full per-function detail: verify in code — file is ~2560 lines.)

Dependencies: utilities/mongodb, utilities/redis, socket.js, logger, AWS SDK (image gen), axios.

Widget	Calculation 📄	Data source
Smart Alert Banners	Anomaly thresholds vs prior period + low-wallet	`daily_summary_by_parent` + `business_configs`
Filter by Outlet	Scope all widgets to one `business_id`	`outlet_metrics_by_day`
Customer Overview	`Total Customers = Transacting + Non-Transacting`	`business_aggreator`
New vs Returning	New = first order in period; Returning = ordered before AND in period	`orders_by_day_business` / `business_aggreator`
Where Orders Come From	Channel revenue sums to 100%; each channel share	`fulfillment_breakdown_by_parent`, `dashboard_mis` channel counters
Orders Overview	`Total Orders = Delivery + Pick-Up + Dine-In`	`orders_by_day_business`, `fulfillment_breakdown_by_parent`
When Do They Buy	Orders bucketed by hour (outlet TZ / IST)	`hour_of_day_heatmap_by_parent`
Star Rating Distribution	Count per star 1–5, `avg_rating`	`rating_mis`
Upcoming Birthdays	Customers with birthday in next N days	`customers` (KYC birthday)
Digital Bill Insights	Sent / viewed / downloaded / feedback	`digital_bill_mis`
Feedback Insights	Rating + NPS aggregates	`rating_mis`, feedback collections
Loyalty Analysis	`Total Points = Used + Available + Expired` (never negative)	wallet/loyalty collections
Most Selling Item	Top items by qty/revenue (excl. cancelled)	`top_selling_items_by_day`
Your Effective Offers	Offer ROI; AOV = Revenue / Redemptions	`promotions` / `campaign_metrics_by_day`

These invariants must hold across every widget; a violation is a P0 data bug:

Total Customers = Transacting + Non-Transacting
Total Orders = Delivery + Pick-Up + Dine-In
Total Loyalty Points = Used + Available + Expired (never negative)
Channel revenue shares sum to 100%
AOV = Revenue / Orders (and for offers, Revenue / Redemptions)
Unique Customers ≤ Redemptions always

⚠️ In the modern summary, AOV is computed as totalRevenue / totalOrders with a divide-by-zero guard (totalOrders ? … : 0). ✅ The Effective Offers widget must use Revenue / Redemptions — see bug E1.9 below.

9.3 Data Validation Protocol (P0, MANDATORY per migrated screen) 📄¶

Every Next.js-migrated widget must pass Roadmap A4.1–A4.6: logic validation (matches Product Logic Spec), composition (breakdowns sum), sync time (orders <5min, profiles <5min, points real-time, campaign stats <15min, RFM nightly), cross-widget integrity, regression on merges, period-filter consistency. This is the acceptance bar for any dashboard PR.

9.4 Known bugs correlated to this module ⚠️ (Roadmap §E)¶

Bug	Symptom	Root-cause / fix direction
E1.9 (P1)	"Your Effective Offers" shows AOV = ₹0	AOV wrongly divides by Orders (or a 0 denominator); must be Revenue / Redemptions. Fix in the effective-offers calc.
E1.10 (P1)	Loyalty 2-point discrepancy: `Total ≠ Used + Available + Expired`	Points live across MongoDB and MySQL, non-atomic (see High-Level Arch §9); reconcile the ledger. Affects Loyalty Analysis widget.
E1.1 (P0)	ROI calculation wrong	`campaignMis.js` / `campaign_metrics_by_day` attribution window logic. Affects campaign + Effective Offers widgets.
E1.4 (P0)	Various CRM statistics incorrect	`dashboard_mis` / `business_aggreator` rollups drift; validate against A4 protocol.

Full triage: ../../13-CommonIssues/Known-Bugs-From-Roadmap.md.

10. Performance¶

Materialized views: the modern path pushes all GROUP BY to the nightly aggregationProcess cron (0 0 * * * IST). Read endpoints only scan pre-shaped, day-keyed docs → O(days) instead of O(orders). ✅
Parallelism: getDashboardSummary runs 10 aggregations in Promise.all. ✅
Redis cache: dashboardControllerNew caches hot payloads (ElastiCache serverless). ✅
Idempotent writes: ETL uses replaceOne(upsert) keyed on (parentBusinessId, date) → safe to re-run for a given day.
MongoDB read preference: SECONDARY_PREFERRED, maxPool 400 (infra) — analytics reads offload to replicas.
⚠️ Missing indexes on ETL collections ((parentBusinessId, date)); large ranges may table-scan.
⚠️ Cron coupling: 91 crons run in-process with the web server — a heavy aggregation night can raise API latency.

11. Logging¶

Winston (require('./logger')) — logger.info/error; daily-rotating combined-*.log / error-*.log. ✅
aggregationController logs run start/finish and sends email alerts on start/complete/failure. ✅
Trace correlation: loggerService + traceIdMiddleware inject a trace_id per request (infra §5.2).
pool_stats JSON every 60s for MySQL pool health (relevant when legacy dashboard hits MySQL).

12. Monitoring¶

Cron health: aggregation_process collection — a run with status:false and no completed_at older than ~1 day = stuck/failed aggregation. Watch the start/complete emails.
Data freshness: compare latest date in orders_by_day_business vs today; a gap = cron missed.
Sync-time SLOs (A4.3): orders <5min, profiles <5min, points real-time, campaign stats <15min, RFM nightly.
APM: New Relic / Elastic present but disabled (infra §5.4).

13. Troubleshooting¶

Issue	Likely cause	Fix	Command
`/stats` widgets empty/stale	Aggregation cron didn't run	Re-run aggregation; check run log	`db.aggregation_process.find().sort({started_at:-1}).limit(3)`
Widget shows 0 for a live channel	Integration inactive OR cron gap	Confirm channel counter in `dashboard_mis`; verify integration	`db.dashboard_mis.find({business_id:501}).sort({created_at:-1}).limit(1)`
AOV = ₹0 in Effective Offers	Bug E1.9 — wrong denominator	Use `Revenue/Redemptions` in offers calc	grep effective-offers calc
Loyalty totals off by 2	Bug E1.10 — Mongo/MySQL ledger drift	Reconcile points ledger	see Loyalty module
Numbers differ between two widgets	Cross-widget integrity break (A4.4) / type mismatch on `parent_business_id`	Verify all widgets read same period + same tenant type	check `parentQuery` coercion
`/stats` returns 403	Bad staff token	Re-auth	verify `token` header + `business_id`
Slow summary	Missing index / huge range	Add `(parentBusinessId,date)` index; narrow range	`db.orders_by_day_business.getIndexes()`
`/crm_api/v2/dashboarData` unauthenticated data	Route has no middleware ⚠️	Add `authMiddleware` (see Admin-Business)	`routes/crmv2.js`

14. FAQs¶

Legacy vs modern — which is source of truth? The modern /stats + aggregationProcess path is the target; the legacy dashboardController/dashboard_mis path is being deprecated. New widgets should read ETL collections. ♻️
Why does a widget show 0 instead of hiding the channel? By design (Excel 3.5) — an inactive channel must appear as 0, not vanish. ✅
Where does AOV come from? totalRevenue / totalOrders in getDashboardSummary; the offers widget must use Revenue / Redemptions (E1.9). ✅
Do the read APIs write anything? No — read-only. Only the cron writes. ✅
Why are counts sometimes matched with both Number and String ids? Tenant-id type inconsistency in Mongo; metricsHelper coerces defensively. ⚠️
What triggers aggregation? node-cron "0 0 * * *" in aggregationController, over active parents. ✅

15. Cheat Sheet¶

READ (modern):  POST /stats/dashboard/summary   { parent_business_id, business_id, start, end }
                POST /stats/metrics/{orders|fulfillment|outlets|hour-heatmap|meal-breakdown|
                                     items|campaigns|geo|top-items|special-days|underperforming}
CONTROLLER:     metricsController.* → commonFunctions/metricsHelper.aggregate*Range
WRITE (cron):   aggregationController "0 0 * * *" IST → aggregationProcess.processAggregation{Parent,Child}
ETL COLLS:      orders_by_day_business, fulfillment_breakdown_by_parent, outlet_metrics_by_day,
                outlet_toplist_by_day, state_city_distribution_by_day, hour_of_day_heatmap_by_parent,
                meal_time_breakdown_by_parent, campaign_metrics_by_day, top_selling_items_by_day,
                item_revenue_contribution_by_day, product_pairing_by_day, category_performance_by_day,
                underperforming_items_by_day, daily_summary_by_parent
LEGACY:         dashboardController (dashboard_mis, rating_mis) ⚰️
INTEGRITY:      Customers = Transacting+Non-Transacting | Orders = Delivery+PickUp+DineIn
                Points = Used+Available+Expired | Channel rev = 100% | AOV = Rev/Orders (offers: Rev/Redemptions)
BUGS:           E1.9 AOV=₹0 (offers) | E1.10 loyalty ±2 | E1.1 ROI | E1.4 CRM stats
RUN LOG:        db.aggregation_process (status:false = running/stuck)

Customer-Intelligence-RFM — feeds Customer Overview / New-vs-Returning segments.
Campaigns — campaign_mis, ROI attribution (E1.1) powering the campaign widgets.
Loyalty — Loyalty Analysis widget + point-integrity bug E1.10.
Feedback-NPS — Star Rating & Feedback Insights (rating_mis).
Admin-Business — parent-level admin dashboards + /crm_api/v2/dashboarData.
Database · Queues · Known Bugs · Roadmap

Knowledge Tests¶

Level 1 — MCQs¶

1. Which path does POST /stats/dashboard/summary use to get its numbers? A) Live GROUP BY on orders per request B) Pre-aggregated ETL collections built by a nightly cron C) Redis only D) MySQL addo_orders Answer: B. metricsHelper.aggregate*Range read materialized collections (orders_by_day_business, etc.) written by aggregationProcess.

2. What schedule runs the aggregation ETL? A) every 5 min B) 0 0 * * * (midnight IST) C) hourly D) on every API call Answer: B. aggregationController uses cron.schedule("0 0 * * *"); dates handled in IST.

3. The cross-widget rule for orders is: A) Orders = New + Returning B) Orders = Delivery + Pick-Up + Dine-In C) Orders = Prepaid + COD D) Orders = Swiggy + Zomato Answer: B. 📄 Integrity rule; matches fulfillment_breakdown_by_parent.

4. Bug E1.9 is about: A) ROI window B) 2-point loyalty gap C) AOV showing ₹0 in Effective Offers (should be Revenue/Redemptions) D) missing indexes Answer: C.

5. Why can the same tenant be looked up as both Number and String? A) A/B test B) Mongo stores parent_business_id inconsistently across collections C) Redis quirk D) Sharding Answer: B. ⚠️ Type inconsistency; metricsHelper.parentQuery + summary fallbacks coerce defensively.

Level 2 — Scenarios¶

S1. A brand reports "orders show 0 for Zomato since Tuesday, but Zomato is live." Walk through diagnosis. Expected: Check aggregation_process for a missed/stuck run since Tuesday; check latest date in orders_by_day_business and Zomato counters in dashboard_mis; confirm the Zomato integration is active in business_configs; if the cron gapped, re-run aggregation for the missing days (idempotent upsert). Remember: an inactive channel must show 0 by design, so distinguish "integration down" from "cron gap".

S2. QA says Customer Overview "Total" ≠ Transacting + Non-Transacting for one parent. What broke and what protocol applies? Expected: Cross-widget/composition integrity break (A4.2/A4.4). Likely business_aggreator cohort fields stale or a tenant-id type mismatch splitting the count across Number/String docs. Fix the rollup + apply the Data Validation Protocol before sign-off.

Level 3 — Code reading¶

Read getDashboardSummary in metricsController.js. (a) How is AOV guarded against divide-by-zero? (b) Why are there two business_aggreator.findOne calls chained with ||? (c) How are peakHours derived? Expected: (a) totalOrders ? Math.round(totalRevenue/totalOrders*100)/100 : 0. (b) tenant-id stored as String in some docs, Number in others — try both. (c) sort hourBuckets by orders desc, take top 3 .hour.

Level 4 — Architecture¶

Prism is moving dashboards from the legacy per-request model to the pre-aggregated /stats model. Explain the trade-off (freshness vs latency/scale), why aggregation_process + idempotent replaceOne matter, and one risk of the in-process cron design. Expected: Materialized views trade real-time freshness (data is as fresh as the last nightly run) for fast, scalable reads; run-tracking + idempotency make the ETL safe to retry/backfill; risk: 91 in-process crons couple cron load to API latency (a heavy aggregation night slows requests).

Level 5 — Prod debugging¶

Effective Offers shows AOV ₹0 for every brand; other widgets are fine. Diagnose and fix. Expected: This is E1.9. Other widgets compute AOV = Revenue/Orders correctly; the offers widget must use Revenue/Redemptions. Locate the effective-offers calc (offers/promotions path), confirm the denominator, add a divide-by-zero guard, and re-validate against the integrity rule Unique Customers ≤ Redemptions. Verify no cancelled-order revenue leaks in.

Practical Assignments¶

Trace a widget end-to-end. Pick "When Do They Buy". Find where hour_of_day_heatmap_by_parent is written (aggregationProcess.js) and read (aggregateHourHeatmapRange → getHourHeatmap). Document the exact fields and the IST bucketing. Deliverable: a one-page data-lineage note.
Reproduce & fix E1.9 locally. Seed a small promotions/offers dataset with known redemptions, hit the effective-offers path, confirm AOV = ₹0, then patch the denominator to Revenue/Redemptions and prove the integrity rules hold. Deliverable: before/after numbers + the diff.
Add a freshness monitor. Write a script that compares the max date in each ETL collection against today-1 and the latest aggregation_process status, and emits an alert line per stale collection. Deliverable: the script + sample output (fits the Monitor pattern in Queues).