One quantity column is a queue

The simplest stock table keeps one quantity per product and location and updates it on every sale, receipt and transfer. It works until a store till, a warehouse scanner and an online order touch the same product at the same moment. Each update waits for the one before it, and at busy times the waiting is what customers notice.

Record moves, derive the balance

Instead, record every change as its own stock move: from one location to another, with the quantity, lot or serial number and the document that caused it. Moves are appended, never edited, so concurrent writers do not fight over the same row.

This is how the inventory module we implement models stock: locations, bins, lots, serial numbers and stock moves, with the on-hand figure derived from them. It also gives you a full history for audits and returns.

Make availability checks cheap

Summing every move on every check would be slow, so frequent questions such as available to promise at a location are served from indexes and maintained summaries that the moves keep up to date. The ledger stays the record; the summary is a fast view of it.

Plan for volume and for the network

As transactions grow, older moves can be partitioned by period so day-to-day queries touch only recent data. For warehouses with an unreliable connection, a local edge server can keep scanners working and replicate to the cloud when the link returns.

Read more