# Inventory Management System

> A concept project with a working demo: an inventory system with stock per warehouse, a movements ledger, purchase orders, low-stock alerts with CSV export and admin and staff roles. Oversells are blocked by a CHECK constraint and transfers commit or roll back as one transaction. A dev panel shows the request and SQL behind every action.

- Author: Sami Ullah (https://samiullah.tech/about)
- Type: Concept project
- Role: Full build
- Stack: TypeScript, React.js, Next.js, zod
- Year: 2026
- Updated: 2026-11-13
- Canonical: https://samiullah.tech/work/inventory-management

## 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](/lab/inventory-management) runs in your browser, but its API layer is written the way the [NestJS](/skills/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](/skills/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

```flow
# Every stock change
Client -> ValidationPipe (zod DTO) -> RolesGuard -> MovementsService -> BEGIN -> INSERT stock_movements -> UPSERT stock_levels -> COMMIT -> 201
UPSERT stock_levels -> CHECK qty >= 0 fails -> ROLLBACK -> 422 INSUFFICIENT_STOCK
```

```flow
# Purchase order lifecycle
draft -> send (admin) -> sent -> receive -> received + receipt movements
draft -> cancel (admin) -> cancelled
```

**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:

```sql
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:

```sql
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`:

```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.

```ts
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](/skills/nestjs) modules for products, movements, purchase orders and reports; [PostgreSQL](/skills/postgresql) with the CHECK constraint, row locks and transactions above; a [Next.js](/skills/nextjs) 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](/lab/inventory-management). 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.