Profile artefacts sometimes out of sync with "usr" table fields

[Aaron Wells]

There are five profile fields, that are replicated into columns in the usr table, and artefacts in the artefact table. On the PHP side of things four of them use the "ArtefactTypeCachedProfileField" class:

1. firstname
2. lastname
3. preferredname
4. studentid

The fifth one is the "email" field, which is a little bit different because a user can have multiple email addresses. Each of these is stored as an artefact, and their "primary" email address (the one we actually send emails to) is also replicated in the usr.email field. We also have an additional table, "artefact_internal_profile_email", which has additional data about each email address.

The problem is, in existing databases some of this data is out of sync. Sometimes we find bugs that have caused this (for instance Bug 1630753 and Bug 1630764). Other times, we can't replicate the issue at all.

We could resolve this issue by normalizing the data. That is, keep it in only *one* place in the database, either the usr table or the artefact table. For performance reasons, it would probably be better to "de-artefact" them, because these fields are all frequently accessed by, for instance, the display_name() function. Removing them from the "usr" table would require us to run database joins against the artefact table, which is often huge and could slow things down. In comparison, removing them as artefacts would just complicate the Elasticsearch plugin and the User Profile block a little bit.

[Aaron Wells (u-aaronw)]

Another possibility, as a kind of bandage over the problem, would be to set up a cron job that periodically finds and fixes out-of-sync records. But we'd have to decide which is canonical: the usr table, or the artefact table?