How to create a useful audit trail

Something changes in your system — an invoice total, a customer's credit limit, a delivery address — and three weeks later someone asks: who changed that, and why? If your honest answer is "we're not sure, let me check a few places," you don't have an audit trail. You have a log file nobody trusts.

Many FileMaker systems, ERPs, and custom apps have some form of logging turned on somewhere. Very few have an audit trail that a manager, auditor, or IT lead can actually use under pressure — during a dispute with a customer, a data-integrity investigation, or a compliance check. This article walks through what separates a decorative log from a genuinely useful audit trail, and how to build one without drowning your database in noise.

What exactly is an audit trail, and how is it different from a log?

A log is a record that something happened. An audit trail is a record that lets you reconstruct exactly what happened, by whom, when, and — ideally — what the value was before and after.

Concretely: a system log might tell you "record 4821 was modified on 14 March at 10:32." A useful audit trail tells you "user Sanne changed the shipping address on order 4821 from 'Kerkstraat 12' to 'Kerkstraat 21' on 14 March at 10:32, from the sales module."

The second version is the one that actually resolves a dispute. The first one just proves the file isn't corrupted.

Why do businesses need this, beyond "just in case"?

A few real scenarios where an audit trail earns its keep:

• A customer disputes an invoice. They claim they were quoted a different price. Without an audit trail, it's your word against theirs. With one, you can show exactly who changed the unit price, and when, and compare it against the email thread.

• A price list gets edited overnight and nobody claims it. A warehouse manager notices margins dropped on a product line. An audit trail narrows the search from "anyone with access" to one user, one timestamp, one field.

• Auditors or a certification body (ISO, NEN, GDPR-related requests) ask for evidence of data handling. "We have logging enabled" is not an answer. "Here is the change history for this customer record, including who accessed it and why" is.

• An employee leaves under a cloud, and management wants to know if they touched financial records before departing. This is the scenario nobody wants to explain, and it's exactly when a real audit trail earns its cost back in one afternoon.

• A bug is suspected in a script or integration, not a person. An audit trail that also logs system/API-driven changes (not just human ones) tells you whether the change came from a person or from an automated process gone wrong.

What should a useful audit trail actually record?

At minimum, every audit trail entry should capture:

