Audit record changes

[gmhafiz]

Model methods like Insert(), Update(), and Delete() can be extended to save record changes that includes certain metadata like who is making the change, previous state, and current state. Similar to https://github.com/owen-it/laravel-auditing package for Laravel. From my search there is no single active library that can do this for Go ecosystem.

I have a very early prototype that works with database/sql which uses hooks to capture these information, plus user's IP, user agent etc.. User ID is captured simply with ctx = context.WithValue(ctx, middleware.UserID, {{give user id here}}) in an Auth middleware. It works well with MySQL. Resulting record to an audit table looks like this:

id	actor_id	table_row_id	table_name	action	old_values	new_values	http_method	url	ip_address	user_agent	created_at
42	2	15	users	update	{"name":"test name","id":"42"}	{"name":"changed name","id":"42"}	PUT	/api/v1/user/42	localhost:8080	PostmanRuntime/7.28.4	2021-09-15 02:10:02

However, it cannot capture LastInsertID() for postgres because of driver limitation.

I realize that we can use ctx to save these audit details. We can retrieve

• actor_id - taken from Auth middleware
• table_row_id - taken from model.ID after QueryRowContext() is performed.
• table_name - already available
• action - depends on whether you are doing Insert(), Update(), etc
• old_values - Relevant for Update() and Delete() - extra SQL query need to be performed to query the record before update/delete is performed
• new_values - Relevant for Insert(), Update()
• http_method, url, ip _address, user_agent - taken from a middleware
• created_at - A simple time.Now()

Performance will be degraded because there are extra sql queries being made. So, audit must be optional.

Implementation seems viable. We only need to figure out when is the best time to perform those additional sql queries.

[aarondl]

Your prototype is neat. I'm trying to understand the last insert id problem with postgres. You want to see the RETURNING pieces in the audit log is that it? Is that really necessary?

Or is it that LastInsertID isn't being correctly correlated to a query in mysql driver because it's a separate query?

[gmhafiz]

Your prototype is neat. I'm trying to understand the last insert id problem with postgres. You want to see the RETURNING pieces in the audit log is that it? Is that really necessary?

That audit prototype cannot get the ID in an INSERT operation unless the query being passed into the middleware has a RETURNING clause. The library also do not have an understanding of a table unlike sqlboiler. So in an INSERT operation, that prototype won't know the new record ID for table_row_id to be inserted into audits table making its usefulness limited for postgres.

I realise that it is not an issue for sqlboiler since it adds queryReturning and retrieves a model's primary key value.

Thus, I have a fork to sqlboiler that adds an audit functionality similar to the audit prototype. Requirements are

1. To add to sqlboiler.toml the key audit-enabled = true
2. Use the middleware AuditMW, similar to the one I have in the audit library.
3. Set UserID key to context

It has several issues though, like upsert isn't being generated, it doesn't figure out a model's primary key etc. However, it works well for create, insert, and delete for both mysql and postgres. Overall only one additional sql query is added - inside Update() template because it needs to know the previous set of values for old_values column.

If you think adding an optional audit functionality to sqlboiler is worth it, I will try and fix remaining issues with the fork.