Types of Audit

There are two main flavors of audits: technical and business. You might see other names floating around, but for simplicity, let’s stick with these.

Technical audits focus on low-level changes that developers care about, think of them as the behind-the-scenes record of how your system state changes. For example, you might track every time a feature flag is toggled so you can trace exactly when and why your app behaved differently. Application logs often serve as a form of technical audit.

Business audits are designed for end users, managers, or auditors. They usually come wrapped in a friendly interface where you can search, filter the data. In a banking app, for instance, you’d log every transaction on a customer’s account so that finance teams can review and report on activity. Or in a healthcare app, you’d log every change to a patient’s medication record. Although most business audits have a polished UI, sometimes all you really need is a raw history dump, especially if it’s purely for compliance and rarely browsed.

Note: In this post, we’re zooming in on audits at the database level, whether they serve technical or business needs.

Scenarios Where Quick-and-Dirty Auditing Makes Sense

Imagine you run a highly configurable platform where dozens of users (or developers) tweak settings all day long. Inevitably, someone will change the wrong setting and break something. You need a quick way to see what changed, when, and ideally who did it. (This isn’t about assigning blame, it’s about having enough information to understand and fix the problem.)

Building a full-featured audit system from scratch takes time, and often your logs aren’t even kept forever. It’s quicker to write a couple of lines of SQL or enable a built-in feature and call it a day.

Quick Ways to Add Auditing

1. Built‑In Temporal Tables

Some databases like SQL Server have temporal tables built in. All you need to do is enable the temporal option for your table, and behind the scenes, your database starts versioning rows automatically.

If you’re on Postgres, you can get similar magic by installing one of the temporal table extensions. Once it’s set up, every UPDATE or DELETE will log a snapshot of the old row in your history table.

2. Create Your Own Trigger

For systems without native support, roll your own:

  1. Create an audit table to mirror the structure of your source table (plus timestamps).
  2. Write a trigger (AFTER UPDATE, INSERT and DELETE) on your source table.
  3. In the trigger body, insert the old row into the audit table.

This route takes a bit more work, but you get total control, you can tailor it to your needs, adjust the schema, or attach extra metadata as you see fit.

Considerations

Depending on the concrete solution you choose, there are a few challenges to be aware of.

  • New Columns: Altering the source table without updating your audit schema can result in gaps or lost historical data, you may want to address this.
  • Noisy Updates: Columns like last_accessed_at can generate tons of noise, potentially increasing storage and query overhead.
  • User Tracking: The above techniques don’t capture the application user (only the database user). You’ll need to correlate with your application logs to identify who made the change, or add an updated_by column and populate it from your code, though that approach does require application changes.

Misc

Quick Tip for Entity Framework

If you’re using EF Core with SQL Server, temporal tables are just a flag away. In your OnModelCreating, call:

builder.Entity<Student>()
   .ToTable("Students", tb => tb.IsTemporal());

EF will handle table creation and add hidden columns for you. Then you can query historical data like this:

var yesterdayRecords = await dbContext.Students
    .TemporalAsOf(DateTime.UtcNow.AddDays(-1))
    .ToListAsync();

Cloud

If you are on the cloud (specifically Amazon Aurora PostgreSQL), check out this aws guide on how to have an audit trail with a more precise timestamp and without the overhead of triggers. However, the setup is more complex. That’s why I left it out in the main list above.

That’s it, now you’ve got an audit trail with minimal effort. Happy auditing!