Why most audit log implementations fail after six months

Adding an audit log to a PostgreSQL and Node.js app is easy. Keeping it accurate, queryable and small enough that it does not double your database size is the hard part. Teams usually start with a few console.log calls or a generic logs table, then discover six months later that:

• Half of the writes bypass the logging code (background jobs, migrations, admin scripts, psql sessions).
• Nobody can answer the question “who changed this invoice amount on March 3rd?” without a full table scan.
• The audit table is now 60% of the database and vacuum is struggling.

This guide shows the two approaches that actually work in production, when to pick each one, and the exact schema, triggers, indexes and retention strategy we use at Box Software. Everything below targets PostgreSQL 16 to 18 and Node.js 22/24 LTS.

The two approaches: application-level vs database triggers

There are only two real places to capture a change: in your Node.js code before or after it hits the database, or inside PostgreSQL itself with a trigger.

Criteria	Application-level (Node.js middleware)	PostgreSQL triggers
Coverage	Only what goes through your code path	Everything, including psql, migrations, cron jobs
Business context (intent, endpoint, reason)	Excellent (“user cancelled subscription”)	Poor by default, needs session variables
Atomicity with the change	Only if you write inside the same transaction	Guaranteed by design
Performance cost	One extra INSERT per request (batchable)	One INSERT per affected row (watch bulk updates)
Maintenance	Lives with your codebase, easy to test	Lives in migrations, needs SQL skills
Compliance value (SOC 2, ISO 27001, HIPAA)	Medium, trusted code required	High, can be made append-only

Our recommendation: use both, but for different purposes. Triggers capture the data truth (which row changed, from what to what). Node.js captures the business truth (which endpoint, which actor, which intent). The trick that makes them work together is passing the actor identity from Node into the PostgreSQL session, which we cover below.

What about pgAudit?

The pgAudit extension is frequently recommended, and it is genuinely useful for DBA-level session and object auditing (who ran which statement). But it writes to the PostgreSQL server log file, not to a table. You cannot easily build a “History” tab in your product UI on top of a log file, and on managed services you may not control the extension at all. For product-facing audit trails, a dedicated table is the right tool.

The audit table schema (JSONB diffs, partitioned)

Start with a dedicated schema so the audit data is never accidentally mixed with business tables, and so you can grant permissions separately.

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.record_change (
  id             bigint GENERATED ALWAYS AS IDENTITY,
  changed_at     timestamptz NOT NULL DEFAULT clock_timestamp(),
  table_schema   text        NOT NULL,
  table_name     text        NOT NULL,
  record_id      text        NOT NULL,
  operation      text        NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE')),
  actor_id       text,           -- your app user id
  actor_type     text,           -- 'user' | 'service' | 'system'
  request_id     text,           -- correlate with your HTTP logs
  client_ip      inet,
  db_user        text NOT NULL DEFAULT current_user,
  txid           bigint NOT NULL DEFAULT txid_current(),
  old_data       jsonb,
  new_data       jsonb,
  diff           jsonb,          -- only the changed keys
  PRIMARY KEY (id, changed_at)
) PARTITION BY RANGE (changed_at);

CREATE TABLE audit.record_change_2026_09
  PARTITION OF audit.record_change
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Key design decisions:

1. record_id is text, not uuid or bigint. One table serves every entity, whatever the primary key type.
2. Store old_data, new_data and diff. The diff is what 95% of your queries read; the full rows are the safety net when someone asks for a point-in-time reconstruction. If storage is critical, keep diff plus old_data only for DELETE.
3. Partition by month from day one. Retention then becomes DROP TABLE, which is instant, instead of a DELETE that bloats the table and hammers autovacuum.
4. txid lets you group every row touched by a single transaction, which is how you reconstruct “one user action” from multiple table changes.

A reusable JSONB diff function

CREATE OR REPLACE FUNCTION audit.jsonb_diff(old_row jsonb, new_row jsonb)
RETURNS jsonb LANGUAGE sql IMMUTABLE AS $$
  SELECT COALESCE(
    jsonb_object_agg(k.key, jsonb_build_object('old', old_row -> k.key, 'new', new_row -> k.key)),
    '{}'::jsonb
  )
  FROM (
    SELECT key FROM jsonb_object_keys(old_row) AS key
    UNION
    SELECT key FROM jsonb_object_keys(new_row) AS key
  ) AS k
  WHERE old_row -> k.key IS DISTINCT FROM new_row -> k.key;
$$;

