How do you see a stockout before the shelf is empty?
You see a stockout coming by dividing what is on hand by how fast it is leaving and comparing that with how long resupply takes. We replaced a consumables spreadsheet with a scanned stockroom whose ledger feeds that forecast every four hours and after each issue, with an alert that emails only when an item newly turns critical or warning.
Consumables were tracked in a shared workbook with a minimum, a maximum, an on-hand column and a lead time. We know the shape of it precisely, because the importers we wrote to seed the new system read exactly those columns — one to create the item catalogue, one to back-fill lead times onto items that already existed. Reading the old sheet's columns exactly is the first step in moving a shared workbook onto a real database.
A sheet like that fails in two quiet ways. The on-hand figure drifts, because it is only correct if every person who takes a box remembers to open the file and change it. And the minimum level is a number nobody reads until an item has already reached it — by which point the resupply lead time has been spent rather than planned for.
- Stock counts were maintained by hand, and only when someone remembered
- Nothing connected a minimum level to anybody's attention
- Lead time was recorded somewhere, but nothing computed against it
- The first dependable signal of a shortage was an empty shelf
A stockroom application built around scanning rather than typing. Every unit received takes a licence-plate identifier, and every issue is a scan of that identifier; separately, each item in the catalogue has its own printed barcode label, so pulling an item up on screen is a scan rather than a search. Every event — creation, receipt, issue, move, edit, deletion, count correction, alert — writes a row to one ledger whose columns are the item, the identifier, the quantity change, the user, the time and a note. Eight transaction types share those rows, so the history screen, the forecast and the audit trail all read the same table.
Locations are a controlled list, not a text box. Receiving into or moving to a location that isn't registered is refused, with the instruction to scan a valid one or create it first. That is the difference between a location field and a folk taxonomy a year later.
- Receipts can auto-generate one identifier per unit, each with a random suffix and a database uniqueness constraint behind it
- Issuing stock requires a scanned identifier — the convenient 'just decrement the count' path exists in the code and is deliberately refused
- A scan against an unknown or already-consumed identifier fails and emails an administrator rather than failing quietly
- Physical counts write back through the same ledger: shortfalls consume the oldest stock first, overages open a new adjustment identifier at the default location
- Deleting an item is a soft delete, and the function re-checks the caller's leadership or admin flag in the database rather than trusting page-level access
- Item labels are generated with every user-entered field hex-escaped into the printer language, and the bar module width recalculated from encoded length so the code stays scannable
Why forecast a stockout date instead of a minimum level?
A minimum level answers 'are we low', which is the wrong question. The one worth answering is 'will we run out before a replacement order can arrive', and that takes three inputs: what is on hand, how fast it is leaving, and how long resupply takes.
Consumption is a recency-weighted blend of the 30-, 90- and 365-day rates, weighted toward the recent and smoothed by the history. Each rate is divided by the shorter of its own window and the item's days since first stock movement, so an item that sat in the catalogue for months before its first receipt doesn't read as slow-moving. Stock written off at a count is treated as consumption too — material that left the building without being scanned is still demand, and excluding it would flatter every forecast. No model is involved: this is usage forecasting done with arithmetic a buyer can check.
The result is a projected stockout date, compared against lead time plus a safety buffer of twenty per cent of lead time or three days, whichever is larger. Below minimum, or projected to run out inside the lead time, is critical: an order placed today would arrive too late. Inside the safety buffer is a warning. Inside a further two-week visibility horizon is a watch — which appears on the dashboard and is never emailed.
- Items with no minimum are excluded entirely; 'order as needed' is a legitimate policy and forecasting it produces noise
- Every row carries a confidence grade derived from how many usage events the item has recorded in ninety days, so a projection built on one event is labelled as one
- Trend compares the last thirty days against the preceding sixty and is flagged at plus or minus twenty-five per cent
- Lead times entered in days, weeks or months are normalised to days before anything is compared against them
- The suggested order quantity fills to the configured maximum where that sits above what's on hand, and otherwise covers lead time plus thirty days of projected usage
Minimum level or projected stockout date
| Input | Minimum level on a spreadsheet | Projected stockout date |
|---|
| On hand | Typed in by whoever remembers | Follows from scanned receipts and issues |
|---|
| Rate of use | Not recorded | Recency-weighted blend of the 30-, 90- and 365-day rates |
|---|
| Lead time | Recorded, never computed against | Normalised to days and compared with the stockout date |
|---|
| Stock written off at a count | No forecast to count it in | Counted as consumption |
|---|
| When somebody hears | When the shelf is empty | A critical or warning email, sent once |
|---|
Sending the alert exactly once
The background service wakes every four hours. The same check also runs inline immediately after any issue or completed count, so an alert can follow the scan that caused it by seconds instead of waiting for the next cycle.
Which makes not spamming people the real engineering problem. Three things guard it. The alert is edge-triggered — it fires for items that have newly entered a critical or warning state, compared against the set outstanding at the last check. It is capped at one email a day, and that cap is a row in the shared ledger rather than a variable in memory, so restarting the application doesn't re-send. And the local file holding the outstanding set is written best-effort, swallowing its own errors; if it is lost, the ledger row is still the backstop. Standing problems belong on the dashboard, not the inbox: an item critical for a week does not email again until something changes, which is the difference between an alert people act on and an alert people filter.
The last guard is about which process is doing the sending. Nothing stops the application being started as more than one process — a second instance, or the standalone worker script that owns some of the same queues — so every background loop coordinates through an exclusive, session-scoped database application lock, the standard guard for batch jobs and the schedules they run on, and a process that cannot take it logs why and doesn't start the loop. This monitor needs one extra turn of that pattern. Its start function spawns a daemon thread and returns immediately, and the helper releases the lock as soon as the launched call returns — which would leave a second process free to take it and start a second monitor moments later. So the monitor is one of only two workers launched with the flag that holds the lock for the life of the process instead, with a keepalive query every minute so the database session never quietly drops it. Without that, two processes would run the same forecast on the same schedule, and duplicate alerts would be prevented only by the daily ledger row — a backstop doing a design's job.
The stockroom is a scanned, ledgered system rather than a maintained document. On-hand follows from what was received and issued, the age of each unit is a recorded fact rather than a guess, and a year of consumption per item is reportable without anyone assembling it. That is the usual shape of software that replaces the spreadsheet a small team fights over.
The number the system can answer is the one the spreadsheet never held. Not how much is on the shelf — how many days of it are left, and whether that is longer or shorter than the time it takes to get more.
Questions about this build
Why does a stockout alert email only once?
Because an alert people get every morning is an alert people filter. The check is edge-triggered: it emails only items that newly entered a critical or warning state since the last run, and it is capped at one email a day by a row in the shared ledger, which survives a restart. An item that stays critical for a week sits on the dashboard and does not email again until something changes.
What do critical, warning and watch mean in the forecast?
Critical is below minimum, or projected to run out inside the supplier lead time, so an order placed today would arrive too late. Warning is inside the safety buffer beyond lead time. Watch is inside a further two-week visibility horizon; it appears on the dashboard and is never emailed. Every row also carries a confidence grade from how many usage events the item recorded in ninety days.
