This is an attempt to push the discussion at http://civicrm.org/node/674 to a further level of detail.

This is intended to specify Phase I functionality and its implementation approach. A Phase II section is included as a catch-all for ideas that might be looked at later.

Phase I

Objective

To create a logging system that will provide a fairly complete record of all data changes: who made them, when they were made, and what the changes were. This logging system is intended to be the basis for the future development of user facing functionality.

Exclusions

When data structures change, eg dropping a custom field or dropping a group of custom fields, data in the eliminated structures will not be preserved.

Implementation

1. The following tables will be dropped:
   1. civicrm_log
2. A new page will be added Administer > Configure > Global Settings > Logging, which will allow users to enable and disable logging.
   1. When logging is disabled, the triggers for logging will be dropped.
   2. When the user requests to enable logging, a check will be made to ensure that the MySQL version and database user permissions support creating triggers. If these are not appropriate, an error message will be provided to users with instructions to upgrade, as well as the need to ensure permission grants are properly changed after a 5.0 -> 5.1 upgrade. When adequate db privileges exist, the gencode produced code to modify the database structure will be called by this page.
   3. When logging is enabled, if log tables exist they will be truncated before their base table has triggers added to it. The user will be required to approve a warning message before this action is carried out.
3. After the database connection has been initialized, if logging is enabled, civicrm_initialize will set a MySQL variable to save the current UserID for later use in triggers set up for all loggable events, eg:
   1. $dao->query(sprintf("set @current_user = %d", $session->get('userID')));
