Definitions

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

Stuck order

stuck_order unit: orders agent detector

An order the platform accepted that has not reached the branch's POS, so the kitchen does not know about it. Only orders still open at the time asked are listed.

integrationsHead of Integrations · integrations v2

Accepted at least stuck_seconds ago, still not injected into the POS as of now, and not cancelled or rejected as of now. Dine-in orders are rung up on the POS itself and never count. A cancellation with no recorded time is assumed to have happened at placement, so replays more than 48 hours back can miss stuck orders that were later rejected; failing_integration still sees them. auth_expired in the evidence is the token state as of now: the last of auth_expired, token_refreshed or token_refresh_failed before now, and the stored auth_expires_at only when there is no such event; refresh_failed says the last refresh attempt failed.

latest change: auth_expired is read from the auth event stream as of now instead of the stored expiry, which holds the end state; refresh_failed added.

pass count_betweenobserved 11, expected between 1.0 and 400.0
fail friday_late_eveningobserved 0, expected between 1.0 and 200.0
pass uniqueobserved {'duplicates': 0}, expected order_id unique
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 11}, 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
),
auth_state AS (
  SELECT e.integration_id, arg_max(e.type, e.at) AS last_auth_event, max(e.at) AS last_auth_event_at
  FROM integration_events e
  WHERE (e.type ILIKE '%auth_expired%' OR e.type ILIKE '%token_refresh%' OR e.type ILIKE '%auth_refresh%')
    AND e.at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY e.integration_id
),
failures AS (
  SELECT e.order_id, count(*) AS attempts, max(e.at) AS last_failed_at,
         arg_max(e.payload, e.at) AS last_error
  FROM order_events e
  WHERE e.type ILIKE '%injection_fail%' 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,
       i.id AS integration_id, o.channel_id,
       o.accepted_at + INTERVAL (90) SECOND AS started_at,
       CASE WHEN (CASE WHEN a.last_auth_event IS NULL THEN coalesce(i.auth_expires_at <= TIMESTAMP 'Fri 23:00:00', FALSE)
                     ELSE a.last_auth_event ILIKE '%expired%' OR a.last_auth_event ILIKE '%refresh_failed%' END)
              OR date_diff('second', o.accepted_at, TIMESTAMP 'Fri 23:00:00') >= 600 THEN 'high'
            ELSE 'medium' END AS severity,
       json_object(
         'accepted_at', o.accepted_at,
         'seconds_waiting', date_diff('second', o.accepted_at, TIMESTAMP 'Fri 23:00:00'),
         'threshold_seconds', 90,
         'channel', c.name,
         'order_total', o.total,
         'pos_integration_id', i.id,
         'pos_vendor', v.name,
         'pos_version', i.version,
         'integration_status', i.status,
         'auth_expires_at', i.auth_expires_at,
         'auth_expired', CASE WHEN a.last_auth_event IS NULL THEN coalesce(i.auth_expires_at <= TIMESTAMP 'Fri 23:00:00', FALSE)
                           ELSE a.last_auth_event ILIKE '%expired%' OR a.last_auth_event ILIKE '%refresh_failed%' END,
         'refresh_failed', coalesce(a.last_auth_event ILIKE '%refresh_failed%', FALSE),
         'last_auth_event', a.last_auth_event,
         'last_auth_event_at', a.last_auth_event_at,
         'failed_attempts', coalesce(f.attempts, 0),
         'last_failed_at', f.last_failed_at,
         'last_error', f.last_error
       ) 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 integrations i ON i.id = b.pos_integration_id
LEFT JOIN integration_vendors v ON v.id = i.vendor_id
LEFT JOIN auth_state a ON a.integration_id = i.id
LEFT JOIN closed x ON x.order_id = o.id
LEFT JOIN failures f ON f.order_id = o.id
WHERE o.accepted_at >= TIMESTAMP 'Fri 21:00:00' AND o.accepted_at < TIMESTAMP 'Fri 23:00:00'
  AND o.accepted_at <= TIMESTAMP 'Fri 23:00:00' - INTERVAL (90) SECOND
  AND (o.injected_at IS NULL OR o.injected_at > TIMESTAMP 'Fri 23:00:00')
  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')
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

definitionHead of Integrations integrations v2 auth_expired is read from the auth event stream as of now instead of the stored expiry, which holds the end state; refresh_failed added.
definitionHead of Integrations integrations v1 First version.