Real-Time Inventory Tracking for E-Commerce: Beyond Batch Updates

Batch inventory updates — whether hourly, nightly, or even every 15 minutes — create a window where your store sells products it no longer has. The fix is not faster batching. It is treating every order, return, and warehouse receipt as an event that immediately updates a live inventory count, maintained in SQL with no custom code.

The Problem: Batch Inventory Creates Overselling

An e-commerce operation with 50,000 SKUs running hourly batch jobs can oversell a popular product hundreds of times in a single flash sale. The batch job reconciles at the top of the hour. By then, the damage is done: customer service calls, refunds, and a reputation hit.

The root cause is not the batch job itself. It is the architecture decision to treat inventory as a state that gets periodically recalculated rather than a value that changes incrementally with each event.

Modern e-commerce operations generate a stream of events — orders placed, orders cancelled, items shipped, returns received, warehouse stock received. Each one changes inventory. If you capture these events and process them as they arrive, you never need a batch job.

Why Existing Approaches Fall Short

Polling the transactional database adds load to your primary database and still introduces lag. Polling every 30 seconds at scale is operationally expensive and still not real-time.

Dedicated inventory management systems often have their own batch sync cycles and require custom integration middleware. They solve the user interface problem, not the data freshness problem.

Cache-based approaches (Redis counters, for example) work for simple decrement/increment but break down when you need to join inventory with product data, warehouse locations, or reservation holds. They also require careful cache invalidation logic and are difficult to audit.

Data warehouse + dbt keeps analytics fresh, but the latency is measured in minutes at best. Blocking a checkout on a warehouse query is not an option.

What you actually need is a system that processes event streams incrementally and maintains current state as a queryable, always-fresh materialized view.

The Streaming SQL Architecture

The architecture has three layers:

1. Event ingestion — Orders, cancellations, returns, and warehouse receipts publish to Kafka topics as they occur.
2. Streaming SQL processing — A streaming database consumes these Kafka topics and maintains materialized views of current inventory state.
3. Serving layer — Your application queries the materialized view for current stock levels, just like a regular database table.

No Spark jobs. No custom Python consumers. No scheduled reconciliation. The materialized view is always current because it updates incrementally with each incoming event.

Setting Up the Sources in RisingWave

RisingWave is a PostgreSQL-compatible streaming database, open source under the Apache 2.0 license, written in Rust, and designed to decouple compute from storage using S3. It speaks standard SQL, which means your inventory logic is readable and maintainable by any engineer who knows SQL.

First, create sources for your Kafka topics:

-- Orders topic: fired when a customer places an order
CREATE SOURCE orders_source (
    order_id       VARCHAR,
    product_id     VARCHAR,
    warehouse_id   VARCHAR,
    quantity       INT,
    event_time     TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'orders',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Cancellations topic: fired when an order is cancelled before shipment
CREATE SOURCE cancellations_source (
    order_id       VARCHAR,
    product_id     VARCHAR,
    warehouse_id   VARCHAR,
    quantity       INT,
    event_time     TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'order_cancellations',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Returns topic: fired when a returned item is received back at the warehouse
CREATE SOURCE returns_source (
    return_id      VARCHAR,
    product_id     VARCHAR,
    warehouse_id   VARCHAR,
    quantity       INT,
    event_time     TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'returns',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Receipts topic: fired when new stock arrives at a warehouse
CREATE SOURCE receipts_source (
    receipt_id     VARCHAR,
    product_id     VARCHAR,
    warehouse_id   VARCHAR,
    quantity       INT,
    event_time     TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'warehouse_receipts',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Computing Live Inventory with a Materialized View

Now combine all four event streams into a single, always-current inventory count per product per warehouse:

CREATE MATERIALIZED VIEW live_inventory AS
WITH inventory_events AS (
    -- Orders reduce inventory
    SELECT
        product_id,
        warehouse_id,
        -quantity AS quantity_delta,
        event_time
    FROM orders_source

    UNION ALL

    -- Cancellations restore inventory
    SELECT
        product_id,
        warehouse_id,
        quantity AS quantity_delta,
        event_time
    FROM cancellations_source

    UNION ALL

    -- Returns restore inventory
    SELECT
        product_id,
        warehouse_id,
        quantity AS quantity_delta,
        event_time
    FROM returns_source

    UNION ALL

    -- Warehouse receipts add inventory
    SELECT
        product_id,
        warehouse_id,
        quantity AS quantity_delta,
        event_time
    FROM receipts_source
)
SELECT
    product_id,
    warehouse_id,
    SUM(quantity_delta)            AS available_quantity,
    MAX(event_time)                AS last_updated_at
FROM inventory_events
GROUP BY product_id, warehouse_id;

Every time an event arrives on any of those Kafka topics, RisingWave updates the affected rows in live_inventory incrementally. There is no full recomputation. The materialized view is always consistent with the stream of events processed so far.

Querying Inventory from Your Application

Your application queries inventory exactly as it would a regular PostgreSQL table:

-- Check stock before allowing checkout
SELECT available_quantity
FROM live_inventory
WHERE product_id = 'SKU-98231'
  AND warehouse_id = 'WH-EAST-01';

-- Find all out-of-stock products across all warehouses
SELECT product_id, SUM(available_quantity) AS total_stock
FROM live_inventory
GROUP BY product_id
HAVING SUM(available_quantity) = 0;

-- Identify products below reorder threshold
SELECT
    li.product_id,
    li.warehouse_id,
    li.available_quantity,
    p.reorder_threshold
FROM live_inventory li
JOIN product_catalog p ON li.product_id = p.product_id
WHERE li.available_quantity < p.reorder_threshold;

Because RisingWave is PostgreSQL-compatible, your existing ORM, connection pool, and query tooling work without modification.

Adding Reservation Holds

Real checkouts often require a reservation step — hold the inventory while the customer completes payment, then confirm or release. You can model this with an additional reservation events source:

CREATE SOURCE reservations_source (
    reservation_id VARCHAR,
    product_id     VARCHAR,
    warehouse_id   VARCHAR,
    quantity       INT,
    status         VARCHAR, -- 'held' or 'released'
    event_time     TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'reservations',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW live_inventory_with_holds AS
SELECT
    li.product_id,
    li.warehouse_id,
    li.available_quantity,
    COALESCE(r.held_quantity, 0)                              AS held_quantity,
    li.available_quantity - COALESCE(r.held_quantity, 0)     AS sellable_quantity
FROM live_inventory li
LEFT JOIN (
    SELECT
        product_id,
        warehouse_id,
        SUM(CASE WHEN status = 'held' THEN quantity
                 WHEN status = 'released' THEN -quantity
                 ELSE 0 END) AS held_quantity
    FROM reservations_source
    GROUP BY product_id, warehouse_id
) r ON li.product_id = r.product_id
   AND li.warehouse_id = r.warehouse_id;

Now sellable_quantity reflects true available-to-sell inventory that accounts for in-flight checkouts.

Comparison: Batch vs. Streaming Inventory

Operational Considerations

Exactly-once semantics. Kafka + RisingWave can be configured for at-least-once delivery. Ensure your event producers include idempotency keys so duplicate events do not double-count inventory changes. The order_id on an order event is a natural deduplication key.

Historical replay. Because Kafka retains the event log, you can rebuild the materialized view from any point in time by adjusting the consumer offset. This is your audit trail and your disaster recovery path.

Alerts. You can push low-stock and stockout alerts to another Kafka topic from RisingWave using a sink:

CREATE SINK low_stock_alerts
FROM live_inventory
WHERE available_quantity < 10
WITH (
    connector = 'kafka',
    topic = 'low_stock_alerts',
    properties.bootstrap.server = 'kafka:9092'
)
FORMAT PLAIN ENCODE JSON;

Your alerting system subscribes to low_stock_alerts and notifies the merchandising team in real time.

Frequently Asked Questions

Q: What happens if RisingWave restarts? Is inventory data lost? RisingWave persists materialized view state to S3-compatible object storage. On restart, it recovers its internal state and resumes consuming from the last committed Kafka offset. No inventory data is lost and no manual reconciliation is needed.

Q: Can this replace our existing inventory management system? The streaming SQL layer handles the real-time computation of inventory state. It complements existing systems by providing a always-fresh read layer. Your existing IMS can continue to serve as the source of truth for product configuration, reorder workflows, and supplier management.

Q: How does this handle high-velocity flash sales? RisingWave processes events in micro-batches and scales horizontally. During a flash sale, the event rate increases but the architecture does not change. The materialized view stays consistent as long as the Kafka topic and RisingWave compute can absorb the throughput. Kafka partitioning by product_id helps distribute the load.

Q: What if an order event arrives out of order? For inventory tracking, strict ordering within a product's event stream is what matters. Kafka partitioning by product_id or warehouse_id gives you this guarantee. Cross-product ordering is not required for correct inventory counts.

Q: Is RisingWave difficult to operate? RisingWave separates compute from storage using S3, which simplifies operations significantly. There are no complex storage tiers to manage. Scaling compute up or down does not require data migration. The open-source version runs on Kubernetes or as a managed cloud service.