Messaging within OpenMRS

Message/Alert Data Model (first pass)

We need to support something like:

• send(Message, Collection<Recipient>)
• getUnreadMessageCount(User) // if user is using an external email server for messaging, then check that
• getUnreadMessages(User) // only get messages from internal messaging system
• getUnactionedAlertCount(User) // logic for this may be complicated: find any alerts for any patient that this user is watching
• getUnactionedAlerts(User)

Different ways of delivering messages:

• In-system messages
  • Messages that live in the OpenMRS system, and whose read/unread is tracked there.
  • All Alerts are delivered this way, possibly with email notification as well
    • Read/Unread is not interesting for alerts. Actioned/Unactioned is.
• Trusted Email
  • Administrator should be able to designate some email servers as trusted, meaning that end-to-end encryption is guaranteed between messages from the system to the user. (e.g. a server on the intranet that allows webmail access over SSL only)
• Untrusted Email

message_template

Holds information about the template used to dynamically generate messages. The template text field will probably contain variable substitution parameters that will need to be evaluated by the template engine.

 CREATE TABLE message_template (
   message_template_id int(11) NOT NULL auto_increment,
   template longtext NOT NULL
 );

message

 CREATE TABLE message (
   message_id int(11) NOT NULL auto_increment,
   message_template_id int(11) NOT NULL,
   created_date datetime NOT NULL default '00000000 00:00:00',
   patient_id int(11),                 Patient that the message pertains to
   encounter_id,
   obs_id
 );

message_sent

Keeps track of the message that is being sent, but only saves information about the message being sent (not about each recipient).

sender_id might be a user, event, etc (can be null) sender_type_id references a table that does not exist yet action_status refers to the action taken (viewed, skipped, etc)

 CREATE TABLE message_sent (
   message_sent_id int(11) NOT NULL auto_increment,
   message_id int(11) NOT NULL,
   body longtext NOT NULL,
   subject text NOT NULL,
   message_sent_type_id int(11) NOT NULL,
   sender_id int(11),                         
   sender_type_id int(11),                    
   action_status <datatype>?                  
 );

Why don't we just combine message and message_sent into one table. What's the point in separating them? Djazayeri 01:03, 23 January 2006 (US Eastern Standard Time)

message_sent_type

 CREATE TABLE message_sent_type (
   message_sent_type_id int(11) NOT NULL auto_increment,
   name text NOT NULL,
   description text NOT NULL
 );

message_sent_recipient

Actual message recipients. After the list of recipients is pulled via the distribution list tables, each message is sent and receipt is stored in this table. This allows us to keep track of who has read or taken action on a particular message/alert.

 CREATE TABLE message_sent_recipient (
   distribution_list_entry_id int(11) NOT NULL auto_increment,
   message_sent_id int(11) NOT NULL,
   user_id int(11) NOT NULL,
   date_read datetime NOT NULL default '00000000 00:00:00',
   date_sent datetime NOT NULL default '00000000 00:00:00',
   sent_flag tinyint NOT NULL default 0,
   internal_flag tinyint NOT NULL default 0 -- i.e. is this message sent using the system's own messaging system, as opposed to external email.
 );

message_sent_list

 CREATE TABLE message_sent_list (
   message_sent_list_id int(11) NOT NULL auto_increment,
   message_sent_id int(11),
   distribution_list_id int(11) 
 );

distribution_list

 CREATE TABLE distribution_list (
   distribution_list_id int(11) NOT NULL auto_increment,
   name varchar(100) NOT NULL
 );

distribution_list_entry

 CREATE TABLE distribution_list_entry (
   distribution_list_entry_id int(11) NOT NULL auto_increment,
   distribution_list_id int(11) NOT NULL,
   user_id int(11),
   user_group_id int(11),
   relationship_type_id int(11)
 );

--Jmiranda 14:10, 21 January 2006 (US Eastern Standard Time) Edited: Djazayeri 01:03, 23 January 2006 (US Eastern Standard Time)

More of Burke's blabber

Justin,

I think we need some more discussion on what our goals are for messaging. I understand that here we're modeling just "Messages" and "Alerts" (i.e., system-wide announcements and software event propagation will be handled separately).

Among messages/alerts, I see only two basic types of messages:

1. Messages that must be confirmed
   • This includes simply "confirm message read" or "force selection of an action" — i.e., "read" can be seen and treated as an action, right?
2. Message that need not be confirmed
   • I think we would just as soon send these out to an SMTP server of the administrator's choosing. I suppose you would like to include these so we are not dependent on an outside application, right? If so, could we design the internal "mail" system as an independent module that could serve as a poor man's e-mail delivery system? That way you could choose between internal vs. external mail apps? Alerts (messages needing confirmation) would always be delivered internally (whether or not a duplicate message was generated to be sent via mail).

Would it be sufficient to determine whether a message needed confirmation by message type? And how about if we use user groups instead of creating distribution lists?

message       /* a record for each message sent that we want to track (all alerts +/- messages) */
  message_id
  message_type
  from
  to          /* string for easy display */
  subject
  message     /* completed message (i.e., template with values filled) */

