The timestamp bug that quietly destroys cold chain evidence

Here is a bug that passes every test you are likely to write.

A cold chain tracker loses cellular coverage for two hours inside a warehouse. It keeps sampling and buffers locally. When it reconnects, it flushes the backlog. Your ingest receives 24 readings in one burst, writes them, and returns 200.

Everything looks healthy. Throughput is fine, no errors, no gaps in the row count.

Then someone asks which readings correspond to the two hours the pallet spent on a dock, and you discover that every one of those buffered readings was written with the ingest timestamp — the moment your API received them — rather than the moment the sensor took them.

The data is all there. It is also useless for the only purpose that justified collecting it, because you can no longer say when anything happened.

Four timestamps, not one

The root cause is almost always that the system was designed with one concept of "time" when it needed four.

Timestamp	Set by	Meaning	Trustworthy?
sampled_at	Device RTC	When the sensor actually read	Only as good as the RTC discipline
event_at	Device	When a threshold/state change occurred	Same clock as sampled_at
uplinked_at	Device or modem	When the payload left the device	Useful for diagnosing buffering
received_at	Your ingest	When the server accepted it	As good as your server clock — rarely what you want

The failure mode is collapsing these into one column. Once sampled_at is gone, no downstream query can reconstruct it, and no amount of reporting polish will bring it back.

Same payloads, same row count, same 200 responses. The difference is whether the buffered burst keeps the timestamps it was sampled with, or inherits the one instant it happened to arrive.

A minimum viable reading looks closer to this:

{
  "device_id": "a1b2c3d4",
  "seq": 10432,
  "sampled_at": "2026-08-14T14:32:01Z",
  "uplinked_at": "2026-08-14T16:47:55Z",
  "clock": {
    "source": "gnss",
    "last_sync_at": "2026-08-14T09:12:44Z",
    "drift_ms_estimate": 340
  },
  "channels": {
    "temp_c": 4.7,
    "humidity_pct": 61.2,
    "light_lux": 812,
    "accel_g": 0.02,
    "position": { "lat": 40.7128, "lon": -74.0060, "hdop": 1.4, "fix_age_s": 12 }
  }
}

Three details in there matter more than they look.

seq is a monotonic device-side counter. It survives clock changes. If the RTC gets corrected mid-trip and timestamps jump, seq still gives you a total ordering, and a gap in seq tells you data was lost rather than never generated. Ordering by timestamp alone cannot distinguish those two cases.

The clock block makes the record self-describing. A reading that carries its own sync age and drift estimate lets a downstream consumer state a bounded uncertainty instead of implying precision it does not have. This is the difference between "these two channels are correlated to within a known offset" and "these two channels have the same string in a column."

fix_age_s prevents a common and expensive error. GNSS is frequently unavailable indoors. Many devices report the last known fix rather than nothing. Without a fix age, a stale position from 40 minutes ago is indistinguishable from a current one, and an excursion gets attributed to the wrong location — which is worse than having no location at all.

Do not re-stamp on reconnect

The rule that prevents the opening bug:

Timestamps are assigned once, at the point of measurement, and are immutable thereafter. Ingest may add fields. Ingest may never overwrite them.

In practice this means the server's clock belongs in its own column and nowhere else:

CREATE TABLE readings (
  device_id     TEXT        NOT NULL,
  seq           BIGINT      NOT NULL,
  sampled_at    TIMESTAMPTZ NOT NULL,   -- device, immutable
  uplinked_at   TIMESTAMPTZ,            -- device, immutable
  received_at   TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  clock_source  TEXT,
  clock_sync_age_s INTEGER,
  payload       JSONB       NOT NULL,
  PRIMARY KEY (device_id, seq)
);

Note clock_timestamp() rather than now(). In Postgres now() returns transaction start time, so a batch insert of 24 buffered readings would stamp all of them identically with the start of that transaction. For a received_at column that is arguably fine, but if you ever use it to reason about ingest latency it will quietly mislead you.

The composite primary key on (device_id, seq) does real work. Buffered flushes get retried. Modems duplicate. Gateways replay. With this key, a duplicate delivery is a no-op instead of a double-counted excursion:

INSERT INTO readings (device_id, seq, sampled_at, uplinked_at, payload)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (device_id, seq) DO NOTHING;

One caveat on DO NOTHING: it silently swallows the case where a device resets its counter and reuses a seq with a different payload. That is a genuine data-loss path disguised as successful idempotency. If your devices can reset counters — most can, on watchdog reboot or firmware update — compare a payload hash and raise a conflict rather than discarding:

INSERT INTO readings (device_id, seq, sampled_at, uplinked_at, payload, payload_sha)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (device_id, seq) DO UPDATE
  SET conflict_count = readings.conflict_count + 1
  WHERE readings.payload_sha IS DISTINCT FROM EXCLUDED.payload_sha;

Better still, include a boot counter or session ID in the key so a counter reset produces a new key space instead of a collision.

If you find yourself deduplicating on (device_id, sampled_at) instead, that breaks whenever two samples share the same timestamp at the recorded resolution. TIMESTAMPTZ itself is microsecond-precision, so this is not automatic — but plenty of devices quantise to whole seconds before transmitting, and event-triggered channels are exactly where you get two records inside one tick.

MQTT topic and QoS choices

For topic design, put the routing keys in the topic and everything else in the payload:

tenant/{tenant_id}/device/{device_id}/telemetry
tenant/{tenant_id}/device/{device_id}/event
tenant/{tenant_id}/device/{device_id}/status

Splitting telemetry from event matters because they have different delivery requirements. Periodic telemetry is fine to lose occasionally — the next sample is 5 minutes away. An excursion-start event is not.

On QoS, the usual default here is QoS 1 plus idempotent writes. Worth being precise about why, because it is easy to overstate: QoS 2 gives exactly-once delivery between MQTT endpoints. It does not give you exactly-once all the way into your database — a broker-to-consumer redelivery, a consumer crash after write but before ack, or a replayed batch will still produce duplicates at the storage layer. You need idempotent writes regardless. Given that, QoS 2's extra handshake is often paying twice on the scarcest resource in the system for a guarantee that does not extend to where you actually needed it.

That is a default, not a law. High duplicate-intolerance, expensive downstream side effects, or a compliance requirement that names QoS 2 can all flip it.

QoS 0 on an event topic is a mistake when the event is critical and non-reconstructable — an excursion-start you cannot derive from anything else. For derived or high-rate events that the next message supersedes, QoS 0 is fine.

Session and retention settings worth being explicit about:

MQTT 3.1.1:  Clean Session = false
MQTT 5:      Clean Start = false + Session Expiry Interval > 0
Client ID:   stable and persistent
Keep Alive:  shorter than your NAT idle timeout
Retained:    true on status topic

Two caveats that bite in production. Persistent sessions only queue QoS 1 and 2 messages matching existing subscriptions, and brokers cap queue depth and expiry — so a device offline for two hours may find its queue trimmed regardless of configuration; the device-side buffer is what actually saves you, not the broker. And a Keep Alive short enough to beat NAT timeouts is directly a battery and radio-wakeup cost, which on a shipment tracker running for weeks is not a rounding error.

Retained messages on a telemetry topic are a good way to hand a new subscriber a stale reading they treat as current. It is not categorically wrong — a retained last-known-value can be useful — but only if the payload carries its own timestamp and consumers enforce a staleness bound. Retained is genuinely valuable for status and Last Will, where "the last thing we knew" is exactly the semantics you want.

Excursion logic belongs on the device, not only in the query

A tempting design is to store raw samples and compute excursions in SQL. It is flexible, and it lets you change thresholds retroactively.

It also means the device has no idea an excursion is happening, so it cannot raise a priority uplink, cannot increase its sampling rate during the event, and cannot flag the event if it is offline at the time.

The practical answer is both: evaluate on-device for alerting and adaptive sampling, and keep raw samples for recomputation.

// device side - sustained-breach detection, not single-sample
typedef struct {
    float    threshold_c;
    uint32_t sustain_s;      // must stay out of range this long
    uint32_t hysteresis_c_x10;
    uint32_t breach_started_at;
    bool     in_excursion;
} excursion_cfg_t;

For most temperature-controlled goods a single sample out of range is not an excursion, it is a sample out of range — though this is a policy question, not a universal rule, and some regimes genuinely do treat any breach as reportable. What is consistent is that the default matters: a device firing on one reading will flood your event topic, and operators will start ignoring it, which is the real failure.

Make sustain_s and threshold_c remotely configurable per deployment. Frozen goods and chilled pharmaceuticals do not share a definition of "excursion," and shipping firmware per commodity is not a plan.

If thresholds are remotely writable, treat that path as security-relevant: authenticate the config channel, bound the accepted values server-side, and version the config so a reading can be interpreted against the thresholds that were actually in force when it was taken. A record whose alarm logic silently changed mid-trip is hard to defend.

What to validate at ingest

Reject or quarantine rather than silently accepting:

from datetime import datetime, timedelta, timezone

def parse_iso(s: str) -> datetime:
    # Python 3.11+ handles the trailing Z; older versions need the replace
    return datetime.fromisoformat(s.replace("Z", "+00:00"))

