-- Reserve only when sufficient stock remains. Success is affected_rows = 1.
UPDATE products
SET stock = stock - :quantity
WHERE id = :product_id
  AND stock >= :quantity;

-- A likely index for tenant-scoped recent pending orders. Confirm with EXPLAIN.
CREATE INDEX orders_tenant_status_created_id
    ON orders (tenant_id, status, created_at, id);

