Proposed Schema Design for CRM-8151

Scenario :

#1. Send an email to the targets of all Scheduled activities of type
Evaluation 2 days before activity_date_time and repeat every day till 5
days after activity_date_time.

#2. Send an email to all registered participants of all type of event X two days before event start date.

#3. Send reminder to registered participants for Fall Fundraiser 3 days before event start date.

civicrm_action_mapping

	Columns	Type	DB Value #1	DB Value #2	DB Value #3
1	id	int(10)	1	2	3
2	entity	varchar(64)	civicrm_activity	civicrm_participant	civicrm_participant
3	entity_value	varchar(64)	activity_type	event_type	civicrm_event
4	entity_value_label	varchar(64)	Type	Type	Type
5	entity_status	varchar(64)	activity_status	civicrm_participant_status_type	civicrm_participant_status_type
6	entity_status_label	varchar(64)	Status	Status	Status
7	entity_date	varchar(64)	activity_date_time	event_start_date	event_start_date
6	entity_recipient	varchar(64)	activity_contacts	civicrm_participant_status_type	civicrm_participant_status_type

Regarding #6 above, we need to add a new option group

name	label	is_active	Comments
activity_contacts	Roles	1

which 'll have the following option values :

label
Activity Assignees
Activity Source
Activity Targets

civicrm_action_schedule

	Columns	Type	Value	Comments
1	id	int(10)
2	name	varchar(64)		(?? not sure if we need it )
3	title	varchar(64)
4	recipient	varchar(64)	option_value	option values that correspond to entity_recipient column of the above table
5	entity_value	varchar(64)	^A separated option_values	option values that correspond to entity_value column of the above table
6	entity_status	varchar(64)	^A separated option_values	option values that correspond to entity_status column of the above table
7	reminder_interval	varchar(10)
8	reminder_frequency	varchar(32)		enum/option value (need to take a call?)
9	reminder_action	varchar(64)	before/after
10	is_repeat	tinyint(4)
11	repetition_start_ frequency_unit	enum('day','week','month','year') /option value
12	repetition_start_ frequency_interval	int(10)
13	repetition_end_ frequency_unit	enum('day','week','month','year') /option value
14	repetition_end_ frequency_interval	int(10)
15	repetition_end_action	varchar(64)	before/after
16	is_active	tinyint(4)		to switch OFF/ON the 'Send an email option' internally
17	recipient_manual	varchar(255)		separated contact IDs
18	body_text	longtext
19	body_html	longtext
20	subject	varchar(128)
21	record_activity	tinyint(4)
22	mapping_id	int(10)		FK to civicrm_action_mapping
23	group_id	int(10)		FK to civicrm_group

Column	DB Value Scenario #1	Comments Scenario #1	DB Value Scenario #2	Comments Scenario #2	DB Value Scenario #3
name	equipment_form_ daily_reminder		event_reminder		fall_fundraiser_reminder
title	Equipment form, daily reminder		Event reminder		Fall Fundraiser reminder
recipient	3	option_value.value for option_group corresponding to entity_recipient = 'activity_contacts'	1	civicrm_participant_status_type.id for 'Registered'	1
entity_value	x	option value for activity type 'Evaluation'	3 (assuming its a Fundraiser)	option value for 'X' where option_group.name = 'event_type'	1	the only difference between #2 & #3 is that #2 points to option value for event_type & #3 points to id of the event table event.id = 1 (Fall Fundraiser) option_value.value = 3 (Fundraiser)
entity_status	1	option value for activity status 'Scheduled'	1	civicrm_participant_status_type.id for 'Registered'	1
reminder_interval	2		2		3
reminder_frequency	day		day		day
action_condition	before		before		before
is_repeat	1		0		0
repetition_start_ frequency_unit	day
repetition_start_ frequency_interval	1
repetition_end_ frequency_unit	day
repetition_end_ frequency_interval	5
repetition_end_action	after
is_active	1
recipient_manual
body_text	foo		baar
body_html	foo		baar
subject	some reminder		event alert		event specific reminder
record_activity	1		1		1
mapping_id	1		2		3
group_id

civicrm_action_log

Keeps a record of the action performed on an "entity" and when. This is used to determine when/if to redo the action based on the repetition clause

Name	Type	Comments	Example
id	int(10)	Primary Key - Auto Increment
entity_id	int(10)	id of the entity that the action was performed on. Pseudo - FK
entity_table	varchar(255)	name of the entity table for the above id	civicrm_activity, civicrm_participant
action_schedule_id	int(10)	FK to the action schedule that this action originated from
action_date_time	datetime	date time that the action was performed on
repetition_number	int(10)	Keeps track of the sequence number of this repetition