App Hub user issue fix proposal

[Birkbjo]

We've recently had a meeting discussing the current user-login issue on App Hub. The underlying issue is pretty simple - the users are missing the user_external_id in the database, so the server tries to create a new user - but the email already exists, and due to the unique constraint on the database - it fails. However, due to how developer information is stored and linked in the database, a proper solution seems to actually be kind of complex, or at least have a pretty big scope.
In this discussion I will try to come up with a proposal for a proper solution.

Goals:

• Fix the bug where users get an error if the email already exists, but without a provider-id.
• Allow multiple social logins HUB101 .

Background, dependencies and complexities

Before the latest auth0-upgrade, the email of the user was provided in the token, and if the provider-id was not found, it fell back to check for the email address itself, and used that user-id for identifying the user. However it never actually updated the user with the provider-id, so when we switched to the new auth0-flow that does not include the email-address in the token, the user would in this case not be found. The system would try to create the user, but fails due to unique constraint on the email.

Why would a user have an email stored, but no provider-id? This is the main crux of the problem. When a new app is uploaded - the developer email and developer name are used to create a new user-entity. So in the database, the app does not have a simple developer_email-field, it is a developer_user_id, and is thus joined with the user table! This has been brought up before, and I do believe it's wrongly modeled. The developer is not a user in the system until she has logged in. This information should be plain-text in the app-schema, or simply removed.

A simple solution would be to lookup the provider-id using auth0-API, and add the current provider-id if the email matches with the one on auth0's side. However we should not automatically llink users, and we want to move away from unique emails. See HUB-101 for some alternatives for handling multiple emails. I will not repeat those here, but go for option 4.2 in my proposal.

Proposal

• Remove unique constraint on email in the user-table.
  Lets keep the email in the user table (as opposed to in user_external_id-table). Might open for users to change this email down the line, but by default this is the email that the email logged in with.

• Create a new field in apps-table 'developer_email`, a plain text field
  Users might want different emails for a specific app other than the organisation contact-email, so lets keep this around. Maybe rename to contact email?

• Remove the code that creates an user when an app is uploaded
  This also means that we need a migration-script for setting the developer_email-field to the user-field.

• Remove developer name from UI and backend
  I think this is redundant, as we already require organisation for an app. Developer name may still be in package.json.

• When adding users to organisation, the invited member needs to get an email notification with a link. When link is pressed the invited user logs in (regardless of logged in session) and is then added to the organisation. This is how NPM works.

  • This supports duplicate emails, as the user would select a provider to log in with (which can have the same email).
  • Users with duplicate email, but different providers are treated as distinct users
  • Implementation of email-service (eg. nodemailer) and Amazon SES (HUB-69

[mediremi]

So in the database, the app does not have a simple developer_email-field, it is a developer_user_id, and is thus joined with the user table! This has been brought up before, and I do believe it's wrongly modeled. The developer is not a user in the system until she has logged in. This information should be plain-text in the app-schema, or simply removed.

I agree 👍 I think the developer email associated with an app should be stored in the apps schema but also that this should be an optional field with the fallback being the email associated with an organisation.

Remove unique constraint on email in the user-table.
Lets keep the email in the user table (as opposed to in user_external_id-table). Might open for users to change this email down the line, but by default this is the email that the email logged in with.

I agree that we should remove the unique constraint on email but I don't think the users table is the right place to store it — modelling users as having only a single email address is overly restrictive imo. Instead, I think we should add an email column to the external users table. Then to get all of a user's email addresses we could do SELECT email FROM external_users WHERE user_id = 'some user ID'.

While this may mean that if a provider does not expose a user's email address to us we will not be able to associate an email with each user, this will push us to enforce organisations having their own contact email which makes more sense in my opinion. As I've mentioned above, I think apps should have an optional developer contact email whose fallback is the (required) organisation email. In fact, I'm not sure we even need to know individual user's email addresses.

Remove the code that creates an user when an app is uploaded

Agree 💯 In fact I've updated the create app handler in dhis2/app-hub#458 to not do this.

Remove developer name from UI and backend

I agree 👍

When adding users to organisation, the invited member needs to get an email notification with a link. When link is pressed the invited user logs in (regardless of logged in session) and is then added to the organisation. This is how NPM works.

This would be a big improvement to both UX and how we handle new members in the backend 👍 .

---

I prefer thinking about database schemas visually so I've created a Google Drawing where we can collaborate on the new schema. I've added some more comments there about some of the changes I'm proposing.

[amcgee]

I'm not sure we're talking about multiple emails per user here, rather multiple users per email, so point 2 might be moot (for now) and we can just remove the unique constraint on the email in the users table while making the provider id required 👍

Agreed that organizations should have required contact emails, we should remove developer name (though we'll want to keep a backup of this probably as there might be important information there) and we should rename developer email to "Contact Email" for an app (optional, falling back to the organization email). Whichever is submitted by the user, it should be exposed behind a 'contact form' in the App Hub and App Management App UIs (using the App Hub SMTP service)

Create a new field in user-table 'developer_email, a plain text field Think this is a typo, should be apps` table I believe