Rework Flows Backwards: Modelling Rework Lots, Allergen Rules and Recursive Traceability in a Food ERP

By Isaac Muthui, CEO, Tamalaki Business Network Ltd. Originally published at factoraerp.com.

In most inventory systems, material flows one way: raw material in, finished goods out. Food factories break that assumption every day. Underweight packs are opened and tipped back into the hopper. Broken biscuits are milled into the next mix. A tank of milk is pasteurised a second time. Each of those is rework: finished or part-finished product flowing backwards into production.

If your data model treats rework as a quantity adjustment or a note on a batch sheet, you lose the one thing a food business needs most in a recall: the ability to say which batches contain material from which other batches. This post walks through how we think about modelling rework in an ERP for food and agro manufacturers in East Africa. The operational checklist behind it is in the original article.

What the domain rules say

The requirements read like a spec:

• 21 CFR 117.3 defines rework as "clean, unadulterated food that has been removed from processing for reasons other than insanitary conditions or that has been successfully reconditioned by reprocessing and that is suitable for use as food". Source
• 21 CFR 117.80: "material scheduled for rework must be identified as such"; "rework that contains food allergens, must be identified and held in a manner that prevents allergen cross-contact"; adulterated food may only be incorporated into other food after being reconditioned by a proven method or "reexamined and subsequently found not to be adulterated". Source
• EAS 39:2001 (East African hygiene code): food contaminated by pathogenic organisms "shall not be blended with uncontaminated product, as a means of bringing the food within specification". Source
• Codex CXC 1-1969: the HACCP flow diagram should show "where applicable reworking and recycling take place", and lot identification is "essential in product recall". Source

Translated: rework must have an identity, a status, an allergen profile and a provenance; it can only be consumed after an approval; some sources can never become rework; and the graph of which batch fed which must be queryable in both directions.

The core entities

Batch            (id, product_id, lot_no, produced_at, status)
ReworkLot        (id, lot_no, product_id, reason_code, safety_related,
                  qty_created, uom, created_at, created_by,
                  status, use_by, disposition, decided_by, decided_at)
ReworkSource     (rework_lot_id, source_batch_id, qty)
ReworkIssue      (rework_lot_id, target_batch_id, qty, issued_at, issued_by)
ProductAllergen  (product_id, allergen_code)
ReworkRule       (target_product_id, source_product_id, max_pct)
ReasonCode       (code, label, reworkable, requires_retest)

A few design choices matter more than the table names.

1. Rework is a lot, not an adjustment

The moment material is pulled from the line, create a ReworkLot with its own lot number and print a label. The US rule says material scheduled for rework "must be identified as such"; in data terms, it needs a primary key. A negative adjustment on the source batch plus a positive adjustment somewhere else destroys provenance.

2. Status is a state machine with an approval gate

CREATED -> ON_HOLD -> RELEASED -> PARTIALLY_USED -> USED
                  \-> DIVERTED
                  \-> DESTROYED
          (any open state) -> EXPIRED  (when now() > use_by)

Only RELEASED or PARTIALLY_USED lots can be issued. decided_by must be a QA role, and the transition out of ON_HOLD is the only place disposition is set. This mirrors the Codex options: reprocess, divert or destroy.

3. Reason codes encode what can never be rework

ReasonCode.reworkable = false for things like foreign body findings, pest contamination or unknown-history returns. requires_retest = true for safety-related reasons, so a lot created from a failed test cannot move to RELEASED without a linked passing result. The rule "contaminated product is not rework until reconditioned and found suitable" becomes a constraint, not a training slide.

4. Allergen matching is a set comparison

A rework lot inherits the allergens of its source product(s). Issuing it into a target batch is allowed only if the rework's allergen set is a subset of the target product's declared allergens:

-- allergens in the rework that the target product does not declare
SELECT pa.allergen_code
FROM rework_source rs
JOIN batch sb           ON sb.id = rs.source_batch_id
JOIN product_allergen pa ON pa.product_id = sb.product_id
WHERE rs.rework_lot_id = :rework_lot
EXCEPT
SELECT allergen_code FROM product_allergen WHERE product_id = :target_product;
-- any row returned => block the issue

5. No dilution, enforced at issue time

Two checks run when a ReworkIssue is created. First, ReworkRule.max_pct: the total rework issued into a batch cannot exceed the allowed share of the batch input. Second, if the rework lot is safety_related and lacks a linked passing re-test, the issue is rejected. That is EAS 39's "shall not be blended ... to bring the food within specification" as a database rule.

6. Provenance is a graph; query it recursively

Rework can contain rework. A recall question ("which finished batches contain anything from batch 2410-07?") is a traversal:

WITH RECURSIVE downstream(batch_id) AS (
  SELECT :problem_batch
  UNION
  SELECT ri.target_batch_id
  FROM downstream d
  JOIN rework_source rs ON rs.source_batch_id = d.batch_id
  JOIN rework_issue  ri ON ri.rework_lot_id   = rs.rework_lot_id
)
SELECT b.lot_no, b.status FROM downstream d JOIN batch b ON b.id = d.batch_id;

The same query reversed gives upstream provenance for a complaint. Without ReworkSource and ReworkIssue as separate tables, this question has no answer except "check the notebooks".

7. Expiry propagates, never extends

A rework lot's use_by and the receiving batch's expiry must not be later than the earliest expiry among its sources. Compute it; do not let users type it. This matters in markets where regulators have seized product repackaged with fresh dates.

What this buys you

• Show every rework lot on hold older than 24 hours.
• Which batches this week contain rework, and how much, by product?
• Recall: every finished batch downstream of a problem batch, through any number of rework hops.
• Rework cost and rate by reason and shift, from the same tables.

None of this is exotic. It is a lot table, two link tables, a state machine and a handful of constraints. The hard part is deciding that rework deserves first-class modelling at all.

FactoraERP is an ERP for food and agro manufacturers in East Africa, built by Tamalaki Business Network Ltd. Full checklist: Rework Control Checklist.