Configuring SQLite Database Administration on SIMATIC IOT2040

The SIMATIC IOT2040 is an industrial IoT gateway designed by Siemens for data acquisition, edge buffering, and simple SCADA-style telemetry at the machine level. With its Intel Quark x86 CPU, Yocto-based Linux, Node-RED pre-installed, and dual Ethernet ports, it is well suited to act as a local historian for shop-floor signals that cannot be pushed continuously to a higher-level MES or cloud platform. This reference walks through the practical design, build, and commissioning of a local SQLite database administration stack on the IOT2040, exposing Node-RED, Node.js, and Express as the application and presentation layers.

1. System Overview and Use Case

The IOT2040 is positioned between field devices (PLCs, sensors, energy meters) and higher-level systems. SQLite adds a self-contained, zero-config SQL store at the edge so a single node can:

• Buffer process values when uplink connectivity drops.
• Log alarm and event transitions with millisecond timestamps.
• Serve dashboards directly from the gateway without requiring a Windows host.
• Export CSV/JSON to engineering tools such as Eclipse for offline analysis.

2. Hardware and Firmware Prerequisites

3. Architecture and Component Topology

The application stack is a logical four-layer pipeline. Each layer communicates over loopback unless explicitly bound to an external NIC.

4. Installing SQLite on the IOT2040

1. Establish SSH access as the industrial user:
   ssh industrial@<iot2040-ip>
2. Verify the Yocto userland and architecture:
   uname -a; cat /etc/os-release
3. Install the SQLite command-line shell and the node-sqlite3 bindings (use the Siemens-supplied opkg feed when present, otherwise deploy the pre-built binary bundle via USB):
   opkg update && opkg install sqlite3 node-sqlite3
4. Confirm versions and capture for the asset register:
   sqlite3 --version
node -e "console.log(require('better-sqlite3/package.json').version)" (or sqlite3 package version)
5. Place the data file on the external media to avoid wearing the internal eMMC. Mount the USB or SD card, then create the database directory:
   mkdir -p /media/usb/db && cd /media/usb/db
6. Initialise a writable database:
   sqlite3 historian.db "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;"
7. Set file permissions so the Node-RED and Express daemons can both read and write:
   chown -R industrial:industrial /media/usb/db && chmod 750 /media/usb/db

5. Database Schema for Process Data

Design the historian schema for write-once-tag-many, not for OLTP. Three tables cover the common case and map cleanly to Node-RED msg.payload structures.

-- tags: static description of each datapoint
CREATE TABLE tags (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE,
  unit        TEXT,
  min_value   REAL,
  max_value   REAL,
  description TEXT
);

