Inventory That Actually Adds Up
FIFO stock lots, deliveries that split across batches, and the accept handshake that finally made office and branch numbers agree.
Once everything talked to one server (last post), the next question was the one the whole business actually cares about: how much of each thing do we have, and what did it cost us?
Turns out “how much flour do we have” is a harder question than it looks, because the answer changes price depending on when you bought it.
FIFO, because cost isn’t one number
We buy the same raw material at different prices over time. A sack of flour last month cost less than a sack this week. So “the cost of flour” isn’t a single number — it’s a stack of batches, each with its own price and quantity.
I modelled stock as lots. Every purchase creates a lot: a quantity, a unit cost, and a date. When stock leaves, it’s consumed oldest-first (first in, first out), drawing down lots in order until the quantity is covered.
This matters because it means every delivery and every sale can report what it actually cost us, not a rough average. When you pull from three different-priced lots to fill one order, the system tracks all three.
Deliveries that split
Here’s where it got interesting. The office ships stock to a branch. Say the branch needs 30kg of flour, but our oldest lot only has 20kg left. FIFO says: take 20 from that lot, then 10 from the next.
So a single delivery isn’t one row in the database — it’s one row per lot it consumed, each with its own cost, all tied together by a shared transaction ID. One delivery ticket, several rows underneath, because that’s the honest picture of where the stock came from.
That decision rippled through everything. Every time I read or update a delivery’s status, I have to operate on all the rows sharing that transaction ID at once — never a single row — or a ticket ends up half-shipped. I learned that the hard way and it’s now a rule I follow everywhere.
The accept handshake
The other thing I wanted was for stock to not just teleport. When the office ships, the stock shouldn’t magically appear in the branch’s inventory the instant someone clicks send. It’s on a truck. It might not arrive. It might arrive short.
So a delivery is created in transit. It sits there, owned by nobody’s shelf, until the branch opens their app and confirms “yes, this arrived.” Only then does it flip to accepted, record who accepted it and when, and credit the branch’s own stock.
It’s a tiny thing but it changed the feel of the system. The office can’t inflate a branch’s numbers by clicking a button. The branch confirms reality on their end. Two parties, one handshake, and the total is conserved across the move.
The bug that taught me to distrust “typed” data
One war story from this stretch, because it cost me an afternoon.
My delivery-cost query kept coming back with nulls where there should have been numbers. No error. No crash. Just… blank costs, silently.
The culprit: my database library was doing a case-sensitive lookup on column names, and Postgres had quietly folded my aliased column names to lowercase. So RemainingQty in my code never matched remainingqty from the database, and instead of complaining, it just handed me null.
The fix was one line — use the strongly-typed query that maps case-insensitively. But the lesson stuck: a silent null is so much worse than a loud crash. A crash tells you where to look. A null just poisons a report three screens later and makes you doubt your own math.
By the end of this session the core was real: buy stock into priced lots, ship it out FIFO across batches, and have the branch confirm receipt before it counts. For the first time the office and a branch could look at the same item and see the same number.
Next up: the part that isn’t allowed to depend on any of this working — the counter.