[Design Discussion] User Display Attribute for User Types

[DonOmalVindula]

Related Feature Issue

#1511

Problem Summary

Thunder allows creating user types (schemas) that define the structure of user attributes. However, when listing users across any endpoint (user list, OU users, group members, role assignments), users are identified only by their UUID. This makes it impossible to meaningfully identify users in the UI or API responses without making additional per-user requests.

For example, a typical user list response currently looks like:

{
  "users": [
    { "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890" },
    { "id": "f9e8d7c6-b5a4-3210-fedc-ba9876543210" }
  ]
}

There is no way to show a human-readable identifier (like a name or email) without fetching each user individually — an N+1 problem that degrades both UX and performance.

High-Level Approach

1. Display Attribute on User Schema

Each user schema gains a displayAttribute field — the name of one top-level string attribute designated as the human-readable identifier for users of that type.

Constraints:

• Must reference a top-level string attribute in the schema
• Credential-type attributes (password, pin, secret, passkey) are blocked
• If the schema has exactly one eligible string attribute, it is auto-selected
• If there are multiple eligible attributes, the creator must explicitly choose
• If there are zero eligible attributes, the field is left empty (fallback to user ID)

API surface:

{
  "id": "schema-123",
  "name": "employee",
  "displayAttribute": "email",
  "schema": {
    "email": { "type": "string", "required": true, "unique": true },
    "department": { "type": "string" },
    "password": { "type": "string" }
  }
}

2. Opt-in Display Resolution via include=display

All user listing endpoints gain an optional include=display query parameter. When present, the response includes a display field alongside each user ID:

GET /users?include=display

{
  "users": [
    { "id": "a1b2c3d4-...", "display": "[email protected]" },
    { "id": "f9e8d7c6-...", "display": "[email protected]" }
  ]
}

Without the parameter, responses remain unchanged (fully backward compatible).

3. SQL JOIN for Display Resolution (No N+1)

Display values are resolved via a single SQL JOIN at the store layer — the USER table is JOINed with USER_SCHEMAS to get the DISPLAY_ATTRIBUTE column name, and the actual value is extracted from the user's JSON attributes in Go. This eliminates the N+1 problem entirely.

SELECT u.USER_ID, u.ATTRIBUTES, us.DISPLAY_ATTRIBUTE
FROM "USER" u
LEFT JOIN USER_SCHEMAS us ON u.TYPE = us.NAME
    AND us.DEPLOYMENT_ID = u.DEPLOYMENT_ID
WHERE u.DEPLOYMENT_ID = $3
ORDER BY u.USER_ID LIMIT $1 OFFSET $2

4. Cache-Backed User Schema Store

Following the existing cache_backed_store pattern (used by application, flow/mgt, cert packages), a cache layer is added around the user schema store. Schema lookups use TTL-based caching since schemas rarely change. This is especially beneficial for the role assignment display resolution path, which resolves display per-user via cached schema lookups.

5. Fix Role Assignment Display

The existing role assignment include=display feature currently returns the user ID for user-type assignments (see getDisplayNameForAssignment in role/service.go). With this change, it will resolve the actual display attribute value using the cached schema service.

Architecture Overview

Affected Endpoints

Endpoint	Change
GET /users	Add ?include=display support
GET /users/tree/{path}	Add ?include=display support
GET /organization-units/{id}/users	Add ?include=display support
GET /organization-units/tree/{path}/users	Add ?include=display support
GET /groups/{id}/members	Add ?include=display support
GET /roles/{id}/assignments	Fix to return actual display value (already has include=display)
POST /user-schemas	Accept displayAttribute field
PUT /user-schemas/{id}	Accept displayAttribute field
GET /user-schemas, GET /user-schemas/{id}	Return displayAttribute field

Database Change

Add DISPLAY_ATTRIBUTE VARCHAR(100) column to the USER_SCHEMAS table (nullable for backward compatibility with existing schemas).

Model Changes

Add optional Display string field (with json:"display,omitempty") to existing structs:

• user.User
• ou.User
• group.Member

The omitempty tag ensures the field is omitted from JSON when include=display is not used, maintaining full backward compatibility.

Display Resolution Strategies by Endpoint

Endpoint Type	Strategy	DB Queries
User list, OU users, Group members	SQL JOIN at store layer	1 query
Role assignments	Per-user GetUser + cached schema lookup	N queries (cached schema)

Cache Architecture

userschema/cache_backed_store.go
├── schemaByNameCache: cache.CacheInterface[*UserSchema]
├── schemaByIDCache:   cache.CacheInterface[*UserSchema]
└── store:             userSchemaStoreInterface (underlying DB store)

