ERP Inventory Module Design: Movements, Valuation, Guards

Every ERP I have seen fail at inventory failed the same way: someone stored on-hand quantity as a single mutable number on the item table, updated it from six different code paths, and within three months the number on screen disagreed with the number on the shelf. When I built the AP/AR and stock-touching flows for ANCoraPRO, a production ERP for a real Indonesian client running on React, NestJS, and PostgreSQL, the first architectural decision was the opposite: quantity is never stored as truth, it is always derived.

This post covers the three design decisions that make or break an inventory module: modeling stock movements as an immutable ledger, picking a valuation method you can actually defend to an accountant, and building negative stock guards that survive concurrent writes. These are the parts you cannot bolt on later, because every other module — purchasing, sales, accounting — builds on top of them.

Stock Movements Are a Ledger, Not a Counter

The core insight is borrowed straight from double-entry accounting: you never edit a balance, you append a transaction. Every receipt, issue, transfer, and adjustment becomes one immutable row in a stock_movement table with a signed quantity. On-hand stock for any item in any warehouse is simply the SUM of its movements. Deleting or editing a movement is forbidden; corrections are new compensating movements, which gives you a free audit trail.

Each movement carries a reference_type and reference_id pointing back to the business document that caused it — a purchase order line, a sales delivery, a stock opname. This is what lets you answer the question warehouse teams actually ask: not just how many do we have, but why does the system say we have 40 when I count 38. With a ledger, you trace the discrepancy to a specific document in minutes.

-- The core table: every stock change is one immutable row
CREATE TABLE stock_movement (
  id              BIGSERIAL PRIMARY KEY,
  item_id         BIGINT NOT NULL REFERENCES item(id),
  warehouse_id    BIGINT NOT NULL REFERENCES warehouse(id),
  movement_type   TEXT NOT NULL,  -- RECEIPT | ISSUE | TRANSFER_IN |
                                  -- TRANSFER_OUT | ADJUSTMENT
  quantity        NUMERIC(18,4) NOT NULL,  -- signed: +in, -out
  unit_cost       NUMERIC(18,4),           -- filled at receipt
  reference_type  TEXT NOT NULL,  -- PO | SO | TRANSFER | OPNAME
  reference_id    BIGINT NOT NULL,
  moved_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by      BIGINT NOT NULL REFERENCES app_user(id)
);

-- On-hand quantity is ALWAYS derived, never stored as truth:
SELECT item_id, warehouse_id, SUM(quantity) AS on_hand
FROM stock_movement
GROUP BY item_id, warehouse_id;

Yes, SUM over millions of rows gets slow eventually. The answer is not to abandon the ledger — it is periodic snapshot rows plus summing only movements after the snapshot, exactly how banks handle account balances. PostgreSQL handles this pattern comfortably into the hundreds of millions of rows before you need anything exotic.

Choosing a Valuation Method: FIFO vs Average Cost

Valuation answers a different question than quantity: what is this stock worth, and what cost do we book when it leaves. The method you pick changes your COGS, your margins, and your tax position, so this is a decision to make with the client's accountant, not alone at your keyboard. The three methods that matter in practice:

Microsoft's Business Central design docs are honest about the trade-offs: FIFO inflates balance sheet value when prices rise, average cost smooths volatility, and back-dated movements force recalculation of every affected entry afterwards. That last point is the one that bites custom builds — if you allow back-dated receipts, your AVCO implementation must replay the average forward from that date, or your books silently drift.

For most Indonesian SMB clients I default to average cost. Purchase prices in IDR are volatile enough that FIFO layer tracking adds real complexity, while AVCO gives the accountant one defensible number per item. I implement FIFO only when the business genuinely rotates stock that way and the finance team asks for it. Odoo's own documentation calls FIFO the most accurate but highly sensitive to input data and human error — an honest description of what you are signing up to maintain.

Negative Stock Guards That Hold Under Concurrency

Negative stock is the inventory module's classic data-quality disease. Two warehouse operators issue the last 5 units at the same moment, both reads see on-hand 5, both writes succeed, and now the system says minus 5. Every report downstream — valuation, COGS, reorder suggestions — is now quietly wrong.

My approach in NestJS with PostgreSQL: wrap the read and the write in one transaction, take a row-level lock on the movements for that item and warehouse with SELECT FOR UPDATE, validate the balance, then append. The lock forces concurrent issues of the same item to queue up and re-read a true balance:

// NestJS service: issue stock with a row-level lock guard
async issueStock(dto: IssueStockDto) {
  return this.dataSource.transaction(async (em) => {
    // 1. Lock the item+warehouse balance row (serialize writers)
    const balance = await em.query(
      `SELECT COALESCE(SUM(quantity), 0) AS on_hand
         FROM stock_movement
        WHERE item_id = $1 AND warehouse_id = $2
        FOR UPDATE OF stock_movement`,
      [dto.itemId, dto.warehouseId],
    );

    // 2. The guard: reject, do not "fix later"
    if (Number(balance[0].on_hand) < dto.quantity) {
      throw new ConflictException(
        'Insufficient stock: on hand ' + balance[0].on_hand,
      );
    }

    // 3. Append the movement row
    await em.getRepository(StockMovement).insert({
      itemId: dto.itemId,
      warehouseId: dto.warehouseId,
      movementType: 'ISSUE',
      quantity: -dto.quantity,
      referenceType: dto.referenceType,
      referenceId: dto.referenceId,
      createdBy: dto.userId,
    });
  });
}

One nuance worth building in: a per-warehouse allow_negative_stock flag. Some clients genuinely need to ship now and fix paperwork later — a hard block stops the business, so they will work around your system in Excel, which is worse. The compromise that works: allow it per warehouse, flag every negative-stock movement for mandatory review, and show the offending list on the inventory dashboard until someone clears it.

Reserved, Available, and the Four Quantities That Matter

The second most common design miss: treating on-hand as the only quantity. The moment you support sales orders that are confirmed but not yet shipped, or purchase orders in transit, one number is not enough. You need four, and the UI must be explicit about which one it is showing:

Keep reservations out of the movement ledger. A reservation is an intention, not a physical event — mixing the two makes your valuation wrong and your audit trail lie. When the goods physically leave, the reservation is consumed and a real ISSUE movement is appended in the same transaction.

Design Checklist Before You Write the First Migration

These are the questions I now force myself to answer before coding any inventory module, in roughly the order they will hurt if skipped:

• Is every stock change an immutable movement row with a reference to its business document? Can on-hand always be rebuilt by replaying the ledger?
• Which valuation method did the client's accountant sign off on — and is it written down? Changing methods later is an accounting event, not a refactor.
• What happens when two users issue the same stock simultaneously? Prove the guard with a concurrent test, not a code read.
• Are back-dated movements allowed? If yes, who recalculates dependent costs and how long does that job take on realistic volume?
• Do sales screens show available-to-promise or raw on-hand? Wrong number here means overselling, which the business will blame on the ERP.
• How do stock opname adjustments post — as compensating movements with a reason code, or as silent edits? Only one of those survives an audit.
• Is unit-of-measure conversion handled at the edge (entry forms) so the ledger stores exactly one base unit per item?

The Takeaway

An inventory module is an accounting system that happens to count boxes. Treat quantity like money: append-only ledgers, derived balances, database-enforced guards, and valuation rules agreed with the people who sign the financial statements. Every shortcut against those principles turns into a reconciliation meeting six months after go-live.

If you are building this on NestJS and PostgreSQL like I do, you already have everything you need: transactions, row locks, and NUMERIC columns. The hard part is not the technology — it is refusing to store that one convenient mutable quantity column when the deadline pressure says you should.