Inventory Tracker

Track store stock changes, sell items and reconcile physical counts without allowing concurrent sales to create negative availability. This worked design targets SDE-1; the scale figures below are exercise assumptions, not claims about a named company's implementation.

Clarify the prompt

Requirements and scope

The core deliverable is: Track store stock changes, sell items and reconcile physical counts without allowing concurrent sales to create negative availability. The first design implements the APIs below and the state constraints in the data model. Agree the latency, freshness, retention and failure policy before adding infrastructure. Every successful acknowledgment must have an explicit meaning: accepted intent, committed state or completed external work. These are separate milestones.

Capacity and bottleneck reasoning

Assume 20,000 SKUs and 50 movements/s peak. At 200 bytes/movement and 100,000/day, raw history grows 20 MB/day. Index (store_id,sku_id) for stock and (store_id,created_at) for audit pages.

Keep average and peak demand separate. Add replication, indexes, retries and safety headroom to raw arithmetic before selecting capacity. Load-test the busiest ownership key and recovery traffic as well as uniform happy-path throughput.

APIs and data model

• POST /stock/{sku}/movements {delta,reason,requestKey} -> new version
• POST /sales {lines,requestKey} -> receipt or 409 insufficient stock
• GET /stock?cursor=... -> stock page with version

stock(store_id,sku_id,on_hand,reserved,version,PRIMARY KEY(store_id,sku_id))
movement(movement_id PK, operation_key UNIQUE, sku_id, delta, reason, actor)
Invariant: on_hand >= reserved >= 0; available = on_hand - reserved

Authorize resource ownership at the API boundary; do not trust IDs or state supplied by a client. Bind retry identity to the caller and payload for mutating operations. Reject an incompatible replay instead of returning an unrelated stored result.

Component relationships

Read the diagram

Follow the labeled relationship panels; they are separate directed paths, not one global timeline.

View full sizeOpen original

Inventory Tracker: state boundaries

Reset viewClose

Inventory Tracker: state boundaries

Step-by-step design

1. Establish the authority

Treat every sale, restock and correction as a movement with an actor and reason. Apply the movement and update its materialized stock row in one transaction using a stable operation key.

2. Make retries and concurrent work safe

Use an atomic conditional decrement or row locks, not read-then-write in application memory. For a multi-SKU sale lock rows in a stable order and commit all lines together.

3. Scale and recover the critical path

Physical stocktakes produce an adjustment against a recorded snapshot version. Investigate concurrent sales before applying the count; a blind overwrite can erase legitimate movements.

Worked request and concurrency example

SKU X has one unit. Registers A and B each try to sell one. A executes a conditional update requiring available >= 1 and commits its movement. B updates zero rows and returns insufficient stock. A network retry of A looks up the sale operation key and returns the same receipt. A later return appends a compensating movement; it never deletes the original sale.

Failure and recovery walkthrough

A crash between inserting history and updating balance must roll back both. If an external receipt printer fails after commit, reprint from the stored sale instead of decrementing stock again. Offline registers require an explicit capped allocation or accepted oversell risk.

Read the diagram

This numbered trace follows one scenario; explain the durable evidence at each transition.

View full sizeOpen original

Inventory Tracker: concrete trace

Reset viewClose

Inventory Tracker: concrete trace

Deep dive: stock is a conserved movement history

This original small-store baseline tracks sales, restocks, reservations, consumption, release and authorized corrections in integer units. The core invariant is on_hand >= reserved >= 0, with available = on_hand - reserved. History and the stock row commit together. Cached counts and receipt printers do not own the physical-stock decision.

A release must not add units to on_hand, and consumption must not decrement only on_hand while leaving reserved unchanged. Define supported operation types rather than letting clients submit arbitrary balance overwrites. Scope operation identity by store/tenant and action; compare the accepted lines and payload fingerprint on retry.

Make a multi-SKU sale all-or-nothing locally

A receipt wants A=1 and B=2. At the decision point, A has one available unit but B has only one. Under this all-lines policy the transaction rejects the sale and changes neither row. Lock affected rows in stable (store_id, sku_id) order, validate every line and append the movements with the stock updates and saved receipt result in one supported transaction. On deadlock/serialization retry, recompute the full decision under a bounded deadline.

begin
  resolve scoped sale key or reject changed payload
  lock all requested stock rows in stable order
  validate quantities and available for every line
  insert unique movement lines under sale operation identity
  update stock balances and versions
  store accepted receipt result
commit

If a printer fails after commit, reprint the stored receipt without another stock decrement. If the response is lost, retry the same sale identity. An independent history insert followed by a separate stock update creates a crash window where the audit and balance disagree.

Work a stocktake race numerically

The counter captures system version 7 while the tracked on_hand is 10. A physical count reports 9 at that checkpoint, suggesting adjustment −1. Before the correction commits, a sale of 2 changes the row to version 8 with on_hand 8. A blind overwrite to the old observed count 9 would erase the sale's effect.

Read the diagram

