Audit Logs With Triggers or Application Code in Go

When something goes wrong with data, the first questions are who changed it, when and from what to what. An audit log answers them, and where you put the logging decides what it can and cannot see. The two real options are a database trigger that records every row change and application code that writes a log entry next to each change. They are not interchangeable. This article builds both on PostgreSQL 18.6 from Go 1.25.6, makes a change that only one of them sees, and measures what the trigger costs.

It belongs to the database cornerstone. It builds on the transaction handling in unit of work and offers an alternative to soft delete for keeping history.

Two ways to write the log

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func must(db sqlExec, q string, args ...any) {
	if _, err := db.ExecContext(context.Background(), q, args...); err != nil {
		log.Fatalf("%s: %v", q, err)
	}
}

type sqlExec interface {
	ExecContext(context.Context, string, ...any) (sql.Result, error)
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	for _, q := range []string{
		"drop table if exists accounts624, audit624, audit_app624 cascade",
		"create table accounts624 (id int primary key, email text not null, plan text not null)",
		"create table audit624 (id bigserial primary key, at timestamptz not null default now(), actor text, op text not null, row_id int not null, old jsonb, new jsonb)",
		"create table audit_app624 (id bigserial primary key, at timestamptz not null default now(), actor text, op text not null, row_id int not null, old jsonb, new jsonb)",
		`create or replace function audit624_fn() returns trigger language plpgsql as $$
		 begin
		   if tg_op = 'DELETE' then
		     insert into audit624 (actor, op, row_id, old) values (current_setting('app.actor', true), tg_op, old.id, to_jsonb(old));
		     return old;
		   elsif tg_op = 'INSERT' then
		     insert into audit624 (actor, op, row_id, new) values (current_setting('app.actor', true), tg_op, new.id, to_jsonb(new));
		     return new;
		   end if;
		   insert into audit624 (actor, op, row_id, old, new) values (current_setting('app.actor', true), tg_op, new.id, to_jsonb(old), to_jsonb(new));
		   return new;
		 end $$`,
		"create trigger accounts624_audit after insert or update or delete on accounts624 for each row execute function audit624_fn()",
	} {
		must(db, q)
	}

	// Application code audit: the service writes the audit row itself, in the same transaction.
	update := func(tx *sql.Tx, actor string, id int, plan string) {
		var old string
		_ = tx.QueryRowContext(ctx, "select plan from accounts624 where id = $1", id).Scan(&old)
		must(tx, "update accounts624 set plan = $1 where id = $2", plan, id)
		must(tx, "insert into audit_app624 (actor, op, row_id, old, new) values ($1, 'UPDATE', $2, jsonb_build_object('plan', $3::text), jsonb_build_object('plan', $4::text))", actor, id, old, plan)
	}

	must(db, "insert into accounts624 values (1, '[email protected]', 'free'), (2, '[email protected]', 'free')")

	// 1. Both kinds record an update that goes through the service.
	tx, _ := db.BeginTx(ctx, nil)
	must(tx, "select set_config('app.actor', 'user:7', true)")
	update(tx, "user:7", 1, "pro")
	_ = tx.Commit()
	fmt.Println("1. update through the service: trigger rows", scalar(db, "select count(*) from audit624 where op = 'UPDATE'"), "application rows", scalar(db, "select count(*) from audit_app624"))

	// 2. A fix applied in psql, a migration, another service: only the trigger sees it.
	must(db, "update accounts624 set plan = 'team' where id = 2")
	must(db, "delete from accounts624 where id = 2")
	fmt.Println("2. an update and a delete from a SQL console: trigger rows", scalar(db, "select count(*) from audit624"), "application rows", scalar(db, "select count(*) from audit_app624"))

	var who, change string
	if err := db.QueryRow("select coalesce(nullif(actor, ''), 'nobody'), (old->>'plan') || ' -> ' || (new->>'plan') from audit624 where row_id = 2 and op = 'UPDATE'").Scan(&who, &change); err != nil {
		log.Fatal(err)
	}
	fmt.Printf("   the console change is recorded, with actor %q and change %s\n", who, change)
	if err := db.QueryRow("select coalesce(nullif(actor, ''), 'nobody'), (old->>'plan') || ' -> ' || (new->>'plan') from audit624 where row_id = 1 and op = 'UPDATE'").Scan(&who, &change); err != nil {
		log.Fatal(err)
	}
	fmt.Printf("   the service change carries actor %q and change %s\n", who, change)

	// 3. Cost of the trigger on 20,000 updates in one transaction.
	must(db, "insert into accounts624 select i, 'u' || i, 'free' from generate_series(100, 20099) i")
	run := func(label string) {
		start := time.Now()
		must(db, "update accounts624 set plan = 'pro' where id >= 100")
		must(db, "update accounts624 set plan = 'free' where id >= 100")
		fmt.Printf("   %-30s %v for 40,000 row updates\n", label, time.Since(start).Round(time.Millisecond))
	}
	fmt.Println("3. cost of the trigger")
	must(db, "alter table accounts624 disable trigger user")
	run("trigger disabled")
	must(db, "alter table accounts624 enable trigger user")
	run("row level audit trigger")
	fmt.Println("   audit rows written:", scalar(db, "select count(*) from audit624"))
}

