Ecommerce database design: schema, SQL and ER diagram

A sound ecommerce database design has four groups of tables and one rule. The catalog is products and product_variants, where the variant (the SKU) is the thing you actually sell. The cart is carts and cart_items, a scratchpad that holds no prices. The order is orders, order_lines and order_addresses. Money and stock live in two ledgers, payments and stock_movements, with stock_reservations holding what checkout has promised. The rule: an order is a snapshot, not a set of pointers. At checkout the price, title, SKU, email and shipping address are copied onto the order, and nothing that happens to the catalog or the customer's address book afterwards can change what was sold. Below is the full ecommerce database schema, the reasoning behind each table, and a PostgreSQL file you can run.

The tables at a glance

Users, staff roles, discounts, tax rules, shipments and returns are referenced in the prose below but left out of the runnable file. They attach to this core without changing it.

Download the ecommerce database schema and ER diagram

Download the PostgreSQL schema and sample orders (.sql) · Download the ER diagram (.svg)

Run the SQL once in an empty practice database with PostgreSQL 16 or later:

psql -v ON_ERROR_STOP=1 -d your_practice_database -f ecommerce-example.sql

It creates an ecommerce_example schema with sixteen tables, two views, the guard triggers and an idempotent place_order function, then plays out a short story: a retried checkout, a price rise, a shopper refused stock that is already promised, an unpaid order cancelled, a webhook delivered twice and a refund larger than the charge. The final query returns ORD-1001 | delivered | 12500 | 12500 | 2400 and ORD-1002 | cancelled | 5700 | 0 | 0. Running the file again stops at the existing schema rather than overwriting anything.

The file covers the order path, not a whole store: catalog, cart, checkout, order, payment and stock for a single location. Before production you still need authentication, staff permissions, a real tax calculation and a payment-provider integration. Run the app as a restricted database role, because table owners and superusers can disable the triggers that make the ledgers append-only.

How to design an ecommerce database, step by step

1. Write down the questions the store must answer. What did this customer pay, and where did it ship? How much money arrived this week, net of refunds? How many of this SKU can we still sell? Each is a question about history, and history is what a naive schema overwrites.
2. Separate the catalog from the transaction. Products, variants and prices are things you edit every week. Orders and payments are records of something that happened. The first group is mutable. The second is written once.
3. Pick the sellable unit. In a store with sizes or colours that is the variant, not the product. Decide it before anything else references either.
4. Decide what gets copied at checkout. Price, title, SKU, email and address, at minimum. Anything a customer or an accountant could later ask "what did it say at the time?" about belongs on the order.
5. Make money and stock ledgers. A payment is a row the provider told you about. A stock change is a row with a reason. Balances are sums.
6. Write the order state machine down as data, with the allowed transitions in a table and a trigger that refuses anything else.
7. Make checkout idempotent and concurrent-safe. Assume the request will be sent twice and that two people will want the last unit at the same moment.
8. Test with a story, not with fixtures. Place an order, change the price, retry the checkout, pay twice, refund, cancel. Then check the balances by hand, as the downloadable file does.

Products and variants: the SKU is the thing you sell

The ER diagram most tutorials show for an ecommerce database has a products table with price and stock columns, and colours and sizes as a comma-separated field or a separate table nobody references. It works until the first product comes in two sizes. Then "Oxford shirt, navy, M" has its own price, its own stock and its own barcode, and none of them fit on the product row.

The product is what a shopper browses. The variant is what goes in a cart, what an order line records and what a warehouse counts. In the research behind our online store planner, variants with their own SKU, price and stock were the single most common capability: present in 49 of 90 ecommerce sources we counted (platform documentation, open-source store software, store scripts and live admin demos).

create table products (
  id          bigint generated always as identity primary key,
  slug        text not null unique,
  title       text not null,
  description text,
  status      text not null default 'draft'
              check (status in ('draft','active','archived')),
  attributes  jsonb not null default '{}'
              check (jsonb_typeof(attributes) = 'object'),
  created_at  timestamptz not null default now()
);
create index products_attributes_idx on products using gin (attributes);

