A reorder point is a simple idea that gets expensive fast. Every business wants a default, overrides per category, overrides per product, and then the one outlet by the station that sells four times the water everybody else does.
That is four levels: global, category, product, and outlet × product. Resolving them is trivial for one row. Doing it inside the transaction that records a 500-line POS batch, for a network with a million outlet × product pairs, is not.
Resolve once, store in units
The rules are not consulted when stock moves. They are consulted when the rules change, and the outcome is written onto each stock level row in units, together with which level it came from.
| Field | Meaning |
|---|---|
| reorderPoint | Units, already resolved from the winning rule |
| safetyStock | Units — the floor below which the alert is critical |
| maxLevel | Units — drives suggested reorder quantity and overstock |
| ropSource | Which level won: GLOBAL, CATEGORY, PRODUCT or OUTLET_PRODUCT |
Evaluating stock health then costs a single-row comparison. Recomputing after a rule change is a set-based UPDATE rather than a million round trips — synchronous up to about 25,000 affected rows, and a background job beyond that.
Days of cover needs a window, not a guess
Thresholds can be expressed in days rather than units, which is how most people actually think about fast movers. That needs an average daily sales figure, and the window you choose decides what kind of mistake you make.
- Too short, and one busy Saturday reorders the whole shelf.
- Too long, and a product that has quietly doubled in velocity runs out before the number notices.
- Thirty days is the default: long enough to smooth a weekly cycle, short enough to follow a trend.
Days are converted to units and rounded up, because rounding down towards a stockout is the wrong direction to be wrong in.
Bands, and the hysteresis that stops flapping
OUT on hand <= 0
CRITICAL 0 < on hand <= safety stock
LOW safety stock < on hand <= reorder point
HEALTHY reorder point < on hand <= max
OVERSTOCK on hand > max (optional)An alert resolves only once on-hand clears the reorder point plus a buffer — ten percent by default, at least one unit. Without that, an item hovering at exactly its reorder point opens and closes an alert every time somebody buys one, and the people receiving those notifications stop reading them within a day.