func scalar(db *sql.DB, q string) (n int) {
	_ = db.QueryRow(q).Scan(&n)
	return
}

1. update through the service: trigger rows 1 application rows 1
2. an update and a delete from a SQL console: trigger rows 5 application rows 1
   the console change is recorded, with actor "nobody" and change free -> team
   the service change carries actor "user:7" and change free -> pro
3. cost of the trigger
   trigger disabled               118ms for 40,000 row updates
   row level audit trigger        505ms for 40,000 row updates
   audit rows written: 60005

The trigger. audit624_fn is a PL/pgSQL function attached after insert or update or delete ... for each row. For each change it writes a row into audit624 with the time, the operation, the row id, and the old and new row as JSON (to_jsonb(old), to_jsonb(new)). It also records current_setting('app.actor', true), a custom setting the application can set. The trigger lives in the database, so it fires for everything that touches the table.

The application code. The update helper in the program reads the old plan, performs the update, and inserts a row into audit_app624 with the actor it was given, all in one transaction. It records exactly the fields the author chose, with the context only the application has. It fires only when the application calls it.

What each one sees

1. An update through the service. Both logs got one row. The service called set_config('app.actor', 'user:7', true) inside its transaction, so the trigger knew the actor, and the application log had it explicitly.
2. An update and a delete from a SQL console. The program ran update accounts624 set plan = 'team' and a delete as plain statements, the way an engineer fixing data in psql, a migration or another service would. The trigger log grew to 5 rows: the two initial inserts, the service update, and the console update and delete. The application log stayed at 1. A log written by application code has a hole shaped like every path that does not use the application.

That is the main argument for triggers, and the strongest one: when an auditor asks for every change to a table, the answer must not depend on which tool made it.

The trigger log has its own gap. The console change was recorded as free -> team, with the actor nobody: the database knows the role that connected, but not the person at the keyboard, and the app.actor setting was never set outside the service. Pass the actor through set_config(..., true) in every transaction that writes, in a middleware or in the unit of work, and make the trigger fall back to current_user for console sessions so a person's database login is recorded. For automation, give each service its own database role so "nobody" never appears.

What it costs

The third block measures the price. A table of 20,000 accounts is updated twice, 40,000 row updates, first with the trigger disabled and then enabled:

The trigger made the writes about 4.3 times slower, because each updated row also inserts an audit row with two JSON documents. In absolute terms it is 10 microseconds per audit row. For a table that sees a few hundred writes a second that is invisible. For a table that takes tens of thousands of updates a second it is a real load, and the audit table grows quickly: this run wrote 60,005 audit rows for 20,000 inserts and 40,000 updates.

Ways to bring the cost down:

• Audit only what matters. Attach the trigger to the tables that hold money, permissions and personal data, not to a sessions table.
• Log changed columns only. Compare old and new and skip the row if nothing relevant changed, or store only the changed keys.
• Use a statement level trigger with transition tables for bulk changes, which logs a whole batch in one insert and not one per row.
• Partition the audit table by month and move old partitions to cheap storage or drop them on schedule; see sharding and partitioning basics.
• Keep the audit table append only. Give the application role insert but not update or delete on it, so a compromised service cannot rewrite history.

What a trigger cannot tell you

A trigger sees row changes. It does not know the business reason. "Plan changed from free to pro" is a row change; "customer upgraded after a sales call, approved by Dana" is an event, and only the application knows it. If the log has to answer why, store the reason where it is known: write an event row from application code, and let the trigger log the row changes underneath it.

Application level logs are also the right place for events that are not row changes at all: a login, an export, a failed permission check, a read of sensitive data. A trigger cannot see a select.

Keeping the log useful

An audit log nobody can read is a cost with no benefit, so decide three things at the start. Retention: how long the business and the law need it, and who deletes older rows and how. Access: who may read it, since it holds old copies of personal data and therefore falls under the same erasure and access rules as the live table; storing whole rows as JSON means a deleted customer's email survives in the log, so decide whether the log is exempt or must be scrubbed. Queries: the questions people actually ask are "what happened to this row" and "what did this person change", so index (row_id, at) and (actor, at). A log you can search in a second gets used; one that needs an hour of scanning does not.

Choosing

Most systems that take audit seriously end up with both: triggers for completeness and the application's own events for meaning, linked by a shared transaction id (txid_current() stored in both) so a business event can be joined to the row changes it caused. If you adopt a trigger, write it as a migration, test it with a console style change like the one above, and add a test that every audited table has its trigger. The mechanism is documented in the PostgreSQL manual under trigger functions, which lists the tg_op and transition table variables used here, and the settings function in configuration settings functions. For SQL injection and other ways a console gets misused, see SQL injection in Go.