supabase / auth / 8973453095

69%

Build Type

push

github

Committed by web-flow

Commit Message

feat: refactor one-time tokens for performance (#1558)

Refactors all One-Time Tokens (OTP) used for sign-in with email, SMS,
email confirmation, phone confirmation, change... to achieve:

- Performance (as current method does not use an index due to the use of
[partial
indexes](https://github.com/supabase/auth/blob/master/migrations/20220429102000_add_unique_idx.up.sql#L10-L14)
which [cannot be used in
practice](https://www.postgresql.org/docs/current/indexes-partial.html))
- Future enhancements (such as OTP verification counters, adaptive OTP
lengths, etc.)

Summary of the change:

- A new `one_time_tokens` table is added which uses a double-write
mechanism with `users`.
- Each new OTP is both written in the corresponding `users` column and
as a new row in `one_time_tokens`.
- Lookup for an OTP hash is performed first in `one_time_tokens` and if
not found, using the traditional `users` approach.
- In a few days, once all OTPs using the `users` columns have expired, a
new change will be deployed which removes the `users` lookup. This
completely solves the performance issue for looking up OTPs.
- In a future change, the `one_time_tokens` table can be used to add a
verification counter based on lookups on the `relates_to` (email or
phone number) column, enabling new security features.

---------

Co-authored-by: Joel Lee <lee.yi.jie.joel@gmail.com>

Run Details

202 of 339 new or added lines in 6 files covered. (59.59%)

5 existing lines in 2 files now uncovered.

8365 of 12776 relevant lines covered (65.47%)

64.73 hits per line