Skip to main content

Purchase History

Implementation Status (feature 017)​

✅ Phase 1 — MVP (implemented)​

  • GET /customers/:id/purchase-summary — live metrics (LTV, visit count, avg, last date); 60 s Redis TTL cache
  • GET /customers/:id/purchases — paginated UNION of sale + order rows; supports fromDate/toDate/type/saleType/locationId filters
  • GET /customers/:id/purchases/:purchaseId — full receipt detail (line items, variants, discounts, payment methods, parent link for returns)
  • FK constraint added: sale.customer_id REFERENCES customer(id) ON DELETE SET NULL
  • PWA: PurchasesTab with PurchaseSummaryPanel, PurchaseTimeline, PurchaseDetailDrawer, filter bar
  • Customer merge: reattributeSalesToCustomer re-attributes all sale+order rows in a single transaction

🔜 Phase 2 — Deferred​

  • customer_metrics snapshot table (live queries cover current scale)
  • Favorites / frequently bought again (deferred per clarification)
  • Quick-return action from receipt drawer
  • Offline sync write queue

Original Specification Notes​

Purchase history is where the customer profile becomes intelligent. Without purchase history → customer = contact With purchase history → customer = behavior data This powers loyalty, recommendations, segmentation, returns lookup, and analytics. Here’s what to add for Customer Management → Purchase history. What “Purchase history” means (business meaning) Purchase history is the timeline of everything a customer has bought (and returned). It allows: quick receipt lookup customer service loyalty insights product recommendations segmentation marketing triggers In retail → style/size behavior In restaurant → frequency + preferences Core concept (important) Purchase history is not a separate entity. It is a projection of sales linked to customer. Meaning: 👉 Do NOT create a “purchase_history table” 👉 Use sale + sale_line + customer_id But you may add derived views for performance. What you must track (minimum data already needed) Ensure your sale table includes: customer_id (nullable but recommended) sale type (sale/return/exchange/layaway completion) totals snapshot location employee created_at Ensure sale_line includes: product/variant qty price snapshot discount snapshot bundle/promo references That is enough to build history. Derived purchase history signals (very important) These should be computed: Customer-level metrics lifetime value (LTV) total orders last purchase date frequency average ticket favorite categories favorite sizes (apparel) favorite items These become extremely valuable. Database additions (recommended)

  1. Ensure indexes exist (critical) You must index: sale(customer_id, created_at) sale_line(product_id) sale_line(variant_id) Without this, history screens become slow.
  2. Optional: customer metrics snapshot (Phase 2) For performance: customer_metrics customer_id lifetime_value order_count avg_ticket last_purchase_at This is a derived cache, not source of truth.
  3. Optional: customer_product_stats (very powerful) customer_product_stats customer_id variant_id purchase_count last_purchase_at Enables recommendations. MVP can skip. Backend functionality you need Core queries get customer orders list get order detail (receipt view) get returns linked to customer compute summary metrics Endpoints GET /customers/:id/purchases GET /customers/:id/purchase-summary GET /customers/:id/favorites (Phase 2) Frontend screens (PWA) Customer profile → Purchases tab Show: order list (date, total, location) status (sale/return/exchange) quick receipt view quick reprint quick return action This is used constantly by staff. Advanced (Phase 2 UX) favorite products size history (apparel huge) reorder suggestion (restaurant huge) customer value badge Reports (Metabase — extremely valuable) customer lifetime value distribution cohort analysis repeat purchase rate top customers category affinity churn risk customer segmentation size demand analysis (apparel gold) This is where analytics shines. Retail vs Restaurant differences Retail: variant history critical (size/color) style preference season behavior return patterns Restaurant: favorite items visit frequency reorder behavior time-of-day patterns Same base data model. MVP vs Phase 2 ✅ MVP purchase history customer_id on sale indexed queries purchase list UI receipt drill-down return lookup via history 🚀 Phase 2 customer metrics snapshot recommendations segmentation engine predictive churn size preference modeling AI personalization marketing triggers Biggest edge cases (important) Guest sales later linked to customer Customer merge → history merge Returns after merge Multi-location visibility rules Data privacy deletion rules Performance at scale Offline sync These must be planned. Strategic insight for FlowPOS (important) Purchase history is the foundation of: loyalty intelligence recommendation engine targeted promotions replenishment insights AI assistant features Meaning this is one of the most important “data exhaust” features. If you keep sales immutable and well indexed → everything else becomes easier. If you want, next I can give you: Customer metrics computation queries (Metabase-ready) ⭐ recommended Customer segmentation model (very valuable) Recommendation engine design (FlowPOS differentiator) Customer merge architecture (important) Privacy/GDPR design (future-proof) Restaurant reorder UX design