Modeling Audit Trails: Ensuring Data Integrity in Databases

Previously in this course, we discussed Data Archiving Strategies to maintain performance as your SaaS grows. Now that we know how to move old data, we need to focus on visibility: how do we track who changed what and when?

In production environments, simply knowing the current state of a record isn't enough. You need an audit trail to satisfy compliance requirements, debug user errors, or recover from accidental data modification. This lesson covers how to implement effective tracking within your database design.

The Principles of Audit Logs

An audit log is a chronological record of changes made to your data. Unlike the "current state" of a user record, an audit trail is an append-only ledger. It must capture three core dimensions: Who changed it, What changed, and When it happened.

When designing these, avoid the temptation to just add updated_by columns to your main tables. While that tells you the last person to touch a row, it doesn't give you the historical context of previous values. For robust data integrity, you need a dedicated structure.

Choosing Your Strategy

There are two primary ways to implement audit logs:

For most SaaS startups, application-level logging is preferred because it allows you to capture metadata (like user agent or request ID) that is often unavailable inside the database engine.

Designing the Audit Table

We will create a generic audit_log table. To keep it flexible, we use a JSONB column (in PostgreSQL) to store the "diff" of the changes.

SQL
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id INT NOT NULL,
    action_type VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
    changed_by_user_id INT,
    old_values JSONB,
    new_values JSONB,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_record ON audit_logs(table_name, record_id);

Worked Example: Tracking User Updates

Imagine we are updating a user’s email address. Instead of just running an UPDATE query, our application logic performs the audit:

1. Fetch the current record.
2. Perform the update.
3. Write the diff to audit_logs.

SQL
-- The application performs the update:
UPDATE users SET email = 'new@example.com' WHERE id = 123;

-- The application then logs the change:
INSERT INTO audit_logs (table_name, record_id, action_type, changed_by_user_id, old_values, new_values)
VALUES (
    'users', 
    123, 
    'UPDATE', 
    45, -- The Admin ID
    '{"email": "old@example.com"}', 
    '{"email": "new@example.com"}'
);

Hands-on Exercise

Using the schema above, write the SQL to log a "Soft Delete" operation for a subscription record (as we covered in Handling Soft Deletes).

Task:

1. Assume the subscription record with id: 50 was deleted.
2. The old_values should contain the subscription status active.
3. The new_values should reflect the deleted_at timestamp.

Common Pitfalls

• Auditing Everything: Don't audit high-frequency, low-value tables (like a session_heartbeat table). You will quickly bloat your database and degrade performance.
• Ignoring Sensitive Data: If your columns contain PII (Personally Identifiable Information), ensure your audit_logs table has the same encryption or access control policies as the main tables.
• Missing Context: Never log just the change without the user_id of the actor. An audit trail without an actor is just a log of events, not a history of accountability.

Frequently Asked Questions

Q: Should I use a single audit_logs table or one for each entity? A: Start with one generic audit_logs table. It’s easier to maintain and query across the entire system. Only move to entity-specific tables if the volume is so massive that it causes index contention.

Q: Does JSONB in Postgres affect performance? A: JSONB is highly efficient for storage and retrieval. As long as you aren't performing complex aggregations on the JSON keys, it’s the standard choice for audit trails.

Recap

Audit logs are essential for modern database design. By separating your audit data from your operational data and capturing the "before" and "after" states, you provide your team with a powerful tool for troubleshooting and compliance. Remember to favor application-level logging to keep your business logic transparent and your database clean.

Up next: We will discuss how to manage time-series data, specifically for tracking usage metrics and system events.