Scheduled reports

Added in 4.14.0. internal/reports runs operator-defined reports on a schedule: it queries the historian, renders the result as CSV and HTML, stores it on a volume, and optionally emails it. It is the counterpart of Ignition’s Reporting module for the part of that module a plant most often uses, a table of numbers delivered on time. Off by default (HOTLOOP_REPORTS_ENABLED).

What it is, and what it is not

• API only. There is no screen for it in this version. Reports are defined, run, listed, and downloaded through /api/reports. A UI is a separate piece of work.
• CSV and HTML only. No PDF. Producing a good PDF in pure Go, with no external binary (the runtime image has none, on purpose), means choosing a PDF library and a layout engine, and that is a decision of its own. The HTML file is self-contained and prints cleanly from any browser, which covers “I need a PDF” for now.
• Tables of aggregates, not free-form layouts. A report is a list of tags, a window, a bucket size, and a set of aggregations. It is not a page designer.
• One Gateway instance. The scheduler assumes it is the only thing running reports against this database. Two replicas would each fire every report. This chart runs one replica, and the reports volume is ReadWriteOnce.
• Not an alarm feature. Alarm email is internal/notify. Reports share its SMTP relay settings and nothing else.

A report definition

Definitions are operator-edited data, stored in the database like devices, tags, alarm definitions, and fleet members, and managed through the API. Example:

{
  "id": "line1-daily",
  "name": "Line 1 daily summary",
  "enabled": true,
  "tags": [
    {"deviceId": "press-01", "tagName": "Zone1_Temp"},
    {"deviceId": "press-01", "tagName": "Pressure"}
  ],
  "window": {"count": 1, "unit": "days", "end": "local_midnight"},
  "bucket": "hour",
  "aggregations": ["min", "max", "avg", "count"],
  "schedule": {"kind": "daily", "at": "03:00"},
  "format": "both",
  "recipients": ["ops@example.com"],
  "retain": 30
}

Field	Meaning
id	1 to 64 characters of letters, digits, dot, dash, or underscore, starting with a letter or digit. It names the report’s directory on the volume, so it is validated the way every other id in this app is.
name	Shown in the HTML title and the email subject. No control characters.
enabled	Whether the scheduler runs it. A disabled report can still be run by hand.
tags	1 to 50 device and tag pairs. Each must exist when the definition is saved, and must not be a string tag.
window	The span of history each run covers. See below.
bucket	window (one row per tag), hour, or day. Defaults to window.
aggregations	One or more of min, max, avg, first, last, or count, each at most once.
schedule	See below.
format	csv, html, or both. Defaults to csv.
recipients	Optional. Plain addresses only (ops@example.com, not Ops <ops@example.com>). At most 20.
retain	How many runs to keep. 1 to 1000. Defaults to 30.

PUT replaces the whole definition. A client can read a report, change one field, and send it back as it is: the read-only fields the server adds (nextRunAt, lastRun, lastRunAt, createdAt, and updatedAt) are accepted and ignored.

A definition that would produce more than 1000 buckets is refused when it is saved (hourly buckets over more than about 41 days, for example).

Windows

A window is relative to when the run happens.

You want	count	unit	end
The previous 24 hours	24	hours	run_time
The previous calendar day	1	days	local_midnight
The previous 7 whole days	7	days	local_midnight
A rolling week	7	days	run_time

• end is the moment the run starts, or, for local_midnight, the most recent local midnight at or before it. A run at 03:00 ending at local midnight covers whole calendar days.
• Hours are elapsed time. Days are calendar days in the plant timezone. A one-day window that ends at local midnight is the whole of one local day, which is 23 hours long on the day the clocks go forward and 25 on the day they go back. “The previous 24 hours” is always exactly 24 hours.
• A window is half-open: it includes its start and excludes its end. A reading stamped exactly at midnight belongs to the day that starts there. Two consecutive daily reports never both count a reading and never both miss it.
• A manual run and a catch-up run compute the window as of the moment they run.

Buckets

• window: one row per tag over the whole window.
• hour: one row per tag per local clock hour. A window that does not start on the hour begins with a shorter partial bucket, and one that does not end on the hour ends with one. Hours stay on the hour across a clock change and in zones whose offset is not a whole number of hours.
• day: one row per tag per local calendar day.

Every tag has a row for every bucket, including buckets with nothing in them. The report never omits the hour a sensor was silent, because a report that quietly left the row out would look the same as one for a sensor that had nothing to say. Each row states the exact span it covers (period_start and period_end, with their UTC offsets), so a 23-hour day shows as one.

Aggregations and the quality rule

The aggregations are min, max, avg, first (the earliest reading in the period by timestamp), last (the latest), and count.

