We've identified a discrepancy between the database state and API responses when retrieving user groups. When a user requests their groups, they're not receiving all groups where they are members. Specifically, when user ID 1062 requests their groups, they should receive 3 groups (IDs 22, 25, and 28), but only receive 2 groups (IDs 25 and 28).
The logs show only two groups being returned:
2025-05-21 21:38:17,604 - src.routers.user_groups - DEBUG - Fetching groups for user 1062...
2025-05-21 21:38:17,604 - src.database.user_groups - DEBUG - Getting user groups with users for user_id: 1062
2025-05-21 21:38:17,604 - src.database.user_groups - DEBUG - Fetching groups where user 1062 is a member...
2025-05-21 21:38:18,656 - src.database.user_groups - DEBUG - Found 1 groups where user 1062 is a member: [28]
2025-05-21 21:38:18,656 - src.database.user_groups - DEBUG - Fetching groups where user 1062 is an admin...
2025-05-21 21:38:19,606 - src.database.user_groups - DEBUG - Found 1 groups where user 1062 is an admin: [25]
2025-05-21 21:38:19,606 - src.database.user_groups - DEBUG - Added 1 additional groups as admin (not already as member): [25]
2025-05-21 21:38:19,606 - src.database.user_groups - DEBUG - Total: Found 2 user groups with users for user_id: 1062, Group IDs: [25, 28]
Our database queries confirm that the user should be in 3 groups:
SELECT id, name, user_ids, admin_ids
FROM user_groups
WHERE 1062 = ANY(user_ids) OR 1062 = ANY(admin_ids)
ORDER BY id;Result:
id | name | user_ids | admin_ids
----+-------------+-----------------+-----------
22 | UNDP Studio | {1062,774,1067} | {}
25 | GROUP 3 | {1062} | {1062}
28 | test4 | {774,1062} | {774}
The code in user_groups.py uses the correct SQL syntax to retrieve groups where a user is a member or admin:
- First query:
SELECT ... FROM user_groups WHERE %s = ANY(user_ids) ORDER BY created_at DESC; - Second query:
SELECT ... FROM user_groups WHERE %s = ANY(admin_ids) ORDER BY created_at DESC;
These queries should correctly find all groups, but the first query is only returning group 28, not both 22 and 28 as expected.
Users may not see all groups they belong to in the application, which could lead to:
- Reduced access to signals shared in "missing" groups
- Confusion about group membership
- Workflow disruptions if users expect to find signals in specific groups
- SQL query execution issues
- Application-level filtering not visible in the code
- A caching or stale data issue
- Transaction isolation level issues
- Potential race condition if groups are being modified simultaneously
We've implemented a comprehensive solution with multiple layers of improvements:
Modified the approach in the affected functions to use a single combined query with explicit array handling:
# Run a direct SQL query to ensure array type handling is consistent
query = """
SELECT
id,
name,
signal_ids,
user_ids,
admin_ids,
collaborator_map,
created_at
FROM
user_groups
WHERE
%s = ANY(user_ids) OR %s = ANY(admin_ids)
ORDER BY
created_at DESC;
"""
await cursor.execute(query, (user_id, user_id))We also added explicit type conversion when checking for user membership:
# Track membership rigorously by explicitly converting IDs to integers
is_member = False
if data['user_ids']:
is_member = user_id in [int(uid) for uid in data['user_ids']]
is_admin = False
if data['admin_ids']:
is_admin = user_id in [int(aid) for aid in data['admin_ids']]Added a debug_user_groups function that runs multiple direct queries to diagnose issues:
async def debug_user_groups(cursor: AsyncCursor, user_id: int) -> dict:
# Various direct SQL queries to check database state
# and array position functions
# ...Created a completely separate direct SQL implementation in user_groups_direct.py as a fallback:
async def get_user_groups_direct(cursor: AsyncCursor, user_id: int) -> List[UserGroup]:
"""
Get all groups that a user is a member of or an admin of using direct SQL.
"""
# Simple, direct SQL with minimal processing
# ...Modified the user groups router to automatically detect and handle discrepancies:
# Check if there's a mismatch between direct query and regular function
if missing_ids:
logger.warning(f"MISMATCH! Direct query found groups {direct_group_ids} but function returned only {fetched_ids}")
logger.warning(f"Missing groups: {missing_ids}")
# Fall back to direct SQL implementation
logger.warning("Falling back to direct SQL implementation")
user_groups = await user_groups_direct.get_user_groups_with_users_direct(cursor, user.id)
logger.info(f"Direct SQL implementation returned {len(user_groups)} groups")These changes ensure:
- More reliable querying of user group memberships
- Better debug information if issues persist
- Automatic fallback to a simpler implementation if needed
- More detailed logging throughout the process
After these changes, users should see all groups where they are members or admins consistently.
During testing, we discovered that the Signal entity was missing the can_edit attribute that's dynamically added in user group contexts. This caused AttributeError exceptions when trying to access signal.can_edit.
AttributeError: 'Signal' object has no attribute 'can_edit'Added the can_edit field to the Signal entity definition:
can_edit: bool = Field(
default=False,
description="Whether the current user can edit this signal (set dynamically based on group membership and collaboration).",
)This ensures that:
- The Signal model accepts the
can_editattribute when created - The attribute defaults to
Falsefor signals that don't have edit permissions - Both the regular and direct SQL implementations can properly set this attribute
- Frontend code can safely access
signal.can_editwithout errors
The fix has been applied to both endpoints:
/user-groups/me(user groups without signals)/user-groups/me/with-signals(user groups with signals)
Both endpoints now include the same debug checks and automatic fallback to direct SQL if discrepancies are detected.