Designing a TPMS Event Pipeline for Calgary Fleets: Schemas, Sensor Provenance, Temperature Normalization, Alert Debouncing, and Audit Logs

A tire-pressure monitoring stream looks simple until it reaches production. A sensor reports pressure, a gateway forwards the reading, and software compares the number with a threshold. That three-step sketch hides nearly every difficult part: units, clocks, identity, calibration, duplicated packets, temperature changes, missing data, axle-specific limits, and the difference between a warning that deserves action and a transient caused by moving a truck from a heated bay into a Calgary cold snap.

This article designs a practical event pipeline for a mixed fleet. The goal is not to diagnose every vehicle problem. It is to turn TPMS telemetry into traceable operational evidence while keeping drivers and tire-service staff in the loop. A tire-pressure event can support an inspection decision; it cannot prove the cause of a leak, replace the vehicle placard, or authorize unrelated mechanical work.

Calgary makes the engineering problem especially interesting. A unit may leave a garage at +12 °C, sit outside at −24 °C, enter stop-and-go traffic on Deerfoot Trail, and finish with warm casings after a run on Stoney Trail. A Chinook can move ambient temperature by double digits within hours. Those changes are not edge cases. They are ordinary operating conditions, so the data model and alert rules must represent them explicitly.

The design below uses plain SQL, JSON, and pseudocode. The examples are illustrative rather than readings from a real fleet. They apply whether ingestion comes from original-equipment telematics, an aftermarket receiver, or a periodic yard scanner, provided the source exposes enough metadata to establish provenance.

1. Define the decision before choosing the threshold

Start with the operational question: “Which wheel positions need a human pressure check, and why?” That is narrower and safer than “Which tires are bad?” The pipeline should prioritize observations, preserve uncertainty, and show the evidence behind each state transition.

A useful first version produces four outcomes:

1. Normal: sufficient recent evidence is inside the configured operating band.
2. Observe: a reading is unusual, but persistence or data quality is insufficient.
3. Inspect: repeatable evidence indicates pressure should be checked with an appropriate gauge.
4. Unavailable: telemetry is stale, contradictory, or missing.

Do not let “unavailable” silently become “normal.” A sensor that stopped transmitting on a cold route has not demonstrated healthy pressure. Equally, one packet below a threshold should not automatically create a high-severity incident. The business rule needs both a pressure condition and an evidence condition.

Configuration should originate from authoritative vehicle information. The driver-door placard or the fleet’s verified fitment record defines cold inflation pressure for the installed configuration. The molded number on a sidewall is not the routine target; how to read tire sidewall information helps explain why maximum markings and service specifications are different concepts. For mixed commercial units, load range, axle use, and actual configuration matter. If a fleet cannot establish a verified target, the software should flag missing configuration rather than inventing one.

This decision-first framing prevents a common architecture mistake: putting a single pressure_psi < 30 rule in the ingestion service. A universal number ignores placard differences and makes later corrections hard to audit. Ingestion should preserve facts. Evaluation should apply versioned policy.

2. Establish measurement semantics and units

Pressure is meaningless without stating whether it is absolute or gauge pressure. Most vehicle-facing tire values are gauge pressure: pressure relative to surrounding atmosphere. Some silicon sensing elements measure absolute pressure and rely on a receiver or algorithm to convert it. The pipeline must record the semantic form received.

Use SI internally where possible:

• pressure: kilopascals, kPa
• temperature: degrees Celsius, °C
• time: UTC timestamps plus a named local zone for display
• distance: kilometres
• speed: kilometres per hour

Store the original value and unit as well as the normalized value. A conversion bug then remains discoverable.

1 psi = 6.894757 kPa
1 bar = 100 kPa
°C = (°F - 32) × 5/9

Avoid prematurely rounding normalized pressure. If a device sends 34.7 psi, retaining approximately 239.25 kPa is useful even if the interface displays 239 kPa. Round at presentation boundaries, not during ingestion.

Temperature needs a source label. ambient_temp_c, sensor_temp_c, and tire_cavity_temp_c are not interchangeable. A TPMS sensor may report a temperature influenced by the wheel cavity, braking heat, sunlight, and sensor placement. A weather feed may describe conditions kilometres away. A gateway thermometer may sit behind a windshield. Each can be useful, but none should be renamed “temperature” without qualification.

The pipeline should also preserve accuracy and resolution when the vendor documents them. A sensor that reports integer psi does not provide tenth-psi certainty simply because the database uses decimals. Suggested fields include:

{
  "pressure_raw": 34,
  "pressure_raw_unit": "psi_gauge",
  "pressure_kpa_gauge": 234.4217,
  "pressure_resolution_kpa": 6.894757,
  "pressure_accuracy_kpa": null,
  "temperature_raw": -4,
  "temperature_raw_unit": "celsius_sensor",
  "sensor_temperature_c": -4.0
}

Null accuracy means “not established,” not zero error. This distinction becomes important when readings from different sensor families are compared.

3. Model assets, wheel positions, installations, and sensors separately

