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_chainA 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 noteHistory
append-only; nothing is edited in place- definitionCold Chain Lead cold_chain v1 First version (three readings).
- 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.