External TimescaleDB SQL

If you want to store telemetry in your own TimescaleDB (in addition to or instead of Internal DB), use the PostgreSQL output in a rule. This page gives the schema and the template mapping for a single SenFlat v1 message.

Download timescaledb-eksternal.sql (hypertables, compression, and views; safe to run again). Run it as the database owner:

psql "$URL" -f timescaledb-eksternal.sql

Two tables, one rule per table, because the PostgreSQL output writes one row per message in the normal mode. For many rows per message see Rule to PostgreSQL: many rows.

Setup in the Rules editor

1. Channel Subscriber: the channel with the devices. The subtopic +/telemetry for the telemetry rule; +/event and +/status for the events table.
2. Logic: a Comparison that forwards SenFlat messages, for example message.payload.f equals flat. Without this filter, config and cmd messages are processed too and fail in the template.
3. PostgreSQL output: host, port, user, password, database, sslmode according to the server (require by default), Schema iot, and the Table as below.
   • Use a dedicated user that only has INSERT (an example CREATE ROLE and GRANT are at the end of the SQL file).
   • A server on a private network is refused by the network guard unless the administrator enables RULES_ALLOW_PRIVATE_TARGETS.

An empty template result becomes NULL, so a metric the device did not send is automatically NULL; numbers and booleans are converted by PostgreSQL to the column type.

The table iot.device_telemetry

Column	Template
time	{{.Message.created}}
device_ts	{{with .Message.payload.t}}{{printf "%.3f" .}}{{end}}
device_id	{{with .Message.payload.id}}{{.}}{{else}}{{.Message.publisher}}{{end}}
channel_id	{{.Message.channel}}
publisher	{{.Message.publisher}}
subtopic	{{.Message.subtopic}}
temp_inlet, temp_outlet, temp_ambient, humidity	{{.Message.payload.m.temp_inlet}} (the same metric name)
voltage, current, power_draw, energy, frequency, power_factor	{{.Message.payload.m.voltage}}
uptime, heap_free, door_front_open, door_rear_open	{{.Message.payload.m.uptime}}
metrics	{{json .Message.payload.m}}

The table iot.device_events

Column	Template
time, device_ts, device_id, channel_id, publisher, subtopic	The same as above
state	{{.Message.payload.m.state}}
fw	{{.Message.payload.m.fw}}
ip	{{.Message.payload.m.ip}}
metrics	{{json .Message.payload.m}}

The full content of an event (for example {"door_front_open":true}) is in the metrics column:

SELECT * FROM iot.device_events_v WHERE kind = 'event' AND metrics ? 'door_front_open';

Notes

• time is the receive time at the broker. The device’s measurement time (t) is stored in device_ts and is available as device_time in the *_v views.
• A new metric from the firmware does not require changing the rule: it goes into metrics (jsonb). For your own column: ALTER TABLE iot.device_telemetry ADD COLUMN name double precision; then add one column row on the output.
• One output has at most 30 columns.
• The output rate limit: a burst of 100 rows and 50 rows per second per output. For many devices with short intervals, watch for errors in the Executions tab.
• This output reads messages that are a single SenFlat object. A combined array or SenML needs a different rule and template.

Tip

Use Test connection on the PostgreSQL node before enabling the rule: it checks the connection, schema, table, columns, and the INSERT privilege without writing anything.