Result for a price change looks like this, which is directly renderable in a UI:

{"price": {"old": 4900, "new": 5900}, "updated_at": {"old": "...", "new": "..."}}

Approach 1: PostgreSQL triggers that know your app user

The classic objection to triggers is “the database only knows the connection user, not my application user”. The answer is set_config with transaction scope. Node sets a session variable at the start of the transaction, the trigger reads it.

The generic trigger function

CREATE OR REPLACE FUNCTION audit.log_change()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_old jsonb;
  v_new jsonb;
  v_pk  text;
  v_excluded text[] := COALESCE(TG_ARGV, ARRAY[]::text[]);
BEGIN
  IF TG_OP <> 'INSERT' THEN v_old := to_jsonb(OLD) - v_excluded; END IF;
  IF TG_OP <> 'DELETE' THEN v_new := to_jsonb(NEW) - v_excluded; END IF;

  v_pk := COALESCE(v_new ->> 'id', v_old ->> 'id');

  -- Skip no-op updates (common with ORMs that resend identical values)
  IF TG_OP = 'UPDATE' AND v_old IS NOT DISTINCT FROM v_new THEN
    RETURN NULL;
  END IF;

  INSERT INTO audit.record_change (
    table_schema, table_name, record_id, operation,
    actor_id, actor_type, request_id, client_ip,
    old_data, new_data, diff
  ) VALUES (
    TG_TABLE_SCHEMA, TG_TABLE_NAME, v_pk, TG_OP,
    NULLIF(current_setting('app.actor_id', true), ''),
    COALESCE(NULLIF(current_setting('app.actor_type', true), ''), 'system'),
    NULLIF(current_setting('app.request_id', true), ''),
    NULLIF(current_setting('app.client_ip', true), '')::inet,
    v_old, v_new,
    CASE WHEN TG_OP = 'UPDATE' THEN audit.jsonb_diff(v_old, v_new) END
  );

  RETURN NULL; -- AFTER trigger
END;
$$;

Attaching it to a table

CREATE TRIGGER trg_audit_invoices
AFTER INSERT OR UPDATE OR DELETE ON public.invoices
FOR EACH ROW EXECUTE FUNCTION audit.log_change('search_vector', 'updated_at');

The trigger arguments are columns to exclude, which is how you keep password_hash, tsvector columns or noisy timestamps out of the log. Never log secrets or personal data you do not need, because the audit table typically has the longest retention in your system.

Passing the actor from Node.js

Use AsyncLocalStorage to carry the request context, then set it on the connection inside every transaction. Transaction-scoped (true as the third argument) means it resets automatically and cannot leak between pooled connections.

// db.js
import { AsyncLocalStorage } from 'node:async_hooks';
import pg from 'pg';

export const ctx = new AsyncLocalStorage();
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

