Scale group summaries for large user counts

[dr-hoseyn]

Problem

GET /api/groups currently eager-loads every User row attached to every returned group only to compute total_users with len(group.users). On installations with many users this makes response time and memory use grow with the number and width of user records, even though the response only needs a count.

The association-table primary keys are ordered (user_id, groups_id) and (inbound_id, group_id). Group-centric counts, joins, and bulk deletes therefore lack a deterministic reverse access path on databases that do not automatically index foreign-key columns.

Evidence

Synthetic local SQLite benchmark with 500,000 user memberships across 100 groups:

Path	Time	Traced Python allocations	Query plan
Current full user hydration	1,014.19 ms	74.43 MiB	Loads user rows
SQL count, no reverse index	2,196.56 ms median	—	Full association scan
SQL count with covering reverse index	18.016 ms median	0.0098 MiB peak	Covering-index search

This benchmark is directional rather than a production guarantee, but it reproduces both scaling problems: object hydration and reverse membership lookup.

Proposed change

• Populate Group.total_users with a correlated SQL count expression in the group-list query.
• Keep the users relationship unloaded for group summaries.
• Add covering reverse indexes:
  • users_groups_association (groups_id, user_id)
  • inbounds_groups_association (group_id, inbound_id)
• Build/drop the PostgreSQL indexes with CONCURRENTLY outside the migration transaction so membership writes remain available during upgrades.
• Preserve existing create/update/single-group behavior by setting total_users whenever those paths intentionally load the relationship.

Acceptance criteria

• /api/groups returns the same total_users values without loading Group.users.
• Group-centric membership queries use the new reverse indexes.
• Upgrade and downgrade work on SQLite, PostgreSQL, and MySQL/MariaDB.
• PostgreSQL production upgrades do not use a write-blocking normal index build.
• Regression tests cover the index definitions and unloaded relationship behavior.