Rails 24:00 Orvane Home & Outdoor

← Definitions

Duplicate order duplicate_order

The same order placed twice by a double submit: same customer, same channel, same basket, same delivery address, seconds apart. The later copy is the duplicate.

Parameters: window_seconds = 120

Run the tests on this world

operations · v1 · head of operations

A later order with the same customer, channel, basket (every line and quantity) and delivery address as an earlier one placed within window_seconds, not cancelled, held or refunded as of now. A customer who really buys the same thing again two minutes later looks exactly like this; it happens, rarely.

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

WITH recent AS (
  SELECT id, customer_id, marketplace_id, basket_key, ship_line1, total, placed_at, paid_at, picked_at,
         shipped_at, cancelled_at
  FROM orders
  WHERE placed_at <= {now} AND placed_at >= {period_start} - INTERVAL ({window_seconds}) SECOND
    AND placed_at < {period_end}
),
pairs AS (
  SELECT d.id AS order_id, arg_min(o.id, o.placed_at) AS original_order_id, min(o.placed_at) AS original_at,
         any_value(d.placed_at) AS placed_at
  FROM recent d
  JOIN recent o
    ON o.customer_id = d.customer_id AND o.marketplace_id = d.marketplace_id AND o.basket_key = d.basket_key
   AND o.ship_line1 = d.ship_line1 AND o.id <> d.id
   AND (o.placed_at < d.placed_at OR (o.placed_at = d.placed_at AND o.id < d.id))
   AND d.placed_at - o.placed_at <= INTERVAL ({window_seconds}) SECOND
  WHERE d.placed_at >= {period_start}
  GROUP BY d.id
),
handled AS (
  SELECT DISTINCT order_id FROM order_events WHERE type IN ('held', 'refunded') AND "at" <= {now}
)
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, o.placed_at AS started_at,
       CASE WHEN o.shipped_at <= {now} THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'order_no', o.order_no, 'original_order_id', p.original_order_id,
         'seconds_apart', date_diff('second', p.original_at, o.placed_at), 'total', o.total,
         'items', o.item_count, 'marketplace', m.name,
         'stage', CASE WHEN o.shipped_at <= {now} THEN 'shipped' WHEN o.picked_at <= {now} THEN 'picked'
                       WHEN o.paid_at <= {now} THEN 'paid' ELSE 'placed' END,
         'paid', o.paid_at <= {now}
       ) AS evidence
FROM pairs p
JOIN orders o ON o.id = p.order_id
JOIN marketplaces m ON m.id = o.marketplace_id
LEFT JOIN handled h ON h.order_id = o.id
WHERE 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 sale_evening_bug unique