Loyalty Program — Points, Tiers & Redemption¶

Validation legend: ✅ verified in code · 📄 Excel/spec-only · 💻 code-only (no Excel) · ♻️ duplicate · ⚰️ dead code · ⚠️ risk/known-issue

[!IMPORTANT] The single most important fact about Loyalty: points live in TWO stores that are written NON-ATOMICALLY. The authoritative balance is in MySQL (wallet, wallet_history, wallet_rules), but allocation is driven through the MySQL queue crm_points_allocation_queue and there are MongoDB mirrors (loyalty_mis, customers.milestone_level, wallet_transactions for comms). Because point credit spans MySQL wallet + MongoDB customer metrics with no distributed transaction, balances can drift — this is the likely root cause of bug E1.10 (Total ≠ Used + Available + Expired, 2-point discrepancy). See §9 and High-Level Architecture §9.

1. Overview¶

Loyalty is Prism's free retention engine: customers earn points on orders, burn them for discounts, climb tiers (milestones), and lose points on expiry or downgrade. It is the counterpart to paid Membership, and shares the same MySQL wallet_rules config row and the Wallet ledger.

Recently shipped (Roadmap D1.1–D1.5, all ✅ in code): - D1.1 Tier downgrade on inactivity (crons/loyalty_milestone_downgrade.js). - D1.2 Point forfeiture on downgrade (unredeemed points reset). - D1.3 Per-order-type earn/burn rates (delivery/pickup/dinein/incar). - D1.4 KYC-gated welcome points (welcome_kyc flag in wallet_rules). - D1.5 Refer & Earn — see Referral.

Two customer-facing surfaces: - Loyalty microsite / WLA (customer app/web) — via appMiddleware routes. - POS integration — Posist redemption/revoke via routes/posistLoyaltyIntegration.js.

2. Business Flow¶

2.1 Example journey¶

flowchart LR
    A[Customer places order<br/>type: delivery/pickup/dinein/incar] --> B[Points enqueued<br/>crm_points_allocation_queue type=uen_order]
    B --> C[Cron every 2 min<br/>handleWalletPointsFlow]
    C --> D[Earn % by order type + milestone<br/>credit wallet + wallet_history FIFO]
    D --> E{LTO/LTV crosses<br/>milestone?}
    E -->|yes| F[Upgrade tier + upgrade_bonus<br/>generate milestone rewards]
    E -->|no| G[Balance updated]
    G --> H[Customer redeems at POS/cart<br/>OTP → FIFO burn]
    H --> I[wallet_history txn_type=2<br/>available-=x, used+=x]

Earn. Order completes → row inserted in crm_points_allocation_queue (type=uen_order). Cron drains it every 2 min → handleWalletPointsFlow computes points from the order-type-specific allocation % and the customer's milestone level, then credits wallet + appends a FIFO lot to wallet_history (txn_type=1, usedStatus=0, notUsedAmount, validTill).
Welcome points (KYC). If welcome_kyc=1, welcome points are only granted once the customer completes KYC (birthday/gender/email). Enqueued as type=welcome_point.
Tier up. If the order pushes LTO/LTV/wallet across a milestone, handleMilestoneUpgrade bumps milestone_level, credits upgrade_bonus, and generates milestone-unlock rewards.
Redeem (burn). Customer requests redemption → OTP (authenticate_points_redemption, which debits the business wallet for the OTP send) → redeem_points burns oldest lots first (FIFO), writes wallet_history txn_type=2.
Expiry. Points expire at validTill (= order date + loyaltyDays). Downgrade forfeits unredeemed points (D1.2).

2.2 Edge cases¶

Case	Rule	Validation
Cancelled/refunded order	Points clawed back; `revokeLoyaltyPoints` reverses FIFO + earned points, sets `wallet_history` status=2	✅ (posist)
Tier downgrade	Step −1 after `downgrade_days` inactivity; MTO/MTV reset to new step's `min_orders`; unredeemed points forfeited	✅ D1.1/D1.2
Welcome points before KYC	Withheld until KYC complete when `welcome_kyc=1`	✅ D1.4
Different earn % per channel	`delivery/pickup/dinein/incar_allocation_pct` (fallback `allocation_pct`)	✅ D1.3
Fraud/blocked customer	`checkCustomerIsBlocked` before allocation and redemption	✅
POS pending redemption	If `pos_redemption_expiry_minutes>0`, redemption is `status=0` (pending) until POS order matches; auto-revoked on expiry	✅
Dual-store drift	Non-atomic MySQL+Mongo writes → 2-point discrepancy	⚠️ E1.10

