Why Do Ecommerce Backends Keep Overselling Stock?

Originally published on Medium - cross-posting here since I know a lot of you spend more time on dev.to. I'm a backend software engineer with 13 years of experience, most of it spent building and fixing ecommerce systems. This is the first in a series on modeling orders and inventory in a way that actually holds up in production.

This is aimed at backend engineers who've shipped a few CRUD APIs and are ready for the next layer - the tradeoffs that only show up under real concurrency and scale. If you're just starting out with databases, some of this will be a stretch; if you've already designed a production inventory system, most of this will be familiar (though I'd still love to hear how you did it differently).

Somewhere in the history of almost every ecommerce system, there is a version of the same incident: a flash sale or limited drop where more units sell than actually exist in stock. Customer support ends up fielding angry emails, and the postmortem traces back to the same root cause - an inventory table with a single quantity column, where under load, dozens of checkout requests read the same stale count, decrement it, and write it back. A classic lost-update race condition. No amount of "just add more servers" fixes a data model that is wrong at its core.

I have seen a version of this play out at nearly every ecommerce company I have worked with. Swap in your own war story and the shape is almost always the same: most overselling, refund chaos, and 2 AM pages do not come from bad code - they come from a data model that was never designed to survive concurrency, returns, or scale.

This article walks through the decisions that actually matter here - the ones tutorials tend to skip, because they are less about "how do I write this query" and more about "what happens when three of these queries run at once". If you take one thing away, let it be this: separate reservations from raw quantity, treat inventory as an append-only ledger instead of a mutable number, and model fulfillment per line item instead of per order. Everything below is why.

Why the Naive Model Falls Apart

Almost every team starts here:

• An orders table
• An order_items table
• An inventory table with a quantity column per SKU

It works fine in a demo. It works fine with ten orders a day. It quietly breaks the moment two things happen at once: real concurrency, and real-world messiness like returns, cancellations, and partial shipments.

The core problem is that this model treats inventory as a single mutable number instead of a series of events and commitments. A quantity column cannot tell you why it changed, who is holding a claim on it, or what to undo if an order fails halfway through checkout. You are one race condition away from selling something you do not have.

Want to actually build this instead of just reading about it?
That is the course I am putting together - real schemas, a working system, your own hands on the keyboard. Join the waitlist - free, in the works now, you will be the first to know.

The Entities that actually hold up in production

A model that survives contact with real traffic usually separates a few concepts that the naive version collapses into one.

Orders and line items are the customer-facing record of what was purchased. Honestly, this part is usually fine even in the naive model - it is not where the trouble starts.

The trouble starts with inventory reservations. A reservation is a temporary, time-boxed claim on stock, created the moment a customer starts checkout, not when payment succeeds. It is the piece most naive models are missing entirely. In my experience it is the single biggest fix for overselling - more impactful than any amount of locking or retry logic bolted on afterward.

The other piece is a stock ledger: an append-only log of every change to inventory - received, reserved, released, sold, returned - rather than a single number you overwrite. The current available quantity becomes a derived value, the sum of the ledger, instead of a source of truth you are racing to update every time an order comes in.

This separation sounds like extra complexity, but it actually removes complexity elsewhere: you stop needing defensive locking scattered across your codebase, because the ledger itself makes concurrent changes safe to reason about.

A minimal version of the reservation and ledger tables looks something like this - simplified on purpose to show the shape of the idea, not as a copy-paste production schema (a real version would add foreign keys, a unique constraint to prevent double-reserving the same cart, and indexes on sku_id and expires_at):

create table inventory_reservations (
  id uuid primary key,
  sku_id uuid not null,
  order_id uuid,
  quantity int not null,
  status text not null, -- 'active', 'committed', 'released'
  expires_at timestamptz not null,
  created_at timestamptz not null default now()
);

create table stock_ledger (
  id bigserial primary key,
  sku_id uuid not null,
  delta int not null, -- positive or negative
  reason text not null, -- 'received', 'reserved', 'released', 'sold', 'returned'
  reference_id uuid, -- order_id, reservation_id, etc.
  created_at timestamptz not null default now()
);

