Decide What the Audit Trail Is For
An audit trail can help answer who changed a record, what changed and whether the action succeeded. That is useful when a customer disputes an edit, a support team investigates a deletion, or a product needs an attributable history of important decisions. It does not automatically restore a deleted record or satisfy every retention obligation.
Separate three purposes before implementing the feature. Accountability needs an event history. Recovery needs enough correct data and relationships to restore an authorized state. Retention needs an approved policy for how long each class of information remains available. One store may support more than one purpose, but their permissions and lifetimes may differ.
OWASP distinguishes audit and transaction trails from security logging because their purposes and details often differ. Use that distinction to avoid exposing internal security events through a customer history screen. OWASP logging guidance The specification below is proposed for a hypothetical SaaS contact-management product, not a report of a completed implementation.
Preserve Useful Evidence Without Copying Everything
For an important edit or deletion, record a unique event ID, organisation, authenticated actor or worker identity, affected record ID, action, server timestamp, outcome, correlation ID and record version. Selected before-and-after values can explain a change; they need not be a full copy of every column.
Do not routinely capture passwords, access tokens, private keys, signed URL query strings or full payment data. Personal data and sensitive notes need a justified purpose and restricted treatment. OWASP recommends removing or protecting sensitive information in logs and checking the reliability of event sources. OWASP data exclusions
The application should derive the actor from verified identity context. A browser-supplied user ID, reason or timestamp can be useful input but is not authoritative evidence of who performed the action. Store a customer-entered reason as such, sanitize it for safe display and distinguish it from the server's decision.
For deletions, a minimal tombstone might preserve record ID, organisation, actor, action and timestamp without keeping the entire deleted personal-data payload. If recovery snapshots are needed, define them as a separate protected data class. Calling a full copy “audit metadata” does not remove its privacy implications.
Filled Product Specification Worksheet
Suppose organisation A uses a SaaS CRM and wants to understand contact edits and recover accidental deletions. These are proposed product choices.
| Decision | Proposed specification | Acceptance evidence |
|---|---|---|
| Covered events | Successful contact edits, deletion requests, completed deletions and authorized restores | Each event type has a fixture |
| Actor | Verified user or identified background worker | Caller cannot substitute another actor |
| Tenant scope | Organisation ID attached to every event | Cross-organisation reads denied |
| Changed data | Field names and selected values where justified | Secrets and excluded notes absent |
| Customer history | Scoped event summaries for authorized organisation roles | Internal security details omitted |
| Recovery data | Separate limited snapshot when policy permits | Tested restore with relationships |
| Capture policy | Mutation and durable event commit together | No successful mutation with lost required event |
| Operational export | Restricted internal export route | Current grant and export scope checked |
| Retention | Approved duration per event/data class | Purge and hold behavior tested |
The worksheet deliberately leaves the numeric retention period to the product owner and appropriate specialist review. There is no universal SaaS audit-history rule of one to seven years. A proposed period for one business must not be presented as a statutory requirement for all South African services.
Write down who owns the decision, what requirement or agreement informs it, and what happens to backups, search indexes, archives and exported copies. If an investigation hold applies, record its authorized scope and review date rather than keeping all events indefinitely.
Capture Events Durably
An ordinary asynchronous log call after a database change can be lost if the process exits before the message is stored. If the product promises a complete history of covered mutations, choose a durable capture mechanism and test its failure behavior.
One proposed design writes the business mutation and an audit event or durable outbox entry in the same database transaction. A separate worker can deliver the event elsewhere afterward. It needs stable event IDs, retry-safe processing, delivery monitoring and a defined backlog policy. This is an architecture recommendation, not a claim that a generic framework provides those guarantees automatically.
Decide what a failed audit write means for the particular action. For this hypothetical contact-history contract, a failure to persist the required event aborts the covered mutation. Other operational logs may use a different availability policy. Document that choice so developers do not silently alternate between rejecting a change and losing its history.
Record committed outcomes separately from rejected attempts. A failed authorization attempt belongs in the relevant restricted security log; it must not appear as a successful customer edit. Include imports, bulk edits, scheduled jobs and administrator actions, not only changes made through one screen.
Measure storage, write latency and worker backlog with realistic volumes. Asynchronous delivery can reduce coupling to a remote log sink, but it does not remove the requirement for durable local capture.
Protect History With Real Permission Boundaries
A table described as append-only is not inherently immutable. Restrictive grants can prevent ordinary application users from updating or deleting events, while a privileged owner, superuser or role with BYPASSRLS may still have broader access. PostgreSQL documents these RLS exceptions; evaluate the actual roles rather than promising that RLS prevents all tampering. PostgreSQL row security
Keep customer event-writing and history-reading paths separate from maintenance permissions. In Supabase, the service role bypasses RLS and must remain on the server. An endpoint using it must implement its own organization and role checks. A token containing old permissions may also need current-state verification for sensitive history exports. Supabase role and policy boundaries
OWASP recommends least privilege and permission checks on each request. Apply that to history detail pages, pagination, filters, search, downloads and exports. Hiding a tab does not secure its API. OWASP authorization guidance
| Viewer | Proposed visibility | Exclusions |
|---|---|---|
| Authorized organisation manager | Own organisation's customer change summaries | Other organisations and internal security logs |
| Ordinary member | Only history permitted by their role and record access | Restricted records and private fields |
| Support agent with approved case grant | Redacted diagnostic events for that case | General audit exports |
| Security investigator | Approved internal event scope | Unrelated customer content |
| Maintenance worker | Required retention or delivery operations | Interactive unrestricted browsing |
Customer-visible history is a product choice, not categorically forbidden. Show the minimum event details needed for its purpose and redact sensitive actors or fields where the policy requires it. Internal security logs can remain separately restricted.
For stronger tamper evidence, consider independent protected copies or a verified integrity scheme and define who can alter its keys or storage. These measures require testing and operational ownership. They do not justify an unsupported claim that no administrator can ever change history.
Recover a Deleted Record Safely
An audit event containing a record ID and changed field names is not enough to restore the original record. Even a full row snapshot may omit related records, attachments, permissions or external references. Recovery requires a designed and tested procedure.
In the hypothetical CRM, an administrator first confirms that the deletion was accidental and that restoration is permitted. A contact deleted because it should no longer be retained must not be automatically recreated from history. Record the recovery request, authority and purpose.
Compare the stored version with the current state before restoring. A newer contact might reuse a unique email address, related company membership may have changed, or an attachment may no longer exist. Restore only the approved scope, resolve conflicts explicitly and create a new restore event pointing to the earlier deletion event. Do not erase the deletion history.
Run the recovery in an isolated fixture first. Verify identities, relationships, files and current permissions, then read back the restored record through an ordinary authorized user. A successful insert by an administrator is insufficient acceptance evidence.
Acceptance and Failure-Test Matrix
These expected results implement the proposed specification; they are not claims that a live product has passed.
| Test | Expected result | Recovery when it fails |
|---|---|---|
| Authorized edit | One committed event with correct actor, version and selected changes | Repair identity or capture mapping |
| Completed deletion | Tombstone and permitted recovery data follow the policy | Block false success and inspect transaction |
| Required event write fails | Covered mutation does not commit under this contract | Repair durable capture before retry |
| Worker retries delivery | Same event is not duplicated in customer history | Use stable event ID and retry-safe delivery |
| Organisation B requests A history | No protected events, names or counts returned | Repair route and database scope |
| Ordinary user alters an event | Update/delete denied and event unchanged | Repair grants and endpoint permissions |
| Privileged maintenance runs | Only approved maintenance scope is exercised | Review credential boundary and evidence |
| Authorized restore conflicts with current record | Conflict reported; no blind overwrite | Resolve version and relationship policy |
| Retention expires or hold applies | Correct event classes purge or remain under the authorized hold | Repair lifecycle policy and downstream cleanup |
Keep a positive control: an authorized organisation manager must see the intended history. A test that returns no events to everyone does not prove useful isolation. Record the endpoint's denial contract rather than assuming every filtered SELECT returns an error; some correctly constrained reads return zero rows.
Turn the Worksheet Into an Operational Contract
Before release, retain the completed field specification, role matrix, policy decisions, test fixtures and failure evidence. Assign owners for backlog alerts, retention jobs, recovery approval and history exports. The absence of recorded incidents is not proof that the event capture was complete.
Monitor whether the audit mechanism itself has stopped or whether access patterns are unexpected. Restrict the monitoring data and avoid logging a full sensitive event again while diagnosing a logging problem. OWASP specifically recommends testing logging failures and verifying access controls on log data. OWASP logging verification
A useful audit trail gives attributable, scoped evidence of the actions the product promises to preserve. Recovery capability and legal retention decisions remain separately specified and verified.
Frequently Asked Questions
Should customers be able to view audit trails?
A scoped customer change-history view can be useful for authorized organisation roles. Keep internal security logs and sensitive fields separate, and enforce the same record and organisation permissions on every history route.
How long should audit trails be kept?
Choose an approved period for each event and recovery-data class based on the service's requirements and obligations. Do not assume a universal one-to-seven-year rule. Define deletion, backups, exports and authorized holds explicitly.
Can audit trails impact system performance?
Yes. Measure write latency, event volume and backlog. If complete history is promised, retain durable capture even when delivery is asynchronous; an unpersisted message after a successful mutation can be lost.
How to prevent audit trail tampering?
Restrict ordinary update/delete privileges, separate maintenance roles and monitor access. Evaluate privileged-role exceptions and any independent integrity controls; a separate table alone does not guarantee immutability.
If your business needs to implement or improve audit trails for your SaaS product, understanding these principles and using a clear product specification is vital. For professional guidance on SaaS development, get in touch through our SaaS development service.
If you need help turning these requirements into tested product behavior, get in touch about SaaS development.
For more on SaaS product design and maintenance, see our guides on CMS vs custom development and website maintenance costs. Understanding the user journey also helps design better audit and recovery workflows.