Multi-Warehouse Inventory Is a Distributed Systems Problem: What to Build in Supply Chain Software

Quick Answer

Multi-warehouse inventory goes wrong in the same ways distributed systems do: two writers race for the last unit, a scanner retries a request, a message is published twice or never, and a device works offline for an hour. Keep a balance row with CHECK constraints, change it with one conditional UPDATE, record every change in an append-only ledger under a unique idempotency key, publish events through an outbox, key Kafka messages by stock position and filter reservations by expiry at read time. This guide shows each part with SQL tested on PostgreSQL 16 and behavior taken from PostgreSQL 18, Kafka 4.3, Stripe and Firebase documentation checked on October 26, 2026. It ends with five teams that build supply chain software.

Why Is Multi-Warehouse Inventory a Distributed Systems Problem?

One warehouse with one database and one operator can get away with UPDATE stock SET qty = qty - 1. Several warehouses cannot. Pickers on handheld scanners, a web order flow, a replenishment job and an ERP sync all touch the same stock positions, over networks that drop requests, and each caller may retry.

That creates four failure modes worth designing for:

• Lost updates. Two writers read 10 units and both write back 9.
• Overselling. Two orders reserve the same last unit.
• Duplicate effects. A scan is retried and decrements stock twice.
• Split-brain events. The database says stock moved, but the message that tells other systems never left, or it left twice.

The rest of this guide takes them in turn. The SQL ran on a local PostgreSQL 16 server while this article was prepared. Statements about concurrent behavior come from the PostgreSQL 18 documentation, not from a load test.

How Do You Stop Stock From Going Negative?

Put the rule in the database, then make the write conditional. PostgreSQL's documentation says a CHECK constraint passes when its expression is true or null, so a quantity column needs NOT NULL as well. It also says a CHECK cannot look at other rows, so "the sum of ledger rows is at least zero" cannot be a CHECK on the ledger. Keep a balance table and put the constraint there.

CREATE TABLE stock_balance (
  sku_id        bigint  NOT NULL,
  warehouse_id  bigint  NOT NULL,
  on_hand       integer NOT NULL DEFAULT 0
                CONSTRAINT on_hand_non_negative CHECK (on_hand >= 0),
  reserved      integer NOT NULL DEFAULT 0
                CONSTRAINT reserved_non_negative CHECK (reserved >= 0),
  CONSTRAINT reserved_le_on_hand CHECK (reserved <= on_hand),
  PRIMARY KEY (sku_id, warehouse_id)
);

Reserving stock is then a single statement:

UPDATE stock_balance
   SET reserved = reserved + 4
 WHERE sku_id = 1 AND warehouse_id = 1
   AND on_hand - reserved >= 4
RETURNING on_hand, reserved;

With 10 units on hand, the first call returned one row (10, 4). Asking for 7 more returned zero rows, which PostgreSQL reports as UPDATE 0 and not as an error. Treat zero rows as a business answer ("insufficient stock") and not as an exception.

Why is this safe under concurrency? The documentation says that in the default Read Committed level, an UPDATE that meets a row another transaction is changing waits for it, and if the other transaction commits, the WHERE clause is checked again against the new row version. The second writer therefore sees the reduced balance and matches nothing. The same page warns that complex multi-row conditions can behave differently, so keep the conditional update to one row.

At Repeatable Read and Serializable, the documentation says the database may instead raise a serialization failure (SQLSTATE 40001) and that the application must retry the whole transaction. If you raise the isolation level, add the retry loop first.

When one transaction touches several balance rows, take them in a consistent order, for example sorted by SKU and warehouse. The documentation names consistent ordering as the best defense against deadlocks and recommends keeping transactions short.

Should You Store Stock as a Ledger or as a Column?

