Inventory system architecture: the decisions that matter

Situation: Updating a quantity column in place cannot explain itself and loses data on races and bugs.

Choice: Every quantity change is a movement row; balances are a cache updated in the same transaction and proved nightly.

• Rejected: Quantity column updated by code: simple, unauditable.
• Rejected: Event sourcing in a separate store: more infrastructure than needed.
• Consequence: Reconciliation and recall tracing are simple.
• Consequence: Posting does more writes than a counter update.

Situation: Paying on invoice alone is the most common source of duplicate and fraudulent payments.

Choice: Supplier invoices are matched to PO and receipt lines inside tolerances; only matched or approved-override invoices flow to AP.

• Rejected: Two-way match: misses short deliveries.
• Rejected: Manual review of every invoice: does not scale.
• Consequence: Tolerances become configuration.
• Consequence: A review queue for exceptions must be staffed.

Situation: Concurrent storefront orders can oversell the last units.

Choice: Reserve with a single conditional UPDATE guarded by on-hand minus reserved, inside the order transaction, with expiry for abandoned reservations.

• Rejected: Read then write: races.
• Rejected: Queue every reservation: adds latency without extra safety.
• Consequence: Hot items serialise on one row, acceptable at retail volumes.
• Consequence: Reservation expiry job is required.

Indexes are chosen from the questions the app asks most often. Each line below names a table and the question its main index answers.

• warehouses: unique (org_id, code) — warehouse code unique per tenant.
• locations: unique (warehouse_id, code) — bin code unique per warehouse.
• items: unique (org_id, sku) — SKU unique per tenant.
• stock_moves: unique (org_id, idempotency_key) — a retried scan or API call posts once.
• stock_balances: unique (item_id, lot_id, location_id) — one balance row per item, lot and bin.
• suppliers: (org_id, name) — supplier lookup and duplicate detection.
• purchase_orders: unique (org_id, number, revision) — PO number and revision unique.
• goods_receipts: (purchase_order_id) — receipts of an order.

Forge64 matches your description to one of 25 reference architectures, writes the plan and data model into the project, generates the app, and runs a quality gate over the code before you see it. Each app gets its own Postgres database and sign-in with no per-user fee. Hosting on Forge Cloud and Forge Payments are rolling out.

This is a reference design for the category, not a promise that every item is generated today. Built-ins such as background jobs, file uploads and search are rolling out; email sent from your app is coming soon, so anything here that needs it is something you add yourself for now.

What is the most important architecture decision for an inventory system?

Stock as an insert-only movement ledger with cached balances. Every quantity change is a movement row; balances are a cache updated in the same transaction and proved nightly.

Do I have to make these decisions myself?

No. Forge AI plans them from your description and writes the decisions into the project as architecture records you can read and change.

What do the alternatives cost me?

For example, for "Three-way match gates payment release": Two-way match: misses short deliveries. Manual review of every invoice: does not scale.

• How to build an inventory system
• Example prompt for an inventory system
• Inventory system security checklist
• Inventory system scaling checklist
• Often combined: online store
• Often combined: accounting system
• All app types