create table product_variants (
  id                bigint generated always as identity primary key,
  product_id        bigint  not null references products(id),
  sku               text    not null unique,
  option_values     jsonb   not null default '{}'
                    check (jsonb_typeof(option_values) = 'object'),
  price_cents       integer not null check (price_cents >= 0),
  compare_at_cents  integer check (compare_at_cents > price_cents),
  is_active         boolean not null default true,
  unique (product_id, option_values)
);

Three decisions are buried in that block.

Every product gets at least one variant, even when it has no options. A tote bag with no sizes still gets a variant row with option_values = '{}'. That way carts, orders and stock reference one table, always, and there is no if product.has_variants branch anywhere in the code.

Price lives on the variant, as integer cents. Floating-point money produces totals that are off by a cent in a way nobody notices until an accountant does. compare_at_cents (the struck-through "was" price) has a check constraint because a "was" price lower than the real one is a bug, not a promotion.

Option values are a JSON object with a unique constraint. {"colour": "navy", "size": "M"} stored as jsonb compares as equal regardless of key order, so unique (product_id, option_values) stops two rows from claiming to be the same navy M shirt. That is the most common variant bug in practice, and the one a separate variant_options table cannot stop without a trigger.

Product attributes: columns, EAV or JSON

Product attributes (material, fit, wattage, ISBN) are the oldest argument in ecommerce database design. There are three answers.

Our default: real columns for what every product has (title, status), jsonb for what varies by product type, and a promoted column the moment one attribute starts driving business logic rather than display. Filtering by attribute stays one indexed query:

select slug, title from products
where status = 'active' and attributes @> '{"material": "cotton"}';

Carts are not orders

A cart is a shopper's intention, and it is allowed to be wrong: the price may change, an item may sell out, the shopper may leave for a week. An order is an agreement. Keeping them in separate tables is what lets the cart be cheap and the order be strict.

create table carts (
  id           bigint generated always as identity primary key,
  token        uuid   not null unique default gen_random_uuid(),
  customer_id  bigint references customers(id),
  status       text   not null default 'open'
               check (status in ('open','converted','abandoned')),
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);

create table cart_items (
  cart_id     bigint  not null references carts(id) on delete cascade,
  variant_id  bigint  not null references product_variants(id),
  qty         integer not null check (qty > 0),
  added_at    timestamptz not null default now(),
  primary key (cart_id, variant_id)
);

There is no price on cart_items. The cart shows today's price, every time it renders, until checkout freezes it. Storing a price in the cart creates a third copy of the price (catalog, cart, order) and the question of which one wins when they disagree. If you want to tell a shopper "the price changed since you added this", store price_seen_cents as a display hint and never charge from it.

The primary key (cart_id, variant_id) means adding the same SKU twice increments one row rather than creating two, and customer_id is nullable because guest checkout is real. A guest cart is found by its token in a cookie. When a guest signs in, merge their cart into the account's open cart.

A separate cart table also keeps the abandoned-cart report simple, and keeps carts out of every order report without a filter someone will eventually forget.

The order is a snapshot

Ecommerce order database design is where most schemas go wrong, and it is where the one rule comes from.

The tutorial order_items table has order_id, product_id and quantity. To show an order, it joins to products for the price and title. That means editing a product's price rewrites the revenue on every historical order that contains it, renaming a product renames it on every invoice already sent, and deleting a product breaks every order that ever sold it. The customer's address has the same problem: point the order at the address book and the parcel that went to their old flat now appears to have gone to their new one.

create sequence order_number_seq start 1001;

create table orders (
  id               bigint generated always as identity primary key,
  number           text    not null unique
                   default 'ORD-' || nextval('order_number_seq'),
  cart_id          bigint  unique references carts(id),
  idempotency_key  text    not null unique,
  customer_id      bigint  references customers(id),  -- null = guest checkout
  email            text    not null,
  status           text    not null default 'pending'
                   check (status in ('pending','paid','shipped','delivered','cancelled')),
  currency         char(3) not null default 'USD',
  subtotal_cents   integer not null check (subtotal_cents >= 0),
  discount_cents   integer not null default 0 check (discount_cents >= 0),
  shipping_cents   integer not null default 0 check (shipping_cents >= 0),
  tax_cents        integer not null default 0 check (tax_cents >= 0),
  total_cents      integer not null,
  placed_at        timestamptz not null default now(),
  check (discount_cents <= subtotal_cents),
  check (total_cents = subtotal_cents - discount_cents + shipping_cents + tax_cents)
);

