As hinted in the blog post on upcoming features, CiviCRM 3.3 will ship with the first cut of database-level logging.

This feature was first discussed alongside contact undelete (introduced in CiviCRM 3.2) and specified further on the wiki, and after running some tests the current idea of the implementation follows quite closely what was discussed and – especially – speced there (so do check the wiki for details).

The general idea is that every CiviCRM table that we’ll want to log will acquire a counterpart log table (e.g., civicrm_contact_log will be introduced to track civicrm_contact changes) that will be append-only (and so use the ARCHIVE engine). The log tables will mirror their counterpart ‘source’ tables, with additional columns for storing the logtime, the MySQL connection id, the contact id of the current CiviCRM user and the action that is happening to the ‘source’ table (insert/update/delete).

The log tables will be populated using triggers on the relevant ‘source’ tables, with the connection id and the current CiviCRM user coming from the current session. This approach allows us to offload the logging to the database, which should be much more efficient, less error-prone and automated.

As triggers are often not available on shared hosting, logging will be an optional feature (off by default), available for enabling after a check that all the requirements are met.

We plan to leverage the CiviReport stack to do log-based reporting; in the future (CiviCRM 3.3 if possible for a first cut) we also plan to introduce undo/revert functionality; this will be based on the connection id tracking in the log tables (so an undo/revert atom will be whatever happened to the database during a single connection).