Inventory System Design

Core Components

Inventory System:
├── Stock Management
│   ├── Stock levels
│   ├── Stock sources
│   └── Stock status
├── Reservations
│   ├── Cart reservations
│   ├── Order reservations
│   └── Reservation management
├── Sources
│   ├── Warehouse management
│   ├── Source priority
│   └── Source selection
└── Notifications
    ├── Low stock alerts
    ├── Out of stock alerts
    └── Back in stock alerts

Data Model

-- Stock source table
CREATE TABLE inventory_source (
    source_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    source_code VARCHAR(255) UNIQUE,
    is_active TINYINT(1) DEFAULT 1,
    country_id VARCHAR(2),
    city VARCHAR(255),
    street VARCHAR(255),
    postcode VARCHAR(255)
);

-- Stock table
CREATE TABLE inventory_stock (
    stock_id INT AUTO_INCREMENT PRIMARY KEY,
    stock_name VARCHAR(255),
    website_id INT DEFAULT 0
);

-- Stock source link
CREATE TABLE inventory_stock_source (
    stock_id INT,
    source_id INT,
    priority INT DEFAULT 0,
    PRIMARY KEY (stock_id, source_id)
);

-- Inventory quantity
CREATE TABLE inventory_source_item (
    source_item_id INT AUTO_INCREMENT PRIMARY KEY,
    source_id INT,
    sku VARCHAR(255),
    quantity DECIMAL(12,4),
    status VARCHAR(32) DEFAULT 'in_stock'
);

