The MQTT-to-database glue code you wrote once and now maintain

Many DIY telemetry projects begin with a reasonable piece of engineering:

1. Install Zigbee2MQTT and an MQTT broker.
2. Decide that current state is not enough.
3. Subscribe to device topics from a small Node.js or Python process.
4. Extract fields and write them to a time-series database.
5. Build a dashboard.

The first version may be short, understandable, and exactly right for the problem you had that day. The challenge starts when the script becomes part of the home’s monitoring infrastructure and nobody revisits its assumptions.

Six months later, it may be responsible for battery history, energy graphs, temperature trends, and alerts. A change to a device payload, broker configuration, database version, or host can now affect data you expect to have available. The script is no longer merely glue. It is a data pipeline with operational responsibilities.

That does not make the original DIY approach wrong. It means you should recognise when a prototype has become a service and decide whether you still want to operate it.

Why the small script grows

A first implementation often assumes that every message has the same shape and that the database is always available. Real Zigbee2MQTT installations are more varied.

Zigbee2MQTT publishes device state below its configured base topic, which defaults to zigbee2mqtt; device topics and payloads depend on the device and its exposed features. One temperature sensor may publish temperature and humidity, while a smart plug may publish power, energy, current, and voltage. Different devices can report at different intervals and may omit fields that have not changed.

The ingestion code therefore starts acquiring rules:

• Ignore control topics such as /set and bridge-management topics.
• Identify a device from its topic while coping with renamed friendly names.
• Parse JSON and handle malformed or unexpected payloads.
• Convert units and normalise field names.
• Decide whether missing fields mean “unchanged,” “unknown,” or “not supported.”
• Preserve the message timestamp correctly.
• Prevent commands or retained state from being mistaken for a new measurement.
• Deal with duplicate delivery and reconnects.

Each rule can be sensible. The maintenance problem is that the rules are often added one incident at a time, without a clearly defined data contract or test suite.

MQTT delivery is not a database write

An MQTT message arriving at your consumer does not mean that it has been safely stored in your historical database. The consumer has to parse it, transform it, connect to the database, perform the write, and decide what to do if any step fails.

MQTT quality of service affects delivery guarantees. QoS 0 is at-most-once delivery, while QoS 1 can redeliver until acknowledgement, which means consumers must be prepared for duplicates. Retained messages are also a separate concept: a broker can deliver the latest retained message when a subscriber connects, but that message is not a complete historical backlog.

A reliable consumer therefore needs explicit handling for:

• Database downtime.
• MQTT broker restarts.
• Temporary network failures.
• Duplicate messages.
• Messages queued while the consumer is unavailable.
• Slow database writes that create backpressure.
• Poison messages that repeatedly fail parsing.
• Shutdown and restart ordering.

A try/catch around the database call is not a retry strategy. Retrying every failure immediately can overload a recovering database. Retrying indefinitely can cause memory growth. Dropping every failed message silently creates gaps that may not be discovered until much later.

The right behaviour depends on the importance of the data and the capabilities of the broker and database. For some home dashboards, occasional gaps are acceptable. For a telemetry history that supports maintenance decisions, the gap should at least be visible and explainable.

The hidden work is observability

A pipeline that produces no errors is not necessarily a pipeline that is collecting correctly. You need to monitor both failures and absence.

Useful operational signals include:

• Last successful MQTT connection.
• Last message received for each expected device.
• Number of messages parsed and written.
• Number of parse, validation, and database errors.
• Retry queue size and oldest queued message.
• Database write latency.
• Disk utilisation and database growth.
• Time since the last successful write.

The “absence” check is particularly important. A process can remain running while its subscription is wrong, its credentials have expired, or all writes are failing. A process monitor may report healthy because the process has not crashed, even though no new telemetry is reaching the database.

For device availability, avoid using one global timeout for everything. A mains-powered device and a sleeping battery sensor have different expected reporting patterns. Zigbee2MQTT provides availability and last-seen-related mechanisms, but the thresholds need to match the device and configuration.

Schema and naming become contracts

A telemetry script often starts by writing whatever fields it receives. That is convenient, but it makes later analysis harder if names and units are inconsistent.

Before storing data, decide how you will represent:

• Device identity and topic.
• Friendly name changes.
• Location or site.
• Measurement name and unit.
• Numeric versus textual state.
• Event timestamps versus ingestion timestamps.
• Firmware, model, or configuration changes.

