← Raspberry Pi MySQL Alarm Logging with Node-RED and Laravel

Raspberry Pi MySQL Alarm Logging with Node-RED and Laravel — full example

Log industrial alarms to MySQL from Node-RED on a Raspberry Pi: event history plus per-input alarm definitions, queried by a Laravel web frontend.

Complete, runnable program for the Raspberry Pi (Docker) (alarm-history-mysql.sql): wiring header, requirements and integration notes included.

Read-only preview.

-- ============================================================================
-- COMPLETE EXAMPLE — Alarm history in MySQL
--
-- Hardware:  Raspberry Pi (Docker) with MySQL 8 + Node-RED + Laravel frontend
-- Based on:  automated agricultural irrigation project
--
-- Requirements:
--   - MySQL container from the stack (see docker-compose-irrigation.yml).
--   - node-red-node-mysql palette in Node-RED for the inserts.
--   - The password goes in the stack's MYSQL_PASSWORD environment variable.
--
-- Model:
--   alarms_describe  defines each possible alarm: which digital input it is
--                    tied to, the message to display and whether it is enabled.
--   alarms           event history: each trigger inserts a row with the
--                    device, the input, the message and the timestamp.
--   The Laravel frontend queries both tables for the listing and the counters.
-- ============================================================================

CREATE DATABASE IF NOT EXISTS irrigation
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE irrigation;

-- ----------------------------------------------------------------------------
-- Alarm definitions: one row per monitored digital input
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS alarms_describe (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  devices_id  CHAR(36)     NOT NULL,            -- device UUID (Raspberry Pi)
  input       VARCHAR(10)  NOT NULL,            -- digital input: I0, I1...
  message     VARCHAR(255) NOT NULL,            -- text shown to the user
  enabled     TINYINT(1)   NOT NULL DEFAULT 1,  -- 0 = alarm disabled
  UNIQUE KEY uq_device_input (devices_id, input)
);

-- ----------------------------------------------------------------------------
-- Alarm event history
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS alarms (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  devices_id  CHAR(36)     NOT NULL,
  input       VARCHAR(10),
  message     VARCHAR(255),
  timestamp   VARCHAR(255),                     -- ISO-8601 generated in Node-RED
  KEY idx_device_ts (devices_id, timestamp)
);

-- ----------------------------------------------------------------------------
-- Sample data: alarm definitions of the installation
-- ----------------------------------------------------------------------------
INSERT INTO alarms_describe (devices_id, input, message, enabled) VALUES
  ('00000000-0000-0000-0000-000000000001', 'I0', 'Irrigation pump thermal fault',      1),
  ('00000000-0000-0000-0000-000000000001', 'I1', 'Low pressure in irrigation network', 1),
  ('00000000-0000-0000-0000-000000000001', 'I2', 'Altivar 320 VFD fault',              1),
  ('00000000-0000-0000-0000-000000000001', 'I3', 'Minimum level in irrigation pond',   1),
  ('00000000-0000-0000-0000-000000000001', 'I4', 'Cabinet door open',                  0)
ON DUPLICATE KEY UPDATE message = VALUES(message);

-- ----------------------------------------------------------------------------
-- Queries used by the Laravel frontend
-- ----------------------------------------------------------------------------

-- Latest 50 alarms of a device (history screen)
SELECT a.input, a.message, a.timestamp
FROM alarms a
WHERE a.devices_id = '00000000-0000-0000-0000-000000000001'
ORDER BY a.id DESC
LIMIT 50;

-- Alarms by type over the last 30 days (bar chart)
SELECT a.input, d.message, COUNT(*) AS total
FROM alarms a
JOIN alarms_describe d
  ON d.devices_id = a.devices_id AND d.input = a.input
WHERE a.timestamp >= DATE_FORMAT(NOW() - INTERVAL 30 DAY, '%Y-%m-%dT%H:%i:%s')
GROUP BY a.input, d.message
ORDER BY total DESC;

-- ============================================================================
-- INSERT FROM NODE-RED (function node -> mysql node)
-- The mysql node executes msg.topic with the values from msg.payload.
-- It fires when a monitored digital input becomes active and its
-- definition is enabled in alarms_describe.
-- ============================================================================
-- // --- contents of the "insert alarm" function node ------------------------
-- const DEVICE_ID = "00000000-0000-0000-0000-000000000001";
--
-- // msg.alarm comes from the I/O tab: { input: "I1", message: "Low pressure..." }
-- msg.topic =
--     "INSERT INTO alarms (devices_id, input, message, timestamp) " +
--     "VALUES (:devices_id, :input, :message, :timestamp)";
--
-- msg.payload = {
--     devices_id: DEVICE_ID,
--     input: msg.alarm.input,
--     message: msg.alarm.message,
--     timestamp: new Date().toISOString(),   // same format Laravel reads
-- };
-- return msg;   // second output of the flow: function D of the Telegram bot
-- ------------------------------------------------------------------------