3. Technical Flow (source → API → validation → logic → DB → events → queues → notifications → response)¶

SOURCE:      order (Edge/POS/aggregator) OR microsite redemption OR POS redemption
API:         /allocation_points (earn, no auth) | /redeem_points (auth) | posist /redeem_loyalty_points
VALIDATION:  business/parent id, mobile, checkCustomerIsBlocked (fraud), redeem_points_threshold, OTP
LOGIC (earn): handleWalletPointsFlow
               point_allocation_type → base amount (1=total, 2=subtotal, 3=subtotal-discount-wallet_amount)
               milestone lookup (type1=LTO, type2=wallet, type3=LTV; downgrade uses MTO/MTV)
               order-type allocation_pct → final_amt (or flat if fixed_allocation)
               cap by max_allocation_amount
LOGIC (burn): calculateRedeemPoints (redemptionType 1=% of points, 2=% of subtotal) → redeemLoyaltyPoints (FIFO)
DB:          MySQL wallet (total_points/available/used/expired/validTill),
             wallet_history (FIFO lots), wallet_rules (aggregate total_points/available),
             MongoDB customers (LTO/LTV/MTO/MTV/milestone_level), loyalty_mis
EVENTS:      milestone upgrade/downgrade → milestone_logs; reward generation → rewards_{parent_business_id}
QUEUES:      crm_points_allocation_queue (MySQL, primary) ; loyalty_points_allocate (Mongo, legacy ⚰️)
NOTIFS:      sendWalletPointsCommunication (WhatsApp/SMS) — debits business wallet_balance, logs wallet_transactions
RESPONSE:    JSON {balance, redeemable, rules} ; report endpoints return MIS/ledger

Non-atomicity ⚠️: the cron may credit MySQL wallet then fail before updating MongoDB customers (or vice versa). Retry (max 5, 15-min backoff) can re-apply partial work → drift → E1.10.

4. Architecture Diagram¶

4.1 Earn pipeline (flowchart)¶

flowchart TB
    ORD[Order completed] --> Q[(MySQL crm_points_allocation_queue<br/>type=uen_order/order_reward/welcome_point/refer_earn<br/>status=pending)]
    Q -->|every 2 min, batch 5000, retry x5/15min| CRON[crmPointsAllocationQueueCron.js]
    CRON -->|uen_order| HWPF[handleWalletPointsFlow]
    CRON -->|order_reward| HOR[helpers.handleOrderRewards]
    CRON -->|welcome_point / refer_earn| MAN[wallet_history insert + wallet update]
    HWPF --> WAL[(MySQL wallet)]
    HWPF --> WH[(MySQL wallet_history FIFO)]
    HWPF --> WR[(MySQL wallet_rules aggregate)]
    HWPF --> CUST[(MongoDB customers LTO/LTV/MTO/MTV)]
    HWPF --> MU{milestone crossed?}
    MU -->|yes| UP[handleMilestoneUpgrade<br/>upgrade_bonus + rewards]
    UP --> ML[(MongoDB milestone_logs)]
    HWPF -.->|comms| WT[(MongoDB wallet_transactions)]
    LEG[(Mongo loyalty_points_allocate ⚰️ legacy)] -.->|pointAllocation.js 1min| HWPF

4.2 Milestone state machine¶

stateDiagram-v2
    [*] --> L0: new customer (milestone_level=0)
    L0 --> L1: LTO/LTV/wallet crosses step → upgrade_bonus
    L1 --> L2: next threshold
    L2 --> L1: inactivity ≥ downgrade_days (step−1, points forfeited)
    L1 --> L0: inactivity (step−1)
    L2 --> L2: active, retained

4.3 POS redemption sequence¶

sequenceDiagram
    autonumber
    participant POS as Posist POS
    participant API as posistLoyaltyIntegration
    participant SQL as MySQL wallet/history
    participant PR as pending_redemptions
    POS->>API: get_loyalty_details (crm_token)
    API-->>POS: balance + redeemable coupons
    POS->>API: authenticate_points_redemption
    API->>API: generate OTP, debit business wallet for send
    POS->>API: redeem_loyalty_points (OTP)
    API->>SQL: FIFO burn, wallet_history status=0 (pending)
    API->>PR: track expiry (pos_redemption_expiry_minutes)
    Note over PR: no matching POS order in window → auto-revoke
    POS->>API: (on cancel) revoke_loyalty_points → status=2, restore FIFO