4. The gencode program that reads the database schema and creates the DAO layer and mysql to create the CiviCRM database will be modified to support logging in the following ways:
   1. The existing <log> tag for tables in the XML schema will be repurposed to support the new more extensive logging proposed here.
   2. Logging will be enabled for the following CiviCRM tables (generally, ones that contain contact relationship information and with records that can be edited and/or deleted through the UI), which will be indicated by adding <log>true</log> to their XML schema definitions:

      civicrm_activity

      civicrm_activity_assignment

      civicrm_activity_target

      civicrm_address

      civicrm_case

      civicrm_case_activity

      civicrm_case_contact

      civicrm_contact

      civicrm_contribution

      civicrm_contribution_page

      civicrm_contribution_product

      civicrm_contribution_recur

      civicrm_contribution_soft

      civicrm_contribution_type

      civicrm_contribution_widget

      civicrm_currency

      civicrm_custom_field

      civicrm_custom_group

      civicrm_discount

      civicrm_email

      civicrm_entity_file

      civicrm_entity_tag

      civicrm_event

      civicrm_file

      civicrm_financial_trxn

      civicrm_grant

      civicrm_group

      civicrm_group_contact

      civicrm_group_nesting

      civicrm_group_organization

      civicrm_im

      civicrm_line_item

      civicrm_loc_block

      civicrm_location_type

      civicrm_membership

      civicrm_membership_block

      civicrm_membership_payment

      civicrm_membership_status

      civicrm_membership_type

      civicrm_note

      civicrm_option_group

      civicrm_option_value

      civicrm_participant

      civicrm_participant_payment

      civicrm_participant_status_type

      civicrm_pcp

      civicrm_pcp_block

      civicrm_phone

      civicrm_pledge

      civicrm_pledge_block

      civicrm_pledge_payment

      civicrm_preferences

      civicrm_preferences_date

      civicrm_premiums

      civicrm_premiums_product

      civicrm_price_field

      civicrm_price_set

      civicrm_price_set_entity

      civicrm_product

      civicrm_project

      civicrm_relationship

      civicrm_relationship_type

      civicrm_subscription_history

      civicrm_tag

      civicrm_task

      civicrm_task_status

      civicrm_uf_field

      civicrm_uf_group

      civicrm_uf_join

      civicrm_uf_match

      civicrm_value_*

   3. Tables with logging enabled (called base tables) will have an additional log table created for them, eg civicrm_contact will have a civicrm_contact_log table created to store the log of inserts, updates and deletes to its records. Each log table will contain all the fields in its base table, and the following additional fields:
      `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Contact Log ID',
      `log_ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp when Contact Log record created',
      `log_contact` INT(10) unsigned NOT NULL COMMENT 'ID of Contact who acted to create the Contact Log record',
      `log_action` ENUM ('Initialization', 'Insert', 'Update', 'Delete') NOT NULL COMMENT 'Action that created Contact Log record'
   4. If data exists in a base table when logging for it is enabled, corresponding records will be inserted into the log table with log_action set to 'Initialization'.
   5. For performance reasons, indexes will kept to a minimum on log tables. Indexes will be selectively added as functionality to use the log tables is developed. For example, the following indexes exist on civicrm_contact: PRIMARY KEY (`id`),
        UNIQUE KEY `UI_external_identifier` (`external_identifier`),
        KEY `index_contact_type` (`contact_type`),
        KEY `index_contact_sub_type` (`contact_sub_type`),
        KEY `index_sort_name` (`sort_name`),
        KEY `index_preferred_communication_method` (`preferred_communication_method`),
        KEY `index_hash` (`hash`),
        KEY `index_api_key` (`api_key`),
        KEY `index_first_name` (`first_name`),
        KEY `index_last_name` (`last_name`),
        KEY `UI_prefix` (`prefix_id`),
        KEY `UI_suffix` (`suffix_id`),
        KEY `UI_gender` (`gender_id`),
        KEY `index_household_name` (`household_name`),
        KEY `index_organization_name` (`organization_name`),
        KEY `FK_civicrm_contact_mail_to_household_id` (`mail_to_household_id`),
        KEY `FK_civicrm_contact_primary_contact_id` (`primary_contact_id`),
        KEY `FK_civicrm_contact_employer_id` (`employer_id`). The corresponding log table will have the following indexes (other log tables will only have the first four indexes): PRIMARY KEY (`id`, `log_id`),
        KEY `index_id` (`id`),
        KEY `index_log_id` (`log_id`),
        KEY `index_log_ts` (`log_ts`),
        KEY `index_contact_type` (`contact_type`),
        KEY `index_contact_sub_type` (`contact_sub_type`),
        KEY `index_sort_name` (`sort_name`),
        KEY `index_household_name` (`household_name`),
        KEY `index_organization_name` (`organization_name`).
   6. Constraints on base tables will not be retained for log tables to improve performance.
   7. Here are samples of the three triggers that will be created for each log table:
      1. CREATE DEFINER='civicrm_db_user'@'localhost'
         TRIGGER civicrm_contact_after_insert
         AFTER INSERT ON civicrm_contact FOR EACH ROW
         INSERT INTO civicrm_contact_log
         (log_contact, log_action, `id` , `contact_type` , `contact_sub_type`,`do_not_email` ,  `do_not_phone`,  `do_not_mail` ,  `do_not_sms` ,  `do_not_trade` ,  `is_opt_out`,  `legal_identifier`,  `external_identifier`,  `sort_name`,  `display_name` ,  `nick_name`,  `legal_name`,  `home_URL` ,  `image_URL` ,  `preferred_communication_method` ,  `preferred_mail_format` ,  `hash`,  `api_key`,  `source` ,  `first_name` ,  `middle_name` ,  `last_name` ,  `prefix_id` ,  `suffix_id` ,  `email_greeting_id` ,  `email_greeting_custom` ,  `email_greeting_display` ,  `postal_greeting_id`,  `postal_greeting_custom` ,  `postal_greeting_display`,  `addressee_id` ,  `addressee_custom`,  `addressee_display` ,  `job_title` ,  `gender_id` ,  `birth_date` ,  `is_deceased` ,  `deceased_date` ,  `mail_to_household_id`,  `household_name`,   `primary_contact_id`,  `organization_name` ,   `sic_code` ,  `user_unique_id` ,  `employer_id` )
         VALUES (@current_user, 'INSERT', NEW.`id`, NEW.`contact_type`, NEW.`contact_sub_type`, NEW.`do_not_email`, NEW.`do_not_phone`, NEW.`do_not_mail`, NEW.`do_not_sms`, NEW.`do_not_trade`, NEW.`is_opt_out`, NEW.`legal_identifier`, NEW.`external_identifier`, NEW.`sort_name`, NEW.`display_name`, NEW.`nick_name`, NEW.`legal_name`, NEW.`home_URL`, NEW.`image_URL`, NEW.`preferred_communication_method`, NEW.`preferred_mail_format`, NEW.`hash`, NEW.`api_key`, NEW.`source`, NEW.`first_name`, NEW.`middle_name`, NEW.`last_name`, NEW.`prefix_id`, NEW.`suffix_id`, NEW.`email_greeting_id`, NEW.`email_greeting_custom`, NEW.`email_greeting_display`, NEW.`postal_greeting_id`, NEW.`postal_greeting_custom`, NEW.`postal_greeting_display`, NEW.`addressee_id`, NEW.`addressee_custom`, NEW.`addressee_display`, NEW.`job_title`, NEW.`gender_id`, NEW.`birth_date`, NEW.`is_deceased`, NEW.`deceased_date`, NEW.`mail_to_household_id`, NEW.`household_name`, NEW.`primary_contact_id`, NEW.`organization_name`, NEW.`sic_code`, NEW.`user_unique_id`, NEW.`employer_id`) ;
      2. CREATE DEFINER='civicrm_db_user'@'localhost'
         TRIGGER civicrm_contact_after_update
         AFTER UPDATE ON civicrm_contact FOR EACH ROW
         INSERT INTO civicrm_contact_log
         (log_contact, log_action, `id` , `contact_type` , `contact_sub_type`,`do_not_email` ,  `do_not_phone`,  `do_not_mail` ,  `do_not_sms` ,  `do_not_trade` ,  `is_opt_out`,  `legal_identifier`,  `external_identifier`,  `sort_name`,  `display_name` ,  `nick_name`,  `legal_name`,  `home_URL` ,  `image_URL` ,  `preferred_communication_method` ,  `preferred_mail_format` ,  `hash`,  `api_key`,  `source` ,  `first_name` ,  `middle_name` ,  `last_name` ,  `prefix_id` ,  `suffix_id` ,  `email_greeting_id` ,  `email_greeting_custom` ,  `email_greeting_display` ,  `postal_greeting_id`,  `postal_greeting_custom` ,  `postal_greeting_display`,  `addressee_id` ,  `addressee_custom`,  `addressee_display` ,  `job_title` ,  `gender_id` ,  `birth_date` ,  `is_deceased` ,  `deceased_date` ,  `mail_to_household_id`,  `household_name`,   `primary_contact_id`,  `organization_name` ,   `sic_code` ,  `user_unique_id` ,  `employer_id` )
         VALUES (@current_user, 'INSERT', NEW.`id`, NEW.`contact_type`, NEW.`contact_sub_type`, NEW.`do_not_email`, NEW.`do_not_phone`, NEW.`do_not_mail`, NEW.`do_not_sms`, NEW.`do_not_trade`, NEW.`is_opt_out`, NEW.`legal_identifier`, NEW.`external_identifier`, NEW.`sort_name`, NEW.`display_name`, NEW.`nick_name`, NEW.`legal_name`, NEW.`home_URL`, NEW.`image_URL`, NEW.`preferred_communication_method`, NEW.`preferred_mail_format`, NEW.`hash`, NEW.`api_key`, NEW.`source`, NEW.`first_name`, NEW.`middle_name`, NEW.`last_name`, NEW.`prefix_id`, NEW.`suffix_id`, NEW.`email_greeting_id`, NEW.`email_greeting_custom`, NEW.`email_greeting_display`, NEW.`postal_greeting_id`, NEW.`postal_greeting_custom`, NEW.`postal_greeting_display`, NEW.`addressee_id`, NEW.`addressee_custom`, NEW.`addressee_display`, NEW.`job_title`, NEW.`gender_id`, NEW.`birth_date`, NEW.`is_deceased`, NEW.`deceased_date`, NEW.`mail_to_household_id`, NEW.`household_name`, NEW.`primary_contact_id`, NEW.`organization_name`, NEW.`sic_code`, NEW.`user_unique_id`, NEW.`employer_id`) ;
      3. CREATE DEFINER='civicrm_db_user'@'localhost'
         TRIGGER civicrm_contact_after_delete
         AFTER DELETE ON civicrm_contact FOR EACH ROW
         INSERT INTO civicrm_contact_log
         (log_contact, log_action, `id` , `contact_type` , `contact_sub_type`,`do_not_email` ,  `do_not_phone`,  `do_not_mail` ,  `do_not_sms` ,  `do_not_trade` ,  `is_opt_out`,  `legal_identifier`,  `external_identifier`,  `sort_name`,  `display_name` ,  `nick_name`,  `legal_name`,  `home_URL` ,  `image_URL` ,  `preferred_communication_method` ,  `preferred_mail_format` ,  `hash`,  `api_key`,  `source` ,  `first_name` ,  `middle_name` ,  `last_name` ,  `prefix_id` ,  `suffix_id` ,  `email_greeting_id` ,  `email_greeting_custom` ,  `email_greeting_display` ,  `postal_greeting_id`,  `postal_greeting_custom` ,  `postal_greeting_display`,  `addressee_id` ,  `addressee_custom`,  `addressee_display` ,  `job_title` ,  `gender_id` ,  `birth_date` ,  `is_deceased` ,  `deceased_date` ,  `mail_to_household_id`,  `household_name`,   `primary_contact_id`,  `organization_name` ,   `sic_code` ,  `user_unique_id` ,  `employer_id` )
         VALUES (@current_user, 'DELETE', OLD.`id`, OLD.`contact_type`, OLD.`contact_sub_type`, OLD.`do_not_email`, OLD.`do_not_phone`, OLD.`do_not_mail`, OLD.`do_not_sms`, OLD.`do_not_trade`, OLD.`is_opt_out`, OLD.`legal_identifier`, OLD.`external_identifier`, OLD.`sort_name`, OLD.`display_name`, OLD.`nick_name`, OLD.`legal_name`, OLD.`home_URL`, OLD.`image_URL`, OLD.`preferred_communication_method`, OLD.`preferred_mail_format`, OLD.`hash`, OLD.`api_key`, OLD.`source`, OLD.`first_name`, OLD.`middle_name`, OLD.`last_name`, OLD.`prefix_id`, OLD.`suffix_id`, OLD.`email_greeting_id`, OLD.`email_greeting_custom`, OLD.`email_greeting_display`, OLD.`postal_greeting_id`, OLD.`postal_greeting_custom`, OLD.`postal_greeting_display`, OLD.`addressee_id`, OLD.`addressee_custom`, OLD.`addressee_display`, OLD.`job_title`, OLD.`gender_id`, OLD.`birth_date`, OLD.`is_deceased`, OLD.`deceased_date`, OLD.`mail_to_household_id`, OLD.`household_name`, OLD.`primary_contact_id`, OLD.`organization_name`, OLD.`sic_code`, OLD.`user_unique_id`, OLD.`employer_id`) ;
   8. gencode.php will produce code to enable and disable logging, including the creation of 'Initialization' records in log tables as the log table is enabled.
   9. Where database constraints are used to cascade changes to other tables (if they are) from base tables, these cascading actions will be rewritten into triggers on the base tables. For the sake of simplicity, when logging is disabled, these cascading actions will still be implemented via triggers rather than constraints.