Count reads 10 units at stock version 7 → A sale of two units commits version 8 → Delayed stocktake tries to set quantity to 9 → Expected-version condition rejects stale overwrite → Reconcile evidence or recount before adjustment

View full sizeOpen original

Inventory Tracker: recovery decision

Reset viewClose

A count is evidence at a point in time, not a safe replacement for a later balance.

Use a conditional correction on the expected stock version and record count metadata/actor/reason. When it conflicts, recount or reconcile the exact movement interval if the physical-count timing and movement evidence support that calculation. For this clean exercise, if the count of 9 happened before the known sale of 2 and no other movement intervened, inferred current physical quantity would be 7; that conclusion requires those facts. Do not infer 7 from a mismatched version alone.

A count below outstanding reservations may violate on_hand >= reserved. Investigate, cancel/reallocate obligations under a declared policy or route to repair; do not silently create negative availability and hide it in the UI. Inventory accuracy and fulfillment promises are related but different business problems.

Handle duplicates and reversals explicitly

One unit remains. Registers A and B race to sell it. The authoritative predicate/transaction permits one accepted movement and one rejection. A's repeated key returns the same receipt; it cannot decrement again. A return is a new signed movement with a stable return identity linked to the sale and a policy limiting returned quantity. Deleting the original sale to “undo” it destroys audit evidence and makes reconciliation harder.

For a reservation, active→consumed and active→released compete at the same current identity/generation. The loser is a no-op/conflict. An old expiry message cannot release a new reservation. Record the operation's effects before reporting success; external payment and shipment remain separate workflows with their own uncertainty.

Offline registers need a budget, not a shared guess

If two disconnected registers each cache “one available,” both can sell the last unit locally. Safe offline sales require disjoint preallocated stock budgets or a product that explicitly accepts oversell risk. Allocation transfer between registers needs its own identity and reconciliation so the same unit is not simultaneously promised twice. Reconnection merges movements by stable identity, not by last-writer-wins stock total.

For a 10,000-SKU store receiving 20 sales/s with three lines on average, the initial workload is roughly 60 line decisions/s before restocks and retries. That does not establish the hottest SKU's capacity or a database benchmark. Use indexes for the store/SKU and operation lookup, short transactions and bounded connections; a distributed counter is not a substitute for enforcing a last-unit decision.

Reconcile and authorize the truth

Compare movement identities and per-SKU sums against stock versions and reservation state at a compatible checkpoint. Global totals can match while two SKUs are individually wrong. Keep immutable correction/reversal evidence and separate privileged adjustment permissions from ordinary sale access. Store staff should not mutate another store's stock by changing an ID.

Monitor conflict/retry rates, unavailable rejections, stale stocktakes, unexplained movement/balance differences, stuck reservations and offline reconciliation age. Protect scan/export queries from overwhelming checkout. A physical recount can reveal missing goods even when every software transaction is internally consistent; model that as a new authorized correction, not proof that history never mattered.

Inventory assessment: preserve movement meaning

Question: A reservation of 2 is released. Should on_hand increase by 2?

Question: A multi-line sale cannot fulfill B. May A's decrement remain committed under this exercise's contract?

Question: A stocktake expects v7 but the row is v8 after a sale. Is overwriting with the old count safe?

Score one point each for movement equations, local all-line atomicity, retry identity/audit, reservation terminal-state arbitration and stocktake/offline policy. Use Transactions and Isolation to defend concurrency and Cart and Checkout to extend this baseline into fulfillment. The example is a teaching model, not accounting integration or a physical inventory guarantee.

Decisions and alternatives

Operational contract

Monitor negative-stock invariant checks, adjustment frequency, rejected sales and reconciliation drift. Require privileged reasons for corrections. Test two concurrent registers against the last unit and replay the same sale key.

Choose alert thresholds from the agreed user-facing objective. Assign an owner for stuck work and a replay/repair procedure that preserves the original business identity. Bound queue depth, concurrency and recovery throughput so an outage does not produce an uncontrolled catch-up burst.

Foundation, senior and staff depth

• Foundation: Use stock conservation, immutable movements and integer quantities.
• Senior: Serialize all-line sales and version-check stocktake adjustments.
• Staff: Explain offline budgets, reservation reconciliation and audited correction history.

Practice before revealing the answer

Spend 45 minutes: 5 clarifying scope, 7 on capacity/APIs/state, 12 on the first architecture, 15 on the concurrency and failure examples, and 6 on trade-offs and operations. Draw your own design before comparing it with this chapter.

Question: Why is reading stock and then saving stock minus one unsafe?

Verification and rollout

Race sales for the final units and reject stale absolute stocktake writes. Replay sale/release identities and reject changed payloads. Verify all-line rollback, nonnegative available stock and conservation after returns and adjustments.

Primary technical reference

Official documentation for the mechanism used in this exercise. This lesson is an original interview design, not a description of undisclosed company internals.

Related study

Before this design, review these mechanisms and identify which owns the critical state transition:

• Data Modeling
• Dealing with Contention

Use the Interview Playbook to adapt the depth and timing, then return to the question breakdown overview.