Rails 24:00 Orvane Home & Outdoor

← Definitions

Price below cost price_below_cost

A listing selling under what the product costs, without an approved markdown: usually a price-feed sync gone wrong.

Parameters: margin_floor = 0.0

Run the tests on this world

trading · v1 · trading lead

The listing's price as of now (the latest price sync, else an active promotion, else the list price) is below the product's unit cost plus margin_floor, the listing is not paused, and no approved below-cost promotion covers this listing at this price. Approvals are recorded per listing: a loss leader approved on the own shop and copied to a marketplace by hand is listed until its marketplace listing is approved too.

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

WITH synced AS (
  SELECT listing_id, arg_max(CAST(json_extract(payload, '$.price') AS DOUBLE), "at") AS price,
         arg_max(json_extract_string(payload, '$.source'), "at") AS source, max("at") AS changed_at
  FROM listing_events WHERE type = 'price_sync' AND "at" <= {now} GROUP BY listing_id
),
promo AS (
  SELECT listing_id, promo_price, below_cost_approved, approved_by, starts_at
  FROM promotions WHERE starts_at <= {now} AND ends_at > {now}
),
paused AS (
  SELECT listing_id, arg_max(type, "at") AS state
  FROM listing_events WHERE type IN ('paused', 'resumed') AND "at" <= {now} GROUP BY listing_id
),
priced AS (
  SELECT l.id AS listing_id, l.product_id, l.marketplace_id, l.price AS list_price,
         CASE WHEN s.price IS NOT NULL AND (pr.listing_id IS NULL OR s.changed_at >= pr.starts_at) THEN s.price
              WHEN pr.listing_id IS NOT NULL THEN pr.promo_price ELSE l.price END AS price,
         CASE WHEN s.price IS NOT NULL AND (pr.listing_id IS NULL OR s.changed_at >= pr.starts_at) THEN s.changed_at
              WHEN pr.listing_id IS NOT NULL THEN pr.starts_at END AS since,
         CASE WHEN s.price IS NOT NULL AND (pr.listing_id IS NULL OR s.changed_at >= pr.starts_at) THEN s.source
              WHEN pr.listing_id IS NOT NULL THEN 'promotion' ELSE 'list price' END AS source,
         coalesce(pr.below_cost_approved, FALSE) AND pr.promo_price IS NOT NULL AS approved,
         pr.promo_price
  FROM listings l
  LEFT JOIN synced s ON s.listing_id = l.id
  LEFT JOIN promo pr ON pr.listing_id = l.id
  WHERE s.listing_id IS NOT NULL OR pr.listing_id IS NOT NULL
),
sold AS (
  SELECT ol.listing_id, count(*) AS orders, round(sum(ol.qty * (p.unit_cost - ol.unit_price)), 2) AS loss
  FROM order_lines ol JOIN orders o ON o.id = ol.order_id JOIN products p ON p.id = ol.product_id
  WHERE o.placed_at <= {now} AND o.placed_at > {now} - INTERVAL 1 DAY AND ol.unit_price < p.unit_cost
  GROUP BY ol.listing_id
)
SELECT 'listing' AS subject_type, x.listing_id AS subject_id, x.listing_id, x.product_id, x.marketplace_id,
       'listing:' || x.listing_id AS scope, x.since AS started_at,
       CASE WHEN x.price < p.unit_cost * 0.5 THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'sku', p.sku, 'product', p.name, 'marketplace', m.name, 'price', x.price, 'unit_cost', p.unit_cost,
         'margin_pct', round((x.price - p.unit_cost) / x.price * 100, 1), 'list_price', x.list_price,
         'promo_price', x.promo_price, 'price_source', x.source, 'priced_since', x.since,
         'orders_below_cost_24h', coalesce(so.orders, 0), 'loss_24h', coalesce(so.loss, 0),
         'category_manager', c.manager
       ) AS evidence
FROM priced x
JOIN products p ON p.id = x.product_id
JOIN categories c ON c.id = p.category_id
JOIN marketplaces m ON m.id = x.marketplace_id
LEFT JOIN paused pa ON pa.listing_id = x.listing_id
LEFT JOIN sold so ON so.listing_id = x.listing_id
WHERE x.price < p.unit_cost * (1 + {margin_floor})
  AND coalesce(pa.state, 'resumed') <> 'paused'
  AND NOT (x.approved AND x.price = x.promo_price)
ORDER BY started_at, x.listing_id

Tests: count_between unique