Forum OpenACS Development: Mail Tracking

Collapse
Posted by Malte Sussdorff on
We have the need for keeping track of E-Mails a user has recieved from the system. One of the easiest solutions would be to add an E-Mail Tracking table to acs-mail-lite and store every email send out by acs-mail-lite in this with an index on the user_id. Sadly this will create a lot of data, most likely even redundant one, if we take notifications for example.

So my next best idea was to keep the table around but also offer the option not to store the content of the mail, but where the mail came from (e.g. notifications) and make sure that notifications could recreate the mail for display.

We will need this functionality for mails send from:

- Forums
- Projekt Manager
- File-Storage
- Events

(all using notifications)

- acs-subsite (registration e-mails)
- mailinglist manager
- contacts and/or P/O CRM system

Does anyone already have a solution? If not, what solution do you prefer so I can write a TIP (as this I guess needs tipping) ?

Collapse
2: Re: Mail Tracking (response to 1)
Posted by Torben Brosten on
The ecommerce package has a solution as part of the customer-service section[1]. It really needs to be separated out into a kind of customer-management-services package.

Also, someone mentioned they are working on an intra-site webmail service, where notifications are tracked and viewed between users within OpenACS without using standard email/sendmail services. WebCT has something like this for user-user and forum-user posts. iirc, the RFC was around the time the contacts package was being discussed --can't find a reference to it right now.

1. "Sending Email to Customers" in https://openacs.org/doc/openacs-5-1/ecommerce/ecommerce-customer-service

Collapse
4: Re: Mail Tracking (response to 1)
Posted by Richard Hamilton on
Yes, that was me. The module concerned was the one that I referred to in my email to Malte the other day. I plan to work on a written requirements document in the next few days. Just very busy at present.

I suggest that we consider having a system messages table where any system originated emails that are to be sent out by any package are entered once and registered with the central 'system message' generating module. That way the storage of which user has rewceived which system message is a simple matter of storing the object_id of the message against the user_id.

This will hook in nicely with the 'admin tasks' and contacts related system messages as well as other workflow related task messages. Any other thoughts?

Regards
Richard

Collapse
5: Re: Mail Tracking (response to 1)
Posted by Malte Sussdorff on
This sounds good, though we have to think about messages that are send personalized (e.g. mailinglist manager). For notifications your approach definitely will work though (I think... 😊).
Collapse
6: Re: Mail Tracking (response to 1)
Posted by Nima Mazloumi on

Who is sending emails?

There are two ways how an email is sent to a user.

  • user driven
  • system/package driven
Each case has to be dealt separately - specially when the multiplicity is 1:m as it is the case with notifications and mailing lists.

How are email send in the system?

Also we need to know how emails are sent out from oacs. At present there are several packages and ways how emails are sent in oacs[see below]. Some of them even seem to track sent emails.
  • acs-mail-lite
  • acs-mail
  • bulk-mail
  • spam
  • mailing-lists
  • form-to-mail
  • webmail
  • webmail-system
  • notifications
  • ns_sendmail
  • stored pl/pgsql procedures
  • ...(what else?)

ACS Mail Lite

Here the parameters we should store in our 'acs-mail-log' table:

  • message_id - the message id from acs mail lite
  • recipient_id - receipients user_id
  • sender_id - senders user_id
  • subject - subject of the email
  • body - actual email body
  • package_id - sending package
  • send_date - when the email was sent
Some questions:
  • Should we create a new acs object for our logged messages?
  • Should we store the content type? What if the email contained attachments? Should they be stored as well? What if the message was a multipart message? One solution would be to store the raw email. This would allow to reconstruct everything and to store the whole content.
  • ACS Mail Lite only stores to_addr and from_addr. Thus there could be persons who sent or received an email who are not registered at the system. What shall we do with them?
  • We don't really know wether the email ever reached the user. Any idea on how to solve that problem? Maybe the package 'Email Handler' would be solution.

Notification and Mailing lists

These packages cause a lot of traffic due to the fact that we have a 1:m relation. From what I can see the best would be to store the email only once and to store the corresponding notification_id or mailing list id. With this solution we could reconstruct who received an email.

The problem is that notification subscriptions and mailing lists are volatile. Best would be therefore if we could support versioning for them. This would allow us to figure out who was listed in a list or had a subscription for a given object at the time the email was sent.

When should we log and how?

Assuming that we have a single package (for instance acs-mail-lite) that does all the message sending, a solution could look like this:
  1. We have a single queue (table acs_mail_lite_queue) where all messages that are to be sent out are stored.
  2. We have a scheduled proc that sends out the messages for us (like acs_mail_lite::sweeper).
  3. We have an event listener that logs each email after it was sent out. This was our mail-tracking package could be an optional package. My suggestion would be to simply define a trigger that logs (insert) messages in our acs-mail-log table when every time a message was sent (delete) from the queue. This could look like this:
    CREATE TRIGGER acs_mail_lite_queue_tr AFTER DELETE ON acs_mail_lite_queue
    FOR EACH ROW EXECUTE PROCEDURE acs_mail_log();
    

