Stock, replenishment and documents per article on one page

The situation

The project folder does not say what this solved or what people did before. No README, no brief, no ticket references; commit messages are almost all “update” or “add new features”. Reconstructing a story from that would be invention.

Indirect evidence, and no more: the source data sits in roughly 25 separate warehouse views plus a separate product information system, and the folder contains a formal request to the data team for further fields. So the scope plausibly grew field by field as users asked for things.

The approach

A daily batch runs an hour after the upstream warehouse layer refreshes. One large SQL query — roughly thirty chained sub-queries — assembles per article: balance per storage location, purchase orders and lead times, forecast, production and batch expiry, sales and customer concentration, consumption and packaging.

The result is flattened into one document per article and country, so that orders and sales attach to the receiving market rather than being pooled globally. Each document is embedded and written to a search index; documents not rewritten in a run are deleted afterwards — an article whose stock fell to zero disappears rather than lingering.

A second index holds product content from the product information system, joined by article number. Everything the user sees — filtering, alert rules, working-capital tiles, ABC/XYZ classification, CSV export — runs in the browser.

Where it could have gone the other way

Stock balances are derived from the movement log instead of the ready-made inventory view.

The obvious route was the warehouse’s snapshot view: one row per article and location, already aggregated. It was used first, then deliberately dropped — because that view stops writing rows once a balance hits zero. The last non-zero value therefore keeps surfacing as “current” forever. A code comment records an observed case: a 91-day-old balance reported as live stock.

The replacement reads the movement log and takes the latest row per article, location and category. More expensive — but it catches exactly the movement that brings a balance to zero.

The same judgement recurs elsewhere: the join from articles to catalogue products deliberately has no fallback to description matching. That would have raised the match rate — and produced plausible cross-family mismatches.

The result

Around 334,000 documents in the index, assembled from roughly 25 warehouse views. About three months of work, 60 commits across 21 working days, one person — in bursts of four to nine commits with week-long gaps between them, so visibly alongside other duties.

14 alert rules flag articles needing attention: running low, overstocked, not moving, near expiry, waiting on an overdue supplier order.

Deliberately bounded: read-only. Nothing is written back to SAP or the product system; requisitions, stock moves and disposals still happen by hand in the source systems. Once a day, no history — not a real-time availability check, and because the index is overwritten each run the system cannot say what changed since yesterday.

Stated openly: alert rules run in the browser when somebody searches — nobody is notified. Search returns at most 500 rows, export 10,000, and filtering runs over that slice; the code flags this itself as something that belongs server-side. And there are no tests: every change is verified by hand.