Audit trail

Some applications, those that deal with medical records for example, need an audit trail. I.e. they need to keep track of every database change. One way to achieve this is by

time stamping and signing every records of every table
storing a copy of the current record every time it is updated
marking new/modified records as "active" and stored records as "inactive".
identify all modifications of the same record with a UUID

(this solution does not break references)

Here is a simple recipe to implement it:

In the model

Insert, somewhere after you define db, auth and crud:

import uuid
user_id = auth.user.id if auth.user else 0
unique_id = Field('unique_id','boolean',default=uuid.uuid4(),writable=False,readable=False)
active = Field('active','boolean',default=True,writable=False)
created_by = Field('created_by',db.auth_user,default=user_id,writable=False)
created_on = Field('created_on','datetime',default=request.now,writable=False) 
modified_by = Field('modified_by',db.auth_user,default=user_id,update=user_id,writable=False)
modified_on = Field('modified_on','datetime',default=request.now,update=request.now,writable=False)

Edit all the tables that need an audit trial as follows:

db.define_table('mytable',...other fields...,unique_id,active,created_by,created_on,modified_by,modified_on)

Reference records by "id" as you would normally do.

Define the following function

def copy_record(table):
    def copy_record_aux(form, table=table):
        if form.record:
            d = dict([(f,form.record[f]) for f in table.fields if not f=='id'])
            d['active'] = False
            table.insert(**d)
    return copy_record_aux

In controllers

Use the following syntax for crud.update. If "id" is valid then when the form is submitted the current record is stored and the updated. If "id" is None or 0, then a new record is created, timestamped and signed.

@auth.requires_login()
def update():
    id = request.args(0)
    form = crud.update(db.mytable, id, onvalidation=copy_record(db.mytable))
    return dict(form=form)

Make sure every time you select records to select only active one

    db(db.mytable.active==True)(query).select()

You can select all previous versions of any record by

   db(db.mytable.unique_id=='<unique_id>').select(orderby=db.mytable.modified_on)

Google App Engine?

yes, it runs on Google App Engine

Comments (4)

Login to post
order by: newest oldest upvoted downvoted
0
pwoolf 15 years ago
1. following is a way to define a field group that can be appended to table definitions. Note that unique_id is string, not boolean as shown above. I added a field, 'reason', for my own purposes. #audit field group audit=db.Table(None,'audit', Field('unique_id', default=uuid.uuid4(), writable=False, readable=False), Field('active', 'boolean', default=True, comment='set Active off to delete record'), Field('created_by', db.auth_user, default=user_id, writable=False), Field('created_on', 'datetime', default=request.now, writable=False), Field('modified_by', db.auth_user, default=user_id, update=user_id, writable=False), Field('modified_on', 'datetime', default=request.now, update=request.now, writable=False), Field('reason', requires=IS_IN_SET(('change', 'new info', 'correction'))) ) db.define_table('home', Field('city'), audit, ) #Note that the validation of the reference from person to home below requires a filter on active rows only. db.define_table('person', Field('firstname'), Field('lastname'), Field('home', db.home, requires=IS_IN_DB(db(db.home.active==True), 'home.id', 'home.city')), audit, ) 2. A record delete for an audit-ed table should be logical only, achieved by setting active to False, & not allowing actual record deletion. Include deletable=False, in the crud.update to suppress delete checkbox. 3. You can use named scopes to separate out the selection of active records from other selection criteria to make the code clearer: person_active=db.person.active==True # required once only in controller def person_list_S(): rows=db(person_active)(db.person.lastname.like('S%')).select() return dict(rows=rows) #or, active=db(db.person.active==True), then rows=active(db...) ?The update fn shown in the slice does not show the updated record, even though the update is executed correctly.
0
villas 15 years ago
@aleksdj Yes, see Web2py 2nd Manual Pg 179, section Table Inheritance
0
aleksdj 15 years ago
I think I saw a way to append fields to table using only a variable, something like this in pseudocode: variable auditFields = defineFields (unique_id =Field('unique_id','boolean',default=uuid.uuid4(),writable=False,readable=False), bla bla) and now, for each table you can do: db.define_table('mytable', other fields,.., auditFields) I couldnt find it but I'm almost sure that I saw it.
0
mrfreeze 15 years ago
Perfect timing! I need just this sort of thing for a new project. Thanks.

Audit trail

Audit trail

In the model

In controllers

Google App Engine?

Related slices

Comments (4)

pwoolf 15 years ago

villas 15 years ago

aleksdj 15 years ago

mrfreeze 15 years ago