Historian targets

HotLoop keeps its own history, and it isn’t going anywhere. But a plant already has a database. IT backs it up, patches it, points Power BI at it and answers for it in an audit, and they are not adopting ours to get a trend. So HotLoop writes history out to theirs as well.

Every reading the built-in historian keeps goes to every target: the same readings, after the same deadband, with the same quality. Not more, not fewer. A trend drawn from the target and one drawn in HotLoop agree, because they’re the same rows.

Targets are in HotLoop IoT, Edge and Gateway. Not the Edge Relay: it has no history of its own to catch a target up from, and a relay’s whole job is getting readings to a Gateway, which can write them anywhere.

Databases HotLoop writes to:

Kind	Versions run against	Details
PostgreSQL and TimescaleDB	PostgreSQL 16 with TimescaleDB 2.30	below
Microsoft SQL Server	SQL Server 2025 CU9	below
InfluxDB 2 and 3	InfluxDB 2.9.1 and InfluxDB 3 Core 3.12.0	below
MySQL and MariaDB	MySQL 26.7.0 and MariaDB 13.0.2	below

Adding one

Settings, Historian targets, Add a target, or POST /api/historian/targets (see API.md). It’s admin only, because a target is somewhere plant data goes, and deciding where is an admin’s call. Everybody who can read the settings sees the targets and how each one is doing.

A target is a kind, an address (host or host:port), a database, a table, and a login. The password is sealed with the same key as device credentials (HOTLOOP_SECRET_KEY_FILE, see BACKUP.md) and never comes back out of the API. With no key, a target with a password is refused: a database login in the clear in a backup is exactly what that key exists to stop.

HotLoop makes the table if it isn’t there. If it is there and isn’t one HotLoop made (it doesn’t have the six columns below), the target is down and says which columns are missing, rather than having every reading refused one at a time.

What lands in the table

The same six columns HotLoop’s own history has, so a query written for one reads the other:

Column	Details
`ts`	When the reading was taken, UTC, to the microsecond
`device_id`	The device
`tag_name`	The tag
`value`	The number, or empty for a text reading
`string_val`	The text, or empty for a number
`quality`	`good`, `bad` or `uncertain`

The primary key is (device_id, tag_name, ts), device and tag first because a trend asks for one tag over time. That key is also why sending a reading twice is harmless: it’s one row. The catch-up below leans on that on purpose.

When a target is down

This is the part that matters, so here’s exactly what happens.

Polling never waits for it. Readings go to a target through a queue in memory, as big as the built-in historian’s (HOTLOOP_HISTORIAN_QUEUE_SIZE). Handing a reading over never blocks. A full queue drops its oldest reading to make room, the same rule the built-in historian follows, and for the same reason: an unbounded queue on a pod with a memory limit is an OOM kill, and then you’ve lost the backlog, the live data and the process. A target behind a firewall that swallows every packet is tested to hold up nothing: not the poll, not the built-in historian, not a shutdown.

It catches up when it’s back. What waited in the queue goes first. What the queue had to drop goes next, from HotLoop’s own history, which already holds every one of those readings. There’s no second copy on disk to size or fill, and the catch-up works for as long as HotLoop keeps its history (HOTLOOP_HISTORIAN_RETENTION_DAYS, 90 days unless you changed it), not for as long as some buffer lasts.

A restart in the middle doesn’t forget. What a target is owed is a time range written in its row, not a pile of readings in memory. A clean stop writes down exactly what’s owed. A crash can’t, so the moment a target goes down or starts dropping, HotLoop writes down where it started, open-ended. The next start sends everything from there.

It says out loud what it can’t send. Two things can’t be caught up, and both go in the system log, with how many and when they were taken:

Readings older than HotLoop’s own retention by the time the target is back. They’re gone from HotLoop too. The log names the exact range.
Readings the target’s database refuses outright: a value its column can’t hold, a check constraint somebody put on the table. The batch is split until the readings it won’t take are found, so one bad reading never holds up the rest, and those are counted as refused. That’s said at most once a minute, because a tag the database can’t hold is refused on every poll.

A database that won’t let HotLoop in at all (wrong password, no insert permission, the table missing a column) is down, never “refusing”. The readings wait, and the moment somebody fixes it, they land. Only a refusal about the readings themselves is ever given up on.

Each step goes in the system log, under the subject historian/<id>:

warning  Historian target plant-pg (Plant historian) is down: dial tcp 10.20.0.9:5432: connect: connection refused.
         Readings wait in memory, up to 50000 of them; past that they're sent from HotLoop's own history when it's back.