Reports follow the quality rule, the same one the trend chart follows, so a report and a trend over the same hour agree. A reading with no numeric value (a string reading) is left out too. There is no switch for it: an average that quietly mixes in readings the historian itself marked as untrustworthy is worse than a report that says how many it did not use.

Every row therefore carries an excluded column: how many readings in that period were left out. count is how many were used. The run record and the email carry the totals.

Nothing is fabricated. A tag with no usable reading in a period has no minimum, and the report says so: the cells are empty in the CSV and read no data in the HTML. They are never zero. Zero is a reading (a valve closed, a flow of nothing) and is aggregated like any other value. count is the one column that is a number when there is no data, because zero is a true count of nothing.

Schedules and time

A schedule is one of:

{"kind": "interval", "every": "6h"}
{"kind": "daily",    "at": "03:00"}
{"kind": "weekly",   "weekday": "monday", "at": "06:30"}

• An interval is elapsed time (at least one minute) and ignores the wall clock.
• Daily and weekly times are wall-clock times in the plant timezone, which is HOTLOOP_TIMEZONE (the chart’s global.timezone). It must be a real zone name such as America/Chicago. An unusable name is a refused start, reports on or off, not a quiet fall back to UTC, because a report meant for 03:00 local that fires at 03:00 UTC is a bug nobody sees until the numbers are wrong.

Daylight saving

The next run is computed by building a wall-clock time in the plant’s location with time.Date. It is never computed by adding 24 hours to the previous run, which fires an hour early or an hour late from one clock change to the next. Two wall-clock times are awkward on a change day, and the rule for each is fixed in code rather than left to time.Date, whose behavior for both is documented as unspecified:

• A time that does not exist (02:30 on the spring-forward day, when the clock jumps from 02:00 to 03:00) fires at the first instant after the gap, 03:00. It runs late by at most the size of the gap and does not skip the day.
• A time that happens twice (01:30 on the fall-back day) fires once, at its first occurrence.

last_run_at, the next run, and catch-up

last_run_at is when the scheduler last started the report, whether that run then succeeded or failed. A manual run does not move it, so running a report by hand at 02:00 does not cancel tonight’s 03:00 run.

The next run is the first scheduled time after the latest of the definition’s creation, its last edit, and its last_run_at. Measuring from the last edit means moving a report from 03:00 to 09:00 at 10:00 waits for tomorrow instead of firing at once for a time that had already passed.

Catch-up. If the next run after last_run_at is already in the past when the Gateway starts, the Gateway was not there to make it, and the report runs once, immediately, labeled catchup. It is not run once per missed time: a daily report that missed three days runs once, on the day the Gateway comes back, with the window that means as of now. The missed days are not backfilled. After that the report is back on its schedule.

last_run_at is written before the run starts, so a Gateway that dies mid-run does not repeat the run on restart on top of an email that may already have gone out. The interrupted run is recorded as failed instead. A failed scheduled run is not retried; the next scheduled time runs normally, and POST /api/reports/{id}/run is there when someone wants it again.

Output

Each run writes its files under HOTLOOP_REPORTS_DIR (/data/reports, a PVC in the chart):

/data/reports/<report id>/<UTC timestamp>-<run id>.csv
/data/reports/<report id>/<UTC timestamp>-<run id>.html

• File names are built only from a validated id, a UTC timestamp, and the run’s integer id. Path traversal is closed off by construction: the report id must satisfy the same identifier rule as everywhere else, and a file name read back from a run record is matched against the exact pattern above before it is opened. The {run} in a download URL is parsed as an integer and used only to look up the run record together with the report id. The file that is opened is the one that record names. Nothing from the URL becomes part of a path.
• Files are created with O_EXCL and mode 0640, and directories with 0750. A file that already exists is an error, never overwritten. The output holds plant data and tag names, so the mode is a floor, not a substitute for protecting the volume.
• Retention. After every run the newest retain runs that stored files are kept, and older ones are deleted with their files. Failed runs, which have no files, are kept separately (the newest retain of those), so a run of failures cannot push the last good report out. Any file in the report’s directory that matches the naming pattern but is not referenced by a remaining run record (the partial file of a run that died) is removed as well. A file that does not match the pattern, such as one an operator put there, is left alone.

CSV

RFC 4180, from encoding/csv, with CRLF line endings. The columns are device_id, tag_name, unit, period_start, period_end, one column per aggregation in the order the definition lists them, and excluded. Timestamps are RFC 3339 in the plant timezone with their offset. Numbers are written at full precision.

Spreadsheet formula injection. A tag name or a unit is text somebody typed, and a spreadsheet reads a cell that begins with =, +, -, or @ as a formula. Any text cell that begins with one of those (or with a tab or carriage return, or a space followed by one) is prefixed with an apostrophe, which spreadsheets treat as “this is text” and do not display. The numeric columns are written from numbers, so a negative reading is left alone and still reads as a number.

