# Batch and Serial Tracking in ERP: Full Traceability > How to design batch and serial-number traceability into a custom ERP, covering receiving, movements, FEFO picking, and recall reporting for regulated inventory. URL: https://www.matthewswong.com/en/blog/erp-batch-serial-number-traceability/ - Author: Matthews Wong (https://www.matthewswong.com/en/about/) - Published: 2026-07-04 - Updated: 2026-07-04 - Category: ERP 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. > Do not force instance-level serialization onto every item category just because a few products need it. Serial-level tracking multiplies the number of rows your movement ledger writes on every transaction, and most warehouse teams cannot scan a hundred individual serials during a rush pick. Reserve it for items where regulation or warranty actually requires unit-level history. ## 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. Concept Lot-level example Instance-level example Identifier Batch LOT-2026-0311 Batch LOT-2026-0311 + serial SN-88213 Quantity per record 500 units in one lot row 1 unit per stock_unit row Recall query grain All shipments containing the lot The exact unit and its owner ## 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. > FEFO logic has an edge case that trips up a lot of first implementations: partially expired or unusually short-dated lots that arrive from a supplier ahead of older stock. If your picking query only sorts by expiry date without also checking a minimum shelf-life threshold at time of shipment, you can ship a lot that expires before it even reaches the customer. Add a configurable minimum-remaining-shelf-life rule per item category, not just a raw expiry-date sort. ## 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. > A well-designed traceability module pays for itself the first time a recall report that used to take a warehouse team two days to compile by hand runs in under a minute from a single batch number lookup, with a full customer distribution list attached. ## 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. ## Sources - Traceability — Wikipedia - First Expired, First Out (FEFO) — Wikipedia - Product recall — Wikipedia > **Key takeaway:** Batch and serial tracking decides whether a product recall takes an afternoon or a week. An ERP that records which batch went into which shipment can answer, in seconds, exactly which customers are affected — and that query is the whole reason the tracking overhead is worth carrying. ## Source links - https://en.wikipedia.org/wiki/Traceability - https://en.wikipedia.org/wiki/First_Expired,_First_Out - https://en.wikipedia.org/wiki/Product_recall ## FAQ ### What is the difference between batch traceability and serial number traceability in ERP? Batch traceability tracks a group of units produced or received together under one lot number, so a single record covers hundreds or thousands of items. Serial number traceability assigns a unique identifier to every individual unit in addition to its lot. Most ERPs use batch tracking for raw materials and packaged goods, and reserve serial tracking for high-value or safety-critical items like medical devices. ### Why does FEFO matter more than FIFO for regulated inventory? FIFO picks stock based on receipt order, which works fine for non-perishable goods but can ship an older, near-expiry lot after a fresher one arrives. FEFO picks based on expiry date regardless of receipt order, which is what actually prevents expired stock from reaching a customer. Pharmaceutical, food, and chemical businesses generally require FEFO because shelf life, not arrival date, determines usability. ### How do I design a database schema that supports both lot-level and serial-level tracking? Separate the concepts into three tables: a lot table holding batch metadata like manufacturing and expiry dates, a unit table that optionally carries a unique serial number and references its parent lot, and an append-only movement table that logs every receipt, transfer, and shipment against a lot or unit id. This lets an item category use lot-only tracking simply by never populating the serial number column. ### Can a recall report be generated automatically from ERP movement data? Yes, if every movement row references the lot or unit by foreign key rather than a copied text field. A recall report resolves the batch or serial number to its internal id, queries every shipment or transfer movement referencing that id, joins to the originating sales order or delivery, and aggregates by customer. Done correctly, this turns a manual multi-day recall trace into a query that runs in seconds. ### What happens when a lot fails quality control after it has already been received into stock? The lot should move into a quarantine status on the lot record itself, which blocks it from being picked for any new order without deleting its movement history. From there a disposition workflow, such as return to supplier, destruction, or rework, gets logged as its own movement event so the full lifecycle of the quarantined lot remains auditable. --- - HTML version: https://www.matthewswong.com/en/blog/erp-batch-serial-number-traceability/ - Bahasa Indonesia: https://www.matthewswong.com/id/blog/erp-batch-serial-number-traceability/ - Site index for agents: https://www.matthewswong.com/llms.txt - Sitemap: https://www.matthewswong.com/sitemap.xml - API description (OpenAPI): https://www.matthewswong.com/openapi.json