The CEO forwarded a support ticket. A customer’s email address had changed, they missed a critical billing notification, and now no one on the team could answer the basic question: who changed it, when, and to what value? The application logs showed a UPDATE users SET email = ... query at 3:14 AM, but the log did not capture the old email, the new one, or which user session ran the query. The team spent two days cross-referencing deployment timestamps, database connection logs, and Slack messages to figure out it was a batch script that had a bug.

That scenario repeats in nearly every codebase that does not have an audit log. The fix is not “add more logging to the application.” The fix is a trigger-based audit log inside PostgreSQL that captures every row-level change on the tables you care about, automatically, without a single line of application code. This post is the exact implementation: the schema, the trigger function, the hstore trick for old and new values, the query patterns that make the data useful, and the performance measurements that prove it works under production load.

The table that stores every change

The heart of an audit log is a single append-only table. Every INSERT, UPDATE, or DELETE on a tracked table produces one row here.

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TYPE audit.operation AS ENUM ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE');

CREATE TABLE audit.audit_log (
    id              bigserial PRIMARY KEY,
    table_name      text NOT NULL,
    operation       audit.operation NOT NULL,
    row_id          text,                            -- PK value of the changed row
    old_values      jsonb,                           -- null on INSERT
    new_values      jsonb,                           -- null on DELETE
    changed_by      text NOT NULL DEFAULT 'unknown',  -- application user
    changed_at      timestamptz NOT NULL DEFAULT now(),
    session_info    jsonb                            -- client_addr, app_name, etc.
);

CREATE INDEX idx_audit_log_table_name ON audit.audit_log (table_name, changed_at DESC);
CREATE INDEX idx_audit_log_row_id ON audit.audit_log (table_name, row_id, changed_at DESC);

Key design decisions in this schema:

jsonb for old and new values. The alternative is one column per tracked field, which breaks every time you add a column to the source table. JSONB stores the entire row as a key-value map, which means the audit log adapts to schema changes without migrations. If you add a phone column to users tomorrow, the next UPDATE on that row captures the old and new phone values automatically.

row_id stores the primary key as text. Using text instead of a typed foreign key avoids needing a different audit table per source table. If your PK is a UUID, a serial integer, or a composite key serialized as col1,col2, it fits in this column. The trade-off is that you cannot enforce referential integrity on the audit log, but that is acceptable: the audit log is a historical record, not a live constraint.

Separate indexes on (table_name, changed_at DESC) and (table_name, row_id, changed_at DESC). Every query against the audit log filters by table name first. The first index supports “show me the last 100 changes across all orders.” The second supports “show me every change to this specific order row.” Both use a descending sort on changed_at because the most common query is “what changed recently.”

The trigger function that does the work

PostgreSQL trigger functions are written in PL/pgSQL. The function below is generic: it reads the operation type from TG_OP, captures old and new row data into JSONB, and INSERTs into the audit log. It works on any table without modification.

CREATE OR REPLACE FUNCTION audit.audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    pk_value    text;
    old_row     jsonb;
    new_row     jsonb;
    user_name   text;
    session_data jsonb;
BEGIN
    -- Detect the application user. Fall back to session user if not set.
    user_name := current_setting('app.current_user', true);
    IF user_name IS NULL THEN
        user_name := session_user;
    END IF;

    -- Capture session metadata once per statement.
    session_data := jsonb_build_object(
        'client_addr', inet_client_addr(),
        'client_port', inet_client_port(),
        'backend_pid', pg_backend_pid(),
        'application_name', current_setting('application_name', true)
    );

    -- Identify the primary key value.
    -- Assumes the first column is the PK (true for typical `id` columns).
    -- For composite PKs, override this logic per table.
    BEGIN
        pk_value := NEW.pk_value_from_first_col::text;
    EXCEPTION WHEN OTHERS THEN
        pk_value := NULL;
    END;

    IF TG_OP = 'INSERT' THEN
        SELECT row_to_json(NEW)::jsonb INTO new_row;
        INSERT INTO audit.audit_log (table_name, operation, row_id, old_values, new_values, changed_by, session_info)
        VALUES (TG_TABLE_NAME, 'INSERT', pk_value, NULL, new_row, user_name, session_data);
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        SELECT row_to_json(OLD)::jsonb INTO old_row;
        SELECT row_to_json(NEW)::jsonb INTO new_row;
        INSERT INTO audit.audit_log (table_name, operation, row_id, old_values, new_values, changed_by, session_info)
        VALUES (TG_TABLE_NAME, 'UPDATE', pk_value, old_row, new_row, user_name, session_data);
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        SELECT row_to_json(OLD)::jsonb INTO old_row;
        INSERT INTO audit.audit_log (table_name, operation, row_id, old_values, new_values, changed_by, session_info)
        VALUES (TG_TABLE_NAME, 'DELETE', pk_value, old_row, NULL, user_name, session_data);
        RETURN OLD;

    ELSIF TG_OP = 'TRUNCATE' THEN
        INSERT INTO audit.audit_log (table_name, operation, row_id, old_values, new_values, changed_by, session_info)
        VALUES (TG_TABLE_NAME, 'TRUNCATE', NULL, NULL, NULL, user_name, session_data);
        RETURN NULL;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Two details here that separate a production audit log from a toy.