Both, and this is the author's recommendation rather than a documented rule. A mutable quantity answers "how many now" quickly but forgets why. An append-only ledger answers "what happened" but needs a fold to answer "how many". Martin Fowler's event sourcing article, which he describes as a draft he will not update, lists the drawbacks as well as the benefits: replays are slow without snapshots, side effects during replay need care and retrofitting is hard. Microsoft's Azure Architecture Center page says to apply event sourcing selectively and lists simple CRUD systems and prototypes as poor fits.

The hybrid that follows from this: the ledger is the audit record, the balance row is the fast read, and both change in the same transaction.

CREATE TABLE stock_ledger (
  entry_id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku_id           bigint      NOT NULL,
  warehouse_id     bigint      NOT NULL,
  qty_delta        integer     NOT NULL,
  reason           text        NOT NULL,
  idempotency_key  text        NOT NULL,
  created_at       timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ledger_idem UNIQUE (idempotency_key)
);

How Do You Make a Scanner Retry Harmless?

Give every scan event a key and let the database refuse the second copy. Stripe's API documentation is a useful worked example: the client generates the key (it suggests high-entropy values such as UUIDs, up to 255 characters), the server stores the first result and returns it on a retry, and a retry with different parameters is an error. Stripe also says keys can be pruned once they are at least 24 hours old, so a key is not a permanent identity.

The IETF has an Idempotency-Key header draft in progress. As of October 26, 2026 it is an Internet-Draft (revision 08, updated October 22) and not an RFC, so describe it as a draft. It suggests 409 when a retry arrives while the first request is still running and 422 when a key is reused with a different payload.

For a scanner, one key per scan event is enough, for example a UUID or the device ID plus a local sequence number. The ledger's UNIQUE constraint does the rest:

BEGIN;
WITH ins AS (
  INSERT INTO stock_ledger (sku_id, warehouse_id, qty_delta, reason, idempotency_key)
  VALUES (1, 1, -4, 'pick', 'scan-0001')
  ON CONFLICT (idempotency_key) DO NOTHING
  RETURNING qty_delta
)
UPDATE stock_balance b
   SET on_hand = b.on_hand + ins.qty_delta
  FROM ins
 WHERE b.sku_id = 1 AND b.warehouse_id = 1
RETURNING b.on_hand, b.reserved;
COMMIT;

Results from the test run: the first call returned on_hand = 6. The same key sent again inserted nothing, so the update matched nothing and the balance stayed at 6. A second pick of 9 with a new key violated the on_hand_non_negative constraint, the transaction rolled back, and the ledger still held one row. The application should read "no row returned" as "already applied" and answer with the earlier result.

How Do You Publish Stock Events Without Losing or Duplicating Them?

Writing to the database and then publishing to a broker is two actions that can fail separately. The transactional outbox pattern, as described on microservices.io, writes the message to an outbox table in the same transaction as the business change and lets a separate relay publish it. The message goes out if and only if the transaction commits. The same page says the relay can publish a message more than once, for example after a crash between publishing and marking the row sent, so consumers must be idempotent.

A relay can claim rows with FOR UPDATE SKIP LOCKED:

BEGIN;
SELECT id, event_key, payload
  FROM outbox
 WHERE published_at IS NULL
 ORDER BY id
 LIMIT 100
 FOR UPDATE SKIP LOCKED;
-- publish these to the broker, then:
UPDATE outbox SET published_at = now() WHERE id IN (/* claimed ids */);
COMMIT;

The PostgreSQL documentation says SKIP LOCKED gives an inconsistent view of the data, is not for general-purpose work, and suits queue-like tables with several consumers. That fits an outbox, but never use it to compute stock totals. One consequence is the author's inference: with several relay workers, a later row can be published before an earlier row that another worker holds, so either run one relay per key range or let consumers order events by a per-key sequence.

What Should the Kafka Side Look Like?

Kafka guarantees order per partition, not per topic, and events with the same key go to the same partition. So key messages by the stock position, for example warehouse_id:sku_id, and every movement of that position stays in order.

