# Building a Stock System That Cannot Be Wrong

## The Problem Every Retailer Knows

If you've ever managed inventory for a retail business, you know the nightmare: the system says you have 5 units, but you can only find 3 on the shelf. Or worse, you sell something the system said was in stock, only to discover it was already gone.

Traditional inventory systems store stock as a **single number in a database column** — and every operation just increments or decrements that number. It works... until it doesn't. A failed API call, a race condition, a developer forgetting to update the count in one edge case — and suddenly your stock is off. Forever. And you have no idea *when* or *why* it went wrong.

I built Lensiro's stock system so that this class of bugs is **architecturally impossible**.

* * *

## The Core Idea: Never Store the Stock Count

Here's the fundamental insight: **the stock quantity is never stored anywhere.** There is no `quantity` column in the Stock table. Zero. None.

Instead, every time anyone asks "how many of item X do we have at branch Y?", the system **calculates it from scratch** by summing up every movement that has ever happened to that item.

```plaintext
Stock = SUM(all inflows) - SUM(all outflows)
```

That's it. That's the whole trick.

![Screenshot of the stock detail trail view showing a chronological ledger of all movements — purchases, sales, transfers, adjustments — with running totals](https://cdn.hashnode.com/uploads/covers/61f4e5bb4bea13573e62c7da/b0ab7d3b-fcbd-47f1-9543-5bf697786ef8.png align="center")

* * *

## The Ledger: Every Movement Tells a Story

Every stock change is recorded as an **immutable event** in its own dedicated table. There are 8 types of movements:

**Things that add stock (+):**

*   **Purchase Receive** — goods physically received from a supplier
    
*   **Stock Opname (positive)** — physical count reveals more stock than expected
    
*   **Transfer In** — items arriving from another branch
    

**Things that remove stock (-):**

*   **Sale** — items sold to customers
    
*   **Complimentary** — items given away
    
*   **Purchase Return** — items sent back to supplier
    
*   **Transfer Out** — items shipped to another branch
    
*   **Stock Opname (negative)** — physical count reveals less stock than expected
    

![Screenshot of the stock detail expanded view showing increase/decrease entries with dates, descriptions, and clickable links to source documents](https://cdn.hashnode.com/uploads/covers/61f4e5bb4bea13573e62c7da/d859fc92-82e8-47e4-8e7d-7ed621ca759e.png align="center")

Each movement record contains:

*   The count (how many)
    
*   The timestamp (when)
    
*   The cost price (for accounting)
    
*   A link back to its source document (the sale, the purchase order, the transfer)
    

These records are **append-only**. Once a purchase receive is recorded, it stays recorded. You don't go back and edit the number — if there's a correction, you create a *new* adjustment record. The history is sacred.

* * *

## This Was Not Easy to Implement

The idea is simple — just sum up all movements. The implementation? Not so much. Here's what made it hard and what I did to make it work.

### The Query From Hell

The stock count query unions **9 different tables** into a single CTE (Common Table Expression). Each sub-query has its own JOIN conditions, its own sign logic (positive for inflows, negative for outflows), and its own edge cases. The first version of this query was slow. Really slow. Loading the stock list page with a few thousand items could take seconds.

The fix was **pre-aggregating inside each UNION ALL branch.** Instead of dumping every individual row into the CTE and then aggregating at the end, each sub-query does its own `GROUP BY stockId` and `SUM(count)` first. This means the CTE combines 9 small, pre-summed result sets instead of potentially tens of thousands of raw rows. The difference was night and day.

I also split the query into layers using multiple CTEs:

1.  `movements` — unions and pre-aggregates all 9 movement sources
    
2.  `filtered_stock` — applies all the filter conditions (branch, category, brand, color, prescription, price range, name search) against the Stock and Item tables
    
3.  `stock_counts` — joins filtered stocks with their movements, applies HAVING clauses for min/max stock filters
    

This way the database can optimize each layer independently. Filters narrow down which stocks we care about *before* we join with movements, so we're not calculating counts for items nobody asked for.

### The Consistency Problem

If you're not careful, event-sourced stock calculation can show inconsistent numbers. Imagine:

*   The stock list page calculates stock using the **SQL CTE** on the server
    
*   A user clicks "Detail" on an item, which fetches all relations and calculates stock using **TypeScript** on the client (`calcStockPlus()` minus `calcStockMinus()`)
    
*   If the logic in SQL and TypeScript doesn't match *exactly*, the numbers disagree
    

This actually happened during development. I added a new movement type (`StockBuffer`) to the SQL query but forgot to add it to the TypeScript calculation. The stock list showed one number, the detail view showed another. Staff would understandably lose trust in the system.

The solution was discipline: **every time a new movement type is added, it must be added in three places** — the SQL CTE, the TypeScript `calcStock` functions, and the `generateTrail` function that renders the timeline. There's no clever abstraction that enforces this automatically. It's just a rule you follow.

### FIFO Cost Price Calculation

Getting the stock count right was only half the battle. For accounting, every outflow (sale, return, transfer) needs a **cost price** — how much did we pay for this specific unit? We use FIFO (First In, First Out): the oldest purchased units are assumed to be sold first.

The tricky part: what happens when you sell an item *before* the purchase is recorded? This happens in real retail — you receive goods on Monday, sell one on Tuesday, but the purchase order isn't entered until Wednesday. The outflow happened before the inflow exists in the system.

I handle this with **retroactive cost pricing**. Outflows that happen before sufficient inflows are queued as "pending." When a new purchase is recorded, the system re-processes pending movements and assigns them the correct cost price. This runs asynchronously using `waitUntil()` after the main transaction commits, so it doesn't block the user.

### Batch Operations

Early on, creating stock records one at a time during bulk operations (like receiving a purchase order with 50 line items) was painfully slow — each one triggered a separate `findFirst` then `create` query. I replaced this with `fetchOrCreateStocksBatch()`, which finds all existing stocks in one query, identifies the missing ones, bulk-creates them with `createMany`, and fetches the new ones. What used to be 100+ queries became 3-4.

* * *

## Why "Cannot Be Wrong"?

When I say this system "cannot be wrong," I mean something very specific. The stock count is **mathematically derived** from the complete set of transactions. If the transactions are recorded correctly, the stock count is correct — by definition. There is no way for the count to "drift" out of sync, because there is no separate count to drift.

Here's what makes this different from a traditional `UPDATE stock SET quantity = quantity - 1` approach:

### 1\. No Stale State

There is no cached or stored quantity that can go stale. Every query recalculates from the source of truth. If you run the query right now and again in 5 minutes (with no new transactions), you get the exact same answer.

### 2\. Complete Auditability

Every single unit of stock can be traced back to *how* it got there. Got 12 units? You can see: 10 came from a purchase on January 3rd, 3 came from a transfer on January 15th, and 1 was sold on January 20th. 10 + 3 - 1 = 12. The math is right there on screen.

![Screenshot of the trail view showing the running total column, where each row adds or subtracts and you can visually verify the math](https://cdn.hashnode.com/uploads/covers/61f4e5bb4bea13573e62c7da/62d58518-a41c-4439-9a7f-e55fa355a7da.png align="center")

### 3\. Self-Correcting with Stock Opname

When staff does a physical stock take (we call it "Stock Opname"), they count what's actually on the shelf and enter it. The system compares: ledger says 12, physical count says 11. It creates an adjustment record of -1. Now the ledger says 11. The discrepancy is *recorded as an event itself* — not silently patched.

![Screenshot of the Stock Opname page showing the adjustment creation flow](https://cdn.hashnode.com/uploads/covers/61f4e5bb4bea13573e62c7da/ca462cbf-657d-4032-8b92-1d549746c838.png align="center")

### 4\. Atomic Transactions

Every operation that touches stock is wrapped in a database transaction. If you're recording a sale with 3 line items, either all 3 items get their movement records, or none of them do. No partial states.

### 5\. Branch Isolation

Each branch has its own stock ledger. Branch A's inventory is completely independent of Branch B's. When items move between branches, it creates a *pair* of records: a decrease at the source branch and an increase at the destination branch (only when the receiving branch confirms receipt).

![Screenshot of the stock list filtered by branch, showing branch selector and different stock counts per branch](https://cdn.hashnode.com/uploads/covers/61f4e5bb4bea13573e62c7da/67731eb0-8cde-40eb-b280-20c6a089df86.png align="center")

* * *

## The SQL Behind It

On the backend, a single SQL query with a Common Table Expression (CTE) unions all movement tables together and sums them up:

```sql
WITH movements AS (
  SELECT stockId, SUM(count) as value
  FROM PurchaseReceiveItem
  GROUP BY stockId

  UNION ALL

  SELECT stockId, SUM(count) as value
  FROM StockAdjustment
  GROUP BY stockId

  UNION ALL

  SELECT targetStockId, SUM(count) as value
  FROM StockMutationItem
  WHERE received = true
  GROUP BY targetStockId

  UNION ALL

  SELECT stockId, -SUM(count) as value
  FROM SaleItem
  GROUP BY stockId

  -- ... and so on for all movement types
)
SELECT stockId, COALESCE(SUM(value), 0) AS stockCount
FROM movements
GROUP BY stockId
```

On the client side, the same logic is mirrored in TypeScript — `calcStockPlus()` sums all inflows, `calcStockMinus()` sums all outflows, and `calcStock()` returns the difference. Both server and client use the same formula. There's nowhere for the numbers to disagree.

* * *

## The Trail View: Show Your Work

My favorite part of this system is the trail view. When a staff member clicks on any stock item, they see a full chronological timeline of every movement, each one clickable back to the source document.

![Screenshot of the expanded trail view for a single item, showing the full timeline with purchase receives, sales, and transfers in chronological order](https://cdn.hashnode.com/uploads/covers/61f4e5bb4bea13573e62c7da/d859fc92-82e8-47e4-8e7d-7ed621ca759e.png align="center")

It's like a bank statement for inventory. Every "deposit" and "withdrawal" is listed. The running total is visible. Anyone can verify the math by scrolling through the list.

This isn't just for debugging — it's a daily tool. When a staff member questions the count, they don't need to call IT. They click "Detail," read the trail, and understand exactly why the system says what it says.

* * *

## But Wait — It Can Still Be Bugged

I want to be honest here. When I say the system "cannot be wrong," I'm talking about the *architecture* — the structural impossibility of stock count drift. The system is designed so that **if all movements are recorded, the count is mathematically guaranteed to be correct.**

But software is software. Bugs can still happen:

*   **A movement could fail to be recorded.** If a purchase receive action crashes halfway through and the transaction doesn't roll back properly, a movement might be lost. The stock count would then be wrong — not because of drift, but because of missing data.
    
*   **A movement could be recorded with the wrong count.** If the UI sends `count: 10` instead of `count: 1` due to a frontend bug, the ledger faithfully records the wrong number. Garbage in, garbage out.
    
*   **A new movement type could be added without updating the calculation.** If a developer adds a 9th movement type but forgets to include it in the calculation functions or the SQL query, those movements would be invisible.
    
*   **Concurrent transactions could interact unexpectedly.** Although we use database transactions, extremely high concurrency on the same stock item could theoretically cause issues depending on the isolation level.
    
*   **The client and server calculations could diverge.** We mirror the logic in both SQL and TypeScript. If someone updates one but not the other, you'd see different numbers depending on where you look.
    

The architecture eliminates the *most common* class of inventory bugs — the silent drift that accumulates over months and is impossible to diagnose. But it doesn't make the software immune to all bugs. No architecture can.

What it *does* give you is **debuggability**. When something looks wrong, you can open the trail, read every movement, and find the problem. Compare that to a traditional system where the stock is just... a number. How did it get there? Who knows.

* * *

## The Buffer System: Handling In-Progress Sales

One more detail I'm proud of. In optical retail, there's often a gap between selling an item and physically handing it to the customer (because lenses need to be fitted into frames). During this gap, the item is "sold" but still physically in the store.

We handle this with a **buffer** concept. The stock count includes sold-but-unfulfilled items as decreases, but the UI shows a clear breakdown:

*   **Total Stock:** 5 (the ledger total)
    
*   **Buffer:** 2 (sold but not yet fulfilled)
    
*   **Available:** 3 (actually available to sell)
    

![Screenshot of the buffer stock indicator showing the yellow warning box with Total Stock, Buffer, and Available counts](https://cdn.hashnode.com/uploads/covers/61f4e5bb4bea13573e62c7da/6667e159-83ad-4d6a-996e-462fdcde2e6b.png align="center")

This prevents double-selling without hiding information from staff.

* * *

## Lessons Learned

1.  **Derived state > stored state.** If you can calculate it, don't store it. Stored state lies. Calculated state can't.
    
2.  **Make the audit trail a feature, not an afterthought.** When staff can see *why* the number is what it is, trust in the system goes up dramatically.
    
3.  **Event sourcing isn't just for distributed systems.** Even a simple retail app benefits from treating every state change as an immutable event.
    
4.  **The best debugging tool is transparency.** When something goes wrong, a complete trail of events is worth more than any amount of logging.
    

* * *

![Screenshot of the full Lensiro stock management page with filters, table, and a detail trail expanded — the whole system working together](https://cdn.hashnode.com/uploads/covers/61f4e5bb4bea13573e62c7da/fbc5c4b0-53ce-4edf-a250-e986b0d3b566.png align="center")

* * *

* * *

## About Lensiro

[Lensiro](https://lensiro.com/) is a complete retail management platform built specifically for optical stores. It handles everything an eyewear business needs in one place — from inventory and stock management across multiple branches, to point-of-sale, purchasing, member management, and accounting with double-entry bookkeeping. If you run an optical retail business and want a system that actually gets inventory right, check it out at [lensiro.com](https://lensiro.com/).

*Built with Next.js, PostgreSQL, Prisma, and a lot of stubbornness about doing inventory right.*