5. Folder Structure (real files)¶

uengage-crm/
├── controllers/
│   ├── loyaltyController.js            ✅ rules, milestones, allocation, redemption, ledger, MIS, referral rules
│   ├── rewardsController.js            ✅ rewards catalog + purchaseLoyaltyRewards + checkLoyaltyRewards
│   ├── posistLoyaltyIntegration.js     ✅ POS redeem/revoke/coupon + createPosCustomer
│   └── uengageloyaltyController.js     ⚰️ empty placeholder (never implemented)
├── commonFunctions/
│   ├── handleWalletPointsFlow.js       ✅ core earn engine (dual-store)
│   ├── handleMilestoneUpgrade.js       ✅ upgradeMilestoneLevel
│   └── rewardHelper.js                 ✅ generatePromo (promo_type 7/9/10)
├── crons/
│   ├── crmPointsAllocationQueueCron.js ✅ MySQL queue drain (2 min, 5000/batch, retry x5)
│   ├── pointAllocation.js              ⚰️ legacy Mongo loyalty_points_allocate (1 min)
│   ├── loyalty_milestone_downgrade.js  ✅ downgrade (02:00, batch 1000)
│   ├── loyaltyCustomerPointsCron.js    ✅ Redis dashboard cache (02:35)
│   └── loyaltyPointMIS.js              ✅ loyalty MIS (see Wallet doc)
└── prism_routes/
    └── loyalty_rewards_routes.js       ✅ loyalty + rewards routes
    (routes/posistLoyaltyIntegration.js) ✅ POS routes

6. Database¶

MySQL (authoritative)¶

Table	Purpose	Key columns (✅ from code)
`wallet_rules`	Per-`parentBusinessId` loyalty config	`redemptionType`, `redemptionPct`, `loyaltyDays`, `walletName`, `welcomePoints`, `minOrderValue`, `point_allocation_type`, `milestones`, `single_milestone`, `downgrade`, `welcome_kyc`, `flowType`, `total_points`, `available`, `tnc`, `how_to_use`, `how_to_earn`
`wallet`	Per-customer balance	`contactMappingId`, `parentBusinessId`, `mobileNo`, `total_points`, `available`, `used`, `expired`, `validTill`
`wallet_history`	Append-only ledger (FIFO lots)	`wallet_id`, `amount`, `txn_type` (1=credit/2=debit), `orderId`, `usedStatus`, `notUsedAmount`, `validTill`, `source`, `event_date/month/year/hour`
`loyalty_milestones`	Tier definitions	`milestone type` (1=LTO,2=wallet,3=LTV), `min_orders`, `max_orders`, `allocation_pct` + per-order-type pct, `redemption` pct, `upgrade_bonus`, `downgrade_days`, `unlock_type`
`crm_points_allocation_queue`	Allocation queue	`type` (uen_order/order_reward/welcome_point/refer_earn), `status` (pending/done/failed), `retry_count`, `next_retry_at`, `contactMappingId`, `orderId`, `points`
`addo_contacts_mapping`	Customer↔business + `milestone_level`	`contactMappingId`, `businessId`, `milestone_level`
`referral_rules`, `referral_queue`	Refer & Earn config/queue	see Referral

MongoDB (mirrors / real-time)¶

Collection	Purpose
`customers`	`LTO`, `LTV`, `MTO`, `MTV`, `milestone_level`, `last_milestone_action`, `dob_md`
`wallet_transactions`	communication cost ledger for loyalty OTP/notification sends (service_id 1=SMS, 4=WhatsApp) — NOT point credits
`loyalty_mis`	daily `total_credited`, `total_redeemed`, `total_expired`
`milestone_logs`	upgrade/downgrade audit `{mobileNo, parentBusinessId, milestoneStep, type, insertedAt}`
`rewards_{parent_business_id}`	purchased/unlocked rewards per customer
`wallet_config`, `business_configs`	program flags, `wallet_balance` (business wallet), `crm_token`