The file has no byte order mark. Excel opens a UTF-8 CSV without one as if it were a legacy code page, so a tag name with non-ASCII characters may need to be imported as UTF-8.

HTML

One self-contained file, built with html/template, so tag names and values are escaped for where they land: a tag called <script> is written as text. The stylesheet is inline and there is no script, font, image, or link to anywhere, because deployments can be air-gapped. It also carries a Content-Security-Policy meta tag that forbids loading anything, and the API serves it with a policy that adds sandbox, as an attachment.

Delivery by email

If a report has recipients, each run emails the files as attachments, through the SMTP relay configured under HOTLOOP_NOTIFY_SMTP_* (host, port, username, password, from, TLS). Only the relay is shared with the alarm channel: whether that channel is enabled, and who alarms go to, have no effect on reports, so a deployment can email reports without paging anyone. The chart writes the relay’s variables for reports alone when the alarm channel is off.

It is the same SMTP code as the alarm channel, not a second copy. internal/notify gained one general function, SendMessage, that sends a message with attachments to a list of recipients. The alarm channel now sends through it as well. The connection handling is unchanged (port 465 now speaks TLS from the first byte, see NOTIFY.md): STARTTLS whenever the relay offers it, AUTH when credentials are set, and a hard failure before AUTH when HOTLOOP_NOTIFY_SMTP_TLS is set and the relay does not offer STARTTLS. For an ASCII alarm the message is the same bytes as before, apart from the Date header, and a test pins that. A non-ASCII alarm subject is now sent as an RFC 2047 encoded word instead of raw UTF-8.

The message is multipart/mixed: a quoted-printable text part, then each file base64-encoded. Header injection is refused, not cleaned. Recipients, the sender, the subject, and the attachment file names are validated: a value with a CR, LF, or other control character in it, a recipient in display-name form or holding a list, or a file name with a quote or a path in it makes the message fail before anything is sent to the relay. The alarm channel’s own subject is the one exception, because a page must go out whatever an alarm is called: it flattens control characters to spaces first.

• No relay configured. A report with recipients still runs and stores its files, and the run is recorded as failed, with emailStatus failed and an error that says the relay is not configured. It does not succeed silently.
• Too big. HOTLOOP_REPORTS_MAX_EMAIL_BYTES (10 MiB) caps the attachment data in one email. A report over it is stored and downloadable, and its run records why it was not emailed.
• A run made by hand can skip the email with ?deliver=false.

Run records and the audit trail

Every run is recorded in report_runs from the moment it starts: when it started and finished, the trigger (schedule, catchup, or manual), who asked, the window, the outcome and error text, the number of tags, rows, readings aggregated, and readings excluded, the files stored, the email outcome and its recipients, and notes (for example, that a tag has since been deleted and is being reported from its recorded history). Success and failure are also written through rt.Event, the same audit trail alarms and fleet actions use, with the report id as the subject.

A run cut short by shutdown, by its time limit (HOTLOOP_REPORTS_RUN_TIMEOUT), or by the report being deleted is recorded as failed with that reason. A run left running by a process that died is marked failed when the Gateway next starts. Only one run of a report is in flight at a time; a second request while one is running is a 409.

API

Route	Permission	Details
GET /api/reports	reports.read	Every definition, each with its nextRunAt and most recent run
POST /api/reports	reports.write	Create. 409 if the id exists
GET /api/reports/{id}	reports.read	One definition
PUT /api/reports/{id}	reports.write	Replace. The id cannot change
DELETE /api/reports/{id}	reports.write	Delete, with its runs and files. A run in flight is cancelled first
POST /api/reports/{id}/run	reports.run	Run now and wait. ?deliver=false skips the email
GET /api/reports/{id}/runs	reports.read	Run records, newest first (?limit=, up to 500)
GET /api/reports/{id}/runs/{run}/download?format=csv|html	reports.read	Stream one stored file as an attachment

POST .../run returns the run record with 200 whether the run worked or not. The status says whether the request was carried out, and the record’s own status says whether the report was. A run that fails is a result, and it is recorded either way. The run is not tied to the client’s connection: hanging up does not abort a report that may already have started an email.

With the feature off, the list answers {"reports": [], "configured": false} and every other route answers with an error naming HOTLOOP_REPORTS_ENABLED.

Who may do what

Role	reports.read	reports.run	reports.write
viewer	yes	no	no
operator	yes	yes	no
admin	yes	yes	yes

