Data model: Track creation+modification times for activities+cases

== Background ==

In developing workflows, reports, and UIs for activities and cases, it is useful to sort and filter based based on when a record was created or when a record was last modified.

For cases and activities, this information is sometimes available in another table, such as "log_civicrm_case", "log_civicrm_activity", "civicrm_log", or "civicrm_activity". However, working with this data poses a few challenges:

• Sorting and grouping by data in another table is more complicated and harder to optimize.
• The "log_civicrm_case" and "log_civicrm_activity" are only available if you enable logging.
• Any of the log tables ("civicrm_log", "log_civicrm_{case,activity}") may be incomplete – either because the implementer truncated them to reduce load, because logging wasn't always enabled, or because the data wasn't migrated.

Previously, we faced a similar issue with contact records (--CRM-10554--) which was addressed by adding new SQL columns and triggers ("CRM_Contact_BAO_Contact::triggerInfo"). We can do the same thing for cases and activities. Notably, this change is additive: we do not need to change, replace, or disable existing elements or behaviors.

== Acceptance Criteria ==

• Schema: Schema for {new or upgraded} sites include columns for the {creation or modification} timestamps of {cases and activities}.
• Activity behavior: When {creating or modifying} an activity via the following interfaces, the activity timestamps are updated.
  • APIv3
  • "New Activity" screen
  • "View Contact" screen
  • "Manage Case" screen
• Case behavior: When {creating or modifying} a case via the following interfaces, the case timestamps are updated.
  • APIv3
  • "New Case"
  • "Manage Case"
• Case-Activity behavior: When creating or modifying a case-related activity (via any of the above), the case timestamps are updated.
• APIv3: The {creation and modification} timestamps of {cases and activities} can be returned, filtered, or sorted via APIv3.
• Migration: When upgrading an existing site:
  • The activity creation times are either (a) the activity's oldest timestamp from "civicrm_log" or (b) NULL if unavailable.
  • The activity modification times are either (a) the activity's newest timestamp from "civicrm_log" or (b) NULL if unavailable.
  • The case creation times are either (a) the "Open Case" activity_date_time or (b) the oldest creation-time of any activity in the case, or (c) NULL if unavailable.
  • The case modification times are either (a) the newest creation or modification time of any included activity, or (b) NULL if unavailable.
• Cleanup: If a database contains any NULL timestamps, the system-check displays a message like this: "This database contains $X activity records and $Y CiviCase records with unclear timestamps. These records were probably created before the software had functionality to track them. At time of writing, this issue is not critical because core UI's. However, future extensions/reports/upgrades may try to query this data. For suggestions on backfilling timestamps, (click here)."

== Pre-emptive Critiques/Disclaimers ==

• Triggers: Around the same time as -----CRM-9716----- and -----CRM-10554-----, there were critiques about the usage of triggers: in particular, Civi's workflow for creating/destroying triggers was focused on standard/baseline deployments (one mysqld, with admin rights, not replicated, etc) but didn't specify a good workflow for deploying on more sophisticated systems. IMHO, today the trigger system is part of the platform (used several ways by core and documented for use by extensions). Lingering concerns about their workflow should be orthogonal – if you want a different workflow for managing triggers, then please work directly on that in a separate issue.
• Data completeness: One factor influencing the design/scope of this ticket is the theory that systems in the wild will have imperfect data about case/activity timestamps. Unfortunately, we lack a mechanism to test or measure this theory. The only thing within our power as developers is to code defensively and report when/if there are issues with completeness. This ticket anticipates a phased rollout:
  • Phase 1: Update core code
    • Add the SQL columns and SQL triggers.
    • Migrate any data that is readily resolvable.
    • But if data is not available, leave it blank. (This means that creation/modification times are NULLable.)
    • For sake of continuity, existing UIs/reports in "civicrm-core" should continue working with "log_civicrm_{foo}", "civicrm_log", or whatever they're doing today. They'll behave the same as before, regardless of completeness_P_
  • Phase 2: Incremental cleanups
    • After deployment, report to administrators when/if there are any blanks. Suggest techniques for working around.
  • Phase 3: Gradual uptake
    • In extensions, begin using the new fields gradually.