Relationships: mobileNo → addo_contacts_mapping.contactMappingId → wallet (1:1) → wallet_history (1:many). MongoDB customers keyed by mobileNo.

Indexes: ⚠️ Database map flags missing indexes on wallet(parentBusinessId, validTillDate) and MongoDB business-id fields. Redemption/expiry queries scan.

Invariant: total_points = available + used + expired must hold (Data Validation Protocol). E1.10 = this invariant broken by ~2 points.

Common queries (✅):

-- balance + tier
SELECT total_points, available, used, expired, milestone_level
FROM wallet w JOIN addo_contacts_mapping acm ON w.contactMappingId=acm.contactMappingId
WHERE w.mobileNo=? AND w.parentBusinessId=?;

-- expiring soon (loyaltyCustomerPointsCron)
SELECT COUNT(*) FROM wallet
WHERE total_points>0 AND validTillDate BETWEEN CURDATE() AND CURDATE()+INTERVAL 5 DAY;

7. APIs¶

Representative — verify in prism_routes/loyalty_rewards_routes.js & routes/posistLoyaltyIntegration.js. All POST unless noted.

Loyalty / rewards (`prism_routes/loyalty_rewards_routes.js`)¶

Path	Handler	Auth	Purpose
`/add_edit_rules`	`loyaltyController.addEditRules`	authMiddleware	upsert `wallet_rules`
`/add_edit_milestone`	`loyaltyController.addEditMilestones`	authMiddleware	upsert tiers
`/fetch_loyalty_details`	`loyaltyController.fetchRulesMilestones`	authMiddleware	rules + milestones
`/day/wise/data`	`loyaltyController.dayWiseLoyaltyData`	authMiddleware	daily MIS
`/loyalty/ledger`	`loyaltyController.loyaltyLedger`	authMiddleware	paginated txns (20/page)
`/monthly/wallet/balance`	`loyaltyController.getMonthlyWalletBalance`	authMiddleware	opening/closing balance
`/allocation_points`	`loyaltyController.allocationPoints`	none ⚠️	earn (server-to-server)
`/authenticate_points_redemption`	`loyaltyController.authenticatePointsRedemption`	authMiddleware	OTP (debits biz wallet)
`/redeem_points`	`loyaltyController.redeemLoyaltyPoints`	authMiddleware	burn (FIFO)
`/get/customer/loyalty/details`	`loyaltyController.getCustomerLoyaltyDetails`	appMiddleware	microsite
`/view/all/rewards`	`loyaltyController.viewAllRewards`	appMiddleware	rewards list
`/get/referal/rules` etc.	`loyaltyController.getReferalRules/...`	authMiddleware	see Referral

POS (`routes/posistLoyaltyIntegration.js`)¶

Path	Handler
`/get_loyalty_details`	`getLoyaltyDetails`
`/authenticate_points_redemption`	`authenticatePointsRedemption`
`/redeem_loyalty_points`	`redeemLoyaltyPoints`
`/revoke_loyalty_points`	`revokeLoyaltyPoints`
`/get_coupon_details`	`getCouponDetails`
`/redeem_coupon_details`	`redeemCoupon`
`/update_pos_customer_details`	`createPosCustomer`

Example — earn (/allocation_points):

// request (server-to-server; no auth ⚠️)
{ "parentBusinessId": 123, "businessId": 456, "mobileNo": "9812345678",
  "order_id": "ORD1", "order_amount": 500, "subTotal": 450, "discount": 50,
  "orderType": 1, "wallet_amount": 0, "type": "uen_order" }
// response (verify)
{ "status": true, "points_credited": 22, "available": 122 }

Failures: blocked customer → fraud rejection; below minOrderValue → no points; missing OTP → redemption denied; order not found (queue) → retry then failed.

⚠️ /allocation_points has no auth middleware — an open earn endpoint. Flag for the A2.2 "Loyalty & Trust" hardening.

8. Code Walkthrough¶

crmPointsAllocationQueueCron.js  (*/2, batch 5000, MAX_RETRIES=5, 15-min backoff)
  ├─ type uen_order      → handleWalletPointsFlow(...) → sets addo_orders.cashbackReceived=1
  ├─ type order_reward   → helpers.handleOrderRewards
  └─ type welcome_point / refer_earn → manual wallet_history + wallet update

