By Isaac Muthui, CEO, Tamalaki Business Network Ltd. Originally published at factoraerp.com.
Most food factories that monitor cold rooms end up with one of two things: a paper sheet with two readings a day, or a logger dashboard full of lines that nobody connects to stock. Neither answers the question a QA lead gets after a compressor fails overnight: which batches were in that room while it was too warm, what did we decide about each one, and has any of it already left the building?
A temperature reading on its own decides nothing. What matters is the excursion: a period when a location was outside its limit for longer than its tolerance, the stock that was there during that period, and the decision made about that stock. This post walks through how we think about modelling that 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 come from food safety texts, not from software people:
- Codex CXC 1-1969 (General Principles of Food Hygiene) asks for "tolerable limits for time and temperature variations", says temperature control systems should be "monitored and recorded", that devices should be "checked for accuracy and calibrated at regular intervals", and that "where possible, monitoring of CCPs should be continuous". Source
- Codex CXC 8-1976 (quick frozen foods) sets -18°C or colder throughout the cold chain, never warmer than -12°C in the warmest package during distribution, says warm loads should be "identified and sorted immediately" with delivery and sale suspended, asks for a contingency plan for power losses, and wants records kept "for a period that exceeds the shelf-life of the product". Source
- EAS 39:2001 (East African hygiene code) requires records of storage conditions, a working, fuelled refrigeration unit before loading, and recommends temperature recorders on refrigerated vehicles. Source
Read as a spec: limits belong to a product in a location, tolerance is a duration, readings are a time series per location, gaps are failures, excursions must link to stock, and stock in an excursion needs a status that blocks dispatch until someone decides.
The data model
A simplified version of the entities:
Location id, type (cold_room | freezer | chiller | dock | vehicle), volume_m3
Sensor id, location_id, position_note, calibrated_until, accuracy_c
TempLimit id, location_id, product_class, min_c, max_c, tolerance_minutes
Reading sensor_id, read_at, value_c -- append-only time series
Excursion id, location_id, started_at, ended_at, peak_c, kind (high | low | no_data),
status (open | assessed | closed), owner_id
ExcursionStock excursion_id, lot_id, qty, minutes_exposed
Decision id, excursion_id, lot_id, outcome (release | reprocess | downgrade | destroy),
decided_by, decided_at, reason
StockMove lot_id, from_location, to_location, moved_at, qty
Lot id, product_id, batch_id, status (available | on_hold | released | rejected)
Three design choices matter more than the rest.
1. Tolerance is a duration, not a threshold. A cold room door opens during loading and the air spikes for three minutes. Alarming on every spike trains people to ignore alarms. A TempLimit carries tolerance_minutes, and an Excursion opens only when readings stay outside the limit longer than that. Codex talks about "tolerable limits for time and temperature variations"; both halves matter.
2. No data is an excursion type. If a sensor stops reporting for longer than its expected interval, that is kind = no_data. A flat logger battery during a power cut is exactly when you most need the record. Treating silence as normal is how a weekend outage becomes "we have no idea".
3. Exposure comes from stock moves, not guesses. Which lots were in the room between 01:10 and 05:40? You can answer that only if every move into and out of cold locations is recorded with a timestamp. Then exposure is a join on time intervals, not a memory exercise.
The query that matters
-- Lots present in a location during an excursion window
SELECT m.lot_id
FROM stock_moves m
WHERE m.to_location = :location
AND m.moved_at < :excursion_end
AND NOT EXISTS (
SELECT 1 FROM stock_moves o
WHERE o.lot_id = m.lot_id
AND o.from_location = :location
AND o.moved_at > m.moved_at
AND o.moved_at <= :excursion_start
);
Every lot returned goes into ExcursionStock and flips to on_hold. Dispatch, picking and production issue all check lot status, so held stock cannot leave by mistake. Each held lot then needs a Decision before it can move again, and the excursion cannot close while any lot is undecided.
Detecting excursions from a time series
The detector is simple, which is the point:
def update_excursion(location, readings, limit, now):
out = [r for r in readings if not (limit.min_c <= r.value_c <= limit.max_c)]
last_seen = max((r.read_at for r in readings), default=None)
if last_seen is None or (now - last_seen).total_seconds() > location.expected_interval_s * 3:
open_or_extend(location, kind="no_data", at=now)
elif out and minutes_continuous(out, now) >= limit.tolerance_minutes:
open_or_extend(location, kind="high" if out[-1].value_c > limit.max_c else "low",
at=out[0].read_at, peak=max(r.value_c for r in out))
else:
close_if_open(location, at=now)
Run it every minute. The hard part is not the code. It is getting tolerance_minutes, sensor positions and alarm recipients right for each location, which is a QA decision, not an engineering one.
Alarms are a people problem
An excursion that sends a message to a group chat at 02:00 and gets no reply is not a control. Model the escalation chain explicitly: primary on-call, how long before escalating, secondary, and record who acknowledged and when. Unacknowledged alarms are themselves worth reporting every morning.
What to keep simple
- Store raw readings append-only. Never let anyone edit a reading; corrections go in a note on the excursion.
- Keep limits, tolerances and escalation rules as configuration, because they come from the HACCP plan, customers and regulators, not from code.
- Store each sensor's calibration date and accuracy with it, and flag readings from overdue sensors. Codex suggests air recorders accurate to ±2°C and product probes to ±0.5°C, so the same 1°C overshoot means different things from different instruments.
- Keep records at least as long as the product's shelf life. That is a retention policy, not an afterthought.
The general lesson: in food manufacturing, the useful record is not "the room was at 4°C this morning" but "this room was out of limit for this long, these lots were in it, this person decided this, and here is where the stock is now". Once excursions are events linked to lots, "what happened during the blackout?" becomes a query.
The full 10-step cold chain temperature monitoring checklist, with the Codex, EAS and Kenyan dairy regulation references, is on the FactoraERP blog.
Isaac Muthui, CEO, Tamalaki Business Network Ltd
