Purpose

Currently, all user attributes are stored as a JSON in the DB. This prevents leveraging DB indexes to optimize reads (For example, reading the user data by username to perform authentication).

Resolves: #511

Goals

Add support to store a subset of user attributes in separate columns and index those. Attributes which need to be indexed should be configurable in the user schema

Approach

This pull request introduces support for indexed user properties in both the database schema and backend logic, enabling more efficient querying and filtering of users by specific attributes. It also refactors authentication and user identification flows to support an explicit userType parameter and updates related interfaces and tests accordingly.

Database schema enhancements for indexed properties:

• Added five indexed property columns (INDEXED_PROP_1_VALUE through INDEXED_PROP_5_VALUE) to the USER table in both PostgreSQL and SQLite schemas, along with corresponding indexes to support efficient lookups. The USER_SCHEMAS table now also stores the names of these indexed properties. [1] [2]

Backend logic and API changes for indexed properties and user type:

• The user creation logic in userService now extracts values for indexed properties from user attributes and stores them in the new indexed columns.
• The BuildFilterQuery utility is updated to accept and process both indexed and unindexed filters, generating optimized queries for indexed columns. Related tests are updated as well. [1] [2] [3] [4] [5] [6] [7]

Authentication and user service interface changes:

• The authentication flow is refactored to accept a userType parameter for credential-based authentication, propagating this parameter through handler, service, and interface layers. [1] [2] [3] [4] [5] [6] [7] [8] [9]
• The AuthenticateUserRequest type is changed from a map to a struct with Attributes and UserType fields for clarity and extensibility.
• The IdentifyUser method in the user service interface and all its call sites are updated to accept a userType parameter. [1] [2] [3] [4] [5]

These changes lay the groundwork for efficient querying of users by indexed properties and allow for more flexible authentication flows based on user type.

Release note

Add support for indexed user attributes