info     Historian target plant-pg is back after 41m12s down. What waited in memory goes first.
warning  Historian target plant-pg couldn't keep up, and its queue dropped readings taken between
         2026-10-03T10:12:40Z and 2026-10-03T10:43:19Z (182211 readings dropped since HotLoop started).
         They go out from HotLoop's own history next.
info     Historian target plant-pg caught up: 182211 readings taken between 2026-10-03T10:12:40Z and
         2026-10-03T10:43:19Z went out from HotLoop's own history.

The Settings card shows the same thing live: the state (writing, catching up, down, off), why it’s down, how much is waiting, and what’s still owed.

The catch-up waits a minute behind the newest reading it’s owed, and for the built-in historian to be keeping up, because a reading still in that queue isn’t in the database yet. Reading past it would skip it for good.

Switching one off, changing one, removing one

Switched off, a target gets nothing and is owed nothing for the time it’s off. What it was already owed stays owed. A change (a new password, a new address) starts it over with the new settings, and what it was owed is still owed. Removing one forgets what it was owed. What it already holds stays in its database.

Only the HotLoop that’s serving the UI writes to targets. An --mcp-stdio helper running beside it doesn’t, or everything would go out twice.

PostgreSQL and TimescaleDB

{"id":"plant-pg","name":"Plant historian","kind":"postgres","address":"db.plant.local","database":"plant",
 "username":"hotloop","password":"...","useTls":true}

The port defaults to 5432. The table defaults to hotloop_history, and can carry a schema (history.hotloop). The schema has to exist.
If the database has the TimescaleDB extension, the table HotLoop makes is a hypertable on ts. If making it one fails, the table stays plain, it still takes every reading, and the system log says why.
The login needs to connect, create the table the first time (or you create it, with the six columns above), and insert into it. Nothing else. A login that can read the table but not insert into it is a target that’s down, not one refusing readings.
useTls checks the server’s certificate (sslmode=verify-full). insecureSkipVerify encrypts without checking it (sslmode=require). Without useTls the connection isn’t encrypted (sslmode=disable).
NaN and the infinities go in as they are: double precision holds them.
Statements aren’t prepared, so a PgBouncer in transaction mode in front of the database is fine.

The table, if you’d rather make it yourself:

CREATE TABLE hotloop_history (
    ts         timestamptz      NOT NULL,
    device_id  text             NOT NULL,
    tag_name   text             NOT NULL,
    value      double precision,
    string_val text,
    quality    text             NOT NULL,
    PRIMARY KEY (device_id, tag_name, ts)
);

Microsoft SQL Server

{"id":"plant-sql","name":"Plant historian","kind":"sqlserver","address":"sql.plant.local","database":"plant",
 "username":"hotloop","password":"...","useTls":true}

The port defaults to 1433, the table to hotloop_history in the dbo schema. A schema in front (history.hotloop) is used as given, and has to exist.
The database has to exist. HotLoop makes the table, not the database.
The table HotLoop makes has ts as datetime2(6), the names as nvarchar, value as float, string_val as nvarchar(max), and the primary key (device_id, tag_name, ts). That key is why a tag name can be 380 characters at most, and a longer one is refused as a reading and said so: a clustered key may be 900 bytes.
The login needs to connect, create the table the first time (or you create it, with those six columns), and insert into it. A login that can read the table but not insert is a target that’s down, not one refusing readings.
NaN and the infinities go in with their time and quality and no number. SQL Server’s float can’t hold them. The reading is there, and value is NULL.
SQL Server encrypts the login itself, so the password never crosses in the clear. useTls encrypts the readings too and checks the server’s certificate; insecureSkipVerify encrypts without checking it.
A reading already there is skipped, so sending one twice is one row.

The table, if you’d rather make it yourself:

CREATE TABLE dbo.hotloop_history (
    ts         datetime2(6)  NOT NULL,
    device_id  nvarchar(64)  NOT NULL,
    tag_name   nvarchar(380) NOT NULL,
    value      float         NULL,
    string_val nvarchar(max) NULL,
    quality    varchar(16)   NOT NULL,
    CONSTRAINT PK_hotloop_history PRIMARY KEY CLUSTERED (device_id, tag_name, ts)
);

MySQL and MariaDB

{"id":"plant-mysql","name":"Plant historian","kind":"mysql","address":"mysql.plant.local","database":"plant",
 "username":"hotloop","password":"...","useTls":true}

One kind for both: they speak the same protocol and take the same statements here, and both are run in the test suite.

