Chapter 7: Inventory Management Fundamentals

Why This Exists

E-commerce is the illusion of a digital transaction mapping perfectly to a physical reality. When a customer clicks "Buy," they expect a physical box to arrive at their door. Inventory management exists to ensure that the numbers in the database accurately reflect the physical boxes in a warehouse. Failing to manage inventory leads to "overselling" (selling items you don't have), which results in canceled orders, refund fees, furious customers, and a destroyed brand reputation.

Real World Problem

A merchant creates a product and sets the inventory to 100 in the database.

Over the weekend, they sell 20 items at a physical pop-up shop but forget to update the database.
During shipping, a warehouse worker drops a box, destroying 5 items.
A customer returns 2 items, but one is damaged and can't be resold. The database still says 100, but the physical reality is 76. The website sells 100 items. 24 customers are charged for products that do not exist.

Everyday Analogy

Think of a bank account. You don't just have one number representing your money. You have:

Ledger Balance: All the money officially in the account.
Available Balance: The money you can actually spend right now (minus pending credit card charges). If you look at the wrong number, you bounce a check. Inventory works exactly the same way.

Beginner Explanation

Inventory isn't just a single number; it's a lifecycle. When items arrive from a factory, you Receive them (add to stock). When a customer buys an item, you Allocate it (reserve it for them). When the warehouse ships the item, you Fulfill it (permanently deduct it). If the customer brings it back, you Return it (add it back to stock).

Intermediate Explanation

To solve the bank account problem, professional e-commerce systems separate inventory into three distinct states for every SKU:

On-Hand (The Physical Reality): The exact number of items physically sitting on the warehouse shelf. (e.g., 100)
Reserved / Allocated (The Pending State): Items that have been purchased by a customer but have not yet left the warehouse. (e.g., 10)
Available to Sell (ATS): The number the website actually uses to decide if a customer can click "Add to Cart." (e.g., 90).

Formula: ATS = On-Hand - Reserved

Advanced Explanation

Updating a single stock_count row in a SQL database is an anti-pattern at scale. It destroys historical data. Why did stock drop from 50 to 10? Was it a sale? A theft? A manual adjustment?

Modern architectures use Event Sourcing (an Inventory Ledger). Instead of updating a number, every change is appended as an immutable event to a ledger.

Event 1: RECEIVED_STOCK (+100, Reason: PO-44)
Event 2: ORDER_ALLOCATED (-2, Reason: Order-99)
Event 3: MANUAL_ADJUSTMENT (-1, Reason: Damaged Goods)

The current stock count is simply the sum of all ledger events. This provides a perfect audit trail and solves race conditions, as appending rows is faster and safer than locking and updating a single row.

Real World Example

Amazon Fulfillment Centers: Amazon takes inventory management further by tracking not just how many items exist, but where they are geographically. If you live in New York, Amazon's API might show an item as "Available," because it's in a New Jersey warehouse. If a user in California looks at the exact same item, it might show "Out of Stock" because the California warehouse is empty, and shipping it from NJ would ruin the unit economics. The inventory system is heavily integrated with the logistics network.

Architecture Design

Here is the flow of an Event-Sourced Inventory System:

graph TD
    Order[Checkout System] -->|Event: Order Placed| InvService[Inventory Service]
    Admin[Warehouse API] -->|Event: Stock Received| InvService
    
    InvService --> Ledger[(Inventory Ledger DB - Append Only)]
    
    Ledger -- Asynchronous Materialized View --> ATS_Cache[(ATS Cache - Redis)]
    
    Customer[Website] -->|Queries Stock| ATS_Cache

Database Design

1. The Inventory Ledger (Audit Trail):

CREATE TABLE inventory_ledger (
    id UUID PRIMARY KEY,
    sku VARCHAR(100),
    warehouse_id INT,
    quantity_change INT, -- e.g., +10 or -2
    transaction_type VARCHAR(50), -- 'ORDER', 'RECEIVE', 'ADJUST'
    reference_id VARCHAR(100), -- Order ID or PO ID
    created_at TIMESTAMP
);

2. The Materialized View (Fast Reads):

CREATE TABLE inventory_levels (
    sku VARCHAR(100) PRIMARY KEY,
    on_hand INT,
    reserved INT,
    available_to_sell INT -- Generated by logic
);

API Design

Adjusting Stock (Warehouse): POST /api/inventory/adjust Payload: { "sku": "TSH-RED", "change": 50, "reason": "RESTOCK" }

Checking Availability (Frontend): GET /api/inventory/availability?sku=TSH-RED Returns: { "available_to_sell": 12 }

Production Considerations

Caching Dangers: Caching inventory counts in a CDN or Redis for too long is the #1 cause of overselling. If a flash sale happens, the cache might say "100 Available" while the actual database is at 0. Inventory reads during the checkout phase must bypass caches and query the source of truth.
Idempotency: If the network drops and the checkout service sends the "Allocate Inventory" event twice, you will deduct double the stock. Every ledger entry must be idempotent (tied to a unique Order ID so duplicates are ignored).

Security Considerations

Negative Inventory Exploits: If not constrained, a system might allow ATS to drop to -5. This forces the business to cancel orders. Database-level CHECK (available_to_sell >= 0) constraints are the last line of defense against code bugs.
Unauthorized Adjustments: Only internal warehouse systems, authenticated via strict API keys, should be allowed to perform positive RESTOCK events.

Common Mistakes

Deleting Data: Using UPDATE inventory SET count = X. You lose all context of why the count changed.
Ignoring Multi-Warehouse: Hardcoding the system to assume all inventory sits in one location. When the business opens a second warehouse, the entire inventory architecture has to be rewritten.

Tradeoffs and Alternatives

Strict Consistency vs. Availability: In strict consistency, if the inventory database is down, nobody can check out. In a highly available (eventually consistent) system, you allow checkouts even if the exact inventory isn't known, accepting the risk that you might oversell and have to apologize to a small percentage of customers. Big companies often choose availability; lost sales cost more than refund apologies.

Interview Questions

Explain the difference between On-Hand, Reserved, and Available-to-Sell inventory.
Why is it dangerous to use a simple UPDATE SQL statement for inventory counts?
How would you design an inventory system to support multiple warehouses across different countries?

Hands-On Exercise

Assume a SKU starts with 0 inventory.
Write out the ledger entries (Quantity, Type, Reference) for the following timeline:
- Warehouse receives 50 units.
- Customer A buys 2 units.
- Customer B buys 1 unit.
- Customer A returns 1 unit in perfect condition.
Calculate the final On-Hand, Reserved, and ATS numbers assuming neither order has shipped yet.

Key Takeaways

Inventory management maps digital numbers to physical reality.
Never use a single number for inventory; track On-Hand, Reserved, and ATS separately.
Use Event Sourcing (an append-only ledger) to maintain a perfect audit trail of all inventory changes.
Caching inventory is dangerous during high-velocity sales.