Inventory Management for E-Commerce

Development of Inventory Management for E-Commerce

Inventory management is not just a counter next to the product. It's a system that links the storefront to real physical stock, prevents sales of non-existent goods, and provides data for purchasing decisions. Without properly implemented inventory, the store operates blind.

What's Included in the Task

Minimum viable stock management system includes:

• storing quantity of units in warehouse with breakdown by variants (size, color, SKU)
• reserving stock at order placement until payment fact
• releasing reservation on cancellation or timeout expiration
• deducting when order transitions to "shipped" status
• threshold notifications: "running out" and "out of stock"
• displaying status on storefront without full page reload

Without reservation, two buyers simultaneously can buy the last copy — classic race condition. Without release reserve abandoned carts block inventory forever.

Data Schema

Basic structure for PostgreSQL:

CREATE TABLE product_variants (
    id          BIGSERIAL PRIMARY KEY,
    product_id  BIGINT NOT NULL REFERENCES products(id),
    sku         VARCHAR(100) NOT NULL UNIQUE,
    attributes  JSONB NOT NULL DEFAULT '{}',
    stock_qty   INTEGER NOT NULL DEFAULT 0,
    reserved_qty INTEGER NOT NULL DEFAULT 0,
    low_stock_threshold INTEGER NOT NULL DEFAULT 5,
    CHECK (stock_qty >= 0),
    CHECK (reserved_qty >= 0),
    CHECK (stock_qty >= reserved_qty)
);

CREATE TABLE stock_movements (
    id          BIGSERIAL PRIMARY KEY,
    variant_id  BIGINT NOT NULL REFERENCES product_variants(id),
    delta       INTEGER NOT NULL,
    type        VARCHAR(50) NOT NULL, -- 'reserve', 'release', 'deduct', 'restock'
    reference   VARCHAR(255),         -- order_id, shipment_id, etc.
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

Field available_qty = stock_qty - reserved_qty is calculated on-the-fly or via generated column. Table stock_movements — immutable log of all operations, necessary for audit and recovery on failure.

Reservation Without Race Conditions

Atomicity is ensured via SELECT ... FOR UPDATE or UPDATE ... RETURNING:

-- Atomic reservation
UPDATE product_variants
SET reserved_qty = reserved_qty + :qty
WHERE id = :variant_id
  AND (stock_qty - reserved_qty) >= :qty
RETURNING id, stock_qty, reserved_qty;

If row didn't return — insufficient stock. No SELECT before UPDATE, no application-level checks that lie under concurrent access.

For Laravel wrapped in transaction with pessimistic locking:

DB::transaction(function () use ($variantId, $qty, $orderId) {
    $variant = ProductVariant::lockForUpdate()->findOrFail($variantId);

    if ($variant->available_qty < $qty) {
        throw new InsufficientStockException($variantId, $qty);
    }

    $variant->increment('reserved_qty', $qty);

    StockMovement::create([
        'variant_id' => $variantId,
        'delta'      => -$qty,
        'type'       => 'reserve',
        'reference'  => "order:{$orderId}",
    ]);
});

Releasing Stuck Reserves

Buyer abandoned cart — reserve should return. Two approaches:

1. TTL via queue. When reserve created, job dispatched with delay:

ReleaseStockReservation::dispatch($reservationId)
    ->delay(now()->addMinutes(30));

If order paid before expiration — job checks status and exits without action.

2. Scheduled cleanup. Cron every 5 minutes finds expired reservations:

// app/Console/Commands/ReleaseExpiredReservations.php
StockReservation::where('expires_at', '<', now())
    ->where('status', 'pending')
    ->each(fn($r) => $r->release());

First approach more precise, second simpler in infrastructure. For most stores with load up to 1000 orders/day — scheduled cleanup sufficient.

Import and Sync with 1C / Warehouse System

If client has accounting system (1C Commerce, Moysklad, Megaplan), stock comes from outside:

• Webhook mode: warehouse calls /api/stock/update endpoint on change
• Pull mode: store requests stock diff every N minutes

For pull-mode, last_sync_at stored and only changed requested:

GET /api/moysklad/stock?changedSince=2025-03-01T00:00:00Z

On import important not to overwrite reserved_qty — only stock_qty. Otherwise sync will drop active reserves.

Stock Display on Storefront

Three presentation variants:

For high-traffic pages, status cached in Redis with TTL 60 seconds. Precision ±1 minute — acceptable compromise for most stores.

Low Stock Alerts

Purchasing manager must know in advance. Alert triggered in observer:

class ProductVariantObserver
{
    public function updated(ProductVariant $variant): void
    {
        if ($variant->wasChanged('stock_qty') && $variant->isLowStock()) {
            LowStockAlert::dispatch($variant);
        }
    }
}

Alert goes to email, Telegram or Slack — depends on client infrastructure.

Implementation Timeline

• Basic reservation + deduction + logging: 3–5 days
• Integration with 1C or Moysklad via API: +3–5 days
• Status caching via Redis + storefront display: +2 days
• Admin panel stock with filters and export: +2–3 days

Typical project: 1–2 weeks depending on external accounting system presence.