Re: [Gnumed-devel] audit trail question

[Karsten Hilbert]

Engelbert,

> i did 3 for a bill managment system years ago, there was one execute_sql
> function and this one checked if it is an insert update or delete
> and if puts it into the changelog table (which therefore grew
> fast and sometimes blew up the indexes).

That's doable even inside the database: just fire triggers upon
insert/delete/update. One can (and should) regularly (say,
daily) dump and empty the audit trail tables and notarize the
dump. If an online database of audit data is wanted (perhaps
for being able to display a revision history of a record) one
could restore the dump into a dedicated database.

> but on occasion i took a setup the system from a backup and reexecuted
> the queries from the changelog.
This is one thing we were thinking about and that's one of the
reasons gate-ware audit trailing had been discussed
previously. We need to come to a clear understanding of what
we want from out audit trail. Do we want to just be able to
track changes to a column or row of columns ? That would serve
the medico-legal aspect of audit trailing. Or do we also want
to be able to easily recreate the entire query history in
which case we'd need employ a dedicated gate-ware (middleware
sitting at the gate to PostgreSQL. That would afford us a
complete query log for nearly arbitrary rollback/roll-forward.

> 1 and 2 might allow to show what has changed aside the current entry.
Well, since all business level changes are going to happen
within the same transaction it is possible to pull all
modifications with the same timestamp by the same committer --
this *should* represent the changes within that transaction.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346