Four in stock, six sold: availability is a measurement, a reservation is a promise

Forty units of a limited run, announced to a mailing list at 20:00, sold out in eleven minutes. The warehouse packed forty of them the next morning and then stopped, because there were forty, and the webshop had taken forty-six orders.

The six people at the back of that queue had a confirmation e-mail with an order number on it. One of them had already arranged to give it to someone for a birthday. Support spent a morning on it, we refunded six payments plus the shipping we had already charged, and the client asked the only reasonable question: which part of this was broken?

Nothing was. The storefront rendered the number it had been given. The ERP answered truthfully every time it was asked. All forty-six orders were valid orders, placed by real people, paid for with real money. The defect was not in a component — it was in a sentence nobody had written down: "in stock: 4" is a measurement, and the shop had been treating it as a promise.

The product page cannot promise you anything

The number on a product page is the answer to a question asked in the past: how many were there when we last looked? Between that question and the moment somebody's card is charged, there are at least four clocks running, and only one of them is usually in the conversation.

The one everybody discusses is the cache. A statically rendered catalogue page is as old as its last build; an incrementally revalidated one is as old as its revalidation window; a client-side query is as old as its staleTime. That is real, and it is the easiest to fix, which is why it absorbs all the attention.

The other three are more stubborn. There is the sync interval, if availability travels from the ERP to the storefront on a schedule rather than an event. There is concurrency: two visitors reading 4 within the same second are both reading the truth, and the truth is about to be wrong for one of them. And there is the human in the middle — the three to six minutes between adding to a cart and completing a payment, during which the shopper is finding their card reader and the stock figure belongs to everybody.

That last one is the proof that this is not a caching problem. Suppose the product page read the ERP live, with no cache at all, at a cost you would never actually pay. You would still oversell, because the check and the commitment are two different events separated by a human being. No amount of freshness closes a gap that exists by construction.

So the architecture question is not "how do we make the number more accurate". It is where in the flow does the shop stop measuring and start committing — and in most headless builds we are handed, the answer is nowhere.

Four numbers, and the one your storefront is probably reading

Before anything else: Odoo will cheerfully give you several different quantities for the same product, and they mean genuinely different things. Most of the oversells we have traced came down to a field name chosen a year earlier by whoever wrote the first integration.

qty_available is the trap, because it is the field with the obvious name and it looks right in a test database where nothing is reserved. virtual_available is the opposite trap: it is not wrong, it is answering a different question — it is the number a planner wants, and publishing it to a customer turns "in stock" into a statement about the future that your supplier gets to decide.

Both are also location-sensitive in a way that is easy to miss on a single-warehouse client and expensive to discover on their second one:

/**
 * free_qty is on-hand minus reserved, which is the only one of Odoo's
 * quantity fields that respects promises already made to other orders.
 * The warehouse context matters: without it you get the company-wide total,
 * which includes stock you cannot ship from this channel.
 */
const [product] = await odoo.read("product.product", [id], {
  fields: ["free_qty", "qty_available", "virtual_available", "uom_id"],
  context: { warehouse: WEBSHOP_WAREHOUSE_ID, lang: "en_US" },
})

Odoo's own e-commerce module gets this right by default — it sells on free_qty and has settings for out-of-stock behaviour and a display threshold. The regression is almost always ours: somebody builds a React storefront in front of the ERP, reimplements the availability read in the API layer, and picks the field that was easiest to find.

Publish a band, keep the number for the checkout

Here is the first opinionated bit, and it is the change that removes most of the pain for the least work: stop putting an integer on the product page.

Three states are enough for a catalogue — in stock, only a few left, out of stock. Not because precision is bad, but because a band is stale far less often than a number is. Forty units ticking down to thirty-seven changes the number three times and the band zero times. A figure that changes every minute is a figure you have to keep fresh every minute; a band that changes twice a week can live happily inside a statically rendered page and a sensible revalidation window.

It is also more honest. "Only a few left" is true for a range of real values, so it cannot be contradicted by a single sale. "4 in stock" is a specific claim that the shop is in no position to make, and customers read it as a reservation — reasonably, since that is how it reads.