current_setting('app.current_user', true) reads a custom configuration parameter that your application sets at the start of each session. This is how the audit log captures the real application user (“alice@company.com”) instead of the database user (“app_svc_production”). Your app sets it once per connection:

// Node.js: set once when you acquire a connection
await client.query("SET app.current_user = $1", [req.user.email]);

Without this, every change logs as app_svc_production and is useless for compliance audits. With it, the authenticated user flows through the connection pool into every logged operation.

SECURITY DEFINER ensures the trigger runs with the privileges of the function owner (who can write to audit.audit_log), not the application user who triggered the change. This lets you grant only INSERT/UPDATE/DELETE on application tables to your app user while the trigger silently writes to a table the app user cannot directly modify. That means an attacker who compromises your app can still not delete or alter the audit trail through normal queries.

Attaching the trigger to your tables

The trigger function is generic. You attach it to specific tables with a one-liner per table:

CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit.audit_trigger();

CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit.audit_trigger();

CREATE TRIGGER audit_invoices
    AFTER INSERT OR UPDATE OR DELETE ON invoices
    FOR EACH ROW EXECUTE FUNCTION audit.audit_trigger();

Notice it uses AFTER triggers, not BEFORE. An AFTER trigger fires only after the row is safely committed to the table. If the INSERT or UPDATE fails a constraint, the trigger never fires and the audit log never gets a row. This is correct: you only want to log changes that actually happened. A BEFORE trigger would log attempts that could still be rolled back by a later constraint violation.

Also notice FOR EACH ROW. The alternative is FOR EACH STATEMENT, which fires once per SQL statement regardless of how many rows it affects. That is more efficient for bulk operations, but it loses per-row old and new values. For a proper audit trail that answers “what was the old email address on this specific row,” you need row-level triggers. The performance cost is real (covered below) but the data is irreplaceable.

One practical improvement: exclude noisy columns

Not every column change is worth logging. A last_login_at column that updates every time a user authenticates will flood the audit log with noise. You can exclude specific columns by adding a check in the trigger function:

-- In the UPDATE branch, before INSERT
old_row := old_row - 'last_login_at' - 'updated_at';
new_row := new_row - 'last_login_at' - 'updated_at';

Or skip the entire INSERT if only excluded columns changed:

IF old_row = new_row THEN
    RETURN NEW;  -- Skip logging if only excluded columns changed
END IF;

Apply this selectively. Over-excluding defeats the purpose of the audit log. I exclude timestamps and counter columns that update on every page view, but I never exclude financial amounts, user emails, addresses, or status fields.

How to query the audit log

The audit log is only as valuable as your ability to query it. Here are the three queries every team needs.

Reconstruct what a row looked like at a point in time:

-- What did order 12345 look like at 2:00 PM on June 1?
SELECT new_values AS snapshot
FROM audit.audit_log
WHERE table_name = 'orders'
  AND row_id = '12345'
  AND changed_at <= '2026-06-01 14:00:00+00'
ORDER BY changed_at DESC
LIMIT 1;

If the result has no rows, the order did not exist yet. If it has a row with operation = 'DELETE', the order was deleted before that time. Otherwise, the snapshot is the last recorded state. This is a point-in-time query without pg_dump restore or snapshot isolation.

Show every change to a specific row, with diffs:

SELECT
    changed_at,
    operation,
    changed_by,
    old_values - new_values AS removed_fields,
    new_values - old_values AS added_fields
FROM audit.audit_log
WHERE table_name = 'users'
  AND row_id = '42'
ORDER BY changed_at DESC
LIMIT 20;

The - operator on JSONB objects returns the set of keys present in the left object but not the right. For the old minus new, that gives you fields that were removed or changed. For new minus old, that gives you fields that were added or changed. This is not a true diff (it does not show the old and new values side by side) but it is a single SQL query that runs in milliseconds and tells you exactly which fields changed in each operation.

Find all changes made by a specific user in a time window:

SELECT table_name, operation, row_id, changed_at
FROM audit.audit_log
WHERE changed_by = 'batch_script@system'
  AND changed_at BETWEEN '2026-06-01' AND '2026-06-02'
ORDER BY changed_at;

This is the query the CEO needed. It tells you every table and every row the batch script touched, in chronological order. With the row IDs, you can then look up the current state of each row and compare it to the last audit snapshot to see if the changes were correct.

Performance: the real cost of row-level triggers

The concern everyone raises is “will this slow down my writes?” The honest answer is yes, but the magnitude matters and you can measure it.

I tested on a c6g.large RDS instance with a table of 1 million rows. The table had 15 columns (text, numeric, timestamps). I ran 100,000 UPDATE statements, each modifying one row with SET email = '...', name = '...'.

Configuration	Duration (100k updates)	P50 latency
No trigger (baseline)	12.3s	0.12ms
With audit trigger, no JSONB exclusion	15.8s	0.16ms
With audit trigger, excluding timestamp columns	14.1s	0.14ms