5. The CiviCRM upgrade script will be modified in the following ways:
   1. If *_log tables exist, then all changes to their base tables will also be made to the *_log tables.
6. Inserts, updates, and deletions of custom fields and custom groups will be mirrored to the corresponding log tables.

Unit Testing

1. Gencode.php will produce the following PHP unit tests for each table that is loggable:
   1. Does enabling logging on a table for the first time create the appropriate *_log table?
   2. Does enabling logging on a table for the first time create appropriate 'Initialization' records for each record in the base table?
   3. Does enabling logging on a table after it has been disabled truncate the *_log table?
   4. Does enabling logging on a table after it has been disabled create appropriate 'Initialization' records for each record in the base table?
   5. Record level tests for insert, update and delete triggers:
      1. When logging is enabled, do the actions on the base table produce a record in the log table?
      2. When logging is disabled, do the actions on the base table produce no record in the log table?
      3. If there are cascading actions on a base table, do the cascading actions occur before logging is enabled?
      4. If there are cascading actions on a base table, do the cascading actions occur when logging is enabled?
      5. If there are cascading actions on a base table, do the cascading actions occur after logging has been enabled and disabled?
   6. Field level tests for insert, update and delete triggers:
      1. Does each base table field get accurately copied to log table?
      2. Do the values for the four log_* fields get accurately set?

