Rails 24:00 Orvane Home & Outdoor

← Definitions

Changed to an address not on file address_change_fraud

An order whose delivery address was changed after payment to an address the account has never used, in another region, on a high-value basket from an established account: the shape of an account takeover.

Parameters: min_value = 200.0 min_account_age_days = 180

Test results

meaningtestpassedobservedexpected
trustcount_betweenpassed0between 0.0 and 20.0
trustuniquepassed{'duplicates': 0}order_id unique
trustdetector_shapepassed{'missing_columns': [], 'bad_rows': 0, 'rows': 0}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

trust · v1 · trust and safety lead

The address changed (as of now) to one not on file and in a region other than the account's home region, the order is worth at least min_value, the account is at least min_account_age_days old, and the order is not already held, intercepted or released. Customers who move or send a gift look the same; whether they asked for the change in a message first is in the evidence, and the detector does not use it.

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

WITH changes AS (
  SELECT order_id, max("at") AS changed_at, arg_max(payload, "at") AS payload
  FROM order_events
  WHERE type = 'address_changed' AND "at" <= {now} AND "at" >= {period_start} AND "at" < {period_end}
  GROUP BY order_id
),
handled AS (
  SELECT DISTINCT order_id FROM order_events
  WHERE type IN ('held', 'intercept_requested', 'released') AND "at" <= {now}
),
asked AS (
  SELECT m.order_id, max(m.at) AS asked_at FROM customer_messages m
  WHERE m.topic = 'address_update_request' AND m.at <= {now} GROUP BY m.order_id
)
SELECT 'order' AS subject_type, o.id AS subject_id, o.id AS order_id, o.customer_id AS account_id,
       o.marketplace_id, 'order:' || o.id AS scope, ch.changed_at AS started_at,
       CASE WHEN o.total >= 500 THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'order_no', o.order_no, 'total', o.total, 'marketplace', m.name,
         'account_age_days', date_diff('day', c.created_at, {now}),
         'minutes_after_payment', date_diff('minute', o.paid_at, ch.changed_at),
         'home_region', hr.name, 'new_region', nr.name,
         'new_address', json_extract_string(ch.payload, '$.to_line1'),
         'asked_in_message', a.asked_at IS NOT NULL,
         'stage', CASE WHEN o.delivered_at <= {now} THEN 'delivered' WHEN o.shipped_at <= {now} THEN 'shipped'
                       WHEN o.picked_at <= {now} THEN 'picked' ELSE 'paid' END
       ) AS evidence
FROM changes ch
JOIN orders o ON o.id = ch.order_id
JOIN customers c ON c.id = o.customer_id
JOIN marketplaces m ON m.id = o.marketplace_id
JOIN regions hr ON hr.id = c.region_id
JOIN regions nr ON nr.id = CAST(json_extract(ch.payload, '$.to_region_id') AS INTEGER)
LEFT JOIN handled h ON h.order_id = o.id
LEFT JOIN asked a ON a.order_id = o.id
WHERE NOT CAST(json_extract(ch.payload, '$.on_file') AS BOOLEAN)
  AND nr.id <> c.region_id
  AND o.total >= {min_value}
  AND date_diff('day', c.created_at, {now}) >= {min_account_age_days}
  AND h.order_id IS NULL
  AND (o.cancelled_at IS NULL OR o.cancelled_at > {now})
ORDER BY started_at, o.id

Tests: count_between unique