Endpoints that query the user model should not be case sensitive

[FrankApiyo]

Environmental Information

• Onadata version: 5.0.3

Problem description

Endpoints that query the user model using the username are currently case sensitive.
Examples are as follows:

• /api/v1/profiles
• /api/v1/projects/10574/share

Expected behavior

We should be able to query for usernames in a case-insensitive manner

Steps to reproduce the behavior

The following commands should yield the same result:

➜  curl 'scheme://api-domain/api/v1/profiles.json?users=fapiyo' \
  -H 'accept: application/json' \
  -H 'authorization: TempToken <Your temp token>'
➜  curl 'scheme://api-domain/api/v1/profiles.json?users=FApiyo' \
  -H 'accept: application/json' \
  -H 'authorization: TempToken <Your temp token>'

➜  curl 'scheme://api-domain/api/v1/projects/<project-id>/share' \
  -X 'PUT' \
  -H 'accept: application/json' \
  -H 'authorization: TempToken <Your temp token>' \
  --data-raw 'username=fapiyo&role=readonly'
➜  curl 'scheme://api-domain/api/v1/projects/<project-id>/share' \
  -X 'PUT' \
  -H 'accept: application/json' \
  -H 'authorization: TempToken <Your temp token>' \
  --data-raw 'username=FApiyo&role=readonly'

[FrankApiyo]

A potential solution is to use citext: https://www.postgresql.org/docs/current/citext.html

[FrankApiyo]

https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collation/

[FrankApiyo]

This issue also affects 3rd party packages interacting with Onadata

[FrankApiyo]

We need to use a case-insensitive collation instead of citext because citext is not recommended by PostgreSQL.

You can learn more here: https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collation/

Additionally, Django has, over the years, dropped native support for citext. Have a look at the features removed from Django 4.2 here: https://docs.djangoproject.com/en/5.2/releases/4.2/#deprecated-features-4-2

[FrankApiyo]

We would also like both the email and username fields to be case insensitive