Inventory System Design Interview: Reservations, Overselling, and Reconciliation

An inventory system design interview is a state-accounting problem disguised as a stock counter. Ten units on a shelf can simultaneously be described as on-hand, partly committed to orders, partly damaged, and partly available. If those meanings are not defined first, “decrement inventory” will oversell during retries or erase the evidence needed for reconciliation.

Shopify officially distinguishes inventory states such as on-hand, committed, unavailable, and available. Square documents inventory adjustments, physical-count reconciliation, timestamps for offline changes, and idempotency keys. Those facts justify a ledger model; the exact schemas, partitioning, and service boundaries below are editorial design choices.

For a closely related drill, use Design Inventory Availability and Order Placement.

Inventory System Interview: Quick Answer

Represent every change as an inventory movement for (sku, location): receipt, reservation, release, sale, return, transfer, damage, or physical-count correction. Maintain a transactional projection where available = on_hand - committed - unavailable under the chosen business definitions. Reserve with an idempotency key and an expiry; finalization consumes that exact reservation token.

Serialize scarce-unit decisions at the owner of the SKU-location record. Publish versioned changes to search and storefront caches, but do not let those projections authorize the sale. Reconcile ledger totals, order commitments, and periodic physical counts, recording corrections rather than overwriting history.

The Core Contract

Inventory is a ledger of physical and promised units, not a mutable counter. A strong design names each quantity state, chooses where overselling is forbidden, and repairs projections from immutable movements.

Movement	On hand	Committed	Available	Typical trigger
receive 10	+10	0	+10	warehouse receipt
reserve 3	0	+3	-3	checkout hold
sell reservation 3	-3	-3	0	paid order
expire reservation 2	0	-2	+2	hold timeout
mark damaged 1	0	0	-1	inspection

The table is a declared accounting model, not a claim that every retailer uses the same labels. A candidate should adapt it when backorders, safety stock, or vendor-managed inventory change the business contract.

Define the SKU-Location Invariant

Choose the allocation grain. A global SKU total is useful for search but cannot decide which warehouse can ship. The authoritative key is often SKU plus location, batch, or lot. State whether negative available inventory is ever allowed for preorders. If not, the reservation transaction checks available >= requested and updates commitment atomically.

Do not conflate safety stock with missing units. Safety stock is a policy hold represented as unavailable or as an allocation rule. Damaged and quarantined units remain on-hand physically but are unavailable to promise. These distinctions make customer and warehouse views consistent even when their totals differ.

Give Reservations an Identity and a Clock

A reservation contains reservation_id, order or cart ID, SKU-location, quantity, state, created time, expiry, and version. Repeating the same request returns the existing hold. Expiry changes active to released only if the version still matches. A payment callback must present the reservation token; after expiry it either fails, re-reserves, or enters exception handling under a stated policy.

Keep the order and inventory services from each believing the other committed first. A saga can reserve, authorize payment, confirm inventory, and compensate on failure, but each step needs idempotency. Measure reservation age and expiry backlog because a stuck sweeper can look like a sudden stockout.

Choose an Oversell Boundary

For one warehouse database, a conditional update or row lock is a clear linearization point. At high scale, assign one leader per SKU-location or partition a quantity into regional allocations. Allocations reduce cross-region latency but may strand stock. Oversell remains possible if the same units are sold in a store while the network is offline, so the business must choose whether to block sales, reserve an offline quota, or accept later exception handling.

Read caches may be stale. Show “low stock” conservatively, and revalidate at checkout. Never promise that cache invalidation prevents oversell; only the authoritative reservation transition does.

Reconcile Logical Stock with Physical Reality

Reconciliation compares the movement ledger projection, open orders, transfers in transit, and a trusted physical count at a named watermark. A difference becomes a correction movement with reason, operator, device, and count session—not a silent set-to-value update. Offline scanners need event IDs and observed times; server receipt time alone can misorder a delayed adjustment.

Track ledger-versus-projection divergence, negative availability attempts, expired-hold age, unconfirmed transfers, and count variance. Rebuild a new projection generation from movements, verify checksums by SKU-location, then switch readers. That recovery story is more persuasive than adding more databases.

Work Through a Failure Trace

A warehouse has five on-hand, one damaged, and two committed units, so two are available. Two checkouts each request two. Only one conditional reservation succeeds. If that hold expires before payment, a versioned release restores two; a late callback cannot consume it without a new reservation. A physical count of four then adds a documented -1 correction at the count watermark.

Inventory System check: What do on-hand, available, committed, and unavailable mean? Walk the trace once in event-time order, once with retries and reordering, and once after rebuilding projections from durable records. State the expected invariant after every step. If the outcome depends on message arrival order when the contract says it should not, the design is missing an identity, version, or transaction boundary. If the design intentionally permits a temporary difference, give it a watermark, maximum age, and repair owner.

Inventory System check: Where is the no-oversell decision serialized? This trace is an interview model, not a measured production benchmark. Add a skew test concentrated on one tenant or key, a two-times-peak capacity test, and a fault injection at the acknowledgment boundary. Preserve input versions and random seeds so a failed run can be reduced and replayed.

