Lot Traceability Data Model: Lots, Batch Records and Genealogy

Key takeaways

• A lot traceability model needs few tables: item, lot, batch record, consumption, output, handling unit and shipment line. The consumption table does most of the work.
• Every consumption record is an edge from an input lot to every output lot of its batch, with a quantity. Together the edges form the genealogy graph you walk backward and forward.
• Lot status is a hard gate: a lot in Quarantine, On hold, Rejected or Expired must never be consumed or shipped.
• A trace you cannot reconcile by mass balance is incomplete. Unexplained kilograms are product whose location you do not know.
• EU food law asks for one step back and one step forward. Internal input to output links are what keep a recall to the lots that really contain the problem.

What this model has to answer

A traceability system exists to answer two questions fast, under pressure, with numbers you can defend.

• Backward: which supplier lots are inside this finished lot?
• Forward: which customers received product that contains this raw material lot, and how much?

The model below works for batch processes (food, chemicals, compounding) and for discrete parts made from coil, bar or resin. For the difference between the two words, see batch vs lot production.

This page covers the lot side only. The order header, operations, BOM lines and quantity reports are in our production work order data model, and this model plugs into its material issue and lot_no fields.

The entities and their fields

1. Item

The item master says whether a material is lot tracked at all. Decide it per item, not per plant habit.

2. Lot

One table holds every lot: raw material, intermediate, rework and finished. A single table keeps the genealogy query simple, because every node in the graph has the same shape.

Never overwrite the supplier's lot number with your own. Keep both, because the supplier will name its own number in a recall notice and you must find it in seconds.

One supplier lot can arrive in two deliveries. Give each receipt its own internal lot_id and let both carry the same supplier_lot_no, so a search on the supplier number returns both.

3. Batch record

The batch record is the production order seen from the lot side. It needs only a few fields beyond the order itself.

4. Consumption (the genealogy edge)

This is the table that makes traceability possible. One row per input lot per batch, never one row per item.

If a batch uses the last 900 kg of one flour lot and 600 kg of the next, that is two consumption rows. Booking 1,500 kg against one lot is the most common way genealogy goes wrong.

5. Output, handling unit and shipment line

For most output, one batch makes one finished lot, and lot_id is the join key to the batch record. Serial numbers are only needed where each unit must be traced alone; see lot vs serial tracking.

The genealogy graph

Treat every lot as a node and every consumption record as a directed edge from the input lot to every output lot of that batch, and B-2454 below has two. The quantity on the edge says how much went across.

Because every batch creates a new lot, the graph has no cycles. Keep it that way: a lot can never be consumed into itself, and rework always gets its own lot_id.

A single chain is traceability, while the full branching tree behind one unit is genealogy. Our article on traceability vs genealogy explains when the extra depth pays off.

Backward trace: what is inside this finished lot?

1. Start at the finished lot and find its batch_id.
2. Collect every consumption row for that batch. Each input_lot_id is one step back.
3. For each input lot of type produced or rework, repeat from step 1.
4. Stop at lots of type received. Their supplier_id, supplier_lot_no and coa_ref are the answer.

Forward trace: who got this raw material?

1. Start at the suspect lot (or every lot with that supplier_lot_no).
2. Find consumption rows where it is the input, then the output lots of those batches.
3. Repeat from each output lot until no more consumption rows are found.
4. Join every lot reached to shipment_line, and to handling_unit for pallets, to get customers and quantities. Whatever is not shipped is in stock, WIP, retained samples or scrap, and the mass balance tells you which.

In SQL this is one recursive query (WITH RECURSIVE) over the consumption table. Add a depth counter and a visited list, so a bad data entry cannot loop forever.

One rule surprises people: a blended output lot is affected as a whole. If 900 kg of suspect flour went into a 2,000 kg mix, all 2,000 kg are suspect, because you cannot separate it again.

Lot status rules

The rules that should be enforced by the system, not by a work instruction:

• A consumption or shipment is refused unless the lot is Released and not past expiry.
• No negative stock on a lot-tracked item. Negative stock means someone booked the wrong lot.
• Every status change stores who, when and why. Auditors ask for that history.
• When QA puts a lot On hold, the system should list every downstream lot from the forward trace, so QA can decide which to hold too.

How a hold is run as a process, with owners and deadlines, is covered in quality hold process design.

Mass balance: the check that proves the trace

A trace lists where material went. Mass balance proves that the list is complete.

Batch variance = inputs minus (good output + rework + scrap + samples)

Variance % = batch variance ÷ inputs × 100

Set a tolerance per process. A dry blend with no evaporation can hold a tight band such as ±0.5%, while a bake or dryer needs an expected moisture loss built into the formula first.

Run the same check on each raw material lot: received minus consumed minus on hand minus recorded losses should be close to zero.

Worked example: one flour lot, 9 finished lots, 6 customers