• Read is for viewers because a stored report is aggregates of history a viewer can already read through values.read, so reading or downloading one grants nothing new.
• Run is for operators because running a report only ever delivers to the recipients an admin already chose, and it can be run without delivering.
• Write is admin-only. A definition names the addresses its output is emailed to, so being able to define one is being able to send plant data to an arbitrary address on a schedule. That is closer in kind to notify.test and fleet.config (also admin-only, also “do something to the outside world”) than to a read. This is a judgment call, and could be revisited if a real workflow needs operators to build reports.
• An operator can still cause an email to go to a report’s recipients by running it, and repeatedly. There is a one-run-at-a-time guard and ?deliver=false, but no rate limit.
• A user with an explicit permission list holds exactly what it names; the reports.* wildcard covers all three.

Configuration

Variable	Default	Details
HOTLOOP_REPORTS_ENABLED	false	The feature is off, including the scheduler, until this is set
HOTLOOP_REPORTS_DIR	/data/reports	Where each run’s files are written. Meant to be a PVC (reports.persistence in the chart)
HOTLOOP_REPORTS_RUN_TIMEOUT	5m	Bounds one run: query, render, write, and email
HOTLOOP_REPORTS_MAX_EMAIL_BYTES	10485760	The most attachment data in one email
HOTLOOP_TIMEZONE	UTC	The plant timezone. Must be a real zone name, or HotLoop won’t start
HOTLOOP_NOTIFY_SMTP_HOST, _PORT, _USERNAME, _PASSWORD, _FROM, _TLS, _TLS_SKIP_VERIFY		The relay reports email through. See NOTIFY.md

The chart’s reports block has enabled, runTimeout, maxEmailBytes, and persistence (size, storage class, access mode). notify.smtp supplies the relay. Reports are not started in --mcp-stdio mode, which is an agent spawning the binary as a helper, and a second scheduler beside the real Gateway would run every due report twice.

Backups

Report definitions and run records are user data and are part of the database backup (internal/backup). The output files are not: they are on the reports volume, and restoring a backup into a new deployment brings back the records but not the files, so downloading an old run there answers that the file is no longer on disk. Copy the reports volume if the files matter.

Adding these two tables to the backup list is the kind of change that is easy to forget, so the backup package now has guards for it. One test lists every table in the schema and fails if any is in neither backup.Tables nor an explicit exclusion list with a reason. Another checks that Tables is in foreign-key order, reading the foreign keys from the database. The round-trip test seeds a report definition and a run and checks the restored values and the sequence.

What has and has not been verified

This repository distinguishes tested from verified, and this feature is tested. Nothing in it has been verified against a specific mail provider or a specific spreadsheet application.

Tested, against real infrastructure and not mocks:

• The historian aggregation, against a real timescale/timescaledb:2.29.1-pg16, in both storage modes: the TimescaleDB hypertable and the native-partitioning fallback. The same plain SQL (a join, width_bucket against a boundary array, and ordinary aggregates) runs in both. Every cell of a stored CSV is compared with values worked out independently in the test from the seeded readings, including hours with no data, an hour with only bad readings, bad and uncertain readings among good ones, negative values, a zero, a reading exactly on an hour boundary, and readings on either side of the window.
• Delivery, against two real SMTP servers (axllent/mailpit, run with Podman, never Docker): a bare one, and one that requires STARTTLS and a login. The message is read back through Mailpit’s HTTP API and the test checks the recipients, the subject, the body, and that the bytes of each attachment equal the file the run stored. A wrong password is recorded as a failed run.
• The scheduler firing on its own (a moving clock, no call to the run function), the catch-up run and that it is only one, and the daylight-saving cases at the level of the schedule and of the scheduler: 03:00 stays 03:00 across the spring-forward and fall-back days in America/Chicago, the previous calendar day is 23 and 25 hours on those days, and a schedule that added 24 hours would fail these tests.
• The lifecycle under the race detector: repeated start and stop, and a run held open in the database while it is edited, deleted, stopped, and timed out.
• The HTTP surface, through the real router: every route against a role or explicit permission list that has and lacks each permission, path-traversal attempts in the report id, the run number, and the format, and downloads.
• Several of the tests above were proved by mutation, by breaking the code and watching the test fail. The pull request lists them.

Not verified:

• Any specific mail provider or gateway. STARTTLS and implicit TLS on port 465 both work, the same as the alarm channel (see NOTIFY.md); neither has been tried against a commercial provider.
• Any specific spreadsheet application. The formula-injection defense is the standard apostrophe prefix, checked by parsing the CSV, not by opening it in Excel, LibreOffice, or Google Sheets.
• A browser rendering of the HTML file. It is checked for escaping, for being self-contained, and for what it says, not for how it looks.
• Zones whose clock changes are not a whole hour (Lord Howe Island, 30 minutes) go through the same code, and a half-hour offset is tested for hour buckets, but such a change itself is not.
• Very large windows or many tags. The limits (50 tags, 1000 buckets, 20 recipients) are enforced and tested, and query time at the top of them on a large historian has not been measured.
• Clocks. The scheduler trusts the Gateway’s clock, and the database’s for a definition’s created and edited times.