Re: Database Design: Maintain Audit Trail of Changes

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)

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle

On Thu, Jan 3, 2013 at 10:38 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>wrote:

> 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.
>
> TIA,
>
> Rich
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>