[OPEN-ILS-DEV] Normalization of e-mail addresses and phone numbers in patron data

[Thomas Berezansky]

I have a number of comments here. In no particular order.

For phone numbers, and email addresses I guess, an "active" flag might  
be nice to allow for no loss of other information but still turning  
the entry "off".

For the usr_phone_type, assuming time windows are added, the "default"  
window for that type might be a good idea to add there.

On a slightly different note there, another thing discussed was how to  
share addresses between patrons (I personally feel that is broken  
right now). Perhaps we should consider the same thing for phone  
numbers and/or email addresses?

A different thought on the SMS front might be to, if SMS is going to  
only be done via emails, allow flagging an email address as SMS,  
rather than a phone number. My SMS gateway address looks nothing like  
a phone number right now, for example (by my own choice with my  
provider), but it does look like an email address. That would reduce  
the number of possible issues with validation, I think.

Only slightly related, we have noticed that by default A/T email  
notifications have no clue how to validate if they should be firing,  
but just do so. Hold available notifications don't check if email  
notification is turned on for the hold, for example, and they all fire  
even when a patron has no email address. In the process of fixing some  
of that and implementing the proposed changes a mapping table of email  
-> notice type may be useful, so that different types of notices can  
go to different emails. And if the email is flagged as SMS a "short"  
form of the notice could be generated instead (within the triggers,  
anyway).

As for the "valid" boolean on emails defaulting to *true*, I disagree.  
It should default to *false* (except possibly on upgrade script  
execution), be reset to false whenever someone edits it, and an A/T  
event should fire on create/edit to say "validate my email". THAT  
process should set it to "true". All other A/T events should ignore  
emails set to not be valid. Maybe send a very short code you need to  
enter into the opac for SMS flagged addresses?

Thomas Berezansky
Merrimack Valley Library Consortium


Quoting Lebbeous Fogle-Weekley <lebbeous at esilibrary.com>:

> This is something I have been planning to undertake.  In light of a  
> conversation that took place in #evergreen about doing new things  
> with actor.usr_address, in which email addresses and phone numbers  
> were also at mentioned, I think I'd better share my plans and try to  
> get on the same page with anyone else who's going to make changes in  
> that neighborhood.
>
> The actor.usr table has four columns which I would like to break out  
> into three new tables.
>
> This would give us the ability to add metadata to phone numbers and  
> email addresses (I'm particularly interested in a "validity"  
> property for them) without bloating the actor.usr table.  We'd also  
> get the ability to have multiple email addresses for patrons, and  
> more free organization of phone numbers for patrons (instead of  
> having a hard three types: day, evening, other).
>
> The email column should be replaced with one new table  
> (actor.usr_email) and a set of relationships, and the day_phone,  
> evening_phone and other_phone columns should be replaced with a  
> second table (actor.usr_phone) and set of relationships.
>
> The third table (actor.usr_phone_type) is the target of a particular  
> foreign key column in actor.usr_phone.
>
> New table: actor.usr_email
>
> id, a primary key
> usr, a foreign key referring to a row in actor.usr
> address, a text field, not nullable
> label, a text field, nullable
> valid, a boolean defaulting to true
> notify, a boolean defaulting to false
>
> Plan: You might imagine I'd eliminate the email field from the  
> actor.usr table now, but I actually prefer to keep it, but change it  
> into a foreign key pointing back at actor.usr_email.  This creates a  
> set of circular references, true, but the purpose of the one  
> pointing from actor.usr to actor.usr_email is to define which of  
> potentially many rows in actor.usr_email should be considered  
> primary to the user.  This is the same idea behind the card column  
> of actor.usr the way it works now.
>
> To consider: We may still wish to have a constraint on the foreign  
> key represented in the actor.usr.email field, such that it cannot  
> refer to a row in actor.usr_email with a different usr value.
>
> To consider: The plan above precludes the possibility of keeping the  
> email field around as a virtual field in the IDL, stuffing it in  
> middle layer methods so that some interfaces can keep using it  
> without changes.  To have such a virtual field now, we'd need to  
> give the actor.usr -> actor.usr_email linking column a different  
> name (such as primary_email).
>
> New table: actor.usr_phone
>
> id, a primary key
> usr, a foreign key referring to a row in actor.usr
> phone_type, a foreign key referring to a row in actor.usr_phone_type
> label, a text field, nullable
> number, a text field, not nullable
> valid, a boolean field, default true
> notify_voice, a boolean field, default false
> notify_sms, a boolean field, default false
>
> To consider: Phone numbers could, and maybe should, have time  
> windows for notifications associated with them.  There could be  
> defaults based on whether a phone number originally came from  
> day_phone or evening_phone.  More to discuss.
>
> New table: actor.usr_phone_type
>
> code, a primary key
> label, a text field with internationalization, not nullable
>
> To consider: One could argue that actor.usr_phone_type is not  
> necessary at all, and I could be so persuaded, but I think it's the  
> logical way to preserve distinctions among the existing day_phone,  
> evening_phone and other_phone fields.
>
> I'm not sure we need a "primary" phone for each user in the same way  
> that we need a "primary" email address.  For that reason I'm not  
> specifying a foreign key on actor.usr that refers to actor.usr_phone  
> to indicate a "primary" or otherwise special phone number.  We can  
> revisit this point if somebody can point out why we would, in fact,  
> need a primary phone number.  Otherwise, the existing day_phone,  
> evening_phone and other_phone fields in the IDL can be redefined as  
> virtual, and helpfully stuffed by middle layer methods when possible.
>
> Middle layer changes
>
> Fortunately the Fieldmapper class for actor.usr does not have the  
> pcrud controller, so we don't have to go out and find code using  
> pcrud to fetch users and teach such code any new tricks.  Within  
> OpenILS::Application::Actor, we can change the subroutine  
> flesh_user() to 1) flesh the new email and phone objects by their  
> has_many IDL links, and 2) stuff any virtual fields we're going to  
> use with the best-fit data from the new tables. E.g.,  
> $user->day_phone($user->phones()->[$some_element_chosen_deterministically]).
>
> Ideally that will make many interfaces that retrieve user data able  
> to continue along as if nothing has changed, until there is a  
> particular need to let those interfaces know anything has changed.
>
> Testing should tease out other areas of the middle layer where  
> changes will need to be made. flesh_user() is certainly not the only  
> subroutine that handles user objects, and other code will be  
> affected by the database schema changes.
>
> User Editor changes
>
> We will need to adjust the current User Editor to deal with these  
> schema changes, naturally, and we should particularly remember to  
> make sure that the toggle for the validity of an e-mail address or  
> phone number is sensibly placed and functional.
>
> Notifications
>
> All action trigger event definitions with the SendEmail reactor will  
> need updated to get the complete set of valid and notifiable email  
> addresses per user.  A TT helper method may be appropriate.
>
> All action trigger event definitions for telephone notices will need  
> similar updating for phone numbers.
>
> I have no designs for changing the way ahr.notify_phone is used, for now.
>
> OPAC
>
> "My Account" in the Template Toolkit OPAC should allow patrons to  
> edit their email addresses and mark their validity, but it should  
> not allow them to edit their phone numbers (sometimes you have to  
> send people to collections, after all).
>
> Possibly in the future it could offer patrons the ability to add new  
> phone numbers for notifications (maybe SMS numbers should work this  
> way, and we bring them in out of usr_setting land? Not yet sure  
> whether that would actually be an improvement or if it just "seems"  
> cleaner), but we do not want patrons to be able to edit their  
> already known phone numbers (remember, these may be used in  
> collections).
>
> Other concerns
>
> To be clear, I'm not proposing anything that will automatically  
> determine the validity of patron email addresses or phone numbers,  
> but that's not to say that can't come later.
>
> -- 
> Lebbeous Fogle-Weekley
>  | Software Developer
>  | Equinox Software, Inc. / Your Library's Guide to Open Source
>  | phone:  1-877-OPEN-ILS (673-6457)
>  | email:  lebbeous at esilibrary.com
>  | web:  http://www.esilibrary.com
>