user_message  /* used to map alerts +/- messages to recipients */
  message_id
  user_id
  status      /* read, unread, action taken, etc. */

message_type
  message_type_id
  name
  description
  priority    /* alert vs. informational message */
  actions     /* for alerts: some string that can easily be parsed to determine actions choices to offer */
  template    /* template for generating message */  

With an API something like:

/* for sending out messages (both alerts and unconfirmed messages) */
void sendMessage(User[] to, String subject, MessageType messageType, Object[] params)

/* CRUD operations */
create/get/update/deleteMessageType(...)

/* to get alerts (messages needing confirmation) and take action(s) */
Message[] getUnconfirmedAlerts(User who)
void confirmAlert(User who, Message message, String action)

/* for interacting with mail system -- whether internal or external */
int getUnreadMessageCount(User who)
String getMessageSystemUrl(User who)

I'm pushing to separate "Alerts" (messages that must be confirmed or acted upon) vs. "Messages" that are for informational purposes and confirmation is not needed.

• Alerts would be displayed every time the user logged into the system.
• Messages would be rendered as a teaser ("you've got mail") and clicking on that link would take you to the "mail" system, which might be our internal mail display module or a full-fledged e-mail client set up by the administrator. While you might want to create your own e-mail module, I would argue that a simple module with an inbox for display and delete functionality would do the job; anybody wanting more functionality could point the API to an external e-mail client instead.

-Burke 13:21, 23 January 2006 (US Eastern Standard Time)

Ben's Late Input

Burke, Paul and I talked today about needing to get feedback to our data assistants. This could be easily done by creating an Alert (as defined in Burke's last blabber). This is in-system, no outside email needed. The only user response allowed for an alert is "ok".

An alert would not be too complicated to create:

 CREATE TABLE `alert` (
  `alert_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `role` varchar(50) default NULL,
  `text` varchar(512) NOT NULL,
  `date_to_expire` datetime NOT NULL default '0000-00-00 00:00:00',
  `creator` int(11) NOT NULL,
  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed_by` int(11) default NULL,
  `date_changed` datetime default NULL,
  
  PRIMARY KEY (`alert_id`),
  
  KEY `alert_creator` (`creator`),
  KEY `alert_assigned_to_user` (`user_id`),
  KEY `alert_assigned_to_role` (`role`),
  KEY `user_who_changed_alert` (`changed_by`),
  
  CONSTRAINT `alert_creator` FOREIGN KEY (`creator`) REFERENCES `users` (`user_id`),
  CONSTRAINT `alert_assigned_to_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `alert_assigned_to_role` FOREIGN KEY (`role`) REFERENCES `role` (`role`),
  CONSTRAINT `user_who_changed_alert` FOREIGN KEY (`changed_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=;

  CREATE TABLE `alert_read` (
  `alert_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `date_read` datetime NOT NULL default '0000-00-00 00:00:00',
  
  PRIMARY KEY (`alert_id`, `user_id`),
  
  CONSTRAINT `alert_read` FOREIGN KEY (`alert_id`) REFERENCES `alert` (`alert_id`),
  CONSTRAINT `alert_read_by_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=;

An alert can be sent to one user with `alert.user_id`, or to all users with a certain role `alert.role`. I realize you may want to sent to an arbitrary group of users, but tough luck, that will come in version 2 (or send a message).

When you log in all non-voided/non-expired/non-read alerts that you match will display on the top of every page (in an overflow div, so as not to take up the entire page if there are a lot of them). In order for the alerts to disappear you must click the box next to them -- essentially saying you read the alert. (Claiming you read the alert populates the `alert_read` table with that `alert_id` and your `user_id`.)

Justin's response

I like your idea about the overflow div. We were discussing something similar today (04/12/2006) with regards to our lab data entry system. We think that it will be important to notify doctors, data entry clerks, etc immediately when a condition in the system causes an alert to be raised. This includes the following actions:

1. Send a message to the user's browser (via ajax) as you suggested
2. Send a message to phone(s), pager(s), or email(s) (via the message service) based on the user's preferences.

Obviously, the phone/pager/email message will contain non-specific information (i.e. "You have an alert about a patient with a low CD4 count"). The doctor will need to log into the system in order to view specific information about the alert.

Here's a use case:

1. Lab technician enters a lab result into the system
2. System stores lab result
3. System runs MLMs over lab result
4. System raises alert
5. System saves alert
6. System sends message to appropriate parties

--Jmiranda 21:46, 12 April 2006 (EDT)

Darius's response

The reason I wanted to allow messages to be sent to groups of users is because I thought we had User-Group-Role in the data model, as we talked about at AMIA, rather than just User-Role. I see that's not in the data model yet, so we have no problem waiting on that.

Djazayeri 23:17, 12 April 2006 (EDT)

Ben's reponse

Theres where you're in luck. Roles are grouped into roles, so can still send a note to, say, all Providers easily. What you can't do is send something to Data Assistant #3, and Clinician #2 and #6.

-Ben 23:21, 12 April 2006 (EDT)

• This page was last modified 19:21, 13 April 2006.
• This page has been accessed 1,342 times.
• License
• About OpenMRS^®