Vehicle identity, wheel position, tire identity, and sensor identity change on different schedules. Combining them into one mutable record erases history. A sensor may move during a seasonal change, a tire may be replaced, and a trailer may connect to several tractors. The event pipeline needs temporal relationships.

A compact relational model can use:

CREATE TABLE fleet_asset (
    asset_id           uuid PRIMARY KEY,
    fleet_id           uuid NOT NULL,
    external_label     text NOT NULL,
    asset_type         text NOT NULL CHECK (asset_type IN
                         ('passenger','van','light_truck','tractor','trailer')),
    timezone_name      text NOT NULL DEFAULT 'America/Edmonton',
    active_from        timestamptz NOT NULL,
    active_to          timestamptz,
    UNIQUE (fleet_id, external_label)
);

CREATE TABLE wheel_position (
    wheel_position_id  uuid PRIMARY KEY,
    asset_id           uuid NOT NULL REFERENCES fleet_asset(asset_id),
    position_code      text NOT NULL,
    axle_number        integer NOT NULL CHECK (axle_number > 0),
    side_code          text NOT NULL CHECK (side_code IN ('L','R','C')),
    dual_index         integer CHECK (dual_index IN (1,2)),
    active_from        timestamptz NOT NULL,
    active_to          timestamptz,
    UNIQUE (asset_id, position_code, active_from)
);

CREATE TABLE tpms_sensor (
    sensor_id          uuid PRIMARY KEY,
    manufacturer       text,
    model              text,
    hardware_serial_hash text NOT NULL,
    protocol_family    text NOT NULL,
    pressure_semantics text NOT NULL CHECK (pressure_semantics IN
                         ('gauge','absolute','vendor_converted')),
    commissioned_at    timestamptz,
    retired_at         timestamptz
);

CREATE TABLE sensor_installation (
    installation_id    uuid PRIMARY KEY,
    sensor_id          uuid NOT NULL REFERENCES tpms_sensor(sensor_id),
    wheel_position_id  uuid NOT NULL REFERENCES wheel_position(wheel_position_id),
    valid_from         timestamptz NOT NULL,
    valid_to           timestamptz,
    installed_by_ref   text,
    evidence_ref       text,
    CHECK (valid_to IS NULL OR valid_to > valid_from)
);

The serial should usually be hashed or tokenized in general analytics views. Raw identifiers can allow tracking beyond the fleet’s purpose. Keep the mapping in a restricted operational store if it is needed for maintenance.

Temporal joins are critical. A packet observed at 08:15 must join the installation valid at 08:15, not whichever sensor is attached to that wheel today. If the system learns an installation interval late, use a correction record and rerun affected evaluations without rewriting the original event.

Position codes need a fleet-wide convention such as A1-L, A1-R, A2-L-IN, and A2-L-OUT. Do not rely on array order. Dual-wheel ambiguity can turn a useful alert into an inspection of the wrong tire.

For background on matching configuration to service requirements, load index and tire capacity concepts are useful, while fleet-specific targets still need verified vehicle and operating information.

4. Use an immutable event envelope

Every incoming reading should receive a common envelope before vendor-specific parsing disappears. The envelope supports replay, deduplication, and audit review.

{
  "event_id": "01J3TPMS9G1K2D2H5Y7Z8A1B2C",
  "schema_version": "tpms.observation.v1",
  "tenant_id": "fleet-demo",
  "source_system": "gateway-family-a",
  "source_message_id": "gw17:884129",
  "sensor_token": "s_8f1d...",
  "gateway_token": "g_17a2...",
  "observed_at": "2026-01-14T14:02:11.420Z",
  "received_at": "2026-01-14T14:02:16.083Z",
  "ingested_at": "2026-01-14T14:02:16.204Z",
  "sequence_number": 884129,
  "payload": {
    "pressure": 228,
    "pressure_unit": "kPa_gauge",
    "sensor_temperature": -18,
    "temperature_unit": "celsius",
    "battery_state": "ok"
  },
  "transport": {
    "protocol": "vendor_radio_to_lte",
    "receiver_rssi_dbm": -76
  },
  "payload_sha256": "illustrative-hex-digest"
}

event_id identifies the platform’s immutable copy. source_message_id identifies the upstream message when one exists. The payload digest detects altered retransmissions. None of these alone is a complete deduplication strategy.

Keep raw payloads in a restricted landing store for a bounded period. Parsed canonical observations belong in a durable table. Dead-letter records should contain a failure code and enough safe context to reproduce parsing, but should not scatter raw personal or location information into logs.

An envelope also gives schema evolution a home. New optional fields can arrive under v1; incompatible semantic changes require v2. Consumers must reject unknown major versions or route them to quarantine. “Best effort” parsing of a changed pressure unit is too dangerous.

5. Separate event time, receiver time, and ingestion time

Three clocks describe different facts:

• observed_at: when the sensor or source says the measurement occurred
• received_at: when the fleet gateway or vendor platform saw it
• ingested_at: when the pipeline accepted it

