CiviCase - Data Objects and Properties

Specification of the schema usage and changes needed to meet CiviCase requirements/use cases for phase one.

Case (civicrm_case)

Changes to Case

• Implement custom field functionality for Case object.

Relationship (civicrm_relationship)

Relationships will be used to store "Case Roles" by extending the schema to provide linkage between a relationship and a case (in addition to linking the case service provider to the client).

EXAMPLE: In a Physician Health case the roles might be: subject ("client"), primary care physician, workplace monitor, drug screen supervisor, licensing authority representative, medical staff assosciation representative, etc. The "client" is linked directly to the case (civicrm_case.contact_id). The other people are related to the case through a Relationship of a given type. "Drug Screen Supervisor" would be a relationship type.

Note that we would have the flexibility to decide whether the Relationship should be used to link the person in the role to the Case or not. Relationships which persist outside of the case context would NOT have the case linkage. Spouse would definitely link client to their husband / wife without a case linkage. Primary Care physician might be related to the Case or not, based on whether this relationship persists beyond the scope of the Case.

Changes to Relationship

• Add an optional foreign key - case_id - to civicrm_relationship table.

NOTE: We will need to create relationships where the case_id and contact_id_a (client) are defined, but the service provider (client_id_b) is not yet assigned (NULL). The schema currently supports this, but there may be business rules in the code which need to be modified to allow it.

Activity (civicrm_activity)

Current schema (as of 2.1)

Changes to Activity Type

CiviCase will have lots of activity types and users will need to filter activity listings (and input of activity type) using categories. In order to accomplish this, we'll need to move Activity Types out of the current storage in civicrm_option_value (which doesn't support categorization). We'll use single recursive table model to allow storing hierarchical categorisation. In phase 1, we'll limit it programmatically to two levels (top level will be categories, second level will be actual types), but the model will allow more depth if need be, for future versions.

• New table - civicrm_category
  • id
  • label VARCHAR 255 - the displayed label for this category, can be modified / translated
  • name VARCHAR 255 - the internal name for this category, not editable for reserved records so we can use as the key for programmed behaviors
  • weight INT
  • description VARCHAR 255
  • is_active BOOLEAN
  • is_reserved BOOLEAN
  • parent_id INT FK to civicrm_category.id
  • component_id INT FK to civicrm_component.id

Changes to Activity

• Add the following columns:
  • medium INT implicit FK to civicrm_option_value (option_group = activity_medium). Used to record the "encounter" medium, e.g. Face-to-face, Inbound phone, Letter, Email, etc.
  • is_auto BOOLEAN, Set to true for activities which have been automatically inserted when Case is created. Records with is_auto TRUE will NOT be revisioned when edited by a user - but the flag will be set FALSE in post process. This gives us an easy way to tell which records have been altered by a user.
  • relationship_id INT Optional FK to civicrm_relationship table. Used to record the relationship that the source contact has with the "client". This is needed because service providers may fulfill multiple 'roles' in a case.

• Activity Type will be moved back to it's own table. Change activity_type_id to be a "real" foreign key to civicrm_activity_type.id

NOTE: Whenever an activity record is inserted, the date-time and ID of the contact who created each activity will be recorded to the civicrm_log table.

NOTE: Since the "reporter" (source) is always a contact record, the Activity form will need to include an easy way for the user to create a new contact record if that person hasn't been entered in the system yet AND assign them a relationship (case role) if needed. This can work something like the "Current Employer" field in contact editing (but with the additional ability to define a relationship).

Activity Target(s) (civicrm_activity_target)

The target is the contact(s) whom the activity is "about" or "regarding". For CiviCase activities, this will always be the client.

Activity Usage Example Case Manager meets with Parole Officer about Jane Doe (client): In this case, the Parole Officer is source_contact (reporter), Jane Doe is the target contact, and the case manager is the creator of the activity (created_by contact id).

Activity Assignment (civicrm_activity_assignment)

One or more contacts assigned to do the activity. Case managers might "assign" other staff to handle activities related to the case.