If you are building the ingestion side of a cold chain or asset tracking platform, there is a category of bug that does not show up in staging, does not show up in the first pilot, and shows up for the first time when a customer asks you to produce a defensible record of a shipment that went wrong.
It is not a bug in your code. It is a bug in your data contract. This post is about the three places it hides.
1. The time base problem
Here is the naive schema most platforms start with, one row per sensor reading:
{
"device_id": "GPT45M-000117",
"sensor": "temperature",
"value": 6.2,
"unit": "C",
"timestamp": "2026-08-25T04:31:07Z"
}
This looks fine. It is fine, right up until someone asks a correlation question: did the temperature rise before or after the container was opened?
To answer that you join the temperature series against the light series on timestamp. And now you need to know something the schema does not tell you: were those two values sampled against the same clock?
If the temperature comes from a logger inside the load and the light comes from a different device on the door, the answer is no. And the size of the disagreement is larger than most people assume, because in a cold chain the environment attacks the timekeeping directly.
A 32.768 kHz tuning fork crystal — what essentially every low-power device uses — has a parabolic frequency-versus-temperature curve. It is cut to hit nominal at a turnover point, specified at 25 °C ± 5, and runs slow on both sides:
Δf/f = k(T − T₀)² k ≈ −0.035 ppm/°C² typical
(datasheets quote −0.02 to −0.045)
T = −20 °C: (25 − (−20))² = 2025
2025 × 0.035 ≈ 71 ppm
71e-6 × 86400 ≈ 6.1 s/day
Three tolerances sit on top of that: k is quoted as a range (0.020–0.045, so 3.5–7.9 s/day at the ends), the turnover point carries its own ±5 °C and gets squared (±25 %), and each device has an independent ±20 ppm factory tolerance of unknown sign — ±1.7 s/day per device, ±3.5 s/day differential.
Net: the separation between two specific units is somewhere between near-zero and about 10 s/day, typically around 6. Over an eleven-day lane, typically about a minute — but the useful property is not the magnitude, it is that the value is unbounded and unmeasured for your pair on your lane.
A minute is fatal here. The entire value of the light channel is the claim "the temperature rose 90 seconds after the door opened." With a minute of uncertainty you cannot make a 90-second claim — the record is worth exactly what a plain temperature log is worth.
And note what is not happening: nothing is broken, nothing is out of spec, no packet was lost. Each file is an accurate record of what its own oscillator counted. That is why this survives code review, staging and the first pilot — there is no failure to detect.
Worth pre-empting the obvious objection, because it is a good one: the logger measures temperature, so why not compensate? It can, and some devices do — a TCXO RTC gets you to a couple of ppm, and plenty of ordinary RTCs expose an offset register. What almost none of them do is publish the residual. So the question for an integrator is never "is this fixable," it is "did this device fix it, and does the payload tell me." If the answer is no or unknown, keep every channel you intend to correlate on one device.
The fix is structural, not computational. Sensors that need to be correlated must be sampled by the same MCU against the same oscillator and emitted as one record:
{
"device_id": "GPT45M-000117",
"boot_id": 37,
"seq": 2871,
"uptime_ms": 918274310,
"rtc_utc": "2026-08-25T04:31:07Z",
"rtc_sync": {
"source": "network",
"last_sync_utc": "2026-08-24T22:00:03Z",
"drift_correction_ms": 1650
},
"readings": {
"temperature_c": -18.4,
"humidity_pct": 71.4,
"light_lux": 318.0,
"pressure_hpa": 1006.2,
"accel_rms_mg": 41
},
"position": {
"fix": "gnss",
"lat": 51.9244,
"lon": 4.4777,
"hdop": 0.9,
"fix_age_ms": 4200
}
}
Three things in that payload do real work:
(boot_id, uptime_ms) is the monotonic truth. uptime_ms never moves backwards within a power cycle, but it resets to zero on every boot — and mid-trip reboots are a known failure mode on primary-cell hardware, where a TX burst can sag the rail enough to brown out the MCU. Order by the pair, never by uptime_ms alone, or a post-reboot record gets filed back at the start of the trip.
rtc_utc is a label: correctable, re-syncable, sometimes simply wrong. Use it only to place the trip on a wall clock. Platforms that sort by it get events that appear to travel backwards at every resync, and then someone writes a "fix" that quietly drops those rows.
rtc_sync makes drift auditable. Positive drift_correction_ms means the device was behind and was moved forward — define the sign in your schema, because half of all integrations get it backwards. The value above is what ~70 ppm looks like across a six-and-a-half-hour gap, which is roughly what a device sitting at −20 °C will hand you. Without this field you cannot put a confidence interval on any correlation you compute, and a record that cannot state its own uncertainty is not evidence.
fix_age_ms prevents a specific, very common lie. A GNSS fix takes time — 5 seconds on a hot start with cached ephemeris, 30+ on a cold start. Low-power devices frequently attach the last known position to a sensor record rather than burning the energy for a fresh fix. That is a legitimate design choice. Reporting it as if it were concurrent is not. Without fix_age_ms, a position four minutes and six kilometres stale is indistinguishable from a live one, and your map places an excursion at the wrong depot with total confidence.
2. The delivery problem
Battery-powered cellular devices are offline most of the time by design. A tracker in long-standby mode with PSM enabled is unreachable for hours at a stretch. When it does connect, it dumps a backlog.
That produces three delivery behaviours your ingestion must survive:
- Out-of-order arrival. Backlog from a buffer and live samples from the current window can interleave. Records for 03:00 can land after records for 09:00.
- Duplicates. The device sends, the network acknowledges, the acknowledgement is lost, the device retries on next wake. You will receive the same record twice — sometimes days apart.
- Bulk arrival. A device offline for a week may upload thousands of records in one session, which is a load spike and a rate-limit interaction, not just a correctness problem.
The mitigation is an idempotency key the device generates, not the server:
idempotency_key = sha256(f"{device_id}|{boot_id:08x}|{seq:08x}")
Use delimiters or fixed-width fields — this is not a stylistic point. Naive concatenation collides: boot_id=3, seq=741 and boot_id=37, seq=41 both produce 3741, the same hash, and the DO NOTHING below silently discards the second record. On a post about data integrity, that is the first thing a reader will check.
boot_id is a counter incremented once per power-up and persisted to flash. seq is a per-record counter that resets each boot.
It is tempting to reach for uptime_ms instead of boot_id. Don't: uptime_ms also resets on boot, and it is not unique per record anyway — a device can emit several records inside the same millisecond. boot_id does carry one dependency worth naming: it must survive across power cycles, so it lives in flash and a wear-levelling failure that resets it will reintroduce collisions. Budget for that with a wide counter and a write-once-per-boot policy.
-- idempotency_key TEXT PRIMARY KEY (the ON CONFLICT target needs the unique index)
INSERT INTO telemetry (idempotency_key, device_id, boot_id, seq, uptime_ms, payload, received_at)
VALUES ($1, $2, $3, $4, $5, $6, now())
ON CONFLICT (idempotency_key) DO NOTHING;
Two rules that are easy to get wrong:
Never make received_at part of the key. The whole point is that the same record arriving twice at different times collapses to one row. Include the server clock in the key and you have built a deduplication system that never deduplicates.
Never dedupe on a content hash of the readings. A device sampling every 5 minutes in a stable freezer legitimately produces identical readings for hours. Content-hash dedup silently deletes them, and you discover it during an audit when a 90-minute window is missing from a record that was supposed to be continuous.
For the bulk-arrival case, accept the whole batch and process asynchronously. Rejecting a batch with a 429 means the device — which is on a power budget, not a retry budget — will re-transmit the entire backlog on next wake, burning battery and producing the exact duplicate storm you were trying to avoid.
3. The schema ownership problem
This one is organisational, and it is the one that costs the most to fix later.
Most platforms integrate their first device by writing a parser for that vendor's payload. It works. Then the vendor ships a firmware update that adds a field, or renames temp to temperature_c, or changes a unit — and the parser breaks, or worse, doesn't break and starts recording wrong values.
The deeper problem shows up at audit time. If your stored evidence has a vendor's payload shape baked into it, then your compliance record has a vendor dependency inside it. When you change hardware suppliers — and over a multi-year deployment you will — you now have two incompatible eras of evidence for the same lane.
Define the canonical record yourself, and treat every vendor payload as an adapter input.
device payload → adapter → canonical record → storage
↑
versioned, per firmware
Practical rules:
-
Version the adapter, not the storage.
adapter_versiongoes on every stored row. When you discover a unit bug in adapter v3, you know exactly which rows to reprocess. - Store the raw payload alongside the canonical record. Object storage is cheap; regenerating evidence you no longer have the source for is impossible. This is also the only way to survive discovering an adapter bug two years in.
-
Make units explicit in field names.
temperature_c, nottemperature. The most expensive class of telemetry bug is a silent unit mismatch, and it is entirely preventable at the naming layer. -
Require
schema_versionin the canonical record. Not the adapter version — the shape of your own record. These change on different schedules and conflating them makes migrations unnecessarily painful.
When you push into a TMS or WMS downstream, push the canonical record, never the device payload. Those systems have their own upgrade cycles and you do not want a device firmware release to be able to break a customer's warehouse integration.
A minimum viable evidence record
If you take one thing from this, take the checklist. A telemetry record that can support a dispute needs:
-
A monotonic pair —
(boot_id, uptime_ms)— for ordering, sinceuptime_msalone resets on every reboot - A wall-clock timestamp with sync provenance — when it was last disciplined, and by how much it moved
- All correlated channels in one record, sampled by one MCU against one oscillator
-
Explicit staleness on any derived or cached value, especially position (
fix_age_ms) - A device-generated idempotency key, so retries collapse instead of duplicating
- The raw payload retained, alongside a canonical record you own
-
Units in the field names, and a
schema_versionon every row
Six of those seven cost you nothing at design time and are effectively impossible to retrofit onto data you have already collected.
I work on the hardware side of this — my company builds cold chain and asset tracking devices — so the payload shapes above reflect a particular set of design choices. The failure modes are universal, and I have watched every one of them happen on platforms integrating somebody else's hardware.
What's your dedup strategy for devices that legitimately emit identical readings for hours? I've not found an approach I'm fully happy with.