They should never be collapsed. A trailer gateway may buffer packets outside coverage and upload them later. An old low-pressure observation arriving after a new normal one should enrich history, not reverse the live state.

Event-time processing uses observed_at for windows and state order, subject to a lateness policy. Ingestion-time processing uses ingested_at for operational metrics such as pipeline latency. Receiver time helps isolate whether delay occurred before or after the vendor service.

Define clock-quality fields:

ALTER TABLE tpms_observation ADD COLUMN observed_time_source text
  CHECK (observed_time_source IN ('sensor_clock','gateway_clock',
                                  'vendor_clock','ingestion_substitute'));
ALTER TABLE tpms_observation ADD COLUMN clock_skew_estimate_ms integer;
ALTER TABLE tpms_observation ADD COLUMN time_quality text
  CHECK (time_quality IN ('trusted','estimated','suspect','unknown'));

If a sensor has no clock, the gateway time may be the best event-time approximation. Record that choice. If observed_at lies 30 days in the future, quarantine it rather than extending a processing watermark.

A practical live evaluator might accept events up to ten minutes late, update historical aggregates for events up to seven days late, and require manual review beyond that. Those durations are illustrative; fleets should tune them to route connectivity and operational need.

For Calgary routes, lateness can correlate with geography. A unit travelling industrial outskirts or remote approaches may reconnect after returning toward city coverage. That is a transport characteristic, not pressure behaviour, and dashboards should show it separately.

6. Validate without destroying evidence

Validation should classify events as accepted, accepted-with-warning, or quarantined. It should not quietly coerce impossible values into plausible ones.

Core rules include:

Field	Example rule	Failure handling
pressure	numeric and declared unit	quarantine if absent or unrecognized
gauge pressure	0–1,500 kPa broad transport bound	quarantine outside bound
sensor temperature	−80 to +150 °C broad device bound	warn or quarantine by vendor
observed time	not beyond allowed future skew	quarantine
sensor token	valid known format	quarantine
schema version	supported major version	quarantine
sequence	non-negative when present	warn on rollback
asset mapping	installation valid at event time	accept as unmapped, then reconcile

Broad transport bounds catch corruption; they are not alert limits. A 150 kPa reading can be structurally valid yet operationally low for one configured position. That judgment belongs in policy evaluation.

Validation pseudocode:

def validate(envelope, registry, now):
    issues = []
    if envelope.schema_version not in SUPPORTED_SCHEMAS:
        return quarantine("UNSUPPORTED_SCHEMA")

    p = parse_decimal(envelope.payload.get("pressure"))
    unit = envelope.payload.get("pressure_unit")
    if p is None or unit not in ALLOWED_PRESSURE_UNITS:
        return quarantine("PRESSURE_FORMAT")

    normalized = convert_pressure_to_kpa_gauge(
        value=p,
        unit=unit,
        atmospheric_context=envelope.payload.get("atmospheric_pressure_kpa")
    )
    if normalized is None:
        return quarantine("ABSOLUTE_TO_GAUGE_CONTEXT_MISSING")
    if not Decimal("0") <= normalized <= Decimal("1500"):
        return quarantine("PRESSURE_TRANSPORT_RANGE")

    if envelope.observed_at > now + timedelta(minutes=5):
        return quarantine("FUTURE_EVENT_TIME")
    if registry.sensor(envelope.sensor_token) is None:
        issues.append("UNREGISTERED_SENSOR")

    return accept(normalized=normalized, warnings=issues)

Converting absolute pressure to gauge pressure requires atmospheric context. Subtracting a fixed 101.325 kPa may be adequate for a rough demonstration, but elevation and weather alter ambient pressure. Production conversion should use documented vendor semantics and an appropriate atmospheric estimate, or retain the observation without claiming gauge equivalence.

7. Capture calibration and provenance as first-class data

“The sensor said 228 kPa” is incomplete. Which sensor, firmware, receiver, transformation, and calibration status produced that value? Provenance makes the answer reviewable.

Create append-only calibration records:

CREATE TABLE sensor_calibration (
    calibration_id       uuid PRIMARY KEY,
    sensor_id            uuid NOT NULL REFERENCES tpms_sensor(sensor_id),
    performed_at         timestamptz NOT NULL,
    method_code          text NOT NULL,
    reference_device_id  uuid,
    reference_trace_ref  text,
    pressure_points_kpa  jsonb NOT NULL,
    fitted_offset_kpa    numeric(8,3),
    fitted_scale         numeric(10,7),
    uncertainty_kpa      numeric(8,3),
    valid_from           timestamptz NOT NULL,
    valid_to             timestamptz,
    result               text NOT NULL CHECK (result IN
                           ('pass','limited','fail','unknown')),
    created_by           text NOT NULL,
    created_at           timestamptz NOT NULL DEFAULT now()
);

Some sensors are factory calibrated and never field-adjusted. Represent that honestly with a provenance record sourced from manufacturer documentation, not a fictional shop calibration. A comparison against a known gauge is a verification observation, not automatically a formal calibration.

For each canonical reading, save the transformation lineage:

