Every message flespi normalises, in storage you own

Two reasons, and they are both about the storage rather than the ingestion. flespi's own retention is priced per message-day, so keeping several years of history there costs more than keeping it yourself. And it is cloud-only: if a customer needs their telematics data inside a jurisdiction you control, no plan tier answers that.

So the split is deliberate. flespi stays the ingestion and normalisation layer — the thing it is genuinely best at — and the long-term store moves to a machine you choose, in a country you choose, under a retention policy you set rather than one you rent.

Messages arrive over MQTT 5 with QoS 1 and manual acknowledgement, on a channel-level subscription — one subscription covers every device on the channel, so no billable device entities are created to read them. Each message is written and fsynced to a local disk spool before it is acked, which moves the durability boundary from a broker buffer you cannot inspect onto hardware you can.

A batcher drains that spool into ClickHouse in batches of 20,000 rows or two seconds, whichever comes first. Identity is (org_id, ident, ts, ts_key) over a ReplacingMergeTree with insert deduplication tokens, which turns at-least-once delivery into exactly-once once the parts merge. A ClickHouse outage therefore cannot open a gap on the flespi side: the spool keeps acknowledging and the backlog drains when the cluster returns.

There are no webhooks in this design, which makes backfill the only thing standing between an outage and permanent loss — so it is built as a first-class component rather than a script somebody runs. A reconciler watches per-channel freshness against what is actually in ClickHouse and, when a channel falls behind, pulls the missing time range over the REST API into the same identity key. Re-pulling a window replaces it instead of duplicating it.

Healing has a limit and the limit is stated rather than hidden: a consumer can be down for as long as flespi's per-channel buffer holds, which at real volumes is hours rather than the nominal thirty days. Anything past that is recorded in a Postgres ledger as lost, alerted on, and covered by a runbook — which is a worse outcome than healing it, and a much better one than a silent hole in a chart nobody notices for a quarter.

flespi's parameter catalogue is the union across every protocol it supports: 4,188 entries at the last regeneration. Any one device emits a few dozen of them, and which few dozen depends on the model. A table with a column per parameter would be both enormous and permanently out of date.

Instead each row carries 17 typed hot columns — the fields every query touches, with their own codecs — plus a native JSON column holding the sparse tail. A device that starts emitting parameter 4,189 next week lands with no migration and no deploy. The catalogue ships embedded in the binary and is served to the console, so a parameter nobody hardcoded still shows up with a human label and a unit.

Each organisation gets its own MQTT runner under a supervisor that reconciles what should be running against what is, and its own flespi token pair, encrypted with a versioned AES-GCM keyring so rotation is a version bump rather than a migration. The fleet hierarchy lives in Postgres and is mirrored into ClickHouse dictionaries, which is what lets a query filter by fleet without joining across two stores.

One binary serves both the JSON API and the React console: a fleet map, a message browser over any promoted column or catalogue parameter with an AND/OR filter tree and CSV export, device discovery, and per-user access granted fleet by fleet. Every route is session-authenticated and scope-checked per organisation.

Running against a real flespi account: 8 channels, about 1,470 devices, measured at roughly 1,400 messages a minute in the peak hour and 320 averaged across the day. That 4.4× diurnal swing is the number that matters when sizing — the peak is what plan ceilings have to clear, the average is what the storage bill derives from.

It is deliberately not an FMS. There are no dashboards for drivers, no dispatch screen and no mobile app; it is a mirror with an operator console, and the analytics happen in whatever already reads ClickHouse. If you want the managed, reporting-shaped version of this idea against Wialon instead, that is FleetSQL.