The Inventory Pattern

Based on "Enterprise Patterns and MDA" by Jim Arlow & Ila Neustadt

Problem

A simple stockQuantity field on Product fails when you need:

• Multiple locations — Same product in warehouse A and warehouse B
• Reserved vs available — 100 on hand, but 30 reserved for pending orders
• Audit trail — Why did stock change? Who changed it?
• Stock movements — Transfers between locations
• Different tracking types — Some items serialized, some by quantity

Concept

Inventory is modeled as a ledger system. Every change is recorded as a transaction. The balance is the sum of all transactions.

Key entities:

• Location — Where stock is stored (hierarchical: Warehouse → Zone → Aisle → Bin)
• InventoryItem — Stock of a Product at a Location
• InventoryTransaction — Record of stock change (immutable)

Formula: Available = On Hand - Reserved

Structure

Quick Reference

Formula: Available = OnHand - Reserved

Examples

Receiving stock and fulfilling an order

warehouse = findLocation("WH-01-A-001")  // Bin in Warehouse 1
thinkpad = findProduct("LEN-X1C-G11-16-512")

// Get or create inventory item for this product at this location
item = findOrCreate InventoryItem(product: thinkpad, location: warehouse)

// Receive shipment from supplier
item.receive(20, "PO-2026-0015", "Supplier delivery")
// item.quantityOnHand = 20, available = 20
// emits StockReceived(item, 20, "PO-2026-0015")

// Customer order placed — reserve stock
item.reserve(1, "ORD-2026-000042", "Order reserved")
// item.quantityOnHand = 20, reserved = 1, available = 19
// emits StockReserved(item, 1, "ORD-2026-000042")

// Order shipped — issue from reserved
item.shipReserved(1, "ORD-2026-000042")
// item.quantityOnHand = 19, reserved = 0, available = 19
// emits StockIssued(item, 1, "ORD-2026-000042")

Stock count correction

// Physical count finds only 18 units (1 missing)
item.setCount(18, "Annual stock count - 1 unit unaccounted")
// Creates adjustment of -1
// item.quantityOnHand = 18
// emits StockAdjusted(item, -1, "STOCK_COUNT")

Transfer between locations

source = findInventoryItem(thinkpad, "WH-01-A-001")
destination = findInventoryItem(thinkpad, "WH-02-B-003")

InventoryService.transfer(source, destination, 5, "XFER-2026-001")
// source.quantityOnHand -= 5
// destination.quantityOnHand += 5
// emits StockTransferred(source, destination, 5, "XFER-2026-001")

Attributes

Location

Location Types

InventoryItem

Unique constraint: One InventoryItem per (product, location) pair.

InventoryTransaction

Behaviors

Entity Location:
    
    function isLeaf(): Boolean
        return not hasChildren()
    
    function getPath(): String
        // Returns "Warehouse 1 / Zone A / Aisle 01 / Bin 001"
        if parent is None:
            return name
        return parent.getPath() + " / " + name
    
    function getRoot(): Location
        if parent is None:
            return this
        return parent.getRoot()
    
    function canStoreInventory(): Boolean
        return isStockable and isActive