{
  "normalization": {
    "parser_version": "vendor-a/2.4.1",
    "conversion_rule": "psi_gauge_to_kpa_gauge",
    "conversion_version": "units/1.0.0",
    "calibration_id": null,
    "calibration_status": "factory_claim_unverified",
    "input_digest": "illustrative-digest"
  }
}

Firmware belongs in provenance because a vendor update may change resolution or encoding. Receiver identity matters because two nearby gateways can hear one broadcast. Policy version is separate: the sensor produced an observation; the rules engine produced a finding.

Fleet teams should document when a gauge check disagrees with telemetry. If a tire needs physical inspection or repair assessment, tire repair information for Calgary explains the service context. The software should not infer repairability from pressure decline alone.

8. Normalize temperature without pretending physics is perfect

Pressure changes with temperature even when the contained air mass is unchanged. A simplified constant-volume ideal-gas relationship can normalize an observation to a reference absolute temperature:

P₂(abs) = P₁(abs) × T₂(K) / T₁(K)

Gauge pressure must first be converted to absolute pressure by adding ambient atmospheric pressure. Celsius must become kelvin by adding 273.15. After normalization, subtract reference ambient pressure to return to gauge pressure.

def normalize_gauge_pressure(
    measured_gauge_kpa,
    measured_temp_c,
    reference_temp_c,
    ambient_kpa_at_measurement,
    ambient_kpa_at_reference,
):
    t1 = measured_temp_c + 273.15
    t2 = reference_temp_c + 273.15
    if t1 <= 0 or t2 <= 0:
        raise ValueError("invalid absolute temperature")
    p1_abs = measured_gauge_kpa + ambient_kpa_at_measurement
    p2_abs = p1_abs * (t2 / t1)
    return p2_abs - ambient_kpa_at_reference

That result is an estimate, not a replacement for a cold-pressure measurement. Tire volume changes, the temperature field is not uniform, moisture affects behaviour, rolling warms the assembly, and reported sensor temperature may not equal gas temperature. The pipeline should store:

• measured gauge pressure
• measured temperature and its source
• reference temperature
• atmospheric input and its source
• normalized estimate
• model version
• uncertainty or confidence class

Never overwrite the measured value. The normalized estimate supports comparison across transitions; the original drives traceability.

Consider a van leaving a +15 °C garage for −20 °C ambient. A reading taken immediately outside may reflect a warm tire cavity while the weather feed reflects cold air. Using weather temperature as if it were cavity temperature can overcorrect pressure. Mark the state thermal_transition until dwell time, sensor temperature slope, or driving context indicates relative stability.

One useful model has three thermal states:

1. garage_exit: ambient-to-sensor difference exceeds a configured amount and the asset recently crossed a depot geofence.
2. cold_soak: asset has remained parked outdoors long enough and sensor temperature slope is small.
3. rolling_warm: speed or wheel activity shows travel, with pressure and temperature rising together.

Chinooks create the reverse transition. A vehicle cold-soaked overnight can experience rapidly warming air while its wheel mass lags. Temperature normalization should expose the mismatch, not manufacture certainty.

9. Build a deduplication strategy in layers

Radio observations are frequently duplicated. Multiple gateways may receive the same broadcast, an upstream API may retry after timeout, and a consumer may replay a batch. Exactly-once delivery is usually an application claim built on idempotency, not a property of the entire path.

Layer one uses a vendor message key when stable:

CREATE UNIQUE INDEX uq_tpms_source_message
ON tpms_raw_event (source_system, source_message_id)
WHERE source_message_id IS NOT NULL;

Layer two creates a deterministic fingerprint for messages without reliable IDs:

fingerprint = SHA-256(
  tenant_id ||
  sensor_token ||
  rounded_observed_at_to_protocol_resolution ||
  raw_pressure_value ||
  raw_pressure_unit ||
  raw_temperature_value ||
  sequence_number
)

The fingerprint must reflect protocol resolution. Rounding all events to a minute could collapse legitimate readings. Including receiver identity may prevent merging the same radio packet heard by two receivers, so it belongs in reception metadata rather than the sensor-observation fingerprint.

Layer three performs semantic suppression in the alert engine. Two distinct observations can both be valid but should not create two notifications. That is alert correlation, not event deduplication.

Keep a duplicate ledger with first_event_id, duplicate_event_id, matching rule, and discovery time. Discarding duplicates without a trace makes transport problems invisible. Duplicate rate is a useful health metric by gateway and firmware.

Sequence numbers help but can wrap or reset after battery work. Treat a rollback as a warning evaluated with boot identifiers and time gaps. Never assume sequence_number is globally unique.

10. Evaluate pressure policy against versioned configuration

Policy should be data, not scattered conditionals. A pressure profile can be effective-dated by asset, axle, or position:

