Definitions

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

Late order

late_order unit: orders canonical: ops

Orders that missed their promise. Ops measures the kitchen against the promised prep time; Customer Success measures the guest's wait against the ETA shown. The ops definition is set as canonical.

opsCOO · ops v1canonical

The kitchen marked the order ready more than late_minutes after the promised prep time, counted from acceptance. Orders still in the kitchen past that point count as late now.

latest change: First version.

pass count_betweenobserved 3803, expected between 10.0 and 15000.0
pass stricter_thresholdobserved 5233, expected between 10.0 and 20000.0
pass uniqueobserved {'duplicates': 0}, expected order_id unique

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
),
timed AS (
  SELECT o.*, b.merchant_id,
         o.accepted_at + INTERVAL (o.promised_prep_min) MINUTE AS promised_ready_at,
         o.accepted_at + INTERVAL (o.promised_prep_min + 10) MINUTE AS late_at,
         least(coalesce(o.ready_at, TIMESTAMP 'Fri 23:00:00'), TIMESTAMP 'Fri 23:00:00') AS ready_as_of
  FROM orders o
  JOIN branches b ON b.id = o.branch_id
  LEFT JOIN closed x ON x.order_id = o.id
  WHERE o.accepted_at >= TIMESTAMP 'Fri 00:00:00' AND o.accepted_at < TIMESTAMP 'Fri 23:00:00' AND o.accepted_at <= TIMESTAMP 'Fri 23:00:00'
    AND NOT (o.status IN ('cancelled', 'rejected') AND coalesce(x.closed_at, o.placed_at) <= TIMESTAMP 'Fri 23:00:00')
)
SELECT 'order' AS subject_type, t.id AS subject_id, t.id AS order_id, t.branch_id, t.merchant_id,
       t.channel_id, t.late_at AS started_at,
       json_object(
         'accepted_at', t.accepted_at,
         'promised_prep_min', t.promised_prep_min,
         'ready_at', CASE WHEN t.ready_at <= TIMESTAMP 'Fri 23:00:00' THEN t.ready_at END,
         'prep_minutes', round(date_diff('second', t.accepted_at, t.ready_as_of) / 60.0, 1),
         'minutes_past_promise', round(date_diff('second', t.promised_ready_at, t.ready_as_of) / 60.0, 1),
         'threshold_minutes', 10,
         'still_in_kitchen', t.ready_at IS NULL OR t.ready_at > TIMESTAMP 'Fri 23:00:00'
       ) AS evidence
FROM timed t
WHERE t.ready_as_of > t.late_at
ORDER BY started_at, t.id

csHead of CS · cs v1

The guest got the order after the ETA (placed time + promised prep + eta_buffer_minutes), or is still waiting past it. Delivery and own-app orders only; dine-in has no ETA.

latest change: First version.

pass count_betweenobserved 14486, expected between 10.0 and 20000.0
pass uniqueobserved {'duplicates': 0}, expected order_id unique

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
),
timed AS (
  SELECT o.*, b.merchant_id, c.name AS channel,
         o.placed_at + INTERVAL (o.promised_prep_min + 30) MINUTE AS eta,
         least(coalesce(o.delivered_at, TIMESTAMP 'Fri 23:00:00'), TIMESTAMP 'Fri 23:00:00') AS delivered_as_of
  FROM orders o
  JOIN branches b ON b.id = o.branch_id
  JOIN channels c ON c.id = o.channel_id
  LEFT JOIN closed x ON x.order_id = o.id
  WHERE o.placed_at >= TIMESTAMP 'Fri 00:00:00' AND o.placed_at < TIMESTAMP 'Fri 23:00:00' AND o.placed_at <= TIMESTAMP 'Fri 23:00:00'
    AND o.accepted_at IS NOT NULL AND c.kind <> 'dine_in'
    AND NOT (o.status IN ('cancelled', 'rejected') AND coalesce(x.closed_at, o.placed_at) <= TIMESTAMP 'Fri 23:00:00')
)
SELECT 'order' AS subject_type, t.id AS subject_id, t.id AS order_id, t.branch_id, t.merchant_id,
       t.channel_id, t.eta AS started_at,
       json_object(
         'placed_at', t.placed_at,
         'eta', t.eta,
         'delivered_at', CASE WHEN t.delivered_at <= TIMESTAMP 'Fri 23:00:00' THEN t.delivered_at END,
         'minutes_past_eta', round(date_diff('second', t.eta, t.delivered_as_of) / 60.0, 1),
         'channel', t.channel,
         'still_waiting', t.delivered_at IS NULL OR t.delivered_at > TIMESTAMP 'Fri 23:00:00'
       ) AS evidence
FROM timed t
WHERE t.delivered_as_of > t.eta
ORDER BY started_at, t.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

definitionHead of CS cs v1 First version.
definitionCOO ops v1 First version.