handleWalletPointsFlow(businessId, parentBusinessId, orderFrom, wallet_points_source,
                       customerData, token, order_id, order_amount, connection,
                       subTotal, discount, orderType, wallet_amount=0)
  1. base = point_allocation_type (1 total | 2 subTotal | 3 subTotal-discount-wallet_amount)
  2. milestone lookup (type1 LTO | type2 wallet | type3 LTV ; downgrade→MTO/MTV)
  3. pct = order-type allocation_pct (fallback allocation_pct); fixed_allocation→flat
  4. final_amt = min(base*pct/100 , max_allocation_amount)
  5. WRITE MySQL wallet, wallet_history (FIFO: usedStatus=0/notUsedAmount/validTill), wallet_rules aggregate
  6. WRITE MongoDB customers LTO/LTV/MTO/MTV + milestone_level
  7. if milestone_step > prev → handleMilestoneUpgrade (upgrade_bonus + milestone_logs + rewards)
  8. checkCustomerFraud; sendWalletPointsCommunication (debits business wallet)

redeemLoyaltyPoints
  ├─ validate OTP + redeem_points_threshold + checkCustomerIsBlocked
  ├─ calculateRedeemPoints (redemptionType 1=% points | 2=% subtotal; per-order-type pct; caps)
  └─ FIFO burn: wallet_history txn_type=2; available-=x, used+=x; wallet_rules aggregate

rewardsController.purchaseLoyaltyRewards
  ├─ deduct wallet (available-=amt, used+=amt), helpers.redemptionFifo
  ├─ generatePromo (promo_type 9=% / 10=fixed / 7=freebie) → addo_promo_code_engine
  └─ enforce max_allowed_per_customer via rewards_count_{parent_business_id}

Dependencies: MySQL pool (jobsPool for crons), MongoDB, fraudRulesController.checkCustomerIsBlocked, helpers.js (redemptionFifo, reverseFifoRedemption, handleOrderRewards, milestoneNotification), rewardHelper.generatePromo, Redis (dashboard cache), SMS/WhatsApp senders.

9. Business Rules¶

Rule	Config / value	Where	Validation
Point expiry window	`wallet_rules.loyaltyDays` (default 365) → `validTill = orderDate + loyaltyDays`	`handleWalletPointsFlow`, loyaltyController L3427	✅
Earn base	`point_allocation_type` 1/2/3	code	✅ D1.3
Per-order-type earn/burn	delivery/pickup/dinein/incar pct	milestones	✅ D1.3
Redemption model	`redemptionType` 1=% of points, 2=% of subtotal	`calculateRedeemPoints`	✅
FIFO ledger	`flowType=1` → FIFO lots (`usedStatus`,`notUsedAmount`,`validTill`)	code	✅
Milestone match	type1 LTO / type2 wallet / type3 LTV	code	✅
Upgrade bonus	`upgrade_bonus` credited on step increase	`handleMilestoneUpgrade`	✅
Downgrade	step−1 after `downgrade_days` inactivity; MTO/MTV reset to new `min_orders`; unredeemed forfeited	`loyalty_milestone_downgrade.js`	✅ D1.1/D1.2
Welcome points	`welcomePoints`, gated by `welcome_kyc`	code	✅ D1.4
Fraud gate	`checkCustomerIsBlocked` pre-allocate & pre-redeem	code	✅
Queue retry	max 5, 15-min backoff, batch 5000, every 2 min	cron	✅
Invariant	`total = available + used + expired`	Data Validation Protocol	⚠️ E1.10 broken

Feature flags: downgrade, welcome_kyc, single_milestone, flowType (FIFO), expireRewards, loyalty_communication, crm_offers. Permissions: rules/milestones = Admin/Marketing (authMiddleware); microsite = customer (appMiddleware); /allocation_points = unauthenticated ⚠️. Hidden logic: downgrade path secretly switches milestone matching from LTO/LTV to MTO/MTV (monthly counters); hardcoded WhatsApp/SMS template IDs for specific parent_business_ids in handleWalletPointsFlow (💻).

10. Performance¶

Queue drain every 2 min, 5000/batch — sized for throughput but ⚠️ can lag under order spikes; watch crm_points_allocation_queue depth.
FIFO redemption scans wallet_history lots per burn — costly for high-LTO customers; needs (wallet_id, usedStatus, validTill) index.
Downgrade cron batches 1000; optimized path when all milestones share downgrade_days, else per-customer (slower).
Missing indexes on wallet(parentBusinessId, validTillDate) slow expiry/segment queries (Database §5.1).
Dashboard reads are Redis-cached (loyaltyCustomerPointsCron) to avoid hot MySQL aggregation.