For delivery, the Kafka 4.3 documentation describes the idempotent producer, which lets the broker discard duplicate resends using a producer ID and sequence numbers. In 4.3 enable.idempotence defaults to true unless a conflicting setting disables it, and it requires acks=all, retries above zero and at most five in-flight requests per connection. Setting it explicitly makes a conflict fail loudly instead of silently turning it off.

enable.idempotence=true
acks=all
max.in.flight.requests.per.connection=5
# only if you use transactions:
# transactional.id=inventory-writer-1
# consumer side for read-process-write:
# isolation.level=read_committed
# enable.auto.commit=false

These are the documented property names and were not run against a cluster. The part people overlook is the consumer. Kafka's documentation says exactly-once to an external system needs that system's cooperation, so a consumer that writes to PostgreSQL is at-least-once unless it stores the event ID, or the offset, in the same transaction as the write. Use the ledger's idempotency key for that. I have not checked how adding partitions changes the mapping from key to partition, so verify that before you resize a live topic.

How Should Reservations Expire?

No official document defines an inventory reservation, so treat this as a design. Hold stock in a reservations table with an expires_at column, treat expired rows as inactive when you read, and run a worker that uses SKIP LOCKED to release expired rows and reduce reserved in the same transaction.

Be wary of letting a database's TTL feature release stock for you. Redis documents passive and active expiry, but a Redis key should not be your system of record for stock. Firestore's documentation says TTL deletion typically happens within 24 hours of expiry and that expired documents still appear in queries until deleted. DynamoDB says expired items are typically deleted within a few days and remain readable until then. Both tell you to filter on the expiry field in queries. TTL is cleanup, not a timer.

What Happens When Scanners Work Offline?

Firestore's offline documentation says a client can read and write cached data while disconnected and that changes sync on reconnect, with last write winning when the same document is changed more than once. For inventory, that rule is a trap. If two offline scanners each write an absolute quantity of 7 to the same document, one count silently disappears. The transactions page adds that transactions fail while the client is offline, though it documents FieldValue.increment() for simple counter updates.

No official document I found prescribes a conflict strategy for offline scanners, so the following is the author's design: queue scan events on the device, give each an idempotency key, send deltas and not absolute counts, apply them in order per stock position on the server, and flag any event that would push stock below zero for a person to resolve. Firestore also documents a limited sustained write rate for a single document. A figure of about one write per second is often cited, but Google's current quota page does not state it and the best-practices page says the rate depends on workload, so load-test a hot SKU before choosing a counter design.

What Should You Build First?

Problem	Control	First step this week
Negative stock	CHECK on the balance row plus a conditional UPDATE	Add NOT NULL and CHECK constraints
Double scans	Unique idempotency key on the ledger	Generate one key per scan event
Lost messages	Outbox written in the same transaction	Add an outbox table and a relay
Duplicate messages	Idempotent consumers, keyed by event ID	Store processed IDs in the consumer's database
Wrong order	Kafka key by stock position	Key by warehouse and SKU
Phantom holds	Expiry filtered at read time	Add expires_at and a release worker
Offline conflicts	Deltas with keys, not absolute counts	Queue events on the device

Which Teams Build Supply Chain Software? Five Worth Knowing

Figures below come from each company's own website and are self-reported, so confirm them directly before signing anything.

1. Dev Technosys

Dev Technosys is a supply chain software development company that lists custom supply chain management development, consulting, software integrations, AI-powered supply chain development, maintenance and support, and dedicated developers for hire. Its page names inventory, transportation, warehouse, procurement, logistics and supplier relationship management solutions, and describes a three-step path from business and process evaluation through custom software strategy to an implementation and delivery plan. It cites 250+ professionals. Dev Technosys suits manufacturers, distributors and 3PLs that want strategy, engineering and integration for one supply chain platform from a single provider.

2. SysGears