• Cache-through on GetUserSchemaByName and GetUserSchemaByID
• Invalidate on Create, Update, Delete
• Uses existing cache.GetCache[T]() framework with TTL

Validation Rules

1. Parse schema JSON → collect top-level string attributes
2. Filter out credential types (password, pin, secret, passkey)
3. Single eligible → auto-set if displayAttribute is empty; validate if provided
4. Multiple eligible → displayAttribute is required; must be in eligible set
5. Zero eligible → allow empty (runtime fallback to user ID)

Frontend Changes

• Create/Edit User Type forms: Add display attribute selector dropdown (lists eligible string attributes, auto-selects if only one)
• User listing components: Pass ?include=display to API, show display value as primary column
• TypeScript types: Add displayAttribute to schema types, display to user types

Security Considerations

• Display attribute is read-only in user context: Users cannot change which attribute is the display attribute; only schema administrators can
• Credential types are blocked: Password, PIN, secret, and passkey attributes cannot be selected as display attributes, preventing accidental exposure of sensitive data in list views
• Input sanitization: The displayAttribute field is sanitized via sysutils.SanitizeString() in the handler layer
• No new authentication/authorization changes: Display values are derived from attributes the user already has access to in the full user response

Impacted Areas

Backend

• backend/internal/userschema/ — Model, store, service, handler, cache-backed store, declarative resource, error constants
• backend/internal/user/ — Model, store, service, handler, store constants
• backend/internal/ou/ — Model, store, service, handler, store constants
• backend/internal/group/ — Model, store, service, handler, store constants
• backend/internal/role/ — Service (display fix), init (new dependency)
• backend/internal/system/servicemanager/ — Wiring for new dependency
• backend/dbscripts/thunderdb/ — SQLite and PostgreSQL schema scripts

API Specifications

• api/user.yaml, api/ou.yaml, api/group.yaml, api/role.yaml

Frontend

• User types feature (create/edit forms, type definitions)
• Users feature (list components, type definitions)
• Organization units feature (user listing in OU management)

Tests

• Integration tests for schema CRUD validation
• Integration tests for include=display across all listing endpoints

Alternatives Considered

1. N+1 Schema Lookup (Per-User Service Call)

Approach: For each user in a list, call userSchemaService.GetDisplayAttributeByName(user.Type) then extract the value.

Rejected because: Even with caching, this requires N JSON unmarshal operations at the service layer and doesn't leverage the database's ability to resolve the attribute name in the same query. The JOIN approach is more efficient and keeps display resolution contained in the store layer.

2. Denormalized Display Column on USER Table

Approach: Store the resolved display value directly in the USER table as a separate column, updated on every user create/update.

Rejected because: Requires triggers or application-level logic to keep the denormalized value in sync. If the display attribute is changed on the schema, all users of that type would need to be updated. The JOIN approach is simpler and always reflects the current schema configuration.

3. Always Include Display (No Opt-in)

Approach: Always return the display value in list responses without requiring include=display.

Rejected because: This would break backward compatibility for API consumers that parse responses strictly. The opt-in pattern is already established with role assignments and is the safer approach.

4. Separate Display Endpoint

Approach: Create a new /users/display endpoint that returns display mappings.

Rejected because: Requires an extra API call from the frontend. The include=display query parameter pattern is more ergonomic and consistent with the existing role assignment pattern.

Questions for Community Input

1. Fallback behavior: When a user's display attribute value is empty/null, should we fall back to the user ID, or show an empty string? Current proposal: fall back to user ID.

2. Existing schemas migration: For existing schemas without a displayAttribute, should we auto-infer it at read time (if exactly one eligible string attribute exists), or require explicit migration?

3. Display in single-user GET: Should GET /users/{id} also support include=display, or is it only needed for list endpoints? (The full user response already contains all attributes.)

4. Multiple include values: Should the include parameter support comma-separated values for future extensibility (e.g., include=display,attributes)?

[thiva-k]

Are we planning to move the USER_SCHEMAS table into the user DB from the config DB? The JOIN operation doesn't support cross-DB unless they are in the DB connection/instance which may bring more complexity and sqlite cannot support this AFAIK.

Can we also consider onboarding a batch fetch query support for user/group services ( e.g. userService.GetUsersByIDs) . If we do the N+1 issue of roles assignment endpoint can be fixed to fetch the display names. Also having a cache-backed store may not make much sense if we fix this N+1 issue. WDYT?

[DonOmalVindula]

Are we planning to move the USER_SCHEMAS table into the user DB from the config DB? The JOIN operation doesn't support cross-DB unless they are in the DB connection/instance which may bring more complexity and sqlite cannot support this AFAIK.

