Definitions

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

Overdue invoice

overdue_invoice unit: companies agent detector canonical: finance

Merchants that owe money past the due date. Finance escalates at overdue_days past due (canonical); Customer Success wants every past-due account.

financeCFO · finance v1canonical

One row per merchant with at least one positive invoice unpaid overdue_days or more past its due date. Evidence lists the invoices and whether the merchant still received orders in the last trading_days days.

latest change: First version.

pass count_betweenobserved 37, expected between 20.0 and 120.0
pass uniqueobserved {'duplicates': 0}, expected merchant_id unique
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 37}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

SQL, rendered for now

WITH unpaid AS (
  SELECT i.*
  FROM invoices i
  WHERE i.amount > 0 AND i.issued_at <= TIMESTAMP 'Fri 23:00:00'
    AND (i.paid_at IS NULL OR i.paid_at > TIMESTAMP 'Fri 23:00:00')
    AND i.due_at <= TIMESTAMP 'Fri 23:00:00' - INTERVAL (45) DAY
),
trading AS (
  SELECT b.merchant_id, count(*) AS orders, max(o.placed_at) AS last_order_at
  FROM orders o
  JOIN branches b ON b.id = o.branch_id
  WHERE o.placed_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY AND o.placed_at <= TIMESTAMP 'Fri 23:00:00'
    AND o.status <> 'rejected'
  GROUP BY b.merchant_id
)
SELECT 'merchant' AS subject_type, m.id AS subject_id, m.id AS merchant_id,
       min(u.due_at) + INTERVAL (45) DAY AS started_at,
       CASE WHEN coalesce(t.orders, 0) > 0 THEN 'high' ELSE 'medium' END AS severity,
       count(*) AS invoices,
       round(sum(u.amount), 2) AS amount_overdue,
       any_value(u.currency) AS currency,
       json_object(
         'merchant', m.name,
         'plan', m.plan,
         'csm_owner', m.csm_owner,
         'invoices', list(json_object('invoice_id', u.id, 'amount', u.amount, 'currency', u.currency,
                                      'period_start', u.period_start, 'issued_at', u.issued_at,
                                      'due_at', u.due_at, 'days_past_due', date_diff('day', u.due_at, TIMESTAMP 'Fri 23:00:00'))
                          ORDER BY u.due_at),
         'amount_overdue', round(sum(u.amount), 2),
         'oldest_days_past_due', max(date_diff('day', u.due_at, TIMESTAMP 'Fri 23:00:00')),
         'threshold_days', 45,
         'orders_recent', coalesce(t.orders, 0),
         'trading_days', 7,
         'still_receiving_orders', coalesce(t.orders, 0) > 0,
         'last_order_at', t.last_order_at
       ) AS evidence
FROM unpaid u
JOIN merchants m ON m.id = u.merchant_id
LEFT JOIN trading t ON t.merchant_id = m.id
GROUP BY m.id, m.name, m.plan, m.csm_owner, t.orders, t.last_order_at
ORDER BY started_at, m.id

csHead of CS · cs v1

One row per merchant with any positive invoice past its due date and unpaid, however recent.

latest change: First version.

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

SQL, rendered for now

WITH unpaid AS (
  SELECT i.*
  FROM invoices i
  WHERE i.amount > 0 AND i.issued_at <= TIMESTAMP 'Fri 23:00:00'
    AND (i.paid_at IS NULL OR i.paid_at > TIMESTAMP 'Fri 23:00:00')
    AND i.due_at < TIMESTAMP 'Fri 23:00:00'
),
trading AS (
  SELECT b.merchant_id, count(*) AS orders, max(o.placed_at) AS last_order_at
  FROM orders o
  JOIN branches b ON b.id = o.branch_id
  WHERE o.placed_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY AND o.placed_at <= TIMESTAMP 'Fri 23:00:00'
    AND o.status <> 'rejected'
  GROUP BY b.merchant_id
)
SELECT 'merchant' AS subject_type, m.id AS subject_id, m.id AS merchant_id,
       min(u.due_at) AS started_at,
       CASE WHEN max(date_diff('day', u.due_at, TIMESTAMP 'Fri 23:00:00')) >= 45 THEN 'high' ELSE 'low' END AS severity,
       count(*) AS invoices,
       round(sum(u.amount), 2) AS amount_overdue,
       any_value(u.currency) AS currency,
       json_object(
         'merchant', m.name,
         'csm_owner', m.csm_owner,
         'invoices', list(json_object('invoice_id', u.id, 'amount', u.amount, 'due_at', u.due_at,
                                      'days_past_due', date_diff('day', u.due_at, TIMESTAMP 'Fri 23:00:00'))
                          ORDER BY u.due_at),
         'oldest_days_past_due', max(date_diff('day', u.due_at, TIMESTAMP 'Fri 23:00:00')),
         'orders_recent', coalesce(t.orders, 0),
         'still_receiving_orders', coalesce(t.orders, 0) > 0
       ) AS evidence
FROM unpaid u
JOIN merchants m ON m.id = u.merchant_id
LEFT JOIN trading t ON t.merchant_id = m.id
GROUP BY m.id, m.name, m.csm_owner, t.orders
ORDER BY started_at, m.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.
definitionCFO finance v1 First version.