CREATE TABLE pressure_policy (
    pressure_policy_id   uuid PRIMARY KEY,
    wheel_position_id    uuid NOT NULL REFERENCES wheel_position(wheel_position_id),
    target_cold_kpa      numeric(8,2) NOT NULL,
    observe_below_kpa    numeric(8,2) NOT NULL,
    inspect_below_kpa    numeric(8,2) NOT NULL,
    critical_below_kpa   numeric(8,2),
    max_above_kpa        numeric(8,2),
    basis_code           text NOT NULL,
    evidence_ref         text NOT NULL,
    valid_from           timestamptz NOT NULL,
    valid_to             timestamptz,
    policy_version       integer NOT NULL,
    CHECK (inspect_below_kpa <= observe_below_kpa),
    CHECK (target_cold_kpa > observe_below_kpa)
);

Thresholds in this article are intentionally not prescribed. They depend on the vehicle placard, tire configuration, load and fleet policy. The database constraints merely preserve ordering.

Evaluation reads the policy valid at event time. It emits a pressure_finding with:

• observation event ID
• installation and wheel position IDs
• pressure policy ID and version
• measured and normalized values
• thermal state
• data-quality flags
• candidate severity
• rule trace
• evaluator software version

If the installed tire or axle configuration changes, close the old policy interval and create a new version. Do not edit history. That method supports a later question such as, “Why did this reading create an inspection state last January?”

The broader tire configuration can be documented through fleet management information and commercial tire service context, but telemetry policy still needs fleet-owned evidence rather than assumptions copied from a web page.

11. Debounce alerts with persistence, hysteresis, and rate limits

False alarms teach people to ignore the system. Debouncing requires more than waiting 30 seconds. It combines persistence, recovery hysteresis, data quality, thermal state, and notification limits.

An illustrative state machine:

def transition(state, finding, history, policy):
    if finding.data_quality in {"invalid", "unmapped"}:
        return state.with_observation_only(finding)

    if finding.thermal_state == "garage_exit":
        return state.to("OBSERVE", reason="THERMAL_TRANSITION")

    low = finding.measured_kpa < policy.inspect_below_kpa
    corroborated = history.count_distinct_low(
        window_minutes=8,
        distinct_event_minimum=3,
        minimum_span_minutes=4
    ) >= 3

    falling_fast = history.robust_slope_kpa_per_minute(
        window_minutes=12
    ) < policy.rapid_loss_slope

    if low and (corroborated or falling_fast):
        return state.to("INSPECT", reason=(
            "RAPID_LOSS" if falling_fast else "PERSISTENT_LOW"
        ))

    recovered = history.all_recent_above(
        threshold_kpa=policy.observe_below_kpa + policy.recovery_margin_kpa,
        count=4,
        minimum_span_minutes=10
    )
    if state.name == "INSPECT" and recovered:
        return state.to("RECOVERED_PENDING_CONFIRMATION")

    return state

Three readings are not useful if they are retransmissions of one broadcast. Count distinct canonical observations and require a minimum time span. A robust slope, such as Theil–Sen or a median of pairwise slopes, resists one outlier better than a simple difference.

Hysteresis means the recovery threshold is higher than the entry threshold. Without it, readings near a boundary cause repeated open-close cycles. A recovery state can wait for a verified gauge check or a longer stable series, depending on operational policy.

Notification rate limits are separate from state transitions. The audit record should still show every meaningful transition even if paging is suppressed. A sensible routing layer might send one initial notice, one escalation when severity rises, and a periodic unresolved summary. The exact cadence should be chosen with dispatchers and drivers.

Route context can modify confidence, not erase evidence. Pressure and temperature often rise together during sustained Stoney Trail travel. Stop-and-go Deerfoot traffic can generate a different heating curve. The evaluator may label these regimes, yet a persistent or rapidly falling measured pressure still deserves attention.

12. Distinguish leaks, thermal shifts, and sensor faults cautiously

Telemetry can classify patterns; it rarely proves causes. Build explanations as hypotheses with evidence.

A thermal-shift pattern often shows:

• similar directional change across several wheel positions
• pressure change correlated with sensor temperature
• stable relative ranking among tires
• no continuing decline after temperature stabilizes

A possible leak pattern may show:

• one position diverging from peers
• continued decline after thermal stabilization
• repeat decline after verified inflation
• slope inconsistent with neighbouring positions

A possible sensor issue may show:

• impossible jumps with no intermediate values
• frozen values while temperature and peers change
• frequent sequence resets or checksum failures
• disagreement with a documented manual measurement
• battery or radio-quality warnings

Label outputs consistent_with_thermal_change, possible_pressure_loss, or possible_sensor_fault. Avoid declaring puncture, valve failure, wheel damage, or another cause without physical evidence.

Wheel imbalance is also not diagnosed by TPMS. If drivers report vibration, wheel balancing service information describes a tire-service path, while unrelated mechanical concerns should be referred to an appropriate provider. Keeping those boundaries explicit protects both fleet workflow and data credibility.

Peer comparison needs compatible positions. Comparing a loaded trailer dual with a passenger van front tire is nonsense. Even same-asset comparisons must respect axle targets. Use dimensionless deviation from each verified target when making cross-position summaries:

target_ratio = measured_gauge_kpa / configured_target_cold_kpa

The ratio is contextual evidence, not a universal safety score.

