Rails 24:00 Orvane Home & Outdoor

← Definitions

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

Run the tests on this world

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.

Changed 2026-10-12: Agreed with the owner.

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

Tests: count_between unique