The port defaults to 3306, the table to hotloop_history. A name with the database in front (other.hotloop) is used as given. The database has to exist; HotLoop makes the table, not the database.
The table HotLoop makes is InnoDB with ts as DATETIME(6) in UTC, value as DOUBLE, string_val as LONGTEXT, and the primary key (device_id, tag_name, ts). The names are utf8mb4_bin, because HotLoop’s tag names are case sensitive and the servers’ default collation isn’t: Temp and TEMP would be one key. That key is why a tag name can be 512 characters at most, and a longer one is refused as a reading and said so.
INSERT is the only privilege it needs once the table is there, and CREATE the first time if you’d rather HotLoop made it. It doesn’t read the table, and it doesn’t use ON DUPLICATE KEY UPDATE, which would ask for UPDATE too: a reading that’s already there is a duplicate on the primary key, and that is skipped.
NaN and the infinities go in with their time and quality and no number. DOUBLE can’t hold them. value is NULL.
A duplicate on some other unique index somebody added is not the same reading: it’s refused and said so, like a check constraint.
useTls encrypts and checks the server’s certificate; insecureSkipVerify encrypts without checking it. Without useTls the password still doesn’t cross in the clear (MySQL 8’s default login sends it encrypted with the server’s key), but anybody in the middle could swap that key, so say useTls for anything that isn’t on the plant’s own wire.

The table, if you’d rather make it yourself:

CREATE TABLE hotloop_history (
    ts         DATETIME(6)  NOT NULL,
    device_id  VARCHAR(64)  CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    tag_name   VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    value      DOUBLE       NULL,
    string_val LONGTEXT     CHARACTER SET utf8mb4 NULL,
    quality    VARCHAR(16)  NOT NULL,
    PRIMARY KEY (device_id, tag_name, ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

InfluxDB 2 and 3

{"id":"plant-influx","name":"Plant historian","kind":"influxdb","address":"influx.plant.local:8086",
 "org":"plant","database":"hotloop","password":"<an API token>","useTls":true}

The same target talks to InfluxDB 2 and to InfluxDB 3, over plain HTTP (/api/v2/write, line protocol, microsecond timestamps). Both answer it, so there is no driver to pin and nothing to choose.

The port defaults to 8086, InfluxDB 2’s. InfluxDB 3 listens on 8181 unless you changed it, so give the port.
database is the bucket. org is InfluxDB 2’s organization, and InfluxDB 2 wants one: without it every write is a 400 that says so, and the target is down with that message. InfluxDB 3 has no organizations, so leave it out.
password is the API token. It needs write on the bucket (InfluxDB 2) or on the database (InfluxDB 3). HotLoop doesn’t make the bucket. A bucket that isn’t there is a 404, which is the target down, not readings refused. InfluxDB 3 makes a database when one is written to.
table is the measurement, hotloop_history unless you say otherwise.
A reading is a point. The device and the tag are tags, so each HotLoop tag is one series. The number (value) or the text (string_val) and the quality (quality) are fields. Sending a reading twice overwrites itself.
NaN and the infinities go in with their time and quality and no value field. Line protocol has no way to write them.
InfluxDB holds a field’s type for good (per shard in 2, per column in 3). If something else already wrote a number into string_val or text into value, HotLoop’s readings of the other kind are refused, counted, and named in the system log, and the rest go in.
A 401 or 403 (a token it doesn’t know or one that can’t write) is the target down, not a refusal: nothing is thrown away, and the readings go in the moment the token is right.
useTls switches to https and checks the certificate; insecureSkipVerify doesn’t check it.

How it’s tested

Against the real database, in a container (test/services/up.sh starts it), with HotLoop’s real built-in historian on the other side. The outage is a real one: the target is reached through a TCP relay in the test that gets cut, plugged back in, or left connected to nothing that answers. The tests prove, for every kind:

the target holds exactly what HotLoop’s own history does, deadband and all, and writing everything twice adds nothing;
a cut while the plant keeps reading, far past the queue, ends with the target holding exactly what HotLoop holds, and the system log saying down, back, what was dropped and what was caught up;
a restart while it’s down, clean or a crash, catches up on the next start;
a target that hangs holds up neither the poll path nor the built-in historian nor a shutdown;
what’s past retention is named in the log, and the rest still goes.

And for each SQL database: a table somebody else made left alone, one refused reading not taking its batch with it, a login with the insert grant alone being enough, and a login without it being down rather than refusing, until the grant lands and everything with it. MySQL and MariaDB add tag names that differ only in case, and a unique index somebody else added. PostgreSQL adds the hypertable on TimescaleDB, and SQL Server a table in a schema and a tag name too long for its key. InfluxDB is run in both versions: a wrong token being down and not refusing, a missing bucket and a missing organization being down, a field somebody else typed differently being refused, and every character line protocol has to escape.