Order Count : Backend HLD

Status	PLANNING
Prepared and Implementation By	@Teg Anmol Singh (Unlicensed)
Technical Review By
Jira ticket	PLAT-1443: Introduce segments & smart segments on 'All Orders'PLAN
Related docs	Figma \|\| Returns (workspace): WIP \| Nov 2023 Old segment flow:Segments New segment backend document: New Segment, Smart Segment & Filters \| HLD Return count integration: Record Count API for return service

Document Outline:

Introduction
Requirements
Scope

Introduction:

This document outlines the backend architecture and design principles for providing order count functionality as per different filters. It discusses the requirements, scope, system design, segment module structure, and integration with another module.

UI SCREEN:

Requirement:

By introducing the segments & smart segments on various listing screens in the Eshopbox workspace, our goal is to enhance the structure of the listing pages. This will make it easier for users to view and prioritize actions that need to be completed. We aim to improve the user experience by providing a clear overview of orders requiring intervention to resolve any issues that may arise.

Implement a frontend system to manage segment-related functionality.
Display segments and smart segments and my view with applicable display name, count, description(on hover), and icons.
Refresh CTA with the last sync timestamp: On click, the record count will fetched from the API
Allow users to select segments, smart segments and my view.
Provide filter options based on selected segments/smart segments/my view.
Allow users to apply filters and retrieve the records/data.
Show filter chips based on filter query with functionality to change the filter using chip

Scope:

The scope includes designing an API which returns the order count on the basis of segments and the corresponding filters provided against them to meet the requirements. It involves decoding the filters for segments, smart segments, my view (Caching segment, count and filter data).

Backend System Design:

Record Count API by Service:

Order endpoint can offer a record count API with quick or zero-latency response times to the frontend.
The segment service will then aggregate and send the response to the frontend segment API along with the record count.

Backend Module Implementation:

The shipping team can implement a lightweight method within the order return new service in the existing repository for record count.
This method should ensure minimal latency.

FrontEnd to service API call

Order Return service can offer a record count API with quick or zero-latency response times to the frontend.
Service will use cache to store records count to reduce latency

As per the discussion, we have decided to go with FrontEnd to service API call approach for order service.

API

API Method: POST

API Endpoint: TBD

Request

Sample Base URL (POST): https://eshop.auperator.co/allOrders/count
Payload:
Default segment
{
    filterQuery: [
     "61d2d83019e2b31cf7d350a1": "orderCreatedOn=last30days&status=|IS|Ready to ship|Out for delivery",
     "61d2d83019e2b31cf7d350a2": "orderCreatedOn=last30days&status=|IS|Unfulfillable"
     ] // key - {segmentId:filterquery, 
userDefined: false

}

User defined:
{
    filterQuery: [
     "61d2d83019e2b31cf7d350a1": "orderCreatedOn=last30days&status=|IS|Processing|New",
     "61d2d83019e2b31cf7d350a2": "orderCreatedOn=last30days&status=|IS|Delivered"
     ] // key - {segmentId:filterquery}
, 
userDefined: true
}

Response:

{
  "61d2d83019e2b31cf7d350a1":20,
  "61d2d83019e2b31cf7d350a2":40,
  "updated_at":'2024-03-01 00:00:00'
}

Fields to create user defined segments:

Filters	CurrentOrderStatus on Algolia

Filters	CurrentOrderStatus on Algolia
Date Range	Handover done
Sales Channel	Restocked
Location	Delivered
RTS Urgency	Cancelled
Payment Method	Return on the way
Forward Shipping Mode	New
Risk Score	Quarantined
Product Type	Return in processing
Shipment Zone	Unfulfillable
More Filters	Processing
	Lost in processing
	Ready to ship
	Damaged
	On the way
	Out for delivery
	On hold
	Return lost in transit
	Awaiting pickup
	Lost in transit
	Pickup cancelled