SysGears says it was founded in 2010, lists offices in Paphos, Dnipro, Lviv and Wroclaw and cites 110+ experts. Its warehouse page covers custom WMS builds, additions to existing systems and legacy modernisation, with integrations to carriers, e-commerce platforms, ERP and hardware such as AMRs and ASRS. A seven-step process runs from a kickoff call through discovery, team building, design and agile development to QA and collaboration. It names Node.js, NestJS, Kafka and PostgreSQL and no certifications. SysGears suits mid-size operators that want a WMS-focused team with a clear process.

3. Django Stars

Django Stars lists a Wilmington, Delaware address in its footer and gives conflicting team sizes, so none is quoted. Its supply chain service covers custom builds, legacy modernisation, ERP, WMS and TMS integration, EDI and API work and IoT telematics. Seven steps run from a supply chain audit through process mapping, architecture, development, integration and testing to launch. It names Python, Django, PostgreSQL and Redis, and claims ISO 27001 without naming an issuer. Django Stars suits retail, distribution and 3PL teams that want a Python-based build with a phased method.

4. Atomic Object

Atomic Object lists offices in Ann Arbor, Chicago, Grand Rapids and Raleigh and says it has worked with clients since 2001, without stating headcount. Its logistics page describes three stages: alignment through human-centred design, delivery of working software in two-week increments, and a no-surprises stage that tracks progress against the remaining budget. It offers a free build-versus-buy guide and a project budget model after a meeting, names no programming languages and claims no certifications. Atomic Object suits US freight brokers, shippers and carriers that want a US-based, product-minded build team.

5. Saigon Technology

Saigon Technology labels Ho Chi Minh City as its head office, says it has operated since 2012 and states 400+ developers. Its supply chain page lists planning, procurement, inventory, warehouse, transportation and visibility work, with integrations to SAP, Oracle, Microsoft Dynamics and EDI, and messaging through Kafka and gRPC. Five stages run from discovery and architecture through two-week sprints and testing to deployment and support. It names BSI as the issuer of its ISO 27001 and 9001 certificates. Saigon Technology suits buyers wanting a Vietnam-based team with a staged process.

Frequently Asked Questions

How do you prevent overselling in a multi-warehouse system?

Use one conditional UPDATE on a balance row that checks available stock in its WHERE clause, backed by NOT NULL and CHECK constraints, and treat a zero-row result as "insufficient stock". PostgreSQL's documentation says the condition is checked again after a competing transaction commits.

Is exactly-once delivery possible with Kafka and PostgreSQL?

Kafka documents exactly-once processing within Kafka using transactions. For an external system such as PostgreSQL, its documentation says that system must cooperate, so store the event ID or offset in the same database transaction as the write, or dedupe by idempotency key.

Should I use event sourcing for inventory?

Only where you need the audit trail or replay. Microsoft's guidance lists simple CRUD systems and prototypes as poor fits. A ledger plus a balance row gives most of the audit value with less machinery, which is the author's recommendation.

How do I choose a supply chain software development company?

Ask for a written scope, how stock consistency and offline scanning will be tested, which integrations are included, who owns the code afterward and what support costs once the system is live. Be cautious of any price quoted before someone has seen your warehouses and systems.

Comments

[Oleksandr]

The UPDATE ... WHERE on_hand - reserved >= 4 pattern is the single most important takeaway here. It's amazing how many teams try to solve concurrency with SELECT then UPDATE in application code, only to get destroyed by race conditions. Letting the database handle row-level locking in a single atomic statement is the only way to prevent overselling.

The outbox + FOR UPDATE SKIP LOCKED combo is also the gold standard for reliable events.

One technical nuance on the idempotency: you mention that a retried ON CONFLICT DO NOTHING returns zero rows, and the app should interpret that as "already applied." How do you handle the response payload in that case? If the first scan succeeded but the response was lost in the network, the retry gets zero rows, but the client still needs to know the new balance. Do you do a secondary SELECT to fetch the current state and return it, or does the client just gracefully accept the "no-op" response?