Batch and Serial Tracking in ERP: Full Traceability

When a customer calls to say a product from a specific production run failed quality control, the question that decides whether your recall takes an afternoon or a week is simple: can your ERP tell you, in seconds, exactly which shipments, customers, and warehouse bins contain that lot? For food, pharmaceutical, chemical, and medical device businesses, batch and serial-number traceability is not a nice-to-have report, it is the backbone of every receiving, movement, and shipping transaction in the system.

This post walks through how to design that traceability layer in a custom ERP: the difference between lot-level and instance-level tracking, the data model that supports both, first-expired-first-out picking logic, and the recall report that ties a single batch number back to every customer who received it.

Lot-level versus instance-level tracking

Not every item needs a unique serial number, and not every regulated item can get away with only a batch number. The GS1 standards body describes three levels of traceability granularity that map directly onto ERP design decisions: class-level identification by product code alone, lot-level identification by product code plus batch or lot number, and instance-level identification by product code plus a unique serial number per unit. Choosing the right level per item category is the first architectural decision, because it determines how much detail your movement ledger has to carry.

• Class-level: generic commodity items where any unit is interchangeable with any other unit of the same product code. No batch, no serial. Fastest to move, least traceable.
• Lot-level: items produced or received in batches that share a manufacturing date, expiry date, or supplier certificate. One batch number covers hundreds or thousands of units. This is the right default for raw materials, packaged food, and most pharmaceutical intermediates.
• Instance-level: high-value or safety-critical items where each individual unit needs its own history, such as medical implants, electronic assemblies under warranty, or controlled substances. Every unit gets a unique serial number in addition to its lot.

The data model: lots, units, and movements

A traceability-capable schema separates three concerns that are often flattened into one inventory table in simpler systems: the lot itself, with its manufacturing and expiry metadata; the physical unit, which may or may not carry its own serial number; and the movement event, which is the only place quantity actually changes. Keeping movements as an append-only ledger, rather than mutating a running balance, is what makes recall queries and audit trails possible after the fact, because you can always reconstruct the state of any lot at any point in history. This also means the current on-hand quantity for a lot is never stored directly, it is derived by summing signed quantities across every movement row referencing that lot, which is slower to read but far harder to silently corrupt than a mutable balance column.

Receiving, movements, and FEFO picking

Every physical event, a purchase receipt, an internal transfer, a pick for an outbound order, or a quarantine hold, writes a row to the movement ledger referencing either a unit or a lot. On the outbound side, regulated inventory almost always requires FEFO, first-expired-first-out, rather than the simpler FIFO used for non-perishable goods. FEFO prioritizes the stock closest to its expiry date regardless of when it arrived, which minimizes write-offs and keeps expired stock from ever reaching a customer. The picking query below shows the core logic: filter to available stock for the requested item, sort by expiry date ascending, and take only what the order needs.

-- Simplified schema for lot/serial-level stock ledger
create table stock_lot (
  id            bigint primary key generated always as identity,
  item_id       bigint not null references item(id),
  lot_code      text not null,
  mfg_date      date,
  expiry_date   date,
  unique (item_id, lot_code)
);

create table stock_unit (
  id            bigint primary key generated always as identity,
  lot_id        bigint not null references stock_lot(id),
  serial_no     text unique, -- null when item is lot-tracked only, not serialized
  status        text not null default 'in_stock' -- in_stock, reserved, shipped, quarantined
);

create table stock_movement (
  id            bigint primary key generated always as identity,
  unit_id       bigint references stock_unit(id),
  lot_id        bigint references stock_lot(id),
  qty           numeric not null,
  movement_type text not null, -- receipt, transfer, pick, ship, adjust
  warehouse_id  bigint not null references warehouse(id),
  ref_doc       text,          -- PO number, SO number, or transfer id
  moved_at      timestamptz not null default now()
);

-- FEFO pick candidate query: earliest expiry first, only usable stock
select su.id, sl.lot_code, sl.expiry_date, sl.item_id
from stock_unit su
join stock_lot sl on sl.id = su.lot_id
where su.status = 'in_stock'
  and sl.item_id = $1
order by sl.expiry_date asc, sl.id asc
limit $2;

Recall reporting: the query that matters most

The entire point of the schema above is to answer one question fast: given a batch or serial number, list every downstream location and customer that received it. A recall report needs to walk the movement ledger forward from receipt to shipment, which is straightforward if movements always reference the lot or unit id rather than a copied text field. The typical recall trace follows this sequence:

1. Resolve the lot or serial number to its internal lot_id or unit_id.
2. Query all movement rows referencing that id with movement_type of ship or transfer.
3. Join each shipment movement to its reference document to resolve the customer, sales order, and delivery date.
4. Aggregate into a single report grouped by customer, with quantity shipped and remaining on-hand quantity still in the warehouse for immediate quarantine.

Quarantine and disposition workflow

Traceability data is only useful if the ERP can act on it immediately. When quality control flags a lot, the system needs a quarantine status that blocks the lot from being picked for any new order without deleting its movement history, followed by a disposition workflow, return to supplier, destroy, or rework, that itself gets logged as a movement event. Treating quarantine as a status flag on the lot record, checked at pick time, is far more reliable than trying to physically remove stock from every open order the moment a hold is placed.

Practical implementation notes

A few decisions consistently separate traceability modules that hold up under a real audit from ones that fall apart the first time an auditor asks a follow-up question. These are lessons learned the hard way, usually the first time a real recall request lands on a Friday afternoon and the report has to be correct on the first try:

• Never let a movement row reference a lot or serial number by copied text. Always use a foreign key to the lot or unit table, so a correction to metadata like expiry date propagates everywhere instantly.
• Store both manufacturing date and expiry date on the lot, even when only expiry is used for FEFO, because shelf-life audits and supplier quality reviews need the manufacturing date independently.
• Index the movement ledger on lot_id, unit_id, and moved_at together. Recall queries are read-heavy and time-bounded, and a composite index is what keeps a query over millions of movement rows fast during an actual recall event.