Inventory management system database design: the schema, and the one column that ruins it

Search for a database design for an inventory management system and you will find the same ER diagram over and over: a products table with a quantity column, a categories table beside it, maybe suppliers and orders, and an arrow between them. It is on every tutorial site, in most university coursework, and it is what a coding agent will produce if you ask it for an inventory schema without saying more.

That schema is wrong, and it is wrong in one specific place. products.quantity is a number somebody can type into. The moment it exists, your database can no longer answer the only question inventory software is ever really asked: the shelf says nine and the system says twelve — what happened?

This is the schema we actually build, why each table looks the way it does, and how to migrate to it if you already have the quantity column. It is PostgreSQL, but nothing here depends on Postgres; the same shape works in MySQL, SQLite or SQL Server with the obvious substitutions.

The rule the whole schema is built around

On-hand stock is derived, never stored. Every change in quantity is an append-only row in a movement ledger, and the current level is the sum of those rows.

That single decision determines almost everything else in the design. It is also the capability that appears most often in the market: we maintain a hand-counted inventory of what retail stock systems actually ship, tallied across 120 retail-only sources, and on-hand stock derived by item and location — rather than held as an editable field — is evidenced in 32 of them, more than any other capability we counted. The movement ledger that makes it possible is named explicitly in 12.

If you take nothing else from this page: the ledger is the schema. The rest is bookkeeping around it.

The item master, and what it must not contain

create table items (
  id             bigserial primary key,
  sku            text        not null unique,
  name           text        not null,
  category_id    bigint      references categories(id),
  unit           text        not null default 'ea',
  cost_cents     integer,
  price_cents    integer,
  reorder_point  integer,
  is_active      boolean     not null default true,
  created_at     timestamptz not null default now()
);

There is no quantity column, and that omission is the entire design. Everything else is negotiable.

Two details worth arguing about while you are here. Store money as integer minor units (cost_cents), not float — floating-point rounding in a stock valuation report is a bug you will find during an audit rather than during development. And prefer is_active over deleting items: a deleted SKU orphans every historical movement that referenced it, and you will want those movements in three years when somebody asks about a supplier dispute.

If you sell size-and-colour retail you need variants, and the honest version is that a variant is an item. Give it its own row and its own SKU, with a parent_item_id pointing at the style. A separate variants table with its own quantity column reintroduces the bug you just removed, one level down.

Locations

create table locations (
  id    bigserial primary key,
  code  text not null unique,
  name  text not null,
  kind  text not null default 'store'
        check (kind in ('store', 'stockroom', 'transit'))
);

Add this on day one even if you have exactly one shop. Retrofitting a location dimension into a ledger that does not have one means rewriting every balance query and backfilling every historical row, and single-location systems grow a second location far more often than they get rewritten.

The transit kind matters later — see transfers.

The movement ledger

This is the table the tutorials leave out.

create table stock_movements (
  id           bigserial   primary key,
  item_id      bigint      not null references items(id),
  location_id  bigint      not null references locations(id),
  qty          integer     not null check (qty <> 0),
  reason       text        not null
               check (reason in ('opening','receipt','sale','adjustment',
                                 'count','transfer','return','damage','shrinkage')),
  ref_type     text,
  ref_id       bigint,
  note         text,
  actor_id     bigint      not null references users(id),
  occurred_at  timestamptz not null default now()
);

create index on stock_movements (item_id, location_id);
create index on stock_movements (occurred_at);
create index on stock_movements (ref_type, ref_id);

Signed quantities: positive for stock in, negative for stock out. check (qty <> 0) because a zero-quantity movement is always a bug and it is cheaper to reject it than to explain it.

reason is constrained rather than free text. The temptation is to allow anything so the UI can send whatever it likes; resist it, because the day someone asks "how much did we lose to damage last quarter" you need damage to mean one thing. A lookup table works equally well and is easier to extend without a migration — the point is that the set is closed.

ref_type and ref_id are the loose pointer back to whatever caused the movement: a receipt, a count session, a transfer. A polymorphic reference is not beautiful, but the alternative is nine nullable foreign-key columns, and this table is going to be your largest.

actor_id is not optional. A movement without an actor is an anonymous change to your stock figure, which is precisely the thing you built the ledger to prevent.

Making it actually immutable

A ledger you can UPDATE is not a ledger, it is a table with good intentions. Enforce it in the database, not in the application layer, because the application layer is where the admin screen lives and the admin screen is what will edit it:

create function stock_movements_append_only() returns trigger
language plpgsql as $$
begin
  raise exception 'stock_movements is append-only (attempted %)', tg_op;
end $$;

create trigger stock_movements_immutable
  before update or delete on stock_movements
  for each row execute function stock_movements_append_only();

Corrections are new rows with the opposite sign and a reason, exactly as an accounting system reverses an entry. Never an edit. This is the constraint that survives a change of developer.

On-hand

create view stock_on_hand as
select item_id, location_id, sum(qty)::int as qty
from stock_movements
group by item_id, location_id;

That view is your stock level. Every screen, every report and every reorder rule reads it.

It is also fast enough for far longer than people expect — a few million movement rows aggregate comfortably on a modern database with the index above. When it eventually is not fast enough, the answer is a cache, and the important part of a cache is that you can prove it right:

create table stock_balances (
  item_id      bigint not null references items(id),
  location_id  bigint not null references locations(id),
  qty          integer not null,
  primary key (item_id, location_id)
);

Update it from a trigger on insert into stock_movements, and keep this query as a scheduled reconciliation:

select b.item_id, b.location_id, b.qty as cached, v.qty as ledger
from stock_balances b
full join stock_on_hand v using (item_id, location_id)
where b.qty is distinct from v.qty;

If that returns rows, the cache is wrong and the ledger is right. Always. Do not add the cache before you need it, and never let the cache become the source of truth — that is the original bug wearing a hat.

Purchase orders, which are a state machine

Where most schemas go wrong the second time is treating a purchase order as a document rather than a process. In the market inventory we counted, the full PO lifecycle — draft through sent, partial receipt, and close — is evidenced in 24 of 120 sources, joint second only to the item master itself. Receiving against those POs is evidenced in 18.

create table purchase_orders (
  id           bigserial   primary key,
  number       text        not null unique,
  supplier_id  bigint      not null references suppliers(id),
  location_id  bigint      not null references locations(id),
  status       text        not null default 'draft'
               check (status in ('draft','sent','partially_received',
                                 'received','cancelled')),
  ordered_at   timestamptz,
  closed_at    timestamptz,
  created_by   bigint      not null references users(id)
);

create table purchase_order_lines (
  id               bigserial primary key,
  po_id            bigint    not null references purchase_orders(id) on delete cascade,
  item_id          bigint    not null references items(id),
  qty_ordered      integer   not null check (qty_ordered > 0),
  unit_cost_cents  integer   not null,
  unique (po_id, item_id)
);

Note what is not on purchase_order_lines: a qty_received column. It is the quantity bug again in a different table. Deliveries arrive in parts, short, substituted and occasionally twice, and a single mutable counter cannot represent any of that.

Receipts are their own events:

create table goods_receipts (
  id           bigserial   primary key,
  po_id        bigint      references purchase_orders(id),
  location_id  bigint      not null references locations(id),
  received_at  timestamptz not null default now(),
  actor_id     bigint      not null references users(id),
  note         text
);

create table goods_receipt_lines (
  id            bigserial primary key,
  receipt_id    bigint    not null references goods_receipts(id) on delete cascade,
  po_line_id    bigint    references purchase_order_lines(id),
  item_id       bigint    not null references items(id),
  qty_received  integer   not null check (qty_received > 0)
);

Received-to-date is a sum over goods_receipt_lines, the same way on-hand is a sum over movements. po_id is nullable because ad-hoc receiving — a delivery nobody raised an order for — is a real event in every shop, and a schema that cannot record it forces someone to invent a fake PO.

Committing a receipt writes one stock_movements row per line, with reason = 'receipt' and ref_type = 'goods_receipt'. The PO status is recalculated from the sums, not set by hand.

Stocktakes, which are a session

A stocktake is not a form that overwrites quantities. It is a session with a lifecycle, and it is evidenced in 23 of the 120 sources we counted.

create table count_sessions (
  id            bigserial   primary key,
  location_id   bigint      not null references locations(id),
  status        text        not null default 'open'
                check (status in ('open','review','committed','cancelled')),
  scope         text        not null default 'full'
                check (scope in ('full','cycle')),
  opened_at     timestamptz not null default now(),
  committed_at  timestamptz,
  actor_id      bigint      not null references users(id)
);