Where you do want the exact number is the cart and the checkout, which are the two places the customer is deciding to commit, and the two places you can afford a live read because there are three of them per session rather than three hundred. That splits cleanly along the line we already use for where a fetch belongs: the band ships with the page, the number is a request made on the customer's behalf at the moment it matters.

The exception worth keeping is the one case where the count itself is doing sales work — a genuine scarce drop, where "7 left" is the reason someone buys now. Fine. Pay for it deliberately: live read, short cache, and a hold window at checkout (below), rather than a stale integer and a crossed-fingers emoji.

The buffer is not an admission of defeat

Available to promise should be free_qty minus a buffer, and engineers hate this. It feels like giving up: we have a database, the database knows, why would we deliberately publish a number lower than the truth?

Because free_qty is not the truth. It is the ERP's best record of the truth, and the two diverge for reasons that have nothing to do with software:

• Counting drift. Cycle counts find discrepancies in every warehouse we have ever worked in. Barcode-driven picking shrinks the gap a lot; it does not close it.
• Returns in limbo. A parcel is back in the building, physically on a shelf, and not yet inspected or restocked.
• Other channels selling the same stock. A shop floor, a marketplace listing, a B2B rep taking an order by phone. A point of sale that kept trading through a dead connection is correct behaviour that your webshop learns about several minutes late.
• Damage found at pick time. The unit exists in the system and is not sellable.

So the buffer is a measured compensation for known drift, not a fudge factor. Size it per SKU class from your own data — fast-moving smalls with high pick error, one or two; pallet goods counted on arrival, zero — and store it as a field on the product rather than a constant in the API layer, so that the person who understands the warehouse can change it without a deploy.

And price both sides of it before arguing about the number. A buffer costs you the tail of a sell-through: the last unit or two of a line that sells out, occasionally. An oversell costs you a refund, the shipping you already paid, twenty minutes of a support person's day, and a customer whose first experience of the brand was a cancellation e-mail. In every exercise we have done with a client, the buffer is cheaper by a wide margin, and the only honest exception is a product where the final units are the whole margin.

The fix lives at the commitment, not on the page

Everything above reduces how often you are wrong. It does not make the shop correct, because correctness has to happen at the one moment where the shop stops showing numbers and starts making a promise. That moment is checkout, and what has to happen there is a reservation: a write that either succeeds and takes the stock out of circulation, or fails and tells the customer before their money moves.

In Odoo terms this already exists and most integrations skip it. Confirming a sale order creates the delivery and asks the warehouse to assign stock to it; the moves come back assigned when they are fully reserved, partially_available or confirmed when they are not. The reservation strategy is configurable per operation type — at_confirm, by_date or manual — and if that is set to anything other than at_confirm for your webshop's picking type, confirming an order reserves nothing and your "stock check" is a formality.

/**
 * Confirming the order is the stock decision. Everything before this point is
 * advisory. `_action_confirm` triggers reservation (picking type must be set to
 * reserve at confirm), so the move states tell us whether the promise holds.
 */
async function confirmWithStock(orderId: number, idempotencyKey: string) {
  await odoo.call("sale.order", "action_confirm", [[orderId]], {
    context: { idempotency_key: idempotencyKey },
  })
 
  const moves = await odoo.searchRead("stock.move", {
    domain: [["sale_line_id.order_id", "=", orderId]],
    fields: ["product_id", "product_uom_qty", "quantity", "state"],
  })
 
  // `assigned` means the warehouse has physically committed these units.
  // Anything else is a partial or an unfulfillable line — a business outcome,
  // not an exception to swallow.
  const short = moves.filter((m) => m.state !== "assigned")
  return short.length === 0
    ? { ok: true as const }
    : { ok: false as const, short }
}

The interesting part of that function is the return type. A shortfall is not an error — it is one of the outcomes the checkout is designed to produce, and it needs a screen. Most of the storefronts we are asked to fix have no such screen, which is why the shortfall ends up as a 500, a retry, and in the worst case a second order.

Which brings up the prerequisite: that call needs an idempotency key, because a checkout POST that times out will be retried by the browser, the customer, or your own client, and the failure mode here is not a duplicate row in a log — it is a second reservation against stock that only existed once. This is one more argument for the order write living behind a single typed API layer instead of in three storefronts: one place owns the key, so one place can recognise a replay. If your order endpoint is not idempotent, nothing else in this post will save you.

