I know this is a terribly old thread, but if you are still looking for software to provide an audit trail of changes in the database, please see Cyan Audit at http://pgxn.org/dist/cyanaudit. I think it will do just what you're looking for.
(Full disclosure: I am the author of this software)
I have the need to develop an application that will use postgres as the back end, and most of the design has been worked out, but I've one issue left to resolve and want help in this. If this is not the appropriate forum for this type of question, please point me in the right direction.
For several reasons (including operational and legal) once data are entered in a table they cannot be changed or deleted without an audit trail of the change, when it occurred, who made the change, and the reason for it. Tables might contain laboratory or instrument measurement values or the names of regulatory staff.
My current thoughts are that there needs to be a separate table, perhaps called 'changes', with attribute columns for the source table, identifying value(s) for the original row, new value, date of change, person making the change, and the reason for the change. The original table should have an attribute flag to indicated that a row has been changed.
The middleware of the application needs to check this table when data are to be viewed in the UI and present only the current row contents. A separate view would display a history of changes for that row.
All thoughts, suggestions, and recommendations based on your expertise and experience will be most welcome.
