Store Grams, Not Bags: Designing a Multi-Unit Inventory Ledger in Postgres

Q: How do you make an append-only ledger actually append-only?

With a database trigger that rejects UPDATE and DELETE on the movement table, not with application convention. Discipline doesn't survive the first maintenance script or admin hotfix, and one edited row makes the whole ledger unreconcilable — quietly, because every report built on top of it still renders.

A kitchen buys in bags, cooks in grams, counts in kilos. How I modelled a multi-unit inventory ledger in PostgreSQL: one base unit, one lock target, one log.

TL;DR — A kitchen buys paneer in 5 kg bags, cooks it in grams and counts it in kilos. A multi-unit inventory ledger has to answer all three without any of them drifting. Store one canonical base unit per dimension — grams, millilitres, pieces — convert once at the API edge, and keep the typed quantity alongside for audit. Then make a materialised balance row the thing you lock, keep an append-only movement ledger next to it, and stamp the resulting balance on every row so reconciliation is a real check instead of a tautology.

Key takeaways

Convert to a base unit at the API edge, once, in a module with no I/O. Every quantity crossing the database boundary is grams, millilitres or pieces.
A purchase pack is not a unit. One bag is 5 kg of paneer and 25 kg of flour, so pack size is a fact about the item, never about the unit table.
Don’t derive current stock with SUM() over the ledger. It’s O(n) on a table that only grows, and — the part people miss — there is nothing there to take a row lock on.
Put the non-negative rule in the procedure, not in a CHECK constraint. An overdraw should come back as a 409 with numbers attached, not a constraint violation that reads as a 500.
Idempotency: the lookup before the lock is a cheap path for retries. The unique index is the actual guarantee. Don’t confuse the two — I did, in my own notes.

One shelf, three numbers: why a multi-unit inventory ledger is a modelling problem

The hard part of a multi-unit inventory ledger is not concurrency or schema size — it’s that one shelf of stock is three different numbers depending on who is looking at it.

I built raw-material inventory for a venue on a multi-tenant food-tech SaaS: an item catalog, stock in and out, weekly physical counts, low-stock alerts, a reorder list. The problem that shapes every other decision shows up in the first ten minutes.

Purchasing sees bags. The kitchen sees grams. The person walking the shelves with a clipboard on Sunday sees kilos. All three are correct, all three are the same paneer, and whatever you store has to answer all three questions without any of them drifting.

The system already tracked whether a sellable dish was in stock — a single integer decremented on each order. That’s a different problem wearing similar clothes. A dish is not an ingredient, one dish consumes several ingredients, and no arithmetic on a menu-level counter tells you how much oil you burned last week. So this is a separate catalog with its own ledger, and phase 1 deliberately doesn’t touch the menu counter.

One thing up front, because it changes how you should read the numbers below: this is built and verified against a scratch PostgreSQL 17 instance, not deployed. Where I say something behaves a certain way, I mean I exercised it against that scratch database. Where I haven’t verified something, I say so.

Store one base unit and convert at the edge

Every quantity that crosses the database boundary is stored in one canonical base unit per measurement dimension. Mass is grams. Volume is millilitres. Count is pieces. Conversion happens exactly once, at the API edge, in a single pure module.

   purchase pack        recipe unit       count unit       display unit
   "Bag, 5 kg"          "g"               "kg"             "kg"
        │                    │                 │                │
        └──────────┬─────────┴─────────────────┴────────────────┘
                   ▼
        CANONICAL BASE UNIT      mass → g    volume → ml    count → pc
                   │
                   ▼
   ┌───────────────────────────────────────────────────┐
   │  balance row: quantity + moving average cost      │  ← locked, materialised
   └───────────────────────────────────────────────────┘
                   ▲
                   │ every change, no exceptions
                   ▼
   ┌───────────────────────────────────────────────────┐
   │  movement ledger: append-only, balance stamped    │
   └───────────────────────────────────────────────────┘

The alternative — store the quantity next to the unit the user typed, convert on read — works fine until a recipe explodes into ingredients. The recipe says 250 g of paneer, stock says 4 bags, and now every consumption path needs a runtime unit lookup plus its own rounding policy. Multiply that by the number of call sites and the rounding policies stop agreeing with each other. Storing base units turns the phase-2 subtraction into plain arithmetic.

