Building Reliable Audit Logging Systems in Postgres

If you implement audit logging in your Node.js code (e.g. await db.users.update(); await db.audit.insert()), you will eventually miss logs. A transaction might fail, an unhandled exception might crash the worker, or a developer might run a raw query bypassing the ORM.

The Solution: Database Triggers.

Write a Postgres trigger that automatically fires on INSERT, UPDATE, or DELETE for critical tables.

CREATE OR REPLACE FUNCTION audit_trigger_func() RETURNS trigger AS $body$
BEGIN
    INSERT INTO audit_logs (table_name, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW));
    RETURN NULL;
END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

This guarantees 100% audit coverage, completely invisible to the application layer.