finance · v2 · Finance Controller
0
invoices at Mon 20:00
kind = work; received before now; not paid and not held before now. Matched when tickets.id equals the ticket field, or else the first number in the reference, and that ticket's vendor is the invoice's vendor or the ticket's history shows that vendor dispatched to it before the invoice arrived.
finance · changed 2026-11-02: A vendor dispatched to the ticket at any time matches, not only the vendor on it now. Found on seed 7: after a re-dispatch, the first vendor's honest invoice for the visit was flagged.
Tests
- count_between min 0.0 max 80.0
- unique on invoice_id
- detector_shape
SQL
WITH open_invoices AS (
SELECT i.*, coalesce(i.ticket_id, TRY_CAST(regexp_extract(i.reference, '([0-9]+)', 1) AS INTEGER)) AS ref_id
FROM vendor_invoices i
WHERE i.kind = 'work' AND i.received_at <= {now}
AND (i.paid_at IS NULL OR i.paid_at > {now}) AND (i.held_at IS NULL OR i.held_at > {now})
)
SELECT 'invoice' AS subject_type, i.id AS subject_id, i.id AS invoice_id, i.vendor_id, i.building_id,
i.received_at AS started_at, CASE WHEN i.amount >= 500 THEN 'high' ELSE 'medium' END AS severity,
i.amount,
json_object('reference', i.reference, 'referenced_ticket_id', i.ref_id,
'referenced_ticket_vendor_id', t.vendor_id, 'referenced_ticket_exists', t.id IS NOT NULL,
'vendor', v.name, 'trade', v.trade, 'status', i.status,
'reason', CASE WHEN i.ref_id IS NULL THEN 'no job number'
WHEN t.id IS NULL THEN 'job number does not exist'
ELSE 'job belongs to another vendor' END) AS evidence
FROM open_invoices i
JOIN vendors v ON v.id = i.vendor_id
LEFT JOIN tickets t ON t.id = i.ref_id
WHERE t.id IS NULL
OR (t.vendor_id IS DISTINCT FROM i.vendor_id
AND NOT EXISTS (SELECT 1 FROM ticket_events e WHERE e.ticket_id = t.id AND e.at <= i.received_at
AND e.type IN ('vendor_dispatched', 'vendor_reassigned', 'vendor_replaced')
AND TRY_CAST(json_extract_string(e.payload, '$.vendor_id') AS INTEGER) = i.vendor_id))
ORDER BY i.received_at, i.id