13. Persist an auditable chain of decisions

An audit log should answer who, what, when, why, and based on which version. It should not be a mutable text field on the current alert.

CREATE TABLE pressure_case_event (
    case_event_id       uuid PRIMARY KEY,
    case_id             uuid NOT NULL,
    occurred_at         timestamptz NOT NULL,
    recorded_at         timestamptz NOT NULL DEFAULT now(),
    event_type          text NOT NULL,
    actor_type          text NOT NULL CHECK (actor_type IN
                         ('system','driver','dispatcher','technician','admin')),
    actor_token         text NOT NULL,
    reason_code         text NOT NULL,
    details             jsonb NOT NULL,
    source_event_ids    uuid[] NOT NULL,
    policy_version      text,
    software_build      text,
    previous_hash       text,
    record_hash         text NOT NULL
);

Hash chaining can make accidental or unauthorized alteration evident, though it does not replace access controls or backups. Canonical JSON serialization is essential; otherwise equivalent objects produce different hashes.

Possible case events include CASE_OPENED, NOTICE_SENT, NOTICE_SUPPRESSED, DRIVER_ACKNOWLEDGED, GAUGE_CHECK_RECORDED, TIRE_SERVICE_RECORDED, STATE_RECOVERED, and CASE_CLOSED. A free-text note can accompany a reason code, but automation should never depend on parsing prose.

Corrections should append. If a wheel position was mapped incorrectly, add MAPPING_CORRECTED, specify old and new installation references, and rerun derived findings. Preserve the earlier output with superseded_by. This is more honest than making history look as if the error never happened.

Human measurements need units, device reference, and method context:

{
  "gauge_check": {
    "value": 232,
    "unit": "kPa_gauge",
    "measured_at": "2026-01-14T14:29:00Z",
    "device_token": "gauge_42",
    "tire_state": "recently_driven",
    "entered_by": "staff_token_17"
  }
}

“Recently driven” prevents that value from being misrepresented as verified cold pressure.

14. Add privacy, access, and retention boundaries

TPMS data becomes sensitive when joined with location, driver assignment, shift, and route. Minimize collection. The pressure engine usually needs asset and wheel-position tokens, not a driver’s name.

Use role-based views:

• drivers see assigned asset status and actionable instructions
• dispatch sees fleet cases and freshness
• tire-service staff see relevant wheel history and verified configuration
• engineers see pseudonymized telemetry and pipeline health
• administrators have narrowly audited identity access

Precise location should be coarsened or excluded unless route context is necessary and authorized. “Stoney Trail operating regime” may be enough for thermal analysis; a second-by-second personal movement trail often is not.

Retention should be purpose-based:

• raw vendor payload: short troubleshooting window
• canonical observation: long enough for seasonal analysis and case reconstruction
• case and audit events: according to fleet record obligations
• exact location joins: shorter, restricted duration
• derived aggregate: potentially longer if appropriately de-identified

Do not promise a universal schedule. Document the fleet’s legal, contractual, and operational basis, then automate deletion. A deletion ledger can record dataset, date range, policy version, count, and job run without retaining erased content.

Encryption in transit and at rest is baseline. Token rotation, least privilege, and access review matter just as much. Avoid placing sensor serials, driver names, or coordinates in notification URLs and ordinary application logs.

15. Design observability for the pipeline itself

Pressure alerts are unreliable if ingestion health is invisible. Measure:

• events received per sensor and gateway
• end-to-end latency percentiles
• late-event rate
• duplicate rate by matching rule
• quarantine rate by reason
• unmapped-sensor count
• clock-skew distribution
• sensor freshness by asset
• policy-evaluation failures
• notification suppression count
• case acknowledgement and closure timing

Freshness should be contextual. A parked seasonal unit may transmit differently from an active van. Define expected cadence by device family and asset state, then alert on absence relative to that expectation.

A useful service-level indicator is:

eligible observations evaluated within 60 seconds
-------------------------------------------------
all eligible observations ingested

“Eligible” must exclude quarantined events but include evaluation failures. Otherwise the metric can look perfect while bad messages disappear.

Dashboards should separate data-plane conditions from tire findings. A red gateway-outage chart does not mean every tire is low. Conversely, a healthy message rate does not validate the pressure itself.

Trace IDs can connect vendor receipt, canonical observation, finding, state transition, and notice. Keep high-cardinality identifiers out of metric labels; put them in logs or traces with controlled retention.

16. Work through a Calgary winter example

Consider illustrative asset VAN-27 with four sensors. It leaves a heated northeast depot at 06:50 local time. Outdoor air is −23 °C. The route uses Deerfoot Trail, includes urban stops, then returns via Stoney Trail.

At 06:48, all four sensor temperatures are near +11 °C and pressures are close to their configured cold targets. At 06:54, the gateway crosses the depot boundary. Ambient temperature falls abruptly, while sensor temperature declines gradually. The left rear reports one value just below its observe threshold.

A naive rule opens an incident. The stateful pipeline labels garage_exit, retains the observation, and starts a short evidence window. It does not describe the reading as false; it says the environment is transitioning.

