Real-Time Industrial Alarm Management with RisingWave

Real-time industrial alarm management with RisingWave means detecting process deviations, routing alarms by priority and area, tracking acknowledgment state, and suppressing nuisance alarms—all using streaming SQL that updates with sub-second latency. RisingWave, a PostgreSQL-compatible streaming database, provides the continuous query engine for ISA-18.2 compliant alarm pipelines.

Why Industrial Alarm Management Needs Streaming SQL

Industrial alarm management is a safety-critical discipline governed by ISA-18.2 (ANSI/ISA-18.2-2016). The standard defines alarm rationalization, priority classification, alarm rate management, and response time requirements. The Texas City refinery disaster and other industrial accidents have been directly linked to alarm flooding—operators overwhelmed by hundreds of simultaneous alarms, unable to identify and respond to the critical ones.

Effective alarm management requires real-time capabilities that traditional SCADA historians don't provide:

• Alarm rate monitoring: detecting when alarm counts per hour exceed ISA-18.2 thresholds (> 10 alarms/operator/hour is considered overloaded)
• Chattering alarm suppression: silencing alarms that toggle on/off more than three times in 10 minutes
• Standing alarm tracking: alarms that have been active for more than 24 hours without acknowledgment
• Consequence protection: ensuring high-priority alarms are never masked by alarm floods

Streaming SQL handles all of these continuously, in real time.

How Streaming SQL Solves This

RisingWave ingests alarm events from SCADA systems via Kafka and maintains materialized views for alarm state, alarm rate, chattering detection, and standing alarm tracking. Each view updates incrementally as new events arrive. Operators and safety systems query fresh alarm state at sub-second latency.

Step-by-Step Tutorial

Step 1: Connect Your Data Source

Alarm events from SCADA systems flow as Kafka messages:

CREATE SOURCE alarm_events (
    alarm_id      VARCHAR,
    tag_name      VARCHAR,
    alarm_class   VARCHAR,   -- 'PROCESS', 'EQUIPMENT', 'SAFETY'
    priority      INT,       -- 1=Critical, 2=High, 3=Medium, 4=Low
    state         VARCHAR,   -- 'ACTIVE', 'CLEARED', 'ACKNOWLEDGED', 'SUPPRESSED'
    area          VARCHAR,
    operator_id   VARCHAR,
    value         DOUBLE,
    setpoint      DOUBLE,
    description   VARCHAR,
    event_time    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'scada-alarms',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Build the Real-Time View

Track the current state of every alarm—active, cleared, or acknowledged:

CREATE MATERIALIZED VIEW active_alarm_state AS
SELECT DISTINCT ON (alarm_id)
    alarm_id,
    tag_name,
    alarm_class,
    priority,
    state,
    area,
    operator_id,
    value,
    setpoint,
    description,
    event_time AS last_state_change
FROM alarm_events
ORDER BY alarm_id, event_time DESC;

-- Filter to active, unacknowledged alarms for the operator view
CREATE MATERIALIZED VIEW operator_alarm_queue AS
SELECT
    alarm_id,
    tag_name,
    alarm_class,
    priority,
    area,
    description,
    value,
    setpoint,
    last_state_change,
    NOW() - last_state_change AS alarm_age
FROM active_alarm_state
WHERE state = 'ACTIVE'
ORDER BY priority ASC, last_state_change ASC;

Step 3: Window-Based Aggregations

Alarm rate monitoring per operator area per hour (ISA-18.2 KPI):

CREATE MATERIALIZED VIEW alarm_rate_per_hour AS
SELECT
    area,
    window_start,
    window_end,
    COUNT(*)                                           AS total_alarms,
    COUNT(*) FILTER (WHERE priority = 1)               AS critical_alarms,
    COUNT(*) FILTER (WHERE priority = 2)               AS high_alarms,
    COUNT(*) FILTER (WHERE state = 'ACTIVE')           AS active_alarms,
    COUNT(*) FILTER (WHERE state = 'ACKNOWLEDGED')     AS acknowledged_alarms
FROM TUMBLE(alarm_events, event_time, INTERVAL '1 HOUR')
WHERE state IN ('ACTIVE', 'ACKNOWLEDGED')
GROUP BY area, window_start, window_end;

Chattering alarm detection: alarms that toggle more than 3 times in 10 minutes:

CREATE MATERIALIZED VIEW chattering_alarms AS
SELECT
    alarm_id,
    tag_name,
    area,
    window_start,
    window_end,
    COUNT(*)                                          AS state_changes,
    COUNT(*) FILTER (WHERE state = 'ACTIVE')          AS activations,
    COUNT(*) FILTER (WHERE state = 'CLEARED')         AS clearances
FROM TUMBLE(alarm_events, event_time, INTERVAL '10 MINUTES')
GROUP BY alarm_id, tag_name, area, window_start, window_end
HAVING COUNT(*) > 3;

Standing alarm detection: alarms active for more than 24 hours:

CREATE MATERIALIZED VIEW standing_alarms AS
SELECT
    alarm_id,
    tag_name,
    alarm_class,
    priority,
    area,
    description,
    last_state_change,
    NOW() - last_state_change AS standing_duration
FROM active_alarm_state
WHERE state = 'ACTIVE'
  AND NOW() - last_state_change > INTERVAL '24 HOURS';

Step 4: Alerts and Sinks

Route alarms by priority to different notification channels and sink alarm management KPIs for reporting:

-- Critical alarms need immediate notification
CREATE MATERIALIZED VIEW critical_alarm_notifications AS
SELECT
    alarm_id,
    tag_name,
    area,
    description,
    value,
    setpoint,
    last_state_change,
    'CRITICAL_ALARM' AS notification_type
FROM active_alarm_state
WHERE priority = 1
  AND state = 'ACTIVE';

-- ISA-18.2 overload alert: more than 10 alarms per hour per area
CREATE MATERIALIZED VIEW alarm_flood_alerts AS
SELECT
    area,
    total_alarms,
    critical_alarms,
    window_end AS detected_at,
    'ALARM_FLOOD' AS alert_type,
    'Alarm rate exceeds ISA-18.2 limit of 10 alarms/hour' AS message
FROM alarm_rate_per_hour
WHERE total_alarms > 10;

CREATE SINK critical_alarms_sink
FROM critical_alarm_notifications
WITH (
    connector = 'kafka',
    topic = 'critical-notifications',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

CREATE SINK alarm_kpi_sink
FROM alarm_rate_per_hour
WITH (
    connector = 'kafka',
    topic = 'alarm-kpis',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

How does RisingWave handle alarm acknowledgment state tracking?

The DISTINCT ON pattern in active_alarm_state always reflects the latest event state per alarm. When an operator acknowledges an alarm, a new event with state = 'ACKNOWLEDGED' arrives and updates the view. The alarm is removed from operator_alarm_queue automatically.

Can I implement alarm shelving and suppression in RisingWave?

Yes. Maintain a CREATE TABLE of shelved or suppressed alarms. Join it in operator_alarm_queue to exclude shelved alarms from the display. Update the suppression table via standard SQL INSERT/DELETE as operators shelve or reinstate alarms.

How do I generate ISA-18.2 compliance reports?

Query alarm_rate_per_hour, chattering_alarms, and standing_alarms views with appropriate time filters. Because these views are PostgreSQL-compatible, you can connect any reporting tool—Grafana, Tableau, or a custom Python script—to generate shift, daily, or weekly ISA-18.2 KPI reports.

Key Takeaways

• RisingWave provides continuous alarm state tracking, alarm rate monitoring, chattering detection, and standing alarm identification—all ISA-18.2 required KPIs—through incrementally maintained materialized views.
• Alarm flood detection fires automatically when any area exceeds the ISA-18.2 recommended alarm rate threshold.
• Critical alarm notifications sink to Kafka with millisecond latency, enabling immediate routing to operator paging systems.
• The PostgreSQL-compatible interface connects alarm management data to any BI or reporting tool without custom adapters.

Get started with RisingWave | Join the community on Slack