Rails 24:00 Orvane Home & Outdoor

← Definitions

Oversell oversell

A listing that keeps taking orders the warehouses cannot fill, because the channel still shows stock the warehouses no longer have (the stock feed drifted). One row per listing.

Parameters: min_failures = 2 lookback_minutes = 180

Run the tests on this world

marketplaces · v1 · head of marketplaces

At least min_failures orders on the listing failed allocation (no stock in any warehouse) in the last lookback_minutes, and were not reallocated since. A listing whose latest stock sync already shows zero cannot be oversold and is not listed. A single failed allocation is a picker's miscount far more often than a drifted feed, so one is not enough.

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

WITH fails AS (
  SELECT e.order_id, e.at, CAST(json_extract(e.payload, '$.listing_id') AS INTEGER) AS listing_id
  FROM order_events e
  WHERE e.type = 'allocation_failed' AND e.at <= {now}
    AND e.at > {now} - INTERVAL ({lookback_minutes}) MINUTE
),
reallocated AS (
  SELECT DISTINCT order_id FROM order_events WHERE type = 'reallocated' AND "at" <= {now}
),
sync AS (
  SELECT listing_id, arg_max(CAST(json_extract(payload, '$.qty') AS INTEGER), "at") AS qty, max("at") AS synced_at
  FROM listing_events WHERE type = 'stock_sync' AND "at" <= {now} GROUP BY listing_id
),
open_fails AS (
  SELECT f.listing_id, f.order_id, f.at, o.total
  FROM fails f
  JOIN orders o ON o.id = f.order_id
  LEFT JOIN reallocated r ON r.order_id = f.order_id
  LEFT JOIN sync s ON s.listing_id = f.listing_id
  WHERE r.order_id IS NULL AND coalesce(s.qty, 1) > 0
),
agg AS (
  SELECT listing_id, count(*) AS failed, min("at") AS first_failed, max("at") AS last_failed,
         list(order_id ORDER BY "at") AS order_ids, round(sum(total), 2) AS value
  FROM open_fails GROUP BY listing_id HAVING count(*) >= {min_failures}
),
stock AS (SELECT product_id, sum(on_hand) AS on_hand FROM stock_levels GROUP BY product_id)
SELECT 'listing' AS subject_type, l.id AS subject_id, l.id AS listing_id, l.product_id, l.marketplace_id,
       'listing:' || l.id AS scope, a.first_failed AS started_at,
       CASE WHEN a.failed >= 4 THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'sku', p.sku, 'product', p.name, 'marketplace', m.name,
         'failed_orders', a.failed, 'order_ids', a.order_ids, 'value_at_risk', a.value,
         'last_failed_at', a.last_failed,
         'qty_shown', coalesce(s.qty, l.qty_shown), 'last_stock_sync', coalesce(s.synced_at, l.last_synced_at),
         'warehouse_on_hand', coalesce(st.on_hand, 0), 'lookback_minutes', {lookback_minutes}
       ) AS evidence
FROM agg a
JOIN listings l ON l.id = a.listing_id
JOIN products p ON p.id = l.product_id
JOIN marketplaces m ON m.id = l.marketplace_id
LEFT JOIN sync s ON s.listing_id = l.id
LEFT JOIN stock st ON st.product_id = l.product_id
ORDER BY started_at, l.id

Tests: count_between sale_evening unique