Model Pest Control So Activity Has a Location and an Owner: Device, DeviceCheck, Sighting and CorrectiveAction

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

In most factory systems, pest control does not exist as data. It is a PDF the contractor emails once a month, filed in a shared folder that nobody queries. Finance sees a supplier invoice. Quality sees a binder. Nobody can answer a simple question like "which bait station has shown activity three visits in a row, and what did we do about it?"

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 11.2, says that even when monitoring is outsourced, the food business should review monitoring reports and make sure corrective action is taken; that the cause of an infestation should be identified; and that records should be kept of infestation, monitoring and eradication. In Kenya, the Food, Drugs and Chemical Substances (Food Hygiene) Regulations, 1978 require effective measures to exclude pests from food areas and pesticide use that does not contaminate food or packaging (regulation 12).

"Review", "cause", "corrective action" 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 ─┬─ Zone (EXTERNAL | INTERNAL_NON_FOOD | INTERNAL_FOOD)
      │     └─ Device (bait station, trap, insect light trap) — numbered, on a map
      │           └─ DeviceCheck (who, when, finding)
      ├─ Sighting (staff report between visits)
      ├─ PestFinding (opened from a check or sighting)
      │     ├─ RootCause (ENTRY_POINT | FOOD_SOURCE | HARBOURAGE | INCOMING_MATERIAL)
      │     ├─ CorrectiveAction (owner, due date, closed_at, verified_by)
      │     ├─ Treatment (product, registration ref, quantity, applicator)
      │     └─ LotExposure → Lot (hold / release)
      └─ MonthlyReview (trend sign-off)

Zone and Device

CREATE TABLE zone (
  id          BIGSERIAL PRIMARY KEY,
  site_id     BIGINT NOT NULL REFERENCES site(id),
  name        TEXT NOT NULL,
  zone_type   TEXT NOT NULL CHECK (zone_type IN ('EXTERNAL','INTERNAL_NON_FOOD','INTERNAL_FOOD'))
);

CREATE TABLE device (
  id          BIGSERIAL PRIMARY KEY,
  zone_id     BIGINT NOT NULL REFERENCES zone(id),
  code        TEXT NOT NULL,           -- the number painted on the device
  device_type TEXT NOT NULL CHECK (device_type IN ('TOXIC_BAIT','NON_TOXIC_MONITOR','SNAP_TRAP','GLUE_BOARD','INSECT_LIGHT_TRAP')),
  map_x       NUMERIC, map_y NUMERIC,
  active      BOOLEAN NOT NULL DEFAULT true,
  UNIQUE (zone_id, code)
);

The zone type matters because Codex says traps and detectors should be designed and located so they cannot contaminate raw materials, products or facilities. Make the policy a constraint, not a memo: for example, reject TOXIC_BAIT in an INTERNAL_FOOD zone with a trigger or an application-level rule your site agrees with its qualified provider.

DeviceCheck: append-only

CREATE TABLE device_check (
  id          BIGSERIAL PRIMARY KEY,
  device_id   BIGINT NOT NULL REFERENCES device(id),
  checked_at  TIMESTAMPTZ NOT NULL,
  checked_by  TEXT NOT NULL,           -- staff or contractor technician
  source      TEXT NOT NULL CHECK (source IN ('INTERNAL','CONTRACTOR')),
  finding     TEXT NOT NULL CHECK (finding IN ('NO_ACTIVITY','BAIT_TAKEN','DROPPINGS','CATCH','DAMAGED','MISSING')),
  catch_count INT,
  notes       TEXT
);

Two design choices carry most of the value:

1. One row per device per check. If the contractor's report is a PDF, import it into rows. A PDF cannot tell you a station's history; rows can.
2. No updates. A check is evidence. Corrections are new rows with a reference to the one they correct.

Sightings close the gap between visits

CREATE TABLE sighting (
  id          BIGSERIAL PRIMARY KEY,
  zone_id     BIGINT NOT NULL REFERENCES zone(id),
  seen_at     TIMESTAMPTZ NOT NULL,
  reported_by TEXT NOT NULL,
  pest_type   TEXT NOT NULL,           -- rodent, cockroach, stored-product insect, bird, fly...
  description TEXT
);

This is the cheapest table in the schema and the one that changes behaviour most: a phone form on the floor that anyone can submit.

