Lanes freight exceptions
World clock Tue 18:00:00 UTC held · 120×

Definitions

The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.

Reefer temperature excursion

reefer_excursion unit: containers operator detector canonical: cold_chain

A refrigerated container whose last readings have all been outside the band around its setpoint. One or two readings out of band are a defrost cycle or an open door; the rule waits for a run.

cold_chainCold Chain Lead · cold_chain v2canonical

Readings at or before now within lookback_hours. The current run is every reading after the container's last reading within tolerance_c of its setpoint; it counts when it has at least consecutive readings. The excursion started at the run's first reading.

latest change: A run needs four readings out of band, not three: on the tuning seed a door opening next to a defrost cycle made three-reading runs that were not excursions.

  • pass count_betweenobserved 5, expected between 0.0 and 60.0
  • pass uniqueobserved {'duplicates': 0}, expected container_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 5}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH r AS (
  SELECT container_id, "at", temp_c, setpoint_c, abs(temp_c - setpoint_c) > 2.5 AS out_band
  FROM reefer_readings
  WHERE "at" <= TIMESTAMP 'Tue 18:00:00' AND "at" > TIMESTAMP 'Tue 18:00:00' - INTERVAL (72) HOUR
),
last_ok AS (
  SELECT container_id, max("at") FILTER (WHERE NOT out_band) AS ok_at FROM r GROUP BY container_id
),
run AS (
  SELECT r.container_id, min(r."at") AS started_at, count(*) AS readings, max(r."at") AS last_at,
         max(r.temp_c - r.setpoint_c) AS max_above, min(r.temp_c - r.setpoint_c) AS max_below,
         arg_max(r.temp_c, r."at") AS last_temp, any_value(r.setpoint_c) AS setpoint
  FROM r JOIN last_ok o ON o.container_id = r.container_id
  WHERE r.out_band AND (o.ok_at IS NULL OR r."at" > o.ok_at)
  GROUP BY r.container_id
)
SELECT 'container' AS subject_type, u.container_id AS subject_id, u.container_id, c.shipment_id, s.shipper_id,
       u.started_at,
       CASE WHEN greatest(u.max_above, -u.max_below) >= 6.5 THEN 'critical' ELSE 'high' END AS severity,
       json_object(
         'container', c.number, 'shipment', s.ref, 'commodity', s.commodity, 'setpoint_c', u.setpoint,
         'last_temp_c', u.last_temp, 'max_deviation_c', round(greatest(u.max_above, -u.max_below), 1),
         'readings_out_of_band', u.readings, 'hours_out_of_band', round(date_diff('minute', u.started_at, u.last_at) / 60.0, 1),
         'cargo_value_usd', s.cargo_value_usd, 'carrier', k.name, 'carrier_id', s.carrier_id,
         'shipper', sh.name, 'account_owner', sh.account_owner
       ) AS evidence
FROM run u
JOIN containers c ON c.id = u.container_id
JOIN shipments s ON s.id = c.shipment_id
JOIN carriers k ON k.id = s.carrier_id
JOIN shippers sh ON sh.id = s.shipper_id
WHERE u.readings >= 4
ORDER BY started_at, u.container_id

Parameters

change one, see what changes in the window, then save it as a new version with a note
now 2.5 · used by cold_chain
now 4 · used by cold_chain
now 72 · used by cold_chain

History

append-only; nothing is edited in place
  1. definitionCold Chain Lead cold_chain v1 First version (three readings).
  2. definitionCold Chain Lead cold_chain v2 A run needs four readings out of band, not three: on the tuning seed a door opening next to a defrost cycle made three-reading runs that were not excursions.