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

A swab result looks like the simplest record in a food factory: a site, a date, a pass or a fail. In practice it is one of the hardest to model well. The same "fail" can mean "re-clean the bench" or "hold three days of product", depending on where the sample was taken, what it was tested for, when it was taken relative to cleaning, and what has been produced since the last clear result. If the data model can't express that, the result ends up in a PDF from the lab and nobody can trend it.

This post walks through how we think about modelling environmental monitoring 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 CXG 61-2007 (Annex I) lists what a Listeria environmental monitoring programme must define: type of samples (food contact and non-food contact), target organisms, sampling locations and number of samples, frequency, tools, analytical methods, "a system to record the data and their evaluation, e.g. performing trend analyses", and actions on positive results. Source
  • EAS 39:2001: environmental sampling "at a level commensurate with identifying the risk" (4.3.4), timely action when results exceed action limits (4.3.7), a per-line tracking system "to track the location of possible sources of contamination" (4.6.2), and "records of environmental tests" (14.3.4). Source
  • 21 CFR 117.165(b)(3) (a US benchmark, not Kenyan law): the written procedure must identify the test microorganisms, "the locations from which samples will be collected and the number of sites", the timing and frequency, the tests and methods, the laboratory, and the corrective action procedures. Source

Translated into engineering terms: a sampling plan is configuration, a sample is an event against a site, a result is a measurement against a limit, and a positive is the start of a workflow, not the end of a record.

Sites are first-class entities

The most common mistake is storing the location as free text on the sample. "Filler", "filler area", "near filler" and "Filler 2" become four sites and the trend disappears.

EmSite (id, code, area_id, zone, surface_type, food_contact bool, active_from, active_to) zone IN (1,2,3,4) EmPlanItem (id, site_id, target, method, frequency, timing, action_limit, owner_id) timing IN ('in_production','pre_startup','post_clean')

Sites get stable codes that match the plant map (RTE-Z1-03), a zone, and validity dates so the history survives a layout change. The plan item says what to test at that site, how often and when relative to cleaning. Timing matters: a post-clean swab verifies the sanitiser, a mid-shift swab looks for harbourage. Mixing them in one trend hides both signals.

Samples and results 

EmSample (id, site_id, plan_item_id, collected_at, collected_by, timing, pooled_group_id, lab_id, received_at) EmResult (id, sample_id, target, method, value, unit, result_text, status, tested_at) status IN ('pass','fail','pending')

Two details come straight from the guidance. First, pooling: Codex allows compositing samples where sensitivity allows, but "in the case of positive findings additional testing will be necessary to determine the location". A pooled_group_id lets a positive pool fan out into follow-up samples for each member site. Second, received_at: if the lab can't show when a sample arrived, a transport problem looks like a clean result.

In FactoraERP today, the laboratory module stores samples with a type (including environmental and water), a collection point, date, time and collector, and test results with spec min/max and a pass/fail status. Non-conformances with root cause and CAPA live in the same quality module.

A positive opens a workflow

The response is where most systems stop. Codex describes a range: "no action (no risk of recontamination), to intensified cleaning, to source tracing (increased environmental testing), to review of hygienic practices up to holding and testing of product". That's a rule table keyed on zone and target:

EmResponseRule (zone, target, actions[]) (1, 'Listeria spp.') -> [hold_product_since_last_negative, intensified_clean, vector_sampling, ncr] (3, 'Listeria spp.') -> [intensified_clean, vector_sampling, increase_frequency]

"Hold product since last negative" is the expensive one, and it is only possible if the system can answer a query like this:

-- batches produced on lines served by a site since its last clear result SELECT b.id, b.batch_code, b.produced_at FROM batch b JOIN em_site s ON s.area_id = b.area_id WHERE s.code = :site_code AND b.produced_at > ( SELECT max(es.collected_at) FROM em_sample es JOIN em_result er ON er.sample_id = es.id WHERE es.site_id = s.id AND er.status = 'pass' );

"Vector sampling" means automatically creating follow-up samples at the positive site and its neighbours. Neighbours can be modelled as an adjacency list on the site map, so the plan grows around a problem and shrinks back when it's fixed, which is what EAS 39 4.6.2 asks for.

Trending is the point

CXG 61 says long-term review "can also reveal low level, intermittent contamination that may otherwise go unnoticed". The useful view isn't a pass rate; it is positives per site over time:

SELECT s.code, s.zone, date_trunc('month', es.collected_at) AS month, count(*) FILTER (WHERE er.status = 'fail') AS positives, count(*) AS samples FROM em_sample es JOIN em_site s ON s.id = es.site_id JOIN em_result er ON er.sample_id = es.id WHERE es.timing = 'in_production' GROUP BY s.code, s.zone, month ORDER BY positives DESC, month;

One positive is an event. The same drain showing up three months in a row is a harbourage site.

Takeaways

  • Make sampling sites first-class entities with stable codes and zones.
  • Record timing relative to cleaning, and don't trend post-clean and mid-shift swabs together.
  • Support pooled samples with fan-out on positives.
  • Treat a positive as the start of a workflow keyed on zone and target.
  • Be able to query the product made since a site's last clear result.

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