Module 3: Audit Logs and History Tables

Audit Logs and History Tables — Module introduction

Next →

Capsule overview

In the previous module you learned not to lose the data when something gets deleted. This module teaches you not to lose the change when something gets modified. They're sibling problems: soft delete preserves the "what was deleted"; the audit log preserves the "who, when, and what changed." In production the two coexist, and together they form what's called "defensive modeling" — the layer of your schema that exists so that no incident, regulator, or customer asks you something your database can't answer.

The question is simple: "who changed this task's state from open to archived on Tuesday night?". The answer can take three very different forms. PostgreSQL triggers, which record the change's metadata in a separate table. History tables, which duplicate the complete row with temporal validity marks. Lightweight event sourcing, which stores the raw events and rebuilds the state by replay. All three answer the same question. All three have different operational costs. Most teams pick the first one they find in a blog post, without understanding what they're sacrificing.

By the end of this module you'll know when each approach is the right answer, you'll have runnable code for all three in PostgreSQL 16+ and SQLAlchemy 2.0 async, and you'll have integrated an audit log with triggers into a real tasks table — the same pattern the capstone project of module 8 will use when combining audit with multi-tenancy. You'll also learn the detail that separates a useful audit log from a useless one: how to pass the request's user_id to the trigger using SET LOCAL, without which the table only records "somebody changed something" — information with no value for real auditing.

Where are we? Where are we going?

In module 1 you learned cursor pagination. In module 2 you learned soft delete with partial indexes and automatic mechanisms. You solved how to navigate large volumes and how not to lose data when something gets deleted. But there's an operational question still open: when a customer opens a support ticket saying "somebody changed my task's title and it wasn't me," do you have a way to answer?

Without an audit log the answer is "no." The current state is in tasks, the deleted state is in deleted_at, but the intermediate change (from "Buy milk" to "Buy bread," on Tuesday at 21:14, done by user_id = 47) doesn't exist anywhere. Soft delete preserves the row; the audit log preserves the history. They're complementary, not substitutes.

After this module comes module 4 (Multi-Tenancy with RLS), which opens the "Live Operations" block. The transition is natural: you already know how to preserve the data (soft delete) and the change (audit log). It's time to learn how to isolate them per tenant. And you're going to discover that RLS, on top of isolation, simplifies audit logs in multi-tenant setups — because the tenant_id is already available in the trigger's context, without your app having to pass it by hand.

Professional objective

By the end of this module you'll be able to:

  • Identify what to audit (who/when/what) and what NOT to (passwords, unnecessary PII, columns that change on every request) with criteria grounded in compliance and debugging.
  • Implement an audit log with PostgreSQL 16+ triggers: a PL/pgSQL function that captures OLD and NEW, writes the diff as JSONB, and uses current_setting('audit.user_id') to record the change's actor.
  • Pass the app's context to the trigger using SET LOCAL from a FastAPI dependency, the integration that separates an audit log "that exists" from one "that's useful."
  • Implement history tables with valid_from and valid_to, capable of reconstructing any record's state at any moment in the past with a single query.
  • Implement lightweight event sourcing: an append-only events table, replay to rebuild an aggregate, clear criteria for when NOT to use this approach.
  • Compare the three approaches with a quantitative decision matrix: write cost, read cost, operational complexity, cases where each one wins.
  • Design the audit log's retention and partitioning from day one: PostgreSQL declarative partitioning by month, archiving to S3 after N months, keeping the audit log from growing bigger than the audited table.

Why does this module matter?

1. It's the first thing any compliance auditor asks. SOX, GDPR, HIPAA, PCI-DSS — each framework has its nuance, but they all agree on one requirement: "demonstrate that you know who changed which sensitive data." Without an audit log, that question has no answer. The legal team hands you the hot potato and you discover during the audit that your five-year-old app has no history. It's the classic "technically it works, from a regulatory standpoint it doesn't exist."

2. It's the difference between a 30-minute debug and a 3-day one. A customer reports their task changed state on its own. Without an audit log, you have to correlate app logs, access IPs, and float hypotheses. With an audit log, one query (SELECT * FROM audit.task_log WHERE entity_id = 123 ORDER BY changed_at DESC) shows you the change with the user_id, timestamp, and diff. The ticket closes in minutes.

3. It's the technical foundation of modern UX features. "Activity timeline," "undo change," "see previous version," "what changed since the last time I logged in" — they're all features your product can offer when the audit log exists, and that are impossible when it doesn't. Deciding to implement it properly from the start saves you from redesigning the schema when the product team asks for any of those features.

4. It's where most teams over-engineer or under-engineer. Under-engineering: "I'll just store the whole JSON in a notes column" — it ends up an unreadable blob nobody knows how to parse. Over-engineering: "let's go with pure event sourcing and CQRS" in a monolithic API — it ends with a system that's impossible to maintain, solving a problem a 30-line trigger already solved. Knowing how to pick the right approach for the context is a senior skill.

A scenario that illustrates the module

