Synthetic data, shaped after a Gulf delivery platform Canon Ops
World clock Fri 23:00:00 GST paused · 10×

Definitions

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

Failing integration

failing_integration unit: integrations agent detector canonical: ops

A POS integration that is losing orders. Ops looks at each branch's own link over the last hour; Integrations looks at whole releases, because a bad vendor version shows up as many small local problems. The ops definition is canonical for counting.

opsCOO · ops v4canonical

A branch's POS integration failed at least min_failures orders in the last window_minutes, and those are more than error_rate of its orders in that window. An order failed if an injection attempt errored, or it is still not in the POS stuck_seconds after acceptance and nobody rejected or cancelled it. When every failure is a mapping error on one item, item_id and channel_id name it and fix says to switch that item off on that channel rather than pause the channel.

latest change: auth_expired is read from the auth event stream as of now (stored expiry only when there is no event), refresh_failed added, and fix says to escalate to the POS partner when the last refresh failed.

  • pass count_betweenobserved 6, expected between 1.0 and 150.0
  • pass friday_late_eveningobserved 4, expected between 1.0 and 50.0
  • pass uniqueobserved {'duplicates': 0}, expected integration_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 6}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH fails AS (
  SELECT e.order_id, max(e.at) AS last_failed_at, arg_max(e.payload, e.at) AS last_error,
         any_value(TRY_CAST(e.payload->>'item_id' AS INTEGER)) AS item_id,
         any_value(coalesce(e.payload->>'modifier', (e.payload->>'modifier_id'))) AS modifier,
         bool_or((e.payload->>'error') ILIKE '%mapping%' OR (e.payload->>'error') ILIKE '%modifier%'
                 OR (e.payload->>'error') ILIKE '%E_COMBO%') AS mapping_error
  FROM order_events e
  WHERE e.type ILIKE '%injection_fail%'
    AND e.at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (60) MINUTE AND e.at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY e.order_id
),
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%') AND e.at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY e.order_id
),
recent AS (
  SELECT b.pos_integration_id AS integration_id, o.id AS order_id, o.accepted_at, o.channel_id,
         f.item_id, f.modifier,
         coalesce(f.mapping_error, FALSE) OR o.rejection_reason = 'payload mapping error' AS mapping_error,
         f.order_id IS NOT NULL
           OR ((o.injected_at IS NULL OR o.injected_at > TIMESTAMP 'Fri 23:00:00') AND x.order_id IS NULL
               AND o.accepted_at <= TIMESTAMP 'Fri 23:00:00' - INTERVAL (90) SECOND) AS failed,
         f.last_failed_at, f.last_error
  FROM orders o
  JOIN branches b ON b.id = o.branch_id
  JOIN channels c ON c.id = o.channel_id
  LEFT JOIN fails f ON f.order_id = o.id
  LEFT JOIN closed x ON x.order_id = o.id
  WHERE o.accepted_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (60) MINUTE AND o.accepted_at <= TIMESTAMP 'Fri 23:00:00'
    AND c.kind <> 'dine_in' AND b.pos_integration_id IS NOT NULL
),
per_integration AS (
  SELECT integration_id, count(*) AS orders,
         count(*) FILTER (WHERE failed) AS failed_orders,
         min(accepted_at) FILTER (WHERE failed) AS first_failure_at,
         arg_max(last_error, last_failed_at) AS last_error,
         list(order_id ORDER BY accepted_at) FILTER (WHERE failed) AS failed_order_ids,
         count(*) FILTER (WHERE failed AND mapping_error) AS mapping_failures,
         count(DISTINCT item_id) FILTER (WHERE failed AND item_id IS NOT NULL) AS failing_items,
         min(item_id) FILTER (WHERE failed AND item_id IS NOT NULL) AS item_id,
         any_value(modifier) FILTER (WHERE failed AND modifier IS NOT NULL) AS modifier,
         mode(channel_id) FILTER (WHERE failed) AS failing_channel_id
  FROM recent
  GROUP BY integration_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
),
events AS (
  SELECT e.integration_id, count(*) AS error_events, arg_max(e.type, e.at) AS last_event, max(e.at) AS last_event_at
  FROM integration_events e
  WHERE e.at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (60) MINUTE AND e.at <= TIMESTAMP 'Fri 23:00:00'
    AND (e.type ILIKE '%fail%' OR e.type ILIKE '%error%' OR e.type ILIKE '%expired%')
  GROUP BY e.integration_id
)
SELECT 'integration' AS subject_type, i.id AS subject_id, i.id AS integration_id, i.branch_id,
       b.merchant_id, i.vendor_id, i.version, p.first_failure_at AS started_at,
       CASE WHEN p.failing_items = 1 AND p.mapping_failures = p.failed_orders THEN p.item_id END AS item_id,
       CASE WHEN p.failing_items = 1 AND p.mapping_failures = p.failed_orders THEN p.failing_channel_id END AS channel_id,
       CASE WHEN p.failing_items = 1 AND p.mapping_failures = p.failed_orders THEN 'switch item off on channel'
            WHEN coalesce(a.last_auth_event ILIKE '%refresh_failed%', FALSE) THEN 'escalate to the POS partner'
            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 THEN 'refresh the POS token'
            ELSE 'investigate the POS link' END AS fix,
       CASE WHEN p.failed_orders = p.orders OR (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) THEN 'high'
            ELSE 'medium' END AS severity,
       json_object(
         'vendor', v.name,
         'version', i.version,
         'integration_status', i.status,
         'window_minutes', 60,
         'orders', p.orders,
         'failed_orders', p.failed_orders,
         'error_rate', round(p.failed_orders / p.orders, 3),
         'threshold', 0.2,
         '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,
         'last_sync_at', i.last_sync_at,
         'last_error', p.last_error,
         'error_events', coalesce(ev.error_events, 0),
         'last_event', ev.last_event,
         'last_event_at', ev.last_event_at,
         'failed_order_ids', p.failed_order_ids[1:20],
         'mapping_failures', p.mapping_failures,
         'item_id', CASE WHEN p.failing_items = 1 THEN p.item_id END,
         'modifier', CASE WHEN p.failing_items = 1 THEN p.modifier END,
         'channel_id', p.failing_channel_id
       ) AS evidence