The cost is real: the stored number is no longer the number the user typed. I paid it by keeping the typed quantity and the typed unit on every ledger row. The row says both 10000 g and 2 bags, so an auditor can see what the human actually did.

The conversion module imports nothing — no database client, no logger. Unit conversion, weighted average, edible-portion cost, low-stock classification and suggested order quantity all live there, deterministic and testable without a database running. Most of my unit tests point at that one file.

// Illustrative. One conversion, at the edge, before anything is persisted.
function toBaseUnits({ quantity, unitCode, packId, packCount }, item, units) {
  // Exactly one of {quantity, unitCode} or {packId, packCount}. Both is ambiguous,
  // neither is meaningless — either shape is rejected in validation, not coerced here.
  if (packId != null) {
    const pack = item.packs.find(p => p.id === packId);
    if (!pack) throw domainError('UNKNOWN_PACK');
    return packCount * pack.baseQuantity;      // pack size is stored in base units
  }
  const unit = units.find(u => u.code === unitCode && u.dimension === item.dimension);
  if (!unit) throw domainError('UNKNOWN_UNIT');
  return quantity * unit.multiplierToBase;      // kg → 1000, g → 1, bag → not here
}

Note what that function refuses to do: it never guesses a unit. An earlier cut of it defaulted to the item’s base unit when unitCode was missing, which meant a caller who meant kilograms and forgot the field silently posted grams. Off by a thousand, no error. Requiring the unit whenever a loose quantity is supplied costs one validation rule and removes an entire class of silent corruption.

A pack is not a unit

Packs cannot live on the shared unit table, and that constraint is the whole reason they are a separate concept. A unit table expresses dimension-generic conversion: kilograms to grams, litres to millilitres. Those ratios are properties of mass and volume, true everywhere.

“One bag” is not. One bag is 5 kg of paneer and 25 kg of flour. That’s a purchasing fact about a specific item, so it belongs on the item — a per-item list of named packs, each storing its size in base units. Trying to force it into the unit table means either a bag_paneer unit and a bag_flour unit, or a nullable item reference on a table that is otherwise global and seeded. Both are worse than a small child table.

Three copies of one number

Current stock is a row you update, not a sum over the ledger.

Deriving the balance from SUM(signed_quantity) is the purist answer, and it’s wrong here for two reasons. The obvious one is that it’s O(n) on a table that only grows. The one that actually decided it: there is nothing to lock. A sum over a thousand rows is not a row lock, so two concurrent movements can both read the same total and both write. The materialised row is what gives you something to take a lock on.

So there are three copies of the same number in the system:

The materialised balance on the stock row.
The sum of signed quantities in the ledger.
The running balance stamped on the newest ledger row at the time it was written.

That’s deliberate redundancy, and it’s what makes reconciliation a real check rather than a tautology. If all three agree, the ledger is intact. If they don’t, you know something wrote a balance without appending a movement, or appended a movement without updating the balance — and you know it before the period cost report is built on top of it. This is the same instinct as checking reality against your own records on a schedule rather than assuming two systems stayed in step.

Approach	Read cost	Lock target	Reconcilable
`SUM()` over the ledger	O(n), grows forever	none — this is the killer	nothing to check against
Materialised balance only	O(1)	the balance row	no, it’s the only copy
Both, with the balance stamped per row	O(1)	the balance row	yes, three independent copies

The ledger is append-only, and that’s enforced by a trigger rather than by convention. Updates and deletes on the movement table abort the transaction. Corrections are compensating movements, not edits.

Application-level discipline is not enforcement. Any future maintenance script, admin tool or well-meaning hotfix can update a row, and once one row has been edited the ledger no longer reconciles with anything — and the variance report built on it is unrecoverable, quietly. This is also the one place I broke the codebase’s own convention that stored procedures return a status instead of raising. The trigger’s entire job is to abort, so it raises, and I wrote the reason into the migration next to it.

None of this shape is originally software’s. Accountants settled on append-only entries with compensating corrections long before we did, and Martin Fowler’s accounting narrative pattern is the clearest write-up of why the correction has to be a new row rather than an edit.

The two-partial-index trick

The balance table is unique on item plus location, and location is nullable for venues running a single implicit store. A plain nullable unique constraint does not do what you want here, because Postgres treats NULLs as distinct and will happily accept two rows for the same item in the implicit store.

