Multi-Warehouse Management for E-Commerce

Development of Multi-Warehouse Management for E-Commerce

One warehouse — simple task. Two or more — fundamentally different architecture. Multi-warehouse is needed when goods are stored at multiple physical locations: central warehouse + regional, own storage + dropship suppliers, retail store + offline points. The system's task — not just count inventory by each warehouse, but also route orders to optimal shipping source.

When It Becomes Necessary

• Store works with multiple suppliers, each stores their goods
• Several regional warehouses exist, delivery from nearest to buyer
• Part of goods is dropshipping, part is own warehouse
• Offline points participate in online order fulfillment (BOPIS — Buy Online, Pick Up In Store)

Data Schema

Extension of single-warehouse schema: warehouse_id dimension added:

CREATE TABLE warehouses (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    code        VARCHAR(50) NOT NULL UNIQUE,
    address     TEXT,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    priority    INTEGER NOT NULL DEFAULT 0,  -- routing priority
    type        VARCHAR(50) NOT NULL DEFAULT 'internal' -- internal | dropship | store
);

CREATE TABLE warehouse_stock (
    id           BIGSERIAL PRIMARY KEY,
    warehouse_id BIGINT NOT NULL REFERENCES warehouses(id),
    variant_id   BIGINT NOT NULL REFERENCES product_variants(id),
    stock_qty    INTEGER NOT NULL DEFAULT 0,
    reserved_qty INTEGER NOT NULL DEFAULT 0,
    UNIQUE (warehouse_id, variant_id),
    CHECK (stock_qty >= 0),
    CHECK (reserved_qty >= 0)
);

-- Aggregated stock across warehouses (materialized view or generated)
CREATE MATERIALIZED VIEW product_total_stock AS
SELECT
    variant_id,
    SUM(stock_qty)    AS total_stock,
    SUM(reserved_qty) AS total_reserved,
    SUM(stock_qty - reserved_qty) AS available_qty
FROM warehouse_stock
GROUP BY variant_id;

Materialized view updates after each change via trigger or REFRESH MATERIALIZED VIEW CONCURRENTLY on schedule (every minute).

Order Routing to Warehouse

This is key multi-warehouse business logic. Warehouse selection algorithm:

class WarehouseRouter
{
    public function resolve(OrderItem $item, Address $destination): Warehouse
    {
        $candidates = WarehouseStock::query()
            ->where('variant_id', $item->variant_id)
            ->whereRaw('stock_qty - reserved_qty >= ?', [$item->qty])
            ->with('warehouse')
            ->get()
            ->filter(fn($ws) => $ws->warehouse->is_active)
            ->sortBy([
                fn($a, $b) => $this->byProximity($a->warehouse, $destination) <=> $this->byProximity($b->warehouse, $destination),
                fn($a, $b) => $a->warehouse->priority <=> $b->warehouse->priority,
            ]);

        return $candidates->first()?->warehouse
            ?? throw new NoWarehouseAvailableException($item->variant_id);
    }
}

Routing strategies — configurable:

For most projects proximity + priority fallback sufficient.

Order Splitting by Warehouses

If goods from one order exist at different warehouses — order splits into multiple shipments:

class OrderSplitter
{
    public function split(Order $order): Collection
    {
        $assignments = collect();

        foreach ($order->items as $item) {
            $warehouse = $this->router->resolve($item, $order->delivery_address);
            $assignments->push([
                'warehouse' => $warehouse,
                'item'      => $item,
            ]);
        }

        return $assignments
            ->groupBy(fn($a) => $a['warehouse']->id)
            ->map(fn($group) => new Shipment($group));
    }
}

Customer sees one order, but internally — multiple shipments with different tracking numbers. Order status aggregated from shipment statuses: "completed" only when all shipments delivered.

Syncing Stock with Multiple Sources

Each warehouse can have its own inventory update channel:

• Internal warehouse: WMS via REST API or file exchange (XLSX, CSV)
• Dropshipper: their API with rate-limit, often non-standard format
• Offline point: POS system (1C Retail, iiko, r_keeper)

For each source — separate adapter:

interface WarehouseStockProvider
{
    public function fetchStock(Carbon $since): Collection; // variants + quantities
    public function confirmReservation(string $reservationId): bool;
    public function releaseReservation(string $reservationId): bool;
}

class MoyskladWarehouseProvider implements WarehouseStockProvider { ... }
class DropshipperApiProvider implements WarehouseStockProvider { ... }
class OneCWarehouseProvider implements WarehouseStockProvider { ... }

Synchronization runs independently for each provider on its own schedule:

* * * * * php artisan stock:sync --warehouse=central
*/5 * * * * php artisan stock:sync --warehouse=dropshipper-a
*/15 * * * * php artisan stock:sync --warehouse=store-minsk

Reservation in Multi-Warehouse Context

On reservation important to fix specific warehouse, not just variant:

CREATE TABLE stock_reservations (
    id           BIGSERIAL PRIMARY KEY,
    warehouse_id BIGINT NOT NULL REFERENCES warehouses(id),
    variant_id   BIGINT NOT NULL REFERENCES product_variants(id),
    order_id     BIGINT NOT NULL REFERENCES orders(id),
    qty          INTEGER NOT NULL,
    status       VARCHAR(50) NOT NULL DEFAULT 'active',
    expires_at   TIMESTAMP NOT NULL,
    created_at   TIMESTAMP NOT NULL DEFAULT NOW()
);

Atomic reservation operation — same UPDATE ... WHERE available >= qty on warehouse_stock, plus record in stock_reservations.

Warehouse Selection Widget for Pickup

If BOPIS enabled, customer selects pickup point. For this, map of points with real inventory needed:

GET /api/warehouses/availability?variant_ids[]=123&variant_ids[]=456

Response contains warehouse list with available_qty per product. Frontend builds point list and map (Leaflet, Yandex.Maps), filtering only those with complete order.

Warehouse Reporting

Analytics operations manager actually needs:

• Stock by warehouse in category breakdown
• Turnover (sold_qty / avg_stock per period)
• Inter-warehouse transfers (transfer orders)
• Deficit forecast by sales days

Basic turnover query:

SELECT
    w.name AS warehouse,
    pv.sku,
    ws.stock_qty,
    COALESCE(sales.sold_30d, 0) AS sold_30d,
    CASE
        WHEN COALESCE(sales.sold_30d, 0) = 0 THEN NULL
        ELSE ROUND(ws.stock_qty / (sales.sold_30d / 30.0))
    END AS days_of_stock
FROM warehouse_stock ws
JOIN warehouses w ON ws.warehouse_id = w.id
JOIN product_variants pv ON ws.variant_id = pv.id
LEFT JOIN (
    SELECT variant_id, SUM(qty) AS sold_30d
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.id
    WHERE o.completed_at >= NOW() - INTERVAL '30 days'
    GROUP BY variant_id
) sales ON sales.variant_id = pv.id;

Implementation Timeline

• Multi-warehouse schema + routing by priority: 5–7 days
• Order splitting + shipment management: 3–5 days
• Integration with one external source (1C, Moysklad): 3–5 days
• BOPIS with pickup map: +3–4 days
• Warehouse analytics reports: +2–3 days

Full multi-warehouse system for store with 2–5 storage points: 2–4 weeks.