One stock pool, four sales channels: a reservation ledger instead of a computed available

Multichannel overselling is not a data problem. It is a concurrency problem that people keep trying to solve with better arithmetic.

The usual implementation computes availability on read:

const available = onHand - allocated - safetyBuffer;
if (requested <= available) accept(order);
else reject(order);

Every term in that expression is correct and the code is still wrong, because between the read and the write another channel can take the same units. Shopify and Amazon and a marketplace and your own storefront all pull the same stock feed, and a pull interval is just a window of guaranteed races with a longer name.

The fix is to stop computing availability and start reserving it, in a way where the reservation itself is the atomic operation.

Model the pool as movements, not as a number

create table stock_movement (
  id           bigint generated always as identity primary key,
  sku          text        not null,
  site_code    text        not null,      -- which of the physical sites holds it
  kind         text        not null,      -- receive | reserve | commit | release | adjust | ship
  quantity     integer     not null,      -- signed
  channel      text,
  external_ref text,                      -- channel order id, unique per reservation
  caused_by    text        not null,      -- order id, receipt id, count id
  expires_at   timestamptz,               -- reservations only; null once committed or released
  created_at   timestamptz not null default now()
);
create unique index uq_reserve on stock_movement (sku, channel, external_ref) where kind = 'reserve';
create index on stock_movement (sku, site_code, created_at);

on_hand and available become derived, and the partial unique index on (sku, channel, external_ref) for reservations is what makes a retried webhook harmless. Insert the same reservation twice and the second insert fails, which you then treat as success, because the units are held either way.

The reservation is a guarded write

The check and the write have to be one statement, or the race survives.

with held as (
  select coalesce(sum(quantity), 0) as reserved
    from stock_movement
   where sku = $1 and site_code = $2 and kind = 'reserve'
     and expires_at > now()
), onhand as (
  select coalesce(sum(quantity), 0) as units
    from stock_movement
   where sku = $1 and site_code = $2 and kind in ('receive','adjust','commit','ship')
)
insert into stock_movement (sku, site_code, kind, quantity, channel, external_ref, caused_by, expires_at)
select $1, $2, 'reserve', -$3, $4, $5, $6, now() + $7::interval
  from onhand, held
 where onhand.units + held.reserved >= $3          -- held.reserved is negative
returning id;

Zero rows means somebody else got there first, and you reject the order. One row means the units are yours. There is no window, because Postgres evaluates the where against the snapshot the insert takes its lock on, and under read committed the two statements above run inside one transaction with the pool row locked explicitly if you need serializable behavior across multiple SKUs in one order.

For multi-line orders, lock in a deterministic order. Sort by SKU before touching any of them, or two carts containing the same two products in different orders will deadlock, and they will do it during a promotion when you least want to be reading deadlock logs.

Reservations must expire

A reservation without a TTL is a leak. Payment failures, abandoned checkouts, marketplace orders that never sync, and cancelled lines all hold stock forever unless you say otherwise.

update stock_movement
   set kind = 'release', quantity = -quantity, expires_at = null
 where kind = 'reserve' and expires_at <= now();

Run it as a sweep, and set the TTL per channel rather than globally. A storefront checkout that times out in fifteen minutes and a marketplace order that takes hours to confirm are not the same promise, and using one number for both means either overselling or holding inventory that is not really sold.

The release row is written rather than deleted. That is deliberate. When a seller asks why the site showed stock at 9am and not at 9:04, the answer has to be in the table.

Commit on ship, not on pick

The lifecycle is reserve → commit → ship, and the temptation is to commit at pick time. Do not commit at pick, because picks fail. A short pick, a damaged unit pulled off the line, a barcode that does not match the carton label: all three are routine, and if the commit already happened you now have a phantom sale and a stock number that will not reconcile at the end of the month.

Commit when the parcel is handed to a carrier and the units have physically left the pool. Until then the stock is held, visible, and reversible.

Publish a smaller number than you have

The feed you push to channels should not be on_hand. It should be:

publishable = onHand
  - activeReservations
  - unitsInPickWavesNotYetShipped
  - quarantineHold
  - channelSafetyBuffer(channel)

quarantineHold is the term everyone forgets, and it is the one that turns an oversell into a cancelled order. Goods received but not yet inspected, goods awaiting a compliance check, goods photographed as damaged pending a carrier claim: all are physically present and none are sellable. If your pool query counts them, you will sell them.

The per-channel buffer is not a fudge factor. It is the price of the sync interval, and it should be tuned from your own oversell history rather than copied from a blog post.

Reconcile against the floor, on a schedule

No amount of correct ledger logic survives contact with a physical count. The reconciliation job is where the two meet:

insert into stock_movement (sku, site_code, kind, quantity, caused_by)
select c.sku, c.site_code, 'adjust',
       c.counted_units - l.book_units,
       'count:' || c.count_id
  from cycle_count c
  join ledger_position l using (sku, site_code)
 where c.counted_units <> l.book_units;

Write adjustments as movements with a caused_by pointing at the count, and you keep the audit chain intact. Overwriting a quantity column destroys it, and afterwards nobody can tell whether the book was wrong or the count was.

Expect variance. Expect it to cluster on a handful of SKUs, usually the ones with similar barcodes or the ones two suppliers ship in near-identical cartons. That pattern is the useful output, and it is only visible if the ledger kept every movement.

This is the shape we use across the three Chinese sites FulfillNexa by SBT (fulfillnexa.com) operates from, Shenzhen at 3,000 m², Suzhou at 13,000 m² and Dongguan at 8,000 m², because a SKU sitting in one of those buildings is not the same sellable unit as the same SKU in another, and a pool that ignores site_code will happily oversell stock that is three hundred kilometers from the order that claims it.