create table order_lines (
  id                bigint generated always as identity primary key,
  order_id          bigint  not null references orders(id),
  variant_id        bigint  not null references product_variants(id),
  sku               text    not null,
  title             text    not null,
  unit_price_cents  integer not null check (unit_price_cents >= 0),
  qty               integer not null check (qty > 0),
  line_total_cents  integer generated always as (unit_price_cents * qty) stored,
  unique (order_id, variant_id)
);

create table order_addresses (
  order_id     bigint  not null references orders(id),
  kind         text    not null check (kind in ('shipping','billing')),
  name         text    not null,
  line1        text    not null,
  line2        text,
  city         text    not null,
  region       text,
  postal_code  text    not null,
  country      char(2) not null,
  primary key (order_id, kind)
);

order_lines.variant_id is still a foreign key, for reporting ("units sold per SKU"). But nothing displayed or charged reads through it. sku, title and unit_price_cents are copies taken at the moment of checkout, and in the downloadable file the table is append-only, so the copy cannot be edited later either. Archive variants with is_active = false rather than deleting them, so the reporting foreign key never dangles.

order_addresses is the same idea applied to people. The address is copied from the address book (or typed by a guest) into the order's own row. In the sample run, Ada changes her address book after ordering, and the order still says 12 Harbour Street, which is where the parcel went. email is copied onto orders for the same reason: receipts go to the address that placed the order, and guest orders have no customer row to look it up on.

The money columns carry the last check: total = subtotal − discount + shipping + tax, enforced by the database. Discounts and tax are calculated by application code (or a tax service) at checkout, and the results are stored. Never recompute a historical order's tax from today's rates. If you need the breakdown per line, add discount_cents and tax_cents to order_lines too. The rule is the same: store the number that was charged.

The order state machine

An order's status is not a free-text field. It is a small state machine, and the bugs live in the transitions nobody listed: shipping a cancelled order, cancelling a delivered one, or marking an order paid because a button was clicked rather than because money arrived.

Put the allowed transitions in a table and let a trigger refuse everything else:

create table order_status_transitions (
  from_status  text not null,
  to_status    text not null,
  primary key (from_status, to_status)
);
insert into order_status_transitions values
  ('pending','paid'), ('pending','cancelled'),
  ('paid','shipped'), ('paid','cancelled'),
  ('shipped','delivered');

create function order_guard() returns trigger
language plpgsql as $$
begin
  if to_jsonb(new) - 'status' is distinct from to_jsonb(old) - 'status' then
    raise exception 'order %: only status may change after placement', old.number;
  end if;
  if new.status = old.status then
    return new;
  end if;
  if not exists (select 1 from order_status_transitions
                 where from_status = old.status and to_status = new.status) then
    raise exception 'order %: % -> % is not an allowed transition',
      old.number, old.status, new.status;
  end if;
  if new.status = 'paid' and (select paid_cents - refunded_cents from order_balances
                              where order_id = new.id) < new.total_cents then
    raise exception 'order % is not fully paid', old.number;
  end if;
  return new;
end $$;

create trigger orders_guard before update on orders
  for each row execute function order_guard();

That trigger does three jobs. It freezes everything on the order except status, which is the snapshot rule enforced rather than hoped for. It rejects any transition not in the table: in the sample run, jumping a paid order straight to delivered fails with paid -> delivered is not an allowed transition. And it refuses paid unless the payments ledger says the money is there.

The transitions live in a table, not in application code, so the admin screen, the webhook handler and the import script are all held to the same rule. Each change also writes an order_events row with the actor and time, which becomes the order timeline in the admin.

