[sususu]

I really like this approach, however I have a use case where the application user id that made the operation must be saved in the audit table, unfortunately, I cannot see how to do that with a pure SQL solution.

Has anyone done something similar with SQL only?

[netcraft]

we do that by setting a local variable before the query and then reading that in the triggers:

```

  SET LOCAL foo.who_id = 'some-uuid';
  UPDATE table
  SET ...

```

```

  -- function to make getting the setting easier
  DROP FUNCTION IF EXISTS get_who_id (text);
  CREATE OR REPLACE FUNCTION get_who_id (default_value text DEFAULT null::text) RETURNS text AS $get_who_id$
 DECLARE
  who_id text;
 BEGIN
  BEGIN
   who_id := current_setting('foo.who_id');
  EXCEPTION
   WHEN SQLSTATE '42704' THEN
    RETURN default_value;
  END;
  IF (length(who_id) = 0) THEN
   RETURN default_value;
  END IF;
  return who_id;
 END
  $get_who_id$ LANGUAGE plpgsql VOLATILE;

```

```

  CREATE OR REPLACE FUNCTION some_table_audit () RETURNS   TRIGGER AS $some_table_audit$
 DECLARE
  who_id text;
 BEGIN

  who_id := get_who_id(null::text);
  ...

```

Identifiers changed, but hopefully will give you the idea.

[tda]

I've experiment with a very similar solution and it felt a bit dirty but so far it seems to be working just fine. I have made an integration for auditing with sqlmodel which I intend to share, but it is kind of rough and I was a bit stuck trying to clean it up. The idea is that you add e.g. a HeroHistory model derived from HeroBase and a HistoryMixin that creates the triggers and relationships to have an Audit log of the Hero table.

If anyone is interested give me a shout out

[sususu]

That's really interesting and gave me some (hopefully) good ideas.

Thank you very much!

[timwis]

PostgREST has an interesting approach to authentication that might give you some ideas:

https://postgrest.org/en/stable/auth.html

Supabase is built on top of PostgREST, but I can’t say for sure it uses the same means of authentication (I think it has its own Auth layer).

[oliverrice]

Supabase passes all the JWT info to postgrest so it is still available in transaction local config

[oliverrice]

if `user_id` is in the table you're auditing you could do

``` alter table audit.record_version add column user_id bigint default (coalesce(record, old_record) ->> 'user_id'); ```

or if you meant the SQL `current_user` you could extend the trigger to track it.

but if the user_id is only available in application space (not in the db) it will not be possible using straight SQL