Yes. You are correct. I was under the impression taht they are in the same DB.

So we will not be able to go through the JOIN approach as they are in 2 separate DBs. Alternatively, we should be able to resolve values in the service layer.

1. Fetch the user list from the user DB as usual
2. Collect the distinct type values from the results (typically 1-3 per page since most deployments have a small number of user types)
3. Look up the displayAttribute name for each distinct type via userSchemaService.GetUserSchemaByName(). This call will be backed by a cache, so repeated lookups for the same type cost very less.
4. Build a map[typeName]displayAttributeName from the results (ex. {"employee": "email", "customer": "username"})
5. For each user in the list, use the map to find the display attribute name for their type, then extract that key from their JSON attributes

This keeps the user DB query unchanged and adds only O(distinct_user_types) config DB lookups per request, which with caching is effectively 0 after the first call. The JSON key extraction is an in-memory map[string] lookup per user.

---

Can we also consider onboarding a batch fetch query support for user/group services ( e.g. userService.GetUsersByIDs) . If we do the N+1 issue of roles assignment endpoint can be fixed to fetch the display names. Also having a cache-backed store may not make much sense if we fix this N+1 issue. WDYT?

Agreed. We should add GetUsersByIDs / GetGroupsByIDs support. However, I think the cache-backed schema store and batch fetch serve different purposes and are complementary rather than alternatives.

Consider the role assignments endpoint. With batch fetch alone, the flow would be:

1. Get role assignments -> list of (userID, groupID) pairs
2. Batch fetch users by IDs -> get user objects with type and attributes
3. For each user, look up their schema to find the display attribute name -> N schema lookups (one per user type)
4. Extract the display value from each user's attributes JSON

Step 3 still hits the config DB repeatedly. Since there are typically only a handful of distinct user types (ex: employee, customer), caching schemas by name is very effective after the first lookup per type, all subsequent ones are cache hits. Without the cache, we'd be making redundant config DB queries for the same schema on every request.

I think we can use both approaches here to improve performance.

• Cache-backed schema store - eliminates redundant config DB lookups for schema metadata (display attribute name, validation rules, etc.). Useful beyond just display, any feature that needs schema info benefits.
• Batch GetUsersByIDs - eliminates the N+1 problem when fetching user data from the user DB.

Together, the role assignments flow becomes as follows.

1. Get assignments - (userID, groupID) pairs
2. Batch fetch users- single query to user DB
3. Group users by type, look up each type's display attribute - cache hits (config DB)
4. Extract display values from JSON attributes

This gives us 1 query to the user DB (batch) + ~0 queries to the config DB (cache hits after warm-up), compared to the current N+1 queries.

I'll add GetUsersByIDs / GetGroupsByIDs as a follow-up task. For this feature's initial implementation, the cache-backed store covers the schema lookup side, and we can optimize the user-fetching side with batch queries in a subsequent PR. Created an issue to track this improvement - #1524

[thiva-k]

For each user, look up their schema to find the display attribute name -> N schema lookups (one per user type)

We can get the distinct type values from the batch-fetched users. Then make only one DB call to resolve the display attributes for all user types.
Something like:

SELECT NAME, DISPLAY_ATTRIBUTE FROM USER_SCHEMAS WHERE NAME IN ($1, $2, ...) AND DEPLOYMENT_ID = $M

This approach should work right?

[DonOmalVindula]

For each user, look up their schema to find the display attribute name -> N schema lookups (one per user type)

We can get the distinct type values from the batch-fetched users. Then make only one DB call to resolve the display attributes for all user types. Something like:

SELECT NAME, DISPLAY_ATTRIBUTE FROM USER_SCHEMAS WHERE NAME IN ($1, $2, ...) AND DEPLOYMENT_ID = $M

This approach should work right?

Yes. +1. This is a better and a more simpler way. Will proceed with this approach.

[ThaminduDilshan]

1. With the suggestion we're kind of restricting display attribute to be a single attribute in the user schema. But is that the case always? I feel like display name being first name + last name is a common case. Should this design cater such requirements too? On a side note, our stance for that could be to define a different attribute as "Display Name" and assign that as the display attribute for the schema.

