Inventory Valuation & Stock Ledger Engine

How every stock movement becomes a valuation-rate-aware ledger row, how moving-average and layered (FIFO/LIFO) costing diverge, and how the system keeps quantity and value reconcilable against the general ledger

1. Requirements

1.1 Functional requirements

• Record every physical stock movement — receipt, delivery, internal transfer, manufacture consumption/output, subcontract send/return, reconciliation adjustment, asset capitalization — as an append-only row per item per warehouse per instant, carrying the quantity change and the resulting running balance.
• For every movement, derive a valuation rate and stock value using the item’s configured costing method: Moving Average (one blended rate per item+warehouse) or a layered queue method (FIFO or LIFO, discrete cost layers consumed in order).
• Maintain a live, per-item-per-warehouse balance cache that every other stock-facing feature reads instead of re-aggregating the full movement history.
• Whenever a movement is inserted into the past, or a single document touches the same item+warehouse more than once under a layered method, recompute every movement downstream of that point rather than trusting independently-computed rows to still be correct.
• Make that recomputation resumable and non-blocking: large recomputations run as background jobs, not inline inside the triggering transaction’s own commit.
• Hand each movement’s computed value change to the accounting side as a self-contained debit/credit pair, so quantity and monetary value can be reconciled against the general ledger without either side re-deriving the other’s arithmetic.
• Support a periodic “closing” operation that seals historical movement rows into per-item-per-warehouse snapshots, so recomputation and reporting don’t replay an item’s entire lifetime.
• Detect, and optionally self-correct, divergence between the stock ledger and its own internally-implied balances, and between total stock value and the general-ledger balance of the accounts warehouses post into.

1.2 Non-functional requirements

• Deterministic replay: given the same ordered sequence of movements, recomputing valuation rate and stock value for an item+warehouse must always produce the same result — what makes a background repost trustworthy rather than a best-effort patch.
• Resumability under failure: a recomputation spanning many movements must survive a timeout, deadlock, or worker restart and continue from where it left off, not restart from the beginning.
• Negative-stock guard, overridable: consuming more than is on hand is rejected by default (with a precise shortfall message), but escapable per item or per reposting run when intentional.
• Non-blocking for common cases: a forward-dated movement with no history conflicts must be postable synchronously, without invoking the background reposting machinery.
• Traceability: every movement stores enough to answer what an item’s value and quantity looked like immediately after that transaction, not just the final aggregate.

1.3 Constraints

• Quantity and valuation are computed together, in the same pass, by the same engine — there is no separate “quantity ledger” and “valuation ledger” to keep in sync; a single row carries both.
• Valuation method is chosen per item, not per transaction — one item cannot be moving-average in one movement and FIFO in the next; an unset method takes the supplied legal entity’s default (possibly blank), falling to the global default only if none was supplied.
• Reposting a movement never rewrites its recorded physical quantity change; only the derived rate, running balance, and stock value are subject to recomputation.
• Backdated postings, cancellations, and multi-touch documents cannot be resolved by processing each movement in isolation — they require walking forward from the earliest affected point, in timestamp order, for every item+warehouse pair disturbed.
• The general-ledger side of a stock posting is out of scope here; this document covers what the stock side computes and hands off, not how the accounting posting funnel turns that into a balanced voucher (see the general-ledger design).

2. High-Level Design

2.1 Component diagram

2.2 Two distinct code paths inside one engine

The same recalculation engine handles two very different situations, and it is easy to assume there is only one:

1. Live single-entry mode. When a movement posts forward in time with no conflicting history, the engine is invoked with that movement’s own identity already known. It replays only that entry against the current balance and, if nothing later already exists for the same item+warehouse, writes the new balance straight onto the balance cache. Synchronous, and what happens on the vast majority of ordinary receipts and deliveries.
2. Bulk queue mode. When no single entry is pinned, the engine pulls every movement at or after a given point for one or more item+warehouse pairs, sorts them by posting moment and creation order, and replays them one by one, chaining in any dependent movements it discovers along the way (e.g. the other legs of a repack-style transformation, which share a valuation dependency). This is the path a Valuation Repost Job drives, built to be interrupted and resumed.

2.3 Data flow — repost trigger and queue

3. Deep Dive

3.1 Data model

Stock Movement Entry (coined; the row-level unit of the stock ledger) — an append-only record of one quantity change for one item at one warehouse at one posting instant, tagged back to its source voucher and voucher line. Beyond item/warehouse/posting date-time, it carries actual_qty (the signed physical quantity change — the one field a repost never rewrites), incoming_rate/outgoing_rate (this movement’s own valuation rate), the three running-balance fields the engine recomputes (qty_after_transaction, valuation_rate, stock_value), the Stock Valuation Delta (below), a serialized Cost Layer Queue snapshot for queue-valued items, and is_cancelled/is_adjustment_entry flags. A movement is never edited or deleted once submitted — reversal is a separate, sign-flipped cancellation row, and cancelling one movement without cancelling its whole source voucher is explicitly refused.

