Chargeback near its deadline without evidence chargeback_deadline
A chargeback inside its last window_hours before the answer is due, with no evidence pack filed yet. Unanswered, it is lost by default.
Parameters: window_hours = 72
Test results
| meaning | test | passed | observed | expected |
|---|---|---|---|---|
| payments | count_between | passed | 0 | between 0.0 and 30.0 |
| payments | unique | passed | {'duplicates': 0} | chargeback_id unique |
| payments | detector_shape | passed | {'missing_columns': [], 'bad_rows': 0, 'rows': 0} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |
payments · v1 · finance lead
Opened by now, not answered by now (no evidence filed, or filed later than now), due after now and within window_hours. Delivery proof in the evidence is what the carrier and the order hub show as of now.
WITH proof AS (
SELECT order_id, count(*) AS scans, max("at") FILTER (WHERE type = 'delivered') AS delivered_scan_at
FROM carrier_events WHERE "at" <= {now} GROUP BY order_id
)
SELECT 'chargeback' AS subject_type, c.id AS subject_id, c.id AS chargeback_id, c.order_id, c.marketplace_id,
'chargeback:' || c.id AS scope,
c.respond_by - INTERVAL ({window_hours}) HOUR AS started_at,
CASE WHEN c.amount >= 150 OR c.respond_by - {now} < INTERVAL 24 HOUR THEN 'high' ELSE 'medium' END AS severity,
json_object(
'amount', c.amount, 'reason', c.reason, 'marketplace', m.name, 'order_no', o.order_no,
'opened_at', c.opened_at, 'respond_by', c.respond_by,
'hours_left', round(date_diff('minute', {now}, c.respond_by) / 60.0, 1),
'delivered_at', CASE WHEN o.delivered_at <= {now} THEN o.delivered_at END,
'delivery_scan_at', pr.delivered_scan_at, 'carrier_scans', coalesce(pr.scans, 0),
'has_delivery_proof', coalesce(o.delivered_at <= {now}, FALSE) OR pr.delivered_scan_at IS NOT NULL
) AS evidence
FROM chargebacks c
JOIN orders o ON o.id = c.order_id
JOIN marketplaces m ON m.id = c.marketplace_id
LEFT JOIN proof pr ON pr.order_id = c.order_id
WHERE c.opened_at <= {now}
AND (c.evidence_filed_at IS NULL OR c.evidence_filed_at > {now})
AND c.respond_by > {now}
AND c.respond_by - INTERVAL ({window_hours}) HOUR <= {now}
ORDER BY started_at, c.id