Entity InventoryItem:
    
    function getQuantityAvailable(): Integer
        return quantityOnHand - quantityReserved
    
    function needsReorder(): Boolean
        return reorderLevel is not None and quantityOnHand <= reorderLevel
    
    
    // === Stock operations ===
    
    function receive(quantity: Integer, reference: String, notes: String): Transaction
        require quantity > 0
        
        quantityOnHand = quantityOnHand + quantity
        
        tx = createTransaction(
            type: TransactionType.Receipt,
            quantity: +quantity,
            reference: reference,
            notes: notes
        )
        emit StockReceived(this, quantity, reference)
        return tx
    
    function issue(quantity: Integer, reference: String, notes: String): Transaction
        require quantity > 0
        require quantity <= quantityOnHand
        
        quantityOnHand = quantityOnHand - quantity
        
        tx = createTransaction(
            type: TransactionType.Issue,
            quantity: -quantity,
            reference: reference,
            notes: notes
        )
        emit StockIssued(this, quantity, reference)
        return tx
    
    function reserve(quantity: Integer, reference: String, notes: String): Transaction
        require quantity > 0
        require quantity <= getQuantityAvailable()
        
        quantityReserved = quantityReserved + quantity
        
        tx = createTransaction(
            type: TransactionType.Reserve,
            quantity: +quantity,  // Positive = reservation increased
            reference: reference,
            notes: notes
        )
        emit StockReserved(this, quantity, reference)
        return tx
    
    function release(quantity: Integer, reference: String, notes: String): Transaction
        require quantity > 0
        require quantity <= quantityReserved
        
        quantityReserved = quantityReserved - quantity
        
        tx = createTransaction(
            type: TransactionType.Release,
            quantity: -quantity,  // Negative = reservation decreased
            reference: reference,
            notes: notes
        )
        emit StockReleased(this, quantity, reference)
        return tx
    
    function shipReserved(quantity: Integer, reference: String): Transaction
        // Ship from reserved stock (order fulfillment)
        require quantity > 0
        require quantity <= quantityReserved
        
        quantityOnHand = quantityOnHand - quantity
        quantityReserved = quantityReserved - quantity
        
        return createTransaction(
            type: TransactionType.Issue,
            quantity: -quantity,
            reference: reference,
            notes: "Shipped from reserved stock"
        )
    
    function adjust(quantity: Integer, reference: String, notes: String): Transaction
        // Positive or negative adjustment
        newOnHand = quantityOnHand + quantity
        require newOnHand >= 0
        
        quantityOnHand = newOnHand
        
        tx = createTransaction(
            type: TransactionType.Adjustment,
            quantity: quantity,
            reference: reference,
            notes: notes
        )
        emit StockAdjusted(this, quantity, reference)
        return tx
    
    function setCount(actualCount: Integer, notes: String): Transaction
        // Physical count correction
        difference = actualCount - quantityOnHand
        if difference == 0:
            return None  // No change needed
        
        return adjust(difference, "STOCK_COUNT", notes)
    
    
    // === Transfer operations ===
    
    function transferOut(quantity: Integer, destinationCode: String, reference: String): Transaction
        require quantity > 0
        require quantity <= getQuantityAvailable()
        
        quantityOnHand = quantityOnHand - quantity
        
        return createTransaction(
            type: TransactionType.TransferOut,
            quantity: -quantity,
            reference: reference,
            notes: "Transfer to " + destinationCode
        )
    
    function transferIn(quantity: Integer, sourceCode: String, reference: String): Transaction
        require quantity > 0
        
        quantityOnHand = quantityOnHand + quantity
        
        return createTransaction(
            type: TransactionType.TransferIn,
            quantity: +quantity,
            reference: reference,
            notes: "Transfer from " + sourceCode
        )
    
    
    // === Helper ===
    
    private function createTransaction(
        type: TransactionType, 
        quantity: Integer, 
        reference: String, 
        notes: String
    ): Transaction
        tx = new InventoryTransaction(
            inventoryItem: this,
            type: type,
            quantity: quantity,
            balanceAfter: quantityOnHand,
            reference: reference,
            notes: notes,
            createdAt: now()
        )
        transactions.add(tx)
        return tx


// Service for multi-item operations
Service InventoryService:
    
    function transfer(
        source: InventoryItem, 
        destination: InventoryItem, 
        quantity: Integer, 
        reference: String
    ): Transaction[]
        require source.product == destination.product
        
        txOut = source.transferOut(quantity, destination.location.code, reference)
        txIn = destination.transferIn(quantity, source.location.code, reference)
        
        // Link the transactions
        txOut.relatedTransaction = txIn
        txIn.relatedTransaction = txOut
        
        emit StockTransferred(source, destination, quantity, reference)
        return [txOut, txIn]

Invariants

1. Non-negative quantities — onHand and reserved cannot be negative
2. Reserved ≤ OnHand — Cannot reserve more than physically present
3. Unique product-location — One InventoryItem per (product, location)
4. Transactions immutable — Never edit a transaction, only create adjustments
5. Balance reconcilable — Sum of transactions should equal current balance
6. Stockable locations only — InventoryItem only at stockable locations

Error Handling

Recovery: When a reservation fails due to insufficient stock, the caller should either:

1. Reserve a partial quantity and back-order the rest
2. Check other locations for available stock
3. Reject the order line with a clear stock-unavailable message

SQL Schema

sql-- Location hierarchy
CREATE TABLE location (
    id              UUID PRIMARY KEY,
    code            VARCHAR(30) NOT NULL UNIQUE,
    name            VARCHAR(100) NOT NULL,
    location_type   VARCHAR(20) NOT NULL,
    parent_id       UUID REFERENCES location(id),
    is_stockable    BOOLEAN NOT NULL DEFAULT TRUE,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_location_type CHECK (
        location_type IN ('Warehouse', 'Zone', 'Aisle', 'Rack', 'Shelf', 'Bin', 'Virtual')
    )
);

