Definitions

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

Courier no-show

courier_no_show unit: orders agent detector

Food is ready and nobody has collected it. Delivery orders only (not dine-in, not customer pick-up), still waiting at the time asked. Each row says who the logistics ticket goes to.

opsCOO · ops v3

Ready at least no_show_minutes ago, not picked up as of now, not cancelled as of now. A timestamp later than now has not happened; a cancellation with no recorded time (lifecycle events are kept for 48 hours) counts as still waiting, and the period bounds how long it stays listed. logistics_partner is the aggregator for aggregator deliveries and the 3PL (or own fleet) for restaurant deliveries.

latest change: Rows carry the channel, order type and logistics partner for the ticket; customer pick-up orders excluded.

pass count_betweenobserved 7, expected between 0.0 and 60.0
pass friday_eveningobserved 3, expected between 1.0 and 30.0
pass uniqueobserved {'duplicates': 0}, expected order_id unique
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 7}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

SQL, rendered for now

WITH closed AS (
  SELECT e.order_id, min(e.at) AS closed_at
  FROM order_events e
  WHERE e.type ILIKE '%cancel%' OR e.type ILIKE '%reject%'
  GROUP BY e.order_id
),
dispatch AS (
  SELECT i.branch_id, min(i.id) AS integration_id, any_value(v.name) AS vendor
  FROM integrations i
  JOIN integration_vendors v ON v.id = i.vendor_id
  WHERE v.kind = 'dispatch'
  GROUP BY i.branch_id
),
assigned AS (
  SELECT e.order_id, arg_max(e.payload->>'provider', e.at) AS provider, max(e.at) AS assigned_at
  FROM order_events e
  WHERE e.type ILIKE '%courier_assigned%' AND e.at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY e.order_id
)
SELECT 'order' AS subject_type, o.id AS subject_id, o.id AS order_id, o.branch_id, b.merchant_id,
       d.integration_id, o.channel_id, c.name AS channel, o.order_type,
       CASE WHEN o.order_type = 'DELIVERY_BY_RESTAURANT' THEN coalesce(a.provider, d.vendor, 'own fleet')
            ELSE c.name END AS logistics_partner,
       o.ready_at + INTERVAL (25) MINUTE AS started_at,
       CASE WHEN o.ready_at <= TIMESTAMP 'Fri 23:00:00' - INTERVAL (2 * 25) MINUTE THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'ready_at', o.ready_at,
         'minutes_waiting', round(date_diff('second', o.ready_at, TIMESTAMP 'Fri 23:00:00') / 60.0, 1),
         'threshold_minutes', 25,
         'channel', c.name,
         'order_type', o.order_type,
         'logistics_partner', CASE WHEN o.order_type = 'DELIVERY_BY_RESTAURANT'
                                   THEN coalesce(a.provider, d.vendor, 'own fleet') ELSE c.name END,
         'ticket_to', CASE WHEN o.order_type = 'DELIVERY_BY_RESTAURANT' THEN '3PL support' ELSE 'aggregator support' END,
         'courier_assigned_at', a.assigned_at,
         'dispatch_vendor', d.vendor,
         'order_total', o.total,
         'branch', b.name,
         'area', b.area
       ) AS evidence
FROM orders o
JOIN branches b ON b.id = o.branch_id
JOIN channels c ON c.id = o.channel_id
LEFT JOIN dispatch d ON d.branch_id = o.branch_id
LEFT JOIN assigned a ON a.order_id = o.id
LEFT JOIN closed x ON x.order_id = o.id
WHERE o.ready_at >= TIMESTAMP 'Fri 20:00:00' AND o.ready_at < TIMESTAMP 'Fri 23:00:00'
  AND o.ready_at <= TIMESTAMP 'Fri 23:00:00' - INTERVAL (25) MINUTE
  AND (o.picked_up_at IS NULL OR o.picked_up_at > TIMESTAMP 'Fri 23:00:00')
  AND c.kind <> 'dine_in' AND coalesce(o.order_type, '') <> 'PICK_UP'
  AND NOT coalesce(x.closed_at <= TIMESTAMP 'Fri 23:00:00', FALSE)
ORDER BY started_at, o.id

Parameters

change one, see what changes tonight, then save it as a new version with a note

History

append-only; nothing is edited in place

definitionCOO ops v2 A cancellation with no recorded time no longer counts as cancelled at placement; replays more than 48 hours back were dropping no-shows the aggregator cancelled later.
definitionCOO ops v3 Rows carry the channel, order type and logistics partner for the ticket; customer pick-up orders excluded.
definitionCOO ops v1 First version: ready at least 25 minutes ago, not picked up, not cancelled.