Two partial unique indexes express what was actually meant:

CREATE UNIQUE INDEX ON stock_balances (item_id)
  WHERE location_id IS NULL;

CREATE UNIQUE INDEX ON stock_balances (item_id, location_id)
  WHERE location_id IS NOT NULL;

Worth knowing that Postgres 15 added UNIQUE NULLS NOT DISTINCT, documented under CREATE INDEX, which solves the same problem in one index. I used the two-index form because it states the two cases explicitly and because a future multi-store phase will want them separate anyway.

The balance changes in exactly one place

Every balance change goes through one stored procedure that takes a row lock. There is no read-modify-write in application code anywhere in the module. The service layer resolves units, loads the item, decides what the movement should be, and hands the whole thing to the procedure.

Here’s the failure that rule exists to prevent. Two concurrent 6 kg stock-outs against 10 kg on hand: both read 10, both compute 4, both write 4. Stock now claims 4, reality is −2, and both ledger rows claim a closing balance of 4. The last part is the real damage — you haven’t just lost 6 kg, you’ve destroyed the only property that made the ledger worth keeping.

-- Illustrative. The read, the arithmetic and the write are one transaction,
-- and the lock is held across all three.
CREATE OR REPLACE FUNCTION apply_stock_movement(
  in_venue_id uuid,
  in_item_id  uuid,
  in_delta    numeric,     -- signed, already in base units
  in_ref_key  text
) RETURNS jsonb AS $$
DECLARE
  v_row     stock_balances%ROWTYPE;
  v_new_qty numeric;
BEGIN
  -- Cheap path first: a retry of a movement we already applied.
  -- This is an optimisation, not the guarantee. See below.
  PERFORM 1 FROM stock_movements
    WHERE venue_id = in_venue_id AND ref_key = in_ref_key;
  IF FOUND THEN
    RETURN jsonb_build_object('status', 'duplicate_ignored');
  END IF;

  SELECT * INTO v_row FROM stock_balances
    WHERE venue_id = in_venue_id AND item_id = in_item_id
    FOR UPDATE;                                  -- the lock target

  v_new_qty := v_row.quantity + in_delta;

  IF v_new_qty < 0 THEN
    -- Policy, not a constraint. Caller maps this to a 409 with numbers attached.
    RETURN jsonb_build_object(
      'status',    'stock_short',
      'available', v_row.quantity,
      'requested', abs(in_delta));
  END IF;

  UPDATE stock_balances SET quantity = v_new_qty
    WHERE venue_id = in_venue_id AND item_id = in_item_id;

  INSERT INTO stock_movements (venue_id, item_id, delta, closing_balance, ref_key)
  VALUES (in_venue_id, in_item_id, in_delta, v_new_qty, in_ref_key);  -- stamped here

  RETURN jsonb_build_object('status', 'applied', 'balance', v_new_qty);
END;
$$ LANGUAGE plpgsql;

SELECT ... FOR UPDATE is doing the work. Everything else in that function is bookkeeping around it.

“FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.”

— PostgreSQL documentation, Explicit Locking

That is also why the lock rather than the isolation level is what you reason about here. Read Committed will happily let both transactions see the same starting balance. This is the same shape as assigning a scarce resource under contention: the interesting part is never the business rule, it’s holding the lock across the read and the write.

All three procedures that touch balances — single movement, bulk post, count posting — take their locks in one documented order: item id ascending, then location ascending with nulls first. That comment lives in the migration, because it’s exactly the kind of invariant that’s invisible in any single function and broken by the next person who adds a fourth.

Don’t put the constraint on the column

There is deliberately no non-negative CHECK on the quantity column. The procedure refuses the overdraw instead.

That looks backwards until you meet a legitimately negative balance, and you will: a purchase entered two days late, or phase-2 consumption of stock nobody ever entered. If the rule sits on the column, that legitimate case surfaces as a constraint violation, which your route layer reports as a 500, and now an operator with a data-entry backlog is looking at a server error.

Policy in the procedure means an overdraw returns a 409 carrying the available and requested quantities, which the UI can actually render, and the rare legitimate negative can still be written behind an explicit flag. The schema stays able to represent the truth; the procedure decides what’s allowed.

