← Back to the chapter · Next detail →

Technical detail · Data Quality Monitoring

Record the quality contract

This PostgreSQL table keeps device identity, measurement and arrival times, schema version, value, unit, message identity, routing state, and reason flags together. Its composite primary key supports duplicate detection. Rejected input has no state in this observation table.

CREATE TABLE telemetry_observation (
  device_id       text        NOT NULL,
  event_time      timestamptz NOT NULL,
  receive_time    timestamptz NOT NULL DEFAULT now(),
  schema_version  integer     NOT NULL,
  metric          text        NOT NULL,
  value_num       double precision,
  unit            text        NOT NULL,
  message_id      text        NOT NULL,
  quality_state   text        NOT NULL CHECK (
    quality_state IN ('accepted', 'flagged', 'quarantined')
  ),
  quality_flags   jsonb       NOT NULL DEFAULT '{}'::jsonb,
  PRIMARY KEY (device_id, event_time, metric, message_id)
);

← Back to the chapter · Next detail →