-- Reservation table
CREATE TABLE inventory_reservation (
    reservation_id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(255),
    stock_id INT,
    quantity DECIMAL(12,4),
    order_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Stock Operations

// Get stock quantity
public function getStockQty($sku)
{
    return $this->resourceModel->getSourceItemsBySku($sku)
        ->sum('quantity');
}

// Update stock
public function updateStock($sku, $sourceCode, $quantity)
{
    $sourceItem = $this->sourceItemFactory->create();
    $sourceItem->setSourceCode($sourceCode);
    $sourceItem->setSku($sku);
    $sourceItem->setQuantity($quantity);
    $sourceItem->setStatus($quantity > 0 ? 'in_stock' : 'out_of_stock');
    
    $this->sourceItemResource->save($sourceItem);
    
    // Trigger stock update event
    $this->eventManager->dispatch('inventory_source_item_save_after', [
        'source_item' => $sourceItem
    ]);
}

// Check availability
public function isAvailable($sku, $qty = 1)
{
    $availableQty = $this->getAvailableQty($sku);
    return $availableQty >= $qty;
}

public function getAvailableQty($sku)
{
    $stockQty = $this->getStockQty($sku);
    $reservedQty = $this->getReservedQty($sku);
    return $stockQty - $reservedQty;
}

Stock Status

// Stock status management
public function updateStockStatus($sku)
{
    $availableQty = $this->getAvailableQty($sku);
    
    if ($availableQty > 0) {
        $status = 'in_stock';
    } elseif ($availableQty > 0 && $this->isBackorderAllowed($sku)) {
        $status = 'in_stock'; // Backorder
    } else {
        $status = 'out_of_stock';
    }
    
    $this->updateStatus($sku, $status);
    
    // Notify if out of stock
    if ($status === 'out_of_stock') {
        $this->notificationService->outOfStock($sku);
    }
    
    // Notify if back in stock
    if ($status === 'in_stock' && $previousStatus === 'out_of_stock') {
        $this->notificationService->backInStock($sku);
    }
}

Source Selection Algorithm

// Source selection for order
public function selectSources($orderItems)
{
    $sources = $this->getActiveSources();
    $sourceSelections = [];
    
    foreach ($orderItems as $item) {
        $itemSelections = [];
        $remainingQty = $item->getQtyOrdered();
        
        // Sort sources by priority
        usort($sources, function($a, $b) {
            return $a->getPriority() - $b->getPriority();
        });
        
        foreach ($sources as $source) {
            if ($remainingQty <= 0) break;
            
            $sourceQty = $this->getSourceQty($source->getId(), $item->getSku());
            $allocateQty = min($remainingQty, $sourceQty);
            
            if ($allocateQty > 0) {
                $itemSelections[] = [
                    'source_id' => $source->getId(),
                    'qty' => $allocateQty
                ];
                $remainingQty -= $allocateQty;
            }
        }
        
        if ($remainingQty > 0) {
            throw new \Exception('Insufficient stock for ' . $item->getSku());
        }
        
        $sourceSelections[$item->getSku()] = $itemSelections;
    }
    
    return $sourceSelections;
}

Source Priority

// Priority-based selection
public function getSourcesByPriority($sku)
{
    return $this->sourceItemRepository->getList(
        $this->filterBuilder
            ->setField('sku')
            ->setValue($sku)
            ->create()
    )->setOrder('priority', 'ASC');
}

// Distance-based selection
public function getSourcesByDistance($sku, $address)
{
    $sources = $this->getActiveSources();
    
    $sourcesWithDistance = array_map(function($source) use ($address) {
        $distance = $this->calculateDistance(
            $address->getLatitude(),
            $address->getLongitude(),
            $source->getLatitude(),
            $source->getLongitude()
        );
        $source->setDistance($distance);
        return $source;
    }, $sources);
    
    usort($sourcesWithDistance, function($a, $b) {
        return $a->getDistance() - $b->getDistance();
    });
    
    return $sourcesWithDistance;
}

Reservation System

// Reserve inventory
class ReservationService
{
    public function reserve($sku, $qty, $orderId = null)
    {
        $reservation = $this->reservationFactory->create();
        $reservation->setSku($sku);
        $reservation->setQty($qty);
        $reservation->setOrderId($orderId);
        $reservation->setStockId($this->getStockId($sku));
        
        $this->reservationResource->save($reservation);
        
        // Update available quantity
        $this->updateAvailableQty($sku);
        
        return $reservation;
    }
    
    public function release($sku, $qty, $orderId = null)
    {
        $reservation = $this->findReservation($sku, $orderId);
        
        if ($reservation) {
            $newQty = $reservation->getQty() - $qty;
            
            if ($newQty <= 0) {
                $this->reservationResource->delete($reservation);
            } else {
                $reservation->setQty($newQty);
                $this->reservationResource->save($reservation);
            }
            
            $this->updateAvailableQty($sku);
        }
    }
    
    public function getReservedQty($sku)
    {
        return $this->reservationResource->getReservedQty($sku);
    }
}

Cart Reservation

// Reserve when adding to cart
public function addToCart($sku, $qty, $cartId)
{
    // Check availability
    if (!$this->isAvailable($sku, $qty)) {
        throw new \Exception('Insufficient stock');
    }
    
    // Reserve for cart
    $this->reservationService->reserve($sku, $qty, null);
    
    // Set expiration (e.g., 30 minutes)
    $this->setReservationExpiry($sku, $cartId, 30 * 60);
    
    // Add to cart
    $this->cartService->addItem($cartId, $sku, $qty);
}

// Release on cart expiration
public function releaseExpiredReservations()
{
    $expired = $this->getExpiredReservations();
    
    foreach ($expired as $reservation) {
        $this->reservationService->release(
            $reservation->getSku(),
            $reservation->getQty()
        );
    }
}

Order Reservation

// Reserve for order
public function reserveForOrder($order)
{
    foreach ($order->getItems() as $item) {
        $this->reservationService->reserve(
            $item->getSku(),
            $item->getQtyOrdered(),
            $order->getId()
        );
    }
}

// Release on order cancel
public function releaseOnCancel($order)
{
    foreach ($order->getItems() as $item) {
        $this->reservationService->release(
            $item->getSku(),
            $item->getQtyOrdered(),
            $order->getId()
        );
    }
}