Imagine you join as tech lead at a B2B startup with three years in production. The app manages legal contracts for corporate clients. On your first day, the CISO calls you: they have a SOC 2 audit in six weeks and the auditors want to see the change log of every contract modified in the last twelve months. The app has no audit log. The only thing that exists is the contracts table with its current state.

Your first reflex is to add a last_modified_by and last_modified_at column. You realize it only covers the last change, not the complete history. You change plan: an audit.contract_log table with entity_id, action, changed_at, changed_by, and a JSONB with the diff. You implement the PL/pgSQL trigger that fires on INSERT/UPDATE/DELETE and records the change. You run the first test: the trigger fires but changed_by is always NULL. You discover why: PostgreSQL only knows about the DB connection, not the app's user. You need to pass the context.

You implement a FastAPI dependency that runs SET LOCAL audit.user_id = '47' at the start of every request. The trigger reads current_setting('audit.user_id') and records the right actor. You try an UPDATE and the row shows up in audit.contract_log with all the data. You show the result to the CISO; he's satisfied.

Three weeks later, the product team asks for a new feature: "see the contract as it was on March 15." Your audit log has the diffs, but reconstructing the complete state from diffs is slow and fragile. You decide to add a history table: contracts_history with the complete row, valid_from and valid_to. One query (SELECT * FROM contracts_history WHERE id = 123 AND valid_from <= '2026-03-15' AND valid_to > '2026-03-15') gives you the exact snapshot. The audit log and the history table coexist: the first for "who and why," the second for "how it looked exactly."

Six months later, the audit.contract_log table has 80 million rows and the INSERT starts to feel the weight of the index. You apply partitioning by month (PARTITION BY RANGE (changed_at)) and a monthly job that moves partitions older than 24 months to S3 (parquet). The audit log stays manageable, the audit keeps passing, and you learned the complete cycle: design → integration → scale. It's exactly the cycle this module covers.

Module map

