Records Are Append-Only: Modelling Signed, Contemporaneous Food Safety Records and Corrections in a Food ERP

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

Most food factories still run on paper records, and most of the software that replaces them makes one mistake: it treats a record as a row you can edit. In a food factory a record is evidence. The pasteuriser reading at 14:10 on Tuesday, who took it, what the limit was and what happened when it was out. If someone can quietly change that row, it is no longer evidence.

This post walks through how we think about modelling food safety records 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:

• Codex CXC 1-1969: CCP records "should be signed or initialled by the person performing the monitoring and should also report the results and timing of the performed activity"; records should be kept "for a period that exceeds the shelf-life of the product or as determined by the competent authority" (13.4); "records can also be maintained electronically" (19.12). Source
• EAS 39:2001: procedures for "identification, collection, indexing, filing, storage, maintenance and disposition of hygiene records" (14.2); records of food safety processes inspected at least daily by a suitably qualified person and signed (4.3.6). Source
• 21 CFR 117.305 (US benchmark): "actual values and observations", "accurate, indelible, and legible", "created concurrently with performance of the activity", with date, time where appropriate, signature or initials, and lot code where appropriate. Source

Translated into engineering terms: entries are append-only, carry an actor and a timestamp, store a typed value next to the limit that applied at the time, link to a lot, and get a separate review event.

The core entities

FormTemplate     (id, code, version, effective_from, superseded_at)
CheckDefinition  (id, form_template_id, name, value_type, unit,
                  limit_min, limit_max, is_ccp)
RecordEntry      (id, check_definition_id, lot_id, value, observed_at,
                  recorded_at, recorded_by, supersedes_entry_id NULL,
                  correction_reason NULL)
Review           (id, form_instance_id, reviewed_by, reviewed_at, outcome)
Deviation        (id, record_entry_id, product_action, corrective_action,
                  decided_by, closed_at NULL)

A few design choices matter more than the table names.

Snapshot the limit. Copy limit_min and limit_max onto the entry (or reference an immutable CheckDefinition version). When the HACCP team changes a critical limit next year, last year's records must still show the limit that applied when the reading was taken.

Two timestamps. observed_at is when the check happened; recorded_at is when it was typed in, set by the server. The gap between them is the most honest data-quality metric you will ever have. A shift where every reading was recorded at 17:58 tells you something no audit will.

Corrections are new rows. Never UPDATE a value. A correction inserts a new entry with supersedes_entry_id and a reason. The "current" value is the latest entry in the chain; the history is the chain. This is the database version of crossing out with a single line and initialling.

Out-of-limit creates work. If a value falls outside its snapshotted limits, create a Deviation in the same transaction. Product on that lot should not be releasable while a deviation is open.

Review is an event, not a flag. A boolean reviewed loses who reviewed and when. A Review row keeps both, and lets you report on records not reviewed within a day.

The query that matters

The test of the model is a recall question: give me everything about lot 2417.

SELECT cd.name, re.value, re.limit_min, re.limit_max,
       re.observed_at, re.recorded_by, d.corrective_action, r.reviewed_by
FROM record_entry re
JOIN check_definition cd ON cd.id = re.check_definition_id
LEFT JOIN deviation d ON d.record_entry_id = re.id
LEFT JOIN review r ON r.form_instance_id = re.form_instance_id
WHERE re.lot_id = :lot
  AND NOT EXISTS (SELECT 1 FROM record_entry x WHERE x.supersedes_entry_id = re.id)
ORDER BY re.observed_at;

If that takes minutes, the system works. If it takes two days of pulling files, it does not, however many records you keep.

Things we learned

• Factories still need paper fallback. Design for a later transcription with observed_at entered by hand and recorded_at set by the server, so the gap stays visible.
• Ticks are a smell. A value_type of boolean should be rare for anything with a measurable result.
• Retention belongs to the record type, not the database. Store a retention rule per form and make disposal a deliberate, logged action.

The full operational checklist, with references, is in the original article.