2. If the answer to the first question is "No" (i.e. we're only allowing to use a single attribute as the display attribute), API design wise, isn't it better to move this inside the schema attribute definition rather than having as a special top level element? Something like;

   {
     "name": "Demo User",
     "ouId": "{{demoOuId}}",
     "allowSelfRegistration": true,
     "schema": {
           "username": {
               "type": "string",
               "required": false,
               "unique": true
           },
           "email": {
               "type": "string",
               "required": false,
               "unique": true,
   +          "displayAttribute": true
           },
           "givenName": {
               "type": "string",
               "required": false
           },
           ...
       }
   }

   With this, we should have a validation in schema mgt service layer to restrict specifying display property to only one attribute

[DonOmalVindula]

1. With the suggestion we're kind of restricting display attribute to be a single attribute in the user schema. But is that the case always? I feel like display name being first name + last name is a common case. Should this design cater such requirements too? On a side note, our stance for that could be to define a different attribute as "Display Name" and assign that as the display attribute for the schema.

I think as you have mentioned, we could cater this requirement with the "Display Name" attribute.

2. If the answer to the first question is "No" (i.e. we're only allowing to use a single attribute as the display attribute), API design wise, isn't it better to move this inside the schema attribute definition rather than having as a special top level element? Something like;

   {
     "name": "Demo User",
     "ouId": "{{demoOuId}}",
     "allowSelfRegistration": true,
     "schema": {
           "username": {
               "type": "string",
               "required": false,
               "unique": true
           },
           "email": {
               "type": "string",
               "required": false,
               "unique": true,
   +          "displayAttribute": true
           },
           "givenName": {
               "type": "string",
               "required": false
           },
           ...
       }
   }

   With this, we should have a validation in schema mgt service layer to restrict specifying display property to only one attribute

I'm also +1 for this approach as well. @darshanasbg what is your preference when it comes to these 2 approaches?

[senthalan]

@ThaminduDilshan , I have a concern on moving the displayAttribute property to the attribute level. This is a property of the of the user type. So it wouldn't be defined with the attribute.

Also as we are planning to have only one attribute as the displayAttribute, we have to perform computational operations when creating or updating or getting the displayAttribute, looping through all the attribute properties to find it and to make sure it's not duplicated.

[ThaminduDilshan]

I have a concern on moving the displayAttribute property to the attribute level. This is a property of the of the user type. So it wouldn't be defined with the attribute.

It's just a matter of defining it here or there right? With the initial proposed approach, we're specifying display attribute name outside the attribute list. With my suggestion, we're embedding it inside the attribute list.

Also as we are planning to have only one attribute as the displayAttribute, we have to perform computational operations when creating or updating or getting the displayAttribute, looping through all the attribute properties to find it and to make sure it's not duplicated.

Yes. This is the same point I mentioned with we should have a validation in schema mgt service layer to restrict specifying display property to only one attribute. IMO it's fine to have this computational overhead for the attribute management (i.e. schema create and update) operations. Anyway we'll be performing some level of validations iterating over the attribute list. Even if we define it outside the attribute list, the same validation should be there to make sure it's a valid attribute in the attribute list.

However this might be a concern for the retrieval points (get display attribute). Even for that, I believe we'll anyway fetch the entire attribute list for the schema and parse it as a array in runtime. Then it will be a single loop over the attribute list to find out the display attribute. Alternatively, in order to avoid this, we can store it as a top level field in the database

[ThaminduDilshan]

There is no way to show a human-readable identifier (like a name or email) without fetching each user individually — an N+1 problem that degrades both UX and performance.

On a side note, isn't it better to include the profile picture/ logo image too with this to improve UX? We may not support this for the moment, but wanted to know our stance on this

[DonOmalVindula]

There is no way to show a human-readable identifier (like a name or email) without fetching each user individually — an N+1 problem that degrades both UX and performance.

On a side note, isn't it better to include the profile picture/ logo image too with this to improve UX? We may not support this for the moment, but wanted to know our stance on this

The issue is that the attribute for the profile picture can vary right? (unless we enforce that as well similar to the display attribute)

[ThaminduDilshan]

Yes. There has to be a way to specify this is the display attribute for the profile picture. But this again complicates the schema. Maybe we should be able to resolve this with attribute type

[DonOmalVindula]

Had a discussion with @darshanasbg regarding this matter. The solution we came up with as follows, the schemas will have a systemAttributes object as follows.

{
    "id": "019cb209-ce92-7142-aff9-d822a3979d8e",
    "name": "Person",
    "ouId": "019cb209-ce6d-7273-abce-60f6c539c4a0",
    "allowSelfRegistration": false,
    "systemAttributes": {
        "display": "email",
        ......
        ......
        ......
    },
    "schema": {
        "username": {
            "type": "string",
            "required": true,
            "unique": true
        },
        "email": {
            "type": "string",
            "required": true,
            "unique": true
        },
        .......
        .......
        .......
        "password": {
            "type": "string",
            "required": true,
            "credential": true
        }
    }
}

For display purposes, we will be using the attribute defined in the display in the systemAttributes object. In the future we can use this object to define attributes used for system purposes (maybe username etc)