Turn the Requirements into Verification

Write these five questions beside the diagram: What do on-hand, available, committed, and unavailable mean? Where is the no-oversell decision serialized? How do reservation expiry and late payment callbacks interact? How are stores or warehouses reconciled after offline changes? Which record can rebuild availability after corruption? Each question must point to a record, transition, test, and observable signal. If one can be answered only with 'the service handles it,' the contract is still incomplete.

Inventory System check: How do reservation expiry and late payment callbacks interact? Build a deterministic fixture with five to ten timestamped inputs. Include an identical retry, a conflicting reuse of an ID, a late or stale input, a worker failure after durable work but before acknowledgment, and a rebuild of the serving projection. Expected results must be written independently of the implementation. Verify the authoritative state, the serving state through a named watermark, and every emitted correction or rejection reason.

Inventory System check: How are stores or warehouses reconciled after offline changes? Then add property checks. Applied logical IDs remain unique; versions never move backward; reserved or consumed value obeys the topic's invariant; a rebuild through the same input boundary produces the same digest; and an unauthorized reader cannot cross its scope. Generate reordered deliveries around the boundary instead of testing only the happy sequence. A passing fixture proves these declared cases, not production throughput or every network failure.

Inventory System check: Which record can rebuild availability after corruption? For fault testing, stop a worker immediately before and after its commit, duplicate queue delivery, delay one partition, and make the read projection unavailable. The runbook should say whether intake pauses, work accumulates durably, readers serve a last-known-good generation, or the request fails closed. Record the recovery point and compare the rebuilt digest before switching traffic. This is concrete evidence that the recovery story matches the write contract.

Capacity, Operations, and Recovery

Inventory System check: What do on-hand, available, committed, and unavailable mean? Estimate the durable write rate, average and peak payload, partition skew, retention volume, read or query shape, and acceptable lag. Convert those assumptions into partitions, worker concurrency, storage tiers, and backpressure. Numbers are sizing inputs, not universal facts. Say which limit fails first and what the user sees when it does.

Inventory System check: Where is the no-oversell decision serialized? Operate from contract metrics: accepted-versus-applied counts, duplicate/conflict rate, oldest pending work, authoritative-versus-serving divergence, retry exhaustion, and p95/p99 latency. Alert on sustained error-budget consumption and correctness gaps rather than raw traffic. Keep a runbook that can pause intake or rollout, drain work, rebuild a named projection through watermark W, verify a checksum, and switch readers to the new generation.

Inventory System check: How do reservation expiry and late payment callbacks interact? Use least privilege between producers, workers, control plane, data plane, and investigators. Encrypt transit and storage, rotate credentials, audit high-risk actions, and avoid placing secrets or unnecessary personal data in operational logs. Security controls support the data contract; they do not replace idempotency or verification.

Misleading Answers and Better Follow-Ups

Inventory System check: How are stores or warehouses reconciled after offline changes? A weak answer names Kafka, Redis, and a database before defining truth. Another says “exactly once” without identifying the idempotency key, persistence boundary, or replay behavior. A third adds a cache but cannot explain invalidation or rebuild. Replace each slogan with one invariant, one linearization point, and one counterexample.

Inventory System check: Which record can rebuild availability after corruption? Ask the interviewer to change a requirement: permit temporary staleness, add multiple regions, require a correction after finalization, or remove a dependency. Explain which state transition, key, and monitor changes. That is more useful than redrawing the same boxes at a larger scale.

Inventory System check: What do on-hand, available, committed, and unavailable mean? Separate three evidence classes in the answer. Official documentation supports the cited product or protocol behavior. The proposed architecture, thresholds, and sequencing are inference from the stated requirements. This prevents a sensible design choice from being presented as an external fact.

A 45-Minute Interview Plan

Spend the first five minutes clarifying actors, scale, correctness, retention, and failure expectations. Use the next eight to define the authoritative records, IDs, versions, and invariants. Draw the write path and its acknowledgment boundary for ten minutes, then the read or serving path for five.

Use ten minutes for the title-specific failure trace, including one retry, one stale or late event, and one recovery. Reserve the final seven minutes for sizing, skew, access control, monitors, and trade-offs. Keep a short list of assumptions visible. When the interviewer changes one, update the affected contract before changing components.

End with the source of truth, the permitted temporary inconsistency, and the repair procedure. A compact design that can be replayed and verified is stronger than a large architecture with undefined states.

Final Takeaway

For a inventory system design interview, lead with this thesis: Inventory is a ledger of physical and promised units, not a mutable counter. A strong design names each quantity state, chooses where overselling is forbidden, and repairs projections from immutable movements. Define the evidence boundary, run a concrete race or failure trace, and show how the system detects and repairs divergence.

Sources and Further Reading

• Shopify inventory states
• Square Inventory API overview
• Square inventory adjustment API
• PostgreSQL explicit locking

Official sources support only the cited mechanism. Architecture recommendations, capacity figures, and interview sequencing are editorial inference.