audit trail, take 2

[David Merrill]

I've worked what I thought were the best ideas on how to handle the
audit trail into this:


AUDITING
--------

The database provides a complete audit trail of all changes to data. The audit
trail mechanism uses the "breadcrumb" approach. No financial data is ever
changed or deleted in the physical database. Instead, records that become
invalid are marked as outdated, and a new record is posted with the new data.

When a record is created, it is marked with the user's guid and a timestamp,
so we always know who created the record and when. The record's status is 'A'.

If a record is deleted, its status is set to 'D'. The user's dbuser_guid is
recorded in update_dbuser_guid, and it is timestamped in update_timestamp.

If a record is changed, its status is set to 'U'. The new data is
stored to a new record. The create_dbuser_guid and create_timestamp are left as
they were on the original record, and update_dbuser_guid and update_timestamp
are set to the user and time of the change. The first record's update_guid is
set to the guid of the new record so we know which record replaced it.

Assigning the record a new guid doesn't affect the client because we use a
transaction_id to identify the transaction, that remains the same in edits.

Of course, all of this is handled completely by the db, and when the engine
requests records it gets a simple set of the active records. Only the
database knows that any of these audit records even exist, unless an admin
comes in via the backdoor of the db and browses the records.


Examples
--------

New record:

xaction_guid	 AAAAAAAAAAAAAAAA
xaction_id	 1
update_guid	 NULL
amount		 100.00
status		 A
create_dbuser	 david
create_timestamp Monday at 8:00 AM
update_dbuser	 NULL
update_timestamp NULL

Here is the record, freshly entered.


Update:

xaction_guid	 AAAAAAAAAAAAAAAA
xaction_id	 1
update_guid	 BBBBBBBBBBBBBBBB
amount		 100.00
status		 U
create_dbuser	 david
create_timestamp Monday at 8:00 AM
update_dbuser	 robert
update_timestamp Tuesday at 8:30 AM

xaction_guid	 BBBBBBBBBBBBBBBB
xaction_id	 1
update_guid	 NULL
amount		 110.00
status		 A
create_dbuser	 david
create_timestamp Monday at 8:00 AM
update_dbuser	 robert
update_timestamp Tuesday at 8:30 AM

Here, a record has been created by david, but then changed by robert.


Delete:

xaction_guid	 AAAAAAAAAAAAAAAA
xaction_id	 1
update_guid	 NULL
amount		 100.00
status		 D
create_dbuser	 david
create_timestamp Monday at 8:00 AM
update_dbuser	 robert
update_timestamp Tuesday at 8:30 AM

And this is what the record would look like if robert deleted it.



The major changes are:

- a single table; no separate audit table.
- the client doesn't work directly with guids, but instead works with
  an "ID" field that does not change when records are edited. The guid
  stays globally unique. The client still has access to the guid, if it
  wants it.

Did I miss anything? There are so many threads going I could have very
easily done that. But that's not a complaint; I'm very glad to see so
many folks interested in this.

-- 
Dr. David C. Merrill                     http://www.lupercalia.net
Linux Documentation Project                dmerrill@lupercalia.net
Collection Editor & Coordinator            http://www.linuxdoc.org
                                       Finger me for my public key

I stopped believing in Santa Claus when I was six. Mother took me to see him in a department store and he asked for my autograph.
		-- Shirley Temple