A mill notifies a dry mix plant that its flour lot M-2409-117 may be contaminated. A search on supplier_lot_no finds one receipt: 24,000 kg, booked as internal lot RM-0917-03, with its COA on file.

1. Forward trace to batches

Each pancake mix batch is 2,000 kg: 1,500 kg flour, 300 kg sugar, 150 kg milk powder, 40 kg raising agent and 10 kg salt.

Batches B-2451 to B-2457 used 1,500 kg each from RM-0917-03. B-2458 was the last batch before the line switched to a second mill's flour, so it used 900 kg from RM-0917-03 plus 600 kg of the new lot.

• Flour consumed: 7 × 1,500 + 900 = 11,400 kg
• Expected on hand: 24,000 minus 11,400 = 12,600 kg

2. Reconcile the raw lot

The 12,570 kg on hand goes On hold at once. The raw lot balance is well inside tolerance.

3. Reconcile each batch

The other five batches match B-2451. B-2455 fails the ±0.5% band: 56 kg of mix, 56 packs if you sell 1 kg packs, are not in any record.

That is why mass balance matters in a recall. Until the 56 kg are found, you cannot say the product is not on a shelf somewhere.

4. Follow the rework

The 60 kg from B-2454 were underweight packs, reopened and booked as rework lot RW-0921-01. A week later that lot was consumed into batch B-2461, made with clean flour.

B-2461 is therefore in scope. A trace that only follows the flour item would miss it, because the path runs flour, B-2454, RW-0921-01, B-2461.

5. Forward trace to customers

Check the total from the output side: six batches at 1,980 kg, plus 1,920 (B-2454) and 1,930 (B-2455), is 15,730 kg. Add B-2461 at 1,980 kg and you get 17,710 kg, which matches the shipments plus stock.

So the answer is: 11,400 kg of suspect flour inside 17,710 kg of finished product, 16,930 kg at six customers, 780 kg in stock, plus 56 kg unaccounted for in B-2455.

Without the consumption records, the only safe answer would have been every batch made since the flour arrived. The 2 kg retained samples from each batch are now your best evidence, so send them to the lab before anything is destroyed.

Edge cases that break simple models

Partial lots and lot changes mid-batch

B-2458 shows the pattern: two input lots, two consumption rows. The second mill's lot is now linked to B-2458 as well, so a future trace of that lot also returns B-2458.

Rework into a later batch

Book rework as a new lot with its own lot_id, created by the batch it came from. Never add it back into stock under the original finished lot number, or the path breaks.

For when to rework and when to scrap, see scrap vs rework.

One lot across two orders

A raw lot consumed by two production orders simply has edges to two batches. Nothing special is needed, as long as consumption is booked per batch and not per day.

Blending and continuous feeds

Silos and tanks that are topped up while running mix lots together.

Make each fill a new silo lot with edges from the delivery lot and the residue it landed on. Then link every silo lot present during a batch's start_ts to end_ts window.

Customer returns

A return comes back as a lot of type returned, linked to the original lot. It starts in Quarantine and never rejoins released stock without a decision.

What regulators and customers expect

In EU food law, Article 18 of Regulation (EC) No 178/2002 requires food and feed businesses to identify who supplied them and which businesses they supplied. This is the one step back, one step forward principle, and the information must be available to the authorities on demand.

The forward step covers business customers, not final consumers. Article 18 does not spell out internal links between input and output lots, but without them the one step forward covers everything you shipped.

Food contact materials, such as packaging and plastic parts that touch food, have a similar traceability duty under Article 17 of Regulation (EC) No 1935/2004.

Once a trace shows unsafe food has left your control, Article 19 of Regulation (EC) No 178/2002 requires you to withdraw it and inform the competent authority. For inputs of animal origin, such as milk powder, Implementing Regulation (EU) No 931/2011 also requires lot, quantity and date details from suppliers.

Beyond the law, food safety certification schemes and some automotive and industrial customers ask for documented traceability tests, often at least once a year. For food, see our HACCP compliance guide.

A common good practice target for a mock recall is to reach full traceability, with quantities reconciled, within 4 hours. Treat it as a benchmark to test against, and check the exact rule in the standard your customers audit you to.

How Fabrico helps

Fabrico is not a lot traceability, MES or ERP lot system. It does not store lots, consumption records or shipments, and it does not run backward or forward traces.

Fabrico is an OEE platform with a full CMMS. It records machine history, downtime and short stops through PLC connections, IoT sensors and AI cameras.

On the maintenance side, your team creates maintenance work orders, runs preventive plans and records the spare parts consumed per work order, from web or mobile apps.

That matters in an investigation. Take a batch's start_ts and end_ts from your lot system, put them next to Fabrico's machine timeline, and your team can see which stops, repairs and part changes happened while that lot ran, and ask the AI assistant about that machine's history in plain language.

For how machine data and traceability records support audits together, read our audit-ready OEE traceability strategy.

Want to see the machine side of your next investigation? Book a 30 minute demo with a Fabrico consultant, no commitment, or contact us with your questions.