Findings Are Rows, Not Paragraphs: Modelling Internal Audits, Independence Rules and Corrective Action Loops in a Food ERP

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

Most compliance software treats an audit as a document: a PDF report with a date, a score and a list of findings typed as paragraphs. That works until someone asks the questions that actually matter. Which findings from the March audit are still open? Which corrective actions were closed without anyone checking they worked? Which problem has been found in three audits in a row?

A report cannot answer those questions. A data model can. This post walks through how we think about modelling internal audits, findings and corrective action loops 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:

• ISO 19011:2026 (published May 2026) defines an audit as a "systematic, independent and documented process for obtaining objective evidence and evaluating it objectively to determine the extent to which the audit criteria are fulfilled". Internal audits are "first-party" audits. Its listed principles include independence, an evidence-based approach and a risk-based approach. Source
• Codex CXC 1-1969: verification "should be carried out by someone other than the person who is responsible for performing the monitoring and corrective actions"; root cause analysis "should be conducted where possible"; corrective actions should be reviewed periodically "to identify trends and to ensure corrective actions are effective". Source
• 21 CFR 117.165: monitoring and corrective action records must be reviewed "within 7 working days after the records are created" (or a justified longer timeframe). Source
• 21 CFR 117.170: the food safety plan must be reanalysed "at least once every 3 years", and sooner after significant changes or when a control is found ineffective. Source

Translated: an audit has a scope, criteria and auditors; auditors must not audit their own area; findings are discrete, graded, evidence-backed records; every finding needs a root cause and an owned, dated action; closure needs independent verification; and the system must be able to see repeats and trends over time.

The core entities

AuditArea        (id, name, risk_level, owner_user_id, max_interval_days)
AuditProgramme   (id, year, approved_by, approved_at)
Audit            (id, programme_id, area_id, type, scope, criteria,
                  planned_for, performed_on, status)
AuditTeam        (audit_id, user_id, role)            -- lead, auditor, expert
Evidence         (id, audit_id, kind, ref, note)      -- record sample, observation, batch trace
Finding          (id, audit_id, area_id, clause_ref, grade,
                  requirement, observed, evidence_id, created_at)
CorrectiveAction (id, finding_id, root_cause, action, owner_user_id,
                  due_on, done_on, status)
Verification     (id, action_id, verified_by, verified_on, effective, note)

A few design choices matter more than the table names.

1. Findings are rows, not paragraphs

A finding with a grade, an area_id and a clause_ref can be counted, filtered, trended and escalated. A finding inside a free-text report cannot. Store the report as a rendering of the rows, never the other way round.

2. Independence is a constraint, not a policy

Codex and ISO both care about who audits. Make it impossible to break by accident:

-- reject an auditor who owns the audited area
CREATE FUNCTION check_auditor_independence() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM audit a JOIN audit_area ar ON ar.id = a.area_id
    WHERE a.id = NEW.audit_id AND ar.owner_user_id = NEW.user_id
      AND NEW.role IN ('lead','auditor')
  ) THEN
    RAISE EXCEPTION 'Auditor owns the audited area';
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

Small plants will need an override for the occasional exception. Make it explicit, reasoned and logged, so it shows up in review.

3. Corrective actions are a state machine

open -> root_cause_found -> action_planned -> done -> verified_effective -> closed
                                               \-> verified_ineffective -> action_planned

Two rules carry most of the value:

• action_planned requires a non-empty root_cause, an owner_user_id and a due_on. "Staff reminded" can be blocked by a short list of banned phrases, though a required "why" field does more.
• closed requires a Verification row with effective = true and verified_by <> owner_user_id. The person who fixed it does not get to sign off that it is fixed.

4. Repeat findings are a query, not a memory

If findings carry area_id and clause_ref, a repeat is easy to detect:

SELECT f.area_id, f.clause_ref, COUNT(*) AS times_found,
       MIN(a.performed_on) AS first_seen, MAX(a.performed_on) AS last_seen
FROM finding f JOIN audit a ON a.id = f.audit_id
WHERE a.performed_on >= now() - interval '12 months'
GROUP BY f.area_id, f.clause_ref
HAVING COUNT(*) > 1
ORDER BY times_found DESC;

Every row in that result is a corrective action that did not work. That is the trend review Codex asks for, in one statement.

5. Coverage is computed from the programme

Each AuditArea has a max_interval_days based on risk. Overdue areas fall out of a simple query:

SELECT ar.name, MAX(a.performed_on) AS last_audit,
       ar.max_interval_days
FROM audit_area ar
LEFT JOIN audit a ON a.area_id = ar.id AND a.status = 'completed'
GROUP BY ar.id
HAVING MAX(a.performed_on) IS NULL
    OR MAX(a.performed_on) < now() - (ar.max_interval_days || ' days')::interval;

6. Evidence links into operational data

The most useful audit step is tracing a real batch end to end. If Evidence.ref can point at a batch, a CCP record, a cleaning record or a QA hold in the same database, the auditor tests the live system rather than a printed copy, and the finding points straight at the record that failed.

7. Findings feed one corrective action system

Audit findings, customer complaints, supplier issues and process deviations all end in the same place: a non-conformance with a root cause and an action. Model one CorrectiveAction table with a source column rather than four separate trackers, so a manager sees one queue and trends across sources.

What this buys you

• Open findings by grade, age and owner, with overdue actions on top.
• Actions closed without independent verification (should be zero).
• Areas overdue for audit against their risk-based interval.
• Repeat findings in the last 12 months, by area and clause.

None of this is exotic. It is a handful of tables, a state machine and two or three constraints. The hard part is deciding that a finding is data, not prose.

FactoraERP is an ERP for food and agro manufacturers in East Africa, built by Tamalaki Business Network Ltd. Full checklist: Food Safety Internal Audit Checklist.