Definitions

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

Acceptance risk

acceptance_risk unit: stores agent detector canonical: ops

A store accepting fewer than 98% of the orders it receives over the last 7 days, or sliding towards that line fast enough to cross it within two days.

opsCOO · ops v2canonical

Per store over the last window_days calendar days to now: every order placed counts as received (the aggregators' denominator) and only the store's own rejections count against it (POS errors - 'payload mapping error', 'configuration error' - and rejections where the POS named a drifted item belong to failing_integration and menu_drift); ratio = 1 - own rejections / received. Flagged when the store received at least min_orders, rejected at least min_rejections and had orders on at least min_days days (a weekly ratio needs a week of orders); it turned away at least min_recent_rate of its orders over the last slide_hours (two and a half times the 2% allowance: staff refusing orders, not a bad hour); no other operational incident explains its rejections this window (POS token expiry, menu drift, printer failures, a kitchen overload burst); and either the ratio is below breach_ratio while the store is getting worse (its own rejection rate over the last slide_hours beats the first slide_hours of the window), or its ratio-to-date fell on each of the last 3 complete days and that slope crosses breach_ratio within projection_days days. started_at is the first day in the window the store rejected more than 2% of its orders.

latest change: Below 98% while getting worse, or projected to cross within two days; a full week of orders, at least 5% of orders turned away in the last 48 hours, every order placed as the denominator, and stores whose rejections another incident explains left to that incident.

pass count_betweenobserved 12, expected between 8.0 and 30.0
pass uniqueobserved {'duplicates': 0}, expected branch_id unique
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 12}, 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,
         bool_or((e.payload->>'item_id') IS NOT NULL) AS pos_named_item
  FROM order_events e
  WHERE e.type ILIKE '%reject%'
  GROUP BY e.order_id
),
decided AS (
  SELECT o.branch_id, o.placed_at, CAST(o.placed_at AS DATE) AS day, o.rejection_reason,
         o.status = 'rejected' AND coalesce(x.closed_at, o.placed_at) <= TIMESTAMP 'Fri 23:00:00'
           AND coalesce(o.rejection_reason, '') NOT IN ('payload mapping error', 'configuration error')
           AND NOT coalesce(x.pos_named_item, FALSE) AS rejected
  FROM orders o
  LEFT JOIN closed x ON x.order_id = o.id
  WHERE o.placed_at >= CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL (7 - 1) DAY AND o.placed_at <= TIMESTAMP 'Fri 23:00:00'
),
bounds AS (
  SELECT branch_id, min(placed_at) AS first_order_at
  FROM decided
  GROUP BY branch_id
),
per_store AS (
  SELECT d.branch_id, count(*) AS received, count(*) FILTER (WHERE d.rejected) AS rejected,
         count(DISTINCT d.day) AS days_with_orders,
         count(*) FILTER (WHERE d.placed_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (48) HOUR) AS recent_received,
         count(*) FILTER (WHERE d.rejected AND d.placed_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (48) HOUR) AS recent_rejected,
         count(*) FILTER (WHERE d.placed_at < b.first_order_at + INTERVAL (48) HOUR) AS early_received,
         count(*) FILTER (WHERE d.rejected AND d.placed_at < b.first_order_at + INTERVAL (48) HOUR) AS early_rejected
  FROM decided d
  JOIN bounds b ON b.branch_id = d.branch_id
  GROUP BY d.branch_id
),
daily AS (
  SELECT branch_id, day, count(*) AS received, count(*) FILTER (WHERE rejected) AS rejected
  FROM decided
  GROUP BY branch_id, day
),
rolling AS (
  SELECT branch_id, day,
         1 - sum(rejected) OVER w / sum(received) OVER w AS ratio_to_date,
         row_number() OVER (PARTITION BY branch_id ORDER BY day DESC) AS age
  FROM daily
  WINDOW w AS (PARTITION BY branch_id ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
),
slide AS (
  SELECT branch_id,
         max(ratio_to_date) FILTER (WHERE age = 2) AS r1, max(ratio_to_date) FILTER (WHERE age = 3) AS r2,
         max(ratio_to_date) FILTER (WHERE age = 4) AS r3, max(ratio_to_date) FILTER (WHERE age = 5) AS r4
  FROM rolling
  GROUP BY branch_id
),
scored AS (
  SELECT p.*, 1 - p.rejected / p.received AS ratio,
         p.recent_rejected / nullif(p.recent_received, 0) AS recent_rate,
         p.early_rejected / nullif(p.early_received, 0) AS early_rate,
         coalesce(sl.r1 < sl.r2 AND sl.r2 < sl.r3 AND sl.r3 < sl.r4, FALSE) AS monotone_slide,
         sl.r1 + 2 * (sl.r1 - sl.r4) / 3 AS projected
  FROM per_store p
  LEFT JOIN slide sl ON sl.branch_id = p.branch_id
),
history AS (
  SELECT branch_id,
         min(day) FILTER (WHERE rejected > (1 - 0.98) * received) AS first_bad_day,
         list(json_object('day', strftime(day, '%Y-%m-%d'), 'received', received, 'rejected', rejected,
                          'ratio', round(1 - rejected / received, 4)) ORDER BY day) AS daily_ratios
  FROM daily
  GROUP BY branch_id
),
explained AS (
  SELECT b.id AS branch_id, 'pos token expiry' AS cause
  FROM integration_events e JOIN branches b ON b.pos_integration_id = e.integration_id
  WHERE e.type ILIKE '%auth_expired%' AND e.at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY AND e.at <= TIMESTAMP 'Fri 23:00:00'
  UNION
  SELECT b.id, 'menu drift'
  FROM integration_events e JOIN branches b ON b.pos_integration_id = e.integration_id
  WHERE (e.type ILIKE '%item_unavailable%' OR e.type ILIKE '%price_changed%' OR e.type ILIKE '%sync_fail%')
    AND e.at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY AND e.at <= TIMESTAMP 'Fri 23:00:00'
  UNION
  SELECT o.branch_id, 'printer offline'
  FROM order_events e JOIN orders o ON o.id = e.order_id
  WHERE e.type ILIKE '%print_fail%' AND e.at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY AND e.at <= TIMESTAMP 'Fri 23:00:00'
  UNION
  SELECT branch_id, 'kitchen overload'
  FROM (SELECT o.branch_id, date_trunc('hour', o.ready_at) AS hour, count(*) AS slow_orders
        FROM orders o
        WHERE o.ready_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY AND o.ready_at <= TIMESTAMP 'Fri 23:00:00'
          AND date_diff('second', o.accepted_at, o.ready_at) >= 2 * 60 * o.promised_prep_min
        GROUP BY ALL)
  WHERE slow_orders >= 5
),
reasons AS (
  SELECT branch_id, list(json_object('reason', rejection_reason, 'orders', n) ORDER BY n DESC) AS by_reason
  FROM (SELECT branch_id, coalesce(rejection_reason, 'unknown') AS rejection_reason, count(*) AS n
        FROM decided WHERE rejected GROUP BY ALL)
  GROUP BY branch_id
)
SELECT 'branch' AS subject_type, b.id AS subject_id, b.id AS branch_id, b.merchant_id, st.store_id,
       CAST(coalesce(h.first_bad_day, CAST(TIMESTAMP 'Fri 23:00:00' AS DATE)) AS TIMESTAMP) AS started_at,
       CASE WHEN s.ratio < 0.98 THEN 'high' ELSE 'medium' END AS severity,
       round(s.ratio, 4) AS acceptance_ratio,
       json_object(
         'branch', b.name,
         'received', s.received,
         'rejected', s.rejected,
         'acceptance_ratio', round(s.ratio, 4),
         'breach_ratio', 0.98,
         'below_line', s.ratio < 0.98,
         'recent_rejection_rate', round(s.recent_rate, 4),
         'early_rejection_rate', round(s.early_rate, 4),
         'slide_hours', 48,
         'monotone_three_day_slide', s.monotone_slide,
         'projected_ratio', round(s.projected, 4),
         'projected_in_days', 2,
         'daily_ratios', h.daily_ratios,
         'rejections_by_reason', coalesce(rs.by_reason, [])
       ) AS evidence
FROM scored s
JOIN branches b ON b.id = s.branch_id
LEFT JOIN stores st ON st.branch_id = b.id
LEFT JOIN history h ON h.branch_id = b.id
LEFT JOIN reasons rs ON rs.branch_id = b.id
LEFT JOIN (SELECT DISTINCT branch_id FROM explained) ex ON ex.branch_id = b.id
WHERE ex.branch_id IS NULL
  AND coalesce(s.recent_rate, 0) >= 0.05
  AND s.received >= 50 AND s.rejected >= 4 AND s.days_with_orders >= 7
  AND ((s.ratio < 0.98 AND coalesce(s.recent_rate > s.early_rate, FALSE))
       OR (s.monotone_slide AND s.projected < 0.98))
ORDER BY started_at, b.id

csHead of CS · cs v1

As the aggregators score it: every rejection counts, whoever or whatever rejected the order (store staff, POS errors, menu drift). Over the last window_days days: ratio below breach_ratio, the last trend_days days no better than before, at least min_orders received and min_rejections rejected.

latest change: First version.

pass count_betweenobserved 39, expected between 8.0 and 120.0
pass uniqueobserved {'duplicates': 0}, expected branch_id unique
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 39}, 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 '%reject%'
  GROUP BY e.order_id
),
decided AS (
  SELECT o.branch_id, CAST(o.placed_at AS DATE) AS day, o.rejection_reason,
         o.status = 'rejected' AND coalesce(x.closed_at, o.placed_at) <= TIMESTAMP 'Fri 23:00:00' AS rejected,
         o.accepted_at <= TIMESTAMP 'Fri 23:00:00' OR (o.status = 'rejected' AND coalesce(x.closed_at, o.placed_at) <= TIMESTAMP 'Fri 23:00:00') AS received
  FROM orders o
  LEFT JOIN closed x ON x.order_id = o.id
  WHERE o.placed_at >= CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL (7 - 1) DAY AND o.placed_at <= TIMESTAMP 'Fri 23:00:00'
),
daily AS (
  SELECT branch_id, day, count(*) FILTER (WHERE received) AS received,
         count(*) FILTER (WHERE rejected) AS rejected
  FROM decided
  GROUP BY branch_id, day
),
running AS (
  SELECT *, 1 - sum(rejected) OVER w / nullif(sum(received) OVER w, 0) AS running_ratio
  FROM daily
  WINDOW w AS (PARTITION BY branch_id ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
),
reasons AS (
  SELECT branch_id, list(json_object('reason', rejection_reason, 'orders', n) ORDER BY n DESC) AS by_reason
  FROM (SELECT branch_id, coalesce(rejection_reason, 'unknown') AS rejection_reason, count(*) AS n
        FROM decided WHERE rejected GROUP BY ALL)
  GROUP BY branch_id
),
per_store AS (
  SELECT branch_id, sum(received) AS received, sum(rejected) AS rejected,
         1 - sum(rejected) / nullif(sum(received), 0) AS ratio,
         1 - sum(rejected) FILTER (WHERE day > CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL (3) DAY)
           / nullif(sum(received) FILTER (WHERE day > CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL (3) DAY), 0) AS recent_ratio,
         1 - sum(rejected) FILTER (WHERE day <= CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL (3) DAY)
           / nullif(sum(received) FILTER (WHERE day <= CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL (3) DAY), 0) AS earlier_ratio,
         min(day) FILTER (WHERE running_ratio < 0.98) AS first_breach_day,
         list(json_object('day', strftime(day, '%Y-%m-%d'), 'received', received, 'rejected', rejected,
                          'ratio', round(1 - rejected / nullif(received, 0), 4)) ORDER BY day) AS daily_ratios
  FROM running
  GROUP BY branch_id
)
SELECT 'branch' AS subject_type, b.id AS subject_id, b.id AS branch_id, b.merchant_id, s.store_id,
       CAST(coalesce(p.first_breach_day, CAST(TIMESTAMP 'Fri 23:00:00' AS DATE)) AS TIMESTAMP) AS started_at,
       CASE WHEN p.recent_ratio < 0.98 - 0.03 THEN 'high' ELSE 'medium' END AS severity,
       round(p.ratio, 4) AS acceptance_ratio,
       json_object(
         'branch', b.name,
         'received', p.received,
         'rejected', p.rejected,
         'acceptance_ratio', round(p.ratio, 4),
         'breach_ratio', 0.98,
         'recent_ratio', round(p.recent_ratio, 4),
         'earlier_ratio', round(p.earlier_ratio, 4),
         'trend_days', 3,
         'daily_ratios', p.daily_ratios,
         'rejections_by_reason', coalesce(r.by_reason, [])
       ) AS evidence
FROM per_store p
JOIN branches b ON b.id = p.branch_id
LEFT JOIN stores s ON s.branch_id = b.id
LEFT JOIN reasons r ON r.branch_id = b.id
WHERE p.received >= 50 AND p.rejected >= 4 AND p.ratio < 0.98
  AND coalesce(p.recent_ratio, 1) <= coalesce(p.earlier_ratio, 1)
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

definitionCOO ops v2 Below 98% while getting worse, or projected to cross within two days; a full week of orders, at least 5% of orders turned away in the last 48 hours, every order placed as the denominator, and stores whose rejections another incident explains left to that incident.
definitionHead of CS cs v1 First version.
definitionCOO ops v1 First version: the store's own rejections, 7-day ratio below 98% with the last 3 days no better than before, at least 4 rejections.