11. Logging¶

Winston across controllers/crons; log tags include "membershipReport"-style function tags in loyalty ledger/MIS calls.
crm_points_allocation_queue status transitions (pending→done/failed) + retry_count/next_retry_at are your audit trail — query the table directly.
milestone_logs = permanent upgrade/downgrade audit.
pointAllocation.js (legacy) emails prabhpreet@uengage.in, hani@uengage.in if stuck >20 min (💻 hardcoded).

12. Monitoring¶

Queue depth: SELECT status, COUNT(*) FROM crm_points_allocation_queue GROUP BY status; — rising pending/failed = drain problem.
Invariant check (E1.10 detector): SELECT * FROM wallet WHERE total_points <> available+used+expired;
Cron health: cron_status/cron_statuses collections (status flag + start_time).
Comms spend: MongoDB wallet_transactions debits (loyalty OTP/notification) drain the business wallet — monitor with Wallet.

13. Troubleshooting¶

Issue	Cause	Fix	Commands
Points not credited	queue not draining / order not found / retries exhausted	inspect queue row status + retry_count	`SELECT * FROM crm_points_allocation_queue WHERE orderId=? ;`
2-point discrepancy (E1.10)	non-atomic MySQL+Mongo, partial retry	reconcile `wallet` vs `wallet_history` sum; add idempotency	`SELECT total_points, available+used+expired FROM wallet WHERE mobileNo=?;`
Redemption denied	blocked customer / below threshold / bad OTP	check fraud block + `redeem_points_threshold`	`grep -n checkCustomerIsBlocked controllers/loyaltyController.js`
Wrong earn %	wrong order type or milestone	verify `orderType` in payload & milestone pct	`grep -n allocation_pct commonFunctions/handleWalletPointsFlow.js`
Tier didn't downgrade	`downgrade=0` or `downgrade_days` not set	enable in `wallet_rules`/milestone	check `crons/loyalty_milestone_downgrade.js` schedule 02:00
Welcome points missing	`welcome_kyc=1` and KYC incomplete	complete customer KYC	`SELECT welcome_kyc, welcomePoints FROM wallet_rules WHERE parentBusinessId=?;`
Legacy queue still filling	`loyalty_points_allocate` (Mongo) fed by old path	migrate to MySQL queue; deprecate `pointAllocation.js`	`grep -rn loyalty_points_allocate crons/`

14. FAQs¶

Q: Which store is authoritative for balance? MySQL wallet/wallet_history. Mongo mirrors are for UI/metrics. ⚠️ They can drift (E1.10).

Q: uen_order vs order_reward vs welcome_point vs refer_earn? The four crm_points_allocation_queue.type values → routed to handleWalletPointsFlow, handleOrderRewards, welcome allocation, and referral respectively.

Q: Is pointAllocation.js still needed? It's the legacy Mongo path (loyalty_points_allocate, every 1 min). Being superseded by the MySQL queue (⚰️/♻️ candidate).

Q: Why does earning debit the business wallet? It doesn't — earning is free. The notification about earned points (sendWalletPointsCommunication) costs an SMS/WhatsApp, which debits the business wallet_balance.

Q: What forfeits points? Expiry (validTill) and downgrade (D1.2).

15. Cheat Sheet¶

Earn:     order → crm_points_allocation_queue(type=uen_order) → [2min,5000,retry5] → handleWalletPointsFlow
Burn:     OTP (authenticate_points_redemption) → redeem_points → FIFO wallet_history txn_type=2
Config:   MySQL wallet_rules (loyaltyDays, redemptionType/Pct, welcome_kyc, downgrade, flowType, milestones)
Balance:  MySQL wallet (total_points/available/used/expired/validTill)  [AUTHORITATIVE]
Ledger:   MySQL wallet_history (FIFO: usedStatus/notUsedAmount/validTill)
Tiers:    loyalty_milestones (type1 LTO/2 wallet/3 LTV) ; upgrade_bonus ; downgrade_days
Crons:    crmPointsAllocationQueueCron */2 · loyalty_milestone_downgrade 02:00 · loyaltyCustomerPointsCron 02:35
Invariant total = available + used + expired   (E1.10 breaks this)
Legacy:   pointAllocation.js + loyalty_points_allocate (Mongo, deprecate)
Danger:   /allocation_points has NO auth ; dual-store non-atomic

