Inventory

Stock is not a stored number on a variant. It is summed from the active locations that hold it, at read time.

What is stored is the history: every change is a StockMovement row.

The two halves belong together. A number you can only read is a number nobody can explain, and a history nobody sums is a number nobody trusts. Keeping the count derived and the history append-only means every quantity in the system has a chain of rows behind it that adds up to exactly that quantity.

What a movement records

A movement is not just a delta. Each row carries where the count was, what changed, where it ended, and who did it:

variantId
locationId
delta              signed: negative = out, positive = in
previousQuantity
newQuantity
reason
actorType          system | user | api | webhook
actorId
actorName
referenceType      order | transfer | return | variant | …
referenceId
notes

The referenceType and referenceId pair is what turns the ledger into an answerable thing. "Why is this variant down three units?" is a query against the movements for that variant, not an inference from timestamps.

The movement constraint

The database refuses a row whose three quantities disagree:

CHECK ("previousQuantity" + "delta" = "newQuantity" AND "delta" <> 0)

The delta <> 0 half matters as much as the arithmetic: a no-op movement is a symptom of a bug, not a harmless write, so it is rejected rather than recorded.

litestore_guard_stock_movement_write adds the rest of the rule. It runs before every insert, update and delete on StockMovement, and:

• raises on an UPDATE or a DELETE, because the ledger is append-only — a correction is a new movement
• raises when there is no Inventory row for that location and variant
• raises when newQuantity does not equal the current Inventory.quantity for that location and variant

The last one is the strict one. A movement is not allowed to claim an ending quantity that the inventory row does not actually hold. A writer that decrements stock and then records a different number in the ledger is refused at the point of the write, rather than discovered later as an unexplainable discrepancy.

Concurrency

Two shoppers checking out the last unit at the same moment are serialised by a SELECT … FOR UPDATE row lock in lib/inventory.ts.

The read that decides whether stock is available and the write that consumes it happen inside the same transaction under that lock, so the decision cannot be made against a stale count.

The ledger entry is written in that same transaction as the change itself, and the resulting quantity comes from the write rather than from a re-read. That is why the before and after on a movement are always exact: nothing observes the row again between the change and the record of it.

test/integration/inventory-transactional.test.ts exercises this against a real database, because the behaviour under test is the database's, not the application's — see Integration Tests.

Absolute edits

An operator correcting a count is a different operation from a sale, and it needs the same lock for a different reason.

When the admin sets a quantity to an absolute value, the row is locked before the current quantity is read, and the implied delta is written through the same canonical path that orders, refunds and returns use.

Without the lock, the operator's absolute "set to 120" silently becomes a relative change applied on top of whatever happened in between:

read 100
    ↓
concurrent sale −5
    ↓
apply +20
    ↓
115, not 120

The one tool operators reach for to fix drift would itself introduce it.

Routing the edit through the canonical chokepoint also means a manual correction appears in the ledger like everything else. Manual edits and imports are not invisible.

Reservations

Checkout decrements at the start of the session and holds for the payable window plus a grace period. Checkout covers the timing and the oversell it was written to prevent.

When stock is allowed to go negative

The guarded decrement rejects a decrement that exceeds stock. There is exactly one path that opts out of that guard, and it exists for a specific reason.

Once a payment has been captured, an order must be created. Refusing to record it because stock ran out in the meantime would leave a charged customer with no order at all, which is worse than a negative count.

So the already-charged path drives the location negative instead of throwing, and the order is flagged with needsReview for a human to reconcile. The oversell becomes a visible item of work rather than a rejected order or a lost charge.

Locations

A location carries independent flags for whether it fulfils online orders and whether it offers pickup, plus a priority.

Those are two separate capabilities, not two points on one scale. A shop floor may allow collection without being a source for online orders; a warehouse may be the opposite; a location can be both, or neither.

Stock sums only over active locations. Deactivating a location therefore removes its units from what the storefront can sell, without deleting the movement history behind them.

That is the difference between taking a location out of service and pretending it never existed. The units stop being sellable immediately; the record of everything that ever moved through that location stays intact.

Priority orders the locations, higher being preferred, and is what picks a default when nothing else has chosen one — the highest-priority active location, with one created only if none exist at all.

Where a new stock write goes

If your code changes a quantity, it goes through the transactional inventory primitive, in the same transaction as whatever caused the change. It should never write Inventory directly and record a movement separately.

If your code needs to correct a past movement, it writes a new one. The database will refuse the edit.

If your code has already taken money and cannot roll back, it is the one case for the unguarded decrement — and it must flag the order for review rather than pretend the numbers reconcile.

The question worth asking before any stock write is not "does this leave the right number?" It is:

If someone sums the ledger for this variant tomorrow, will it explain the number they see?

Related