Inventory Management System

Overview#

Every small shop or warehouse asks the same three questions each morning: what do we have, where is it, and what is running out. Spreadsheets answer them until two people edit the same cell, or a transfer between sites is written down at one end and not the other.

This concept is the system I would build for that business. The live demo runs in your browser, but its API layer is written the way the NestJS service would be: DTO validation, a roles guard, services that run in a transaction, and an exception filter that turns database errors into HTTP responses. A dev panel lists every request with the SQL it would send to PostgreSQL, including BEGIN, COMMIT and ROLLBACK.

My role#

All of it: the data model, the API design, the transaction rules, the demo interface and this write-up. It is a concept project, not client work. The warehouses, products, suppliers and users are fictional.

The problem#

Inventory software fails in quiet ways. Nothing crashes; the numbers just stop being true.

• A number with no history. If stock is a column people edit, nobody can say why it is 14 and not 20.
• Overselling. Two sales of the last five units can both pass a check done in application code.
• Half-finished transfers. Moving 20 units from North DC to City Store is two writes. If the process dies between them, 20 units vanish.
• Double receiving. A purchase order received twice, from a double click or a retried request, doubles the stock.
• Permissions. Warehouse staff need to record movements, but should not change prices or delete products.

Approach & architecture#

A ledger, not a counter. Stock only changes through a row in stock_movements, which records the product, the warehouse, a signed quantity, the reason and the user. stock_levels holds the current number per product and warehouse so reads stay cheap, and both are written in the same transaction. The rule that makes this trustworthy is that the level always equals the sum of its movements, and the demo has a button that checks it:

SELECT l.product_id, l.warehouse_id, l.qty AS level, coalesce(sum(m.qty), 0) AS ledger
FROM stock_levels l
LEFT JOIN stock_movements m USING (product_id, warehouse_id)
GROUP BY 1, 2, 3
HAVING l.qty <> coalesce(sum(m.qty), 0);

The database says no. The schema puts the business rule where a race cannot get past it:

CREATE TABLE stock_levels (
  product_id   integer REFERENCES products(id),
  warehouse_id text    REFERENCES warehouses(id),
  qty          integer NOT NULL,
  PRIMARY KEY (product_id, warehouse_id),
  CONSTRAINT stock_levels_qty_nonneg CHECK (qty >= 0)
);

An oversell makes PostgreSQL raise 23514. The exception filter maps that one constraint to a clear 422, and because the ledger row was inserted in the same transaction, it disappears with the rollback. In the demo the same mapping lives in api.ts:

if (e.code === "23514" && e.constraint === "stock_levels_qty_nonneg") {
  return { status: 422, body: { statusCode: 422, error: "Unprocessable Entity", code: "INSUFFICIENT_STOCK",
    message: "Not enough stock in that warehouse. Nothing was written." } };
}

Transfers are one transaction. The service locks both level rows in a fixed order, debits the source, writes a transfer_out movement, credits the destination and writes transfer_in. Locking by warehouse id, not by "from then to", means two opposite transfers cannot deadlock. The demo has a "simulate failure halfway" switch that throws after the debit. The dev panel shows ROLLBACK, and the before and after table shows both warehouses unchanged.

bumpLevel(tx, p.id, dto.fromWarehouseId, -dto.qty);
insertMovement(tx, { productId: p.id, warehouseId: dto.fromWarehouseId, kind: "transfer_out", qty: -dto.qty, reason, ref });
if (dto.simulateFailure) throw new PgError("57P01", "terminating connection (simulated failure after the first half)");
bumpLevel(tx, p.id, dto.toWarehouseId, dto.qty);
insertMovement(tx, { productId: p.id, warehouseId: dto.toWarehouseId, kind: "transfer_in", qty: dto.qty, reason, ref });

In the demo a transaction works on a structuredClone of the state; a throw returns the original object untouched. In the production design it is dataSource.transaction() on one connection.

State changes are conditional updates. Receiving runs UPDATE purchase_orders SET status = 'received' WHERE id = $1 AND status = 'sent'. If zero rows change, someone already received it, and the request is a 409 before any receipt movement is written. That makes the endpoint safe to retry without a separate idempotency table.

Roles at the guard. Admins create products, edit prices, send and cancel purchase orders. Staff record movements, run transfers, draft and receive orders. The demo's role switch shows a staff price edit coming back as 403 Forbidden resource with no SQL at all, because the guard runs before the transaction starts.

Key challenges#

• Keeping the ledger and the level in step. It is easy to add a "quick fix" endpoint that updates a level directly. The demo has a deliberately bad button that does exactly that, and the reconciliation check then reports the mismatch. In production only the movements service would hold UPDATE rights on stock_levels.
• Mapping database errors to useful messages. A raw 23514 means nothing to a warehouse clerk. Mapping by constraint name keeps the database as the authority and still gives the user a sentence they can act on.
• Validation messages that point at the field. The zod DTOs return one message per issue, in the same array shape NestJS's ValidationPipe uses, so the forms list each problem by field.
• Deleting products that have history. The ledger references products, so delete is a soft archive, and it is refused with a 409 while the product still has stock on hand.
• A CSV that opens everywhere. The low-stock export escapes quotes and commas, uses CRLF line endings and starts with a byte-order mark so Excel reads the encoding correctly.

Tech stack#

• Demo: TypeScript, React and Next.js, with zod for the DTOs. The domain code lives in two framework-free modules, db.ts (tables and seed data) and api.ts (controllers, guard, pipes, transactions), so it reads like the server it stands in for. State is kept in localStorage, and every call has a short simulated delay so loading states are visible.
• Production design: NestJS modules for products, movements, purchase orders and reports; PostgreSQL with the CHECK constraint, row locks and transactions above; a Next.js admin UI with server-rendered tables.

Results#

• A working demo covering products, three warehouses, a ledger of about 260 seeded movements, purchase orders through their full lifecycle, low-stock alerts with CSV export and two roles.
• Oversells, failed transfers and double receives all end with nothing written, and the dev panel shows the ROLLBACK or 409 that proves it.
• The reconciliation check confirms that every stock level equals the sum of its movements, and catches the one deliberately broken code path.
• The code under "View source" on the demo page is the code that runs.

I have not load-tested it or measured anything beyond what the demo shows. It is a concept build without real users.

Try the demo#

Open the inventory management demo. Things to try:

1. On the Stock tab, press Fill an oversell and submit. You get 422 INSUFFICIENT_STOCK, and the dev panel shows the insert, the failed upsert and ROLLBACK.
2. On the Transfer tab, run the transfer with "simulate failure halfway" ticked, then untick it and run it again.
3. Receive PO-1001, then press receive again. The second press is a 409 and the stock does not change.
4. Switch to the staff user and try to edit a price.

What I would do next#

• Move api.ts behind real NestJS controllers with integration tests against a PostgreSQL test database, including two parallel transfers to prove the lock order.
• Add stock valuation (FIFO or weighted average cost) from receipt prices.
• Add reserved stock for open orders, so "available" and "on hand" can differ.
• Write a nightly reconciliation job that alerts if any level drifts from its ledger.