Wallet — the ledger + the business messaging wallet debited by loyalty notifications.
Membership — paid tier; shares wallet_rules.
Referral — refer_earn queue type (D1.5).
Fraud-Prevention — checkCustomerIsBlocked gates every allocation/redemption; A2.2 merges these into "Loyalty & Trust".
Customer-CRM — LTO/LTV/milestone live on the customer profile.
Queues · Database · Roadmap D1.x / E1.10

Knowledge Tests¶

Level 1 — MCQs¶

Which store is authoritative for a customer's point balance? a) MongoDB customers b) MySQL wallet/wallet_history c) Redis d) wallet_transactions Answer: b. Mongo holds mirrors/metrics; MySQL is the ledger of record.
crm_points_allocation_queue is drained… a) every 1 sec b) every 2 min, batch 5000, retry ×5 c) nightly d) on demand Answer: b. crmPointsAllocationQueueCron.js, MAX_RETRIES=5, 15-min backoff.
On tier downgrade, unredeemed points are… a) kept b) doubled c) forfeited (D1.2) d) converted to cash Answer: c. Downgrade forfeits unredeemed points and resets MTO/MTV.
redemptionType=2 means redemption is a % of… a) wallet points b) the subtotal/cart c) LTV d) upgrade_bonus Answer: b. Type 1 = % of points; type 2 = % of subtotal.
Bug E1.10 (2-point discrepancy) is most directly caused by… a) a UI rounding bug b) non-atomic MySQL+Mongo point writes c) FIFO ordering d) OTP expiry Answer: b. No distributed transaction across stores → drift.

Level 2 — Scenarios¶

A customer earned points 20 min ago but the microsite shows the old balance while POS shows the new one. Expected: MySQL wallet (POS reads) updated but MongoDB customers mirror lagged/failed — the non-atomic split. Reconcile; check queue row status and whether the Mongo update step errored.
After a milestone upgrade, a customer's earn rate changed even though order type is the same. Expected: earn % is milestone-specific (per-tier allocation_pct); upgrade moved them to a tier with a different rate. Verify milestone_level and the tier's per-order-type pct.

Level 3 — Code Reading¶

In handleWalletPointsFlow.js, point_allocation_type=3 computes base as subTotal - discount - wallet_amount. Question: for an order with subTotal=450, discount=50, wallet_amount=100, and a dinein tier dinein_allocation_pct=10, no max_allocation_amount, how many points are earned? Expected: base=300, points = 300×10/100 = 30.

Level 4 — Architecture¶

The system keeps loyalty points in MySQL but also mirrors state in MongoDB, written non-atomically. Propose an architecture that guarantees total = available + used + expired while preserving fast microsite reads. Expected: single source of truth (MySQL) + idempotency key on queue rows + outbox/CDC to Mongo (or Mongo as pure read-replica of derived state) + a daily reconciliation job; reference A2.2 "Loyalty & Trust".

Level 5 — Production Debugging¶

Ticket: "Points allocation stopped for one parent business at 14:00; others fine." Expected reasoning: query crm_points_allocation_queue filtered by that business → likely rows stuck pending with retry_count climbing or failed. Check for a poison row (order not found / null mobile) blocking or repeatedly failing, cron stuck flag in cron_status, or MySQL jobsPool exhaustion. Fix the poison row / raise pool / clear stuck flag; verify drain resumes.

Practical Assignments¶

Trace an earn end-to-end. Insert (in a test env) a crm_points_allocation_queue row (type=uen_order) and follow it through handleWalletPointsFlow into wallet, wallet_history, and MongoDB customers. Document every write and confirm the invariant holds after.
Build the E1.10 detector. Write a reconciliation query/script that lists customers where total_points <> available+used+expired, grouped by parentBusinessId, and propose the fix (idempotency + outbox).
Audit the FIFO burn. Redeem points for a customer with 3 lots and verify oldest-lot-first consumption in wallet_history (usedStatus, notUsedAmount), then simulate a POS revoke and confirm reverseFifoRedemption restores the lots.