Reorder Point Service Low-Level Design: Demand Forecasting, Safety Stock, and Purchase Orders

What Is a Reorder Point Service?

A reorder point service determines when to replenish a SKU by comparing current stock levels against a dynamically computed threshold. It combines demand forecasting, safety stock calculation, and automated purchase order generation to keep inventory healthy without overstocking.

Requirements

Functional Requirements

Compute reorder point per SKU per warehouse using historical sales velocity and supplier lead time.
Calculate safety stock to buffer against demand variability and late deliveries.
Generate a purchase order draft when available plus on-order stock falls below the reorder point.
Allow manual override of computed thresholds per SKU.
Expose forecasted stock-out date given current velocity.

Non-Functional Requirements

Threshold recomputation must complete nightly for all active SKUs within a two-hour window.
Purchase order generation must be idempotent to avoid duplicate orders during retry storms.
System should handle seasonal demand spikes without human intervention.

Data Model

sales_history: sku_id, warehouse_id, sale_date, units_sold. Partitioned by month for efficient window queries.
forecast_params: sku_id, warehouse_id, avg_daily_demand, demand_stddev, lead_time_days, lead_time_stddev, service_level_z (e.g. 1.65 for 95%), computed_at.
reorder_threshold: sku_id, warehouse_id, reorder_point, reorder_qty, is_manual_override, effective_from.
purchase_order: po_id, sku_id, warehouse_id, supplier_id, quantity, status (DRAFT, SUBMITTED, CONFIRMED, RECEIVED), idempotency_key, created_at.

Core Algorithms

Moving-Average Demand Forecasting

The service computes a weighted moving average of daily units sold over the past 28 days, giving more weight to recent days using an exponential decay factor (alpha = 0.2). This produces avg_daily_demand. Standard deviation of daily demand over the same window yields demand_stddev.

Safety Stock Calculation

Safety stock absorbs variability in both demand and lead time. The formula used is:

safety_stock = Z * sqrt(lead_time * demand_stddev^2 + avg_daily_demand^2 * lead_time_stddev^2)

Where Z is the service-level factor from the normal distribution. A 95% service level uses Z = 1.65. This formula accounts for combined variance across both dimensions.

Reorder Point Formula

reorder_point = avg_daily_demand * lead_time_days + safety_stock

When the inventory service reports that available_qty + on_order_qty falls at or below this value, the reorder service emits a purchase order. Reorder quantity is computed as the economic order quantity (EOQ) or a fixed multiple of the average weekly demand, configurable per SKU.

Idempotent Purchase Order Generation

The idempotency key is a hash of (sku_id, warehouse_id, trigger_date). Before inserting a new purchase order the service checks for an existing row with the same key and status not in (CANCELLED). This prevents duplicate orders when the trigger event is replayed from Kafka.

API Design

GET /forecast/{sku_id}?warehouse_id=X — returns avg_daily_demand, safety_stock, reorder_point, stockout_date.
PUT /threshold/{sku_id} — manual override of reorder_point and reorder_qty.
POST /reorder/trigger — internal endpoint called by inventory service events; validates and enqueues PO generation.
GET /purchase-orders?sku_id=X&status=DRAFT — lists pending purchase orders for buyer review.
POST /purchase-orders/{po_id}/submit — buyer approves and submits to supplier integration.

Scalability and Reliability

Nightly Batch Recomputation

A scheduled job partitions the SKU catalog into chunks and processes each chunk in parallel using a worker pool. Each worker queries sales_history, runs the forecasting and safety stock calculations, and upserts forecast_params and reorder_threshold. The job tracks progress in a checkpoint table to enable resume after failure.

Real-Time Trigger Path

In addition to the nightly batch, the reorder service subscribes to a StockChanged Kafka topic. For each event it checks the current stock level against the stored reorder_threshold. This real-time path uses the precomputed threshold so it is O(1) per event with no forecast computation at trigger time.

Seasonal Adjustment

For seasonal products a multiplicative seasonality factor is stored per SKU per calendar week (computed from year-over-year sales data). The nightly job multiplies avg_daily_demand by the factor for the upcoming lead-time window before computing the reorder point. This prevents stockouts during peak seasons.

Trade-offs and Interview Discussion Points

Simple moving average versus exponential smoothing: EWM reacts faster to demand shifts but amplifies noise; a higher alpha reduces lag at the cost of stability.
Fully automated PO submission versus buyer approval gate: automation speeds replenishment but exposes the business to runaway spend if demand spikes are anomalous (promotions, data errors).
Per-SKU thresholds versus category-level defaults: per-SKU is more accurate but adds operational overhead for large catalogs; a tiered approach (top 10% by volume get individual tuning) balances both.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How does EWM demand forecasting feed into a reorder point system?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Exponentially Weighted Moving Average (EWM) applies a decay factor to historical sales so recent demand influences the forecast more than older data. The smoothed demand estimate is fed directly into the reorder point formula as the expected daily demand, allowing the system to adapt to trend shifts without a full retraining cycle.”
}
},
{
“@type”: “Question”,
“name”: “What is the safety stock formula when supplier lead time and demand both vary?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “When both demand and lead time are variable, safety stock = Z * sqrt(avg_lead_time * σ_demand² + avg_demand² * σ_lead_time²). The combined variance term accounts for uncertainty in both dimensions independently, and Z is the service-level z-score (e.g., 1.65 for 95%).”
}
},
{
“@type”: “Question”,
“name”: “How is automated purchase order generation triggered from a reorder point service?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A scheduled job or real-time inventory event compares current stock-on-hand plus open PO quantity against the computed reorder point. When stock falls below that threshold, the service publishes a PO-request event to a queue; a downstream purchasing microservice consumes it, deduplicates by SKU+supplier within a cooldown window, and calls the supplier API or EDI endpoint to issue the order.”
}
},
{
“@type”: “Question”,
“name”: “How do you apply seasonal adjustment to a reorder point calculation?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Compute a seasonal index for each SKU by dividing the average demand in a given period by the overall average demand across all periods. Multiply the base EWM forecast by the current period's seasonal index before computing the reorder point. Indices are recalculated periodically (weekly or monthly) from at least one full year of history to capture annual cycles.”
}
}
]
}