Rails 24:00 Orvane Home & Outdoor

← Definitions

Late-shipment risk late_shipment_risk

A warehouse holding a pile of paid orders that are not picked and will miss the marketplace's ship-by time soon. One row per warehouse.

Parameters: risk_hours = 6 min_orders = 15

Test results

meaningtestpassedobservedexpected
fulfilmentcount_betweenpassed1between 0.0 and 3.0
fulfilmentuniquepassed{'duplicates': 0}warehouse_id unique
fulfilmentdetector_shapepassed{'missing_columns': [], 'bad_rows': 0, 'rows': 1}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

fulfilment · v1 · head of fulfilment

Orders paid and not cancelled as of now, not yet picked, whose ship-by time falls before now plus risk_hours (or passed in the last day), not already moved to another warehouse. Listed when a warehouse holds at least min_orders of them. The evidence names the other warehouse that stocks the most of these orders' items.

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

WITH moved AS (
  SELECT DISTINCT order_id FROM order_events WHERE type = 'rerouted' AND "at" <= {now}
),
at_risk AS (
  SELECT o.id, o.warehouse_id, o.marketplace_id, o.ship_by, o.total
  FROM orders o
  LEFT JOIN moved mv ON mv.order_id = o.id
  WHERE o.paid_at <= {now} AND (o.picked_at IS NULL OR o.picked_at > {now})
    AND (o.cancelled_at IS NULL OR o.cancelled_at > {now})
    AND o.ship_by <= {now} + INTERVAL ({risk_hours}) HOUR AND o.ship_by > {now} - INTERVAL 1 DAY
    AND mv.order_id IS NULL
),
per_house AS (
  SELECT warehouse_id, count(*) AS orders, count(*) FILTER (WHERE ship_by <= {now}) AS already_late,
         min(ship_by) AS first_ship_by, round(sum(total), 2) AS value,
         list(id ORDER BY ship_by)[1:100] AS order_ids,
         count(*) FILTER (WHERE marketplace_id = 2) AS marketvine, count(*) FILTER (WHERE marketplace_id = 3) AS tradewick
  FROM at_risk GROUP BY warehouse_id HAVING count(*) >= {min_orders}
),
cover AS (
  SELECT r.warehouse_id, s.warehouse_id AS alt, count(DISTINCT r.id) AS covered
  FROM at_risk r
  JOIN order_lines ol ON ol.order_id = r.id
  JOIN stock_levels s ON s.product_id = ol.product_id AND s.warehouse_id <> r.warehouse_id AND s.on_hand > 0
  GROUP BY r.warehouse_id, s.warehouse_id
),
best AS (
  SELECT warehouse_id, arg_max(alt, covered) AS to_warehouse_id, max(covered) AS covered,
         list(alt ORDER BY covered DESC) AS alternative_warehouse_ids
  FROM cover GROUP BY warehouse_id
)
SELECT 'warehouse' AS subject_type, w.id AS subject_id, w.id AS warehouse_id, 'warehouse:' || w.id AS scope,
       p.first_ship_by - INTERVAL ({risk_hours}) HOUR AS started_at,
       CASE WHEN p.already_late > 0 OR p.orders >= 60 THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'warehouse', w.name, 'orders_at_risk', p.orders, 'already_late', p.already_late,
         'first_ship_by', p.first_ship_by, 'value', p.value, 'order_ids', p.order_ids,
         'by_marketplace', json_object('Marketvine', p.marketvine, 'Tradewick', p.tradewick),
         'to_warehouse_id', b.to_warehouse_id, 'alternative_warehouse_ids', b.alternative_warehouse_ids,
         'orders_coverable_there', b.covered, 'risk_hours', {risk_hours}
       ) AS evidence
FROM per_house p
JOIN warehouses w ON w.id = p.warehouse_id
LEFT JOIN best b ON b.warehouse_id = p.warehouse_id
ORDER BY started_at, w.id

Tests: count_between unique