EmailHistory table modification

[LVerhulst4321]

I would like to add 2 fields to the existing EmailHistory table.
Field 1 would be the badgeid of the recipient of an email message. Proposed field name: email_to_badgeid
Field 2 would be the badgeid of the person sending out the email. Proposed field name: email_from_badgeid

Recording the actual badge ids in the table would allow some reports to be built to show which participants got messages sent to them, when they were sent, and who sent them. There don't seem to be any reports built that look at the EmailHistory table. I would be adding some reports as well.

I am also thinking about saving the email body text in the EmailHistory table too, but I am concerned about how much space this history would eventually take up. I am not entirely sure this concern is valid as I don't think most cons would send out so many email messages that it would be a problem.

What do you guys think about this idea?
I also want to make sure that no one else is working on this before I start touching this part of the code.

[LVerhulst4321]

Also, since it doesn't appear that the EmailHistory table is used anywhere besides in StaffSendEmailCompose.php, I would like to refactor it to match the PlanZ naming convention.

[lostcarpark]

First, I did not realise there was an EmailHistory table. I get the impression it's a write only table (i.e. there's nothing reading it back, but someone could run queries on it if needed).

I agree storing the badge_id of the sender and recipient would be useful.

One thing to consider would be having a two level structure, with one table storing "batch" level information such as sender, "From", "CC", Subject. It might be worth storing the message before replacement here.

The second table wold store the recipient level details such as recipient badge_id, "To", and potentially the message body. Perhaps there could be a flag in the config to specify whether to store bodies or not (you'd still have a column for them on the table, but an empty text field only adds a few bytes, which is more than offset by the fields that aren't being stored for every message).

This would reduce the amount of storage since fields common to the whole batch aren't duplicated for every message. However, it would also make it easier to see which messages were sent in a batch, which I think can only be inferred by the datetime at present.

In the longer term, I would like to have a single participant view with tabs for participant details, availability, survey and their schedule, Adding a tab listing all emails sent to them could be very useful.

And if it was being split into two tables, I think that would seal the case for adopting new naming convention.

[LVerhulst4321]

Ooo. Interesting idea you had to split it into 2 tables. I can see the usefulness of that. New tables being named email_history_from and email_history_to maybe?

A flag in the config file to indicate whether to store the message body is a good idea.

I'll work on this tonight. It's a useful thing, I think.

[bcholmes]

Sounds cool to me!

[lostcarpark]

Does the word "history" tell us anything about the table's function?

This is just my personal style, but I would be inclined to call them something like "sent_email_group" and "sent_email_message".

Could I request a flag field called something like "is_bulk" on the header table (whatever the table on the "one" side of the 1-to-many relationship is). I intend to have the participant workflow send automatic emails sent when a participant's status changes. It would be useful for these to be logged, and to distinguish between individual messages and bulk messages.

[LVerhulst4321]

Yes, the word "history" tells us the table's function. It is a history of the emails that have been sent out and by whom and when. I definitely want to keep the word history in the table name.
I should say that it's a history from only this option at this time:

Note that the emails sent out for setting up a new account or the forgot password function do not get recorded in this table. Would it be useful to save these as well? I don't think so personally.

I can see the usefulness of a flag field like "is_bulk". Would it be useful to also have a field to indicate what option was used to send out the email? If this "history" table is to start holding info from multiple email functions, then that would be useful. I think I just convinced myself to include this new field.

[lostcarpark]

That's fine. I only meant that as my personal preference for naming style.

I would agree that there's no need to track account setup/password reset emails. If you did, you'd probably need to obfuscate links or other sensitive information in the email.

[LVerhulst4321]

James, would you happen to have an EmailHistory table that has data in it that you are willing to share? I wanted to do some testing on splitting a table that already has data, but we've never used the email functionality for Capricon, so I don't have anything.
Considering that the table holds email addresses, those should be obfuscated before sharing.

[lostcarpark]

No problem. I've randomised the email addresses. I've uploaded to a branch of my fork:
https://github.com/lostcarpark/PlanZ/blob/EmailHistoryExample/Install/EmailHistoryExample.sql
I don't think there's any need for a pull request, is there?

[LVerhulst4321]

Thanks! No, no need for a pull request.

I have copied off the table. Feel free to remove it now if you want.

[lostcarpark]

By the way, it does appear that password resets are logged, at least in the version of Zambia this con is using.

[LVerhulst4321]

Huh. I can't find any other reference to the EmailHistory table besides the one that's used by the Staff option to Send Email. I double checked through the Zambia code used by the last Capricon (in Feb).

Checking more....

Ah ha! I found that the Discon3 branch of Zambia has a reference to the table in this file: ForgotPasswordSubmit.php
That routine has now had a bunch of updates from BC and I think it was updated from the master Zambia branch.

So, should the history logging be added back. Hmmm... Well, if Discon3 found it important enough to add, maybe that should be a point to add it back in.

[lostcarpark]

Well spotted. My starting point was Discon3.
I think there would be no harm adding that into PlanZ. I don't think there's any benefit to saving the message body for password reset emails, which avoids the extra complexity of obfuscating details.