NonUniqueResultException in UserStateSqlApi.getHiddenEnvironments() due to incomplete unique constraint

[ImranShahBP]

Describe the bug
Users experiencing 500 errors when trying to access feature toggles due to NonUniqueResultException: Unique expecting 0 or 1 results but got 2 in UserStateSqlApi.getHiddenEnvironments(). This causes complete loss of feature toggle access for affected users.

Root Cause: Database integrity issue where the unique constraint on fh_userstate table doesn't include the user_state column, allowing duplicate records that break the findOne() query.

Which area does this issue belong to?

• FeatureHub Admin Web app
• SDK
• SDK examples
• Documentation
• Other

To Reproduce
Steps to reproduce the behavior:

1. Create duplicate user state records in database (same person + application + userState but different portfolio/environment)
2. User logs into FeatureHub Admin Web app
3. Navigate to any application's feature toggles
4. System internally calls /mr-api/user-data/state/{userId}/hidden-environments
5. See 500 error in browser, feature toggles fail to load

Expected behavior
Users should be able to access feature toggles without 500 errors. The getHiddenEnvironments() method should return a single unique result for each person/application/userState combination.

Error Details

jakarta.persistence.NonUniqueResultException: Unique expecting 0 or 1 results but got 2
  at io.featurehub.db.services.UserStateSqlApi.getHiddenEnvironments(UserStateSqlApi.kt:30)
  at io.featurehub.mr.resources.UserStateResource.getHiddenEnvironments(UserStateResource.kt:21)

Request URI: /mr-api/user-data/state/{userId}/hidden-environments

Database Schema Issue
Current unique constraint in DbUserState.java line 16:

@Index(unique = true, name = "idx_user_state", columnNames = {"fk_person", "fk_portfolio_id", "fk_app_id", "fk_env_id"})

Query logic in UserStateSqlApi.kt line 25:

QDbUserState().person.id.eq(person.id!!.id).application.id.eq(appUid).userState.eq(userState)

Problem: Constraint missing user_state column allows duplicates that break findOne() queries.

Versions

• FeatureHub version: 1.8.2-rc
• Management Repository: mr-1.1-SNAPSHOT.jar
• Database: PostgreSQL (using EBean ORM)
• Environment: Kubernetes production cluster
• OS: Linux containers

Investigation Findings
The issue appears to stem from the unique constraint not matching the query logic:

• Constraint: {"fk_person", "fk_portfolio_id", "fk_app_id", "fk_env_id"}
• Query filters: person.id, application.id, userState

Suggested Investigation Areas

1. Review if the unique constraint should include user_state column
2. Examine if the query logic in UserStateSqlApi.kt needs adjustment
3. Consider if this is expected behavior for certain data scenarios

Database Analysis Query
To identify affected records:

-- Check for potential duplicates
SELECT fk_person, fk_app_id, user_state, COUNT(*) 
FROM fh_userstate 
WHERE user_state = 'HIDDEN_FEATURES'
GROUP BY fk_person, fk_app_id, user_state
HAVING COUNT(*) > 1;

Additional context

• Affects production users (confirmed case in production environment)
• Component restarts don't resolve the issue as it's a data integrity problem
• Related files: DbUserState.java, UserStateSqlApi.kt, UserStateResource.kt
• Requires database migration to fix existing duplicates and add proper constraint