Stock Position Record (coined; renamed from the per-item+warehouse balance cache) — the live snapshot each item+warehouse pair maintains, split across two writers. Ledger-derived — quantity, valuation rate, stock value — comes only from the movement-posting funnel and Valuation Replay Engine, refreshed from the last Stock Movement Entry on every post or repost (a repair utility can also rebuild quantity from movement rows); for these three alone, a read-optimizing cache, never an independent source of truth. Commitment fields — reserved, ordered, indented, planned — are written directly by the commitment-creating documents, re-aggregating open rows, never from movement history; projected quantity combines both.

Cost Layer / Cost Layer Queue (coined; the FIFO/LIFO valuation state) — for a layered-method item, the valuation state is not one number but an ordered list of [quantity, rate] pairs, each a Cost Layer: a tranche of stock still on hand at the rate it was received at. A FIFO queue adds at the end and consumes from the front; a LIFO queue adds and consumes from the same end (a stack). Both share one abstract contract — add, remove, current state — implemented as two concrete, otherwise near-identical classes. There is no third queue-based method; “weighted average across layers” is not what either implementation does.

Stock Valuation Delta (coined; the stock_value_difference field) — the change in an item+warehouse’s total stock value this one movement caused (new_stock_value - previous_stock_value, rounded to the ledger’s currency precision). This is the one figure the accounting side needs; it never re-derives value from quantity and rate itself.

Valuation Replay Engine (coined; renamed from the recalculation engine) — the object every post, cancel, and repost instantiates to compute or recompute Stock Movement Entries. It holds an in-memory per-item+warehouse working state (quantity, rate, value, cost layer queue) seeded either from the last surviving movement or from a supplied resume point, and mutates that state one movement at a time.

Valuation Repost Job (coined; renamed from the queued reposting record) — a submittable record for one bulk recomputation, scoped either to a single source voucher or to one item+warehouse pair from a given moment forward. Carries allow_negative_stock; a landed-cost-voucher flag that suppresses the negative-stock guard for that voucher’s own cancel-then-repost cycle (its adjustment-only rows carry no quantity); an optional flag to also re-derive the source voucher’s own valuation rate before replaying; and an optional flag to delete and rebuild the source voucher’s own movements from scratch (refused for serialized/batched items). Progress is tracked by a total/posted/current-index triad.

Reposting Checkpoint (coined; the persisted progress state) — a gzip-compressed JSON blob attached to a Valuation Repost Job: the remaining item+warehouse queue, the current index, and a map of each pair’s last-processed movement. Rewritten (and committed) every 2,000 processed movements and whenever the job pauses between pairs, so a worker timeout, deadlock, or restart resumes from the checkpoint instead of replaying from the start.

Reposting Policy (coined; the singleton reposting configuration) — whether reposting is item+warehouse-based (default) or whole-transaction-based; sequential (hourly sweep) or bounded-concurrent (a 15-minute recovery sweep, optionally windowed to a daily time slot); which role gets notified on failure; and an opt-in weekly job (off by default) that scans two standing variance reports and auto-creates Valuation Repost Jobs for whatever they flag.

Stock Closing Run (coined; renamed from the periodic closing record) — a submitted request to seal all Stock Movement Entries for a company (optionally scoped to a warehouse, item, or item group) between two dates into per-item+warehouse Closing Balance Snapshots, run as a background job. It cannot overlap another already-submitted run covering the same date range and scope; cancelling one deletes its snapshots outright.

Closing Balance Snapshot (coined) — one row per item+warehouse (or +batch, or +other configured stock dimension) as of a Stock Closing Run’s cutoff: accumulated quantity, stock value, stock value difference, and — for FIFO items — a compacted quantity/date cost-layer trail. A later run starts its own accumulation from the prior run’s snapshots rather than from the beginning of history, so the replay window any repost or report has to scan keeps shrinking over time.

Inventory Posting Bridge (coined; the stock→ledger handoff) — turns a voucher’s Stock Valuation Deltas into a balanced pair of lines per warehouse touched: a debit (or credit, sign following the delta) against the warehouse’s linked ledger account, and the opposite entry against the voucher’s configured expense/COGS account, handed to the posting funnel described in the general-ledger design. For an internal transfer where the two legs were valued at different rates, the residual is booked as an explicit rounding gain/loss pair rather than silently absorbed.

3.2 Algorithm — Moving Average vs. the two Cost Layer Queue methods

