Every e-commerce backend has the same nemesis: two people buying the last item at the same moment. This post is about how a Worker and a D1 database handle it — with a one-line SQL condition doing most of the work.

The story

The storefront is a SPA served from the Worker’s static assets; the catalog, carts and orders live in D1. Carts are anonymous by design: an HttpOnly cookie maps the browser to a carts row, and no account is required until signup. When someone does sign up (or sign in), the existing cart is claimed — attached to the user — so its contents and order history survive across devices.

How it works

Adding to the cart validates stock against D1 before anything is written, and returns a helpful 409 with the real availability when it doesn’t fit:

const product = await env.DB.prepare(
  'SELECT id, stock FROM products WHERE id = ? AND active = 1'
).bind(productId).first<{ id: string; stock: number }>();

const newQty = (existing?.qty ?? 0) + qty;
if (newQty > product.stock) {
  return jsonResponse({ error: `Only ${product.stock} in stock.`, availableStock: product.stock }, 409);
}

Checkout is where the oversell guard lives. Every decrement is a conditional update — the WHERE stock >= qty clause means the row only changes if the stock is actually there:

const statements = [
  ...items.map((item) =>
    env.DB.prepare('UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?')
      .bind(item.qty, item.product_id, item.qty)
  ),
  env.DB.prepare('INSERT INTO orders (id, email, status, total_cents) VALUES (?, ?, ?, ?)'),
  ...items.map((item) => env.DB.prepare('INSERT INTO order_items (...) VALUES (...)')),
  env.DB.prepare("UPDATE carts SET status = 'converted' WHERE id = ?"),
];
const batchResults = await env.DB.batch(statements);

DB.batch runs as a single transaction — all four statements or none. And because a conditional update that loses a race changes 0 rows (meta.changes !== 1), the code can detect the loss and compensate: refund the stock that did land, delete the half-created order, put the cart back to active, and tell the shopper honestly:

const failedIndex = decrements.findIndex((r) => r.meta.changes !== 1);
if (failedIndex !== -1) {
  // refund decrements that succeeded, delete order + items, reactivate cart
  await env.DB.batch(compensation);
  return jsonResponse(
    { error: `"${conflicted.name}" just sold out. Order cancelled, nothing was charged.` },
    409
  );
}

Two details worth stealing for any real project:

• The email rendered in the admin Orders tab is validated against markup characters (<>) at the boundary — defense in depth behind the panel’s output escaping.
• Order history is a union: the signed-in account’s orders plus everything placed from this browser’s cart. History follows the account across devices once orders are placed signed-in.

What the demo shows

The beret is seeded at stock 0 and the minimalist watch at stock 3 — on purpose, so inventory states are demoable without scripting. Open two browser windows, buy the last watch in one, attempt it in the other: the sold-out guard fires, and no one oversells anything.

Evidence: what to capture

• Storefront product cards with stock badges (in stock / only N left / out of stock) → 02-stock-badges.png
• Cart drawer with items and subtotal → 02-cart-drawer.png
• Demo 4 run: window A buys the last watch (order confirmed), window B gets the sold-out guard → two screenshots 02-checkout-race-a.png / 02-checkout-race-b.png
• Cloudflare dashboard → D1 → lumina-users → orders table showing the confirmed order → 02-orders-table.png
• The checkout-flow diagram: open Blog/src/assets/diagrams/checkout-flow.excalidraw in Excalidraw, export PNG → 02-d1-storefront-checkout/checkout-flow.png (embedded above)

Key takeaways

• UPDATE … WHERE stock >= qty is the cheapest reliable oversell guard — the database wins races for you.
• Detect the lost race with meta.changes, then compensate explicitly: refund, delete, restore, explain.
• Anonymous-cookie carts + a claim step gives you a no-signup UX that still converts into accounts cleanly.