Any comments?

References

  1. https://openacs.org/doc/acs-mail/openacs-mail.html
  2. https://openacs.org/doc/current/tutorial-notifications.html
  3. https://openacs.org/forums/message-view?message_id=198873
Collapse
7: Re: Mail Tracking (response to 1)
Posted by Nima Mazloumi on

Mailing List

Sender and Recepient can be anyone. Even non-registered users. But if a mailing list contains users who are not registered at the system a new account is created automatically. So storing the recepient_id is alright. From what I see this is not necessarily true for the sender. So storing the sender_id might break. Should we store the senders email address then as well?

Mailing list has some wrappers for acs-mail-lite but still uses in to finally send emails, so we should be alright with that package.

ML has also some log functionalities: ml_email_log-table stores the senders user_id, the mail_job_id and when the job was sent as send_date while the table ml_mail_jobs stores the complete job content including attributes like mails_sent, mails_bounced. To reconstruct the list of users who actually received the email the table ml_mailing_list_user_map exists. But no versioning is supported. Changing the list will return bad results from a history perspective.

We need to extract the mail specific api and tracking from mailing list into more general packages.

ACS Mail Lite - revisited

The acs_mail_lite_queue table is not very well designed. The attribute to_addr stores a tcl list of the format

email email_address name namefromdb ser_id user_id_if_present_or_empty_string
. This means in the worst case that we don't know who the receiver is.

My suggestion to solve the logging using a trigger makes this a bit tricky since I have to use regular expressions to extract the user_id from to_addr. Here a solution:

v_recepient_id := substring (to_addr from \'user_id ([0-9]+)\');
Now the question is what we do if null is returned. I would suggest we simply raise a notice and fall through without logging that email. Any comments?

Service Contract/ Call Back versus Trigger

Due to the above problems another solution would be to define a service contract or call back instead that is called from each package that wants to log messages sent out. This solution would allow a more application driven compared to the above database driven solution but would also mean that each packages mentioned in my former post need to be touched.

Any comments?

Collapse
8: Re: Mail Tracking (response to 1)
Posted by Nima Mazloumi on
<h3>Bulk Mail</h3>

Bulk mail allows us to send an email to a set of users. The main proc that does the sending is bulk_mail::new. Bulk mail has almost no user interface except for the little info on sent mails. So UI was added to in in dotLRN dotlrn-bm ads a list of the bulk mail history while dotlrn offers a UI to actually send emails to defined parties (groups and persons).

bulk_mail::new proc is very generic. You can pass a subject and must pass a from_addr and a message but the query switch can change them all. This SQL query is used to dynamically manipulate subject and message. The value of a returned column (i.e. 'foo') replaces ever occurrence of '{foo}'. The query must return an email column and optionally the columns from_addr, reply_to, subject and message.

Bulk mail is an acs object and creates new objects using the proc

package_instantiate_object -extra_vars $extra_vars "bulk_mail_message"
which calls the stored procedure bulk_mail__new. The extra_vars variable is a ns_set which is accessed by bulk_mail::sweeper directly which luckily uses acs_mail_lite::send to send the emails. Thus they are also stored in acs_mail_lite_queue.

Bulk mail also has logging functionalities. All emails that are to be sent out are stored in the table bulk_mail_messages. Once the bulk mail is sent the flag sent_p is set to 1.

Like mailing list bulk mail can have any email address as the sender (from_addr). Therefore storing the sender's email and not a user id seems better. On the other hand the receivers ids are well known due to the fact that they are coming from the SQL query-attribute passed to bulk_mail::new.

Now bulk mail iterates over all receivers and calls acs mail lite to send the email. Therefore we have many entries that contain almost the same content. Almost because each mail is customized depending on the interpolation that takes place. If we want to store the email once we will loose the customized content depending on the SQL query and we need to change bulk mail not to call acs mail lite for each recepient. But from what I can see acs mail lite does not support a list of receivers anyway.

<h3>ACS Mail Lite - revisited (2)</h3>

ACS mail lite supports sending emails immediately with the private proc acs_mail_lite::send_immediately. This proc only queues an email if acs_mail_lite::deliver_mail fails somehow. So if we have packages that uses acs_mail_lite::send_immediately directly we have no way to find out.

Since the sweeper runs every minute I don't see why we should deal acs_mail_lite::send_immediately differently to acs_mail_lite::send. We should simply insert that email to the queue and we are set. Any comments?

Collapse
9: Re: Mail Tracking (response to 1)
Posted by Nima Mazloumi on
<h3>Spam</h3>

The spam package is depending on the deprecated acs-content package that has no tcl API or www pages and was designed to support site-wide search. Since Dirk, Dave and Co. are working on a new one I simply removed that dependency to install it. Spam is from ACS 4 times and does not use acs templating. It is also not i18-nized.

The package uses the acs-mail package to send out emails and is a singleton. Applications use this package by passing an object_id and a sql_query similar to bulk mail which returns a list of party_ids. For security reasons the two variables are set using ad_set_client_property. This package is similar to bulk mail. While bulk-mail offers a proc to create a mail this package is UI driven. You add a link and spam takes care of the rest.

Some problems I had testing this package:

  • spam-form-body.adp requires a noquote for date_widget and time_widget
  • The stored procedure spam__new uses a non-existent acs_mail_body__new function so I was not able to test this.

Messages are stored in the tables acs_messages and spam_messages. The scheduled proc spam_sweeper inserts approved messages into the acs_mail_queue_outgoing - acs-mail takes care of the rest.

Since bulk mail supports most of what this package does my suggestion is to add extend bulk mail with the UI stuff spam has and deprecate the spam package.

Any comments?

Collapse
10: Re: Mail Tracking (response to 1)
Posted by Nima Mazloumi on
<h3>Form 2 Mail</h3>

This package works as an include and is a singleton. A good documentation is available.

The admin interface allows the definition a form. The strange thing is that the senders email is required as well. Only the fields subject and body are provided. If extra form fields are needed then a TCL file with a special ad_form call is required. To use the form you add

< include src="/packages/form-to-mail/www/form" form_name="Test Form" >
to your ADP-page. The include statement can also have an optional parameter extra_form_file which references a the above mentioned TCL file. No relative reference seems to be supported.

The package has no support for user driven form definition. A solution would be to either define form field types and corresponding display types that can be added dynamically or using assessment right away. Another approach might be using dynamic types. Any ideas?

Form to Mail uses ns_sendmail to send out the form. We need to fix that to be able to integrate with acs-mail-lite.

In oder to try out the package you need to fix form.tcl (quick hack):

#set action "${closest_mount}form-proc"
set action "/ftm/form-proc"

Collapse
11: Re: Mail Tracking (response to 1)
Posted by Nima Mazloumi on
<h3>Notifications</h3>

The notification package is well documented. Any package can be notification enabled. Thus we can have systems with incredible traffic. Since the notification message is identical for all suscribers logging a single message seems the best.

The problem is that a generic tracking package won't be able to resolve the actual subscribers later on. Also since notification requests can be deleted we don't have the means to reconstruct who was a subscriber at the time the email was sent out. To fix this is simple. Instead of deleting entries from notification_requests we could add two more columns like creation_time and deactivation_time or something. Adding a new request for the same object would deactivate an existing project as does removing a subscription to an object. What do you think?

I prefer to log every single email in favour to a more generic mail-tracking package. We can find ways to archive old logs to prevent the logging table from increasing to much.

Like bulk-mail, notifications doesn't provide a package_id to acs_mail_lite_queue. We need to fix that. I would suggest that we take the package_id of the package that caused the notification.

<h3>ACS Mail Lite (revisited 3)</h3>

The columns to_addr, from_addr and body in acs_mail_lite_queue create strong restrictions on reuse in favor of simplicity:

  • to_addr - stores the sender's email instead of a valid user_id. This allows any email address as the sender. Why should we allow that?
  • from_addr - stores a TCL list that contains the recipients name, email and user id. Shouldn't the user_id suffice?
  • body can literally contain anything that is text. While bulk-mail and mailing-lists simply contain the message text notifications stores the raw email body. Wouldn't a mapping table form content to email be better?
Collapse
12: Re: Mail Tracking (response to 1)
Posted by Guan Yang on

to_addr - stores the sender's email instead of a valid user_id. This allows any email address as the sender. Why should we allow that? Why shouldn't we?

from_addr - stores a TCL list that contains the recipients name, email and user id. Shouldn't the user_id suffice? This relates to the previous question. My impression was that acs-mail-lite can be used for sending any email, not necessarily to or from registered users. You'll have lots of instances where you are sending email to people who are not necessarily users on the system; a banal example would be a "Send this to your friend" feature where a user can type in an email address and something will be sent to that email.

Collapse
13: Re: Mail Tracking (response to 1)
Posted by Nima Mazloumi on
That's true. That's why mailing list creates a new account for unregistered users on the fly which solves some problems but could even break some privacy laws. A person should give his/her approval before an account is created, right?

But how should we deal with messages sent out to unregistered?
How should we deal with messages from unregistered users?

Simply store the email instead?

Collapse
14: Re: Re: Mail Tracking (response to 13)
Posted by Andrew Piskorski on
Nima, creating a party_id record in OpenACS is not the same thing as "creating a new account for an unregistered user"! So which does the acs-mail-lite package actually do?

Whether creating a new party_id is desirable or not probably depends on the use case. I have never looked at the acs-mail-lite package at all, but my instinct is that creating party_id's on the fly should be possible, but not required. My reasoning is that acs-mail-lite is basically low level infrastructure, not an application; let the actual application decide which behavior it wants.