Add DISTINCT to repository queries that return duplicate rows

[stanleykc]

Summary

Repository queries TenantRepo.findAllByUserEmail() and UserRepo.findAllByTenantId() return duplicate rows when a user has multiple roles in the same tenant. This was discovered during the addition of comprehensive repository tests.

Current Behavior

TenantRepo.findAllByUserEmail()

When a user has multiple roles in the same tenant, the query returns one row per user_role entry instead of unique tenants.

Example: User person1@test.io has:

• 1 role in tenant 1 (SYSTEM)
• 2 roles in tenant 2 (acme)

The query returns 3 rows (tenant 2 appears twice) instead of 2 unique tenants.

UserRepo.findAllByTenantId()

When a user has multiple roles in a tenant, the query returns one row per user_role entry instead of unique users.

Example: Tenant 2 (acme) has:

• User 1 with 2 roles
• User 4 with 1 role

The query returns 3 rows (user 1 appears twice) instead of 2 unique users.

Proposed Solution

Add DISTINCT to the affected queries:

TenantRepo.java

@Query("""
SELECT DISTINCT t.*
FROM user_role ur
    INNER JOIN tenant t ON t.id = ur.tenant_id
    INNER JOIN user u ON u.id = ur.user_id
WHERE u.email = :email
""")
List<Tenant> findAllByUserEmail(String email);

UserRepo.java

@Query("""
SELECT DISTINCT u.*
FROM user_role ur 
    INNER JOIN user u ON u.id = ur.user_id
WHERE ur.tenant_id = :tenantId
""")
List<User> findAllByTenantId(Long tenantId);

Impact

• Low risk change
• Affects how tenants/users are listed in the UI
• Tests will need to be updated to reflect unique results

Related Files

• UnityAuth/src/main/java/io/unityfoundation/auth/entities/TenantRepo.java
• UnityAuth/src/main/java/io/unityfoundation/auth/entities/UserRepo.java
• UnityAuth/src/test/java/io/unityfoundation/auth/TenantRepoTest.java
• UnityAuth/src/test/java/io/unityfoundation/auth/UserRepoTest.java

References

• Discovered in backend test coverage improvement effort (2025-12-31)
• See docs/backend-testing-review.md for context