Two variations come up quickly. Cash on delivery ships before it is paid. Add ('pending','shipped') only for orders whose payment method is cash on delivery, and check that in the same trigger. Partial shipments mean "shipped" stops being a property of the whole order. At that point, fulfilment becomes its own shipments and shipment_lines tables, exactly as money became its own ledger, and the order status is derived from them.

Payments and refunds are a ledger

The tutorial version is orders.payment_status = 'paid' and maybe orders.refunded_amount. Both break on the first partial refund, the first declined retry, the first webhook delivered twice, and the first time someone asks how much money actually arrived last Tuesday.

A payment is something the payment provider told you happened. Record each one as a row and never edit it:

create table payments (
  id            bigint generated always as identity primary key,
  order_id      bigint  not null references orders(id),
  kind          text    not null check (kind in ('charge','refund')),
  status        text    not null check (status in ('succeeded','failed')),
  amount_cents  integer not null check (amount_cents > 0),
  provider      text    not null,
  provider_ref  text    not null,
  created_at    timestamptz not null default now(),
  unique (provider, provider_ref)
);

create view order_balances as
select o.id as order_id, o.number, o.status, o.total_cents,
       coalesce(sum(p.amount_cents) filter (where p.kind = 'charge'
                and p.status = 'succeeded'), 0)::int as paid_cents,
       coalesce(sum(p.amount_cents) filter (where p.kind = 'refund'
                and p.status = 'succeeded'), 0)::int as refunded_cents
from orders o
left join payments p on p.order_id = o.id
group by o.id;

unique (provider, provider_ref) is the line that prevents double-counting. Payment providers retry webhooks until they get a success response, so the same confirmation can arrive more than once. The webhook handler inserts with on conflict do nothing, and a duplicate becomes a no-op:

insert into payments (order_id, kind, status, amount_cents, provider, provider_ref)
values (1, 'charge', 'succeeded', 12500, 'testpay', 'ch_1001')
on conflict (provider, provider_ref) do nothing;

Refunds are rows with kind = 'refund', not a subtraction from a stored figure. Partial refunds, multiple refunds and a refund followed by a re-charge all fall out of the same two sums. The downloadable file adds one more guard: a trigger that locks the order and refuses any refund larger than what is still captured. In the sample run, a 200.00 refund against an order with 101.00 left is rejected.

Zero and negative amounts are rejected by check (amount_cents > 0), and the direction of money comes from kind, not from a sign. That keeps "how much did we refund?" a plain sum. If your provider authorises first and captures later, add authorization and capture kinds, and count only captures as paid.

Returns, refunds and cancellations appear in 33 of the 90 sources in our research. The research marks them as common but high-risk, and one of the named failures it records is a return implemented as a single status dropdown.

Stock: on-hand, reserved and available

Stock is a ledger for the same reason money is. Every change is a signed row in stock_movements with a reason, and on-hand is the sum. Our inventory database design article covers that ledger in full (locations, receipts, stocktakes, transfers), so here is only the part ecommerce adds: the gap between what is on the shelf and what you can still sell.

Between checkout and shipment, stock is still physically on the shelf but already promised to a customer. If the storefront shows on-hand as available, two shoppers can buy the same last unit. If it decrements stock at add-to-cart, abandoned carts hide stock that is sitting on the shelf. The answer is a reservation, created when the order is placed and resolved exactly once:

create table stock_reservations (
  id           bigint generated always as identity primary key,
  order_id     bigint  not null references orders(id),
  variant_id   bigint  not null references product_variants(id),
  qty          integer not null check (qty > 0),
  status       text    not null default 'held'
               check (status in ('held','consumed','released')),
  created_at   timestamptz not null default now(),
  resolved_at  timestamptz,
  unique (order_id, variant_id)
);
create index stock_reservations_held_idx on stock_reservations (variant_id)
  where status = 'held';

create view variant_stock as
select v.id as variant_id, v.sku,
       coalesce(m.on_hand, 0)                         as on_hand,
       coalesce(r.reserved, 0)                        as reserved,
       coalesce(m.on_hand, 0) - coalesce(r.reserved, 0) as available