CapsuleTopicWhat you'll learn
02What to audit and whySelection criteria (compliance vs debugging vs UX), which columns to audit and which to exclude, noise vs signal, designing the base schema
03PostgreSQL triggers for auditingA PL/pgSQL function with OLD/NEW, capturing the diff as JSONB, integrating SET LOCAL with a FastAPI dependency
04The history tables patternA mirror table with valid_from/valid_to, point-in-time reconstruction, trade-offs versus triggers
05Lightweight vs full event sourcingAn append-only events table, replaying aggregates, when this approach is over-engineering and when it's the right answer
06Auditing with SQLAlchemy event listenersWhen triggers aren't an option (restricted managed DBs, tests, multi-DB), before_flush and after_flush
07Retention and partitioning of audit logsPARTITION BY RANGE by date, archiving to S3, keeping the audit log from growing forever (a bridge to guide #14)
08Project: audit trail in TaskFlowImplementing an audit log with triggers for a standalone tasks table, integrating with a FastAPI dependency, an "history per entity" view

The narrative flow is: first you understand what to audit and why (capsule 02), then you implement the main approach (triggers, capsule 03), then you get to know its alternatives (history tables and event sourcing, capsules 04-05), you learn the escape hatch for cases where triggers don't apply (capsule 06), and you close with the operational problem every implementation faces (retention, capsule 07). The module project (capsule 08) consolidates the default approach — PostgreSQL triggers — into an implementation ready to be extended in module 8.

Connection with the integrative project

The guide's final project (the TaskFlow API, module 8) implements an audit log with PostgreSQL triggers for the tasks table, integrated with multi-tenancy (RLS, module 4). The user_id and the tenant_id get passed from FastAPI as SET LOCAL, read by the trigger in current_setting(). A task_history_view view lets you query "every change to task X in the last 30 days along with who made them."

This module takes you exactly up to that pattern, but in isolation: only a standalone tasks table, no RLS yet. The module 8 project will combine it with tenant context and show how current_setting('audit.tenant_id') becomes useful once the multi-tenant context is already propagated by a dependency.

What is NOT covered in this module

  • Multi-tenancy and RLS — covered in module 4. Here the audit log is taught in isolation, over a simple tasks table. The integration with tenant_id comes later.
  • Zero-downtime migrations — covered in module 5. If you need to add an audit log to a table in production with no downtime, the expand-contract pattern applies, but it's taught in its dedicated module.
  • Bulk operations — covered in module 7. If your audit log has to absorb millions of changes per hour, the bulk techniques (COPY, batch insert) apply, but their pedagogical home is module 7.
  • Authorization and RBAC — covered in guide #9 (Auth & RBAC). The audit log records "who did what," it doesn't decide "who can do what." They're different topics.
  • Complete event-driven architectures (Kafka, pure CQRS, an event store) — out of scope. Capsule 05 covers "lightweight" event sourcing (an append-only table in PostgreSQL) and explains when to migrate to a dedicated event store, but the complete implementation of an event-driven system is material for another guide.
  • Temporal tables from the SQL:2011 standard — PostgreSQL doesn't support them natively. The history tables you'll build in capsule 04 implement the equivalent pattern with explicit columns.

Traps to avoid while taking the module

1. Don't skip capsule 02 ("what to audit"). It's tempting to go straight to triggers. The problem: if you audit everything, you end up with a log full of noise (last_seen_at changing on every request). If you audit badly, you leave out columns the regulator needs to see. Capsule 02 establishes the criteria; without them, capsules 03-08 teach you to implement the wrong approach efficiently.

2. Don't treat SET LOCAL as an optional detail. The #1 mistake with triggers is: "the trigger works but changed_by is always NULL." The cause is always the same: the app isn't passing the user_id to the trigger. The FastAPI dependency + SET LOCAL audit.user_id integration is the detail that separates an audit log that's useful for real auditing from one that only records "somebody did something."

3. Don't assume event sourcing is "the modern, better approach." It isn't, for this use case. Event sourcing shines when your system ALREADY is event-driven (microservices with Kafka, CQRS, a dedicated event store). Forcing it into a monolithic API to audit a table is over-engineering. Capsule 05 makes it explicit: it's taught so you can recognize when it applies, not to recommend it by default.

4. Don't confuse an audit log with a history table. An audit log is metadata about changes (who/when/what + diff), lightweight. A history table is the complete row duplicated with temporal marks (valid_from/valid_to), heavy but it lets you reconstruct the exact state at any moment. They can coexist and they answer different questions. Capsule 04 establishes the difference with code.

5. Don't leave retention "for later." Audit logs grow without stopping. A table with 10k changes a day generates 3.6M rows a year. In five years, 18M. With no retention policy, the audit log eventually weighs more than the audited table and the INSERT starts to feel it. Capsule 07 covers partitioning by date from day one — it isn't something to postpone.

6. Don't audit passwords, tokens, or unnecessary PII. It's tempting to "store everything just in case." But an audit log with hashed passwords or plaintext tokens is a serious attack vector: the blast radius of an audit log leak is bigger than that of the original table. Capsule 02 establishes the rule: audit the columns that generate audit value, explicitly exclude the sensitive ones.

Self-assessment question

Before starting this module, can you answer?

  • What's the difference between soft delete (module 2) and an audit log? If I know, why do the two coexist in production and it isn't redundant?
  • If a customer asks me "who changed my task's title yesterday?", can my current app answer? If not, what piece am I missing?
  • What is a trigger in PostgreSQL? What's the difference between BEFORE INSERT and AFTER UPDATE?
  • Do I know what current_setting() is in PostgreSQL? If I don't, can I imagine what it might be useful for in an audit trigger?
  • What operational problems can I anticipate from a table that grows without limit (3.6M rows a year)?

If most of the answers are "yes" or "I have some idea," you're ready. If the first two are "no," review module 2 (especially capsule 02 on soft delete trade-offs). If the third and fourth are "no," go over the PL/pgSQL chapter of PostgreSQL's official documentation before capsule 03.

Evidence of success

By the end of this module, you'll know you succeeded if:

  • You can implement an audit log with triggers on a new table in your app in under 30 minutes, including the integration with a FastAPI dependency for SET LOCAL.
  • You can defend in a code review why triggers, history tables, or event sourcing are the right answer for a specific case, with three concrete arguments.
  • You can design the audit log's partitioning schema from the start, without postponing retention "for later."
  • You can explain to a junior teammate the difference between an audit log and a history table in under two minutes, with an example of when each one applies.
  • The module project (capsule 08) is implemented, runnable, with tests verifying the user_id is captured correctly and the JSONB diff is readable.

We start in the next capsule

We start with capsule 02: what to audit and why. Don't jump to triggers yet. You're going to learn the selection criteria — compliance, debugging, UX features — before touching PL/pgSQL. It's the "design" module before the "implementation" module. Whoever skips this ends up with an audit log that records noise and leaves out the signal.

Before moving on, make sure you have PostgreSQL 16+ running locally (Docker or a native install), and a working FastAPI project with SQLAlchemy 2.0 async. If you're coming from module 2, you already have that setup. If not, review capsule 03 of module 2 for the base tasks table setup.

Resources for the module

  1. PostgreSQL Documentation — Triggers — the official reference. Chapters 39 (Triggers) and 41 (PL/pgSQL) are recommended pre-reading for capsule 03.
  2. PostgreSQL Documentation — current_setting() — the function the trigger uses to read the context the app set. A key detail of capsule 03.
  3. Martin Fowler — "Event Sourcing" — the canonical article. Useful as a conceptual reference before capsule 05. Spoiler: Fowler proposes it with caution, not as "the solution."
  4. Vlad Mihalcea — "How to detect and audit data changes with Hibernate Envers" — the Java/Hibernate equivalent. Useful for understanding how other stacks solve the problem and why the idiomatic solution in Postgres + SQLAlchemy is different.
  5. Supabase — pgaudit and audit columns — a reference for how Supabase implements auditing in multi-tenant production. A direct inspiration for the trigger pattern.
  6. Greg Young — "CQRS, Task Based UIs, Event Sourcing agh!" — the coiner of the term introduces event sourcing. Dense material but clarifying for capsule 05.

Module 3 — SQL Patterns for Production APIs Guide

Next capsule: What to audit and why — selection criteria before implementing.

← PreviousModule 2 deliverable: Soft Delete in TaskFlowNext →What to audit and why