Reorder stock when a SKU drops below its reorder point
Send a purchase request to your purchasing API when MySQL stock hits a reorder point, once per SKU per warehouse a week, skipping SKUs already on order.
- MySQL
- Any REST API
- Slack
- Purchasing
- once per SKU per warehouse per week
The trigger
One read-only SELECT against MySQL. Rowfire polls it for new rows, and the rule fires once per SKU per warehouse per week.
SELECT p.sku,
p.name AS product,
w.code AS warehouse,
s.on_hand,
s.reorder_point,
s.reorder_qty,
p.supplier_id,
(SELECT MAX(m.moved_at)
FROM stock_movements m
WHERE m.product_id = s.product_id
AND m.warehouse_id = s.warehouse_id) AS last_movement_at
FROM stock_levels s
JOIN products p ON p.id = s.product_id
JOIN warehouses w ON w.id = s.warehouse_id
WHERE s.on_hand <= s.reorder_point
AND p.discontinued = 0
AND w.is_test = 0
AND NOT EXISTS (
SELECT 1
FROM purchase_orders po
WHERE po.product_id = s.product_id
AND po.warehouse_id = s.warehouse_id
AND po.status IN ('draft', 'sent', 'confirmed')
)Stock runs down one order at a time. Your app records each sale as a row in
stock_movements and lowers on_hand, but no code runs when a SKU crosses
its reorder point. Purchasing finds out from a weekly export, or from the
first customer who can’t buy it.
The query notices instead. It lists every SKU, per warehouse, whose stock is
at or below its reorder point and has no purchase order already in progress.
The key is sku with warehouse, and the clock is last_movement_at, the
latest stock movement for that pair. Discontinued products and test
warehouses drop out. Table and column names are a sketch; adapt them to your
schema.
The cadence is what makes it usable. While a SKU sits below its reorder
point, every sale moves the clock and the row comes back. The rule fires
once per SKU per warehouse per week, so a busy product sends one request,
not one per order. Once a purchase order exists, the NOT EXISTS filter
takes the row out on its own.
The REST API side
Add your purchasing or ERP system as an integration: its base URL, how it authenticates, and one action for the call that opens a purchase request. The action is data, not code, and the body is a template over the row:
{
"sku": "{{ sku }}",
"warehouse": "{{ warehouse }}",
"quantity": "{{ reorder_qty }}",
"supplier_id": "{{ supplier_id }}",
"note": "On hand {{ on_hand }}, reorder point {{ reorder_point }}"
}
Run it in shadow first. Each request is rendered and recorded without being sent, so the buyer can check the quantities before anything reaches the supplier side.
A Slack note for the buyers
Not every team wants requests opened for them. Add a second rule on the same
trigger with a Slack action that posts to #purchasing:
{{ product }} ({{ sku }}) is at {{ on_hand }} in {{ warehouse }}, reorder point {{ reorder_point }}. Suggested order: {{ reorder_qty }}.
Each rule keeps its own count, so the message can go out daily while the API call stays weekly, or the message can be live while the API call is still in shadow.
For another order that needs a person, see open a ticket for orders paid but not fulfilled. For a second REST API on MySQL, see send flagged orders to a fraud service. Or try a trigger on the live demo.
Questions
Won't every sale of a low SKU send another request?
Each sale moves the clock, so the row comes back on the next poll, but the rule fires at most once per SKU per warehouse per week. The key is sku and warehouse together, so two warehouses running low are two requests.
Our stock tables store local time. Is that a problem?
Rowfire reads MySQL in UTC, and a DATETIME with no zone is read as UTC. If yours holds local wall-clock time, convert the clock in the query, for example CONVERT_TZ(m.moved_at, 'Europe/Cairo', '+00:00').
Can Rowfire write the purchase order into our database?
No. It is read-only and never writes to the database it reads. It calls your purchasing system's API, and that system creates the order the way it already does.
What will a backtest show for this trigger?
It evaluates rows as they are now, so a SKU that ran low in the past and has since been restocked is invisible. Treat the count as a floor, then watch the rule in shadow before it goes live.