-- samples: time-series of measurements
CREATE TABLE samples (
  ts     INTEGER NOT NULL,    -- epoch ms (UTC)
  tag_id INTEGER NOT NULL,
  value  REAL NOT NULL,
  q      INTEGER NOT NULL,    -- quality code 0=Good, 1=Bad, 2=Uncertain
  PRIMARY KEY (ts, tag_id),
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
CREATE INDEX idx_samples_tag_ts ON samples(tag_id, ts);

-- events: alarm and operator actions
CREATE TABLE events (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  ts        INTEGER NOT NULL,
  severity  TEXT CHECK(severity IN ('INFO','WARN','ALARM','CRIT')),
  source    TEXT,
  message   TEXT
);

6. Node-RED Acquisition Flow

Node-RED is the IOT2040's primary edge integration tool. Use the sqlite node (pre-installed in recent Siemens images) or call the node-sqlite3 binding from a function node.

1. Open Node-RED at http://<iot2040-ip>:1880.
2. Drag in a Modbus/OPC UA input node, a function node for shaping, and a sqlite node bound to the /media/usb/db/historian.db file.
3. Insert the function node payload normaliser:
   const ts = Date.now(); const row = { ts, tag_id: msg.topic, value: Number(msg.payload), q: msg.q || 0 }; msg.payload = row; return msg;
4. Configure the sqlite node to perform INSERT OR REPLACE INTO samples(ts, tag_id, value, q) VALUES(?,?,?,?) with parameter binding.
5. Add an error catch node wired to a debug pane so connection drops land in the events table.

7. Node.js + Express Admin API

The Express layer exposes JSON endpoints for dashboards and converts SQL rows to CSV for engineering consumption. A minimal stack:

// server.js
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const app = express();
const db = new sqlite3.Database('/media/usb/db/historian.db');

app.use(express.static(path.join(__dirname, 'public')));

app.get('/api/tags', (req, res) => {
  db.all('SELECT id, name, unit FROM tags ORDER BY name', (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  });
});

app.get('/api/samples', (req, res) => {
  const { tag, from, to, limit = 1000 } = req.query;
  db.all(
    'SELECT ts, value, q FROM samples WHERE tag_id=? AND ts BETWEEN ? AND ? ORDER BY ts DESC LIMIT ?',
    [tag, +from, +to, +limit],
    (err, rows) => err ? res.status(500).json({ error: err.message }) : res.json(rows)
  );
});

app.get('/api/export.csv', (req, res) => {
  res.setHeader('Content-Type', 'text/csv');
  res.setHeader('Content-Disposition', 'attachment; filename="export.csv"');
  db.each('SELECT ts, tag_id, value, q FROM samples ORDER BY ts DESC LIMIT 50000', (err, row) => {
    if (err) return res.status(500).send(err.message);
    res.write(`${row.ts},${row.tag_id},${row.value},${row.q}\n`);
  });
  res.end();
});

app.listen(3000, '0.0.0.0', () => console.log('Admin API up on :3000'));

Run with a process manager so it survives crashes:

npm install -g pm2
pm2 start server.js --name iot-admin
pm2 save && pm2 startup

8. Eclipse-Based Development and Validation

Developers can author, lint, and unit-test the Node.js layer in Eclipse with the Node.js + npm extensions. Eclipse is also the tool of choice for offline trend analysis: open the exported CSV in the Eclipse Data Tools Platform or simply parse it through an Eclipse Java/Python JUnit suite that consumes the REST API. Because the IOT2040 file system is exposed to an engineering workstation via SFTP, source files in /home/industrial/iotsrv/ map directly to an Eclipse project — round-trip edits go through SSH.

9. Browser Dashboard (Node-RED UI)

Use the node-red-dashboard palette for a zero-build visualisation layer. Common widgets:

• ui_chart → polls /api/samples every 5 s.
• ui_table → renders the most recent alarm rows from events.
• ui_dropdown → bound to /api/tags for tag selector.

Authenticate the dashboard with node-red-contrib-auth and a 12-character minimum password — the IOT2040 sits inside the OT network but is reachable from the service network, so do not expose TCP/1880 without it.

10. Security Hardening and Patch Management

11. Verification and Commissioning Checklist

1. Insert a known tag with a step change and confirm a row exists:
   sqlite3 /media/usb/db/historian.db "SELECT * FROM samples ORDER BY ts DESC LIMIT 5;"
2. Hit the REST API and verify the JSON response from a PC:
   curl http://<iot2040-ip>:3000/api/tags
3. Export a CSV with sample data:
   curl -OJ http://<iot2040-ip>:3000/api/export.csv
4. Confirm WAL mode persists across reboots:
   sqlite3 /media/usb/db/historian.db "PRAGMA journal_mode;" → expect wal.
5. Apply a forced failover by unplugging the uplink NIC for 60 s; data must still be queriable from the gateway and queued for backfill.
6. Review pm2 logs and Node-RED debug pane for unhandled errors.

12. Troubleshooting Matrix

13. Operational Notes and Limits

• Single-process, single-writer is mandatory. SQLite is not safe for multiple processes writing concurrently with shared cache; use a tiny in-process queue and one writer.
• Keep the data file under ~50 GB. Beyond that the Quark's read amplification drops real-world query responsiveness.
• For audit trails consider the Oracle-style handbook orientation: Getting Started with Database Administration documents general DBA discipline that applies even to SQLite — backup schedule, retention policy, role separation.
• Plan capacity at 1 byte/row × samples/s × history window. Example: 50 tags × 1 Hz × 30 days ≈ 130 MB before indexes.