Multi-Warehouse Inventory and Fulfilment Logic

One stock number breaks the moment you ship from more than one warehouse. Multi-warehouse inventory needs stock per location, an allocation rule that picks which warehouse fills each order line, atomic reservation so two locations are never both promised the last unit, and split shipments and transfers. Model stock by location, allocate inside the order transaction, and test that the total promised never exceeds the total on hand.

If you would rather have it built and tested for you, see how RAITHub would build this below.

Why does one stock number stop working across warehouses?

Because "do we have it" and "where do we ship it from" become two different questions. With one warehouse, available stock is a single number. With several, an order might be fully in one location, split across two, or stuck because the only stock is far from the customer. A single aggregate number hides all of that: it says ten in stock when it is seven in one warehouse and three in another, and the system still has to decide which to use.

Problem	With one stock number	What multi-warehouse needs
Availability	Total count across everywhere	Available per location, and a derived total for display
Allocation	Not a question	A rule: nearest, fewest splits, or cost-based
Oversell	One row to guard	Reserve against a specific location atomically
Fulfilment	One shipment	Split shipments and inter-warehouse transfers

What does the data model look like?

Stock belongs to a (product variant, location) pair, not to the product. Availability at a location is on-hand minus what is already reserved there. The order's allocation records which location fills each line, so a split order is just several allocations.

CREATE TABLE warehouses (
  id    bigserial PRIMARY KEY,
  name  text NOT NULL,
  region text,                    -- used by the allocation rule
  active boolean NOT NULL DEFAULT true
);

CREATE TABLE stock_levels (
  variant_id   bigint NOT NULL REFERENCES product_variants(id),
  warehouse_id bigint NOT NULL REFERENCES warehouses(id),
  on_hand      integer NOT NULL DEFAULT 0 CHECK (on_hand >= 0),
  reserved     integer NOT NULL DEFAULT 0 CHECK (reserved >= 0),
  PRIMARY KEY (variant_id, warehouse_id)
);

