Syncing Inventory: Incrementing vs. Overwriting

Tracking inventory in the world of digital commerce is crucial for both customers who expect their items delivered and business who need to ensure items do not sit in the warehouse unavailable for purchase. While tracking a simple count appears to be a basic programming task, the reality involves multiple systems from an e-commerce system to an ERP (Enterprise Resource Planning), each reading and writing values at high volumes, it is essential to employ the right approach to ensure data integrity. This type of high-throughput system requires additional care, the correct programming approach, and back-up solutions like safety stock values to avoid major pitfalls such as race conditions.

Scenario: Let's consider a scenario where an ERP is the source of truth for inventory data, handling receiving and fulfillment. In addition to the ERP there are multiple customer touch-points including traditional ecommerce, a mobile application, and in-store functionality which can sell the items in inventory. The ERP can be slow and handles many functions, so to remove the burden of constant inventory checks, the inventory levels are synced to an API-based online inventory system that can serve the customer touchpoints. When an order is placed, the online inventory is decreased and the order is sent to the ERP, processing the order in the ERP decrements the master inventory level. In this dynamic environment, simultaneous updates to inventory quantities can occur, and it becomes crucial to handle these updates in a manner that maintains data accuracy and prevents conflicts.

The first approach may be to simply copy the inventory values from the ERP into the online inventory system. In order to keep things up to date, the values may be copied in a CRON job running every 5 minutes. On first launch this appears to work well and in low-volume systems may continue to perform as required. However, as volumes increase values will quickly become inaccurate leaving gremlins in the code, bugs that are highly situational and almost impossible to reproduce. The problem is that syncing data is never instantaneous and with so many events firing it becomes likely for changes to take place after the inventory value is retrieved, but before it is fully synchronized.

Incrementing or Decrementing Values for Syncing: Instead of overwriting the inventory count with a new number during syncing, a more reliable approach involves incrementing or decrementing the existing value. This method ensures that each inventory update considers the changes made by other processes, reducing the risk of data inconsistencies and race conditions. So, a well-designed inventory API will accept an increment or decrement integer which will be applied to the existing quantity rather than taking the quantity as a direct value. You can see an example API on the Elastic Path docs website.

Incrementing or decrementing values offers several advantages:

1. Data Integrity: By incrementing or decrementing values, existing data remains intact, reducing the risk of data loss or discrepancies during inventory updates.
2. Concurrent Updates: In a dynamic e-commerce environment, multiple systems or users may simultaneously update inventory counts. Incrementing or decrementing values allows for sequential application of updates, preventing conflicts and maintaining the accuracy of stock levels.
3. Audit Trail: Incrementing or decrementing values provides an audit trail of inventory changes. Each transaction or adjustment made to the inventory count can be tracked, enabling better accountability, error analysis, and identification of discrepancies.
4. Granularity: Incrementing or decrementing values allows for granular tracking of individual item changes. This detailed record of added or removed items facilitates better traceability and analysis of inventory fluctuations.
5. Historical Reporting: Incrementing or decrementing values preserves historical data, enabling the generation of accurate reports based on past inventory levels. This information is invaluable for analyzing trends, forecasting demand, and making informed business decisions.

Even taking this approach there is still the potential for syncing delays. Inventory may leave the warehouse, but also be sold before the sync is completed, causing an over-sold situation. To combat this issue, it may be beneficial to access the master inventory record during checkout to verify the inventory levels. However, since this process is slow it should only be done when absolutely necessary, this is the role of Safety Stock Values.

We can track a minimum safe quantity in our online system, if the quantity shown is below this value during checkout the system should verify with the ERP. The easy way to handle this requirement is to set a single value for the entire system. A more robust approach is to store the safety stock value within each inventory entry and calculate the necessary value based on recent sell-through of the specific SKU. This is possible with an API system that allows for custom fields, but can be more difficult with a more rigid ecommerce platform.

While this example is tied directly to ecommerce, the approach can be applied to any distributed system where multiple processes need to read, update, and cache a single value. Choosing an increment/decrement approach allows for better syncing by tracking changes instead of point-in-time values and additional business logic can be created to mitigate issues from syncing delays.