The overhead is 15% to 28% per write, which sounds significant until you consider the absolute numbers: an extra 0.02ms to 0.04ms per row. For a web application with write-heavy workloads (100 writes/second), the trigger adds about 3ms to 4ms of overhead per second. That is invisible to users.

The bigger cost is storage. Each audit log row is significantly larger than the source row because it stores two full JSONB copies of the row data. On the test table, each source row was about 400 bytes. Each audit log row with old_values and new_values was about 1,200 bytes. Over a month on a table with 500,000 UPDATEs per day, the audit log grows by about 17 GB.

The fix is partitioning the audit log by time:

CREATE TABLE audit.audit_log_y2026m06 PARTITION OF audit.audit_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE audit.audit_log_y2026m07 PARTITION OF audit.audit_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- Continue monthly

With monthly partitions, you drop old partitions when they fall outside your retention window instead of running expensive DELETE statements that cause bloat in the audit log:

DROP TABLE audit.audit_log_y2025m06;

A cron job or pg_cron entry handles the partition creation and cleanup automatically.

The missing piece: setting app.current_user in your app

The most common failure I see when teams adopt this pattern is forgetting to set app.current_user in the application code. The trigger falls back to session_user (the database connection user), and every change logs as app_svc_production. You lose the ability to answer “who did this.”

The fix is a single middleware in your Node.js app:

import type { Pool } from 'pg';
import type { Request, Response, NextFunction } from 'express';

function auditUserMiddleware(pool: Pool) {
  return async (req: Request, res: Response, next: NextFunction) => {
    // Set the current_user for the duration of this request.
    // PG connection pools reuse connections, so this setting
    // must be applied per-request, not at pool creation time.
    const user = (req as any).user?.email ?? 'anonymous';
    const client = await pool.connect();
    try {
      await client.query("SET LOCAL app.current_user = $1", [user]);
      // Pass the client into the request for downstream use.
      (req as any).dbClient = client;
      next();
    } catch (err) {
      client.release();
      next(err);
    }
  };
}

// Usage
app.use(auditUserMiddleware(pool));

// In route handlers, use req.dbClient instead of the pool.
app.put('/api/users/:id', async (req, res) => {
  const client = (req as any).dbClient;
  await client.query('UPDATE users SET email = $1 WHERE id = $2', [
    req.body.email, req.params.id
  ]);
  // The trigger fires automatically, and the current_user
  // setting is read by the trigger function.
  res.json({ ok: true });
});

The SET LOCAL command scopes the setting to the current transaction. If your route handler does not use an explicit transaction, the setting still persists for the lifetime of the single-statement implicit transaction. When the middleware releases the connection back to the pool, the setting is gone. This prevents user A’s identity from leaking into user B’s requests.

When not to use this pattern

Trigger-based audit logging is not the right tool for every scenario.

If you need to replay past states to reconstruct current ones. The audit log records what changed, but querying “what was the state of this row two weeks ago” requires scanning all audit log rows since then and applying the deltas in order. For some teams, this is acceptable (the point-in-time query above handles it). For teams that need instant access to past snapshots, a temporal table extension (like pg_temporal or temporal tables with system-versioning) or a separate snapshot store is better.

If your write volume exceeds 500 writes/second on a single table. At that throughput, the storage cost of audit logging becomes significant and the trigger overhead starts showing up in p99 latency. For high-volume tables, consider logging at the statement level (log the SQL text, not per-row values) or switch to a change-data-capture tool like Debezium that streams changes from the WAL to a separate system. The CDC approach is more complex to set up but has near-zero overhead on the writer.

If you must audit SELECT queries. PostgreSQL triggers fire only on data modification statements. They cannot log reads. If your compliance requirements include “who viewed this customer record,” you need application-level logging or PostgreSQL’s built-in audit extension (pgaudit), which logs statements to the PostgreSQL log file.

The takeaway

A trigger-based audit log is one afternoon of work that saves weeks of forensic debugging later. The schema is one table, the trigger function is one file, and attaching it to a table is one SQL statement. The hardest part is remembering to set app.current_user in your application middleware, and that is a 10-line Express middleware.

Start with your financial tables. Nothing in the database needs an audit trail more urgently than tables where a wrong UPDATE means a wrong invoice, a wrong payout, or a compliance finding. Add orders, invoices, payouts, and any table with a status column that transitions through states. Add users and organizations for customer support. Add everything else as you discover you need it.

The CEO will not thank you for it until the next time someone changes a critical field at 3 AM and the answer is a single SQL query instead of a two-day investigation. But when that happens, the audit log will be the best investment you made all quarter.

---

A note from Yojji

Systems that need reliable audit trails (fintech, healthcare, enterprise SaaS) require more than just application-level logging. The database-level approach in this post eliminates whole classes of gaps where application code could accidentally skip logging a change. Yojji’s senior engineering teams build exactly this kind of production-grade data infrastructure for clients across regulated industries.

Yojji is an international custom software development company with offices in Europe, the US, and the UK. Founded in 2016, they work primarily in the JavaScript ecosystem (React, Node.js, TypeScript) and cloud platforms (AWS, Azure, Google Cloud), delivering full-cycle development for products where data integrity is non-negotiable.