Returns Processing System

Three nested objects frame the request: customer with membershipTier and returnsHistoryCount, product with type, condition, daysFromPurchase, and price, and return with reason and hasReceipt. The schema pins membershipTier to standard, premium, or vip and condition to five states, which the classification table matches against directly.

Four boolean flags are derived up front: isHighValueCustomer is true for a 'vip' or 'premium' customer.membershipTier, isFrequentReturner trips when customer.returnsHistoryCount > 5, isPurchaseWithinWarranty checks product.daysFromPurchase <= 30, and isHighValueItem marks product.price >= 100. Precomputing these keeps the big classification table free of repeated tier and threshold logic and lets later tables branch on single fields.

Eight first-hit rows cross product.type, product.condition, return.reason, product.daysFromPurchase, and return.hasReceipt into a return type, refund, and fee. Defective 'electronics' or 'appliances' in new condition within 30 days with a receipt earn 'warranty_replacement' at full product.price; damaged or opened units within 15 days drop to 'partial_refund' at 80% with a 10 restocking fee; clothing follows a parallel pair of rows at 100% or 70% store credit. Reasons containing 'quality' get a 'quality_review' within 90 days, receiptless returns past 30 days become discounted 'store_credit' where isHighValueCustomer lifts the payout from 50% to 80% and waives the 15 fee, anything past 90 days is 'denied', and the catch-all routes to 'manual_review'.

The economics behind the tiers are standard retail returns practice: opened electronics cannot be resold as new, so the refund haircut and restocking fee recover part of the remarketing loss, and receiptless returns pay out less because the purchase price cannot be verified. Softening those penalties for premium and vip customers is a deliberate retention trade, spending margin on the customers with the highest lifetime value, while the hard 90-day cutoff bounds the liability window entirely.

Queue routing keys on returnDetails.type alone: 'warranty_replacement' goes to the 'warranty_dept', 'quality_review' to 'quality_assurance', 'manual_review' to 'customer_service', and the blank catch-all sends everything else to the general 'returns_dept'. First hit ordering makes the three specialist rows take precedence over the default.

Separating queues this way matches how return operations are actually staffed: warranty claims need technicians who can test the unit, quality reviews feed defect data back to sourcing, and edge cases need a human with discretion. Keeping the mapping in its own table means a reorganization changes four rows, not the classification logic.

Service speed pairs isHighValueCustomer with isHighValueItem: a high-value customer gets 'high' priority and 2 expected processing days regardless of item value, an ordinary customer returning an item worth 100 or more gets 'medium' and 5 days, and everything else runs 'standard' at 7 days.

Putting the customer flag ahead of the item flag encodes a retention-first stance, since a premium member returning a cheap item still sees the fast lane. The 2, 5, and 7 day targets are internal SLA choices, useful mainly because publishing an expected timeline cuts the where-is-my-refund contact volume.

Messaging follows the verdict in three rows on returnDetails.type: 'denied' triggers the 'rejection_notice' template, 'partial_refund' and 'store_credit' share the 'partial_approval' template, and the catch-all sends 'approved' for everything else.

Collapsing many return types into three templates is a sensible communications choice, because customers only need to know whether they get everything, something, or nothing. Binding the template to the decision output also guarantees the letter can never contradict the refund that was actually booked.