Rails 24:00 Orvane Home & Outdoor

← Definitions

Carrier delay cluster on one lane carrier_delay_cluster

Many shipments on one lane (warehouse to region, one carrier) getting delay scans at once: a depot or a route is backed up, not one parcel.

Parameters: lookback_hours = 6 min_delayed = 8 min_share = 0.08

Test results

meaningtestpassedobservedexpected
logisticscount_betweenpassed0between 0.0 and 18.0
logisticsuniquepassed{'duplicates': 0}lane_id unique
logisticsdetector_shapepassed{'missing_columns': [], 'bad_rows': 0, 'rows': 0}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

logistics · v1 · logistics lead

In the last lookback_hours, at least min_delayed shipments on the lane got a delay scan, and they are at least min_share of the lane's parcels in flight (collected in the last 24 hours, not delivered as of now). A lane switched to its backup carrier in the last 24 hours is not listed. The evidence shows the delay share on the other lanes into the same region: weather delays every lane into a region a little, a depot problem delays one lane a lot.

Changed 2026-10-12: Agreed with the owner.

WITH delays AS (
  SELECT lane_id, count(DISTINCT order_id) AS delayed, min("at") AS first_delay,
         list(DISTINCT order_id) AS order_ids, mode(detail) AS detail
  FROM carrier_events
  WHERE type = 'delay' AND "at" <= {now} AND "at" > {now} - INTERVAL ({lookback_hours}) HOUR
  GROUP BY lane_id
),
flight AS (
  SELECT lane_id, count(*) AS in_flight
  FROM orders
  WHERE shipped_at <= {now} AND shipped_at > {now} - INTERVAL 24 HOUR
    AND (delivered_at IS NULL OR delivered_at > {now})
  GROUP BY lane_id
),
share AS (
  SELECT d.lane_id, d.delayed, d.first_delay, d.order_ids, d.detail, f.in_flight,
         d.delayed / greatest(f.in_flight, d.delayed) AS share
  FROM delays d JOIN flight f ON f.lane_id = d.lane_id
),
switched AS (
  SELECT lane_id FROM lane_events WHERE "at" <= {now} AND "at" > {now} - INTERVAL 24 HOUR
  GROUP BY lane_id HAVING arg_max(type, "at") = 'carrier_switched'
),
region AS (
  SELECT ln.region_id, s.lane_id, s.share FROM share s JOIN lanes ln ON ln.id = s.lane_id
)
SELECT 'lane' AS subject_type, ln.id AS subject_id, ln.id AS lane_id, ln.warehouse_id, ln.region_id,
       'lane:' || ln.id AS scope, s.first_delay AS started_at,
       CASE WHEN s.share >= 0.4 THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'lane', w.name || ' → ' || rg.name, 'carrier', c.name, 'carrier_id', ln.carrier_id,
         'to_carrier_id', ln.backup_carrier_id, 'backup_carrier', bc.name,
         'delayed_shipments', s.delayed, 'in_flight', s.in_flight, 'delay_share', round(s.share, 3),
         'scan_detail', s.detail, 'order_ids', s.order_ids[1:100],
         'other_lanes_into_region_share', (SELECT round(avg(r.share), 3) FROM region r
                                            WHERE r.region_id = ln.region_id AND r.lane_id <> ln.id),
         'lookback_hours', {lookback_hours}
       ) AS evidence
FROM share s
JOIN lanes ln ON ln.id = s.lane_id
JOIN warehouses w ON w.id = ln.warehouse_id
JOIN regions rg ON rg.id = ln.region_id
JOIN carriers c ON c.id = ln.carrier_id
JOIN carriers bc ON bc.id = ln.backup_carrier_id
LEFT JOIN switched sw ON sw.lane_id = ln.id
WHERE s.delayed >= {min_delayed} AND s.share >= {min_share} AND sw.lane_id IS NULL
ORDER BY started_at, ln.id

Tests: count_between unique