What to audit and why

Capsule overview

Before writing a single trigger, you have to make three decisions that look trivial and almost nobody gets right: which tables to audit, which columns within each table to audit, and for what purpose (compliance, debugging, UX). Skipping these decisions leads to one of two extremes. On one side, teams that audit everything "just in case" and end up with a log full of noise (last_seen_at changing on every request, updated_at being recorded as a separate event) that nobody consults because the signal is buried. On the other, teams that leave out columns the regulator later asks to see and discover too late that the audit log doesn't satisfy the audit it exists for.

In this capsule you're going to build a concrete decision framework. You're going to learn the audit log's three "what for"s (compliance, debugging, UX) and how each one changes what you audit. You're going to see a matrix that classifies columns into "always audit," "never audit," and "audit when it applies." You're going to understand why passwords, tokens, and unnecessary PII are the audit log's enemies, not its allies. And you're going to design the base schema of the audit.task_log table you'll use in capsules 03 and 08, calibrated to the TaskFlow case.

By the end you'll have criteria to make the "does this column go into the audit log?" call in under a minute, and you'll be able to defend it in a code review. It's a design capsule, not a code one — but it defines the success of everything that comes after.

The problem: the audit log nobody consults

Imagine two real audit logs in production.

Audit log A (over-audited):

2026-04-15 10:00:01.123 | task#42 | UPDATE | user=null | diff={"last_seen_at": ["2026-04-15T09:59:58Z", "2026-04-15T10:00:01Z"]}
2026-04-15 10:00:02.456 | task#42 | UPDATE | user=null | diff={"last_seen_at": ["2026-04-15T10:00:01Z", "2026-04-15T10:00:02Z"]}
2026-04-15 10:00:03.789 | task#42 | UPDATE | user=null | diff={"updated_at": ["2026-04-15T10:00:01Z", "2026-04-15T10:00:03Z"]}
... (1.2M more rows per day, all similar)

Is this audit log good for anything? For almost nothing. When support looks for "what changed on task#42 yesterday," they have to manually filter to ignore the last_seen_at changes. The query is slow because the table is enormous. The compliance officer looks at the log and says "I don't see anything useful here."

Audit log B (audited with criteria):

2026-04-15 09:14:22 | task#42 | UPDATE | user=47 | diff={"status": ["open", "in_progress"]}
2026-04-15 14:33:01 | task#42 | UPDATE | user=47 | diff={"title": ["Buy milk", "Buy whole-grain bread"]}
2026-04-15 18:02:45 | task#42 | UPDATE | user=12 | diff={"assignee_id": [47, 23], "status": ["in_progress", "blocked"]}

Three rows, three meaningful changes, three identifiable users, three actionable diffs. When support opens the log, they see the task's full history with no noise. The compliance officer can demonstrate to an auditor exactly what changes happened and who made them.

The difference isn't technical. The trigger is the same. The difference is that log B audits meaningful columns and excludes the noisy ones. And it captures the user_id (not null). That's the decision this capsule helps you make.

Mental model: the audit log as a contract with your future self

Think of the audit log like an airplane's black box. The black box doesn't record everything that happens on the plane — it records a carefully chosen subset of variables (altitude, speed, cockpit voice, engine parameters) that lets you reconstruct what matters after an incident. If it recorded everything (the pressure of every bolt, the temperature of every seat), the file would be so big nobody could analyze it and the additional weight would affect the plane.

Your audit log works the same way. The question isn't "what can I audit?" but "what do I need to be able to answer in six months when somebody asks me?". If you don't anticipate the question, you don't know what to store. And if you store everything, the signal gets lost in the noise.

The audit log is a contract with your future self. What you decide to audit today is what you'll be able to answer tomorrow. What you decide to exclude, you lost. There's no way to "retroactively audit" a column — the past changes already happened and they're gone. This forces you to make the decision properly from the start.

The audit log's three "what for"s

Before choosing what to audit, you need to know what you're auditing for. There are three distinct purposes, and each one changes the decisions you'll make.

Purpose 1: Regulatory compliance

What motivated this purpose: SOX (financial), GDPR (privacy), HIPAA (health), PCI-DSS (payments), SOC 2 (general security). Each framework has its nuances, but they all require demonstrating traceability of changes over sensitive data.

What you need to be able to answer:

• Who accessed or modified this data and when?
• What was the exact change (previous and new value)?
• Was the access authorized at that moment?
• How long is the log retained? (typically 7 years under SOX, 6 years under HIPAA)

What to audit: every column with sensitive data or regulatory control (contractual state, amounts, authorizations, medical data, financial data). Exclude technical columns (update timestamps, visit counters) that add no regulatory value.

Retention: long (years). The compliance audit log is typically immutable and archived, not just stored.

Purpose 2: Operational debugging

What motivated this purpose: a customer opens a ticket saying "my task changed on its own." The team needs to reconstruct the record's history to diagnose the bug.

What you need to be able to answer:

• What changes did this record have in the last 24 hours?
• Who made the change the customer is reporting?
• Which API endpoint generated that change?
• Did the change come from a user, a batch job, or a migration?

What to audit: columns the customer can perceive directly (state, title, assignment, priority). It's useful to add extra metadata: request_id to correlate with the app's logs, source (api, cron, migration) to distinguish the change's origin.

Retention: short or medium (30-180 days). Bugs typically get reported within a few days of the incident; older logs are archivable.

Purpose 3: UX features (timeline, undo, snapshots)

What motivated this purpose: the product team wants to show the user their task's history ("your task went from 'open' to 'in_progress' on Tuesday at 14:33"), or allow undoing the last change, or show previous versions.

What you need to be able to answer:

• What's the state before the last change? (for undo)
• What's the list of changes the user sees in the timeline?
• Which changes were made by the current user vs by others?

What to audit: columns visible to the user. It's useful to enrich the log with human context (a description of the change, not just the diff).

Retention: medium (90 days - 2 years). Enough for the feature to be valuable without accumulating forever.

The three aren't mutually exclusive

In production the three purposes coexist. A typical SaaS audit log serves all three at once: it records meaningful changes (compliance), it allows debugging when support asks (debugging), and it feeds the GET /tasks/{id}/history endpoint (UX). The winning design is the one that satisfies all three with a single system, not three parallel systems.

Decision matrix: which columns to audit

This matrix classifies typical columns of a table in a SaaS. Apply it to every table you're going to audit.

Quick heuristic: if the question "who changed X and when?" makes sense for that column, audit it. If the question doesn't make sense (nobody cares who bumped the view_count), don't.

The special case of passwords and tokens

Always keep this in mind: NEVER audit passwords (even hashed ones) or tokens (even revoked ones).

Why? The blast radius. If an attacker compromises your database, they also compromise the audit log. If the audit log contains password_hash before and after every change, you're handing the attacker the complete history of hashes — useful for cracking passwords of accounts that no longer exist, or for spotting password reuse across users. If it contains tokens, you're handing them revoked tokens the attacker can try to reuse (some legacy systems accept "expired" tokens if the entropy is low).

The operational rule: in the trigger, explicitly filter out the sensitive columns before generating the diff. You'll see how in capsule 03 with payload - 'password_hash' - 'api_token'.

"Noise vs signal" in practice

The audit log's most expensive mistake isn't leaving out an important column (that gets noticed fast). It's auditing noisy columns and diluting the signal until it's unusable.

A real case: a team audits the users table with a trigger that captures any UPDATE. The table has a last_active_at column that the frontend updates every time the user navigates. Result: the audit.user_log table receives millions of inserts a day, all with a trivial last_active_at diff. When someone asks "what changes did user 42 have this week?", the query returns 18,400 rows. All of them last_active_at. The relevant row (an email change made by an admin) is buried.

The solution: filter inside the trigger before inserting. You'll see the pattern in capsule 03 — but the design starts here, by deciding that last_active_at simply doesn't get audited.

Operational heuristic: if a column changes more than 100 times a day per entity, it probably shouldn't be audited. If in doubt, measure it: count changes per column in production for a week. The columns with a rate of change orders of magnitude higher than the rest are candidates for exclusion.

The audit log's base schema

With the criteria above, you can design the audit table's schema. This is the schema you'll use in capsules 03 and 08, in TaskFlow's audit.task_log.

-- A separate schema for audit (better organization; allows differentiated permissions)
CREATE SCHEMA IF NOT EXISTS audit;

