The problem
Multi-location businesses that hold perishable or batch-tracked stock — pharmacies, clinics, food distributors, specialty retail — almost always run on a spreadsheet per location. Nobody can see total stock across sites, nobody knows what's about to expire until it already has, and a transfer between two branches is a phone call and a hope. The cost isn't dramatic; it's a slow bleed of written-off stock and emergency reorders.
What we built
A multi-location inventory system built around batches rather than SKUs — because for anyone tracking expiry, the batch is the unit that matters.
- Batch-level tracking. Every unit carries its lot number, expiry date, cost, and location. Stock isn't a number; it's a set of dated batches.
- Expiry intelligence. A rolling 90-day expiry horizon per location, with configurable alert thresholds. FEFO (first-expired-first-out) picking suggested by default.
- Cross-location visibility. One view of every branch. Transfers are a two-click flow with a full audit trail on both sides.
- Movement ledger. Every change — receipt, sale, transfer, adjustment, write-off — is an immutable, attributed row. Current stock is derived from the ledger, never edited directly. You can always answer "how did we get to this number."
- Reorder logic. Per-location reorder points with lead-time awareness.
- Role-based access. Branch staff see their branch. Managers see the group.
Architecture
A React + Vite single-page client (TanStack Query and Table) talks to a thin Express API where every route does one thing: validate, call a single domain function, serialize. Postgres via Drizzle ORM. The core is an append-only ledger_entries table with signed quantities — current stock for any (item, location) is simply SUM(quantity), computed live on every read. There is no mutable stock counter anywhere in the system, and therefore no cache that can drift out of sync with the movements that produced it.
Engineering decisions
Why an append-only ledger instead of a mutable quantity column? Because inventory disputes are the entire reason these systems get replaced. A mutable column can't tell you who changed what. A ledger can, forever, at effectively no cost.
Why Postgres over a document store? Batch, location, and movement are deeply relational, and expiry reporting is a set of range queries. This is exactly the shape SQL is good at.
Why derive stock on read instead of storing a running total? Because a stored total is one more thing that can silently disagree with the ledger. Computing SUM(quantity) on read means the number can never contradict the movements behind it — and with signed quantities and the right indexes, those sums stay fast at this scale, so correctness costs nothing. If a far larger dataset ever demanded it, precomputation is a documented next step, not a starting assumption.
What it does, measured
- Expiry horizon across all locations renders in Benchmark: measure with seeded 50k-movement dataset
- Full movement history for any batch, retrievable in one query
- Handles Benchmark: SKU count SKUs across Benchmark: location count locations in the seeded demo environment
Adaptable for: pharmacy and clinic supply, food and beverage distribution, cosmetics, chemicals, any regulated stock with a shelf life.