Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 37 ยท Projects and Practice

Project: Inventory Ledger

Build an inventory service that never oversells: a movements ledger, atomic conditional updates, idempotency keys and a reconciliation query.

Inventory is where concurrency bugs cost money. Two customers buy the last item, a client retries a request after a timeout and the stock is reduced twice, or a refund runs half way. In this project you will build the core of an inventory service that handles all three: a conditional atomic update prevents overselling, an idempotency key makes retries harmless, and an append-only ledger lets you prove the numbers are right.

Stand-in. The service is written on node:sqlite so it runs in the lesson runner. The SQL is real; with TypeORM you would run the same statements through manager.query, createQueryBuilder().update() or repo.decrement, and with Sequelize through Model.update and Model.decrement. Notes at the end map each part.

The design

Two tables carry the idea:

  • stock(sku, qty): the current quantity, a fast read model.
  • movements(id, sku, delta, reason, idempotency_key): every change ever made, never edited or deleted.

At any moment stock.qty must equal the sum of movements.delta for that SKU. Writes touch both inside one transaction. A unique idempotency_key means the same request cannot be applied twice.

sequenceDiagram
  participant Client
  participant Service
  participant DB
  Client->>Service: reserve pen x2 (key abc)
  Service->>DB: BEGIN
  Service->>DB: insert movement (key abc) fails if seen before
  Service->>DB: update stock where qty is enough
  Service->>DB: COMMIT
  Service-->>Client: ok
  Client->>Service: retry (key abc)
  Service-->>Client: ok, nothing applied twice

The unique key turns a retry into a no-op.

Step 1: the schema and the service

Node.js Reserve stock safely

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Look at what protects the data:

  • update ... where qty + ? >= 0 checks and changes in one statement, so two concurrent orders cannot both pass the check (see Locking and Isolation Levels). The check (qty >= 0) constraint is a second line of defense.
  • The ledger insert is first. If the key is repeated, the unique index stops the request before stock moves.
  • A rejected order rolls back its ledger row too, so the ledger only contains changes that really happened.

Step 2: reconcile the books

A ledger is only useful if you check it. This query finds every SKU where the current quantity disagrees with the sum of its movements. In a healthy system it returns no rows.

Node.js Reconciliation report

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The repair adds a movement rather than rewriting the ledger. History stays honest, and the correction is itself visible and explainable. Here the lamp ledger said 3 while the shelf count said 4, and we treated the shelf count as the truth, so the correcting movement is +1. If your investigation shows the ledger was right, you would instead adjust the stock row and record why.

Decide which side is the truth before you auto-repair. A drift is a symptom; find the code path that wrote only one of the two tables.

Step 3: a concurrency stress test

JavaScript runs your code in one thread, but a real service has many processes and requests in flight. This simulation fires interleaved reservations through asynchronous steps to show why the conditional update matters, by comparing it with the naive read-then-write version.

Node.js Naive versus conditional update

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Six buyers, three pens. The naive version lets all six read "3" before anyone writes, so it reports six sales while the stock ends at 2: the books and the shelf disagree. The conditional update sells exactly three.

Mapping to the libraries

Idea TypeORM Sequelize
Conditional update manager.createQueryBuilder().update(Stock).set({ qty: () => 'qty + :d' }).where('sku = :sku AND qty + :d >= 0') and check affected Stock.update({ qty: sequelize.literal('qty + 1') }, { where: ... }) and check the affected count
Idempotency key @Index({ unique: true }) @Column({ nullable: true }) and catch QueryFailedError unique: true and catch UniqueConstraintError
Atomic pair of writes dataSource.transaction(...) sequelize.transaction(...)
Reconciliation manager.query(sql) or a query builder with groupBy and having sequelize.query(sql, { type: QueryTypes.SELECT })

Extensions

  1. Add reservations that expire: a reserved quantity released by a scheduled job.
  2. Add a GET /stock/:sku/history endpoint with keyset pagination over movements.
  3. Run the reconciliation as a test after every integration test (Testing Queries with node:test).
  4. Retry on lock errors with the helper from Deadlocks and Retries; the idempotency key makes the retry safe.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the idempotency key protect against?

    The unique key makes a repeated request fail the insert and be ignored.
  2. 2. Why is UPDATE ... WHERE qty >= 1 safer than reading qty and then writing qty - 1?

    No other request can slip between the check and the write.
  3. 3. Why is the ledger append-only?

    Editing history destroys the audit trail; a correcting movement explains itself.
  4. 4. What should a reconciliation query return in a healthy system?

    It lists only SKUs whose stock disagrees with the ledger, so a healthy system has none.

Summary

  • Combine a current-state table with an append-only ledger and write both in one transaction.
  • Use conditional atomic updates and constraints to prevent overselling.
  • Idempotency keys make retries safe.
  • Reconcile regularly and fix drift with new entries, after finding the cause.

Next: Practice Exercises