Available stock for a SKU is then sum(stock_ledger.delta) - sum(active reservations. quantity) - never a field you write to directly.

One honest caveat: summing the entire ledger on every read does not scale. It gets slower every day as the ledger grows. In practice, nobody computes it that way on the hot path. The ledger stays the source of truth for auditing and reconciliation. A fast, denormalized available_quantity counter, updated transactionally alongside each ledger insert, is what product pages and checkout actually read from. Some teams periodically checkpoint the running total so a full rebuild only sums deltas since the last checkpoint, not the entire history. Reservations stay cheap either way, since you are only ever summing active reservations - a small, indexed subset.

Concurrency-Safe Reservations at Checkout

Here is roughly how it plays out - and how it would have prevented the flash-sale scenario above:

1. Customer adds an item to cart and begins checkout.
2. The system creates a reservation for that quantity, with a short expiry (commonly 10-15 minutes).
3. Available stock is calculated as on_hand - active_reservations, not read from a single mutable field.
4. If payment succeeds, the reservation converts into a committed deduction on the ledger.
5. If payment fails or the reservation expires, the stock is released automatically - no manual cleanup, no orphaned holds.

The critical design choice is that reservations are first-class records, not just a decremented number. That gives you visibility (you can query "what is currently reserved and by whom"), safety (expiry handles abandoned checkouts), and auditability (you can always explain how a number got to where it is).

Depending on your database, enforcing this safely under load usually comes down to using row-level locking or conditional writes at the reservation step - the point is that the contention happens in one well-defined place, instead of being smeared across the codebase.

The Part Most Tutorials Skip: Returns and Partial Fulfillment

Almost every ecommerce data-modeling tutorial stops at "customer buys item, stock goes down". Real ecommerce systems have to handle:

• A customer returning 1 of 3 items from an order
• A warehouse only having 2 of 3 items in stock, requiring a partial shipment
• A cancellation after payment but before fulfillment
• A return that should restock inventory, but only after inspection

If your model represents an order as a single flat status field (pending, shipped, delivered), none of this fits. What actually works is modeling fulfillment at the line-item level, with its own state machine per item - not per order. An order becomes a container for line items that can each be in different states simultaneously. This one change is usually what separates a model that handles real operations from one that only handles the happy path.

Event Sourcing vs. Mutable State: When It is Worth It

You do not need full event sourcing to get most of these benefits - an append-only ledger for inventory changes, layered on top of otherwise normal mutable tables for orders, gets you 80% of the value with a fraction of the complexity.

Full event sourcing (where the entire system state is derived from an event log) is worth reaching for when you need strong audit requirements, complex reconciliation with external systems (like warehouse or payment providers), or the ability to replay and debug exactly how state evolved. For most teams, that is overkill for orders, but it is genuinely useful specifically for inventory, where "how did we end up with this number" is a question you will get asked - usually by finance, usually under pressure.

One scope note: everything above assumes orders and inventory live close together - one service, one database, one transaction boundary. That is a reasonable starting point, and it is still where a lot of real systems live. But if inventory is split into its own service (increasingly the default in headless/microservices ecommerce setups), reserving stock stops being a single-database transaction and becomes a distributed one - with its own failure modes around what happens when one service confirms a reservation and the other never finds out. That is a big enough topic to deserve its own post rather than a paragraph here.

Where This Leaves You

None of this is exotic. It is a handful of deliberate decisions - separating reservations from raw quantity, treating inventory as a ledger instead of a number, modeling fulfillment per line item - that most teams only discover the hard way, usually after their own version of that Friday-night flash sale.

This post is the overview. I have been writing follow-ups that go deeper on each piece on its own - reservation design and concurrency edge cases, ledger schema patterns, returns and partial fulfillment - since each one has more nuance than fits here.

This is one of the patterns I keep coming back to when I talk to other engineers - a small set of decisions that quietly determine whether a system holds up in production or falls over under real-world conditions. I am putting together a short, focused course on this exact problem: how to design an order and inventory data model that actually survives concurrency, returns, and scale, with real schemas and walkthroughs rather than abstract theory. If that is useful to you, drop a comment with your own worst inventory-bug story, or join the waitlist - free, in the works now, you will be the first to know.