FROM per_integration p
JOIN integrations i ON i.id = p.integration_id
JOIN branches b ON b.id = i.branch_id
LEFT JOIN integration_vendors v ON v.id = i.vendor_id
LEFT JOIN events ev ON ev.integration_id = i.id
LEFT JOIN auth_state a ON a.integration_id = i.id
WHERE p.failed_orders >= 3 AND p.failed_orders / p.orders > 0.2
ORDER BY started_at, i.id
integrationsHead of Integrations · integrations v3

Every integration on a POS release whose order failure rate over the last release_window_hours is at least release_error_rate and regression_factor times the rest of that vendor's releases (or the rest of the fleet when the vendor has one release), on at least release_min_orders orders spread over at least release_min_hours hours of the window. A regression is a change: the release was rolled out within the history (version_upgraded) or had a good day before it went bad; a release that has always run hotter than its siblings is a vendor-quality topic, not an incident. started_at is when the rollout reached the first integration (its version_upgraded event), else the first failure after the release's last good day.

latest change: A regression needs evidence of a change (a rollout event, or a good day before it went bad) and at least 12 hours of orders in its 24-hour window.

  • pass count_betweenobserved 71, expected between 30.0 and 150.0
  • pass uniqueobserved {'duplicates': 0}, expected integration_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 71}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH fails AS (
  SELECT DISTINCT e.order_id
  FROM order_events e
  WHERE e.type ILIKE '%injection_fail%'
    AND e.at <= TIMESTAMP 'Fri 23:00:00' AND e.at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY
),
attempts AS (
  SELECT i.id AS integration_id, i.vendor_id, i.version, o.accepted_at,
         CAST(o.accepted_at AS DATE) AS day, f.order_id IS NOT NULL AS failed
  FROM orders o
  JOIN branches b ON b.id = o.branch_id
  JOIN integrations i ON i.id = b.pos_integration_id
  JOIN channels c ON c.id = o.channel_id
  LEFT JOIN fails f ON f.order_id = o.id
  WHERE o.accepted_at <= TIMESTAMP 'Fri 23:00:00' AND o.accepted_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY
    AND c.kind <> 'dine_in'
),
releases AS (
  SELECT vendor_id, version, count(*) AS orders, count(*) FILTER (WHERE failed) AS failed,
         count(DISTINCT date_trunc('hour', accepted_at)) AS hours_with_orders
  FROM attempts
  WHERE accepted_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (24) HOUR
  GROUP BY vendor_id, version
),
baselines AS (
  SELECT r.*, r.failed / r.orders AS rate,
         (sum(r.failed) OVER (PARTITION BY r.vendor_id) - r.failed)
           / nullif(sum(r.orders) OVER (PARTITION BY r.vendor_id) - r.orders, 0) AS vendor_rate,
         (sum(r.failed) OVER () - r.failed) / nullif(sum(r.orders) OVER () - r.orders, 0) AS fleet_rate
  FROM releases r
),
regressed AS (
  SELECT *, coalesce(vendor_rate, fleet_rate, 0) AS baseline
  FROM baselines
  WHERE orders >= 200 AND hours_with_orders >= 12
    AND rate >= 0.02
    AND rate >= 3 * coalesce(vendor_rate, fleet_rate, 0)
),
daily AS (
  SELECT a.vendor_id, a.version, a.day, count(*) AS orders, count(*) FILTER (WHERE a.failed) AS failed
  FROM attempts a
  JOIN regressed r ON r.vendor_id = a.vendor_id AND r.version = a.version
  GROUP BY a.vendor_id, a.version, a.day
),
last_good AS (
  SELECT vendor_id, version, max(day) FILTER (WHERE failed / orders < 0.02) AS last_good_day
  FROM daily
  GROUP BY vendor_id, version
),
onset AS (
  SELECT d.vendor_id, d.version,
         min(d.day) FILTER (WHERE d.day > coalesce(g.last_good_day, DATE '-infinity')) AS onset_day,
         list(json_object('day', strftime(d.day, '%Y-%m-%d'), 'orders', d.orders, 'failed', d.failed)
              ORDER BY d.day) AS daily
  FROM daily d
  JOIN last_good g ON g.vendor_id = d.vendor_id AND g.version = d.version
  GROUP BY d.vendor_id, d.version
),
first_failure AS (
  SELECT a.vendor_id, a.version, min(a.accepted_at) AS started_at
  FROM attempts a
  JOIN onset s ON s.vendor_id = a.vendor_id AND s.version = a.version
  WHERE a.failed AND a.day >= s.onset_day
  GROUP BY a.vendor_id, a.version
),
upgrades AS (
  SELECT i.vendor_id, i.version, min(e.at) AS first_upgrade_at
  FROM integration_events e
  JOIN integrations i ON i.id = e.integration_id
  WHERE e.type ILIKE '%version_upgraded%' AND e.at <= TIMESTAMP 'Fri 23:00:00'
    AND coalesce(e.payload->>'to', i.version) = i.version
  GROUP BY i.vendor_id, i.version
),
per_integration AS (
  SELECT integration_id, count(*) AS orders, count(*) FILTER (WHERE failed) AS failed
  FROM attempts
  WHERE accepted_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (24) HOUR
  GROUP BY integration_id
)
SELECT 'integration' AS subject_type, i.id AS subject_id, i.id AS integration_id, i.branch_id,
       b.merchant_id, i.vendor_id, i.version, coalesce(up.first_upgrade_at, ff.started_at) AS started_at,
       'high' AS severity,
       json_object(
         'release', v.name || ' ' || r.version,
         'vendor', v.name,
         'version', r.version,
         'window_hours', 24,
         'release_orders', r.orders,
         'release_failed', r.failed,
         'release_error_rate', round(r.rate, 4),
         'baseline_error_rate', round(r.baseline, 4),
         'times_baseline', round(r.rate / nullif(r.baseline, 0), 1),
         'integrations_on_release', count(*) OVER (PARTITION BY r.vendor_id, r.version),
         'onset_day', strftime(s.onset_day, '%Y-%m-%d'),
         'first_upgrade_at', up.first_upgrade_at,
         'daily', s.daily,
         'integration_orders', coalesce(pi.orders, 0),
         'integration_failed', coalesce(pi.failed, 0)
       ) AS evidence
