Comply with GDPR by creating an audit trail

[avidenic]

According to GDPR there should be an audit trail of actions performed on records (create, update, delete).

Revision trail must save data about:

• WHO (user that performed an action) did
• WHAT (object; field; action; original value; changed value) and
• WHEN (date, time of action) (dd.mm.yyy; hh:mm)

The question that presents itself is: is it a good idea to do it in code, or install one of mysql logging plugins?

I would avoid triggers, but that is just my personal preference.

[anuko]

Perhaps, best is to support this in the product for compliance with all sorts of rules and regulations, as the issue occasionally comes up.

[avidenic]

I was thinking that as well. The next question is: for the sake of code maintainability, does it make sense to introduce an ORM, so we can do audit trail in one place?

[anuko]

What exactly are you suggesting? An additional audit log table where everything goes, when enabled?

[avidenic]

Yes. I propose it to be as simple as possible, with these fields:

• id - primary key
• user_id (foreign key to user)
• state (added / deleted / modified)
• type / name of db object
• old value - serialised object into JSON. empty in case record was added
• new value - serialised object into JSON, empty in case record was deleted
• table name - in case of nested objects, we keep just changes for parent. its up for discussion
• identity - primary key(s) of the object serialised into JSON so we can link it to the record in DB
• timestamp

we can than calculate differences for each change on the fly if needed (by iterating through objects properties) - for displaying to the user.

What do you think? I would prefer to have some kind of ORM, so this can be implemented to work "automagically", but it would be big enough task as it is, and maybe we move this to "phase 2".

[anuko]

How about simply having created, created_by, created_ip, modified, modified_by, modified_ip fields for all entities in sql tables that matter (similarly to what we have in tt_groups, tt_users, and tt_log now). This looks pretty straightforward and does not over-complicate things.

created datetime default NULL, # creation timestamp
created_ip varchar(45) default NULL, # creator ip
created_by int(11) default NULL, # creator user_id
modified datetime default NULL, # modification timestamp
modified_ip varchar(45) default NULL, # modifier ip
modified_by int(11) default NULL, # modifier user_id

[avidenic]

Hi,

sorry for the late reply. Sadly this is not enough. We've checked out some options and I implemented a rudimentary support a while ago but did not create a merge request because feature is not finished.

You can check the changes here: https://github.com/avidenic/timetracker/tree/audit_log

When I find the time I will finish it - add UI, settings etc....