export async function withAudit(fn) {
  const client = await pool.connect();
  const c = ctx.getStore() ?? {};
  try {
    await client.query('BEGIN');
    await client.query(
      `SELECT set_config('app.actor_id',   $1, true),
              set_config('app.actor_type', $2, true),
              set_config('app.request_id', $3, true),
              set_config('app.client_ip',  $4, true)`,
      [c.actorId ?? '', c.actorType ?? 'system', c.requestId ?? '', c.clientIp ?? '']
    );
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// audit-context.middleware.js (Express)
import { randomUUID } from 'node:crypto';
import { ctx } from './db.js';

export function auditContext(req, res, next) {
  const store = {
    actorId: req.user?.id ?? null,
    actorType: req.user ? 'user' : (req.apiKey ? 'service' : 'system'),
    requestId: req.headers['x-request-id'] ?? randomUUID(),
    clientIp: req.ip
  };
  res.setHeader('x-request-id', store.requestId);
  ctx.run(store, next);
}

With Prisma you can achieve the same with $transaction plus a raw set_config call as the first statement; with Knex, use knex.transaction and trx.raw(...). The rule is simple: the set_config call and the data change must be in the same transaction. (via https://repost.aws)

Approach 2: application-level audit events in Node.js

Triggers cannot know that a sequence of updates was “Refund issued after customer complaint #4821”. That is what the application-level log is for. Keep it in a separate table, with a stable event vocabulary.

CREATE TABLE audit.activity (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at  timestamptz NOT NULL DEFAULT now(),
  event        text NOT NULL,              -- 'invoice.refunded'
  actor_id     text,
  actor_type   text NOT NULL DEFAULT 'user',
  target_type  text,                       -- 'invoice'
  target_id    text,
  request_id   text,
  outcome      text NOT NULL DEFAULT 'success',  -- success | failure | denied
  metadata     jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX ON audit.activity (target_type, target_id, occurred_at DESC);
CREATE INDEX ON audit.activity (actor_id, occurred_at DESC);
CREATE INDEX ON audit.activity USING gin (metadata jsonb_path_ops);

// audit.service.js
export async function recordActivity(client, event, target, metadata = {}, outcome = 'success') {
  const c = ctx.getStore() ?? {};
  await client.query(
    `INSERT INTO audit.activity
       (event, actor_id, actor_type, target_type, target_id, request_id, outcome, metadata)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8)`,
    [event, c.actorId, c.actorType ?? 'system', target.type, String(target.id),
     c.requestId, outcome, metadata]
  );
}

// usage inside a transaction
await withAudit(async (client) => {
  await client.query('UPDATE invoices SET status = $1 WHERE id = $2', ['refunded', id]);
  await recordActivity(client, 'invoice.refunded', { type: 'invoice', id }, { reason, amount_cents });
});

Three rules for application-level logging

• Same transaction, always. An audit row written after the commit can be lost on crash; a row written in a separate transaction can exist for a change that was rolled back.
• Log failures and denials too. “User X was denied access to record Y” is often the most valuable line during an incident review.
• Use a closed event vocabulary (resource.action) and keep it in a TypeScript union or enum. Free text events become unqueryable within weeks.

Querying audit history without killing your database

The three queries you will run 99% of the time:

-- 1. Full history of one record (the 'History' tab)
SELECT changed_at, operation, actor_id, diff
FROM audit.record_change
WHERE table_name = 'invoices' AND record_id = '8f21...'
ORDER BY changed_at DESC
LIMIT 50;

-- 2. Everything one user did in a time window
SELECT changed_at, table_name, record_id, operation, diff
FROM audit.record_change
WHERE actor_id = '42'
  AND changed_at >= now() - interval '30 days'
ORDER BY changed_at DESC;

-- 3. Who changed a specific field
SELECT changed_at, actor_id, diff -> 'status'
FROM audit.record_change
WHERE table_name = 'invoices'
  AND diff ? 'status'
  AND changed_at >= '2026-09-01'
ORDER BY changed_at DESC;

Indexes that make them fast, per partition:

CREATE INDEX ON audit.record_change (table_name, record_id, changed_at DESC);
CREATE INDEX ON audit.record_change (actor_id, changed_at DESC) WHERE actor_id IS NOT NULL;
CREATE INDEX ON audit.record_change USING brin (changed_at) WITH (pages_per_range = 32);
CREATE INDEX ON audit.record_change USING gin (diff jsonb_path_ops);

Index	Serves	Cost
B-tree (table_name, record_id, changed_at)	Record history page	Medium, mandatory
BRIN (changed_at)	Time range scans and exports	Tiny (kilobytes)
GIN (diff jsonb_path_ops)	Field-level searches	Large, add only if needed

Because the table is partitioned by changed_at, any query with a date filter prunes partitions before touching an index. Always pass a time range from the API layer, even a generous one like 24 months.

Reconstructing a row at a point in time

SELECT new_data
FROM audit.record_change
WHERE table_name = 'invoices'
  AND record_id = '8f21...'
  AND changed_at <= '2026-06-01 00:00:00+00'
  AND operation <> 'DELETE'
ORDER BY changed_at DESC
LIMIT 1;

This is the main reason to keep new_data and not only diffs: replaying hundreds of diffs in application code is slow and error prone.

Retention: keeping the audit log from eating your database

An audit table grows linearly and never stops. Plan the exit before the first row is written.

1. Monthly partitions. Create the next two months ahead of time with a cron job, or use pg_partman if your host supports it.
2. Tiered retention. Hot (queryable in app): 3 to 12 months. Warm (archive): up to the legal requirement. Cold: object storage.
3. Detach, export, drop. Detaching a partition is instant and does not block writers:

ALTER TABLE audit.record_change DETACH PARTITION audit.record_change_2025_09 CONCURRENTLY;
-- export to Parquet/CSV in object storage, then:
DROP TABLE audit.record_change_2025_09;

Additional levers that reduce volume dramatically:

• Do not audit every table. Audit money, permissions, identity and contractual data. Skip caches, sessions, queues and derived tables.
• Exclude noisy columns such as updated_at, last_seen_at, counters and search vectors.
• Drop no-op updates in the trigger (already handled above). With ORMs this alone can cut 30% to 50% of rows.
• Store only the diff for UPDATE and the full row for INSERT and DELETE if storage becomes a real constraint.
• Set a lower fillfactor is unnecessary here: the table is append-only, so set autovacuum_vacuum_insert_scale_factor appropriately and let it be.

Making the audit log tamper-resistant

If the log exists for compliance, your application role must not be able to rewrite history.

REVOKE UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA audit FROM app_user;
GRANT USAGE ON SCHEMA audit TO app_user;
GRANT SELECT, INSERT ON audit.record_change, audit.activity TO app_user;

The trigger function is declared SECURITY DEFINER, so it inserts with the owner privileges even if the application role only has SELECT. For higher assurance, add a hash chain column where each row stores sha256(previous_hash || row_payload), and ship a nightly job that verifies the chain and pushes the last hash to an external system (object storage with object lock, or a separate logging account). Any silent deletion then becomes detectable.

Common pitfalls we see in code reviews

• Bulk updates. A statement touching 500k rows creates 500k audit rows inside one transaction. Either batch the operation, or temporarily disable the trigger for a controlled migration with ALTER TABLE ... DISABLE TRIGGER and log a single “migration” activity event instead.
• Session variables set outside the transaction. With connection pooling (PgBouncer in transaction mode especially), a session-scoped variable can leak to another request. Always use the transaction-local form.
• Logging in a separate connection. Rollbacks then leave phantom audit entries.
• No index on record_id. The history tab is fine at 10k rows and times out at 10M.
• Auditing JSONB blobs wholesale. A 200 KB settings column logged on every save will outgrow the business table within days.
• Using now() instead of clock_timestamp(). now() is identical for every statement in a transaction, which breaks the ordering of multiple changes to the same row.

Which approach should you choose?

• Regulated data, multiple write paths, external tools touching the database: triggers first, application events as enrichment.
• Single Node.js service, product-facing activity feed, no compliance requirement yet: application-level events first, add triggers on sensitive tables later.
• Existing legacy schema you cannot refactor: triggers, because you can add them table by table without touching a line of application code.

Both fit in the same database, share the same request_id, and can be merged in a single API endpoint that returns a unified timeline to the user interface.

FAQ

Do PostgreSQL audit triggers slow down writes?

Expect roughly 10% to 25% overhead on single-row writes on audited tables, mostly the extra INSERT and the JSONB serialization. It is negligible for typical CRUD traffic and noticeable on bulk operations. Only audit the tables that need it, and exclude large or noisy columns.

Can I use pgAudit instead of an audit table?

pgAudit is excellent for infrastructure auditing (which statements ran, by which database role) and writes to the server log. It does not give you a queryable per-record history for your product. Many teams run both: pgAudit for the DBA layer, a dedicated audit table for the application layer.

How do I pass the logged-in user to a PostgreSQL trigger from Node.js?

Call SELECT set_config('app.actor_id', $1, true) as the first statement of the transaction, then read it in the trigger with current_setting('app.actor_id', true). The third argument true makes it transaction-scoped, which is safe with connection pooling.

Should I store the diff or the full row?

Store both when you can. The diff is what you display and index; the full row is what lets you rebuild the record at any point in time without replaying the entire history. If storage is tight, keep the diff for UPDATE and full snapshots for INSERT and DELETE.

How long should audit logs be kept?

It depends on your obligations: commonly 12 months for general activity, 6 to 7 years for financial records, and specific durations for healthcare or public sector data. Keep the recent window in PostgreSQL partitions and archive older partitions to object storage with immutability enabled. There’s a good explainer over at runharbor.com.

Does Prisma or Sequelize handle audit logging automatically?

Prisma middleware and Sequelize hooks can log changes, but they only see what passes through the ORM. Raw queries, migrations and external tools bypass them entirely. Use ORM hooks for business events, and database triggers when completeness matters.

Should the audit log live in a separate database?

Not at first. Keeping it in the same PostgreSQL instance gives you transactional guarantees, which is the single most valuable property of an audit trail. Move it out only when volume or isolation requirements force you to, and replicate it there asynchronously from the partitioned table.

Need help implementing this?

At Box Software we design and ship Node.js and PostgreSQL back ends with auditability, retention and compliance built in from the first migration. If you are adding an audit trail to an existing product, or preparing for a SOC 2 or ISO 27001 assessment, get in touch and we will review your schema with you.