Shelf-Life Is Versioned Config, Expiry Is a Derived Field: Modelling Shelf-Life Studies, Lot Expiry and FEFO in a Food ERP

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

In most food ERPs, an expiry date is a column on a lot. Someone types it at packing, or the system adds a fixed number of days to the production date, and that's the end of it. But the number of days is not a constant. It is the output of a shelf-life study, it is only valid for a particular recipe, pack and storage condition, and it should change when any of those change. If the data model can't express that, the date printed on the pack quietly drifts away from the evidence behind it.

This post walks through how we think about modelling shelf-life and expiry 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:

• Kenya's Food Hygiene Regulations (Cap. 254), regulation 14: each container marked "to identify the producing factory and the lot", and specified products carrying a date marking. Source
• Codex CODEX STAN 1-1985, 4.7: day and month for products lasting up to three months, month and year beyond that, and storage conditions on the label "if the validity of the date depends thereon". Source
• Codex CXC 1-1969, 13.4: records retained "for a period that exceeds the shelf-life of the product". Source
• FSAI Guidance Note 18 (an Irish benchmark, not Kenyan law): set shelf-life before launch, redo it on "modification or reformulation", test at least three replicates from three batches per interval where no plan is specified, apply and document a margin of safety, and verify periodically. Source

Translated into engineering terms: shelf-life is versioned configuration with provenance, expiry is a derived field on a lot, and changes to recipe or packaging are invalidation events.

Shelf-life belongs to a product version, not a product

ShelfLifeSpec (id, item_id, recipe_version_id, pack_spec_id, storage_condition,
               date_type, days, margin_days, study_id, approved_by, valid_from, valid_to)
               date_type IN ('use_by','best_before')
ShelfLifeStudy (id, item_id, recipe_version_id, design_json, lab_id, decision_note, closed_at)

The spec points at the recipe version and the pack specification it was proven for, and at the study that justifies it. days is what goes on the label; margin_days is how much was taken off the study result and why lives in decision_note. When either the recipe or the pack spec gets a new version, the old spec doesn't silently carry over. It needs a review.

Expiry is derived at the moment the lot is made

expiry_date = production_date(lot, factory_timezone) + spec.days

Two details matter. First, compute it from the production date in the factory's time zone, not the server's, or a lot packed at 1 a.m. in Nairobi gets the wrong date. Second, store the derived date and the spec id on the lot, so that a later change to the spec doesn't rewrite history on lots already in the market.

In FactoraERP today, stock items have a trackExpiry flag and a shelfLifeDays value, and finished-goods lots get an expiry date calculated from their production date in the factory's time zone.

Invalidation: changes that should trigger a review

FSAI lists the triggers in plain language: no evidence for an existing date, and modification or reformulation of the food or its production. That's an event rule:

on RecipeVersionPublished(item_id)  -> flag ShelfLifeSpec(item_id) as 'review_required'
on PackSpecChanged(item_id)          -> flag ...
on SupplierApproved(key_ingredient)  -> flag ...
on ComplaintLogged(type='spoilage', lot.expiry_date > complaint_date) -> attach to ShelfLifeSpec as evidence

The last rule is the one most systems miss. A spoilage complaint before the printed date is shelf-life evidence, and it should land on the spec, not only in the complaint log.

FEFO is where the date earns its keep

An expiry date nobody uses for stock rotation is just ink. Dispatch should pick first-expired, first-out, and any exception should be a recorded decision:

-- lots skipped in a dispatch that expire earlier than the lot picked
SELECT l.lot_code, l.expiry_date
FROM lot l
WHERE l.item_id = :item_id
  AND l.status = 'sellable'
  AND l.qty_available > 0
  AND l.expiry_date < :picked_lot_expiry;

In FactoraERP, dispatching out of first-expiry order needs an operations manager or admin and a written reason, and the override is stored on the sale with who and when.

Study results are measurements, not a PDF

A durability study is a grid: batch × replicate × interval × test. FSAI recommends reading it at a high percentile (90th or 95th) rather than the mean, which needs at least three replicates per time point. That's a query, not an opinion:

SELECT interval_day, test,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY value) AS p95,
       count(*) AS n
FROM shelf_life_result
WHERE study_id = :study_id
GROUP BY interval_day, test
HAVING count(*) >= 3
ORDER BY interval_day;

Takeaways

• Version shelf-life by recipe, pack spec and storage condition, and link it to the study that proves it.
• Derive expiry at lot creation in the factory's time zone, and store the spec id with it.
• Treat recipe, packaging and supplier changes as invalidation events.
• Route early spoilage complaints to the shelf-life spec as evidence.
• Enforce FEFO and record every override with who, when and why.

The full 10-step operational checklist, with references to Kenya's food hygiene regulations, Codex CXS 1, CXC 1 and CXG 61 and FSAI Guidance Note 18, is on the FactoraERP blog.