A friendly name is convenient for humans but may change. A stable internal identifier can make long-term history easier to join, although it introduces its own lifecycle and privacy considerations.

You also need to distinguish measurements from states. power: 42 can be stored as a numeric measurement. state: ON is an event or categorical state. battery_low: true is not the same kind of value as cumulative energy. Treating every JSON property as an interchangeable numeric field usually produces awkward queries and misleading charts.

Schema decisions are easy to postpone because the first dashboard only needs a few fields. They become harder once months of data have accumulated under inconsistent names.

Retention and recovery are part of ingestion

Writing data successfully is only half the job. The data also needs a lifecycle.

Raw Zigbee2MQTT telemetry can be valuable for recent troubleshooting, while older data may be more useful as hourly or daily aggregates. Without retention, storage grows continuously. Without downsampling, long-term dashboards may query more detail than the question requires.

A DIY stack therefore needs explicit decisions about retention, aggregation, backups, and restoration. It also needs tests. A backup that has never been restored is an assumption, not evidence that recovery works.

Power outages add another layer. A Raspberry Pi or NAS may restart cleanly, or it may expose an unclean database shutdown, a full filesystem, a missing mount, or a service-ordering problem. The MQTT broker may come up before the consumer, and retained messages may provide the latest state but not all events that occurred during the outage.

The correct response is not to promise that every message can always be recovered. It is to decide which gaps are acceptable, configure the components accordingly, and monitor the resulting behaviour.

DIY is still a good fit

A self-hosted ingestion service is appropriate when you want control over the complete pipeline. It can be the right choice if you need arbitrary transformations, local-only processing, custom correlation with infrastructure metrics, or a common observability platform for home automation and servers.

It is also a good fit when you enjoy operating the system. The script can be a useful learning project, and a small, well-tested service may remain stable for years.

The decision becomes less attractive when the pipeline exists only to provide basic device history and you do not want to maintain another long-running component. In that case, the code’s apparent simplicity can be misleading: you are not only writing a subscriber; you are accepting responsibility for delivery, parsing, storage, monitoring, and recovery.

Where managed ingestion fits

ZigStream complements Zigbee2MQTT rather than replacing it. Zigbee2MQTT remains the local bridge that communicates with your Zigbee devices and publishes their data. Your local automations, coordinator, broker, and device configuration remain under your control.

A managed ingestion workflow changes the boundary at which you operate infrastructure. Instead of writing and maintaining a dedicated MQTT-to-database consumer for device history, you use the ingestion path supported by the service and query the resulting telemetry through its device-history interface.

A managed service is likely to fit when your priority is focused Zigbee2MQTT telemetry history and reduced pipeline ownership. A DIY consumer remains preferable when you need full control over transformations, storage location, schema, or integrations.

Decide whether the script is a service

The useful question is not whether your MQTT consumer is only 200 lines long. It is whether you depend on it and whether you are prepared to operate it.

Ask:

• Would a silent failure leave you without history you care about?
• Do you know how duplicate messages and retained state are handled?
• Can you detect missing writes, not just process crashes?
• Are retries bounded and observable?
• Can you restore the database and consumer configuration?
• Do upgrades have a test or rollback path?
• Is the customisation worth the ongoing work?

If the answers are yes and you enjoy the responsibility, keep the DIY system and improve it deliberately. Add tests for representative payloads, metrics for collection health, retention checks, backups, and a documented recovery procedure.

If the answers are no, moving device history to a focused managed workflow can be a rational simplification. You are not abandoning local Zigbee control; you are choosing not to operate one particular piece of supporting infrastructure.

The bottom line

An MQTT-to-database script is a good way to learn and a perfectly valid component of a self-hosted telemetry platform. The trap is forgetting that it has become a service once you depend on its history.

Reliable ingestion involves more than subscribing and writing. It includes payload variation, timestamps, duplicate delivery, retries, queues, monitoring, retention, backups, and recovery. If those responsibilities are useful to you, own them consciously. If they are not, a managed Zigbee2MQTT telemetry workflow such as ZigStream can reduce the amount of infrastructure you need to maintain while leaving your local Zigbee network and automations in place.

Review your current consumer before the next upgrade or outage forces the question. Either document and harden it as infrastructure, or decide that the code has already outgrown the problem it was meant to solve.