Inventory tracking is often perceived as a simple database problem: save the quantity for a particular product in the database, and update the value every time when the stock is physically moved. When the matter concerns inventory tracking in an enterprise setting, things are complicated by the fact that inventory movements are distributed across different subsystems, transactions might come late, duplicates might happen and so on.
The article covers a series of concepts that can make building reliable inventory systems easier.
Represent Inventory Movements As Events
A naïve approach to inventory management is to save current quantity of a product along with some information about it’s properties. What many designs miss is the fact that current quantity figures result from supply, relocation, reservations, shipment, returns and other movements of goods. Being able to reconstruct event history is critical in order to answer questions such as what caused a particular change in inventory, when did it happen and who is responsible if information in the database conflicts with physical inventory.
A typical event record would have the following shape:
{
"event_id": "evt_1042",
"sku": "SKU-1001",
"event_type": "RECEIPT",
"quantity": 25,
"location_id": "WH-A-01",
"timestamp": "10/15/2026, 9:30:00 AM",
"reference_id": "PO-2048"
}
The format is arbitrary at this point, more involved implementations would have to define quantity units, transaction status, validation rules and so on.
An ability to trace raw material movements helps answer questions about the inventory state at various points in the past.
Track Physical Stock Quantity Separately From Availability
One has to be mindful that while physical stock quantity and available quantity usually track together, they are not the same.
For example, a storage location contains 100 physical units of a product, but 20 of them are already reserved for outstanding orders, which makes 80 units available for new orders. The formula here is simple:
available_quantity = on_hand_quantity - reserved_quantity
More involved systems will have to account for damaged goods, stocked items, quality inspection freezes and goods in transit.
These records help prevent general ledger entries for goods that still have outstanding reservations.
The exact set of properties depends on the business logic, the important thing is that these invariants are enforced in order to prevent erroneous reservations.
Design Inventory Transactions To Handle Duplicates
Many production systems use external scanners, RFID-readers, barcode scanners, messaging systems and human operators to update inventory records in a database. All these methods can be affected by network issues and operator errors. When a receipt transaction is processed twice because the message was resent by a messaging system, the database will be credited with 50 units instead of 25.
An inventory-aware system should be able to process the same transaction multiple times without introducing inconsistencies. One simple way of doing that is to design transactions so that the same event cannot be processed more than once. Here’s how a relational table for inventory events could look like:
CREATE TABLE inventory_events (
event_id TEXT PRIMARY KEY,
sku TEXT NOT NULL,
location_id TEXT NOT NULL,
event_type TEXT NOT NULL,
quantity INTEGER NOT NULL,
created_at TIMESTAMP NOT NULL
);
In practice, an INSERT followed by an inventory update has to be wrapped into a single transaction, for which database-level guarantees are insufficient unless the primary key also tracks changes to inventory records. More involved systems will have to handle concurrency and recovery from partial failures.
Make Inventory Events Traceable To Other Records And Documents
Inventory transactions are often connected with purchase orders, shipment manifests and other records that prove that a given movement of goods happened. Such connections can be implemented in different ways, namely by linking transaction events with external document identifiers:
{
"event_id": "evt_1123",
"sku": "SKU-2011",
"event_type": "ISSUE",
"quantity": 5,
"location_id": "WH-B-32",
"related_event": "rcpt_9283",
"timestamp": "10/15/2026, 9:30:00 AM"
}
By following the related_event ID, one will be able to determine that the issue transaction involved goods receipted from a particular transport provider. When implemented in software, such connections allow software applications to implement self-service analytics that answer questions such as which transaction reduced inventory for a certain product, where did a certain item came from and so on.
When traceability is desired at the individual goods level, the system will have to support serial numbers and genealogy records.
Consider Reconciliation A First-Class Concept
In practice any inventory tracking system will feature inventory discrepancies, stemming from processing errors, goods inspection failures, delayed transactions, etc.
Such discrepancies can range from simple differences between scanned quantity and physical count to complex disagreements with an external inventory management system. A general procedure for reconciliation consists of several simple steps:
Detect discrepancy between current quantity in the database and evidence from physical count or another system.
Determine what inventory groups, items and transactions are affected.
For all affected transactions, review the event history.
Based on event history, identify the reason for the disagreement, correct the records.
Document the reconciliation procedure to detect similar issues in the future.
In many cases, such adjustments will require special adjustment transactions. The fact that they were executed should also be tracked as a transaction event. This helps build an audit trail of why and when a particular change has been carried out.
Use Inventory Field Ownership To Prevent Confusion Between Integrated Systems
It is common for ERP systems to share inventory-related information with order management systems and various warehouses.
Each transaction such as order creation or goods receipt updates the database with new information. Similar transactions in different systems however might represent different aspects of business logic. Whereas one system will update the database to reflect that an order is shipped, another will merely credit the inventory when goods are actually shipped from a warehouse. Since those are fundamentally different events, the systems must clearly distinguish between them.
When designing a mapping between external systems and the target database one has to clearly state:
For each table field, which of the external systems are allowed to write into it.
For each event type, which data fields should be updated.
How failed attempts at writing should be retried.
How duplicate records should be handled.
How systems will detect and resolve field-ownership conflicts.
How reconciliation between systems should be implemented if they use different conventions for the same set of fields.
Clear field ownership specification can be more helpful than generic messaging patterns.
Use Operational Metrics As Signals For Further Optimization
A reliable inventory tracking system is not a system that provides current information about inventory, but rather a system that ensures that inventory operations are reliable. As a result, an inventory-aware system should track metrics around inventory record accuracy, event processing duplication rate, inventory reconciliation frequency and others.
Such metrics can become early warning signals if certain processes are not followed.
Tracking Inventory With Confidence Is A Manageable Problem
All systems that track inventory should be built with the following concepts in mind:
Physical stock and available inventory are not the same.
Inventory transactions require event history tracking and replayability, making it easier to reason about inventory at any given moment.
Inventory systems should be able to withstand duplicate record inserts without getting into an inconsistent state.
Inventory transactions should update related records and documents so that it is possible to follow the lineage of each item.
Inventory reconciliation has to be an explicit process, detectable in the audit trail.
Field ownership has to prevent reconciliation issues between inventory tracking systems and external partners.
With a proper transaction event model in place, inventory reconciliation becomes easier and more accurate. A system that tracks physical stock movements in an event log can reconstruct available quantity with reference to outstanding reservations, shipment records, returned items and other documents.
Ultimately the inventory management question boils down to tracking physical quantity of goods in a certain location using transactions that can be reliably replayed to obtain the current quantity. If that is the case for each inventory entry, investigating a discrepancy is easier.
For a more in-depth overview of inventory tracking, including concepts, technologies and procedures consult with The Inventory Master Website.
Top comments (0)