Implementing Regional Warehouses and Delivery on a Website
Multiple warehouses in different cities accelerates delivery and reduces its cost for regional customers. However, without proper warehouse selection logic, the benefit turns into problems: items are reserved at a distant warehouse when there's stock nearby; delivery cost calculations run from the wrong city; inventory appears unaggregated.
System Tasks
- Store inventory separately for each warehouse
- When displaying a product, show total availability and the nearest warehouse to the buyer
- Calculate delivery from the appropriate warehouse, not a single central one
- When placing an order, reserve inventory at a specific warehouse
- Support partial order fulfillment from multiple warehouses
Data Schema
CREATE TABLE warehouses (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL,
address TEXT,
lat NUMERIC(10,7),
lng NUMERIC(10,7),
country CHAR(2) DEFAULT 'RU',
region_code VARCHAR(20), -- region code
is_active BOOLEAN DEFAULT TRUE,
priority SMALLINT DEFAULT 0 -- higher = preferred when conditions are equal
);
CREATE TABLE warehouse_stocks (
id BIGSERIAL PRIMARY KEY,
product_id BIGINT REFERENCES products(id),
warehouse_id BIGINT REFERENCES warehouses(id),
quantity INT NOT NULL DEFAULT 0,
reserved INT NOT NULL DEFAULT 0,
available INT GENERATED ALWAYS AS (GREATEST(quantity - reserved, 0)) STORED,
updated_at TIMESTAMP DEFAULT NOW(),
UNIQUE(product_id, warehouse_id)
);
CREATE INDEX idx_wstocks_product ON warehouse_stocks(product_id);
CREATE INDEX idx_wstocks_available ON warehouse_stocks(product_id, available) WHERE available > 0;
-- Stock reservation when creating an order
CREATE TABLE stock_reservations (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT REFERENCES orders(id),
order_item_id BIGINT,
warehouse_id BIGINT REFERENCES warehouses(id),
product_id BIGINT REFERENCES products(id),
quantity INT NOT NULL,
status VARCHAR(20) DEFAULT 'reserved', -- 'reserved', 'shipped', 'cancelled'
reserved_at TIMESTAMP DEFAULT NOW(),
expires_at TIMESTAMP -- auto-cancel after 30 min
);
Warehouse Selection for Customer
class WarehouseSelector
{
public function selectForDelivery(
int $productId,
int $quantity,
string $destinationCity,
): ?WarehouseSelectionResult {
// Warehouses with required quantity
$available = WarehouseStock::where('product_id', $productId)
->where('available', '>=', $quantity)
->with('warehouse')
->orderByDesc('warehouse.priority')
->get();
if ($available->isEmpty()) {
// Try split — take from multiple warehouses
return $this->splitWarehouseSelection($productId, $quantity);
}
// Sort by proximity to buyer
$coords = $this->geocoder->getCoords($destinationCity);
if ($coords) {
$sorted = $available->sortBy(function ($stock) use ($coords) {
return $this->haversineDistance(
$coords['lat'], $coords['lng'],
$stock->warehouse->lat, $stock->warehouse->lng,
);
});
return new WarehouseSelectionResult(
warehouse: $sorted->first()->warehouse,
isSplit: false,
);
}
// Geocoding failed — select by priority
return new WarehouseSelectionResult(
warehouse: $available->first()->warehouse,
isSplit: false,
);
}
private function haversineDistance(float $lat1, float $lng1, float $lat2, float $lng2): float
{
$R = 6371; // km
$dLat = deg2rad($lat2 - $lat1);
$dLng = deg2rad($lng2 - $lng1);
$a = sin($dLat / 2) ** 2
+ cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * sin($dLng / 2) ** 2;
return $R * 2 * atan2(sqrt($a), sqrt(1 - $a));
}
}
Delivery Calculation Considering Warehouse
class RegionalDeliveryCalculator
{
public function calculate(
array $cartItems,
string $destination,
): DeliveryResult {
// Group products by warehouse
$warehouseGroups = $this->groupByWarehouse($cartItems, $destination);
$allOptions = collect();
foreach ($warehouseGroups as $warehouseId => $items) {
$warehouse = Warehouse::find($warehouseId);
// Calculate delivery from specific warehouse
$request = new DeliveryRequest(
fromCity: $warehouse->city,
fromLat: $warehouse->lat,
fromLng: $warehouse->lng,
destination: $destination,
items: $items,
);
$options = $this->carrierCalculator->calculate($request);
// If multiple warehouses — sum delivery cost
if (count($warehouseGroups) > 1) {
$allOptions = $allOptions->merge(
$options->map(fn($o) => $o->withWarehouseNote($warehouse->city))
);
} else {
$allOptions = $allOptions->merge($options);
}
}
// Deduplication by delivery method — select best option
return new DeliveryResult(
options: $allOptions->groupBy('method')
->map(fn($g) => $g->sortBy('price')->first())
->values(),
);
}
private function groupByWarehouse(array $cartItems, string $destination): array
{
$groups = [];
foreach ($cartItems as $item) {
$warehouse = $this->selector->selectForDelivery(
$item->product_id,
$item->quantity,
$destination,
);
$warehouseId = $warehouse?->warehouse->id ?? $this->defaultWarehouse->id;
$groups[$warehouseId][] = $item;
}
return $groups;
}
}
Stock Reservation When Placing Order
class StockReservationService
{
public function reserve(Order $order): ReservationResult
{
$reservations = [];
DB::transaction(function () use ($order, &$reservations) {
foreach ($order->items as $item) {
// Find warehouse with required quantity
$stock = WarehouseStock::where('product_id', $item->product_id)
->where('available', '>=', $item->quantity)
->where('warehouse_id', $item->preferred_warehouse_id
?? $this->getNearestWarehouse($order->delivery_city, $item->product_id)->id
)
->lockForUpdate()
->first();
if (!$stock) {
throw new InsufficientStockException(
"Insufficient inventory at warehouse: SKU {$item->product->sku}"
);
}
// Increase reserved quantity
$stock->increment('reserved', $item->quantity);
$reservations[] = StockReservation::create([
'order_id' => $order->id,
'order_item_id' => $item->id,
'warehouse_id' => $stock->warehouse_id,
'product_id' => $item->product_id,
'quantity' => $item->quantity,
'expires_at' => now()->addMinutes(30),
]);
}
});
return new ReservationResult(reservations: $reservations);
}
public function cancelExpiredReservations(): int
{
$expired = StockReservation::where('status', 'reserved')
->where('expires_at', '<', now())
->get();
foreach ($expired as $reservation) {
DB::transaction(function () use ($reservation) {
WarehouseStock::where([
'product_id' => $reservation->product_id,
'warehouse_id' => $reservation->warehouse_id,
])->decrement('reserved', $reservation->quantity);
$reservation->update(['status' => 'cancelled']);
});
}
return $expired->count();
}
}
Displaying Inventory by Warehouses on Product Card
// API: availability by cities
public function stockByCity(int $productId): JsonResponse
{
$stocks = WarehouseStock::where('product_id', $productId)
->where('available', '>', 0)
->with('warehouse:id,name,city')
->get(['warehouse_id', 'available']);
return response()->json(
$stocks->map(fn($s) => [
'city' => $s->warehouse->city,
'warehouse' => $s->warehouse->name,
'qty' => $s->available,
])
);
}
// Component for availability by cities
const StockByCity: React.FC<{ productId: number }> = ({ productId }) => {
const { data } = useQuery(['stock', productId], () => fetchStockByCity(productId));
if (!data?.length) return <span className="text-red-500">Out of stock</span>;
return (
<details className="text-sm">
<summary className="cursor-pointer text-green-600 font-medium">
In stock — {data.length} city(ies)
</summary>
<ul className="mt-1 space-y-1 pl-3">
{data.map(s => (
<li key={s.warehouse} className="text-gray-600">
{s.city} — {s.qty} pcs.
</li>
))}
</ul>
</details>
);
};
Warehouse Transfers
If needed inventory exists only at a distant warehouse, the system can offer a transfer:
CREATE TABLE warehouse_transfers (
id BIGSERIAL PRIMARY KEY,
from_warehouse BIGINT REFERENCES warehouses(id),
to_warehouse BIGINT REFERENCES warehouses(id),
product_id BIGINT REFERENCES products(id),
quantity INT NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
transit_days SMALLINT,
created_at TIMESTAMP DEFAULT NOW()
);
Implementation Timeline
- Data schema + WarehouseStock + basic WarehouseSelector: 2 days
- RegionalDeliveryCalculator with coordinate support: 1–2 days
- StockReservationService + auto-cancellation: 1 day
- Stock availability API by cities + frontend component: 1 day
- Warehouse management interface in admin panel: 1 day
- Transfers between warehouses: +1–2 days
Total without transfers: 6–7 working days.