Automatic Product Stock Updates from Suppliers

Implementing Automatic Product Stock Updates from Suppliers

Stock data is the most volatile in an online store. A customer places an order but there's no inventory in stock. Or conversely: the product exists but is hidden due to zero stock in an outdated feed. Automatic stock data synchronization from suppliers solves this systematically, not with patches.

What Exactly Needs Synchronization

Stock is not just quantity. The complete picture includes:

• qty — units available in supplier warehouse
• warehouse — which warehouse (especially important for regional warehouses)
• available_date — expected arrival date if currently 0
• reserved — reserved for other orders
• status — discontinued, made-to-order, wholesale only

Minimal set for most stores: sku, qty, warehouse_id.

Stock Sources and Formats

CSV/Excel by Schedule

Most common — supplier places updated file on FTP once per hour:

class FtpStockSource implements StockSourceInterface
{
    public function fetch(): array
    {
        $ftp = ftp_connect($this->host);
        ftp_login($ftp, $this->user, $this->pass);
        $tmpFile = tempnam(sys_get_temp_dir(), 'stock_');
        ftp_get($ftp, $tmpFile, $this->remotePath, FTP_BINARY);
        ftp_close($ftp);

        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::load($tmpFile);
        $rows   = $reader->getActiveSheet()->toArray();
        unlink($tmpFile);

        $stocks = [];
        foreach (array_slice($rows, 1) as $row) { // skip header
            $stocks[] = [
                'sku' => (string) $row[0],
                'qty' => (int)    $row[2],
            ];
        }
        return $stocks;
    }
}

REST API with Delta Updates

Modern suppliers provide endpoint for incremental changes — only SKUs with changed stock since last request:

$response = $client->get('/stocks/delta', [
    'query' => ['since' => $this->lastSyncAt->toIso8601String()],
    'headers' => ['X-API-Key' => $this->apiKey],
]);
// Returns only changed items — saves traffic and processing time

Webhook from Supplier

If supplier can push changes:

// routes/api.php
Route::post('/webhooks/stock/{source}', StockWebhookController::class)
    ->middleware('webhook.signature');

class StockWebhookController
{
    public function __invoke(Request $request, string $source): JsonResponse
    {
        $payload = $request->validated();
        ProcessStockWebhookJob::dispatch($source, $payload);
        return response()->json(['status' => 'queued']);
    }
}

Webhook endpoint must respond in <200ms and immediately queue the task.

Stock Application Logic

class StockUpdater
{
    public function apply(array $stocks, int $sourceId): StockUpdateResult
    {
        $updated = $skipped = 0;

        // Bulk upsert in single query instead of N separate UPDATEs
        $chunks = array_chunk($stocks, 500);
        foreach ($chunks as $chunk) {
            $rows = [];
            foreach ($chunk as $item) {
                $productId = $this->skuMap[$item['sku']] ?? null;
                if (!$productId) { $skipped++; continue; }

                $rows[] = [
                    'product_id' => $productId,
                    'source_id'  => $sourceId,
                    'qty'        => max(0, $item['qty']),
                    'updated_at' => now(),
                ];
                $updated++;
            }

            if ($rows) {
                DB::table('product_stocks')->upsert(
                    $rows,
                    ['product_id', 'source_id'],
                    ['qty', 'updated_at']
                );
            }
        }

        return new StockUpdateResult($updated, $skipped);
    }
}

The upsert method is supported from Laravel 8 and works via INSERT ... ON CONFLICT DO UPDATE in PostgreSQL.

Aggregating Stock from Multiple Warehouses

If supplier maintains multiple warehouses, total site stock is either sum of all or selective:

-- View for storefront: total available stock
CREATE VIEW product_available_stock AS
SELECT
    product_id,
    SUM(qty) AS total_qty,
    MAX(updated_at) AS last_synced_at
FROM product_stocks
WHERE source_active = true
GROUP BY product_id;

If one warehouse "Moscow" is priority — can store warehouse_priority and take maximum priority when qty > 0.

Automatic Visibility Management

After stock update, recalculate product visibility:

class StockVisibilityObserver
{
    public function updated(ProductStock $stock): void
    {
        $totalQty = ProductStock::where('product_id', $stock->product_id)->sum('qty');

        Product::where('id', $stock->product_id)->update([
            'in_stock'    => $totalQty > 0,
            'stock_count' => $totalQty,
        ]);
    }
}

Instead of Observer, can use database trigger — faster but harder to test.

Update Frequency and Load

With frequent updates, it's important not to overload the database. Bulk upsert of 500 rows per query is optimal chunk size for PostgreSQL.

Implementation Timeline

• One source (CSV/FTP), scheduler, bulk upsert, visibility recalc — 2 days
• Multiple sources + warehouse aggregation — +1–2 days
• Webhook receipt + sync monitoring dashboard — +2 days

Sync Error Handling

If supplier doesn't respond or returns invalid file — don't zero out stock. Use TTL: if source data is older than max_age (e.g., 4 hours), mark those products as "data outdated" and show warning in admin, but don't touch qty on storefront.