What Happens Between a Sensor Reading and a Database Write?
A database record such as 23.4 °C can appear deceptively simple. It is often treated as a direct statement about the physical world at a particular place and time. In practice, it is the result of a chain of analogue behaviour, electronic conversion, firmware decisions, communication protocols and software processing.
Every stage can introduce error, ambiguity or loss of context. A system may continue to collect plausible values while no longer producing measurements that can be interpreted, compared or defended. The engineering question is therefore not only whether data arrived in the database, but whether the stored record still represents what the sensor observed.
The sensor does not produce a database-ready value
Many sensors begin with an analogue quantity: a voltage, current, resistance, frequency or charge that varies with the measurand. A resistance temperature detector, for example, must be excited, measured and converted into temperature using an appropriate relationship. The result depends not only on the sensing element but also on lead resistance, excitation stability, input impedance, electrical noise, self-heating and the conversion algorithm.
Even sensors with a digital interface are not exempt. They may apply internal filtering, compensation, linearisation or averaging before presenting a numerical output. Those operations may be appropriate, but they are part of the measurement method and should not become invisible merely because they occur inside a packaged device.
A defensible record needs to retain, or be reliably associated with, the sensor identity, engineering unit, acquisition range, applicable calibration state and any meaningful configuration. A raw analogue-to-digital converter count without this context is not generally a useful measurement. Equally, a temperature value with no means to identify the sensor or conversion configuration has limited evidential value.
Acquisition firmware makes consequential decisions
The firmware nearest the instrument commonly determines when to sample, how long to average, which values to reject and when to report an exception. These are not incidental implementation details. A moving average can suppress electrical noise, but it can also conceal a short excursion. A threshold alarm may be evaluated against instantaneous data, filtered data or a value already adjusted by a correction factor. Each choice changes system behaviour.
Timestamping is particularly easy to misunderstand. There may be several relevant times:
- when the physical quantity was sampled;
- when conversion completed;
- when firmware assembled a message;
- when a gateway received it; and
- when the database committed it.
Those times are not interchangeable. If communications are interrupted, an edge device may buffer readings and transmit them later. Recording gateway receipt time as though it were the observation time can distort trends, sequence events incorrectly and make duration calculations unreliable.
The device clock itself also requires consideration. Drift, synchronisation method, time-zone handling, daylight-saving changes and loss of a timing source all affect the interpretation of time-series data. A timestamp is useful only if its origin and uncertainty are understood in the context of the intended use.
Transmission can preserve data while losing meaning
A message can be delivered successfully yet be semantically wrong. Common examples include a unit mismatch, a field decoded using an obsolete schema, byte-order errors or an identifier mapped to the wrong asset. These failures often produce credible-looking values, making them more dangerous than an obvious communications fault.
Wireless systems add further conditions. Packets may be lost, duplicated, delayed or received out of order. Low-power devices may batch data to conserve energy. Protocols may retransmit a message after an acknowledgement failure even though the original arrived. The receiving system needs an explicit strategy for sequence numbers, idempotency and gap detection, rather than assuming that arrival order represents measurement order.
Security boundaries also matter. Encryption in transit protects confidentiality and can contribute to integrity, but it does not establish that a reading came from the expected physical device unless device identity, key management and authentication are designed accordingly. A gateway accepting data from an unauthorised or incorrectly provisioned device can create a clean-looking but untrustworthy dataset.
Gateways are active participants in the measurement chain
A gateway may aggregate readings, translate protocols, apply local rules, cache messages during an outage and forward data to one or more services. It is therefore more than a communications bridge.
Gateway configuration can alter a record through scaling, unit conversion, field mapping or timestamp replacement. Firmware upgrades can change retry behaviour, filtering or serialization. A gateway with insufficient persistent storage may silently discard buffered data during a prolonged network failure. If it has no reliable clock, it may assign uncertain timestamps to delayed messages.
For systems where measurement integrity matters, gateway behaviour should be observable. Useful evidence includes connection state, clock synchronisation status, queue depth, rejected-message reasons, software and configuration versions, and counts of received, forwarded and discarded messages. This does not require retaining every diagnostic signal indefinitely. It does require enough operational evidence to distinguish a genuine process change from a fault in the data path.
Ingestion should preserve source facts before creating convenience fields
At ingestion, applications often normalise data into a convenient schema: one timestamp, one value, one unit and a device identifier. That model is useful for dashboards, but it can discard distinctions needed later.
A more robust design preserves source-supplied time separately from receipt and processing times. It records the original device identifier alongside any platform identifier, maintains message sequence or unique event identifiers where available, and captures the decoding or schema version used. If engineering values are derived from raw values, the relationship should be reconstructable. This may mean retaining raw payloads for a defined period, or retaining sufficient structured source fields and transformation metadata to reproduce the conversion.
Database writes also need failure handling. A successful acknowledgement to a gateway should not be sent before durable acceptance has occurred, unless the system explicitly manages that risk. Duplicate delivery should not create duplicate observations. Invalid data should be quarantined with a reason, not silently coerced into a default value that later appears valid.
The stored value is the end of a chain, not the beginning
The appropriate level of control depends on intended use. A non-critical environmental display may tolerate occasional gaps and approximate receipt times. A system supporting quality decisions, regulated records, condition monitoring or investigation after an incident requires stronger evidence that values are complete, correctly interpreted and attributable to the relevant measurement process.
Designing that evidence into acquisition, transport and ingestion is considerably more effective than attempting to reconstruct it from a database table after an event. The database write should be treated as the final preservation step in a measurement chain whose context has been protected from the sensor onwards.