These are not three parallel implementations of one interface. Moving Average is branch logic living directly inside the Valuation Replay Engine — there is no dedicated class for it. Only FIFO and LIFO are implemented as the two concrete cost-layer-queue classes; describing Moving Average as “a third queue variant” would misstate the actual code.

Moving Average, per movement:

new_qty = qty_after_transaction + actual_qty

if new_qty >= 0:
    if actual_qty > 0:                       # incoming
        if qty_after_transaction <= 0:
            valuation_rate = incoming_rate    # restarting from zero/negative
        else:
            new_value = (qty_after_transaction * valuation_rate) + (actual_qty * incoming_rate)
            valuation_rate = new_value / new_qty
    elif outgoing_rate:                       # outgoing, rate known
        if new_qty:
            new_value = (qty_after_transaction * valuation_rate) + (actual_qty * outgoing_rate)
            valuation_rate = new_value / new_qty
        else:
            valuation_rate = outgoing_rate    # exactly emptied
else:
    # would go negative: valuation_rate is left as-is, or backfilled
    # from outgoing_rate / a fallback rate, never recomputed from new_qty
    ...

qty_after_transaction = new_qty
stock_value = qty_after_transaction * valuation_rate

The rate moves only on an incoming movement, or when a previously-empty/negative balance needs a fresh starting rate; an outgoing movement re-derives it from the same weighted-average formula only while stock stays non-negative.

FIFO / LIFO, per movement, operates on the Cost Layer Queue instead of a single rate:

prev_qty, prev_value = queue.get_total_stock_and_value()

if actual_qty > 0:
    queue.add_stock(qty=actual_qty, rate=incoming_rate)
else:
    queue.remove_stock(qty=abs(actual_qty), outgoing_rate=outgoing_rate,
                        rate_generator=<fallback rate lookup>)

qty, value = queue.get_total_stock_and_value()
stock_value_difference = value - prev_value
valuation_rate = value / qty_after_transaction   # if nonzero

add_stock appends a new layer, unless the last layer already carries the identical rate (merged) or the queue is in a negative-quantity state (absorbed into that negative layer first). remove_stock consumes from the front for FIFO or the back for LIFO — unless an explicit outgoing rate matches a layer anywhere in the queue, in which case that specific layer is consumed first (matching a return to its original receipt rather than whatever is chronologically first/last). If the queue empties before the requested quantity is satisfied, a negative layer opens at the outgoing rate (or the last layer’s rate) to keep the deficit trackable.

A worked FIFO example, consuming across two receipt layers:

Layer state after two receipts:
  Receipt 1: +10 units @ rate 100  ->  queue = [[10, 100]]
  Receipt 2: +5  units @ rate 120  ->  queue = [[10, 100], [5, 120]]
  (different rates, so a new layer is appended rather than merged)

Delivery of 12 units, no matching outgoing rate supplied:
  step 1: consume from the front layer [10, 100]
          qty needed (12) >= layer qty (10) -> pop it whole
          consumed = [10, 100]; qty remaining = 12 - 10 = 2
  step 2: front layer is now [5, 120]
          qty needed (2) < layer qty (5) -> partially consume
          layer becomes [3, 120]; consumed += [2, 120]; qty remaining = 0

  Final queue: [[3, 120]]
  Value consumed this movement = (10 * 100) + (2 * 120) = 1,240
  stock_value_difference for this delivery = -1,240
  Remaining valuation: 3 units, stock value 360, valuation_rate = 120

A LIFO consumption of the same two layers would instead take the 5-unit @120 layer first, then 7 of the 10-unit @100 layer, leaving [[3, 100]] — a different remaining rate from the same physical receipts, which is the entire point of the method choice.

Serialized and batch-tracked items layer a further per-unit or per-batch valuation resolution on top of whichever method the item uses — covered by the traceability document, not here; it only feeds the same stock_value/valuation_rate fields on the same Stock Movement Entry.

3.3 Reposting algorithm — what triggers a bulk recompute

A live, forward-dated, single-touch movement never triggers a Valuation Repost Job — it takes the synchronous, single-entry path described in §2.2. A job is created when, on submit or cancel of a source voucher:

1. A backdated insert is detected — a query checks whether any other Stock Movement Entry already exists at or after this voucher’s posting moment for any item+warehouse pair it touches. If so, everything from that moment forward, for every such pair, is now potentially wrong and must be replayed in order.
2. The same voucher touches one item+warehouse pair more than once under a Cost Layer Queue method — e.g., a repeated consuming row for the same item and warehouse. Independent per-row processing cannot get this right, since the second row’s correct layer state depends on the first having already run; only a chained bulk replay does.
3. The voucher is cancelled — unconditionally forces a repost, regardless of the two checks above.

