How to Build a Real-Time Multi-Warehouse Inventory Reconciliation Engine

The Core Problem: Multi-Warehouse Inventory Drift

If you manage logistics across multiple regional distribution centers and fulfillment hubs in India, you know the frustration of inventory drift. A warehouse worker scans a pallet of goods into Hub A using a barcode scanner running on a local WMS (Warehouse Management System). Meanwhile, your central ERP (such as SAP, Increff, or Unicommerce) records stock availability based on nightly batch syncs or delayed API webhooks.

For several hours every day, physical stock levels disagree with your central database. Orders get accepted for items that were already damaged in transit, or stock sits idle in a staging bay because the central system does not know it arrived. When we run a Systems Audit & Blueprint for distribution clients, inventory discrepancy between the local scanner logs and central ERP ledgers is consistently one of the biggest drivers of unfulfilled orders.

In this guide, I will walk you through building a lightweight, real-time inventory reconciliation pipeline. We will use Redpanda for event streaming, DuckDB for fast in-memory micro-batch joins, PostgreSQL for state storage, and a specialized LLM agent to automatically triage and explain non-matching records.

Architecture Blueprint

Rather than relying on resource-heavy Spark clusters or expensive SaaS analytics tools, this architecture runs efficiently on a single moderate cloud instance (4 vCPU, 16 GB RAM) and processes thousands of stock transactions per second.

1. Event Ingestion (Redpanda)

We stream events from two main sources into Redpanda (a lightweight, Kafka-compatible event store):

• wms.scans: Real-time barcode scan records from warehouse hand-terminals.
• erp.ledger: Central financial and inventory allocation events generated whenever orders or transfers are created.

2. Micro-Batch Reconciliation Engine (DuckDB & Python)

A Python daemon pulls events from Redpanda into rolling 15-minute sliding windows. DuckDB joins incoming physical scans against expected ledger transactions in memory. Because DuckDB utilizes vectorized execution, it evaluates tens of thousands of rows in milliseconds without persistent database overhead.

3. Automated Exception Triage Agent

When DuckDB flags an un-reconciled record (e.g., physical scans show 90 units arrived, but the shipping ledger expected 100 units), the pipeline passes the context to a lightweight LLM agent. The agent checks surrounding context—such as historical supplier damage rates, scanner error logs, or transit time spikes—to assign a probable root cause tag (e.g., Partial Shortage in Transit or Duplicate Barcode Scan).

Step 1: Setting Up Event Streaming Streams

First, configure Redpanda to accept JSON payloads from your WMS scanners and ERP API endpoints. Here is what an incoming WMS physical scan event looks like:

{"event_id": "evt_8831a", "timestamp": "2026-03-29T10:14:22Z", "location_id": "BLR_HUB_02", "sku": "SKU-4902", "qty_scanned": 50, "scan_type": "INBOUND_RECEIPT"}

And here is the corresponding ERP expected delivery event:

{"order_id": "ORD-99104", "timestamp": "2026-03-29T09:30:00Z", "destination_id": "BLR_HUB_02", "sku": "SKU-4902", "qty_expected": 50, "status": "DISPATCHED"}

Step 2: Micro-Batch Matching with DuckDB

Using DuckDB's high-performance C++ execution engine, our Python consumer loads events into memory and executes a sliding window join every 30 seconds. DuckDB handles native JSON queries and temporal joins with minimal syntax.

The Matching Logic in Python

Here is the core logic that isolates mismatched stock records:

import duckdb
import pandas as pd

def reconcile_batch(wms_df, erp_df):
    con = duckdb.connect()
    con.register('wms_events', wms_df)
    con.register('erp_events', erp_df)

    query = """
    SELECT
        w.sku,
        w.location_id,
        SUM(w.qty_scanned) as physical_qty,
        COALESCE(SUM(e.qty_expected), 0) as expected_qty,
        (SUM(w.qty_scanned) - COALESCE(SUM(e.qty_expected), 0)) as discrepancy
    FROM wms_events w
    FULL OUTER JOIN erp_events e
        ON w.sku = e.sku AND w.location_id = e.destination_id
    GROUP BY w.sku, w.location_id
    HAVING physical_qty != expected_qty
    """
    return con.execute(query).fetchdf()

Step 3: Integrating the AI Exception Triage Agent

When discrepancies occur, sending raw alert logs to human operators creates operational fatigue. Most errors stem from a handful of repeatable causes. This is where an AI agent adds genuine value: classifying the anomaly and suggesting an immediate action.

When DuckDB outputs a record with a non-zero discrepancy, the pipeline constructs a prompt payload for the LLM containing:

• The calculated discrepancy numbers.
• The last 5 scanner events for that SKU at the specific location.
• Transit logs for the associated shipment bill.

The agent outputs structured JSON with a root cause probability and a recommended workflow action:

{"discrepancy_reason": "DUPLICATE_PALLET_SCAN", "confidence": 0.92, "action_required": "FLAG_FOR_SUPERVISOR_OVERRIDE", "explanation": "Two inbound scans recorded for SKU-4902 within 1.2 seconds on Terminal B2. Highly indicative of double-scanning a single barcode."}

Step 4: Operationalizing the Results

Once triaged, the reconciled records and flagged exceptions are written back to PostgreSQL. Matched records immediately update operational availability tables in Redis, allowing e-commerce storefronts and order management engines to promise stock accurately.

Exceptions identified as physical shortages automatically trigger a priority re-count ticket in the local WMS, while scans flagged as double-scans are resolved automatically without requiring human intervention.

Key Implementation Learnings

Building this pipeline across multi-warehouse environments revealed three essential operational considerations:

• Keep sliding window sizes realistic: A 15-minute window works well for cross-dock facilities, but manufacturing sites with multi-day receiving holds require a larger window or stateful tracking tables.
• Do not pass entire databases to the LLM agent: Only send the delta records flagged by DuckDB. Keeping the context window small keeps response times under 2 seconds per batch and minimizes API expenses.
• Enforce strict schema validation on streaming events: Malformed JSON payload strings from legacy scanner devices will break in-memory SQL execution. Use Pydantic or JSON schema validators before pushing events into Redpanda.

Batch syncs hide phantom stock until order fulfillment fails; micro-batch event analytics make cross-dock discrepancies visible before the delivery truck leaves.