Where the payment goes in the sequence

This is where EU payment reality intrudes, and where a lot of otherwise sensible designs break.

The textbook answer is: authorise the payment, reserve the stock, capture on success, void the authorisation if the reservation fails. It is a good sequence and it works beautifully with cards.

It does not work with most of what European customers actually pay with. Bancontact, iDEAL and SEPA flows are not authorise-then-capture; the money moves, and your options afterwards are a refund rather than a void. A refund is not a void: it costs a fee, it takes days, and it generates the exact "why was I charged" e-mail you were trying to avoid.

So with those methods the sequence has to invert. Take a short-lived hold before you hand the customer to the payment provider, and let the provider's webhook confirm the order against a reservation you are already holding:

1. Customer clicks pay. You take an internal hold on the lines, with an expiry in minutes.
2. You redirect, and the customer disappears into a banking app.
3. The webhook arrives. You confirm the order against the hold, which is still yours.
4. The webhook does not arrive, or arrives as a failure. The hold expires on its own and the stock comes back.

Step four is the one that has to be a sweeper rather than a cleanup in the failure handler, because the most common way a payment ends is that the customer closes the tab and nobody tells you anything.

A cart is not a reservation — unless you say so, out loud

Holds are the piece people either skip entirely or apply far too enthusiastically. The question is not whether to hold stock; it is at which event, and for how long.

• No hold at all. The right default for a normal catalogue with reasonable depth. Reserve at order confirmation and accept that a 0.1% shortfall is cheaper than the machinery.
• Hold at checkout start, ten to fifteen minutes. The sweet spot for anything scarce. Short enough that abandonment costs you little, long enough to cover a bank app and a lost card reader.
• Hold on add-to-cart. Only for genuinely allocated inventory — ticketing, made-to-measure, a drop of forty. Say so in the interface, with the countdown visible, because an invisible hold is just a confusing error later.

Whatever you pick: a hold without an expiry is inventory you deleted. Every hold needs a TTL and something that reaps them, and the reaper needs monitoring, because a stuck sweeper looks exactly like a product that mysteriously stopped selling.

We keep holds in our own Postgres rather than in the ERP's reservation mechanism, and that is a deliberate split. ERP reservations are warehouse state — they affect picking lists, and nobody in operations wants four thousand abandoned carts showing up as commitments against their morning. Holds are storefront state with a lifetime measured in minutes. Postgres handles this without another moving part:

CREATE TABLE stock_hold (
  id            bigserial PRIMARY KEY,
  product_id    integer     NOT NULL,
  quantity      numeric     NOT NULL CHECK (quantity > 0),
  checkout_id   uuid        NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  expires_at    timestamptz NOT NULL
);
 
-- Only live holds are ever summed, so that is the only thing worth indexing.
CREATE INDEX stock_hold_live
  ON stock_hold (product_id) INCLUDE (quantity)
  WHERE expires_at > now();
 
-- Available to promise, computed where the decision is made. The row lock
-- serialises two checkouts racing for the same last unit; without it both
-- read the same free_qty and both win.
WITH held AS (
  SELECT coalesce(sum(quantity), 0) AS qty
  FROM stock_hold
  WHERE product_id = $1 AND expires_at > now()
)
INSERT INTO stock_hold (product_id, quantity, checkout_id, expires_at)
SELECT $1, $2, $3, now() + interval '12 minutes'
FROM product_availability a, held
WHERE a.product_id = $1
  AND a.free_qty - a.buffer - held.qty >= $2
RETURNING id;

An empty result from that insert is the answer "no", arriving before the payment page rather than after it. That is the entire point of the exercise.

One caveat on the partial index: now() is not immutable, so an index predicate using it is not something Postgres can use for every plan shape the way a constant would. On hold tables this is fine — they are small by construction, because the sweeper keeps them small — but do not copy the pattern onto a table with a decade of history in it.

Push the invalidation, and reconcile anyway

With a hold mechanism in place, the catalogue number stops being load-bearing, which is exactly the point at which it becomes easy to keep fresh. Two mechanisms, and you want both.

Events, for latency. An automation rule in Odoo on stock changes, posting to a webhook on our side, which invalidates the affected SKU. Where the storefront and the ERP share a database boundary we can reach, LISTEN/NOTIFY does the same job with less plumbing. Either way the band updates in seconds and you are no longer waiting for a cron.

