Article / Industrial Software
SCADA/HMI

SQL traceability — one event, even when delivery retries

Give events a source identity, keep capture and receipt times separate, and enforce duplicate handling in the database rather than the UI.

Published: · ING-MC LAB
A repeated source event reaches a unique database key and produces only one row.
Original event-ingestion diagram. The example key is a proposed integration pattern, not a deployed service contract.

The device sends an event, the server stores it, and the acknowledgement is lost. The device retries. If every HTTP request creates a new row, one physical observation becomes two records. Correct traceability starts with the identity of the event, not the number of deliveries.

Define what you are storing

A sampled snapshot is not the same as a state transition. “The machine is running” sampled every second is a series of observations; “the machine changed from stopped to running” is an event derived from trusted observations. Do not count snapshots as starts or invent transitions across a data gap.

For an example telemetry system, identify each observation by device_id, boot_id and sequence. Use a separate process correlation ID when several events belong to one transfer or batch. Neither identifier authenticates a sender: authorization and integrity checks happen before database insertion.

Enforce uniqueness where concurrent writes meet

This is a SQLite teaching schema. Other engines need reviewed types and conflict syntax; do not paste it unchanged into SQL Server or Oracle.

CREATE TABLE telemetry_event (
  id INTEGER PRIMARY KEY,
  device_id TEXT NOT NULL,
  boot_id TEXT NOT NULL,
  sequence INTEGER NOT NULL CHECK (sequence >= 0),
  captured_at TEXT,
  received_at TEXT NOT NULL,
  quality TEXT NOT NULL,
  payload_json TEXT NOT NULL,
  UNIQUE (device_id, boot_id, sequence)
);

SQLite supports unique constraints and targeted UPSERT conflict handling. Use a unique key rather than a client-side “check then insert”: two requests can pass that check simultaneously.

Bind values through parameters. Do not construct SQL by concatenating a device name, JSON payload or user-supplied field. The exact parameter API depends on your driver.

Identical replay and conflicting replay need different outcomes

On first arrival, store the event. If the same key arrives again, compare the validated content with the stored observation. An identical payload is an idempotent retry. Different content under the same key is a conflict and should be recorded for investigation, not overwrite the accepted event.

Canonicalize or normalize content before comparison if your application treats equivalent JSON representations as identical. Otherwise, document that byte-for-byte equality is the rule. Make the rule deterministic and apply it consistently to authentication, validation and replay handling.

A generic INSERT OR IGNORE is too broad for this design: it can hide a different integrity failure. Target the intended duplicate key, verify the existing row and keep the response consistent with the actual storage outcome. Use a transaction when additional event/index updates must succeed together.

Keep two clocks

captured_at describes the source observation; received_at describes arrival at the server. Store UTC timestamps with a documented format. If the source lacks a synchronized clock, keep capture time absent or explicitly estimated rather than silently substituting receipt time.

A delayed batch may arrive after a newer observation. Sort a device session by sequence when reconstructing its order; use capture time only when its quality permits. Server receipt order is useful for transport diagnosis, not proof of process chronology.

Test failure paths with a small dataset

Use three cases: first insertion, identical retry and changed payload with the same key. Add a device reboot with a new boot ID and sequence zero; it should not collide with the preceding session. Then test a failed transaction and reconnect/retry behavior.

Define retention, indexes and backup/restore verification from the real reporting requirement. A database connection that opens is not proof of recoverable history. Keep public examples free of client names, production payloads and credentials. The telemetry guide explains why duplicate delivery should not refresh a stale display.