def validate(r: dict) -> list[str]:
    problems = []
    now = datetime.now(timezone.utc)

    try:
        sampled_at = parse_iso(r["sampled_at"])
    except (KeyError, ValueError):
        return ["sampled_at missing or unparseable"]

    if sampled_at.tzinfo is None:
        problems.append("sampled_at is naive - reject, do not assume UTC")

    if sampled_at > now + timedelta(minutes=5):
        problems.append("sampled_at in the future - device clock ahead")

    if sampled_at < now - timedelta(days=30):
        problems.append("sampled_at implausibly old - check RTC battery")

    if raw_uplink := r.get("uplinked_at"):
        if parse_iso(raw_uplink) < sampled_at:
            problems.append("uplinked_at precedes sampled_at - timestamp inconsistency")

    pos = r.get("channels", {}).get("position")
    if pos and pos.get("fix_age_s", 0) > 300:
        problems.append("stale position - do not use for attribution")

    return problems

Note the explicit parsing. JSON timestamps arrive as strings, and comparing a string to a datetime either raises or — worse, if something upstream has already half-coerced it — compares lexically and silently produces nonsense.

The uplinked_at < sampled_at check deserves an honest label. It is a timestamp inconsistency detector, not proof of any specific cause. The case that motivated it is an RTC corrected backwards by GNSS partway through a buffered backlog, leaving pre-correction readings stamped ahead of their own uplink. But it will not catch forward corrections at all, and it will fire on unrelated problems: a modem supplying uplinked_at from a different clock domain, a timezone bug in your parser, a firmware bug in serialisation. All of those are worth knowing about, which is why the check earns its place — just do not let the alert text assert a root cause it cannot establish.

Quarantine, do not drop. A reading that failed validation is still evidence that something went wrong with the device, and deleting it is the one irreversible operation in the pipeline.

The schema question worth asking early

Most of this comes down to one design decision that is cheap at the start and expensive later: is your record organised around the device, or around the shipment?

A device-centric schema is the obvious first implementation. It is also the one that cannot answer "show me every reading for shipment X across all the devices and custody segments it passed through," because that relationship was never modelled.

CREATE TABLE shipment_device_assignments (
  shipment_id   TEXT        NOT NULL,
  device_id     TEXT        NOT NULL,
  attached_at   TIMESTAMPTZ NOT NULL,
  detached_at   TIMESTAMPTZ,
  custody_party TEXT,
  PRIMARY KEY (shipment_id, device_id, attached_at)
);

-- prevent a device being attached to two shipments at once
ALTER TABLE shipment_device_assignments
  ADD CONSTRAINT no_overlapping_assignment
  EXCLUDE USING gist (
    device_id WITH =,
    tstzrange(attached_at, detached_at) WITH &&
  );

CREATE INDEX ON shipment_device_assignments (device_id, attached_at DESC);

The exclusion constraint needs btree_gist. Without something like it, a missed detach event lets one device appear on two shipments simultaneously, and every query that joins readings to shipments starts double-counting in a way that is very hard to notice.

One honest caveat, since this is where integrations tend to overclaim: a device cannot know legal custody. It knows position, motion, and time. Populating custody_party reliably means reconciling device observations against scan events, geofence definitions, or contractual handoff times from a TMS. Modelling the relationship is what makes that reconciliation possible later — it does not perform it for you.

Summary

• Keep four distinct timestamps. Never let ingest overwrite a device timestamp.
• Carry a monotonic seq so ordering survives clock corrections and gaps are detectable. Include a boot counter if devices can reset it.
• Use (device_id, seq) as the idempotency key, but handle seq reuse explicitly rather than swallowing it with DO NOTHING.
• QoS 1 plus idempotent writes is the usual default — QoS 2 is exactly-once between MQTT endpoints, not end-to-end into your database.
• Persistent sessions need a stable client ID, and broker queues have limits; the device-side buffer is what actually survives a long outage.
• Ship clock sync age and GNSS fix age in the payload so consumers can state bounded uncertainty.
• Split telemetry and event topics — they have different delivery requirements.
• Evaluate excursions on-device for alerting, keep raw samples for recomputation, version and authenticate remote threshold config.
• Quarantine invalid readings instead of dropping them, and label detectors by what they observe rather than by a root cause they cannot prove.
• Model the shipment-to-device relationship early, constrain it against overlaps, and do not pretend the device knows who had custody.

Most of these are cheap on day one and painful to retrofit, because the data you did not capture is not recoverable.

What has bitten you in telemetry ingest? The clock-step-mid-backlog case took an embarrassingly long time to isolate — curious whether others have hit the same thing or found a cleaner way to detect it.

---

This article was written with AI assistance for research and drafting.