Smart Factory: Real-Time Production Line Monitoring

Real-time production line monitoring means tracking throughput, cycle times, downtime events, and quality defects at every station as they occur—not after a shift ends. RisingWave, a PostgreSQL-compatible streaming database, processes machine events continuously so factory operators always see the live state of the floor.

Why Production Line Monitoring Matters

Manufacturing lines generate enormous volumes of event data: PLC signals, conveyor speeds, part-count pulses, torque readings, and vision-system results. This data is gold—but only if you can act on it in real time.

A station that slows by 15% because of a worn tooling insert will degrade the entire line's throughput within minutes. Without real-time visibility, the slowdown isn't detected until the end-of-shift report shows a shortfall. By then, thousands of units of capacity have been lost.

Smart factories solve this by streaming every event into a continuous analytics layer. Line supervisors see live cycle times, live defect rates, and live downtime duration. Maintenance gets notified automatically when a machine exceeds its cycle-time budget. Quality holds are triggered before defective parts reach the next station.

How Streaming SQL Solves This

RisingWave connects directly to Kafka topics fed by edge PLCs, SCADA systems, or MES brokers. Materialized views compute KPIs incrementally—cycle times, station utilization, first-pass yield—and expose them as queryable tables with sub-second freshness.

Because RisingWave is PostgreSQL-compatible, your existing BI tools (Grafana, Tableau, Metabase) connect via standard JDBC/ODBC and query live KPIs without any API changes.

Step-by-Step Tutorial

Step 1: Connect Your Data Source

Production events flow from PLCs through a Kafka broker. Each event carries the station ID, event type, part serial, and timestamp.

CREATE SOURCE production_events (
    station_id    VARCHAR,
    event_type    VARCHAR,    -- 'CYCLE_START', 'CYCLE_END', 'FAULT', 'PART_OK', 'PART_NG'
    part_serial   VARCHAR,
    line_id       VARCHAR,
    shift_id      VARCHAR,
    event_time    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'production-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Build the Real-Time View

Compute cycle time by pairing CYCLE_START and CYCLE_END events for each station and part:

CREATE MATERIALIZED VIEW cycle_times AS
SELECT
    e.station_id,
    e.part_serial,
    e.line_id,
    e.shift_id,
    s.event_time                                              AS cycle_start,
    e.event_time                                              AS cycle_end,
    EXTRACT(EPOCH FROM (e.event_time - s.event_time))        AS cycle_seconds
FROM production_events e
JOIN production_events s
    ON  e.part_serial  = s.part_serial
    AND e.station_id   = s.station_id
    AND e.event_type   = 'CYCLE_END'
    AND s.event_type   = 'CYCLE_START';

Step 3: Window-Based Aggregations

Compute per-station throughput and average cycle time per 5-minute tumble window:

CREATE MATERIALIZED VIEW station_throughput_5min AS
SELECT
    station_id,
    line_id,
    window_start,
    window_end,
    COUNT(*)          AS parts_completed,
    AVG(cycle_seconds) AS avg_cycle_sec,
    MAX(cycle_seconds) AS max_cycle_sec,
    MIN(cycle_seconds) AS min_cycle_sec
FROM TUMBLE(cycle_times, cycle_end, INTERVAL '5 MINUTES')
GROUP BY station_id, line_id, window_start, window_end;

Track first-pass yield (FPY) per station per shift using a rolling window:

CREATE MATERIALIZED VIEW station_fpy AS
SELECT
    station_id,
    shift_id,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'PART_OK')  AS parts_ok,
    COUNT(*) FILTER (WHERE event_type = 'PART_NG')  AS parts_ng,
    COUNT(*)                                         AS total_parts,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE event_type = 'PART_OK') / NULLIF(COUNT(*), 0),
        2
    )                                                AS fpy_pct
FROM TUMBLE(production_events, event_time, INTERVAL '1 HOUR')
WHERE event_type IN ('PART_OK', 'PART_NG')
GROUP BY station_id, shift_id, window_start, window_end;

Step 4: Alerts and Sinks

Fire an alert when any station's average cycle time exceeds the takt time, or when FPY drops below 95%:

CREATE MATERIALIZED VIEW cycle_time_alerts AS
SELECT
    station_id,
    line_id,
    avg_cycle_sec,
    window_end AS detected_at,
    'CYCLE_TIME_EXCEEDED' AS alert_type
FROM station_throughput_5min
WHERE avg_cycle_sec > 45.0;   -- takt time threshold in seconds

CREATE MATERIALIZED VIEW fpy_alerts AS
SELECT
    station_id,
    shift_id,
    fpy_pct,
    window_end AS detected_at,
    'LOW_FPY' AS alert_type
FROM station_fpy
WHERE fpy_pct < 95.0;

CREATE SINK production_alerts_sink
FROM (
    SELECT station_id, line_id AS context, avg_cycle_sec AS metric_value, detected_at, alert_type FROM cycle_time_alerts
    UNION ALL
    SELECT station_id, shift_id AS context, fpy_pct AS metric_value, detected_at, alert_type FROM fpy_alerts
)
WITH (
    connector = 'kafka',
    topic = 'production-alerts',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

How do I handle shift boundaries in the aggregations?

Include shift_id as a grouping key (as shown in station_fpy). Your edge system or Kafka producer should stamp each event with the current shift identifier. Alternatively, derive it in a materialized view using a CASE expression on event_time and your shift schedule stored in a lookup table.

Can RisingWave ingest directly from PLCs without Kafka?

RisingWave currently requires a Kafka or Pulsar broker as an intermediary for streaming sources. Edge gateways or OPC-UA to Kafka bridges (such as Confluent's OPC-UA connector) handle the translation from PLC protocols.

How many production events per second can RisingWave handle?

RisingWave is horizontally scalable. Real deployments handle millions of events per second across distributed compute nodes. For a typical smart factory with thousands of events per second, a modest cluster is sufficient.

Key Takeaways

• RisingWave computes live cycle times, throughput, and first-pass yield by joining event pairs and applying tumble window aggregations—all in SQL.
• Alerts on cycle time overruns and quality drops materialize automatically and sink to Kafka for immediate notification.
• PostgreSQL compatibility means Grafana, Tableau, and other BI tools connect directly to live production KPIs without custom APIs.
• The entire monitoring pipeline—from PLC event to dashboard—runs in a single streaming database.

Get started with RisingWave | Join the community on Slack