I think the first main question you need to ask yourself is: Are you auditing changes to rows in tables, or are you auditing actions performed by users?

The distinction is this: When auditing users, my experience is that you are better off thinking in terms of logical operations (often at the API / controller layer) that users perform, the parameters that they pass in, and include information like ip address, request id, and authorization information. If you are auditing rows on tables, then yeah you’ll maybe want something Repo or trigger based.

In my experience is that most of the time you want an audit log you’re really trying to audit users, isn’t really well handled by the trigger or Repo based approaches. A single user operation will often touch multiple rows in multiple tables, and as your application develops you may need to use things like insert_all or update_all which are more difficult to track from a Repo perspective.

I don’t want to speak for José and his team, but in Dashbit’s Bytepack, they included an AuditLog struct and plugs, which you may find useful:

https://github.com/dashbitco/bytepack_archive/blob/main/apps/bytepack/lib/bytepack/audit_log.ex

and, Hex.pm also has an implementation that is similar but different:

https://github.com/hexpm/hexpm/blob/main/lib/hexpm/accounts/audit_log.ex

We’ve been using an adapted version of Bytepack’s AuditLog for I think about a year now, and it’s been working great for us. While we don’t query the data unless needed, Hex.pm’s version also has indexes on the JSONB column to speed that up.

GitHub - bitcrowd/carbonite: Audit trails for Elixir/PostgreSQL based on triggers · GitHub - came across this sometime ago.