Multi-Location Kitchen Inventory System
A real-time inventory ledger system managing stock across multiple camp locations with idempotent commit and reverse APIs.
A camp kitchen operation spanning multiple locations needed a system to manage ingredient stock in real time — tracking what was available at each location, deducting stock when meals were prepared, handling the complexity of recipe-based material requirements, and maintaining a full audit trail for end-of-event reconciliation. The core engineering challenge was ensuring stock accuracy under concurrent requests from multiple kitchen terminals hitting the same location simultaneously.
The kitchen team was managing stock manually across multiple camp locations — recording what went where on paper, calculating ingredient requirements for batches of meals by hand, and reconciling physical counts against records at the end of each camp. This meant discrepancies were only discovered after the fact, stock ran out unexpectedly because consumption wasn't tracked in real time, and there was no way to know whether a deduction had actually been committed if a terminal lost connectivity mid-transaction.
Designed and built the complete system — from the ledger data model and concurrency strategy to the idempotency layer and the recipe BOM engine.
Engineering challenges
Race Conditions on Concurrent Stock Deductions
Multiple kitchen terminals at the same camp location could submit stock deductions simultaneously — both reading the same current stock value, both computing the deduction in application memory, and both writing back incorrect remaining quantities. This classic read-modify-write race condition was causing stock to drift from physical reality.
Wrapped every deduction operation in a database transaction using SELECT FOR UPDATE on the specific location-ingredient row. This pessimistically locks only the targeted row for the duration of the transaction — other locations remain fully available for parallel operations. Concurrent requests for the same location queue behind the lock and see the correct updated value when they proceed.
Idempotent Commit and Reverse APIs
Kitchen terminals lost connectivity mid-request with some regularity — leaving the system uncertain whether a deduction had been committed. Retry logic on the terminal side was causing double-deductions that threw off stock counts.
Implemented an idempotency key system using client-generated UUID tokens. The server attempts to insert the token into an idempotency_keys table with a UNIQUE constraint before processing any deduction. If the insert succeeds, the request is new — proceed and commit. If it fails with a constraint violation, the request is a duplicate — return the stored response from the original commit without re-executing the deduction. Database constraint enforcement ensures this is safe under concurrent retries at the same millisecond.
Recipe BOM and Ingredient Deduction
Meals are made from recipes with multiple ingredients in precise quantities. When a camp requests 50 rotis, the system needs to calculate that this requires a specific quantity of wheat flour, oil, and salt — and deduct each ingredient from the correct location's stock atomically.
Built a BOM (Bill of Materials) engine that expands a meal request into its ingredient components using the recipe definition. All ingredient deductions for a single meal batch are executed inside a single transaction — either all succeed or all roll back. This prevents partial deductions where some ingredients are committed but others fail.
Append-Only Ledger for Audit Trail
The operations team needed to reconcile physical stock with system records at the end of each camp event. Direct column updates to a stock quantity field made this impossible — there was no history of what happened.
Designed the ledger as an append-only event log. Stock levels are never stored as mutable integers — they are computed as the sum of all ledger entries for a location. Every commit, reverse, and adjustment is a new immutable row with timestamp, operator, and reference data. Reversals write an offsetting credit entry — the original debit remains untouched for audit purposes.
Architecture
What we built
Per-Location Stock Ledger
Each camp location maintains its own inventory ledger. Stock levels are computed from ledger history — not stored as mutable values — giving full traceability of every movement.
Idempotent Commit API
Stock deductions are safe to retry. Each request carries a client-generated idempotency key. Duplicate submissions are detected by database constraint and return the original response without re-executing.
Idempotent Reverse API
Committed deductions can be reversed safely. Reversals write an offsetting ledger entry — the original debit is never deleted, preserving the full audit trail.
Recipe BOM Engine
Meal requests are expanded into ingredient lists using recipe definitions. All ingredient deductions execute atomically — partial deductions on a failed batch are rolled back completely.
Concurrent Terminal Support
Multiple kitchen terminals at the same location operate simultaneously. Pessimistic row locking ensures stock accuracy without any terminal seeing inconsistent values.
End-of-Event Reconciliation Report
Complete audit report of all stock movements per location — every deduction, reversal, and adjustment with operator and timestamp — for physical count reconciliation.
Technical highlights
Pessimistic row locking with SELECT FOR UPDATE — locks only the targeted location-ingredient row, other locations remain fully parallel
Idempotency via UNIQUE constraint trapping — safe under concurrent retries at the same millisecond without application-level IF checks
Append-only ledger design — stock balance computed by SUM aggregation, full debit/credit history preserved
Atomic BOM expansion — all ingredient deductions for a meal batch commit or roll back together in a single transaction
Reversal via offsetting credit entries — original debit immutable, audit trail always complete
ACID compliance enforced at the transaction level — no stock drift under any failure scenario
Add screenshots here — product gallery, admin panel, or key screens.
The impact
Multiple
Camp locations managed
Idempotent
Commit and reverse APIs
ACID
Transaction compliance
Zero
Stock drift under concurrent load
Full
Audit trail for reconciliation
Business outcomes
Real-Time Stock Visibility
Operations team sees current stock at every location in real time — no more manual count requests to find out what is available.
Accurate End-of-Event Reconciliation
Physical stock counts now consistently match system records because every movement is logged immutably and reversals are handled correctly.
Reliable Connectivity Handling
Terminal connectivity drops no longer cause double-deductions — idempotent APIs make retries completely safe.
Lessons learned
Application-level IF EXISTS checks for idempotency create race conditions under concurrent load — database UNIQUE constraints are the only safe approach
Append-only ledger design is more work upfront but makes debugging, reconciliation, and auditing dramatically easier in production
Pessimistic locking scope matters — locking at the wrong granularity either causes unnecessary contention or fails to prevent the race condition