By Isaac Muthui, CEO, Tamalaki Business Network Ltd. Originally published at factoraerp.com.
Most factory software treats a measurement as a number. A batch record stores pasteurisation_temp = 72.4, an intake record stores net_kg = 1240, a pack-weight sample stores grams = 503. Nothing in the schema says which instrument produced the number, or whether that instrument was trustworthy at the time.
That is fine until a QA officer finds that a thermometer has been reading high, or a weighbridge fails its check. Then the questions arrive all at once: which batches did that thermometer sign off? Which supplier deliveries did that scale weigh? Since when? The data to answer them usually exists somewhere, but not in a shape you can query.
This is a data modelling problem before it is a quality problem.
What the standards expect
The Codex General Principles of Food Hygiene (CXC 1-1969) says temperature monitoring and recording devices should be checked for accuracy and calibrated at regular intervals or as needed, and lists "calibrating or checking the accuracy of instruments used for monitoring and/or verification" among HACCP verification activities. The East African hygiene code, EAS 39:2001, says instruments monitoring critical parameters should be calibrated at appropriate intervals so their accuracy stays within defined limits, and lists the calibration status of devices used to assure food safety among the records a plant must keep.
For scales used in trade, Kenya adds legal metrology. The State Department for Trade says every piece of equipment used for trade must bear a valid stamp of verification under the Weights and Measures Act (Cap. 513), and that equipment suspected to be inaccurate should be taken out of use.
Translated into requirements for a system:
- Every reading must be attributable to an instrument.
- Every instrument must have a status at any point in time: in calibration, due, overdue, failed, out of service.
- A failed check must be able to find every record that depended on that instrument since its last good check.
- Using an overdue or failed instrument should be hard, not just discouraged.
A minimal schema
This is a design sketch, not any specific product's schema. Postgres-flavoured SQL:
create table instrument (
id text primary key, -- the tag on the device, e.g. 'TH-PAST-01'
kind text not null, -- scale, thermometer, ph_meter, metal_detector, ...
location text not null,
use_flags text[] not null, -- {'ccp','trade','costing'}
tolerance numeric not null, -- in the instrument's unit
unit text not null,
cal_interval interval not null, -- external calibration
check_interval interval not null, -- in-house check, e.g. '8 hours'
in_service boolean not null default true
);
create table calibration_event ( -- external, by an accredited lab or W&M inspector
id bigserial primary key,
instrument_id text references instrument(id),
performed_at timestamptz not null,
provider text not null,
certificate text not null, -- certificate or verification number
as_found_error numeric, -- error before adjustment
as_left_error numeric, -- error after adjustment
uncertainty numeric,
passed boolean not null
);
create table check_reading ( -- in-house: check weight, reference thermometer, test piece
id bigserial primary key,
instrument_id text references instrument(id),
checked_at timestamptz not null,
expected numeric not null,
observed numeric not null,
checked_by text not null,
passed boolean not null -- set by trigger from instrument.tolerance
);
passed on a check should be derived from the instrument's tolerance, not typed in by the operator. Generated columns cannot reference another table, so set it in a trigger:
create or replace function set_check_result() returns trigger as $$
begin
new.passed := abs(new.observed - new.expected) <=
(select tolerance from instrument where id = new.instrument_id);
return new;
end $$ language plpgsql;
The important detail is as_found_error. Most teams file calibration certificates and only look at the "as left" result: the instrument is fine now. The "as found" value tells you whether it was fine before, which is the question that matters for product already made.
Every measurement points at an instrument
The change that unlocks everything is a foreign key on the measurement itself:
create table measurement (
id bigserial primary key,
instrument_id text not null references instrument(id),
taken_at timestamptz not null,
value numeric not null,
context_type text not null, -- 'batch_ccp', 'goods_receipt', 'pack_sample', ...
context_id text not null -- batch number, GRN number, ...
);
If your intake form, batch record and pack-weight sampling all write through this table (or carry instrument_id themselves), the "what did this instrument touch?" question becomes a query instead of an investigation.
Status is derived, not stored
Do not keep a status column that someone has to remember to update. Derive it:
create view instrument_status as
select i.id,
case
when not i.in_service then 'out_of_service'
when last_fail.checked_at > coalesce(last_pass.checked_at, '-infinity') then 'failed'
when last_cal.performed_at + i.cal_interval < now() then 'overdue'
when last_pass.checked_at + i.check_interval < now() then 'check_due'
else 'ok'
end as status
from instrument i
left join lateral (select performed_at from calibration_event c
where c.instrument_id = i.id and c.passed
order by performed_at desc limit 1) last_cal on true
left join lateral (select checked_at from check_reading r
where r.instrument_id = i.id and r.passed
order by checked_at desc limit 1) last_pass on true
left join lateral (select checked_at from check_reading r
where r.instrument_id = i.id and not r.passed
order by checked_at desc limit 1) last_fail on true;
Then gate writes. A CCP measurement or a goods receipt from an instrument whose status is not ok should be rejected, or at least accepted only with a supervisor override that is itself recorded:
-- in the measurement insert path
if (select status from instrument_status where id = new.instrument_id) <> 'ok' then
raise exception 'Instrument % is not in a usable state', new.instrument_id;
end if;
That is the database version of the floor rule "overdue means out of use".
The window-at-risk query
When a check fails, the window at risk is everything that instrument measured since its last passing check (or since the last calibration whose as-found error was within tolerance):
with failed as (
select instrument_id, checked_at as failed_at
from check_reading where id = $1
),
last_good as (
select max(r.checked_at) as good_at
from check_reading r, failed f
where r.instrument_id = f.instrument_id
and r.passed and r.checked_at < f.failed_at
)
select m.context_type, m.context_id, min(m.taken_at), max(m.taken_at), count(*)
from measurement m, failed f, last_good g
where m.instrument_id = f.instrument_id
and m.taken_at > g.good_at
and m.taken_at <= f.failed_at
group by m.context_type, m.context_id;
The result is the list to act on: batch numbers to place on QA hold, goods receipts whose quantities (and supplier payments) need review, pack-weight samples to discount. It is also why check frequency is a product decision as much as a QA one: the shorter the interval, the smaller this result set.
Design notes from the floor
- Use the device tag as the primary key. Operators read the tag; they will not read a UUID.
- Make the check reading fast to capture. Expected value pre-filled from the check weight or reference in use, observed value typed or read from the device, one tap to submit. If it takes longer than the check itself, it will be skipped.
- Treat a failed check as an event that opens work. Create a non-conformance with an owner, and attach the window-at-risk result to it, rather than sending an email.
- Keep trade verification separate from technical calibration. A Weights and Measures stamp and an ISO/IEC 17025 calibration certificate are different documents with different authorities. Store both, with their own expiry.
- Keep history immutable. Corrections are new rows. An auditor will ask what the record said on the day, not what it says now.
Where this fits
At FactoraERP we work with food and agro manufacturers in East Africa, where the same intake scale often feeds supplier payments, stock and yield, and where a QA hold has to reach the right lots quickly. FactoraERP tracks production batches and lots, stock status including QA holds, non-conformance records and stock adjustments with reason codes, which is the part of this model a failed instrument check has to reach.
The full, non-technical calibration checklist for factory teams, with the Codex, EAS 39 and Kenyan Weights and Measures references, is on the FactoraERP blog.
How do you link measurements to instruments in your systems today: a foreign key, a free-text field, or not at all?
