username sanitization is case sensitive; column is citext

[hydrandt]

Summary

Creating a new user using oauth fails in case there is already the same username with different case (ie. existing: peter, newly registering: Peter)

Steps to reproduce

1. create an account with same username as your github username, but in different case (in my case, HYDRANDT would do)
2. sign out
3. attempt to sign in using github
4. enjoy blue screen "An unknown error occurred"

Expected results

Username sanitization should be case insensitive and username should be sanitized to hydrandt1

Actual results

HYDRANDT and hydrandt are deemed non identical, number is not appended, and inserting a new user fails, as username column on app_public.users is type citext.

Additional context

• problem is in the initial mutation, https://github.com/graphile/starter/blob/main/%40app/db/migrations/committed/000001.sql, in app_private.register_user:

-- Sanitise the username, and make it unique if necessary.
...
  select (
    case
    when i = 0 then v_username
    else v_username || i::text
    end
  ) into v_username from generate_series(0, 1000) i
  where not exists(
    select 1
    from app_public.users
    where users.username = (
      case
      when i = 0 then v_username
      else v_username || i::text
      end
    )
  )
  limit 1;

Possible Solution

Convert username using lower() while checking for uniqueness:

  select (
    case
    when i = 0 then v_username
    else v_username || i::text
    end
  ) into v_username from generate_series(0, 1000) i
  where not exists(
    select 1
    from app_public.users
    -- comparing using lowercase to make sure the username is unique (username column is citext -> constraint is not case sensitive)
    where lower(users.username) = (
      case
      when i = 0 then lower(v_username)
      else lower(v_username) || i::text
      end
    )
  )
  limit 1;