Why Batch Inventory Systems Fail at Scale
Every e-commerce operations team has experienced this: a customer places an order, the system confirms it, and ten minutes later a support ticket arrives because the item was already out of stock. The batch job that updates inventory levels runs every 15 minutes, and in that window, three other customers bought the last units.
This is not a rare edge case. According to the IHL Group, inventory distortion (overstock and out-of-stock combined) costs retailers $1.8 trillion annually. The root cause is simple: traditional inventory systems rely on periodic batch queries against transactional databases, creating a gap between the real world and what the system believes is true.
A real-time inventory tracking system eliminates this gap. Instead of polling the database on a schedule, you process every order event, shipment, and return as it happens, maintaining continuously updated stock levels across all warehouses. In this tutorial, you will build exactly that using streaming SQL in RisingWave, a streaming database that lets you express complex event processing logic in standard SQL.
Architecture Overview
The real-time inventory system has four layers, each handled by streaming SQL:
- CDC ingestion - Capture every INSERT, UPDATE, and DELETE from your order database as a stream of events
- Streaming aggregations - Continuously compute current stock levels per product per warehouse
- Low-stock alerts - Materialized views that filter for products below reorder thresholds, with severity classification
- Multi-warehouse sync - Aggregated views across all warehouses for global inventory visibility and rebalancing
Here is the data flow:
graph LR
A[Order Database] -->|CDC| B[RisingWave Source]
B --> C[order_events table]
C --> D[current_stock_levels MV]
D --> E[low_stock_alerts MV]
D --> F[multi_warehouse_inventory MV]
E --> G[Alert Service / Slack]
F --> H[Inventory Dashboard]
RisingWave handles all four layers with standard PostgreSQL-compatible SQL. No JVM tuning, no custom serializers, no DAG configuration files. You write SQL, and RisingWave incrementally maintains the results as new events arrive.
Setting Up the Data Model
Reference Tables
First, define the reference data: products with their reorder thresholds and warehouse locations. These tables hold slowly changing data that the streaming pipeline joins against.
-- Products catalog with reorder thresholds
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR,
category VARCHAR,
reorder_threshold INT,
unit_price DECIMAL
);
-- Warehouse locations
CREATE TABLE warehouses (
warehouse_id INT PRIMARY KEY,
warehouse_name VARCHAR,
region VARCHAR
);
The reorder_threshold field on each product defines the minimum stock level before an alert fires. This is the business logic encoded directly in your data model.
INSERT INTO products VALUES
(1, 'Wireless Headphones', 'Electronics', 50, 79.99),
(2, 'USB-C Cable', 'Accessories', 200, 12.99),
(3, 'Laptop Stand', 'Accessories', 30, 49.99),
(4, 'Mechanical Keyboard', 'Electronics', 40, 129.99),
(5, 'Webcam HD', 'Electronics', 25, 59.99);
INSERT INTO warehouses VALUES
(1, 'East Coast Hub', 'US-East'),
(2, 'West Coast Hub', 'US-West'),
(3, 'Central Warehouse', 'US-Central');
The Order Events Stream
In a production system, this table would be backed by a Kafka source or a CDC connector reading directly from your PostgreSQL or MySQL order database. For this tutorial, we use a standard table to demonstrate the logic:
CREATE TABLE order_events (
event_id INT,
event_type VARCHAR, -- RECEIVED, SHIPPED, RETURNED
product_id INT,
warehouse_id INT,
quantity INT,
event_time TIMESTAMPTZ
);
Each row represents a single inventory movement. RECEIVED means stock arrived at a warehouse. SHIPPED means stock left for a customer. RETURNED means a customer sent items back. This event-sourced model captures the full history of every unit.
Let's load realistic order activity for a single day:
INSERT INTO order_events VALUES
(1, 'RECEIVED', 1, 1, 500, '2026-03-30 08:00:00+00'),
(2, 'RECEIVED', 1, 2, 300, '2026-03-30 08:05:00+00'),
(3, 'RECEIVED', 2, 1, 1000, '2026-03-30 08:10:00+00'),
(4, 'RECEIVED', 2, 2, 800, '2026-03-30 08:15:00+00'),
(5, 'RECEIVED', 2, 3, 600, '2026-03-30 08:20:00+00'),
(6, 'RECEIVED', 3, 1, 200, '2026-03-30 08:25:00+00'),
(7, 'RECEIVED', 3, 3, 150, '2026-03-30 08:30:00+00'),
(8, 'RECEIVED', 4, 1, 100, '2026-03-30 08:35:00+00'),
(9, 'RECEIVED', 4, 2, 80, '2026-03-30 08:40:00+00'),
(10, 'RECEIVED', 5, 2, 60, '2026-03-30 08:45:00+00'),
(11, 'SHIPPED', 1, 1, 460, '2026-03-30 10:00:00+00'),
(12, 'SHIPPED', 1, 2, 290, '2026-03-30 10:30:00+00'),
(13, 'SHIPPED', 2, 1, 820, '2026-03-30 11:00:00+00'),
(14, 'SHIPPED', 2, 2, 790, '2026-03-30 11:30:00+00'),
(15, 'SHIPPED', 2, 3, 595, '2026-03-30 12:00:00+00'),
(16, 'SHIPPED', 3, 1, 185, '2026-03-30 12:30:00+00'),
(17, 'SHIPPED', 3, 3, 140, '2026-03-30 13:00:00+00'),
(18, 'SHIPPED', 4, 1, 75, '2026-03-30 13:30:00+00'),
(19, 'SHIPPED', 4, 2, 65, '2026-03-30 14:00:00+00'),
(20, 'SHIPPED', 5, 2, 42, '2026-03-30 14:30:00+00'),
(21, 'RETURNED', 1, 1, 5, '2026-03-30 15:00:00+00'),
(22, 'RETURNED', 2, 2, 3, '2026-03-30 15:30:00+00');
This dataset models a typical pattern: morning receiving, daytime shipping, and a few afternoon returns.
Computing Real-Time Stock Levels
The core of the inventory system is a materialized view that continuously computes the current stock level for every product in every warehouse. A materialized view in RisingWave is not a cached query that needs manual refresh. It is an incrementally maintained computation: every time a new event arrives in order_events, RisingWave updates the result without re-scanning the entire table.
CREATE MATERIALIZED VIEW current_stock_levels AS
SELECT
p.product_id,
p.product_name,
p.category,
e.warehouse_id,
w.warehouse_name,
SUM(
CASE
WHEN e.event_type = 'RECEIVED' THEN e.quantity
WHEN e.event_type = 'RETURNED' THEN e.quantity
WHEN e.event_type = 'SHIPPED' THEN -e.quantity
ELSE 0
END
) AS current_stock,
p.reorder_threshold,
MAX(e.event_time) AS last_updated
FROM order_events e
JOIN products p ON e.product_id = p.product_id
JOIN warehouses w ON e.warehouse_id = w.warehouse_id
GROUP BY p.product_id, p.product_name, p.category,
e.warehouse_id, w.warehouse_name, p.reorder_threshold;
The CASE expression is the key. Received and returned items add to stock. Shipped items subtract. The SUM aggregation collapses all events for each product-warehouse pair into a single current stock number.
Query it like any PostgreSQL table:
SELECT product_name, warehouse_name, current_stock,
reorder_threshold, last_updated
FROM current_stock_levels
ORDER BY product_name, warehouse_name;
Result (verified on RisingWave 2.8.0):
product_name | warehouse_name | current_stock | reorder_threshold | last_updated
---------------------+-------------------+---------------+-------------------+---------------------------
Laptop Stand | Central Warehouse | 10 | 30 | 2026-03-30 13:00:00+00:00
Laptop Stand | East Coast Hub | 15 | 30 | 2026-03-30 12:30:00+00:00
Mechanical Keyboard | East Coast Hub | 25 | 40 | 2026-03-30 13:30:00+00:00
Mechanical Keyboard | West Coast Hub | 15 | 40 | 2026-03-30 14:00:00+00:00
USB-C Cable | Central Warehouse | 5 | 200 | 2026-03-30 12:00:00+00:00
USB-C Cable | East Coast Hub | 180 | 200 | 2026-03-30 11:00:00+00:00
USB-C Cable | West Coast Hub | 13 | 200 | 2026-03-30 15:30:00+00:00
Webcam HD | West Coast Hub | 18 | 25 | 2026-03-30 14:30:00+00:00
Wireless Headphones | East Coast Hub | 45 | 50 | 2026-03-30 15:00:00+00:00
Wireless Headphones | West Coast Hub | 10 | 50 | 2026-03-30 10:30:00+00:00
Every product in every warehouse is below its reorder threshold. This is a busy day.
Building Low-Stock Alerts with Severity Levels
Raw stock numbers are useful, but operations teams need prioritized alerts. A product at 90% of its threshold needs a different response than one at 5%. This is where materialized views layered on materialized views become powerful. RisingWave supports MV-on-MV chains, where downstream views automatically update when upstream views change.
CREATE MATERIALIZED VIEW low_stock_alerts AS
SELECT
product_id,
product_name,
warehouse_name,
current_stock,
reorder_threshold,
reorder_threshold - current_stock AS units_below_threshold,
CASE
WHEN current_stock = 0 THEN 'OUT_OF_STOCK'
WHEN current_stock <= reorder_threshold * 0.25 THEN 'CRITICAL'
WHEN current_stock <= reorder_threshold * 0.5 THEN 'LOW'
ELSE 'WARNING'
END AS alert_severity,
last_updated
FROM current_stock_levels
WHERE current_stock < reorder_threshold;
This view only includes products that are below their threshold (the WHERE clause filters at the streaming level). The severity tiers follow standard supply chain practice:
- OUT_OF_STOCK - Zero units remaining, immediate action required
- CRITICAL - Below 25% of threshold, likely to stock out within hours
- LOW - Below 50% of threshold, reorder should be initiated
- WARNING - Below threshold but still has buffer stock
SELECT product_name, warehouse_name, current_stock,
reorder_threshold, units_below_threshold, alert_severity
FROM low_stock_alerts
ORDER BY alert_severity, product_name;
product_name | warehouse_name | current_stock | reorder_threshold | units_below_threshold | alert_severity
---------------------+-------------------+---------------+-------------------+-----------------------+----------------
USB-C Cable | Central Warehouse | 5 | 200 | 195 | CRITICAL
USB-C Cable | West Coast Hub | 13 | 200 | 187 | CRITICAL
Wireless Headphones | West Coast Hub | 10 | 50 | 40 | CRITICAL
Laptop Stand | Central Warehouse | 10 | 30 | 20 | LOW
Laptop Stand | East Coast Hub | 15 | 30 | 15 | LOW
Mechanical Keyboard | West Coast Hub | 15 | 40 | 25 | LOW
Mechanical Keyboard | East Coast Hub | 25 | 40 | 15 | WARNING
USB-C Cable | East Coast Hub | 180 | 200 | 20 | WARNING
Webcam HD | West Coast Hub | 18 | 25 | 7 | WARNING
Wireless Headphones | East Coast Hub | 45 | 50 | 5 | WARNING
Three items are in CRITICAL status. USB-C Cable at Central Warehouse has only 5 units left against a 200-unit threshold: that is a stockout waiting to happen. In a production system, you would connect this materialized view to an alerting service using RisingWave's sink connectors to push notifications to Slack, PagerDuty, or a custom webhook.
Multi-Warehouse Inventory Sync
Individual warehouse views are essential for fulfillment teams, but supply chain managers need a global picture. The multi-warehouse materialized view aggregates stock across all locations and makes reordering decisions based on total inventory, not per-warehouse numbers.
CREATE MATERIALIZED VIEW multi_warehouse_inventory AS
SELECT
product_id,
product_name,
category,
COUNT(DISTINCT warehouse_id) AS warehouse_count,
SUM(current_stock) AS total_stock,
MIN(current_stock) AS min_warehouse_stock,
MAX(current_stock) AS max_warehouse_stock,
reorder_threshold,
CASE
WHEN SUM(current_stock) = 0 THEN 'OUT_OF_STOCK'
WHEN SUM(current_stock) < reorder_threshold THEN 'REORDER_NOW'
WHEN SUM(current_stock) < reorder_threshold * 2 THEN 'REORDER_SOON'
ELSE 'HEALTHY'
END AS overall_status
FROM current_stock_levels
GROUP BY product_id, product_name, category, reorder_threshold;
SELECT product_name, warehouse_count, total_stock,
min_warehouse_stock, max_warehouse_stock,
reorder_threshold, overall_status
FROM multi_warehouse_inventory
ORDER BY product_name;
product_name | warehouse_count | total_stock | min_warehouse_stock | max_warehouse_stock | reorder_threshold | overall_status
---------------------+-----------------+-------------+---------------------+---------------------+-------------------+----------------
Laptop Stand | 2 | 25 | 10 | 15 | 30 | REORDER_NOW
Mechanical Keyboard | 2 | 40 | 15 | 25 | 40 | REORDER_SOON
USB-C Cable | 3 | 198 | 5 | 180 | 200 | REORDER_NOW
Webcam HD | 1 | 18 | 18 | 18 | 25 | REORDER_NOW
Wireless Headphones | 2 | 55 | 10 | 45 | 50 | REORDER_SOON
This view reveals a pattern that per-warehouse views miss. USB-C Cable has 198 total units across three warehouses, but the distribution is severely imbalanced: 180 at East Coast Hub versus 5 at Central Warehouse. The min_warehouse_stock and max_warehouse_stock columns expose these imbalances, giving supply chain teams the data they need to trigger inter-warehouse transfers.
Three products are in REORDER_NOW status. Two more need reordering soon. This is the kind of insight that turns a reactive inventory operation into a proactive one.
Watching Real-Time Updates in Action
The real power of streaming SQL is visible when new events arrive. Let's simulate a shipment arriving at the East Coast Hub:
-- A new shipment of 100 Laptop Stands arrives
INSERT INTO order_events VALUES
(23, 'RECEIVED', 3, 1, 100, '2026-03-31 09:00:00+00');
Within seconds, query the stock levels:
SELECT product_name, warehouse_name, current_stock, reorder_threshold
FROM current_stock_levels
WHERE product_name = 'Laptop Stand'
ORDER BY warehouse_name;
product_name | warehouse_name | current_stock | reorder_threshold
--------------+-------------------+---------------+-------------------
Laptop Stand | Central Warehouse | 10 | 30
Laptop Stand | East Coast Hub | 115 | 30
East Coast Hub jumped from 15 to 115 units. No batch job ran. No cache was invalidated. The materialized view updated incrementally as soon as the event was processed.
Check the alerts view:
SELECT product_name, warehouse_name, current_stock, alert_severity
FROM low_stock_alerts
WHERE product_name = 'Laptop Stand'
ORDER BY warehouse_name;
product_name | warehouse_name | current_stock | alert_severity
--------------+-------------------+---------------+----------------
Laptop Stand | Central Warehouse | 10 | LOW
The East Coast Hub alert disappeared automatically because stock is now above the reorder threshold. Central Warehouse remains in LOW status. Every downstream materialized view updated in cascade, driven by a single INSERT.
Connecting to Production Data Sources
The examples above use direct INSERTs for clarity, but a production deployment uses CDC (Change Data Capture) to stream events from your existing databases. RisingWave supports direct CDC connectors for PostgreSQL, MySQL, and several other databases, as well as ingestion from Apache Kafka topics.
Here is what a Kafka source definition looks like for order events:
CREATE SOURCE order_events_stream (
event_id INT,
event_type VARCHAR,
product_id INT,
warehouse_id INT,
quantity INT,
event_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'order-events',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;
With Debezium CDC from PostgreSQL, you can capture every change to your orders table without modifying application code. The streaming pipeline processes inserts, updates, and deletes, keeping your inventory views always in sync with the source of truth.
Performance Considerations
RisingWave processes inventory updates with single-digit millisecond latency for most workloads. Here are the key factors that affect performance:
- Join cardinality - The three-way join between events, products, and warehouses is efficient because products and warehouses are small lookup tables. RisingWave optimizes these as broadcast joins.
- Aggregation state - The
GROUP BYincurrent_stock_levelsmaintains state proportional to the number of unique product-warehouse pairs, not the number of events. Ten million events for 1,000 products across 10 warehouses still only produces 10,000 state entries. - MV-on-MV chains - Each downstream materialized view processes only the changes propagated from upstream, not the full dataset. When one product's stock changes, only that product's row is re-evaluated in
low_stock_alertsandmulti_warehouse_inventory. - Parallelism - RisingWave distributes streaming operators across available cores. For high-throughput scenarios (thousands of events per second), scale horizontally by adding compute nodes to your RisingWave cluster.
What is real-time inventory tracking with streaming SQL?
Real-time inventory tracking with streaming SQL is an approach to inventory management where stock levels are computed continuously as events occur, rather than in periodic batch jobs. A streaming database like RisingWave ingests order events (shipments received, orders shipped, customer returns) through CDC or message queues and maintains always-current stock levels using materialized views. This eliminates the delay between a transaction occurring and the inventory system reflecting it, reducing overselling and stockout incidents.
How does CDC work for inventory tracking?
CDC (Change Data Capture) monitors your transactional database's write-ahead log and streams every insert, update, and delete as an event. For inventory tracking, this means every order placed, shipment logged, or return processed in your order management system automatically flows into the streaming pipeline. RisingWave supports CDC from PostgreSQL, MySQL, and other databases either directly or through Debezium and Kafka. The key advantage is that you don't need to modify your existing application code to start streaming inventory events.
When should I use streaming SQL instead of batch ETL for inventory?
You should use streaming SQL for inventory when your business experiences revenue loss from stale inventory data. If customers regularly encounter out-of-stock items that the system shows as available, or if your warehouses overstock because reorder signals arrive too late, batch ETL is not fast enough. Streaming SQL is also the right choice when you operate multiple warehouses and need synchronized inventory views for order routing. The break-even point is typically when batch intervals of 15+ minutes cause measurable operational problems.
How does RisingWave compare to Apache Flink for inventory tracking?
RisingWave and Apache Flink can both handle streaming inventory workloads, but they differ in operational complexity. Flink requires Java/Scala expertise, JVM tuning, and a separate state backend (like RocksDB). RisingWave is a database: you write standard SQL, query results with any PostgreSQL client, and skip the infrastructure overhead of managing a distributed stream processing cluster. For inventory tracking specifically, RisingWave's PostgreSQL compatibility means your existing BI tools and dashboards can query live inventory data without custom connectors. See our detailed comparison of RisingWave and Flink for more.
Conclusion
Building a real-time inventory tracking system does not require a complex event processing framework or months of engineering time. With streaming SQL in RisingWave, you can:
- Ingest order events from existing databases via CDC or Kafka, with no application changes required
- Compute live stock levels using materialized views that update incrementally, delivering single-digit millisecond freshness
- Generate prioritized alerts with severity classification (CRITICAL, LOW, WARNING) that update automatically as stock changes
- Synchronize multi-warehouse inventory with global views that expose imbalances and trigger reorder decisions
- Chain materialized views so a single event cascades through the entire pipeline, from raw event to actionable alert
The SQL you saw in this tutorial runs unmodified on RisingWave 2.8.0. Every query, every materialized view, every result was verified against a live instance.
Ready to build your own real-time inventory system? Try RisingWave Cloud free, no credit card required. Sign up here.
Join our Slack community to ask questions and connect with other stream processing developers.