from product_variants v
left join (select variant_id, sum(qty)::int as on_hand
           from stock_movements group by variant_id) m on m.variant_id = v.id
left join (select variant_id, sum(qty)::int as reserved
           from stock_reservations where status = 'held'
           group by variant_id) r on r.variant_id = v.id;

The storefront reads available. The warehouse reads on_hand. Shipping an order consumes its reservations and writes the sale to the ledger. Cancelling releases them. Both happen in one trigger on the order's status, so no code path can ship without moving stock:

create function order_effects() returns trigger
language plpgsql as $$
begin
  if tg_op = 'INSERT' then
    insert into order_events (order_id, from_status, to_status)
    values (new.id, null, new.status);
    return null;
  end if;
  if new.status = old.status then
    return null;
  end if;
  insert into order_events (order_id, from_status, to_status)
  values (new.id, old.status, new.status);
  if new.status = 'shipped' then
    insert into stock_movements (variant_id, qty, reason, source_event)
    select variant_id, -qty, 'sale', 'reservation:' || id
    from stock_reservations where order_id = new.id and status = 'held';
    update stock_reservations set status = 'consumed', resolved_at = now()
    where order_id = new.id and status = 'held';
  elsif new.status = 'cancelled' then
    update stock_reservations set status = 'released', resolved_at = now()
    where order_id = new.id and status = 'held';
  end if;
  return null;
end $$;

create trigger orders_effects after insert or update of status on orders
  for each row execute function order_effects();

source_event = 'reservation:<id>' is unique in the ledger, so a reservation can never be turned into two sales. Our ecommerce inventory planner states the acceptance test in one sentence: an order reserves selected stock; shipment deducts it and cancellation releases it.

One job remains: an unpaid order holds stock until somebody cancels it. Run this on a schedule, with whatever payment window you allow:

update orders set status = 'cancelled'
where status = 'pending' and placed_at < now() - interval '30 minutes';

Reserving at order placement is not the only defensible policy. Some stores only take stock when the payment succeeds, which means a declined card never holds anything, at the cost of a small window in which a paid order can find the shelf empty. Either is fine if it is a decision. The two wrong answers are decrementing at add-to-cart and never checking at all. Selling across several channels, with buffers and channel allocations, is covered in ecommerce order management.

Checkout has to be idempotent

Checkout is the one write in a store that a customer will retry. They double-click, the mobile connection drops after the request was sent, the browser resubmits on back. Each retry that creates a new order is a duplicate charge waiting to happen.

The schema already has two guards: orders.idempotency_key is unique (the client generates one per checkout attempt), and orders.cart_id is unique (a cart becomes at most one order, whatever key arrives). The checkout itself is one function that runs in one transaction:

create function place_order(
  p_cart_id         bigint,
  p_idempotency_key text,
  p_email           text,
  p_ship_to         jsonb,
  p_shipping_cents  integer,
  p_tax_cents       integer
) returns bigint
language plpgsql as $$
declare
  v_order_id  bigint;
  v_cart_id   bigint;
  v_cart      carts%rowtype;
  v_subtotal  integer;
  v_short     record;
