Definitions

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

Overloaded branch

overloaded_branch unit: stores agent detector canonical: ops

A branch whose kitchen has more work than it can handle. Ops looks at the queue (the leading signal the agent acts on by setting the store busy); Customer Success looks at food that is already slow.

opsCOO · ops v1canonical

Open kitchen orders (in the POS or dine-in, accepted, not ready, not cancelled) exceed the branch's capacity per 15 minutes times overload_factor. started_at is when the queue first passed capacity.

latest change: First version.

pass count_betweenobserved 2, expected between 0.0 and 60.0
pass friday_peakobserved 3, expected between 2.0 and 20.0
pass uniqueobserved {'duplicates': 0}, expected branch_id unique
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 2}, 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
),
open_orders AS (
  SELECT o.branch_id, o.id, o.accepted_at, o.channel_id
  FROM orders o
  JOIN channels c ON c.id = o.channel_id
  LEFT JOIN closed x ON x.order_id = o.id
  WHERE o.accepted_at <= TIMESTAMP 'Fri 23:00:00' AND o.accepted_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (90) MINUTE
    AND (o.ready_at IS NULL OR o.ready_at > TIMESTAMP 'Fri 23:00:00')
    AND (o.injected_at <= TIMESTAMP 'Fri 23:00:00' OR c.kind = 'dine_in')
    AND NOT (o.status IN ('cancelled', 'rejected') AND coalesce(x.closed_at, o.placed_at) <= TIMESTAMP 'Fri 23:00:00')
),
queue AS (
  SELECT branch_id, count(*) AS open_orders, list_sort(list(accepted_at)) AS accepted_times,
         list(DISTINCT channel_id) AS channel_ids
  FROM open_orders
  GROUP BY branch_id
),
recent_prep AS (
  SELECT branch_id, count(*) AS ready_orders,
         avg(date_diff('second', accepted_at, ready_at) / 60.0 / nullif(promised_prep_min, 0)) AS prep_ratio
  FROM orders
  WHERE ready_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (30) MINUTE AND ready_at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY branch_id
)
SELECT 'branch' AS subject_type, b.id AS subject_id, b.id AS branch_id, b.merchant_id,
       q.accepted_times[CAST(floor(b.capacity_per_15min * 1.0) AS INTEGER) + 1] AS started_at,
       CASE WHEN q.open_orders >= 2 * b.capacity_per_15min THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'branch', b.name,
         'area', b.area,
         'open_orders', q.open_orders,
         'capacity_per_15min', b.capacity_per_15min,
         'load_ratio', round(q.open_orders / b.capacity_per_15min, 2),
         'oldest_open_since', q.accepted_times[1],
         'recent_ready_orders', coalesce(r.ready_orders, 0),
         'recent_prep_ratio', round(r.prep_ratio, 2),
         'channel_ids', q.channel_ids
       ) AS evidence
FROM queue q
JOIN branches b ON b.id = q.branch_id
LEFT JOIN recent_prep r ON r.branch_id = b.id
WHERE b.capacity_per_15min > 0
  AND q.open_orders > b.capacity_per_15min * 1.0
ORDER BY started_at, b.id

csHead of CS · cs v1

Guests feel overload as slow food: at least min_slow_orders orders became ready in the last prep_window_minutes, and on average they took slow_factor times their promised prep or longer.

latest change: First version.

pass count_betweenobserved 17, expected between 0.0 and 80.0
pass uniqueobserved {'duplicates': 0}, expected branch_id unique
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 17}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

SQL, rendered for now

WITH recent AS (
  SELECT o.branch_id, o.id, o.accepted_at, o.ready_at, o.promised_prep_min,
         date_diff('second', o.accepted_at, o.ready_at) / 60.0 / nullif(o.promised_prep_min, 0) AS prep_ratio
  FROM orders o
  WHERE o.ready_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (30) MINUTE AND o.ready_at <= TIMESTAMP 'Fri 23:00:00'
    AND o.accepted_at IS NOT NULL
),
per_branch AS (
  SELECT branch_id, count(*) AS ready_orders, avg(prep_ratio) AS prep_ratio,
         count(*) FILTER (WHERE prep_ratio >= 1.5) AS slow_orders,
         min(accepted_at) FILTER (WHERE prep_ratio >= 1.5) AS first_slow_accepted_at
  FROM recent
  GROUP BY branch_id
)
SELECT 'branch' AS subject_type, b.id AS subject_id, b.id AS branch_id, b.merchant_id,
       p.first_slow_accepted_at AS started_at,
       CASE WHEN p.prep_ratio >= 2 THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'branch', b.name,
         'ready_orders', p.ready_orders,
         'slow_orders', p.slow_orders,
         'avg_prep_ratio', round(p.prep_ratio, 2),
         'slow_factor', 1.5,
         'window_minutes', 30
       ) AS evidence
FROM per_branch p
JOIN branches b ON b.id = p.branch_id
WHERE p.ready_orders >= 3 AND p.prep_ratio >= 1.5
ORDER BY started_at, b.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.