Design an order and inventory management system

The key facts

• The requirement that shapes everything: one record — stock, orders, purchasing — with quantities derived from an append-only movement history.
• The architecture: a transactional database core, a stateful order service, thin clients (browser, portal, scanner) over an API.
• The design order: requirements and invariants first, data model second, interfaces last — screens designed before the model are rework.
• The companion pieces: the LLD walkthrough takes this design to code level, and the schema reasoning details the tables.
• Where it fits: this page is one step of the order management walkthrough.

Requirements before architecture

The actors: staff who receive, pick and count; customers who order; suppliers who deliver; the accountant who consumes finished invoices. The invariants — the statements that must never be false: stock on hand equals the sum of movements; an order cannot ship more than its allocation; a cancelled reservation returns its stock; every movement has an actor and a reason. The volumes: orders per day, SKUs, concurrent users — modest numbers in most businesses, but they decide the concurrency design early.

The requirements discipline that pays: write the invariants down and treat them as testable. "The record is always true" is not an invariant; "on-hand for variant X at location Y equals the signed sum of its movements, at every committed transaction" is — and it is checkable by a test that runs forever.

The architecture, drawn simply

The core is a transactional database holding products, variants, locations, movements, customers, orders and purchase orders — with the movement table append-only and quantities derived, never stored editable. Around it, a service layer owns the rules: the order state machine (received → allocated → picked → packed → invoiced → paid, with cancellation legal until invoicing), the reservation logic (allocate without consuming; deduct at invoice approval), and the purchasing signal (reorder levels against live stock raising draft purchase orders).

The clients are thin: a browser UI for staff, a customer portal for trade buyers, scanner flows for the floor — all reading and writing through the same API, because a second path to the record is a second way to be wrong. The seams are explicit: invoices push outward to the accounting system with payment status mirroring back; storefront orders import by webhook. Nothing external writes to the record except through the validated API — the schema that makes this work is the design's centre of gravity.

The decisions to make early

Derivation over storage. Quantities derived from movements, even at the cost of an aggregate-maintenance mechanism later — the audit trail and trust are worth it from day one.

Deliberate approval. Stock deducts when a human approves the invoice, not when the order is typed. Reservations hold the promise in between; the cancel path releases them atomically. This single choice removes the worst class of inventory bugs.

Exceptions as designed paths. Partial receipts, short picks, backorders and cancellations are states in the model, not workarounds in a comments field. Design them on paper while it is cheap.

One API, many clients. Every consumer — staff, portal, scanner, integration — shares the validation. The moment a spreadsheet writes directly to the database, the design has grown a hole.

With the architecture set, the work descends into detail — the LLD companion takes it to tables and endpoints, and the honest alternative remains on the table: these decisions are exactly what a maintained system has already survived, and a trial tests the bought version of this design on your own products before a line of code is written.