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

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)

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)
);

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
);

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
);

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
);

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
);

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)
);

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);

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';