[dev] Audit trailing in Hermes

[Vinay Kumar]

Quoting Chuck Hagenbuch <chuck at horde.org>:

> Quoting Vinay Kumar <vinay.kumar at ejyothi.com>:
>
>> Thanks for the suggestions.
>>
>> Actually we are looking only for EDIT action on time slices . I think
>> logging all actions is good one but not essential now. Since we are
>> doing only the audit trail on EDIT action, duplicating record is a
>> good choice. The edit operations are rare in ideal situation and the
>> table may not grow larger. We would also like to track the changes in
>> all the fields of the timeslice table, so I think that this approach
>> will simplify the design.
>>
>> Kindly share your ideas also, so that we can evaluate the pros and
>> cons of the two approaches.
>
> I strongly object to simply creating duplicate rows in the same table.
> No matter the amount of data that is being duplicated - we have to
> duplicate *some* amount with audit logging - putting the duplicate
> data in the same table makes the data model seriously unclear.
>
> -chuck
Hi all,
     Sorry for the delayed response, since we were busy with some  
client projects and vacation. We have throughly examined the pros and  
cons of the two methods for audit trail. We think that a separate  
table for audit trail may make more sense, because the audit  
information is  normally required by the admins/reviewers and the  
normal users may not be interested in the audit log.

Following is the structure of the log table

1. log_id
2. User ID
3. Module -- timeslice/jobtype/deliverable
4. action -- add/update/delete/select
5. log description
6. previous data (serialized)
7. current data (serialized)
8. Modified Timestamp

If a timeslice entry is edited, it will store both the previous and  
current data  in a serilized string(like that of a datatree table). If  
an entry is deleted, it will store only  the previous data and if a  
new entry is added, then only the current data is used.

Please let me know if you have any questions or suggestions. I would  
like to push it for the first stable release of hermes.

Regards,
Vinay Kumar
Programmer Analyst
Ejyothi Services/Ascella Technologies.