Forum OpenACS Development: Re: Mail Tracking

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:

We have a single queue (table acs_mail_lite_queue) where all messages that are to be sent out are stored.
We have a scheduled proc that sends out the messages for us (like acs_mail_lite::sweeper).
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

https://openacs.org/doc/acs-mail/openacs-mail.html
https://openacs.org/doc/current/tutorial-notifications.html
https://openacs.org/forums/message-view?message_id=198873