Most inventory discrepancies I've been asked to fix were not theft and not miscounts. They were software that stored stock as a single mutable number per product and per warehouse, updated from five different code paths, with no record of why it changed. When that number drifts from the shelf, nobody can say when it drifted or which transaction did it. A multi-warehouse inventory system needs to be built like a bank ledger, not like a counter.

I run Loop Origin, where my team builds custom ERP, inventory and logistics systems, mostly on Laravel, Vue 3 and PostgreSQL. I wrote this with help from AI tools and checked every query and claim myself. The schema and code below are simplified from patterns I use in production.

How do you design multi-warehouse inventory that reconciles?

Direct answer: Record every stock change as an immutable movement row (receipt, pick, transfer, adjustment) keyed by SKU, location and lot, and derive on-hand balances from that ledger. Track reservations separately so available equals on-hand minus reserved. Make every scan idempotent, model transfers with an in-transit location, and run a nightly job that proves balances equal the ledger sum.

Three ways to store stock, and what each costs you

Approach How it works Audit trail Concurrency risk Reconciliation When it fits
Mutable quantity column UPDATE products SET qty = qty - 3 None Lost updates if done read-then-write Impossible to explain drift Single store, low volume
Ledger + balance projection Append movement, update a balance row in the same transaction Full Low with row locks or guarded updates Sum ledger vs balance, nightly Most multi-warehouse operations
Full event sourcing Balances rebuilt from events by projectors Full Low, but projector lag Rebuild and diff Very high volume, many read models

The middle option is what I default to. It gives you a complete "why" for every unit, without the operational overhead of running projectors and replay infrastructure for a warehouse team of 20.

The core schema

Four tables carry the design. Locations are hierarchical (warehouse, zone, bin), and every movement points at a bin, not just a warehouse:

CREATE TABLE locations ( id bigserial PRIMARY KEY, warehouse_id bigint NOT NULL REFERENCES warehouses(id), code text NOT NULL, -- e.g. 'A-03-2' or 'IN-TRANSIT' kind text NOT NULL CHECK (kind IN ('bin','receiving','staging','in_transit','quarantine')), UNIQUE (warehouse_id, code) ); CREATE TABLE stock_movements ( id bigserial PRIMARY KEY, sku_id bigint NOT NULL REFERENCES skus(id), location_id bigint NOT NULL REFERENCES locations(id), lot text NOT NULL DEFAULT '', qty numeric(14,3) NOT NULL CHECK (qty <> 0), -- +in, -out reason text NOT NULL CHECK (reason IN ('receipt','pick','transfer_out','transfer_in','adjust_count','adjust_damage','return')), ref_type text NOT NULL, -- 'purchase_order','sales_order','transfer','count' ref_id bigint NOT NULL, idempotency_key uuid NOT NULL UNIQUE, -- one per physical scan or API call user_id bigint NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE stock_balances ( sku_id bigint NOT NULL, location_id bigint NOT NULL, lot text NOT NULL DEFAULT '', on_hand numeric(14,3) NOT NULL DEFAULT 0 CHECK (on_hand >= 0), reserved numeric(14,3) NOT NULL DEFAULT 0 CHECK (reserved >= 0), PRIMARY KEY (sku_id, location_id, lot) );

Three constraints do a lot of quiet work here. CHECK (on_hand >= 0) makes negative stock a database error instead of a report surprise. The UNIQUE idempotency key makes a double-tapped scanner harmless. And the reason list forces every adjustment to declare whether it was a count correction or damage, which is where shrinkage analysis starts.

Posting a movement safely

Every write path, from the receiving screen to the Shopify webhook, goes through one function. In Laravel:

public function post(StockMovementData $m): void { DB::transaction(function () use ($m) { $inserted = DB::table('stock_movements')->insertOrIgnore([ 'sku_id' => $m->skuId, 'location_id' => $m->locationId, 'lot' => $m->lot, 'qty' => $m->qty, 'reason' => $m->reason, 'ref_type' => $m->refType, 'ref_id' => $m->refId, 'idempotency_key' => $m->idempotencyKey, 'user_id' => $m->userId, ]); if ($inserted === 0) { return; // same scan already posted: no-op } DB::statement( 'INSERT INTO stock_balances (sku_id, location_id, lot, on_hand) VALUES (?, ?, ?, ?) ON CONFLICT (sku_id, location_id, lot) DO UPDATE SET on_hand = stock_balances.on_hand + EXCLUDED.on_hand', [$m->skuId, $m->locationId, $m->lot, $m->qty] ); }); }

The upsert is a single atomic statement, so two pickers posting against the same bin can't lose an update. If a pick would push on_hand below zero, the CHECK constraint throws and the whole transaction, movement included, rolls back. The picker sees "short pick" and the system creates a count task for that bin.

Reservations: on-hand is not available

An order placed at 10:02 doesn't move stock; it promises it. If you decrement on-hand at order time, your pickers see bins that look emptier than they are, and cancellations become adjustments. I keep reservations as a separate column, plus a reservations table with an expiry for carts and quotes:

UPDATE stock_balances SET reserved = reserved + :qty WHERE sku_id = :sku AND location_id = :loc AND lot = :lot AND on_hand - reserved >= :qty; -- 0 rows updated = not enough available; try the next location or backorder

The guard in the WHERE clause makes over-reserving impossible without an explicit lock. It's the same hold-and-expire pattern I described for preventing double booking, applied to units on a shelf instead of time slots.

Rows of blue plastic storage bins in a warehouse picking area, representing bin-level stock locations
AI-generated image.

Transfers between warehouses

The most common source of "lost" stock in multi-warehouse setups is a transfer recorded as a single event: minus 50 in Lahore, plus 50 in Karachi, at the moment the truck leaves. For the next two days Karachi shows stock that is on a highway.

Model it as two postings through an in-transit location:

Step Movement Lahore A-03-2 In-transit Karachi receiving
Dispatch transfer_out −50, transfer_in +50 to in-transit 0 50 0
Receive (48 counted) transfer_out −48 from in-transit, transfer_in +48 0 2 48
Resolve variance adjust_damage −2 with transfer ref 0 0 48

Now the two missing units are visible on day two, tied to a specific transfer and truck, instead of surfacing as an unexplained count variance a month later.

Barcode scanning that doesn't create drift

Handheld scanning is where the ledger gets most of its rows, and where bad UX creates most of the errors:

  • Scan location, then item, then confirm quantity. Never let a picker post a movement without a scanned bin.
  • Generate the idempotency key on the device when the scan happens, not on the server. Retries after a Wi-Fi drop reuse the same key, so the server ignores the duplicate.
  • Queue offline scans locally with their keys and timestamps, then sync. The unique key makes replay safe.
  • Reject unknown barcodes loudly instead of mapping them to a "misc" SKU.

Reconciliation: prove the books balance

A nightly job compares the projection with the ledger and alerts on any difference:

SELECT b.sku_id, b.location_id, b.lot, b.on_hand, COALESCE(SUM(m.qty),0) AS ledger FROM stock_balances b LEFT JOIN stock_movements m USING (sku_id, location_id, lot) GROUP BY b.sku_id, b.location_id, b.lot, b.on_hand HAVING b.on_hand <> COALESCE(SUM(m.qty),0);

On a healthy system this returns zero rows every night. When it doesn't, someone wrote to stock_balances outside the posting function, which is a code review problem, not a warehouse problem. Physical accuracy is handled separately with cycle counts: count a rotating set of bins daily, post the difference as adjust_count, and review adjustment value by reason each week.

Failure modes I design against

  • Direct balance edits from an admin panel. Every "fix" must be an adjustment movement with a reason.
  • Integrations posting without idempotency keys. Marketplace and ERP webhooks retry. Use the provider's event ID as the key, as I covered in resilient ERP and CRM integrations.
  • Unbounded ledger growth. Partition stock_movements by month and add periodic snapshot rows so queries don't sum years of history.
  • Units of measure. Store movements in the base unit; convert cartons to pieces at the scan screen.

Implementation checklist

  1. Model locations down to the bin, including receiving, in-transit and quarantine.
  2. Create the movements ledger with a reason code, reference and unique idempotency key.
  3. Route every stock change through one posting function in a single transaction.
  4. Add CHECK (on_hand >= 0) and guarded reservation updates.
  5. Implement transfers as dispatch and receive via an in-transit location.
  6. Generate scan keys on the device and support offline replay.
  7. Schedule the nightly reconciliation query with alerting.
  8. Run daily cycle counts and review adjustments by reason weekly.

Frequently asked questions

Should I use FIFO or weighted average costing?

That's an accounting choice, separate from quantities. The ledger supports both: FIFO needs cost layers per receipt, weighted average needs a running cost per SKU. Agree it with your accountant before go-live.

Can I retrofit a ledger onto an existing system?

Yes. Take a counted opening balance per bin, post it as an opening movement, then route new writes through the posting function. Replacing the old stock module first is a good phased modernization target.

Do I need lot or serial tracking?

Lots matter for anything with expiry dates or batch recalls. Serials matter for high-value items under warranty. The lot column handles lots; serials usually need their own table.

How do I handle negative stock when the physical goods exist?

Block it and create an urgent count task. Allowing negative stock hides receiving errors.

Is PostgreSQL fast enough for a high-volume warehouse?

For most mid-market operations, yes, with indexes on the balance key and a partitioned ledger. Load-test with your real scan rates before go-live.

Should inventory live inside the ERP or as a separate service?

If one team owns both, keep it in the same database so orders and stock commit together. Split it only when separate teams or systems truly need independent deployment.

Follow & Connect