A full sweep, for correctness. Nightly, compare every published availability against the ERP's free_qty, and alert on drift beyond a threshold rather than on any drift at all — a handful of SKUs disagreeing by one unit is normal; four hundred disagreeing in the same direction means an event pipeline died on Tuesday and nobody noticed.

That second job is unglamorous and it is the only thing that catches the worst version of this failure: not a race between two shoppers, but a shop that has been selling from a snapshot for a week. We have walked into that twice. Both times the event delivery had broken silently, both times the oversell rate was an order of magnitude higher than any concurrency effect could explain, and both times a nightly diff would have caught it on day one. It belongs in the same monthly conversation as the restore drill, for the same reason: it is a check on a promise that is invisible until the day it is not kept.

When it happens anyway, the decision is a business rule

You will still oversell occasionally. A warehouse count will be wrong, or two channels will commit the same unit inside the same second. What separates a bad morning from a bad week is whether the allocation decision was made in advance or is being improvised by whoever opens the support inbox.

Decide three things before the next drop, and implement them where the order is confirmed:

• Who gets the stock. First paid is the default and the only one that needs no explanation. By customer value is defensible for B2B. Pro-rata across all orders is right for allocated goods and wrong for everything else.
• What happens to the rest. Odoo already models backorders, so "ship what we have, follow with the remainder" is a configuration rather than a project — but it is only acceptable if the checkout said so, per line, before payment. Otherwise the customer agreed to one delivery and is getting two.
• Who tells the customer, and how fast. An automated message within minutes, from the transactional platform rather than the marketing one, with a concrete option in it — refund now, or wait eleven days — outperforms a human apology the next afternoon by a distance. The e-mail is part of the feature, not the aftermath.

Then measure it. Oversell rate per SKU per month is a product metric, not an engineering one, and it belongs on the same dashboard as the rest of the commercial reporting so that the buffer conversation happens with numbers in it. Every client who started tracking it found the problem concentrated in a dozen SKUs, and fixed those twelve rather than the architecture.

Where we would skip most of this

Not every shop needs a reservation path, and building one where it is not warranted is its own kind of mistake.

Skip it when stock is deep and velocity is low — three hundred units of a line selling twenty a week will never produce a race, and a buffer with a nightly sync is genuinely sufficient. Skip it for made-to-order, print-on-demand and services, where availability is capacity and the question is a scheduling one. Skip it when the supplier owns the stock and you are drop-shipping: you cannot reserve what is not yours, and the honest interface is a lead time rather than a quantity.

The signal that you do need it is narrow and easy to recognise: scarce lines, concurrent demand, and a customer who notices. Limited runs, allocated B2B inventory, anything announced to a list at a specific time. That is where the forty units went, and it is the only situation where the engineering pays for itself in a single incident.

The short version

Overselling is not a bug in a component. It is a missing step in a flow, and the step is a write.

• Read free_qty, not qty_available, with the warehouse in context, and keep virtual_available out of the storefront entirely.
• Publish a band in the catalogue and an exact number only at the cart and checkout. A number you cannot keep fresh is a promise you cannot keep.
• Subtract a per-SKU buffer, sized from your own counting drift, and store it where the warehouse can change it.
• Reserve at the commitment. Confirm the order, check the move states, and treat a shortfall as a screen rather than a 500. Make the endpoint idempotent first.
• Hold before the redirect for payment methods that cannot be voided, with a TTL and a monitored sweeper. Holds live in your database, not in the warehouse's picking list.
• Invalidate on events, reconcile on a schedule, and alert on drift rather than on difference.
• Write the allocation and communication rules down before you need them, and track oversell rate per SKU like the commercial metric it is.

The version of this story that goes well looks almost identical from the outside: the mailing list goes out at 20:00, the line sells out in eleven minutes, and forty-one people get a page saying the last unit went while they were deciding. One of them is mildly annoyed. Nobody is refunded, nobody writes a review about it, and the client never learns the word "reservation" — which is roughly the definition of the thing working.

We build the storefronts and the applications that sit in front of these systems, and we implement and maintain the Odoo behind them — which is how we learned that the most expensive number on a webshop is the one that looked precise.