begin
  select id, cart_id into v_order_id, v_cart_id
  from orders where idempotency_key = p_idempotency_key;
  if found then
    if v_cart_id is distinct from p_cart_id then
      raise exception 'idempotency key % was used for another cart', p_idempotency_key;
    end if;
    return v_order_id;
  end if;

  select * into v_cart from carts where id = p_cart_id for update;
  if not found then
    raise exception 'cart % not found', p_cart_id;
  end if;
  if v_cart.status = 'converted' then
    return (select id from orders where cart_id = p_cart_id);
  end if;
  if v_cart.status <> 'open' then
    raise exception 'cart % is %', p_cart_id, v_cart.status;
  end if;
  if not exists (select 1 from cart_items where cart_id = p_cart_id) then
    raise exception 'cart % is empty', p_cart_id;
  end if;

  -- Lock the variants being bought, in id order, so two checkouts
  -- for the last unit queue up instead of both succeeding.
  perform 1 from product_variants v
  join cart_items ci on ci.variant_id = v.id
  where ci.cart_id = p_cart_id
  order by v.id
  for update of v;

  select v.sku, s.available, ci.qty, v.is_active, p.status as product_status
  into v_short
  from cart_items ci
  join product_variants v on v.id = ci.variant_id
  join products p on p.id = v.product_id
  join variant_stock s on s.variant_id = v.id
  where ci.cart_id = p_cart_id
    and (s.available < ci.qty or not v.is_active or p.status <> 'active')
  order by v.id
  limit 1;
  if found then
    raise exception 'cannot sell %: % available, % requested',
      v_short.sku, greatest(v_short.available, 0), v_short.qty;
  end if;

  select sum(v.price_cents * ci.qty)::int into v_subtotal
  from cart_items ci join product_variants v on v.id = ci.variant_id
  where ci.cart_id = p_cart_id;

  insert into orders (cart_id, idempotency_key, customer_id, email,
                      subtotal_cents, shipping_cents, tax_cents, total_cents)
  values (p_cart_id, p_idempotency_key, v_cart.customer_id, p_email,
          v_subtotal, p_shipping_cents, p_tax_cents,
          v_subtotal + p_shipping_cents + p_tax_cents)
  returning id into v_order_id;

  insert into order_lines (order_id, variant_id, sku, title, unit_price_cents, qty)
  select v_order_id, v.id, v.sku,
         p.title || coalesce(' (' || (select string_agg(value, ' / ' order by key)
                                      from jsonb_each_text(v.option_values)) || ')', ''),
         v.price_cents, ci.qty
  from cart_items ci
  join product_variants v on v.id = ci.variant_id
  join products p on p.id = v.product_id
  where ci.cart_id = p_cart_id;

  insert into order_addresses (order_id, kind, name, line1, line2, city,
                               region, postal_code, country)
  values (v_order_id, 'shipping', p_ship_to->>'name', p_ship_to->>'line1',
          p_ship_to->>'line2', p_ship_to->>'city', p_ship_to->>'region',
          p_ship_to->>'postal_code', p_ship_to->>'country');

  insert into stock_reservations (order_id, variant_id, qty)
  select v_order_id, variant_id, qty from cart_items where cart_id = p_cart_id;

  update carts set status = 'converted', updated_at = now() where id = p_cart_id;
  return v_order_id;
end $$;

A repeated key returns the existing order before anything else happens. The cart row is locked, so two submissions of the same cart queue up and the second finds it already converted. The variant rows are locked in a fixed order, so two carts competing for the last unit cannot both pass the availability check (we tested exactly that with two sessions: the second waits, then is refused), and the fixed order avoids deadlocks. Stock is checked against available, not on-hand. Then price, title and address are copied, stock is reserved and the cart is closed in one transaction. If anything fails, nothing is written.

The order is created before payment. For a hosted payment page, pass the order number to the provider as its reference, and let the webhook move the order from pending to paid. The checkout never marks anything paid itself.

The constraints that are load-bearing

• Nothing displayed or charged on an order reads through to the catalog. Price, title, SKU, email and address are copies. If you enforce one thing at review time, enforce this.
• Order lines, order addresses, payments and stock movements are append-only, enforced by triggers, not by convention. Corrections are new rows: a refund, a return movement, an adjustment.
• The total is a check constraint: total = subtotal − discount + shipping + tax. An order whose numbers do not add up cannot be inserted.
• Status changes go through a transitions table, and paid requires the payments ledger to agree.
• unique (provider, provider_ref) on payments and unique (idempotency_key) and unique (cart_id) on orders. These three lines are the whole of your duplicate-charge protection.
• unique (product_id, option_values) on variants and unique (order_id, variant_id) on order lines. Both stop the duplicate-row class of bug.
• Money is integer minor units, always with a currency on the order. Never float, and never a price without knowing which currency it is in.

Example queries

Every query reads the ledgers or the snapshots, never the live catalog price. All four run unchanged against the downloadable file.

How much money did we actually take, by day, net of refunds? This reads payments, not order totals, because an order total is what was agreed and a payment is what arrived:

select created_at::date as day,
       sum(amount_cents) filter (where kind = 'charge') as charged_cents,
       coalesce(sum(amount_cents) filter (where kind = 'refund'), 0) as refunded_cents,
       sum(case kind when 'charge' then amount_cents else -amount_cents end) as net_cents
from payments
where status = 'succeeded'
group by 1
order by 1;

Against the sample data: one day, 12500 charged, 2400 refunded, 10100 net.

Best-selling SKUs, at the prices they actually sold for:

select l.sku, sum(l.qty) as units, sum(l.line_total_cents) as revenue_cents
from order_lines l
join orders o on o.id = l.order_id
where o.status <> 'cancelled'
group by l.sku
order by revenue_cents desc;

OXF-NVY-M sold 2 units for 9600, although its current price is 5200. The order line says 4800, and that is what was charged. This is gross revenue; subtract refunds from the payments ledger for net.

Orders where the status and the money disagree. This is your daily reconciliation, and on a healthy store it returns nothing:

select number, status, total_cents, paid_cents, refunded_cents
from order_balances
where (status in ('paid','shipped','delivered') and paid_cents < total_cents)
   or (status = 'cancelled' and paid_cents > refunded_cents)
   or (status = 'pending' and paid_cents >= total_cents);

The three conditions catch the three real failures: goods shipped without the money, a cancelled order whose charge was never refunded, and a payment that arrived while the webhook failed to move the order on.

What can we still sell, and what is promised?

select v.sku, p.title, s.on_hand, s.reserved, s.available
from variant_stock s
join product_variants v on v.id = s.variant_id
join products p on p.id = v.product_id
where v.is_active and s.available <= 2
order by s.available, v.sku;

Against the sample data: OXF-NVY-M with 1 available and OXF-NVY-L with 2.

MySQL, MongoDB or PostgreSQL?

The design is relational because its guarantees are: foreign keys, unique and check constraints, and transactions that lock the rows they read. It ports to MySQL with syntax changes. jsonb becomes JSON, the PL/pgSQL triggers and function become MySQL triggers and a stored procedure (or application code inside a transaction), and the partial index becomes a plain index.

MongoDB is tempting because an order with its lines and copied address is naturally one document. The trouble is the guards that span documents, such as a refund that exceeds the charge or a second sale of the last unit. With no foreign keys, those checks move into application code, where every code path must remember them. Keep the snapshot rule and the ledgers either way.

Why coding agents get this wrong

Ask a coding agent for an ecommerce database and it produces the tutorial schema: products with price and stock columns, and order_items pointing back at products. Nothing in "build an online store" says that an order is a snapshot, that a webhook arrives twice, or that two people will want the last unit at once. It is a specification gap, not a reasoning failure.

The failure modes recorded in our online store research are exactly the ones this schema closes: variants that appear in the product page but are never saved into the cart or order, a stock field that exists but that order creation ignores, and checkout "passing" a test because an empty cart redirected somewhere plausible.

We also watched it directly. Three frontier coding models built the same fifteen-feature store from our planner, and all three took a real order end to end. What separated them sat below the storefront. Claude Fable 5's build checks the last unit inside the same transaction that takes the order, and its own hostile testing found that cancelled orders could still be shipped, a missing transition rule, which it fixed before release. GPT-5.6 Sol's build records a confirmed payment exactly once however many times the confirmation arrives. Grok 4.5's build only takes stock once payment succeeds. The full scores are on the online store build-off board, the story is in three models, one online store, and all three stores are live demos you can place an order in.

Every one of those differences is a schema decision made before the first screen exists.

---

If you sell physical stock from more than one place, inventory database design covers the movement ledger, locations, receipts and stocktakes that stock_movements stands in for here, and ecommerce order management covers what changes when orders arrive from more than one channel. Ecommerce website features is the counted feature list these tables serve.

And if you are pointing a coding agent at this, the Online Store planner is the spec we hand ours: variants that reach the order snapshot, stock rechecked at order creation, manual payment as a first-class path, and webhook, idempotency and refund gates whenever a real payment gateway is selected.