-- Which location fills which order line (a split order has several rows).
CREATE TABLE allocations (
  id            bigserial PRIMARY KEY,
  order_line_id bigint NOT NULL REFERENCES order_lines(id),
  warehouse_id  bigint NOT NULL REFERENCES warehouses(id),
  qty           integer NOT NULL CHECK (qty > 0),
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- Available at a location = on_hand - reserved. Total for display = sum across locations.

Keeping reserved separate from on_hand lets you promise stock to an order before it physically ships, without pretending the unit has left the shelf. The same reservation discipline that stops single-warehouse overselling applies here, covered in preventing overselling with a reservation inside the order transaction.

How does allocation pick a warehouse?

Decide the rule before you build, because it changes the maths. Three common rules, often combined:

• Nearest: fill from the warehouse closest to the customer, to cut delivery time and cost. Needs region or distance data.
• Fewest splits: prefer a single warehouse that can fill the whole order, because two parcels cost more and annoy customers.
• Stock-balancing or cost-based: clear slow-moving stock, or minimise total shipping cost across the order.

Whatever the rule, allocation runs as a pure function of the order and current availability, so the same inputs always produce the same plan, which is what makes it testable. If no single location can fill a line, the plan splits it across locations; if nowhere has it, the line is backordered or rejected, never silently oversold.

How do you reserve stock without overselling across locations?

Reserve against a specific (variant, warehouse) row, inside the order transaction, with a row lock. The guard is the same as a single warehouse, but applied per location, so two orders cannot both be promised the last unit in the same warehouse.

BEGIN;
-- Lock and reserve at the chosen warehouse; the guard refuses to over-reserve.
UPDATE stock_levels
SET reserved = reserved + $3
WHERE variant_id = $1 AND warehouse_id = $2
  AND on_hand - reserved >= $3
RETURNING on_hand - reserved AS remaining;
-- No row returned means not enough at this location: fall back to the next in the plan.
INSERT INTO allocations (order_line_id, warehouse_id, qty) VALUES ($4, $2, $3);
COMMIT;

When the allocation plan spans two warehouses, each reservation is its own guarded update; if the second fails because stock moved, re-plan and retry rather than leaving a half-reserved order. Perishable or batch stock adds a dimension (ship the earliest-expiring first), which is the FEFO rule covered in order fulfilment with FEFO stock rotation.

What about transfers and in-transit stock?

Stock moving between warehouses is neither fully in the source nor the destination, so model a transfer explicitly: it decrements on-hand at the source when it leaves and increments on-hand at the destination when it arrives, with an in-transit state in between. If you let a transfer vanish from one place before it appears in the other, your total goes wrong and the allocation rule makes bad decisions. Treat the transfer as its own record with states, the same way you would any multi-step movement.

How do you test multi-warehouse inventory?

The expensive bug is overselling across locations, so test allocation and reservation together under concurrency.

import { describe, it, expect } from 'vitest'
import { allocate, totalReserved, totalOnHand } from './inventory'

describe('multi-warehouse allocation', () => {
  it('splits a line across warehouses when no single one can fill it', async () => {
    await setStock('SKU1', { A: 3, B: 2 })
    const plan = await allocate({ variant: 'SKU1', qty: 5 })
    expect(plan).toEqual([{ warehouse: 'A', qty: 3 }, { warehouse: 'B', qty: 2 }])
  })

  it('never reserves more than is on hand, under concurrency', async () => {
    await setStock('SKU2', { A: 1 })
    const both = await Promise.allSettled([
      allocate({ variant: 'SKU2', qty: 1 }),
      allocate({ variant: 'SKU2', qty: 1 }),
    ])
    const ok = both.filter((r) => r.status === 'fulfilled')
    expect(ok.length).toBe(1)                               // one order gets the unit
    expect(await totalReserved('SKU2')).toBeLessThanOrEqual(await totalOnHand('SKU2'))
  })
})

Add a test that a transfer in flight keeps the total correct, and one that a backordered line is rejected, not silently oversold.

Buy, build or hire?

Option	Choose this when	The catch
An inventory or OMS tool (a platform's multi-location feature, a dedicated OMS)	Standard products and a fulfilment model the tool supports	Allocation rules, batch/FEFO stock and local gateways follow the tool's model, which may not match yours
A spreadsheet per warehouse	Two locations and low volume	No atomic reservation; the two sheets disagree and you oversell
Custom build	Your allocation rule is specific, you handle batch or perishable stock, or you run transfers	You own the allocation logic and the tests that keep the total promised within the total on hand

How long does it take to build yourself, and what is the risk?

For an experienced backend developer adding per-location stock and allocation to an existing store, our estimate is 2 to 4 weeks for a first version, or 6 to 12 weeks with transfers, backorders and a cost-based rule. The main risk is concurrency across locations: an allocation path that is not transactional per row will oversell under load, and it will pass every single-order test while doing so. Test reservation under concurrent orders, not just in isolation.

Why RAITHub for this

• Stock logic in production. TheSkinProof, the founder's own venture built and run by RAITHub, decrements per-variant stock inside the order transaction (750+ tests), and Sundor Skin runs FEFO batch stock across 146 PostgreSQL tables and 530+ tests. See the Sundor Skin case study.
• Concurrency tested. The oversell guards are covered by tests gated in CI, so adding a warehouse cannot quietly reintroduce overselling.
• Honest scope. If your fulfilment fits an off-the-shelf OMS, RAITHub will say so on the first call rather than build what you can buy.

When you don't need us

• An OMS already fits. Standard products and a supported fulfilment model: configure the tool.
• You truly have one warehouse. Then a single stock number and one oversell guard are enough.
• You only need the model. The schema and allocation function above are a fair start for your own developer.

How RAITHub would build this

• Per-location stock: on-hand and reserved by (variant, warehouse), with a derived total for display.
• Allocation engine: a pure function applying your rule (nearest, fewest splits, cost-based), splitting lines and backordering when needed.
• Atomic reservation: guarded per-row updates inside the order transaction, with re-plan and retry on contention.
• Transfers and FEFO: explicit in-transit state and earliest-expiry-first picking where stock is batched or perishable.

Timeline: a first version is 4 to 6 weeks at a fixed scope; transfers, backorders and cost-based allocation push it into the 6 to 12 week backend range. See SaaS development and the ecommerce industry page. A related build is gift cards and store credit that always balance.

You receive: the inventory and allocation code, concurrency and allocation tests gated in CI, handover docs, and full IP in your name under NDA.

Next step: book the free 15-minute technical audit with your warehouses and allocation rule, and we will follow up with a written fixed quote.

Frequently asked questions

Why can't I just keep one total stock number across warehouses?

Because a total hides where the stock is, and the system still has to decide which warehouse fills each order. A total of ten can be seven in one location and three in another, which changes delivery cost, whether the order splits, and whether it can ship at all.

How does the system decide which warehouse ships an order?

Through an allocation rule you choose: nearest to the customer, fewest shipments, or lowest cost, often combined. Allocation runs as a pure function of the order and current availability, so the same inputs always produce the same plan and it can be tested.

How do I stop overselling when stock is split across locations?

Reserve against a specific (variant, warehouse) row inside the order transaction, with a row lock and an on_hand - reserved >= qty guard. Two orders then cannot both be promised the last unit at the same location, and the total promised never exceeds the total on hand.

How should inter-warehouse transfers be modelled?

As an explicit record with an in-transit state: on-hand drops at the source when the stock leaves and rises at the destination when it arrives. If stock vanishes from one place before appearing in the other, your totals go wrong and allocation makes bad decisions.

What about perishable or batch stock across warehouses?

Add a batch dimension and pick the earliest-expiring stock first (FEFO). Allocation then chooses both the warehouse and the batch, so near-expiry stock ships before it is written off, without overselling across locations.

Should I build this or use an order management system?

Use an OMS when your products and fulfilment fit its model. Build custom when your allocation rule is specific, you handle batch or perishable stock, you run transfers, or you need local payment and courier rules the OMS does not support.