1. Who — the authenticated user or system/API identity that made the change (not just "admin" for everyone — see the mistake below).
2. What — which record and which field changed.
3. When — a precise, server-based timestamp (not the user's local clock, which can be wrong or manipulated).
4. Old value → new value — not just "field X was edited," but the actual before/after values.
5. From where — which module, script, layout, or API endpoint triggered the change. This matters more than people expect: a price change made through the sales layout is a different risk profile than the same change made through an unattended nightly import script.
6. Why (optional but powerful) — a reason code or free-text note, especially for sensitive fields like credit limits, discounts, or personal data. This one is the difference between a trail that tells you what happened and one that tells you why it was allowed to happen.

What are the most common mistakes when setting one up?

• Logging everything, everywhere. Turning on field-level logging for every table in the system sounds thorough but produces so much noise that nobody ever reads it. A useful audit trail is selective: focus on financially sensitive fields, personal data, and anything tied to compliance obligations — not every timestamp field that updates itself.
• Sharing generic logins. If three people log in as "admin" or share a single service account, your audit trail says "admin did it" — which is legally and practically useless. Every real user and every integration needs its own identity.
• Storing the audit trail in the same table it's protecting. If a user with edit rights on the data can also edit or delete the audit log, it isn't an audit trail — it's a suggestion. The log needs to be write-only for normal users, ideally in a separate, access-restricted table or even an external append-only store.
• No retention policy. Some industries need audit history for years (finance, healthcare, anything under GDPR data-subject-request obligations); others just need 90 days of operational history. Deciding this upfront avoids either losing evidence too early or drowning your database in years of unused rows.
• No one ever looks at it until there's a problem. A trail that's never reviewed is a trail nobody trusts when it matters. Even a lightweight monthly spot-check of sensitive-field changes catches problems early and builds confidence that the system works.

How do you build this in a FileMaker or similar custom system, in practice?

In a FileMaker-based system, a practical audit trail is usually built as:

1. A dedicated audit log table, separate from operational tables, storing: table name, record ID, field name, old value, new value, user account, timestamp, and source (layout/script/API).
2. Script-triggered logging on OnRecordCommit or field-level OnObjectModify triggers for the specific fields you've decided matter — not a blanket rule across the whole schema.
3. Server-side timestamps (Get(CurrentHostTimestamp)) rather than client-side ones, so the trail can't be fooled by a user with the wrong local clock or bad intentions.
4. Privilege sets that block edit/delete on the audit table for everyone except a very small admin group — and ideally not even them, in a well-designed system.
5. A simple review layout or report that lets a manager filter by user, date range, or field — because an audit trail that requires a developer to query is one that never gets checked.

For systems that combine FileMaker with modern web front-ends — for example using something like FmBetterforms to build browser-based interfaces on top of a FileMaker back end — the same discipline applies: every write coming through the web layer needs to be attributable to a real, authenticated user, not to a shared service account, so the audit trail stays meaningful even when the data entry point isn't the classic FileMaker layout anymore.

Can AI make an audit trail more useful, not just bigger?

Yes — and this is where a lot of systems are still behind. Raw audit logs are hard to read at scale: hundreds of rows of "field X changed from A to B" don't tell a manager a story. Tools like Klai, which bring AI capability inside a FileMaker solution, can be used to summarize audit trail data into plain language — for example, turning a week of raw change records into "3 unusual price changes this week, all made outside business hours by the same user, all on high-margin products" — flagging the pattern a human reviewer might miss scrolling through rows.

This doesn't replace the underlying audit trail discipline described above — AI summarization is only as good as the data it's built on — but it does solve the real-world problem that a technically correct audit trail nobody reads is not actually useful.

How do you know if your current audit trail is actually good enough? (Checklist)

• Every user and every integration has its own login — no shared or generic accounts.
• Sensitive fields (pricing, discounts, personal data, financial approvals) are explicitly logged with old/new values.
• Timestamps are server-based, not client-based.
• The audit log table cannot be edited or deleted by normal users.
• You know your retention period, and it matches any legal or compliance obligation you're under.
• Someone actually reviews the audit trail on a regular schedule, not just after an incident.
• You can answer "who changed this, when, and from where" for any sensitive record in under five minutes.
• The audit trail covers system/script/API-driven changes, not just human edits made through a layout.

If you can't tick most of these boxes today, you don't have a broken audit trail — you likely have no real audit trail at all, just scattered logging.

FAQ

Does every table need an audit trail? No. Logging every field on every table creates noise that hides the changes that actually matter. Prioritize financially sensitive data, personal data, and anything a compliance framework or contract requires you to track.

How long should we keep audit trail data? It depends on your industry and obligations — some financial and healthcare contexts require multi-year retention, while purely operational logs might only need 90 days to a year. Set the policy deliberately rather than defaulting to "forever" or "never."

Can an audit trail slow down our system? A well-scoped one, logging only selected fields with efficient scripts, has negligible performance impact. Logging every field on every table, on the other hand, can measurably slow down record commits — another reason to be selective.

Is an audit trail the same as a backup? No. A backup restores lost data; an audit trail explains how the current data got to be the way it is. You need both, and they serve different failure scenarios — this is one reason audit trails belong in the broader conversation about securing and maintaining business-critical software, alongside backups, permissions, and update discipline.

A useful audit trail isn't a compliance checkbox you set up once and forget — it's a working part of how your business investigates disputes, catches mistakes, and builds trust in its own data. If you're not sure whether your current FileMaker system, ERP, or custom application actually gives you a trustworthy answer to "who changed this, and why," that's worth a closer look. Loggix can help assess your current logging setup, design a properly scoped audit trail as part of a custom FileMaker solution, or bring AI-assisted review — through tools like Klai — into the mix so the data you're already collecting actually gets used.