Save what happens during the session

SQLite, but can (eventually) be any other DB engine.

A crazy idea comes to my mind yesterday or this morning (who cares ?):

is it included in SQLite a feature that can save what the user do during the session (obviously at the end of the session) ?

Or, must I do it all by myself ? Say store in a file - whatever - every change the user do at the moment it do it:
Store the previous Record and the new one WHEN the user make a change,
Store any newly added REcord (at add time, of course),
This may lead to a base to a “Who does what ?”.

The idea comes from some reports I do not understand (errors that happens only on Windows).

I have an application who is running three mornings a week, but for no apparent reason, fill service TABLEs with a Record (where only one Record is “allowed” / needed; a copyright Record that happears 7 times for example when in another t-service TABLE, I have 12 entries instead of 1…).
That application store window location and some other properties, but sometimes something go bersek and the values are wrong: the window opens elsewhere (out of the screen monitor).

You might want to look into triggers to monitor inserts, updates and deletes.

if you want a log of “adds”,“changes” and deletions, the as Scott indicated define triggers attached to every table in your database, and have them insert records into a “log” table

Most of the applications I have built over the past few years have included an audit feature.

Each table has a corresponding XXX_<table_name> containing all the attributes of the table being audited excluding the id field, plus record_id, logged_at, action, user, ip_address and application attributes. Where every CRUD operation is recorded.

There is also an activity log where every significant non CRUD operation is recorded as well, such as requesting a report be generated and so on. And of course there is an error log as well.

There is also an option to export this data with certain fields, such as account numbers, client names, balances, anonymized. So it can be sent to people like myself who are not allowed to see production data. The anonymization is two way, so that if I refer to account ZXC345, the business users know exactly which production record I’m talking about.

This has evolved over time because banks never ever allow me have access to their production environment.

I made a simplier approach using only one table for the logs

CREATE TABLE __user_logs ( id_user_log BIGSERIAL NOT NULL DEFAULT nextval('__user_logs_id_user_log_seq'::regclass), log_date_time TIMESTAMP , table_involved VARCHAR , record_id_involved INT8 , action VARCHAR , id_user INT4 , id_group INT4 )

each CRUD action is logged in this table, from any table input.
you can also monitor what you need more if needed.

as James did, I may add an IP Address log in this table.

Thanks all.