How do you prevent overselling with concurrent inventory reservations?

Use pessimistic locking (SELECT ... FOR UPDATE) within a database transaction. The lock prevents concurrent reads of the same inventory row until the transaction commits. Check quantity_available (on_hand minus reserved) inside the lock, then increment quantity_reserved atomically. This guarantees that two concurrent orders for the last item cannot both succeed.

What is the reorder point formula and how is safety stock calculated?

Reorder Point = (average daily usage * lead time in days) + safety stock. Safety Stock = Z * standard deviation of daily usage * sqrt(lead time days). Z is the service level factor (1.65 for 95%, 2.33 for 99%). Safety stock protects against demand variability and supplier delays. When quantity_available drops below the reorder point, trigger a purchase order automatically.

What is the difference between quantity_on_hand, quantity_reserved, and quantity_available?

quantity_on_hand is the physical stock in the warehouse. quantity_reserved is stock committed to pending orders but not yet shipped. quantity_available = on_hand - reserved, representing stock that can be promised to new orders. When an order is placed, quantity_reserved increases. When shipped, both quantity_on_hand and quantity_reserved decrease by the same amount.

How do stock movements provide an audit trail?

Every inventory change (purchase receipt, sale, return, adjustment, transfer) creates a StockMovement record with the quantity delta, movement type, reference ID, and timestamp. This is an event sourcing pattern: the current inventory level is the sum of all movements. You can reconstruct stock levels at any point in time, trace discrepancies to their source, and produce audit reports for compliance.

What is cycle counting and why is it preferred over full physical inventory counts?

Cycle counting counts a subset of SKUs each day rather than all inventory at once. High-velocity items are counted more frequently (weekly), slow movers less often (monthly). Benefits: no shutdown of warehouse operations, discrepancies are caught earlier (smaller window of divergence), and staff learn to count accurately with practice. A full physical count requires stopping operations and is typically done only annually.

Low-Level Design: Inventory Management System — Stock Tracking, Reservations, and Replenishment

⏱ 5 min read

Core Entities

Product: product_id, sku (unique stock keeping unit), name, description, category_id, unit_cost, unit_price, weight_kg, dimensions_cm (JSON), is_active. Warehouse: warehouse_id, name, address, timezone, capacity_sqft. InventoryItem: item_id, product_id, warehouse_id, quantity_on_hand, quantity_reserved, quantity_available (computed: on_hand – reserved), reorder_point, reorder_quantity, last_counted_at. StockMovement: movement_id, product_id, warehouse_id, movement_type (PURCHASE, SALE, RETURN, ADJUSTMENT, TRANSFER_IN, TRANSFER_OUT), quantity (positive = in, negative = out), reference_id (order_id, transfer_id, etc.), performed_by, created_at. Reservation: reservation_id, product_id, warehouse_id, quantity, reference_type (ORDER, TRANSFER), reference_id, status (ACTIVE, FULFILLED, CANCELLED), expires_at, created_at. PurchaseOrder: po_id, supplier_id, warehouse_id, status (DRAFT, SUBMITTED, CONFIRMED, RECEIVED, CANCELLED), expected_delivery_date, line_items (JSON: [{product_id, qty, unit_cost}]).

Inventory Reservation and Stock Deduction

class InventoryService:
    def reserve_stock(self, product_id: int, warehouse_id: int,
                      quantity: int, reference_id: str,
                      reference_type: str, ttl_minutes: int = 30) -> Reservation:
        with self.db.transaction():
            item = self.db.query(
                'SELECT * FROM inventory_items WHERE product_id=:p AND warehouse_id=:w FOR UPDATE',
                p=product_id, w=warehouse_id
            )
            if not item or item.quantity_available < quantity:
                raise InsufficientStockError(
                    f'Available: {item.quantity_available if item else 0}, requested: {quantity}'
                )
            # Decrement available by incrementing reserved
            self.db.execute(
                'UPDATE inventory_items SET quantity_reserved = quantity_reserved + :q WHERE item_id = :id',
                q=quantity, id=item.item_id
            )
            reservation = Reservation(
                product_id=product_id, warehouse_id=warehouse_id,
                quantity=quantity, reference_id=reference_id,
                reference_type=reference_type,
                status=ReservationStatus.ACTIVE,
                expires_at=datetime.utcnow() + timedelta(minutes=ttl_minutes)
            )
            self.repo.save(reservation)
            return reservation

    def fulfill_reservation(self, reservation_id: int):
        with self.db.transaction():
            res = self.repo.get_reservation(reservation_id, lock=True)
            if res.status != ReservationStatus.ACTIVE:
                raise InvalidStateError()
            # Convert reservation to actual deduction
            self.db.execute(
                'UPDATE inventory_items SET quantity_on_hand = quantity_on_hand - :q, ' +
                'quantity_reserved = quantity_reserved - :q WHERE product_id=:p AND warehouse_id=:w',
                q=res.quantity, p=res.product_id, w=res.warehouse_id
            )
            res.status = ReservationStatus.FULFILLED
            self.repo.save(res)
            self._record_movement(res.product_id, res.warehouse_id,
                                  MovementType.SALE, -res.quantity, res.reference_id)

Reorder and Replenishment

Reorder point (ROP): the quantity at which a replenishment order should be placed. Formula: ROP = (average daily usage * lead time days) + safety stock. Safety stock = Z * stddev(daily_usage) * sqrt(lead_time_days). Z = 1.65 for 95% service level. Example: avg daily usage = 50 units, lead time = 7 days, stddev = 10, Z = 1.65. ROP = 50*7 + 1.65*10*sqrt(7) = 350 + 44 = 394 units. When quantity_available drops below reorder_point: trigger an automated replenishment workflow. Scheduled job (runs every hour): query InventoryItem where quantity_available < reorder_point AND no pending PurchaseOrder exists for this product. Create a draft PurchaseOrder with the preferred supplier (from ProductSupplier table). Notify the procurement team for approval. Economic Order Quantity (EOQ): optimal order size balancing order cost and holding cost. EOQ = sqrt(2 * annual_demand * order_cost / holding_cost_per_unit). Minimize total cost (order frequency cost + storage cost).

Multi-Warehouse Transfers and Adjustments

Warehouse transfer: move stock from warehouse A to warehouse B. Transfer record: TransferOrder (transfer_id, from_warehouse_id, to_warehouse_id, status (PENDING, IN_TRANSIT, RECEIVED, CANCELLED), line_items). On initiation: create a TRANSFER_OUT StockMovement at the source, deduct from source inventory. On receipt confirmation: create TRANSFER_IN StockMovement at destination, add to destination inventory. In-transit inventory: the quantity is neither at source nor destination during transit. Track in the TransferOrder. Cycle counting: periodic physical count of inventory. Rather than counting everything at once, count a subset of SKUs daily. High-velocity items counted weekly, slow movers monthly. On count: compare physical count to system quantity. Discrepancies create an ADJUSTMENT movement with the delta. Root cause logged (damage, theft, receiving error). Audit trail: every quantity change goes through a StockMovement record. This provides a complete audit log and allows reconstruction of inventory levels at any point in time (event sourcing pattern).