By 07:08, all four positions have fallen in pressure as temperatures decline. Their target ratios remain closely grouped. The left rear no longer diverges from peers. The evaluator records consistent_with_thermal_change and closes the observation state without a pressure case.

At 11:40, after several stops, the left rear begins declining while the other three rise slightly with road heat. Canonical observations are distinct, receiver quality is adequate, and the robust 12-minute slope crosses the policy’s configured loss-rate boundary. A case opens with reason RAPID_LOSS, citing six event IDs and policy version 12.

The notice does not claim a puncture. It asks for a safe, verified pressure check under fleet procedure. If tire service is required, Calgary operators can review mobile tire service scope or emergency tire service information, depending on circumstances. Safety and fleet procedure determine whether the unit should continue; software should not improvise roadside instructions.

At 12:15, an authorized person records a gauge value, unit, device token, and recently-driven state. At 13:05, a service outcome is entered. The case closes only after policy-defined confirmation. Every transition remains attached to its inputs.

During the afternoon, a Chinook raises ambient temperature quickly. The asset is parked outside, so wheel temperature lags. The model marks another thermal transition and withholds temperature-normalized comparisons until confidence improves. Raw pressure remains visible throughout.

This example shows why event history matters. The same numeric reading can mean “watch during a garage exit” in one context and “inspect because one position is steadily diverging” in another.

17. Query fleet states without losing chronology

Operations often need the latest valid state, while engineering needs the full series. Materialize the latest state but derive it from append-only events.

WITH ranked AS (
  SELECT
    f.wheel_position_id,
    f.finding_id,
    f.observed_at,
    f.candidate_state,
    f.data_quality,
    row_number() OVER (
      PARTITION BY f.wheel_position_id
      ORDER BY f.observed_at DESC, f.ingested_at DESC, f.finding_id DESC
    ) AS rn
  FROM pressure_finding f
  WHERE f.superseded_by IS NULL
    AND f.observed_at >= now() - interval '24 hours'
)
SELECT wheel_position_id, finding_id, observed_at,
       candidate_state, data_quality
FROM ranked
WHERE rn = 1;

Ordering by event time first avoids letting late uploads overwrite newer conditions. Deterministic tie-breakers prevent inconsistent results. The live state machine may have more rules than this query; its output should also be stored as events rather than reconstructed differently by every dashboard.

For a fleet-wide cold-start view:

SELECT
  a.external_label,
  w.position_code,
  o.pressure_kpa_gauge,
  o.sensor_temperature_c,
  o.observed_at,
  p.target_cold_kpa,
  round(o.pressure_kpa_gauge / p.target_cold_kpa, 3) AS target_ratio
FROM tpms_observation o
JOIN sensor_installation i
  ON i.sensor_id = o.sensor_id
 AND o.observed_at >= i.valid_from
 AND (i.valid_to IS NULL OR o.observed_at < i.valid_to)
JOIN wheel_position w ON w.wheel_position_id = i.wheel_position_id
JOIN fleet_asset a ON a.asset_id = w.asset_id
JOIN pressure_policy p
  ON p.wheel_position_id = w.wheel_position_id
 AND o.observed_at >= p.valid_from
 AND (p.valid_to IS NULL OR o.observed_at < p.valid_to)
WHERE o.thermal_state = 'cold_soak'
  AND o.data_quality = 'accepted';

Half-open intervals—valid from inclusive, valid to exclusive—prevent double matches at a change boundary.

18. Test the system with adversarial cases

Unit tests for conversion are necessary but insufficient. Build fixtures that resemble messy field conditions.

Case A: duplicate broadcast through two gateways

Input: same sensor payload and protocol timestamp received by gateways G1 and G2 three seconds apart.

Expected: two reception records, one canonical observation, one duplicate-ledger entry, and no extra persistence count.

Case B: buffered low event arrives after recovery

Input: a low reading observed at 08:00 arrives at 09:10, after valid readings observed through 09:00.

Expected: historical series includes the late point; current state does not roll backward; a historical reevaluation note records whether the old window would have changed.

Case C: garage-to-outdoor transition

Input: four positions cool together after leaving a +14 °C bay for −25 °C air; pressure falls proportionally.

Expected: thermal-transition state, no immediate inspection notice, raw values preserved, later evaluation after stabilization.

Case D: one wheel diverges on Stoney Trail

Input: three positions warm and rise slightly; one declines over six distinct events with adequate signal quality.

Expected: rapid-loss candidate, inspect state if configured evidence requirements are met, explanation includes slope and peer divergence without naming a cause.

Case E: unit confusion

Input: vendor changes a field from psi to kPa without changing its schema declaration.

Expected: broad-range or vendor-contract validation quarantines suspicious messages; no policy evaluation occurs; an ingestion health alert opens.

Case F: sequence reset

Input: sequence changes from 65,534 to 0 after a documented device reboot.

Expected: observations remain distinct; reset warning attaches to provenance; no mass duplicate classification.

Case G: sensor moved between positions