FROM regressed r
JOIN integrations i ON i.vendor_id = r.vendor_id AND i.version = r.version
JOIN branches b ON b.id = i.branch_id AND b.pos_integration_id = i.id
LEFT JOIN integration_vendors v ON v.id = i.vendor_id
LEFT JOIN onset s ON s.vendor_id = r.vendor_id AND s.version = r.version
LEFT JOIN first_failure ff ON ff.vendor_id = r.vendor_id AND ff.version = r.version
LEFT JOIN upgrades up ON up.vendor_id = r.vendor_id AND up.version = r.version
LEFT JOIN last_good g ON g.vendor_id = r.vendor_id AND g.version = r.version
LEFT JOIN per_integration pi ON pi.integration_id = i.id
WHERE up.first_upgrade_at IS NOT NULL OR g.last_good_day IS NOT NULL
ORDER BY i.vendor_id, i.version, i.id

Parameters

change one, see what changes tonight, then save it as a new version with a note
now 60 minutes · used by ops
now 3 · used by ops
now 0.2 · used by ops
now 90 · used by ops
now 24 · used by integrations
now 200 · used by integrations
now 12 · used by integrations
now 0.02 · used by integrations
now 3 · used by integrations
now 7 · used by integrations

History

append-only; nothing is edited in place
  1. definitionHead of Integrations integrations v3 A regression needs evidence of a change (a rollout event, or a good day before it went bad) and at least 12 hours of orders in its 24-hour window.
  2. definitionCOO ops v3 Names the item and channel when every failure in the window is a mapping error on one item.
  3. definitionCOO ops v4 auth_expired is read from the auth event stream as of now (stored expiry only when there is no event), refresh_failed added, and fix says to escalate to the POS partner when the last refresh failed.
  4. definitionHead of Integrations integrations v1 First version: a release failing at least 2% of its orders and 3x the rest of its vendor (or the fleet), on at least 200 orders in 24 hours.
  5. definitionHead of Integrations integrations v2 Seven days of daily history; the onset is taken from the rollout log when there is one.
  6. definitionCOO ops v1 First version: at least 3 failed orders and more than 20% of the integration's orders in the last 60 minutes.
  7. definitionCOO ops v2 Orders the POS rejected or that were cancelled no longer count as failed unless an injection attempt errored; menu drift was showing up as broken links.