Findings must end in a cause and a verified action

CREATE TABLE pest_finding (
  id            BIGSERIAL PRIMARY KEY,
  opened_from   TEXT NOT NULL CHECK (opened_from IN ('DEVICE_CHECK','SIGHTING','INTAKE','AUDIT')),
  source_id     BIGINT NOT NULL,
  opened_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  root_cause    TEXT CHECK (root_cause IN ('ENTRY_POINT','FOOD_SOURCE','HARBOURAGE','INCOMING_MATERIAL','UNKNOWN')),
  status        TEXT NOT NULL DEFAULT 'OPEN' CHECK (status IN ('OPEN','ACTION_PENDING','VERIFIED','CLOSED'))
);

CREATE TABLE corrective_action (
  id          BIGSERIAL PRIMARY KEY,
  finding_id  BIGINT NOT NULL REFERENCES pest_finding(id),
  action      TEXT NOT NULL,           -- "fit door brush, side door B"
  owner       TEXT NOT NULL,
  due_date    DATE NOT NULL,
  closed_at   TIMESTAMPTZ,
  verified_by TEXT,
  verified_at TIMESTAMPTZ
);

The business rule: a finding cannot move to CLOSED while root_cause is null or any action is unverified. That single rule encodes the Codex expectation that the cause is identified and corrective action taken, and it stops the classic failure where the same station shows activity every month and the only action is "bait replaced".

Treatments carry the chemical audit trail

CREATE TABLE treatment (
  id               BIGSERIAL PRIMARY KEY,
  finding_id       BIGINT REFERENCES pest_finding(id),
  applied_at       TIMESTAMPTZ NOT NULL,
  product_name     TEXT NOT NULL,
  registration_ref TEXT NOT NULL,      -- registration details as recorded from the regulator's list
  quantity         NUMERIC NOT NULL,
  unit             TEXT NOT NULL,
  zone_id          BIGINT NOT NULL REFERENCES zone(id),
  applicator       TEXT NOT NULL,
  applicator_cert  TEXT                -- evidence of the provider's qualification/certificate
);

In Kenya, the Pest Control Products (Inspection and Certification) Regulations, 2024 set qualifications for the person in charge of a pest control service (regulation 12(6)) and require a Board certificate for fumigation with Restricted Class 1 products (regulation 14). Storing the evidence next to each treatment is far easier than reconstructing it during an audit.

LotExposure links hygiene to traceability

CREATE TABLE lot_exposure (
  finding_id  BIGINT NOT NULL REFERENCES pest_finding(id),
  lot_id      BIGINT NOT NULL REFERENCES lot(id),
  decision    TEXT NOT NULL CHECK (decision IN ('HOLD','RELEASED','REWORK','WRITE_OFF')),
  decided_by  TEXT NOT NULL,
  decided_at  TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (finding_id, lot_id)
);

When a finding is in or next to an INTERNAL_FOOD zone, query which lots were in that zone during the exposure window and propose holds. A WRITE_OFF decision should post a stock adjustment with a pest-damage reason code, so it does not show up later as an unexplained variance.

Queries that answer real questions

Stations with activity on three or more consecutive checks:

WITH ordered AS (
  SELECT device_id, checked_at,
         finding <> 'NO_ACTIVITY' AS active,
         ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY checked_at DESC) AS rn
  FROM device_check
)
SELECT device_id
FROM ordered
WHERE rn <= 3
GROUP BY device_id
HAVING bool_and(active);

Findings older than 14 days still waiting on an action:

SELECT f.id, f.opened_at, a.action, a.owner, a.due_date
FROM pest_finding f
JOIN corrective_action a ON a.finding_id = f.id
WHERE a.verified_at IS NULL
  AND f.opened_at < now() - interval '14 days';

The monthly review becomes a report on those two queries plus trend charts by zone, signed off as a monthly_review row.

Where IoT fits, and where it does not

Connected traps and insect light traps with catch counters can write device_check rows automatically. Treat them as another source, not a replacement for people walking the building. A sensor will not notice the torn mesh or the pallet stacked against the wall; the weekly entry-point walk still produces corrective_action rows.

Records

Design these tables for retention and audit, not edits. The goal is that, on any given day, you can show an inspector or auditor the map, the checks, the findings, the causes, the actions and who verified them.

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