aiku
Engineering notes

Stock valuation is a setting, not a column

· #inventory · #accounting · #postgres

Ask three accountants what your stock is worth and you get three numbers: last purchase price, weighted average cost, first‑in‑first‑out. All three are defensible. Only one of them is the figure on the balance sheet, and which one depends on jurisdiction and on what your auditors signed off last year.

We used to store one number per SKU, computed from the last purchase price, and call it the value. That is not allowed in the UK and it moves every time a supplier changes a price. So we rebuilt it.

Replay the history once, get every method

Every stock movement — purchase in, sale out, adjustment, transfer — is a row in a history table. Valuation is a fold over those rows. We wrote one replay that carries the state for both methods and emits both per movement:

  • WAC: running quantity and running value; wac = value / quantity; resets to purchase cost when on‑hand reaches zero or below.
  • FIFO: a queue of cost layers; outflows consume from the oldest; non‑purchase inflows are layered at the current FIFO cost; layers reset when on‑hand goes negative.

The result lands in the history as wac_per_sku, fifo_per_sku and the value columns in organisation and group currency, with NULL meaning before the cutoff so it can never be confused with zero.

There is a cutoff date per organisation. Before it, the opening balance is valued at last purchase price; after it, the replay is authoritative. Backfill runs per SKU as queue jobs ordered by peak value, so the numbers that matter most arrive first.

LIFO is missing on purpose

IFRS prohibits it. Building a valuation nobody may legally report is how spreadsheets fill up with wrong numbers. Leaving it out was a line in the design, not an oversight.

"Official" is one enum

enum OrgStockValuationMethodEnum: string
{
    case WAC  = 'wac';
    case FIFO = 'fifo';

    public static function official(): self
    {
        // group setting → config fallback → FIFO
    }
}

Everything that needs a value — margins, cost of goods sold, product costing, dashboards, four stock‑history tables, three spreadsheet exports, the tooltip on the stock screen — asks official(). The stored last‑purchase‑price fields still exist for people who want to see them, but they are never the accounting figure, and if somebody stores LPP as the official method the enum silently answers FIFO.

The switch lives in the group settings as two radio cards, with a confirmation that tells you, in plain words, to consult every accountant in the group before pressing it. The change is audited. After switching you run three rehydrate commands per organisation; the UI tells you which.

The bug we found along the way

The purchase cost on movements had been filled by a one‑off import and then by nothing. Every purchase for five months had a null cost, and WAC carried forward a stale figure without complaint. Two repair commands later — one that pulls landed costs from the old system, one that derives cost from the purchase amount where there is nothing better — about 1.5% of purchases remain costless. That is fine: WAC carries forward, and the next purchase corrects it.

What we would tell our past selves

Value is a function of history plus a policy, not a fact about a SKU. Store the history, compute all the policies you are allowed to report, and make "which one counts" a setting someone accountable can change.

Related notes