Stock reservation engine for sales and warehouse documents

Problem

A stock reservation is a promise about goods that often have not arrived yet. The reservation is made against a pending order while the user can still change that order, cancel it or close it by hand. The hard part is not reserving: it is releasing at the right moment, because a reservation that is never released blocks stock that is actually available, and one released too early leaves an order unfulfilled.

On top of that, several server tasks touch the same rows at the same time.

Solution

The engine covers the whole cycle, not just the creation of the reservation. When the goods from the order arrive, it assigns batches automatically to the reservations that were waiting. When the document is cancelled, it deletes the related reservations. When someone closes an order or a transfer by hand, the effect has to be the same as if it had been fulfilled: what was reserved becomes free.

Non-inventory items follow their own path, because there is no stock to reserve and the document asks for it anyway.

Concurrency is the delicate part. Two server tasks walking the same reservations in different order each end up waiting for the lock the other one holds, and the database engine resolves that deadlock by aborting one of them. The loser discards the work of its transaction, so catching the error is not enough: access order has to be fixed, transactions kept to what really must be atomic, and the process left in a state it can be retried from without duplicating reservations.

Architecture

Why it is built this way

Releasing is the main case, not the exception. Every path by which a document stops needing stock (cancelled, closed by hand, fulfilled) fires its own release.

When goods arrive, batches are assigned automatically to the reservations that were waiting. The user does not have to go back into the order.

Non-inventory items follow their own path. They have no stock and still appear on documents, so reserving them means nothing.

With concurrency, catching the error is not enough. I fixed the order in which rows are walked and shortened transactions to what genuinely has to be atomic, because blindly retrying something that just died in a deadlock tends to duplicate reservations.

Result

This is the module I have come back to most: close to 400 commits spread between the shared core and the rollouts that use it.