Definitions

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

POS token expiring

pos_token_expiring unit: integrations agent detector canonical: integrations

A live POS integration whose access token runs out within the next half hour and has not been refreshed. Refreshing it now avoids the orders that would otherwise stop reaching the kitchen.

integrationsHead of Integrations · integrations v2canonical

The branch's POS integration, unless pending or disconnected, whose auth_expires_at (the token the platform will use) falls after now and within warning_minutes of it, with no token_refreshed or auth_refreshed event since the warning window opened. A refresh moves auth_expires_at, so a refreshed token drops out on its own; a replay reads the stored expiry, which treats scheduled refreshes that did happen as done. started_at is when the warning window opened (expiry minus warning_minutes).

latest change: Only pending and disconnected integrations are skipped, so integrations whose token later expired (status auth_expired in the snapshot) are caught in their warning window.

pass count_betweenobserved 0, expected between 0.0 and 50.0
pass uniqueobserved {'duplicates': 0}, expected integration_id unique
pass not_nullobserved {'null_cells': 0}, expected no NULLs in ['integration_id', 'branch_id', 'expires_at']
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

SQL, rendered for now

WITH refreshes AS (
  SELECT e.integration_id, max(e.at) AS last_refresh_at
  FROM integration_events e
  WHERE (e.type ILIKE '%token_refresh%' OR e.type ILIKE '%auth_refresh%') AND e.at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY e.integration_id
)
SELECT 'integration' AS subject_type, i.id AS subject_id, i.id AS integration_id, b.id AS branch_id,
       b.merchant_id, i.vendor_id, i.version, v.name AS vendor, i.auth_expires_at AS expires_at,
       i.auth_expires_at - INTERVAL (30) MINUTE AS started_at,
       CASE WHEN i.auth_expires_at <= TIMESTAMP 'Fri 23:00:00' + INTERVAL 10 MINUTE THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'branch', b.name,
         'vendor', v.name,
         'version', i.version,
         'integration_status', i.status,
         'expires_at', i.auth_expires_at,
         'minutes_left', round(date_diff('second', TIMESTAMP 'Fri 23:00:00', i.auth_expires_at) / 60.0, 1),
         'warning_minutes', 30,
         'last_refresh_at', r.last_refresh_at
       ) AS evidence
FROM integrations i
JOIN branches b ON b.pos_integration_id = i.id
LEFT JOIN integration_vendors v ON v.id = i.vendor_id
LEFT JOIN refreshes r ON r.integration_id = i.id
WHERE i.auth_expires_at > TIMESTAMP 'Fri 23:00:00'
  AND i.auth_expires_at <= TIMESTAMP 'Fri 23:00:00' + INTERVAL (30) MINUTE
  AND coalesce(i.status, '') NOT IN ('pending', 'disconnected')
  AND coalesce(r.last_refresh_at, TIMESTAMP '-infinity') < i.auth_expires_at - INTERVAL (30) MINUTE
ORDER BY started_at, i.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 v1 First version, so a token can be refreshed before it expires instead of after.
definitionHead of Integrations integrations v2 Only pending and disconnected integrations are skipped, so integrations whose token later expired (status auth_expired in the snapshot) are caught in their warning window.