A value derived from another sensor trips stuck-sensor and multivariate checks; plus idempotent TimescaleDB rollups and a JSONB view
Three things from one review of a sensor pipeline. (1) A reading that is computed from another reading (here a pH estimated as a clamped linear function of dissolved solids) breaks sensor QC written for real probes: the clamp makes it exactly constant over a whole input range, so a 'same value N times in a row means stuck' rule fires after a few ordinary samples, and in a Mahalanobis distance it inflates the score by sqrt(2/(1+rho)) where rho is the (shrunk) correlation, i.e. between 8% and 41% for shrinkage keeping 0.7 to 0 of the correlation. Fix that worked: keep a tuple of measured channels, skip the derived one in QC, exclude it when choosing the lead channel, and zero its z-score (and its history column) before the distance so the 3x3 code path stays intact. Tests cover both modes by setting the env flag and importlib.reload-ing the module. (2) TimescaleDB on a managed service: adding hourly and daily continuous aggregates next to an existing 5-minute one worked with CREATE MATERIALIZED VIEW IF NOT EXISTS ... WITH (timescaledb.continuous) ... WITH NO DATA, then ALTER ... SET (timescaledb.materialized_only = false), then add_continuous_aggregate_policy(..., if_not_exists => TRUE) with start_offset 7 days / end_offset 1 hour / schedule 1 hour, and 30 days / 1 day / 1 day; each needs autocommit (cannot run in a transaction) and was run in its own try so one failure does not stop the rest. timescaledb_information.continuous_aggregates listed all three afterwards and queries with bucket > now() - make_interval(hours => %s) returned rows at once because real-time aggregation was on. (3) Per-method scores stored inside a JSONB column were exposed as a plain view with jsonb_array_elements(CASE WHEN jsonb_typeof(x) = 'array' THEN x ELSE '[]'::jsonb END) in a CTE and UNION ALL per method; the CASE guard matters because jsonb_array_elements raises on a scalar or object.