Last updated by and last updated at best practice

App Building
, ,
1

Hi all,

Wondering what is the best practice / most common approach for capturing:

  1. last_updated_at date/time

  2. last_updated_by username / email
    in the relevant tables in the Retool DB.

  3. For last_updated_at I believe I can create a field e.g. last_update_date_time and use a default value of now() for that field

  4. For last_updated_by Ive not found any solutions from my searches here.

Sure this is something others have come across/needed?

Thanks!
Nick

2

I have done both using triggers and functions for audit trails but for last_updated_by you can capture the current _user if needed and for last_updated_at, now should suffice... my 2 cents....

3

Thanks @ScottR

Are you able to share a bit more information on your approach? Did you create triggers at the PostgreSQL level? If so, did you create from within an App?

Re capturing current_user - do you do that explicitly as a separate process? For the app I'm initially looking at, I am using Bulk update via a primary key for updates (for inline table updates) and using the {{table_name.changesetArray}} which has the PK and changes, but not current_user.

Do I need a transformer or something similar to append the current_user to the array?

Thanks!
Nick

4

Yes that would be the way to do it....

I created triggers and functions directly in the database...
That being said I have also used the current_user from within the app....

I am sure that if you share some more information such as screenshots and your code someone here could certainly help you find a solution.

5

You'd need to append the current user to each object in your array, I think - so a map function would be good here and saves writing another transformer

ie
{{ table_name.changesetArray.map(x => ({...x, last_updated_by: current_user.email}) ) }}

6

Hi @ScottR and @dcartlidge

Apologies for not sharing a SS, but this is something we will likely implement across all apps (e.g. CRM type tables, various pricing/rates etc.)

I did a quick test of @dcartlidge 's approach and that's exactly what I need for last_updated_by and is quite elegant. Thanks! :medal_sports:

I'll look into the PostgreSQL triggers for last_edited_at

Regards,
NIck