By Isaac Muthui, CEO, Tamalaki Business Network Ltd. Originally published at factoraerp.com.
Most food factories store HACCP in two places that never meet. The plan lives in a document: CCPs, critical limits, corrective actions. The monitoring lives in paper sheets, chart recorders or a logger dashboard. When a pasteuriser dips below its limit at 2 a.m., the question a QA lead has to answer the next morning is a join across those two worlds: which batches passed that control point while it was out of limit, what was decided about each one, and has any of it already shipped?
If your data model cannot answer that with a query, the plant is relying on memory. This post walks through how we think about modelling HACCP CCP monitoring in an ERP for food and agro manufacturers. The operational checklist behind it is in the original article.
What the domain rules say
The requirements come from food safety texts, and they read surprisingly like a spec:
- Codex CXC 1-1969 (General Principles of Food Hygiene): critical limits "should be measurable or observable"; monitoring frequency should "limit the amount of product impacted by a deviation"; "where possible, monitoring of CCPs should be continuous"; records "should be signed or initialled by the person performing the monitoring and should also report the results and timing"; when monitoring was not continuous, the business "should determine what product may have been impacted by the deviation"; and verification "should be carried out by someone other than the person who is responsible for performing the monitoring and corrective actions". Source
- NACMCF/FDA HACCP Principles and Application Guidelines (1997): "critical limits should not be confused with operational limits"; corrective actions should "determine and correct the cause", "determine the disposition of non-compliant product" and "record the corrective actions"; example verification schedule with monthly record review. Source
- EAS 39:2001 (East African hygiene code): records shall include "a record of all critical parameters and tests carried out to assure the safety of the finished product" plus the calibration status of the devices used. Source
Read as requirements: limits are versioned data attached to a control point; checks are timestamped, attributed observations; gaps count as failures; deviations must resolve to a set of batches; dispositions need an owner; and review must be done by a different user.
The core entities
ControlPoint (id, step, line_id, hazard, control_measure, is_ccp)
CriticalLimit (id, control_point_id, parameter, min, max, unit,
operating_min, operating_max, source, valid_from, valid_to)
MonitoringPlan (control_point_id, method, frequency_minutes, role)
Check (id, control_point_id, instrument_id, value, observed_at,
recorded_at, recorded_by, within_limit)
Instrument (id, type, calibration_due, last_check_passed_at)
Deviation (id, control_point_id, started_at, ended_at, cause, status)
DeviationBatch (deviation_id, batch_id, hold_id, disposition, decided_by, decided_at)
Review (id, line_id, shift_start, shift_end, reviewed_by, reviewed_at)
A few design choices matter more than the table names.
1. Critical and operating limits are different columns
Codex and the NACMCF guidance both separate them. An operating limit breach is a prompt to adjust; a critical limit breach opens a Deviation. Keeping both on the same versioned CriticalLimit row means a chart can show both bands, and an operator sees "adjust" long before "stop".
2. Limits are versioned, never edited
When the HACCP team changes a limit after revalidation, insert a new row with valid_from and close the old one. Every Check is evaluated against the limit valid at observed_at. An auditor asking "what was the rule on 3 March?" gets an exact answer.
3. Record both observed_at and recorded_at
Codex wants records to report "the results and timing" of the activity. Storing when the check happened and when it was entered exposes end-of-shift backfilling. A large gap is a verification flag, not a crime, but it should be visible.
4. Missing checks are generated, not inferred
From MonitoringPlan.frequency_minutes and the line's running hours, generate expected check slots. A slot with no Check is an exception that needs a reason. Absence of data is the most common way HACCP records fail, and it is invisible unless you model what should exist.
5. A deviation resolves to batches by time overlap
If monitoring is continuous, the affected window is the time the value was outside limits. If it is periodic, Codex's rule applies: everything since the last good check is potentially affected.
-- batches that passed a control point during a deviation window
SELECT b.id, b.lot_no, b.status
FROM batch_step bs
JOIN batch b ON b.id = bs.batch_id
WHERE bs.control_point_id = :cp
AND tstzrange(bs.started_at, bs.ended_at)
&& tstzrange(:last_good_check_at, :deviation_ended_at);
Each result row becomes a DeviationBatch with a hold placed automatically, so the stock cannot be picked or dispatched until someone records a disposition.
6. Instrument failures widen the window
If a thermometer fails its calibration check, every Check that used it since last_check_passed_at is suspect. That is just another time-range query over the same tables, which is why instruments should be first-class entities rather than free text.
7. Review is a separate user, enforced
A Review row covers a line and shift. The constraint reviewed_by <> recorded_by for the checks in scope is simple to enforce in code and maps directly to the Codex requirement that verification is done by someone other than the monitor. Release of a batch can then require an approved review covering every shift the batch ran on.
What this buys you
With these joins in place, the questions that usually take a morning of searching become queries:
- Show every CCP check for lot 2410-07, with who did it and when.
- List all open deviations with batches still on hold.
- Which shifts have unreviewed CCP records?
- Which instruments were overdue for calibration when they were used?
None of this is novel computer science. The hard part is accepting that a food safety record is relational: limits, observations, time, batches and people. Model it that way and the HACCP binder becomes a view, not a separate world.
How are you modelling monitoring gaps in your own systems: as data, or as the absence of data?
The full operational HACCP CCP monitoring checklist, with references and an FAQ, is on the FactoraERP blog.
Top comments (0)