Audit log

yhrel commented

2026-10-22 15:49:35 +02:00

Collaborator

This new feature is meant to keep track of who changed what, when. It's basically a change history, for the purpose of auditing said changes.

Scope:

Allow admins to see changes to the data
A full view, filterable by period, author, model
The last changes when editing an entry (such as a member), only for admins

Implementation details:

Add a model for audit log (datetime, model name, row id, author id, before [JSONB of fields that changed], after [JSONB of fields that changed]). Hard deleted, cascade on purge
Add database triggers to create audit logs. It doesn't do anything if the diff is empty (updatedAt ignored). Secret fields are replaced with old/new placeholders (password, totp_secret)
Add a UI accessible to admins to view and filter all audit logs with pagination. Render git-style diff from before/after JSONB columns
Add a section in the existing edit UI to list all logs of related models with pagination

Audited models:

persons
field_values
group_members
person_accounts
users
user_roles
roles

Open questions:

How does the trigger know which user is doing the change ? Move every db call to a transaction so we can SET a variable ?
which author is referenced for actions not started by a user (initial seed, debug data). The author id could simply be nullable.
Every field_values and user_roles rows are deleted and re-created on edit. Either reuse rows, or move the diff to the "parent" model (diff person with all it's sub models as a single JSON, and store only that)
UserDelete blanks Name and Email, but now these values stay visible in the logs. Is that acceptable ? Could also log only the field name that change, or put name and email in the list of secret fields.
Gormigrate to be able to create triggers in a reproducible way ?
Don't use trigger and 2 JSONB fields, instead create the audit log from go which generates the diff, in every POST request ? This also takes care of knowing the author. But this spreads the concern all over the codebase and new code has to not forget to handle the log.
Store copy of author's name so we still know after the account is deleted ? Inconsistent with blanking out names on delete.
Auto-delete logs ?
Audit sections, lists, list items and field definitions ?

This new feature is meant to keep track of who changed what, when. It's basically a change history, for the purpose of auditing said changes. Scope: - Allow admins to see changes to the data - A full view, filterable by period, author, model - The last changes when editing an entry (such as a member), only for admins Implementation details: - Add a model for audit log (datetime, model name, row id, author id, before [JSONB of fields that changed], after [JSONB of fields that changed]). Hard deleted, cascade on purge - Add database triggers to create audit logs. It doesn't do anything if the diff is empty (updatedAt ignored). Secret fields are replaced with old/new placeholders (password, totp_secret) - Add a UI accessible to admins to view and filter all audit logs with pagination. Render git-style diff from before/after JSONB columns - Add a section in the existing edit UI to list all logs of related models with pagination Audited models: - persons - field_values - group_members - person_accounts - users - user_roles - roles Open questions: 1. How does the trigger know which user is doing the change ? Move every db call to a transaction so we can SET a variable ? 1. which author is referenced for actions not started by a user (initial seed, debug data). The author id could simply be nullable. 1. Every field_values and user_roles rows are deleted and re-created on edit. Either reuse rows, or move the diff to the "parent" model (diff person with all it's sub models as a single JSON, and store only that) 1. UserDelete blanks Name and Email, but now these values stay visible in the logs. Is that acceptable ? Could also log only the field name that change, or put name and email in the list of secret fields. 1. Gormigrate to be able to create triggers in a reproducible way ? 1. Don't use trigger and 2 JSONB fields, instead create the audit log from go which generates the diff, in every POST request ? This also takes care of knowing the author. But this spreads the concern all over the codebase and new code has to not forget to handle the log. 1. Store copy of author's name so we still know after the account is deleted ? Inconsistent with blanking out names on delete. 1. Auto-delete logs ? 1. Audit sections, lists, list items and field definitions ?

yhrel self-assigned this

2026-10-22 15:49:35 +02:00

yhrel added this to the Intégration JPOP project

2026-10-22 15:49:35 +02:00

yhrel changed title from ~~Audit table~~ to Audit log

2026-10-22 15:56:16 +02:00

wbouzour commented

2026-10-25 10:38:05 +01:00

Owner

You should use Gorm hooks. You can create a transaction inside of it to retain consistency. The user id is in session, and if you need to pass it to a hook, you could add a "last modification by" field in the model.
Nullable in DB, "System" in UI.
I was just being lazy. If you can patch the current code to reuse rows, it will be an improvement.
Yes it can stay visible in the logs, especially if we need an actual audit. The email is blanked so we can reuse it in the future (must be unique). Blanking the name is not really important.
Gormigrate will be needed in the future, but not required if you use Gorm hooks instead of triggers.
I'm more in favor of using Gorm hooks.
You could remove the name blanking as it is not needed. Only email blanking is required.
For audit logs, I'm in favor of keeping them forever.
We should start by auditing the important stuff, and if it's easy to do, we'll talk about auditing everything.

1. You should use Gorm hooks. You can create a transaction inside of it to retain consistency. The user id is in session, and if you need to pass it to a hook, you could add a "last modification by" field in the model. 2. Nullable in DB, "System" in UI. 3. I was just being lazy. If you can patch the current code to reuse rows, it will be an improvement. 4. Yes it can stay visible in the logs, especially if we need an actual audit. The email is blanked so we can reuse it in the future (must be unique). Blanking the name is not really important. 5. Gormigrate will be needed in the future, but not required if you use Gorm hooks instead of triggers. 6. I'm more in favor of using Gorm hooks. 7. You could remove the name blanking as it is not needed. Only email blanking is required. 8. For audit logs, I'm in favor of keeping them forever. 9. We should start by auditing the important stuff, and if it's easy to do, we'll talk about auditing everything.

yhrel referenced this issue

2026-11-01 20:26:38 +01:00

Implement audit log #10

yhrel commented

2026-11-01 20:26:58 +01:00

Author

Collaborator

Implemented in #10

wbouzour closed this issue

2026-11-05 19:55:37 +01:00

Rows
Columns

Audit log #7