Phase II

Administer > Configure > Global Settings > Logging will allow logging to be selectively enabled by table.

Functionality will be developed to make use of the logs. In particular, a Quality Assurance function will allow users with appropriate permissions (eg are members of a Data QA role ACL) to review and approve all data changes to all records since they were last QA'd. This will likely involve creating a new enum value of 'DataQA' for log_action.

1. We will create permissions that decide who can commit changes without QA approval. If the user does not hav that permission their changes go into a queue.
   1. This permission will only affect demographic tables (like civicrm_contact, civicrm_address, civicrm_email, etc...) as we don't want to prevent Contributions, Event registrations, Actions and Memberships from hitting the database immediately.
2. Administrators, with "commit changes" rights will have access to the queue and will have the ability to review. The queue will be a list of records that have been changed and with the date of the latest change.
   1. names in the queue will be links to the record in question or a the header on a cascading fieldset that will open to reveal a list of the fields that are changed grouped by date. So if you have changes on 1/1/10 and 12/31/09 you will have two groups of fields.
      1. the list of changes will be structured in a table/grid (using divs of course ) three rows, field name, the new value as an editable field and the old value. The admin will be able to edit the 2nd row (the new value) and save the entire record. One major trick I have worked through is what to do if the same field is edited on multiple days, hence it gets rendered in multiple groups. The simple answer is to take the latest revision and train admins to only edit that one.
      2. How do we reject an edit so it doesn't show up each time the queue is visited. The logging tables will have to have a "reject" boolean field to track this.
         1. to this end we will want to save records for later consideration or for staff to ask someone else to look at while retaining the ability to mark records a reviewed to ensure they don't continue to show up.
   2. We will likely want for the admin to be able to sort, but that's low priority for now.