Segment (To Process)	Preset filter(Can't be removed) ,only date range is editable, rest is uneditable	Primary filters

Segment (To Process)	Preset filter(Can't be removed) ,only date range is editable, rest is uneditable	Primary filters
Pending to pack	Order created on is last 30 days, Status is New or Processing	Sales channel, Location, RTS urgency, Forward shipping mode, Risk Score, Product type (product/virtual kit), Shipment Zone, Payment method
Pending to Handover	Order created on is last 30 days, Status is Ready to ship	Sales channel, Location, Handover urgency, Expected handover by, Forward shipping mode, Product type (product/virtual kit)
Unfulfillable	Order created on is last 30 days, Status is Unfulfillable	Sales channel, Location, Unfulfillable reason, Risk Score, Product type (product/virtual kit), Payment method
On hold	Order created on is last 30 days, Status is on hold	Sales channel, Location, Forward shipping mode, Risk Score, Product type (product/virtual kit) (Renamed from sold as virtual kit), Shipment Zone, On hold reason, Payment method
Failed to RTS	Order created on is last 30 days, Status is processing AND failed to RTS is YES	Sales channel, Location, RTS urgency, Risk Score, Failed to RTS reason (new filter), Payment menthod
Failed to handover	Order created on is last 30 days, Status is Ready to ship AND failed to handover is YES	Sales channel, Location, Handover by, Product type (product/virtual kit), Failed to handover reason (new filter)
Pickup failed	Order created on is last 30 days, Pickup failed is YES	Sales channel, Location, Handover by, Product type (product/virtual kit), Pickup failed reason (new filter)
All	Order created on is last 30 days, Status is On hold, unfulfillable, processing, ready to ship, New	Primary filters will have all the unique filters of the parent segment excluding need attention. Sales channel, Location, Ready to ship by, Handover by, Forward shipping mode, Risk score, Product type, Shipment zone

Segment (In Transit)	Preset filter(Can't be removed) ,only date range is editable, rest is uneditable	Primary filters

Segment (In Transit)	Preset filter(Can't be removed) ,only date range is editable, rest is uneditable	Primary filters
Handover dispute	Order created on is last 30 days, Status is Handover done AND shipping is Eshopbox shipping for more than 24 hours	Sales channel, Location
Failed to deliver	Order created on is last 30 days, Status is on the way AND failed to deliver is YES	Sales channel, Location, Delivery by, On time Delivery, Number of delivery attempts, Failed delivery reason, Failed delivery action status, Failed delivery action,
Stuck in transit	Order created on is last 30 days, Status is on the way OR out for delivery AND stuck in transit is YES	Sales channel, Location, Delivery by, On time Delivery, Stuck in transit ageing, Stuck in transit reason
All	Order created on is last 30 days, Status is Handover done OR on the way OR out for delivery AND shipping is Eshopbox shipping	Sales channel, Location, Delivery urgency, On time Delivery, Delivery attempts

Segment (Completed)	Preset filter(Can't be removed) ,only date range is editable, rest is uneditable	Primary filters

Segment (Completed)	Preset filter(Can't be removed) ,only date range is editable, rest is uneditable	Primary filters
Handover done	Order created on is last 30 days, Status is Handover done AND shipping is third party shipping	Sales channel, Location, Paid on, Payment satus
Delivered	Order created on is last 30 days, Status is Delivered	Sales channel, Location, Paid on, Payment satus
Lost in transit	Order created on is last 30 days, Status is Handover done AND shipping is Eshopbox shipping
All

Segment (Cancelled)	Preset filter(Can't be removed) ,only date range is editable, rest is uneditable	Primary filters

Segment (Cancelled)	Preset filter(Can't be removed) ,only date range is editable, rest is uneditable	Primary filters
Cancelled	Order created on is last 30 days, Status is Cancelled	Sales channel, Location, Cancelled by, Cancellation reason, Payment Method

To achieve this we have 2 approaches

Need to sync the 4 new added keys to algolia which is faster but will have additional cost.
Fetch all the details from BQ which will be a little slower and will be difficult to manage all the filters.

Approach 1 -

Need to add these 4 keys to algolia-

On time handover (Logic on the basis of first attempt and done dates of handover from tracking_payload in shipment status logs)
Expected handover on (shipments.dispatch_cpt)

OR

We can use BQ to sync data to algolia.

Approach 2 -

Need to design logic to fetch data from BQ as there are 50+ filter keys, will involve a lot of input for implementing, will be slower as compared to algolia and will be slower as compared to approach 1.

As discussed we will add the required keys to algolia and proceed with approach 1.