Validate the whole batch under lock, then write

The bulk procedure takes its locks, runs a full validation pass that writes nothing, and only then runs the write pass.

Validating line by line as you write means a batch that fails on line 3 has already committed lines 1 and 2, and “all or nothing” becomes a documentation lie. During validation the procedure maintains a running projection of each item’s balance, so the same item appearing twice in one batch is validated against what the earlier line would leave behind rather than against the starting balance. That case is easy to miss and trivially producible by a user pasting a delivery note.

Idempotency: the cheap path and the real guarantee

A pre-lock idempotency check is not a guarantee. It’s a cheap path for sequential retries; the guarantee is a unique index on the venue plus the idempotency key.

This is where my own notes contradicted themselves, and the correction is more useful than the original claim.

I had written down that an application-level idempotency check is racy — the gap between “has this reference already been posted?” and the insert is a window a retry gets through — and then, four decisions later, described the pre-lock lookup in the procedure as the idempotency mechanism. Moving check-then-insert into PL/pgSQL doesn’t close the window. It just moves it.

What actually closes it is the unique index:

CREATE UNIQUE INDEX ON stock_movements (venue_id, ref_key);

So the honest description of that code is two-part. The lookup before the lock is a cheap path: a client that times out and retries a minute later gets duplicate_ignored without taking a lock, which is the common case and worth optimising. The unique index is the guarantee: two genuinely concurrent retries of the same reference will both pass the lookup, and the index is what stops the second one landing. That’s the index’s contract rather than something I’ve raced in a test.

Which leaves a loose end I haven’t tied off. The concurrent duplicate surfaces as a unique violation rather than the tidy duplicate_ignored status object the sequential retry gets. Two shapes for one outcome, and the caller has to know about both. Catching unique_violation in the procedure and returning the same status object is the fix; it isn’t written yet.

The idempotency key itself is caller-supplied, and phase 2 gets it for free — order-time consumption uses the order identifier as the key, which is the same discipline as making every handler behind a durable queue safely replayable.

Cost: moving average, yield, and the rounding you can’t retrofit

Valuation is moving weighted average. It’s the method that fits a per-movement procedure — one number to update under a lock I’m already holding. FIFO cost layers would need a layer table and a consumption algorithm, and phase 1 has no consumption. Every incoming movement still stores its own unit cost, so the layers stay derivable from the ledger if a later phase wants them. I checked that the door stayed open before choosing.

Yield percentage sits on the item from day one even though nothing consumed it in phase 1. Cost per edible portion is the as-purchased cost divided by the yield fraction: paneer at ₹200/kg that loses 20% to trim actually costs ₹250/kg to put on a plate. This cannot be retrofitted. Add yield later and every historical cost figure was computed at an implicit 100% yield, so either you restate all of them or your reports silently mix two definitions of cost.

Rounding is specified per quantity type: quantities to three decimals, money to two, per-base-unit costs to four. The four-decimal case is the one that matters, because these costs are per gram rather than per kilogram. Rounding an edible-portion cost of ₹0.5313/g to ₹0.53 carries a 0.25% error into every plate cost that uses it. Postgres numeric is exact, and the numeric type docs are worth reading before you pick a scale, because the choice is permanent in a way the column type isn’t.

What actually broke

Two things, and neither was the concurrency I’d spent the most time on.

The shared response helper existed twice. A TypeScript implementation and a CommonJS twin that the JavaScript route files require. The twin exported the base response function; the TypeScript version had it module-private. I hit it wiring up error handling for this module, because my 4xx responses carry a payload — how much stock was available, which batch line failed — and the typed convenience helpers only carry a message. So I reached for the base function and it wasn’t there.

Nothing had caught it. Types didn’t, because the two files aren’t checked against each other. Tests didn’t, because the affected paths are error paths and nobody had written a route test that provokes one. Reading the two files side by side, the failure mode is clear enough: a 4xx-with-payload path going through the typed module hits an undefined function, and the route reports the resulting TypeError as a 500. I did not measure how often that path was actually taken, so I’m not claiming an impact number — what I can say is that the contract between the two files was unverified, and nothing in the type system or the suite would have noticed the drift.

The fix was one keyword. The useful part is the test, and it’s deliberately not a test of inventory:

test('both implementations expose the same surface', () => {
  for (const name of EXPECTED_HELPERS) {
    assert.equal(typeof typed[name],  'function', `typed build is missing ${name}`);
    assert.equal(typeof legacy[name], 'function', `legacy build is missing ${name}`);
  }
});

Two implementations of one contract will drift. Either delete one or assert they match. Hoping is not a third option.

My own rounding drifted. Having written down that the SQL and the application code round identically, I went back and found they don’t — the procedure was storing the raw quotient for the moving average while the JavaScript helper rounded to two decimals, on a value the spec said should carry four. The specification was right and both implementations were wrong in different directions. Writing the rule down is not the same as enforcing it, which is the same lesson as the paragraph above arriving from the other side.

FAQ

How should I store inventory quantities when items use different units of measure? Store one canonical base unit per measurement dimension — grams for mass, millilitres for volume, pieces for count — and convert at the API edge in a single module. Keep the user’s typed quantity and unit on the ledger row for audit, but never make it the number you do arithmetic on.

Why not just derive current stock by summing the ledger? Two reasons. It’s O(n) on a table that only grows, and there’s no row to lock, so concurrent movements can both read the same total and both write. A materialised balance row gives you a lock target; keeping the ledger alongside it gives you something to reconcile against.

Should negative stock be blocked with a CHECK constraint? Usually not. Legitimately negative balances happen — a late-entered purchase, consumption of stock nobody recorded. Enforce the rule in the procedure so an overdraw returns a 409 with the available and requested quantities, and keep the schema able to represent the true state.

What’s the difference between a unit and a purchase pack? A unit expresses dimension-generic conversion (kg to g) and is true for every item. A pack is a purchasing fact about one specific item — one bag is 5 kg of paneer and 25 kg of flour — so it belongs on the item, storing its size in base units.

Is a pre-lock idempotency check enough? No. It’s a cheap path for sequential retries — a client that times out and retries a minute later gets an “already applied” response without taking a lock. The guarantee is a unique index on the venue plus the idempotency key. Without it, two genuinely concurrent retries both pass the check and one has to fail on the constraint.

How do you make an append-only ledger actually append-only? With a database trigger that rejects UPDATE and DELETE on the movement table, not with application convention. Discipline doesn’t survive the first maintenance script or admin hotfix, and one edited row makes the whole ledger unreconcilable — quietly, because every report built on top of it still renders.

Moving weighted average or FIFO? Weighted average fits a per-movement stored procedure — one number to update under a lock you already hold. Store each receipt’s own unit cost anyway, and FIFO layers stay derivable from the ledger if you need them later.

What has to be decided in phase 1 because it can’t be retrofitted? Yield percentage, rounding scale per quantity type, the enum values your ledger’s source type will ever need, and the lock order. Everything on that list either restates history or migrates a table holding every movement the business has ever made.

What I’d still improve

The concurrent-duplicate response shape. Catch unique_violation and return the same status object the sequential retry gets, so callers handle one shape instead of two.
A property test for the reconciliation invariant. Right now I check that the three copies of the balance agree after a scripted sequence. What I want is a generator that throws random valid movements at it and asserts the invariant after every one.
The duplicated low-stock predicate. “Quantity ≤ reorder level” exists in SQL and in the pure module, because I wanted the offline test. Two implementations of one predicate is exactly the drift I got bitten by above, and a comment at both sites is a weak fix.
Rounding parity, enforced. Same table of expected values, asserted against both the SQL and the JavaScript path, rather than a sentence in a decisions document.

The one idea to take away

Pick the unit the database speaks, and convert exactly once on the way in.

Everything else in this post falls out of that choice. The ledger reconciles because there is one number to compare, not three representations of a number. Phase 2’s recipe subtraction is plain arithmetic instead of a unit lookup with its own rounding policy. The lock has something to lock. If you get the multi-unit inventory ledger boundary right at the API edge, the rest is bookkeeping — and if you get it wrong, you will be writing conversion code at every call site for as long as the system lives.

I write about backend reliability and data modelling from production work on food-tech and healthcare SaaS — more on the engineering work I take on, the hospital meal platform I own the backend for, and how an auditable balance behaves in a multi-tenant system. If you’re modelling inventory or a ledger and want a second pair of eyes, get in touch.