-- Inventory items (stock at location)
CREATE TABLE inventory_item (
    id                  UUID PRIMARY KEY,
    product_id          UUID NOT NULL REFERENCES product(id),
    location_id         UUID NOT NULL REFERENCES location(id),
    quantity_on_hand    INTEGER NOT NULL DEFAULT 0,
    quantity_reserved   INTEGER NOT NULL DEFAULT 0,
    reorder_level       INTEGER,
    max_level           INTEGER,
    updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(product_id, location_id),
    CONSTRAINT chk_non_negative_on_hand CHECK (quantity_on_hand >= 0),
    CONSTRAINT chk_non_negative_reserved CHECK (quantity_reserved >= 0),
    CONSTRAINT chk_reserved_lte_on_hand CHECK (quantity_reserved <= quantity_on_hand)
);

-- Inventory transactions (ledger)
CREATE TABLE inventory_transaction (
    id                  UUID PRIMARY KEY,
    inventory_item_id   UUID NOT NULL REFERENCES inventory_item(id),
    transaction_type    VARCHAR(20) NOT NULL,
    quantity            INTEGER NOT NULL,  -- Can be + or -
    balance_after       INTEGER NOT NULL,
    reference           VARCHAR(50),
    notes               TEXT,
    related_tx_id       UUID REFERENCES inventory_transaction(id),
    performed_by        VARCHAR(100),
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_transaction_type CHECK (
        transaction_type IN ('Receipt', 'Issue', 'Reserve', 'Release', 
                            'TransferOut', 'TransferIn', 'Adjustment', 'Return')
    )
);

-- Indexes
CREATE INDEX idx_location_parent ON location(parent_id);
CREATE INDEX idx_inventory_product ON inventory_item(product_id);
CREATE INDEX idx_inventory_location ON inventory_item(location_id);
CREATE INDEX idx_transaction_item ON inventory_transaction(inventory_item_id);
CREATE INDEX idx_transaction_reference ON inventory_transaction(reference) 
    WHERE reference IS NOT NULL;
CREATE INDEX idx_transaction_date ON inventory_transaction(created_at);

Common Queries

sql-- Total stock across all locations
SELECT 
    p.sku,
    p.name,
    SUM(ii.quantity_on_hand) as total_on_hand,
    SUM(ii.quantity_reserved) as total_reserved,
    SUM(ii.quantity_on_hand - ii.quantity_reserved) as total_available
FROM product p
LEFT JOIN inventory_item ii ON ii.product_id = p.id
WHERE p.id = :productId
GROUP BY p.id;

-- Stock by location for a product
SELECT 
    l.code as location_code,
    l.name as location_name,
    ii.quantity_on_hand,
    ii.quantity_reserved,
    ii.quantity_on_hand - ii.quantity_reserved as available
FROM inventory_item ii
JOIN location l ON l.id = ii.location_id
WHERE ii.product_id = :productId
ORDER BY l.code;

-- Items below reorder level
SELECT 
    p.sku,
    p.name,
    l.code as location,
    ii.quantity_on_hand,
    ii.reorder_level
FROM inventory_item ii
JOIN product p ON p.id = ii.product_id
JOIN location l ON l.id = ii.location_id
WHERE ii.reorder_level IS NOT NULL
  AND ii.quantity_on_hand <= ii.reorder_level;

-- Transaction history for audit
SELECT 
    it.*,
    p.sku,
    l.code as location
FROM inventory_transaction it
JOIN inventory_item ii ON ii.id = it.inventory_item_id
JOIN product p ON p.id = ii.product_id
JOIN location l ON l.id = ii.location_id
WHERE it.reference = :orderNumber
ORDER BY it.created_at;

-- Verify balance matches transactions (integrity check)
SELECT 
    ii.id,
    ii.quantity_on_hand as current_balance,
    SUM(CASE WHEN it.transaction_type IN ('Receipt', 'TransferIn', 'Return') 
             THEN it.quantity ELSE 0 END) +
    SUM(CASE WHEN it.transaction_type IN ('Issue', 'TransferOut') 
             THEN it.quantity ELSE 0 END) +
    SUM(CASE WHEN it.transaction_type = 'Adjustment' 
             THEN it.quantity ELSE 0 END) as calculated_balance
FROM inventory_item ii
LEFT JOIN inventory_transaction it ON it.inventory_item_id = ii.id
GROUP BY ii.id
HAVING ii.quantity_on_hand != calculated_balance;  -- Should return no rows

Design Considerations

Ledger Pattern Benefits

• Verifiable balance — Can always recalculate from transactions
• Complete audit trail — Every change is recorded
• No data loss — History preserved even after corrections

On Hand vs Reserved

Transaction Immutability

Never edit or delete transactions. For corrections:

1. Create an adjustment transaction
2. Reference the correction reason
3. Keep full audit trail

Extensions

Related Patterns

• Product — What is being tracked
• Order — Triggers reservations
• Shipment — Triggers issues (stock leaves warehouse)
• Location — Where stock is stored (defined within this pattern)