-- The audit log table for tasks
CREATE TABLE audit.task_log (
    id BIGSERIAL PRIMARY KEY,
    -- Which entity changed
    entity_id BIGINT NOT NULL,
    -- What kind of change
    action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    -- When
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- Who (comes from the app's SET LOCAL audit.user_id)
    changed_by BIGINT NULL,
    -- The diff: which columns changed and their before/after values
    -- Structure: {"column_name": [old_value, new_value]}
    -- On INSERT: {"column": [null, new_value]}
    -- On DELETE: {"column": [old_value, null]}
    diff JSONB NOT NULL,
    -- Optional metadata for debugging
    request_id UUID NULL,
    source TEXT NULL CHECK (source IN ('api', 'cron', 'migration', 'manual') OR source IS NULL)
);

-- Indexes: the audit log gets queried by entity, by time, or both
CREATE INDEX idx_audit_task_log_entity_time
    ON audit.task_log (entity_id, changed_at DESC);

CREATE INDEX idx_audit_task_log_changed_by_time
    ON audit.task_log (changed_by, changed_at DESC)
    WHERE changed_by IS NOT NULL;

-- A GIN index on diff for searches like "which changes touched status"
CREATE INDEX idx_audit_task_log_diff_gin
    ON audit.task_log USING GIN (diff jsonb_path_ops);

The schema's decisions explained:

• A separate audit schema: it allows differentiated GRANTs (the main app writes; reporting only reads), it simplifies separate backups, and it makes clear this is operational metadata, not domain data.
• action as TEXT with a CHECK: you could use an ENUM, but TEXT with a CHECK is more flexible to evolve (adding RESTORE, MERGE later). The cost of a custom ENUM isn't justified.
• changed_at with DEFAULT NOW(): the trigger doesn't need to set it explicitly; it fills itself in. Use TIMESTAMPTZ (with timezone) by default.
• changed_by NULLABLE: some changes come from jobs with no user (cron, migration). Forcing NOT NULL would paint you into a corner; better to allow NULL and document it with source.
• diff JSONB: the {"column": [old, new]} format is directly comparable, easy to inspect by eye, and GIN indexing allows queries like "which changes touched status."
• Strategic indexes: the three typical query patterns are "by entity," "by the user who made the change," and "by kind of change." Each one has its index.

Explicitly postponed decisions:

• Partitioning by date: vital for production (you'll see it in capsule 07), but the base schema doesn't include it. It teaches you why the table will force a refactor once it grows.
• Retention policy: another responsibility of capsule 07. The schema just declares the table; the deletion/archive policies are operational.
• Replication to an external system (S3, BigQuery): out of the module's scope; it's typically the responsibility of a separate ETL job.

Why does this matter in real work?

1. It's the first review a compliance officer does when looking at your schema. "Which columns do you audit in users? Why don't you audit salary in employees? Why do you audit view_count (this is noise)?". If you arrive at the audit without having made these decisions explicitly, you get forced to make them under pressure and badly.

2. It's what separates an audit log that "records things" from one that "lets you answer questions." The second is valuable; the first is operational overhead with no return. The difference isn't the trigger's code, it's the design of what to audit.

3. It's a storage cost lever. A table of 100M rows/year (auditing everything) costs proportionally more than one of 10M rows/year (auditing with criteria). In self-hosted PostgreSQL, that's disks and maintenance. In the cloud (RDS, Aurora), those are measurable dollars every month.

4. It's what your product team is going to ask you for sooner or later. "We want an activity timeline on each task" is a common feature. If your audit log was designed with UX in mind (not just compliance), the feature is nearly ready. If it was designed only for compliance, you're going to refactor.

Traps and common mistakes

Mistake 1 (conceptual): "audit everything, we'll filter later"

Symptom: the team decides that when in doubt, better to store it. The trigger captures every change with no filters. The audit.* table grows to millions of rows/day with 95% noise.

Why it's confusing: it seems "safer." The intuition is "if I don't audit X, I won't be able to see X later. Better store it all." The reality is: an audit log with noise is worse than an incomplete audit log, because the signal gets buried and nobody consults it.

How to tell: look at the ratio of rows in the log vs rows in the audited table. If the audit log has 100x more rows than the source table, you're probably auditing noise (each row has hundreds of changes, almost all trivial).

How to fix it: apply the decision matrix. Identify the 2-3 noisiest columns and explicitly exclude them from the trigger (you'll see the pattern with IF NEW.status IS DISTINCT FROM OLD.status THEN ... in capsule 03). The log stops growing linearly with traffic and starts growing with meaningful changes.

Mistake 2 (security): auditing passwords or tokens

Symptom: a generic trigger that serializes the whole row to JSONB without filtering out sensitive columns. The audit log contains password_hash before and after every change.

Why it happens: the temptation is to write a reusable "audit everything" trigger. to_jsonb(NEW) is one line; adding column filters requires knowing the domain. Devs new to the pattern go for the generic version.

How to tell: open your audit log and look for sensitive columns: SELECT diff FROM audit.user_log WHERE diff ? 'password_hash' LIMIT 1. If it returns something, you have a serious security problem that requires immediate remediation (purge the log, change passwords, notify security).

How to fix it: in the trigger, filter explicitly: to_jsonb(NEW) - 'password_hash' - 'api_token' - 'refresh_token'. Document the list of excluded columns in a comment on the trigger. You'll see the complete pattern in capsule 03.

Mistake 3 (conceptual): confusing a "system event log" with an "audit log"

Symptom: the team adds system events to the audit log ("the table was reindexed," "the cron ran," "migration X was executed") alongside data changes.

Why it's confusing: both are "logs." The distinction isn't obvious until somebody asks "who changed this task yesterday?" and the answer includes 4,000 irrelevant cron events.

How to tell: they're two different systems. Audit log = changes to domain entities (tasks, users, contracts). Application log = system events (jobs running, deploys, errors). The first answers "what happened to this record." The second answers "what happened to the system."

How to fix it: keep two distinct systems. The audit log in PostgreSQL with triggers (this module). The application log to stdout → a centralized system (Loki, CloudWatch, Datadog). Don't mix them. When you're unsure "does this go in the audit log?", ask: "is it a change to a row in a domain table?". If not, it isn't audit log material.

Mistake 4 (operational): not documenting the audit decisions

Symptom: two years ago someone decided not to audit tasks.estimated_hours. Today a compliance officer asks why it doesn't show up in the log. Nobody on the team knows the reason. You're forced to defend a decision you don't understand.

Why it happens: "what to audit" decisions get made silently, in code, with no context. When someone questions them, the context is gone.

How to fix it: keep an AUDIT-DECISIONS.md document in the project's repo, with sections per table, listing which columns are audited and which aren't, with a one-line justification per excluded column. It's trivial work at design time; priceless when defending the decision months later.

Example:

## Table `tasks`

**Audited:** title, description, status, priority, assignee_id, deleted_at

**Excluded:**
- `view_count`: a volatile counter, changes with every request, noise with no value.
- `updated_at`: redundant, the log already has `changed_at`.
- `cached_completion_score`: derived from other columns, recomputable.

**Decided:** 2026-04-12 by the Backend team.
**Revisit if:** a regulatory obligation on completion scores is added (doesn't apply today).

Exercises

Exercise 1: classify the columns of a real table

You're given the following schema. Classify each column into "always audit," "never audit," or "audit case by case." Justify each one with a single line.

CREATE TABLE invoices (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    invoice_number TEXT NOT NULL UNIQUE,
    status TEXT NOT NULL CHECK (status IN ('draft', 'sent', 'paid', 'cancelled')),
    amount_cents BIGINT NOT NULL,
    currency CHAR(3) NOT NULL,
    issued_at DATE NOT NULL,
    paid_at TIMESTAMPTZ NULL,
    payment_method TEXT NULL,
    payment_token TEXT NULL,
    notes TEXT NULL,
    last_email_sent_at TIMESTAMPTZ NULL,
    email_send_count INTEGER NOT NULL DEFAULT 0,
    pdf_cache_url TEXT NULL,
    pdf_cache_generated_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ NULL
);

Exercise 2: detect the noise in an existing audit log

Your team shows you these stats from the last week's audit log:

SELECT
    diff::text ~ '"status"' AS has_status_change,
    diff::text ~ '"last_seen_at"' AS has_last_seen_change,
    COUNT(*) AS rows
FROM audit.user_log
WHERE changed_at > NOW() - INTERVAL '7 days'
GROUP BY 1, 2;

 has_status_change | has_last_seen_change | rows
-------------------+----------------------+----------
 false             | true                 | 4,800,000
 true              | false                | 2,100
 false             | false                | 12,000
 true              | true                 | 350

Diagnose the problem and propose a fix.

-- Pseudo-code for the trigger (full PL/pgSQL in capsule 03)
IF NEW.last_seen_at IS DISTINCT FROM OLD.last_seen_at AND
   -- If ONLY last_seen_at changed, don't audit
   (NEW.email IS NOT DISTINCT FROM OLD.email AND
    NEW.status IS NOT DISTINCT FROM OLD.status AND
    -- ... other meaningful columns
   ) THEN
    RETURN NULL;  -- Skip audit
END IF;

## Table `users`
**Excluded:** last_seen_at — a volatile counter, noise with no audit value.

Exercise 3: design the audit schema for a new table

Your team is going to add a subscriptions table to the app. The proposed schema:

CREATE TABLE subscriptions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    plan TEXT NOT NULL CHECK (plan IN ('free', 'pro', 'enterprise')),
    started_at TIMESTAMPTZ NOT NULL,
    cancelled_at TIMESTAMPTZ NULL,
    next_billing_at TIMESTAMPTZ NULL,
    last_payment_at TIMESTAMPTZ NULL,
    payment_failures INTEGER NOT NULL DEFAULT 0,
    stripe_customer_id TEXT NULL,
    metadata JSONB NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Design the corresponding audit.subscription_log table and justify the decisions.

CREATE TABLE audit.subscription_log (
    id BIGSERIAL PRIMARY KEY,
    entity_id BIGINT NOT NULL,
    action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changed_by BIGINT NULL,
    diff JSONB NOT NULL,
    request_id UUID NULL,
    source TEXT NULL CHECK (source IN ('api', 'cron', 'webhook', 'migration', 'manual') OR source IS NULL)
);

CREATE INDEX idx_audit_subscription_log_entity_time
    ON audit.subscription_log (entity_id, changed_at DESC);

CREATE INDEX idx_audit_subscription_log_changed_by_time
    ON audit.subscription_log (changed_by, changed_at DESC)
    WHERE changed_by IS NOT NULL;

CREATE INDEX idx_audit_subscription_log_diff_gin
    ON audit.subscription_log USING GIN (diff jsonb_path_ops);

## Table `subscriptions`

**Audited:** user_id, plan, started_at, cancelled_at, metadata (filtered)

**Excluded:**
- `next_billing_at`, `last_payment_at`: updated by billing jobs; noise with no value.
- `payment_failures`: a counter; noise.
- `stripe_customer_id`: it rarely changes; audit on INSERT, skip on routine UPDATEs.
- `metadata.internal_notes`: internal keys; explicitly excluded from the diff.
- `created_at`, `updated_at`: redundant with `changed_at`.

**Decided:** 2026-04-15 by the Backend team.
**Revisit if:** a PCI obligation on auditing payment events is added (likely Q3).

Exercise 4: detect passwords in an existing audit log

Write the SQL query that detects whether your audit log contains sensitive columns audited by mistake. Your app has a password_hash field in users and an api_token in api_keys. Audit both logs.

-- Look for password_hash in any audit log
SELECT
    'users' AS source_table,
    COUNT(*) AS contaminated_rows,
    MIN(changed_at) AS first_occurrence,
    MAX(changed_at) AS last_occurrence
FROM audit.user_log
WHERE diff ? 'password_hash' OR diff::text LIKE '%password%';

-- Look for api_token in any audit log
SELECT
    'api_keys' AS source_table,
    COUNT(*) AS contaminated_rows,
    MIN(changed_at) AS first_occurrence,
    MAX(changed_at) AS last_occurrence
FROM audit.api_key_log
WHERE diff ? 'api_token' OR diff::text LIKE '%token%';

-- Generic version: look for sensitive columns in ALL the logs
DO $$
DECLARE
    audit_table RECORD;
    contaminated INTEGER;
BEGIN
    FOR audit_table IN
        SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'audit'
    LOOP
        EXECUTE format(
            'SELECT COUNT(*) FROM %I.%I WHERE diff::text ~* %L',
            audit_table.schemaname,
            audit_table.tablename,
            '(password|token|secret|api_key|private_key)'
        ) INTO contaminated;

        IF contaminated > 0 THEN
            RAISE NOTICE 'CONTAMINATED: %.% has % rows with sensitive data',
                audit_table.schemaname, audit_table.tablename, contaminated;
        END IF;
    END LOOP;
END $$;

-- Pseudo-code (full PL/pgSQL in capsule 03)
INSERT INTO audit.user_log (entity_id, action, diff)
VALUES (
    NEW.id,
    'UPDATE',
    (to_jsonb(NEW) - 'password_hash' - 'api_token')
);

async def test_audit_log_no_contiene_passwords(session: AsyncSession):
    """The audit log must NEVER contain password_hash."""
    user = User(email="test@x.com", password_hash="$2b$12$abc...")
    session.add(user)
    await session.commit()

    user.email = "new@x.com"
    await session.commit()

    # Verify: the audit log must NOT have password_hash
    result = await session.execute(text("""
        SELECT COUNT(*) FROM audit.user_log
        WHERE diff ? 'password_hash'
    """))
    assert result.scalar() == 0, "password_hash leaked into the audit log"

Exercise 5: justify decisions in a compliance question

Scenario: a compliance officer asks why you don't audit the tasks.view_count column. Argue your position in 3-4 sentences.

Summary and next step

In this capsule you learned:

• The audit log is a contract with your future self: what you audit today is what you'll be able to answer tomorrow. What you exclude, you lost forever. This forces you to make the decisions properly at the start.
• Three distinct purposes for the audit log (compliance, debugging, UX), each one changes what you audit. In production the three coexist in a single system.
• A decision matrix by column type: domain state (always), content data (always), counters/cache/timestamps (never), passwords/tokens (NEVER), PII (case by case).
• Mistake #1 is auditing noise: volatile counters, technical timestamps, and derived cache generate logs of millions of rows with zero signal. It dilutes what matters.
• Mistake #2 is auditing passwords/tokens: it increases the blast radius of a database compromise. Filter explicitly in the trigger.
• The base schema of audit.task_log you'll use in capsules 03 and 08: a separate audit schema, entity_id + action + changed_at + changed_by + diff JSONB, with strategic indexes over the typical query patterns.
• Documenting the decisions in AUDIT-DECISIONS.md keeps you from being unable to defend why you don't audit column X six months from now.

Before moving on you should be able to:

• Apply the decision matrix to a new table in under five minutes.
• Explain the difference between an audit log and an application log to a junior teammate.
• Detect noise in an existing audit log with SQL queries.
• Argue your "don't audit X" decision to a compliance officer.
• Design the schema of an audit.X_log table with its strategic indexes.

Next capsule — PostgreSQL triggers for auditing. You're going to implement the module's default approach: PL/pgSQL triggers that write to audit.task_log on every INSERT/UPDATE/DELETE. You're going to learn the key detail (SET LOCAL audit.user_id from a FastAPI dependency, current_setting() from the trigger), how to capture the diff by comparing OLD and NEW, how to filter out sensitive columns, and how to avoid auditing irrelevant changes. It's the module's most important technical capsule and the pattern the module project (capsule 08) consolidates.

Resources

1. PostgreSQL Documentation — JSONB Functions and Operators — the reference for the - operator to exclude keys from a JSONB and for ? to check existence. Useful when filtering out sensitive columns.
2. PCI-DSS v4.0 — Requirement 10: Logging and Monitoring — what the payments standard requires of audit logs. Required reading if your app processes payments.
3. GDPR Article 30 — Records of processing activities — what kind of "processing records" GDPR requires. It isn't a strict technical audit log, but it motivates many decisions.
4. SOC 2 — Trust Services Criteria — the framework most B2B SaaS uses. The "Monitoring activities" section guides what to audit.
5. Vlad Mihalcea — "How to audit Hibernate entities" — a comparison of approaches in another stack; useful for seeing how they thought about the problem in JPA/Hibernate and bringing the lessons to Postgres + SQLAlchemy.
6. Brandon Williams — "What and Why of Audit Logging" — a classic reference on when the audit log exists for compliance vs for operational value.
7. Supabase docs — Auditing — a real audit implementation with triggers in multi-tenant production; a direct inspiration for capsule 03's pattern.

Module 3 — SQL Patterns for Production APIs Guide

Next capsule: PostgreSQL triggers for auditing — implementing the default approach with PL/pgSQL and FastAPI integration.