Serial & Batch Traceability Architecture

A design reference for tracking individual units and production lots through every stock movement

1. Requirements

1.1 Functional requirements

• Track two kinds of granularity for a stock item: a serial number (one row per physical, individually identifiable unit) and a batch (one row per lot/production run sharing a manufacture date, expiry date, and cost).
• Provide a single record type that captures, for one line of one stock movement, exactly which units or lots were involved and how much of each — this record is the sole place that quantity and rate detail lives; the movement itself no longer carries an independent list.
• Support this tracking across every kind of stock movement: purchase receipts, sales deliveries, internal transfers, manufacturing consumption and output, repacking, stock reconciliation corrections, subcontracting receipts, asset repair consumption, and returns of any of the above.
• Auto-pick existing units/lots for an outward movement (choosing by first-in-first-out, last-in-first-out, or nearest-expiry-first) and auto-create new units/lots for an inward movement, both driven by per-item naming configuration and a global picking-order switch — so a user does not have to hand-type serial numbers for routine receipts and deliveries.
• Prevent an outward movement from using a unit that is not physically in the target warehouse, is already reserved for a different open order, or has already been delivered/consumed; prevent an inward movement from re-registering a unit that is already outstanding elsewhere, unless explicitly permitted.
• Track batch expiry and exclude expired lots from normal picking (while still allowing them to be counted when recalculating raw on-hand levels), and support an optional per-batch valuation mode where a lot keeps its own running average cost instead of sharing the item’s cost pool.
• Feed the aggregate quantity and rate for all lines back into the same stock valuation and general quantity/value bookkeeping that a plain, non-serialized movement uses — a serialized or batched line must reconcile to the same totals a simple line would.
• Support cancellation and amendment of the source document: cancelling delinks and cancels the tracking record and reopens the units/lots it consumed or reserved; amending resets its cancelled/rejected markers so it can be resubmitted cleanly against the new document.
• Support bulk registration of units/lots from an uploaded file, and splitting an existing lot into a new one via a repack-style internal transfer.

1.2 Non-functional requirements

• Idempotency: if a movement line already carries a tracking record, re-processing that movement must validate the existing link rather than create a second one.
• Consistency: the tracking record’s total quantity must match the parent movement line’s own quantity to a tight numeric tolerance, or the whole transaction is rejected — there is no silent drift between “what the line says” and “what units/lots back it.”
• Backdating safety: a backdated transaction must not be allowed to invalidate a unit or lot that a later, already-submitted transaction depends on.
• Non-negative stock: available quantity per lot (and, more loosely, per unit) at a warehouse must not go negative, except where an explicit override — at the lot level or globally — has been set by an operator who accepts the valuation risk.
• Auditability: every unit and lot master carries a stamp of the single most recent movement that touched it, so its current state is always traceable to one transaction rather than to an accumulation of untracked edits.

1.3 Constraints

• One tracking record belongs to exactly one item, one warehouse (except for a non-quantity “maintenance” linkage variant, described below), and one line of one source document.
• Once a tracking record is submitted, its lines are immutable; correcting a mistake requires cancelling and amending the source document rather than editing the record directly.
• Auto-pick and auto-create only fire when the necessary per-item configuration (a serial number naming pattern, a batch naming pattern) and the relevant global switches are present; if they are missing, the transaction fails outright with a configuration error rather than inventing an identifier.
• Availability and duplicate checks run synchronously inside the same validate/submit lifecycle as the movement itself — there is no separate queue or background worker for picking.

2. High-Level Design

2.1 Component diagram

The Bundle Provisioning Handler is invoked from the same submit/cancel lifecycle as the Stock Movement Entry itself — not from a separate integration layer. If the movement’s line carries no tracking record yet, the handler builds one through the Bundle Assembler; if one is already linked, the handler only validates the link and, if the record is still in draft, submits it. On cancellation, the handler delinks and cancels the record and reopens whatever it consumed or reserved.

2.2 Data flow — provisioning at movement time

This path is a short annotated procedure rather than a branching graph:

1. A source document line is saved or submitted (a purchase receipt row, a delivery line, a stock transfer row, and so on).
2. A tracking record is created or reused. If the line already references one, its entries are replaced in place; otherwise a new record is created, scoped to the line’s item, warehouse, transaction direction (inward/outward/maintenance-only/repair-consumption), and posting time.
3. Entries are assembled. For an inward movement with no units/lots supplied, the Bundle Assembler auto-creates them (new serial numbers from the item’s naming pattern, or a new lot). For an outward movement with none supplied, the assembler auto-picks existing ones using the configured order, skipping anything reserved, already delivered/consumed, expired, or outside the target warehouse.
4. Quantities are stamped down. Warehouse, posting time, document reference, and transaction direction are copied from the record onto every one of its lines, so a line is self-describing without a join back to its parent.
5. The aggregate is checked. The record’s own total quantity is compared against the parent line’s quantity field (using the field appropriate to that document type — a stock quantity, a transfer quantity, or a consumed quantity) to within a small tolerance; a mismatch blocks the transaction.
6. On submit, the record’s own docstatus is required to be 1 for every one of its lines, and, if this is the first time it is being finalized, the record itself is submitted alongside the movement.

The convenience text fields still visible on the movement line (a readable list of the serial numbers involved, or a single batch identifier) are written after the record is built, purely as a display courtesy — a global switch can suppress this write entirely — and nothing downstream reads them as authoritative. Everything that matters (availability, valuation, cancellation) is computed from the tracking record’s own lines.

2.3 Data flow — outward picking and valuation

Whichever branch resolves the rate, the outcome is the same shape of result the Valuation Replay Engine already expects from any movement: one aggregate quantity and one aggregate Stock Valuation Delta, folded into the Stock Position Record exactly as described in the stock-ledger design. The traceability layer only changes how the rate is derived, never the shape of what flows downstream.

3. Deep Dive

3.1 Data model

Traceability Bundle (the single source of truth for one movement line’s units/lots) Scoped to one item, one company, and — outside the maintenance-only variant below — one warehouse. Carries the transaction direction (Inward, Outward, Maintenance, or Asset-Repair-consumption), a link back to the source document and its specific line, a posting timestamp, and rolled-up totals (total quantity, average rate, total amount) recomputed from its own lines. A “Maintenance” record intentionally skips the warehouse requirement and most of the valuation logic described below — it exists purely to attach traceability information to a non-stock-moving workflow (a service visit, a quality check) rather than to move quantity or value. Amendment resets the record’s cancelled/rejected markers and clears its old line reference so it binds cleanly to the new document.

Traceability Line (one unit, or one tranche of a lot, within a bundle) Each line carries either a unit reference or a lot reference (never neither, and — outside a narrow same-lot exception — never a mismatched pair), a signed quantity (negative for outward), an incoming/outgoing rate, the resulting value change, and — only when it participates in the item’s shared cost pool rather than its own per-lot average — a snapshot of that pool’s queue state at the moment this line posted. A simplified sketch:

{ unit_or_lot:            <serial number> | <batch identifier>,
  quantity:                signed float,
  rate:                    incoming or outgoing rate for this tranche,
  value_change:            quantity * rate,
  cost_layer_snapshot:     [[qty, rate], ...] | null,   # only for shared-pool batches
  warehouse, posting_time, document_reference, direction:
                           copied down from the parent bundle }

Serial Number Record (one row per physical unit, for the life of that unit) Holds the item, its current warehouse (populated only by a movement — a fresh record cannot be created with a warehouse already set), an optional lot reference, a purchase/incoming rate, an optional customer (once delivered), warranty and equipment-maintenance windows, and a status describing where the unit currently sits in its lifecycle (below). It also carries a single reference back to whichever document most recently claimed it, stamped only if no other document already holds that claim, and cleared again if that same movement is later cancelled.

Batch Record (one row per lot) Holds the item, an optional parent lot (for split lineage), manufacture and expiry dates, a cached on-hand quantity (recalculable on demand), a per-lot valuation-mode flag that is set once at creation and not meant to change afterward, an optional per-lot override to tolerate negative stock, and a disabled flag to retire a lot from picking without deleting it.

