We spent the first week of a project for a pharmaceutical distributor reading spreadsheets. Not writing anything, reading. Forty-odd Excel files, a couple of them so wide you had to scroll for a full second to reach the last used column, and buried in the frozen panes and the colour coding was the entire operating logic of a regulated business. One cell was orange because the batch in it was close to expiry. Nobody had written that rule down anywhere. The orange was the rule.
What they asked for, at the start, was "a proper inventory system". Reasonable. But the first real argument we had, and we had it among ourselves before we had it with the client, was about a single column. Quantity on hand.
The lie in the obvious query
If you have ever built stock for a shop you know the shape of it. There is a products table, each product carries a quantity, something sells and you decrement it. SELECT quantity FROM stock WHERE sku = ? and you are done. That query is perfectly honest for a hardware store. A screw is a screw.
In this domain it is a lie, and not a small one. Two boxes of the same product, identical SKU, identical everything printed on the label, are not interchangeable. One expires in March, one expires next year. One rode inside a validated cold box, the other sat on a loading bay through a Pune afternoon for three hours while somebody hunted for the paperwork. To that SELECT they are simply 200 units. To a regulator they are two different things, and one of them may be worth nothing at all.
So the unit of stock is not the product. It is the batch. Every question the business actually asks, what can I sell today, what must I pull off the shelf, what is this pile worth right now, is a question about batches. The SKU is just a label a batch happens to be wearing.
FEFO, and the picker who argues with the screen
The retail instinct is first in, first out. Oldest received goes first. In pharmaceutical distribution what you want is first expiry, first out, which sounds like the same thing until it isn't. A batch you received last week can expire before one you have had for two months, and it is the expiry date that decides picking order, not the date it arrived. So the pick list has to sort by a clock, not by a receipt log.
SELECT batch_id, expiry_date, qty_available
FROM batch_stock
WHERE sku = $1
AND status = 'sellable'
AND qty_available > 0
ORDER BY expiry_date ASC, received_at ASC
LIMIT 1;On the warehouse floor this stops being abstract. A picker scans a barcode, and the system can tell them they have grabbed the wrong box, because a nearer-expiry batch of the same product is sitting one rack over. And here is what we got wrong, and I mean genuinely wrong, not modestly-wrong-for-the-blog. We made FEFO a hard constraint. The system refused to let anyone confirm a later-expiry batch while an earlier one existed. Clean rule, provably correct, and useless within a day. Sometimes the near-expiry batch is physically behind three pallets. Sometimes a hospital has specifically refused anything with less than a year of shelf life left, which is a real and reasonable thing for a hospital to demand. The floor started keying in fake reasons to get past our beautiful constraint, which is worse than no constraint, because now the data lies and looks compliant doing it. We changed it to a warning plus a logged override reason. Two of us still disagree about whether that override should need a supervisor to sign it off. I think it should. He thinks that just moves the fake reasons up one pay grade.
A recall is a query you can answer, or you can't
The blunt test of whether your data model is right is the recall. A manufacturer flags a batch. You now have to know, quickly, every customer who received so much as a single unit of it. If your stock lives batch-first, that is one query and you have your answer before the kettle boils. If it lives in a quantity column, it is forty spreadsheets, three people, and a week you do not have, and at the end of it you are still not certain you found everyone. There is no partial credit on this. You can either answer in minutes or you cannot answer at all, and the second one is a phone call to a regulator you never want to make.
This is the argument for append-only stock movements over a mutable quantity field, and it is the same argument as the recall really. If every receipt, pick, transfer, adjustment and destruction is its own immutable row, then the history is the truth and the current quantity is just something you add up. You never UPDATE a number and lose what it used to be.
SELECT COALESCE(SUM(qty_delta), 0) AS on_hand
FROM stock_movement
WHERE batch_id = $1;Quantity on hand becomes derived, not stored. A row is written, never changed. When finance and the warehouse disagree about a figure, and they will, you can walk the movements and find the exact one that caused the split, instead of staring at two numbers that are each confidently wrong.
Stock has a clock
Expiry does something to valuation that ordinary inventory never has to deal with. Nothing sold, nothing moved, nobody touched a box, and yet the stock is worth less this morning than it was last night, because a batch expiring in three weeks is not the same asset as a fresh one. The value changes daily on its own. A single mutable quantity has no way to express that, because it has thrown away the one field, the expiry date, that the valuation depends on. Batch-first, the clock is built in. You can price the shelf at any date you like and get an honest answer.
Cold chain: destroyed without anyone moving it
And then there is the one that really breaks the mental model people carry over from ordinary warehousing. A batch can be destroyed without anybody moving it an inch. A temperature excursion in the cold room, a cold box that logged an hour above range in transit, and the units are still sitting there, physically present, fully scannable, and completely dead. Nothing about their location changed. Their status did. That is why status has to be a first-class fact on the batch and not something you infer from where a box is, because location and saleability have quietly stopped agreeing with each other.
We had met a cousin of this on an earlier job for a food-processing manufacturer, where a truck is weighed, sampled and graded before it is allowed to become an invoice. The grade is a quality state stamped on physical goods that no count of them can capture. Cold chain is the same shape of problem wearing a different coat: the number tells you how much, and it is the state that tells you whether the how-much is worth anything.
The migration nobody puts in the quote
Here is the part that ambushes you. You have designed this careful batch-first model, and now you have to load years of history into it from spreadsheets that never had batch identity in the first place. The old files recorded a product and a quantity, sometimes an expiry if whoever typed that row was diligent, and a real batch number almost never. You cannot reconstruct an identity that was simply never captured. It is not hiding in the data. It was never in the data.
So you have to decide what day one looks like, and every option is a bit ugly. We created explicit "unknown-provenance" opening batches for the migrated stock and flagged them loudly, so the first day of the live system reconciled against the last day of the spreadsheets rather than being taken on faith, but the fiction stayed visible for anyone who looked. We did not pretend the old stock had lineage it never had. That mattered more than it sounds, because the alternative, quietly inventing plausible batch numbers to make the import look tidy, would have poisoned the recall query on the exact stock most likely to be old enough to get recalled.
If you take one thing from this: the moment a product can go bad, or go cold, or go out of date, the count of it stops being the interesting number. The batch is the thing that has a history and a clock and a temperature, and the count is just an opinion you form about a batch on a particular morning. Model the opinion and you will be rewriting it inside a year. We were, on the first pass, and we knew better.