PostgreSQL provides a sample trigger timetravel which can be used to log all changes to a database row. Two columns must be added to any table which should use this feature: start_date and stop_date. Additionaly three more columns can be used to log the login name of the user inserting, updating or deleting a row.
Each time a new row is inserted the start_date is set to the current time and stop_date is set to infinity. If an entry is deleted the stop_date is set to the current time. Each update will modify the existing row setting its stop_date and insert a new row containing the updated row with the correct start_date.
Thus the table will contain the history of each row. The currently active rows can be selected by using a ‘WHERE stop_date = infinity’ clause.
Any comments, suggestions or bug reports are welcome.