Input: delayed event belongs to a time before the sensor moved from A1-L to A2-R.

Expected: temporal join maps the old event to A1-L.

Case H: unknown calibration status

Input: plausible pressure from a registered sensor with no accessible accuracy documentation.

Expected: reading can be retained, provenance says unknown, and interfaces avoid displaying false precision.

Case I: atmospheric context unavailable

Input: absolute pressure is received, but no trustworthy ambient pressure estimate exists.

Expected: raw absolute value remains stored; gauge normalization is null; the event cannot enter gauge-threshold policy.

Case J: notification replay

Input: state-transition consumer restarts and reprocesses the same outbox record.

Expected: idempotency key prevents a second external notice; audit log records the delivery retry.

Property-based tests can generate unit combinations, time orderings, and boundary values. Useful invariants include: normalization never mutates raw data; a duplicate cannot increase persistence count; later ingestion cannot make event time newer; and a policy finding always names exactly one policy version.

Load tests should simulate cold-start bursts when many depot vehicles wake at once. Chaos tests can pause a message partition, reorder events, and fail the notification provider. Recovery must be repeatable without multiplying cases.

19. Roll out with shadow decisions and human feedback

Begin by ingesting and validating without notifying. Compare telemetry with documented manual checks and measure data completeness by sensor family. Next, run the evaluator in shadow mode. Dispatchers can see candidate states, but the system does not send operational notices.

Review false positives by reason:

• thermal transition misclassified
• sensor mapping wrong
• stale configuration
• duplicate packets counted
• threshold policy unsuitable
• time ordering incorrect
• telemetry disagreed with gauge

Fix structural causes before tuning thresholds. Raising a threshold to hide bad sensor mapping creates a quieter but less trustworthy system.

Then enable notifications for a small, representative group. Include cold-soaked units, heated-garage units, city routes, Deerfoot travel, and Stoney Trail runs. A pilot made only of easy summer routes will not validate Calgary winter behaviour.

Feedback needs structured outcomes such as confirmed_low, reading_normal_on_gauge, sensor_mapping_error, sensor_suspected, configuration_error, and unable_to_verify. Free text can add context, but stable codes support measurement.

Seasonal tire work also changes installations and pressure policy. Integrate the handoff with the fleet’s records around seasonal tire changes. The pipeline should expect sensor relearns, position moves, and new effective dates rather than treating them as unexplained anomalies.

20. Draw a firm boundary between software and tire service

The pipeline’s job is evidence management. It can reveal a trend, track a wheel position, and retain inspection results. It cannot look inside a tire, determine repairability, verify fitment by itself, or make an unsupported safety promise.

KMJ Tire performs tire services and oil changes. The event workflow can route relevant tire-service needs to Calgary’s local tire shop information or let a fleet review online booking options. Concerns outside tire service and oil changes should go to an appropriate provider.

That boundary also improves architecture. A rules engine should not contain invented diagnostic labels. Store observations, state why a pressure case opened, and let qualified humans record what they actually found.

For replacement planning, the pipeline may link to shopping for tires in Calgary, but it must not claim availability or price. Telemetry should never become a pretext for fabricated urgency.

21. A production readiness checklist

Before enabling the first live notice, verify the following:

• every configured wheel position has an authoritative target and evidence reference
• raw pressure semantics are known for each sensor family
• original and normalized values are both retained
• observed, received, and ingested times are distinct
• clock source and quality are recorded
• installation joins are effective-dated
• deduplication is tested across gateways and retries
• late events cannot roll the current state backward
• thermal transitions are represented
• persistence counts canonical observations only
• recovery uses hysteresis
• policy and software versions appear in each finding
• audit events are append-only
• manual checks include units and measurement state
• access views minimize driver and location data
• retention deletion is automated and evidenced
• gateway outage is distinguishable from healthy pressure
• notification delivery is idempotent
• shadow-mode results have been reviewed across Calgary route types
• tire-service scope is kept distinct from unrelated mechanical work

Also rehearse failure. What happens when the vendor changes units, the registry is unavailable, or the atmospheric feed stops? A safe pipeline degrades to unavailable or observe with clear reasons. It does not convert uncertainty into normal status.

22. Closing design principle: preserve facts, version judgments

The most durable rule for a TPMS event platform is simple: preserve facts and version judgments. The raw packet is a fact about what a source transmitted. The canonical observation is a documented interpretation. Temperature normalization is a model output. A pressure finding is a policy result. An inspection result is a human observation. None should overwrite the others.

That separation handles Calgary’s real operating environment: cold starts, heated garages, Chinook swings, urban stops, and sustained ring-road travel. It also supports correction. When a mapping, calibration record, or policy changes, engineers can replay the facts and explain the new result.

A useful system is not the one with the most alerts. It is the one whose alerts can be trusted, whose uncertainty is visible, and whose history survives scrutiny. Build identity and time semantics first. Keep units and provenance explicit. Debounce from distinct evidence. Treat privacy and auditability as core data requirements. Then the pressure stream becomes operationally valuable without pretending it knows more than it does.