Sensor Metadata and Device Registry Management #

A reading of 23.4 means nothing. It becomes a measurement only when something tells you it came from a temperature probe, in degrees Celsius, two metres above ground, at a known coordinate, with a calibration applied on a known date. That “something” is the device registry, and on most environmental networks it starts life as a spreadsheet, migrates into a hard-coded dictionary, and is finally discovered to be wrong three years later when an analyst asks why a station appears to have moved 40 metres in 2024. The failure is rarely dramatic. It looks like a sensor whose readings drift after a firmware update that also changed its units, or a rooftop monitor whose data is compared against a street-level reference because both rows carry the same latitude and longitude. This stage of the IoT Sensor Data Ingestion & Spatial Synchronization pipeline builds the registry properly: a temporal model where every fact about a device has a period during which it was true, and a join that attaches the right facts to each reading at ingest speed.

The design pressure is unusual. The registry is tiny and almost never changes, but every single reading depends on it, and the one operation that matters — “what was true about this device at this instant” — is the operation a naive schema makes hardest.

---

Prerequisites #

The registry sits between payload decoding and storage. These need to be in place first.

• Python 3.11 with # python 3.11 · pydantic==2.7.1 · psycopg[binary]==3.1.18 · pandas==2.2.2. Pydantic 2 is assumed throughout; the v1 @validator API differs enough that examples will not run unchanged.
• PostgreSQL 15 or later. The temporal join uses range types and a GiST exclusion constraint; both are long-standing features, but daterange overlap exclusion needs the btree_gist extension enabled.
• A stable device identifier from the transport. MQTT client id, LoRaWAN DevEUI, or the vendor serial — whatever survives a firmware reflash. If your only identifier is assigned by the network server, capture it but do not key the registry on it.
• Decoded payloads. The registry join happens after LoRaWAN payload parsing or the MQTT subscriber has produced typed fields, not before.
• UTC timestamps. Validity intervals are compared against the reading’s observation time, so the timestamp hygiene from timestamp alignment and timezone normalization is a hard prerequisite. Comparing a local timestamp against a UTC validity interval produces a wrong join for one hour twice a year.

---

Step-by-Step Workflow #

Step 1 — Separate the Instrument from Its Deployment #

The single most common registry mistake is one table with a location column. A physical instrument outlives any particular place it is installed: it is calibrated at a lab, deployed to a rooftop, recalled for repair, and redeployed to a different site. Model those as two entities.

# python 3.11 · pydantic==2.7.1 · psycopg[binary]==3.1.18 · pandas==2.2.2
CREATE_SQL = """
CREATE TABLE device (
    device_id     text PRIMARY KEY,        -- vendor serial or DevEUI, never reassigned
    model         text NOT NULL,
    manufacturer  text NOT NULL,
    commissioned  date NOT NULL
);

CREATE TABLE deployment (
    deployment_id bigserial PRIMARY KEY,
    device_id     text NOT NULL REFERENCES device(device_id),
    site_id       text NOT NULL,
    latitude      double precision NOT NULL,
    longitude     double precision NOT NULL,
    height_m      real NOT NULL,
    valid         tstzrange NOT NULL,      -- [valid_from, valid_to)
    EXCLUDE USING gist (device_id WITH =, valid WITH &&)
);
"""

The EXCLUDE constraint is the load-bearing line. It makes it physically impossible for one device to have two overlapping deployments — the error that produces duplicated readings after a join, and the one that is otherwise caught only by an analyst noticing a doubled row count.

Complexity: the exclusion constraint costs an index probe per insert, which is irrelevant at registry write volumes (tens of rows per month) and priceless at read time.

Step 2 — Give Every Mutable Fact a Validity Interval #

Anything that can change about a device needs a period, not a value. Location changes when the sensor is moved. Units change when firmware is updated. Calibration coefficients change at every service visit. Ownership changes when a project ends.

CALIBRATION_SQL = """
CREATE TABLE calibration (
    calibration_id bigserial PRIMARY KEY,
    device_id      text NOT NULL REFERENCES device(device_id),
    slope          double precision NOT NULL,   -- m in y = m*x + b
    intercept      double precision NOT NULL,   -- b
    reference_id   text,                        -- co-location reference, if any
    valid          tstzrange NOT NULL,
    EXCLUDE USING gist (device_id WITH =, valid WITH &&)
);
"""

Using the same m/b naming as the cross-device normalization stage is deliberate: a coefficient that is called slope in the registry, m in the calibration code and gain in the export is a coefficient that will eventually be applied twice.

Complexity: O(1) inserts; the read path is covered in Step 4.

Step 3 — Model the Registry in Python, Once #

