Model Factory Water as a Supplier, Not a Utility: WaterSource, SamplePoint, WaterCheck and Batch Exposure

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

In most factory systems, water does not exist as data. Flour has a supplier, a goods received note and a lot number. Sugar has a certificate of analysis. Water arrives through a pipe or a tanker hose, and the only record is a utility bill that finance pays every month.

Then a customer complaint comes in, and someone asks: "Which batches were made with the tanker water we bought during the dry spell?" Nobody can answer, because water was never modelled as an input.

That is a modelling problem before it is a hygiene problem.

The standards are explicit about what the records should prove. The Codex General Principles of Food Hygiene (CXC 1-1969), section 13.3, says water, and ice and steam made from it, should be fit for its intended purpose based on a risk-based approach, and that water not fit for food contact should run in a separate system with no connection or reflux into the food-contact system. Section 13.4 says records should be kept for longer than the product's shelf life. In Kenya, the Food, Drugs and Chemical Substances (Food Hygiene) Regulations, 1978 require a potable supply (regulation 11(1)(b)) and potable water for washing food and making ice (Second Schedule, Part A), and the Ministry of Health's licensing checklist asks plants to monitor both raw and treated water.

"Fit for intended purpose", "separate system", "monitor" and "records" are all things a schema can enforce. Here is how I would model it for a food or agro ERP.

The entities

Site ─┬─ WaterSource (MAINS | BOREHOLE | TANKER | RAINWATER)
      │     └─ WaterDelivery (tanker loads: supplier, vehicle, volume, residual)
      ├─ WaterSystem (POTABLE | NON_POTABLE) ── StorageTank, TreatmentStep
      │     └─ SamplePoint (numbered tap, tank outlet, post-treatment)
      │           ├─ WaterCheck (shift check: free chlorine, pH, appearance)
      │           └─ LabResult (accredited lab, per parameter)
      ├─ WaterUse (INGREDIENT | ICE | FOOD_WASH | CIP | HANDWASH | BOILER | COOLING | FIRE)
      │     └─ LineWaterUse → ProductionLine
      └─ WaterNonConformance
            ├─ RootCause, CorrectiveAction (owner, due, verified_by)
            └─ BatchExposure → Batch (hold / release)

Sources, systems and uses

