Model Complaints as Batch Events, Not Support Tickets: Lot Links, Severity Triage and Hold Triggers in a Food ERP

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

In most business software, a customer complaint is a support ticket: a customer, a subject, a free-text description, an assignee and a status. That is fine for a late delivery. It is the wrong model for a food factory.

When someone finds a hard object in a bag of flour, the useful questions are not "who is handling this ticket?" but "which lot was it, what else was made the same way, where did the rest of that lot go, and should it be on hold right now?" A ticket cannot answer any of those, because it is not connected to the production data.

The standards describe what the response has to do. The Codex General Principles of Food Hygiene (CXC 1-1969) defines a corrective action as one that re-establishes control, segregates and decides the disposition of affected product, and prevents reoccurrence. It says a root cause analysis should be conducted where possible, that corrective actions should be reviewed periodically "to identify trends", and (section 13.5) that when product is recalled, other products produced under similar conditions should be evaluated. The FAO/WHO food recall guide lists consumer complaints as one of the channels through which food safety events are identified. ISO 10002:2018 covers the complaints-handling process itself, from an easy-to-use intake to analysis and review.

"Segregate affected product", "similar conditions" and "identify trends" are queries. Here is how I would model a complaint so those queries are cheap.

The entities

Complaint
  ├─ channel (PHONE | WHATSAPP | EMAIL | DISTRIBUTOR | RETAILER | SALES_REP | ...)
  ├─ category (FOREIGN_MATTER | ILLNESS | ALLERGEN | CONTAMINATION | LABELLING
  │            | SHORT_WEIGHT | QUALITY | PACKAGING | DELIVERY | OTHER)
  ├─ severity (CRITICAL | MAJOR | MINOR | INFORMATIONAL)   -- derived first, overridable with reason
  ├─ ComplaintLot ──► Lot (finished goods)                  -- zero or more, with match confidence
  ├─ Evidence (photo, returned sample, lab result)
  ├─ Investigation ──► NonConformance (source = CUSTOMER_COMPLAINT)
  │                       └─ Action (IMMEDIATE | CORRECTIVE | PREVENTIVE, owner, due, verified_by)
  └─ Closure (outcome, customer_reply_at, customer_satisfaction)

Lot ── produced_by ──► ProductionOrder ──► Line, Shift, RecipeVersion
    ├─ consumed ──► MaterialLot (supplier lots)
    └─ shipped_on ──► DeliveryNote ──► Customer

The important design choice is ComplaintLot. A complaint is not "about a product"; it is about one or more specific lots. Sometimes the customer reads the lot code off the pack. Sometimes you only have a best-before date and a shop, and you narrow it to two candidate lots. Store that as data, with a confidence flag, instead of a sentence in the description.

CREATE TABLE complaint_lot (
  complaint_id   uuid REFERENCES complaint(id),
  lot_id         uuid REFERENCES lot(id),
  match_basis    text CHECK (match_basis IN ('LOT_CODE_ON_PACK','BEST_BEFORE_AND_OUTLET','INFERRED')),
  PRIMARY KEY (complaint_id, lot_id)
);

Triage is a function, not a judgment call at 6pm

Severity should be derived from the category and a few facts captured on first contact, then overridden only with a recorded reason. That keeps the rules consistent between the receptionist on Monday and the sales rep on Saturday.

SAFETY = {"FOREIGN_MATTER", "ILLNESS", "ALLERGEN", "CONTAMINATION"}

def derive_severity(category: str, illness_reported: bool, injury_reported: bool) -> str:
    if illness_reported or injury_reported:
        return "CRITICAL"
    if category in SAFETY:
        return "MAJOR"          # safety until evidence says otherwise
    if category in {"LABELLING", "SHORT_WEIGHT"}:
        return "MAJOR"          # legal exposure
    if category in {"QUALITY", "PACKAGING"}:
        return "MINOR"
    return "INFORMATIONAL"

Your QA team will tune these rules. The point is that they live in code and config, not in someone's head.

Hold triggers: compute exposure, then hold in one transaction

When a complaint is MAJOR or CRITICAL and linked to at least one lot, the system should propose an exposure set and put it on hold. "Similar conditions" becomes a query over the production graph:

-- Lots that share a supplier material lot, or the same line and production day,
-- with any lot linked to this complaint.
WITH seed AS (
  SELECT l.id, po.line_id, po.production_date
  FROM complaint_lot cl
  JOIN lot l ON l.id = cl.lot_id
  JOIN production_order po ON po.id = l.production_order_id
  WHERE cl.complaint_id = :complaint_id
),
shared_material AS (
  SELECT DISTINCT c2.lot_id
  FROM seed s
  JOIN consumption c1 ON c1.lot_id = s.id
  JOIN consumption c2 ON c2.material_lot_id = c1.material_lot_id
),
same_line_day AS (
  SELECT l.id AS lot_id
  FROM seed s
  JOIN production_order po ON po.line_id = s.line_id AND po.production_date = s.production_date
  JOIN lot l ON l.production_order_id = po.id
)
SELECT lot_id FROM shared_material
UNION
SELECT lot_id FROM same_line_day;

Then, in one transaction: set those lots' stock status to QA_HOLD, block them from pick lists and delivery notes, create a non-conformance with source = CUSTOMER_COMPLAINT, and write an audit entry. If the hold and the non-conformance are separate manual steps, one of them will be forgotten.

The same graph answers the recall question. shipped_on from the exposure set gives you every customer and delivery note, which is exactly what a mock recall tests.

Actions are typed, owned and verified

Codex separates the parts of a corrective action: deal with the product, find the cause, prevent reoccurrence. Model them as separate rows, not one text box:

@dataclass
class Action:
    kind: Literal["IMMEDIATE", "CORRECTIVE", "PREVENTIVE"]
    description: str
    owner_id: str
    due: date
    completed_at: datetime | None = None
    verified_by: str | None = None   # must differ from owner_id

def can_close(complaint) -> bool:
    has_root_cause = bool(complaint.investigation and complaint.investigation.root_cause)
    actions_verified = all(a.completed_at and a.verified_by and a.verified_by != a.owner_id
                           for a in complaint.actions if a.kind != "IMMEDIATE")
    customer_replied = complaint.closure and complaint.closure.customer_reply_at
    has_lot_or_reason = complaint.lots or complaint.no_lot_reason
    return all([has_root_cause, actions_verified, customer_replied, has_lot_or_reason])

verified_by != owner_id mirrors the Codex HACCP chapter, which says verification should be done by someone other than the person responsible for monitoring and corrective actions. "No fault found" is a valid root cause, but it should carry evidence.

Trends are a GROUP BY you run every month

Once complaints carry lots, the trend review the standards ask for is ordinary SQL:

SELECT r.product_id, po.line_id, ml.supplier_id, c.category,
       count(*) AS complaints,
       avg(c.closed_at - c.received_at) AS avg_time_to_close
FROM complaint c
JOIN complaint_lot cl ON cl.complaint_id = c.id
JOIN lot l ON l.id = cl.lot_id
JOIN production_order po ON po.id = l.production_order_id
JOIN recipe_version r ON r.id = po.recipe_version_id
LEFT JOIN consumption cn ON cn.lot_id = l.id
LEFT JOIN material_lot ml ON ml.id = cn.material_lot_id
WHERE c.received_at >= date_trunc('month', now()) - interval '3 months'
GROUP BY 1, 2, 3, 4
ORDER BY complaints DESC;

Three "black specks" complaints from three shops look like noise as tickets. As rows joined to lots, they are one line, one week and one sifter.

What I would not do

• No complaint without a lot field. Allow "unknown", but require a reason.
• No free-text severity. Derive it, allow an override, log who overrode it and why.
• No closing on refund. A credit note is an immediate action, not a root cause.
• No separate silo. If complaints live in a helpdesk tool that cannot join to lots, you have rebuilt the spreadsheet problem with a nicer UI.

Wrapping up

The floor version of this, for QA, production and sales teams rather than developers, is an eight-step checklist: one register, lot capture, triage, batch links and holds, investigation, root cause and verified actions, recall triggers, and closing the loop with a monthly trend review. It is on the FactoraERP blog: Customer complaint handling checklist for food factories.

FactoraERP, which my company builds, has a customer complaints register with category, severity, batch numbers, root cause and immediate, corrective and preventive actions, and non-conformance reports that can take a customer complaint as their source, alongside batch records, QA holds and dispatch. If you have modelled complaints differently, I would like to hear how you handled lot matching when the customer has thrown the pack away.