feat: set `email_confirmed_at` to null when autoconfirm is on

[hf]

A long-standing bug has been that when autoconfirm is on (should have been named "allow sign up without verifying email") it would set the email_confirmed_at column to the timestamp of the sign-up call. With this change it will set it to null instead.

It is a backward compatible change because:

1. If you had autoconfirm on, then the values in email_confirmed_at are useless to you and you would not have relied on them anyway.
2. If you had autoconfirm off, then there's no change.

[J0]

If you had autoconfirm on, then the values in email_confirmed_at are useless to you and you would not have relied on them anyway.

I agree in theory but in practice I worry that we might not be able to predict the full range of ways a developer might use the service.

Contrived example: What if a developer turned on Auto-confirm for an initial sign up or pre-registration phase of trusted users and later turned it off after a private alpha to allow less trusted users to hold. They might then depend confirmed_at having a specific date time so they can track when the cohort signed up and also so that they don't have to re-verify.

If not already considered, could we perhaps emit a warning for two weeks and then increment the minor version thereafter as per the backward compatibility advisory ?

[vtsatskin]

@hf thanks for looking into this and opening a PR to solve this problem. I have an interest in this PR going through. For context, I've posted about my use case in a discussion exploring an alternative solution to the problem: https://github.com/orgs/supabase/discussions/22363#discussioncomment-10033530

I'm leaving a link to the existing discussion here in case it's helpful in the conversation and deciding what the best solution is while minimizing impacts to users.

[cstockton]

I believe this change has been abandoned, can we close it @hf?

[matthewadams]

Please see my comments beginning at https://github.com/orgs/supabase/discussions/22363#discussioncomment-13433634. Essentially, for an auth provider that relies on verification (phone, email, etc) provide an additional boolean setting that allows unverified accounts to sign in, so that I can incentivize users to verify and start using my site's services and increase conversion to paying customers. This is really a no-brainer, guys. Supabase is so great, I just can't believe this isn't a Thing yet.