Give the registry a typed representation so the ingest path is not indexing into dictionaries by string key. Pydantic buys validation on load, which is where a bad registry row should be caught — not per reading.

from datetime import datetime
from pydantic import BaseModel, Field, field_validator

class Deployment(BaseModel):
    """One period during which a device sat at one place."""
    device_id: str
    site_id: str
    latitude: float = Field(ge=-90, le=90)
    longitude: float = Field(ge=-180, le=180)
    height_m: float = Field(ge=0, le=500)
    valid_from: datetime
    valid_to: datetime | None = None          # None = still current

    @field_validator("valid_to")
    @classmethod
    def _ordered(cls, v, info):
        start = info.data.get("valid_from")
        if v is not None and start is not None and v <= start:
            raise ValueError("valid_to must be after valid_from")
        return v

    def covers(self, when: datetime) -> bool:
        return self.valid_from <= when and (self.valid_to is None or when < self.valid_to)

Note the half-open interval: valid_from inclusive, valid_to exclusive. Closed-closed intervals put the instant of a move into two deployments at once, which is exactly the duplicate the exclusion constraint exists to prevent.

Complexity: O(1) per covers call.

Step 4 — Build an In-Memory Index Keyed by Device #

The join runs per reading, so it must not touch the database. Load the registry once, index it by device, and keep the intervals sorted so lookup is a binary search rather than a scan.

import bisect
from collections import defaultdict

class Registry:
    """In-memory, point-in-time registry lookup for the ingest path."""

    def __init__(self, deployments: list[Deployment], version: int) -> None:
        self.version = version
        self._by_device: dict[str, list[Deployment]] = defaultdict(list)
        for d in deployments:
            self._by_device[d.device_id].append(d)
        for rows in self._by_device.values():
            rows.sort(key=lambda d: d.valid_from)
        self._starts = {k: [d.valid_from for d in v] for k, v in self._by_device.items()}

    def deployment_at(self, device_id: str, when: datetime) -> Deployment | None:
        rows = self._by_device.get(device_id)
        if not rows:
            return None
        i = bisect.bisect_right(self._starts[device_id], when) - 1
        if i < 0:
            return None
        candidate = rows[i]
        return candidate if candidate.covers(when) else None

Complexity: O(n log n) to build, O(log k) per lookup where k is the number of deployments for that one device — in practice one to five. On a laptop this resolves about two million readings per second, which is three orders of magnitude more headroom than any ingest path needs.

Step 5 — Fail Closed on Unknown Devices #

deployment_at returning None is not an error to swallow. A reading from a device with no registry entry is either a provisioning gap, a spoofed identifier, or a decoder bug — and all three are worth seeing.

QUARANTINE_REASONS = ("unknown_device", "no_deployment_at_time", "no_calibration_at_time")

def resolve(reading: dict, registry: Registry) -> tuple[dict | None, str | None]:
    """Attach registry metadata, or return the reason it could not be attached."""
    dep = registry.deployment_at(reading["device_id"], reading["observed_at"])
    if dep is None:
        known = reading["device_id"] in registry._by_device
        return None, "no_deployment_at_time" if known else "unknown_device"
    enriched = {
        **reading,
        "site_id": dep.site_id,
        "latitude": dep.latitude,
        "longitude": dep.longitude,
        "height_m": dep.height_m,
        "registry_version": registry.version,
    }
    return enriched, None

Stamping registry_version onto every reading is what makes a registry correction auditable later: you can find exactly which rows were resolved against the wrong metadata and reprocess only those.

Complexity: O(log k) per reading, dominated by the lookup in Step 4.

---

Configuration and Tuning #

What belongs in the registry, and what belongs on the reading #

Registry refresh strategy by fleet size #

The footprint numbers matter on gateways. A registry that fits comfortably on a server may not fit alongside a local SQLite fallback buffer on a device with 512 MB of RAM, in which case the edge should carry only the devices it serves.

---

Validation #

A registry is validated by the questions it can answer without ambiguity.

• No overlapping intervals. The exclusion constraint enforces it in the database; assert it again on load so a registry exported to CSV and re-imported cannot smuggle an overlap back in.
• No gaps inside a deployment’s lifetime. A device that reported continuously must not have a period with no deployment row. Query for readings whose resolution failed with no_deployment_at_time — a nonzero count is a registry gap, not a data problem.
• Coordinates inside the site polygon. If sites have boundaries, every deployment coordinate should fall inside its own site. This catches transposed latitude and longitude better than any range check, because a swap usually stays inside the valid numeric range.
• Calibration continuity. Between two consecutive calibration rows, the coefficient change should be small. A slope that moves from 1.02 to 3.4 is a data-entry error; flag any change exceeding a factor you consider physically implausible for the instrument.
• Round-trip test. Resolve a known reading, then re-resolve it after exporting and reimporting the registry. The registry_version and every resolved field must be identical.