3.2 Serial Number Record status lifecycle

A unit’s status is derived by the Bundle Provisioning Handler from the direction and purpose of whichever movement most recently touched it — it is never hand-set by a user.

Two creation paths seed different starting states: bulk pre-registration (from an uploaded file, or before any transaction references the unit) starts a unit as Inactive; auto-creation performed for a specific inward movement starts it directly as Active, since the movement that creates it is also the one that puts it in a warehouse. A defined “Expired” option exists on the status field, but no read code path in this subsystem ever assigns it — it appears to be reserved for a workflow not exercised here, so it is omitted above rather than asserted.

3.3 Algorithm — assembling entries (the Bundle Assembler)

For an inward movement with no units/lots explicitly supplied:

1. If the item tracks lots, create one new Batch Record (or, for a rejected-goods
   line following the same document and line as a prior accepted line, reuse that
   line's lot rather than minting a new one).
2. If the item tracks units, generate as many new serial numbers as the movement's
   quantity, using the item's naming pattern; each is created directly in the
   Active state with its warehouse, lot (if any), and document reference already set.
3. If neither pattern is configured, fail with a configuration error rather than
   guessing an identifier.

For an outward movement with no units/lots explicitly supplied:

1. Determine the picking order from global configuration: oldest-first,
   newest-first, or nearest-expiry-first.
2. Query candidates in that order, filtering out:
   - units reserved against a different open order,
   - units already Delivered/Consumed, or in a different warehouse,
   - lots that have expired (unless recalculating raw on-hand levels),
   - lots/units already claimed by another still-open pick for the same document.
3. Greedily consume candidates until the requested quantity is satisfied. A lot
   contributes at most its own available quantity; the remainder is drawn from
   the next candidate in order.
4. If total available quantity across candidates is less than requested, fail with
   an insufficient-stock error — nothing partial is created.

Once entries exist, the bundle’s own totals are recalculated from them, and that total is validated against the parent line’s own quantity (using whichever quantity field that document type actually uses) to within a tolerance of roughly one-hundredth of a unit. This validation is what makes the tracking record authoritative: the parent line cannot declare a quantity that its own tracking record disagrees with.

Returns are a special case: rather than re-deriving a rate through picking, a return looks up the original line’s tracking record (matched by the same source document and the same line reference) and reuses each returned unit’s or lot’s original incoming rate, so a return never silently re-prices stock through the current queue or average. If no tracking record exists for the original line at all — because it predates this model — the same lookup falls back to the movement history described next.

3.4 Legacy versus current: how the logic is actually split

This subsystem was reworked, and the split shows up as three distinct layers rather than one:

1. The bundle’s own record logic lives with the tracking record itself and owns validation, the picking/creation entry point, quantity reconciliation, submit/cancel/amend behavior, and return-rate lookup.
2. Cross-cutting helpers — the handler invoked from every Stock Movement Entry submit, the entry-assembly builder, and the two valuation-resolver classes (one for units, one for lots) — live in a separate, general-purpose utility module shared by the whole stock subsystem, rather than inside the tracking record’s own module, because they are needed by many document types, not just the tracking record’s own lifecycle.
3. A legacy fallback layer, explicitly marked as deprecated, supplies the arithmetic the current valuation resolvers still call into whenever a unit or lot has value recorded on movement rows that carry no tracking-record link at all — i.e., stock postings made before this model existed. The current resolver classes are literally built on top of these legacy classes (inheriting their fallback methods) rather than duplicating the pre-rework arithmetic, so both live valuation and old, bundle-less valuation share one code path per unit/lot rather than two independently-maintained ones.

What this means for “single source of truth”: for any transaction created under the current model, the tracking record’s own lines are the only place quantity, rate, and unit/lot identity live — the movement’s own text-field mirrors of “serial numbers involved” or “batch used” are write-only conveniences, refreshed from the record and never read back as ground truth. The legacy fallback exists solely so that valuation for very old, bundle-less postings — and returns filed against them — still computes correctly; it is not a second live path for new transactions, and nothing in the current creation or picking logic writes to those old-style fields going forward.

3.5 Cancellation and amendment

Cancelling the source document cancels its tracking record as part of the same operation (except for two carve-outs — a point-of-sale sale consolidated into a later invoice, and an asset-repair consumption record — where the outer document’s own cancellation path owns that instead). Before the record itself is cancelled, every movement row that still points at it is delinked, so a cancelled record is never left as a dangling reference. Cancellation also re-validates on-hand lot quantities and reopens any backdating conflicts the record had been holding.

Amending a cancelled source document does not reuse the old tracking record: a new one is created (referencing the cancelled one as its predecessor), its cancelled/rejected markers are reset to a clean slate, and its old line reference is cleared so it can bind to whichever line the amended document produces. Deleting a cancelled record independently is blocked while any movement or master record — a unit or a lot — still references it; the reference must be cleared first.

3.6 Error handling

• Quantity mismatch between the tracking record’s total and its parent line’s own quantity — rejected before submission.
• Insufficient available units/lots during auto-pick — rejected with an explicit shortfall message; nothing partial is created.
• Negative lot quantity at a warehouse — rejected unless an explicit override (per lot, or globally) has been set.
• Backdated conflict — a transaction dated earlier than an already-submitted transaction that depends on the same unit or the same lot-in-warehouse is rejected until the later one is cancelled first.
• Duplicate outward claim — the same unit appearing in two open transactions, or being re-inwarded while it is still outstanding elsewhere, is rejected unless an explicit override is set.
• Missing configuration — an item with no naming pattern configured for auto-pick/auto-create fails with a configuration error rather than fabricating an identifier.

4. Scale and Reliability

• Load pattern: a tracking record is created for essentially every line of every serialized or batched stock movement, so volume scales linearly with transaction volume rather than running as a periodic batch job — there is no queue to fall behind on.
• Concurrency control: availability queries (what quantity of a lot is on hand, what units currently sit in a warehouse) take row locks on the underlying movement and tracking-line tables while they run, serializing concurrent outward postings against the same item/warehouse rather than relying on optimistic retry — this favors correctness (no two transactions can claim the same unit) over raw throughput under heavy contention on a single hot item.
• Indexing: the lookups that back picking and duplicate-detection are indexed by item and warehouse (on the unit master) and by warehouse plus unit-or-lot plus posting time (on the tracking line), which keeps both auto-pick and future-conflict checks viable as transaction history grows.
• Backdating cost is bounded to the backdated case: the forward-scanning check for conflicting future transactions only runs when a transaction is dated before “now,” so ordinary same-day postings never pay that cost.
• Legacy fallback overhead shrinks over time: the deprecated valuation layer is only invoked when a unit or lot has no bundle-linked history at all for the period being valued; as older postings age past any repost horizon, this branch is exercised less, not more.
• Failure containment: creation, picking, and validation all happen synchronously inside the same submit transaction as the parent movement — a tracking failure aborts the whole movement rather than letting a stock quantity post without corresponding unit/lot detail.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Picking-policy granularity: today’s single global FIFO/LIFO/expiry switch would need to become a per-item or per-category setting if different lines of business need different picking behavior at the same time.
• Row-lock contention: the synchronous, lock-based availability check is correct but could become a throughput bottleneck for a small number of extremely high-velocity items/warehouses; a reservation-token approach would reduce lock hold time at the cost of more moving parts.
• Status lifecycle is implicit in transaction-purpose strings: a unit’s status is derived from matching a document’s declared “purpose” against a fixed list of recognized strings; a new transaction purpose that is not on that list will silently default to the generic outward case rather than erroring, which is easy to miss when new transaction types are added. Making the mapping explicit and exhaustive (rather than a default-plus-exceptions list) would remove that risk.
• Retiring the legacy valuation fallback: once postings that predate this model have fully aged out of the valuation window any given deployment cares about, the inherited legacy arithmetic could be removed rather than carried forward indefinitely as dead-weight complexity in every valuation call.
• Per-lot valuation-mode migration: since the mode flag cannot be changed after creation, a deployment that picked the wrong default early on has no in-place remedy; a supported, audited migration path (recompute and re-flag) would be worth adding as lot counts grow.