Definitions
The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.ETA past the promise, customer not told
eta_slipped unit: shipments operator detector canonical: csThe carrier's current ETA, plus the usual discharge-to-door time, now lands after the delivery date promised to the customer, and nobody has told the customer since the ETA moved. Rolled bookings are left to the roll check.
Sea shipments that have departed and not yet arrived as of now, and were not rolled. The current ETA is the latest eta_updates row for the shipment's voyage and destination at or before now. The slip started at the first update whose ETA plus projection_hours passed promised_delivery_at plus min_slip_hours; it counts once grace_hours have passed with no eta_update, roll_notice or delay notice logged at or before now for a moment at or after that update. A phone call only counts once it is logged.
latest change: First version.
- pass count_betweenobserved 0, expected between 0.0 and 300.0
- pass uniqueobserved {'duplicates': 0}, expected shipment_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH live AS (
SELECT s.id, s.ref, s.shipper_id, s.voyage_id, s.dest_id, s.promised_delivery_at, s.carrier_id
FROM shipments s
WHERE s.mode = 'sea' AND s.booked_at <= TIMESTAMP 'Tue 18:00:00' AND s.etd >= TIMESTAMP 'Tue 18:00:00' - INTERVAL 60 DAY
AND EXISTS (SELECT 1 FROM shipment_events d WHERE d.shipment_id = s.id AND d.type = 'departed'
AND d."at" <= TIMESTAMP 'Tue 18:00:00')
AND NOT EXISTS (SELECT 1 FROM shipment_events a WHERE a.shipment_id = s.id
AND a.type IN ('arrived', 'rolled', 'cancelled') AND a."at" <= TIMESTAMP 'Tue 18:00:00')
),
updates AS (
SELECT l.id AS shipment_id, u."at", u.eta, u.reason
FROM live l JOIN eta_updates u ON u.voyage_id = l.voyage_id AND u.location_id = l.dest_id
WHERE u."at" <= TIMESTAMP 'Tue 18:00:00'
),
current AS (
SELECT shipment_id, arg_max(eta, "at") AS eta, max("at") AS eta_at, arg_max(reason, "at") AS reason
FROM updates GROUP BY shipment_id
),
crossed AS (
SELECT u.shipment_id, min(u."at") AS crossed_at
FROM updates u JOIN live l ON l.id = u.shipment_id
WHERE u.eta + INTERVAL (30) HOUR > l.promised_delivery_at + INTERVAL (0) HOUR
GROUP BY u.shipment_id
)
SELECT 'shipment' AS subject_type, l.id AS subject_id, l.id AS shipment_id, l.shipper_id,
x.crossed_at AS started_at,
CASE WHEN c.eta + INTERVAL (30) HOUR > l.promised_delivery_at + INTERVAL 3 DAY
THEN 'high' ELSE 'medium' END AS severity,
json_object(
'shipment', l.ref, 'voyage', v.code, 'voyage_id', l.voyage_id, 'carrier', k.name,
'eta', c.eta, 'eta_published_at', c.eta_at, 'eta_reason', c.reason,
'promised_delivery_at', l.promised_delivery_at,
'projected_delivery', c.eta + INTERVAL (30) HOUR,
'hours_past_promise', round(date_diff('minute', l.promised_delivery_at,
c.eta + INTERVAL (30) HOUR) / 60.0, 1),
'shipper', sh.name, 'account_owner', sh.account_owner
) AS evidence
FROM live l
JOIN current c ON c.shipment_id = l.id
JOIN crossed x ON x.shipment_id = l.id
JOIN voyages v ON v.id = l.voyage_id
JOIN carriers k ON k.id = l.carrier_id
JOIN shippers sh ON sh.id = l.shipper_id
WHERE c.eta + INTERVAL (30) HOUR > l.promised_delivery_at + INTERVAL (0) HOUR
AND x.crossed_at <= TIMESTAMP 'Tue 18:00:00' - INTERVAL (4) HOUR
AND NOT EXISTS (SELECT 1 FROM notifications n WHERE n.shipment_id = l.id
AND n.kind IN ('eta_update', 'roll_notice', 'delay_notice')
AND n."at" >= x.crossed_at AND n.logged_at <= TIMESTAMP 'Tue 18:00:00')
ORDER BY started_at, l.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- definitionCustomer Service Lead cs v1 First version.