When triggered, either one Valuation Repost Job per affected item+warehouse pair is created (the default, item_based_reposting), or a single whole-transaction job is created that resolves its own item+warehouse set at run time. Either way the job is submitted Queued, not executed inline — except inside automated tests, where it runs immediately unless explicitly suppressed, so assertions about final quantities/values stay deterministic. Live sites drain the queue through the Reposting Policy: sequentially on an hourly sweep, or — if parallel reposting is enabled — through a 15-minute recovery sweep dispatching a configured number of concurrent jobs, capped at one active job per item, inside an optional daily time window.

Once a job actually runs, it drives the Valuation Replay Engine in bulk queue mode, then reruns the Inventory Posting Bridge for the reposted voucher plus every other voucher whose Stock Valuation Delta changed as a ripple effect — unless the job is scoped to accounting-only reposting, in which case the stock side is untouched and only the ledger postings regenerate.

3.4 Error handling

• Insufficient stock is rejected with the exact shortfall named per warehouse, netted against quantity reserved for other pending transactions — unless negative stock is explicitly allowed for the item or the specific repost (every bulk repost defaults to allowing it, since replaying history out of order can transiently look negative before later entries land).
• A missing valuation rate on an outgoing movement falls back, in order, to the last known rate for that item+warehouse, then the item master’s own valuation or standard rate, then a price-list lookup — and only raises if none exist and the item isn’t flagged to allow a zero rate.
• A job interrupted mid-run is distinguished from a genuine failure, as the lifecycle above shows — only a non-recoverable exception is logged, emailed to the configured role, and left Failed for manual restart.
• Reposting is blocked outright before a finalized period close, a closed accounting period, an accounts-frozen date, or a date already covered by a Stock Closing Run — each with its own distinct error, checked before the job can even submit.
• Cancelling a source voucher while its own submission-time repost is still queued or running is refused, so a half-applied recomputation is never orphaned by a cancellation racing ahead of it.

4. Scale and Reliability

• Two cost profiles by design: the synchronous single-entry path is the common, cheap case — one item+warehouse’s working state, one movement. The bulk queue path is the expensive, rarer case, and is the one built with checkpointing, deduplication, and a scheduling window, since it can span an unbounded number of movements once an old backdated entry triggers it.
• Checkpoint cadence bounds blast radius, not correctness: committing progress every 2,000 movements (and between item+warehouse pairs) means a crash loses at most that much replay work — the last-posted-per-pair map is what makes “resume” mean “continue,” not “start over.”
• Concurrency is capped per item, not globally: the parallel-reposting sweep never schedules two active jobs for the same item, since two workers mutating the same Cost Layer Queue concurrently would corrupt it — there is no queue-level lock, only this scheduling-time exclusion.
• A configurable time window keeps bulk reposting off peak hours: the parallel sweep can be restricted to a daily window (with an exception day lifting it), so heavy historical recomputation doesn’t compete with live posting during business hours.
• Closing runs shrink the replay window over time: a Stock Closing Run seeds the next one from its own Closing Balance Snapshots rather than the full movement history, and reposting is blocked from crossing a closing boundary — so replay cost is bounded by “time since the last closing,” not “time since the item was created.”
• Self-detection, opt-in self-healing: two standing checks — one comparing each movement’s recorded balance against an independent recomputation from its own deltas, the other comparing summed stock value per voucher against the general-ledger balance of the accounts its warehouses post to — can, if enabled, auto-create Valuation Repost Jobs weekly, scoped to the current financial year. Journal-only postings are never auto-reposted, and a warehouse mapped to a non-stock-type account is flagged to administrators instead of being endlessly (and fruitlessly) reposted.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Give Moving Average the same pluggable shape as the two queue methods once a third or fourth costing method is seriously considered — today it is special-cased branch logic, not an interface implementation.
• A real lock (or in-flight marker) around a Cost Layer Queue, not just scheduler-level per-item exclusion, once reposting can be triggered from more than one path — today’s safety depends entirely on the scheduler being the sole entry point.
• Make the Reposting Checkpoint queryable at a summary level once volume is high enough that decompressing the attached file to check progress becomes an operational bottleneck.
• Extend the weekly self-healing job beyond the current financial year, with an explicit backlog/rate limit, once installations accumulate multi-year drift the opt-in job currently cannot see.
• Reconsider the backdated-insert detection query’s cost as movement volume per item+warehouse grows — cheap today, but a candidate to index or cache more aggressively at much larger scale.
• Decouple “recreate stock ledgers” from its all-or-nothing, serialized/batched-items-excluded shape, if a future need arises to rebuild part of a voucher’s movements without deleting and reposting the whole document.