CREATE TABLE water_source (
  id           BIGSERIAL PRIMARY KEY,
  site_id      BIGINT NOT NULL REFERENCES site(id),
  source_type  TEXT NOT NULL CHECK (source_type IN ('MAINS','BOREHOLE','TANKER','RAINWATER')),
  name         TEXT NOT NULL,
  active       BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE water_system (
  id           BIGSERIAL PRIMARY KEY,
  site_id      BIGINT NOT NULL REFERENCES site(id),
  name         TEXT NOT NULL,
  grade        TEXT NOT NULL CHECK (grade IN ('POTABLE','NON_POTABLE'))
);

CREATE TABLE water_use (
  id           BIGSERIAL PRIMARY KEY,
  system_id    BIGINT NOT NULL REFERENCES water_system(id),
  line_id      BIGINT REFERENCES production_line(id),
  use_type     TEXT NOT NULL CHECK (use_type IN
               ('INGREDIENT','ICE','FOOD_WASH','CIP','HANDWASH','BOILER_NON_CONTACT','COOLING','FIRE')),
  risk_assessment_ref TEXT          -- required when grade is NON_POTABLE
);

The key rule: under the Kenyan Second Schedule, water used for washing food products and for making ice must be potable, and water that goes into the product obviously must be too. Make that a constraint, not a memo:

CREATE FUNCTION enforce_potable_use() RETURNS trigger AS $$
BEGIN
  IF NEW.use_type IN ('INGREDIENT','ICE','FOOD_WASH')
     AND (SELECT grade FROM water_system WHERE id = NEW.system_id) <> 'POTABLE' THEN
    RAISE EXCEPTION 'use % requires a POTABLE water system', NEW.use_type;
  END IF;
  IF (SELECT grade FROM water_system WHERE id = NEW.system_id) = 'NON_POTABLE'
     AND NEW.risk_assessment_ref IS NULL THEN
    RAISE EXCEPTION 'non-potable use needs a fit-for-purpose risk assessment reference';
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_potable_use BEFORE INSERT OR UPDATE ON water_use
FOR EACH ROW EXECUTE FUNCTION enforce_potable_use();

The second check mirrors Codex: a different quality may be fit for some purposes, but only after you have assessed it (see also CXG 100-2023 on fit-for-purpose water).

Tanker water is a goods receipt

CREATE TABLE water_delivery (
  id              BIGSERIAL PRIMARY KEY,
  source_id       BIGINT NOT NULL REFERENCES water_source(id),   -- a TANKER source
  supplier_id     BIGINT NOT NULL REFERENCES supplier(id),
  vehicle_reg     TEXT NOT NULL,
  declared_origin TEXT,
  volume_litres   NUMERIC NOT NULL,
  into_tank_id    BIGINT NOT NULL REFERENCES storage_tank(id),
  free_cl_mg_l    NUMERIC,
  dedicated_tanker BOOLEAN,
  accepted        BOOLEAN NOT NULL,
  reject_reason   TEXT,
  received_at     TIMESTAMPTZ NOT NULL,
  received_by     TEXT NOT NULL,
  CHECK (accepted OR reject_reason IS NOT NULL)
);

WHO's Guidelines for Drinking-water Quality (2022), section 6.3, says that for large tanker volumes, a free chlorine residual of at least 0.5 mg/l at the point of delivery is desirable, and tankers should be used only for water or thoroughly cleaned first. Section 6.4 says bulk water should be treated as another source. The row above is exactly that: a source with a receipt.

SamplePoint and WaterCheck: append-only

CREATE TABLE sample_point (
  id         BIGSERIAL PRIMARY KEY,
  system_id  BIGINT NOT NULL REFERENCES water_system(id),
  code       TEXT NOT NULL,          -- the number on the tap tag
  location   TEXT NOT NULL,
  feeds_line BIGINT REFERENCES production_line(id),
  UNIQUE (system_id, code)
);

CREATE TABLE water_check (
  id            BIGSERIAL PRIMARY KEY,
  point_id      BIGINT NOT NULL REFERENCES sample_point(id),
  checked_at    TIMESTAMPTZ NOT NULL,
  checked_by    TEXT NOT NULL,
  free_cl_mg_l  NUMERIC,
  ph            NUMERIC,
  appearance_ok BOOLEAN NOT NULL,
  odour_ok      BOOLEAN NOT NULL,
  corrects_id   BIGINT REFERENCES water_check(id)   -- corrections are new rows
);

Two design choices carry most of the value:

1. One row per point per check. A check without a sample point code is an anecdote.
2. No updates. A reading is evidence. A correction is a new row that references the one it corrects.

Limits belong in configuration, not code. Each site sets its target ranges with its water treatment provider; WHO's reference points (Table 8.17) are a free chlorine residual of at least 0.5 mg/l after 30 minutes' contact at pH below 8.0, and at least 0.2 mg/l at the point of delivery.

CREATE TABLE water_limit (
  system_id    BIGINT NOT NULL REFERENCES water_system(id),
  parameter    TEXT NOT NULL,          -- 'free_cl_mg_l', 'ph', 'e_coli_per_100ml', ...
  min_value    NUMERIC,
  max_value    NUMERIC,
  basis        TEXT NOT NULL,          -- e.g. 'KS EAS 12:2018 via lab', 'site target'
  PRIMARY KEY (system_id, parameter)
);

Lab results

CREATE TABLE lab_result (
  id           BIGSERIAL PRIMARY KEY,
  point_id     BIGINT NOT NULL REFERENCES sample_point(id),
  sampled_at   TIMESTAMPTZ NOT NULL,
  lab_name     TEXT NOT NULL,
  certificate  TEXT NOT NULL,         -- certificate number or file reference
  parameter    TEXT NOT NULL,
  value        NUMERIC,
  detected     BOOLEAN,               -- for presence/absence tests
  standard_ref TEXT                   -- e.g. 'KS EAS 12:2018'
);

WHO Table 7.10 says E. coli must not be detectable in any 100 ml sample of water intended for drinking, and that immediate investigative action must be taken if it is detected. So a detected = true on e_coli_per_100ml for a potable system should open a non-conformance automatically, not wait for someone to read the PDF.

The query that matters: batch exposure

When a check or lab result fails, the question is always the same: which batches used that water since the last good result?

WITH failed AS (
  SELECT sp.id AS point_id, sp.feeds_line, wc.checked_at AS failed_at
  FROM water_check wc JOIN sample_point sp ON sp.id = wc.point_id
  WHERE wc.id = $1
),
last_good AS (
  SELECT MAX(wc.checked_at) AS good_at
  FROM water_check wc, failed f
  WHERE wc.point_id = f.point_id
    AND wc.checked_at < f.failed_at
    AND wc.appearance_ok AND wc.odour_ok
    AND wc.free_cl_mg_l >= (SELECT min_value FROM water_limit wl
                            JOIN sample_point sp ON sp.system_id = wl.system_id
                            WHERE sp.id = f.point_id AND wl.parameter = 'free_cl_mg_l')
)
SELECT b.id, b.batch_no, b.started_at, b.ended_at
FROM batch b, failed f, last_good g
WHERE b.line_id = f.feeds_line
  AND b.ended_at   >= COALESCE(g.good_at, '-infinity')
  AND b.started_at <= f.failed_at;

Insert those rows into batch_exposure, set each batch's stock status to QA_HOLD, and require a disposition (release, rework, reject) with a reason before the hold can be lifted. Product written off gets a stock adjustment with a reason code, so the loss is explained rather than appearing as an unexplained variance at the next stock count.

Where IoT fits, and where it does not

Online chlorine, pH and turbidity sensors can write water_check rows every few minutes. Treat them as another source of checks, with the sensor ID and its calibration record, not as a replacement for people. A sensor at the treatment outlet will not notice a hose lying in a floor drain or a tank lid left open after maintenance. The tank inspection and the hose walk still produce corrective_action rows.

Records

Design these tables for retention and audit, not edits. Codex section 13.4 says records should be retained for a period that exceeds the shelf life of the product, or as determined by the competent authority. The goal is that on any day you can show an inspector the source map, the sample points, the readings, the deliveries, the lab certificates, the failures, the batches held and who closed each action.

The full operations checklist for factory teams, with an FAQ, is here: Water Quality Checklist.