support for email subscription

[amitu]

We have fastn_user table which contains entries when a user account is created. User account for now is only created by the email auth “provider". An auth provider is responsible for creating account, and we can have more providers in future, Github, etc.

Github After Email

More than one provider can be activated for a single site (eg you can signin via email or via Github). Each provider is reponsible for storing data in data column, under their own key, so email provider stores data in data -> ‘email' and github provider in data -> github.

If github provider is about create an account, for an email for which there is already a verified account, github provider instead associates the github data with existing user itself.

How does any provider find out if there is an account for verified email? It looks for the email in data -> ‘email’ -> ‘verified-emails’ list. If this list is present it contains all the verified email address of this user.

If the github provider was about to create account, but user is already logged in via email, E1, and github tells us that this user has a verified email E2, github provider adds E2 to user against E1 and now that user has two emails and github associated. User can login with either email now, or via github.

If user was logged in with E1, but it is not verified, the email is stored in data -> ‘email’ -> ‘emails’, and if github comes along and tells us that this user’s github verified email is E1 as well, we move E1 from data -> ‘email’ -> ‘emails’ to data -> ‘email’ -> ‘verified-emails’. We only do this if user is logged in with E1.

If user exists with E1 but is not verified, and not logged in current session, we ask user to enter password of existing unverified account. At this point if user enters the current password, or sets a new password, the email will move to data -> ‘email’ -> ‘verified-emails’. The implication of this rule is if you create an account with someone else’s email address, and afterwards they come via github, they can “claim” your account, and your data will become accessible to them, this happens in all services that allow reset password links via email, as the person who owns the email can get the reset password link and reset it and access the account. There is no way to prevent this, we can technically ask user to create a new account, or claim existing account, and may ask user to give some proof about existing account knowledge to prove they are the ones who created the original account before the claim existing account, but this is a bit dubious).

Subscription

For email subscription, we want to reuse the same table. If we are loading already confirmed emails from other email subscription provider (mail chimp etc), we add the email data for the first fastn_user that matches the incoming email, we first look in verified-emails, and then in emails.

THe subscription data itself goes under data -> subscription key. Subscription is not an auth provider.

If subscription happens first, we have an account for the email address, stored in data -> email -> emails (not verified-emails, even if the user has done double opt in. Double opt in or not info is stored in data -> ‘subscription' -> ‘verified’), and if a user tries to create an account with that email, we set the password (under data -> email -> password), username (under identity column), etc against the same user.

How Do We Know If An Account Exists?

Since we are adding data both via subscription and email, (and eventually via other providers who have provide us email addres). Currently we have data -> email -> identity, if it is set we assume account via email was created. But login method is expecting data -> email -> identity. Currently this is non nullable. If we make it nullable, identity will become nullable for all providers. It may so be that that makes sense, what identity would Facebook give us? For other providers we use identity as unique key, as id, so we have to save facebook uid here.

The problem is we want to show you are logged in as so and so providers, or your account is connected with so and so providers, and how do you identify the providers, like you are logged in via your github account with this username, or these two twitter usernames are associated with your account. How do we identify Facebook? I guess for each provider we have to have “account-name”, and account-identity fields, the name and identity.

I think I am not too keen on making identity optional. Instead when we inserting data for email, we include email as identity (along with including it in emails), and rely on custom -> created or something to see if email account exists. We can look at other fields like name etc being set as well, but having a dedicated field is probably better.

Query Efficiency

Since we are storing emails in JSON arrays, we have to do two such queries:

        format!(
            r#"
            SELECT
                id, data -> '{provider_id}' as data
            FROM
                fastn_user
            WHERE
                EXISTS (
                    SELECT
                        1
                    FROM
                        json_each(data -> '{provider_id}' -> 'emails')
                    WHERE value = $1
                )
            "#
        )

These are not very efficient, they do full table scan, json decode each row, and not sure how the index will work. In future we can create other tables eg email -> fastn_uid, which we can do query on, and do futher queries on all matching fastn_uid (if one email is associated with more than one user for some reason).