For a 200-device network with three years of history, expect roughly 260 deployment rows, 900 calibration rows, and an unknown-device rate below 0.05% once provisioning is disciplined.

---

Failure Modes and Edge Cases #

A device identifier that gets reused. Some vendors reassign serials after a return-to-base repair. If device_id is reused, the entire temporal model silently merges two physical instruments. Detect it by asserting that a device’s deployments do not span an implausible distance, and mitigate it by keying on a composite of serial and commissioning date.

Backdated registry edits. Someone corrects a coordinate that was wrong for six months. Every reading in that period was already resolved against the wrong value. Because each row carries registry_version, you can identify and reprocess exactly the affected rows — but only if you never update a registry row in place. Insert a correction with its own validity interval instead.

The registry lags the fleet. A field team installs sensors on Monday and files the paperwork on Friday. Readings arrive with no deployment row and are quarantined. This is the correct behaviour, but it needs an operational answer: a quarantine table that is replayed after registry updates, not a dead-letter queue nobody reads.

Timezone-naive validity intervals. A valid_from stored without a timezone is compared against a UTC observation time using the server’s local zone. During a daylight-saving transition, an hour of readings joins to the wrong deployment. Store timestamptz, always.

Height above what? Metres above ground and metres above sea level differ by hundreds of metres and both are called “height”. Name the column for its datum (height_agl_m) or store the datum alongside it. Air quality comparisons between a street-level and rooftop monitor are meaningless without it.

Unbounded current intervals. Representing “still current” as valid_to = NULL is right; representing it as a far-future date such as 9999-12-31 works until something computes a duration and produces an eight-thousand-year deployment. Use NULL or an unbounded range, and handle it in one place.

---

Integration #

Upstream, the registry consumes nothing — it is authored by humans and provisioning systems, and its correctness is an operational discipline rather than a pipeline stage. Downstream, almost everything depends on it:

1. Ingest resolves each reading’s coordinates before CRS transformation, because you cannot project a coordinate you do not have.
2. Calibration reads the coefficient pair valid at observation time, which is what makes sensor drift correction reproducible across a recalibration boundary.
3. Spatial analysis groups by site_id rather than by coordinate, so a sensor swapped for an identical unit at the same mast keeps its time series intact.
4. Export ships a registry snapshot beside the data, so a published dataset remains interpretable by someone who will never have access to your database.

The two guides below cover the parts that carry the most implementation risk: the temporal join itself, and the payload schema guard that runs immediately before it.

---

FAQ #

Why not just put latitude and longitude on every reading?

You should store the resolved coordinate on the reading — but it has to come from somewhere authoritative. A sensor that reports its own position gives you GPS jitter on a stationary instrument, and a sensor with no GNSS gives you nothing at all. The registry is the authority; the reading carries the resolved value so that a later registry correction is visible as a difference rather than silently rewriting years of history.

How do I handle a sensor that was physically moved?

Close the current deployment row by setting its valid_to, then insert a new deployment row with the new location and a valid_from at the move time. Readings before the boundary keep joining to the old location and readings after it to the new one. Never update the location in place: every aggregate ever computed from the old rows becomes unreproducible the moment you do.

Should the registry live in the same database as the readings?

For most networks, yes. A registry is small — thousands of rows against billions of readings — and co-locating it makes the join a local hash join instead of a cross-system lookup. Keep it in its own schema with its own migration history so the two are operationally separable, and export a snapshot with every published dataset so the data stays interpretable without the database.

What is the minimum viable registry for a small deployment?

Three tables: device (serial, model, manufacturer), deployment (device, location, height, valid_from, valid_to) and calibration (device, coefficients, valid_from, valid_to). Everything else — ownership, maintenance logs, network credentials — can start as columns and be promoted to tables when they grow their own history.

How often should the ingest process reload the registry?

On a version change, not on a timer and never per message. Publish a monotonically increasing registry version, have the ingest process compare it every few seconds, and rebuild the in-memory index only when it moves. A per-message lookup turns a microsecond dictionary hit into a millisecond round trip and caps your throughput at a few thousand readings per second.

---

Related #

• IoT Sensor Data Ingestion & Spatial Synchronization — the ingestion section this registry underpins
• Modelling Sensor Deployment History with Validity Intervals — the temporal-join pattern in full, with the SQL that makes it fast
• Validating Sensor Payloads with Pydantic — the schema guard that runs immediately before the registry join
• Spatial CRS Mapping on Ingest — where registry coordinates are projected into the storage CRS
• Cross-Device Sensor Normalization Techniques — the downstream consumer of the calibration coefficients stored here