Append-Only Audit Logs in Postgres with Triggers and Node.js

Append-only audit logs in Postgres with triggers give you a record of every change to customer data: who made the change, when, and what the row looked like before and after. The database writes these records itself. You need one audit table, one trigger function and one line in your Node.js API that tells Postgres who is acting.

Here is the situation this solves. A customer asks why their billing email changed, or an auditor asks who edited an account last spring. You should be able to answer with one query. Most apps can't. They have an updated_at column and some application logs that were deleted weeks ago.

Why put the audit trail in the database instead of the app?

Application-level logging only sees writes that go through your application code. Triggers see every write, whatever path it took.

In a real system, data changes from more than one place. Admin scripts, migrations, a second service, a background job, a developer fixing something in psql at 11pm. Any of these can skip your logger. A trigger fires on every INSERT, UPDATE and DELETE no matter who sent it.

The catch is that Postgres doesn't know your app's users. It only knows the database role, and that role is the same for every request. The trick below passes the acting user's ID into the transaction so the trigger can read it.

What does it cost when you can't answer "who changed this"?

When a dispute comes up, you rebuild events from guesses. When an account is compromised, you can't tell which records the attacker touched, so you have to treat all of them as suspect. Enterprise security questionnaires ask for this directly, and "we log some things in the app" slows the deal down.

The worst part is timing. An audit trail only covers changes made after you turn it on. Every month you wait is history you can never get back.

Who can skip this?

If your app is an internal tool with a few trusted users and no personal or financial data, an updated_by column is probably enough. Also, triggers only record writes. If you need to know who viewed a record, use the pgaudit extension or access logs in your app instead.

Step 1: an audit table nobody can edit

CREATE SCHEMA audit;

CREATE TABLE audit.log (
  id          bigserial PRIMARY KEY,
  table_name  text        NOT NULL,
  record_id   text        NOT NULL,
  action      text        NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')),
  actor_id    text,
  changed_at  timestamptz NOT NULL DEFAULT now(),
  old_data    jsonb,
  new_data    jsonb
);

CREATE INDEX ON audit.log (table_name, record_id, changed_at DESC);

-- Append-only: block edits, even from the table owner
CREATE FUNCTION audit.block_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit.log is append-only';
END $$;

CREATE TRIGGER no_edit BEFORE UPDATE OR DELETE ON audit.log
  FOR EACH ROW EXECUTE FUNCTION audit.block_change();
CREATE TRIGGER no_truncate BEFORE TRUNCATE ON audit.log
  FOR EACH STATEMENT EXECUTE FUNCTION audit.block_change();

REVOKE ALL ON SCHEMA audit FROM PUBLIC;
REVOKE ALL ON audit.log FROM PUBLIC;

Your application role gets no write access to audit.log at all. Only the trigger function writes to it.

Step 2: one trigger function for every table

CREATE FUNCTION audit.capture() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, audit AS $$
DECLARE
  actor text := nullif(current_setting('app.user_id', true), '');
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO audit.log (table_name, record_id, action, actor_id, old_data)
    VALUES (TG_TABLE_NAME, OLD.id::text, TG_OP, actor,
            to_jsonb(OLD) - 'password_hash');
  ELSE
    INSERT INTO audit.log (table_name, record_id, action, actor_id, old_data, new_data)
    VALUES (TG_TABLE_NAME, NEW.id::text, TG_OP, actor,
            CASE WHEN TG_OP = 'UPDATE' THEN to_jsonb(OLD) - 'password_hash' END,
            to_jsonb(NEW) - 'password_hash');
  END IF;
  RETURN NULL;
END $$;

CREATE TRIGGER customers_audit
  AFTER INSERT OR UPDATE OR DELETE ON customers
  FOR EACH ROW EXECUTE FUNCTION audit.capture();

Three details matter here. SECURITY DEFINER lets the function write to a table the app role can't touch. The pinned search_path stops anyone from hijacking it with a lookalike table. The - 'password_hash' strips secrets so your audit log doesn't turn into a second copy of your credentials. Add any other sensitive columns to that list.

Step 3: how does Node.js tell Postgres who is acting?

You set a transaction-scoped setting with set_config(..., true). The true means it disappears when the transaction ends. With a connection pool this is not optional: a plain SET would stay on the connection, and the next request would get logged as the wrong user.

async function withActor(pool, userId, fn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query("SELECT set_config('app.user_id', $1, true)", [String(userId)]);
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

app.patch('/customers/:id', requireAuth, async (req, res) => {
  const { rows } = await withActor(pool, req.user.id, (db) =>
    db.query('UPDATE customers SET email = $1 WHERE id = $2 RETURNING *',
      [req.body.email, req.params.id])
  );
  res.json(rows[0]);
});

We use set_config instead of SET LOCAL because SET can't take a bound parameter, and building it with string concatenation is an injection risk. Because the setting only lives for one transaction, this also works behind PgBouncer in transaction pooling mode.

Decide what happens when actor_id comes through as NULL. If the write came from a migration or a cron job, NULL may be fine. If it came from the API, it means a route skipped withActor. Set up an alert on API writes with a null actor, or make the trigger raise an error for your app role.

How do you answer "who changed this record"?

This query lists every change to one customer and shows only the fields that changed:

SELECT
  l.changed_at,
  l.actor_id,
  l.action,
  (SELECT jsonb_object_agg(n.key,
            jsonb_build_object('from', l.old_data -> n.key, 'to', n.value))
     FROM jsonb_each(l.new_data) n
    WHERE l.old_data -> n.key IS DISTINCT FROM n.value) AS changes
FROM audit.log l
WHERE l.table_name = 'customers' AND l.record_id = $1
ORDER BY l.changed_at DESC, l.id DESC;

A result row looks like 2026-09-02 14:11 | user_482 | UPDATE | {"email": {"from": "[e-mail]", "to": "[e-mail]"}}. Join actor_id to your users table and you have something support staff and auditors can read without help.

Where does this stop being enough?

This setup is tamper-resistant, not tamper-proof. A superuser, or the role that owns the table, can still disable triggers. So follow three rules. Never let your app connect as the owner role. Copy audit rows somewhere the database can't overwrite, such as write-once object storage or a separate logging account. And partition audit.log by month once it gets large, so old months can be archived instead of deleted.

Audit gaps rarely show up alone. They tend to sit next to missing authorization checks and over-privileged database roles, especially in apps that were built quickly. Our guide to vibe coding security covers the other holes we see most often.

If you want a second pair of eyes on your schema and API, ask Geminate Solutions for a free review. We sign an NDA before we talk and reply within 24 hours.