create table count_lines (
  id            bigserial primary key,
  session_id    bigint    not null references count_sessions(id) on delete cascade,
  item_id       bigint    not null references items(id),
  expected_qty  integer   not null,
  counted_qty   integer,
  unique (session_id, item_id)
);

expected_qty is a snapshot taken when the session opens, and it is the detail almost every implementation misses. Variance has to be measured against what the system believed at the moment counting began. If you compute it live, a sale rung up while somebody is walking the stockroom changes the variance under their feet, and the count stops meaning anything.

Committing the session writes one movement per non-zero variance with reason = 'count' and a reference to the session, then sets status = 'committed'. After that the session is read-only — enforce it with the same trigger pattern as the ledger. That lock is what makes a stocktake auditable rather than decorative.

Transfers, and where stock lives while it is on a van

The naive design is a transfers table with a status column and a pair of adjustments at either end. It leaves stock existing nowhere in between, so your total stock value drops for two days and nobody can say why.

Use the transit location instead. A dispatch is two movements — out of the source, into transit. A receipt at the far end is two more — out of transit, into the destination:

-- dispatch 10 units from MAIN
insert into stock_movements (item_id, location_id, qty, reason, ref_type, ref_id, actor_id)
values (:item, :main, -10, 'transfer', 'transfer', :transfer_id, :actor),
       (:item, :transit, 10, 'transfer', 'transfer', :transfer_id, :actor);

The ledger stays balanced, stock in transit is a query rather than a special case, and a shortfall on arrival is visible as a residual balance in the transit location instead of vanishing. Transfers appear in 15 of the 120 sources — genuinely common, and genuinely optional for a first version.

The constraints that are load-bearing

• No table other than stock_movements may write a quantity. If you enforce one thing at review time, enforce this.
• Every status column is a check constraint or a lookup table. Free-text status is how you end up with sent, Sent and SENT in the same column.
• Every quantity has a sign convention written down, and it is the same everywhere.
• unique (session_id, item_id) on count lines, and unique (po_id, item_id) on PO lines. Both stop the duplicate-row class of bug that produces double-counted stock.
• Foreign keys are real foreign keys, not application-level conventions. An orphaned movement is unresolvable after the fact.

Migrating a schema that already has products.quantity

If you already shipped the quantity column, this is the path. It is not hard; the last step is the one people skip.

1. Create stock_movements and locations. Nothing reads them yet.
2. Backfill one opening movement per item — reason = 'opening', quantity equal to the current value, dated to now, actor set to the migration. You are not recovering history you never had; you are establishing a floor from which history begins.
3. Add the stock_on_hand view. Confirm it matches the old column exactly for every item. Any mismatch here is a backfill bug, not a rounding issue.
4. Route every write through the ledger. Receiving, adjustments, counts, sales. This is the bulk of the work and it is where you will find the three code paths nobody remembered.
5. Switch reads to the view.
6. Drop products.quantity. Do it. While the column exists, something will write to it — a report, an import script, an admin screen, a well-meaning new developer — and you will be back where you started with the added confusion of two disagreeing numbers.

Between steps 5 and 6, run the reconciliation query above on a schedule. It is the cheapest bug detector you will ever write.

Why coding agents get this wrong

Ask an agent for an inventory database and it produces the tutorial schema, because the tutorial schema is what the training data is full of. It is not a reasoning failure. It is a specification failure: nothing in "build an inventory management system" says on-hand must be derived, so the model builds the version it has seen ten thousand times.

We have watched this directly. Eight working retail stock systems were built from a single planner by four different coding models, and the pattern in the audits was consistent — the visible features arrived, and the substrate was where the differences were. Two of the eight stored everything in flat files rather than a database at all. One could not be served as a single application in production. One shipped with no sign-in whatsoever, and another let you pick a user from a list. The build that scored highest on data integrity was the one whose on-hand figure could only move through a receipt, an adjustment, a count or a return, each recorded with the before and after values and the person responsible.

That is the whole difference, and it is a schema decision made before the first line of application code.

---

If you want the sequence rather than the schema, how to build a retail inventory management system covers the build order and why it matters. What a retail inventory management system includes is the full counted feature inventory these figures come from, and inventory management in Excel is the same ledger rule expressed as three spreadsheet tabs, if you are not ready for a database yet.

And if you are pointing a coding agent at this, the Retail Inventory Management planner is the spec we hand ours — the ledger rule, the lifecycle states and the scope boundaries written so the agent has to build them rather than skip to the tutorial schema.