PR: Fix N+1 query patterns in team member counts

Summary

• Adds batch query methods to eliminate N+1 patterns in admin UI team listings
• Implements Redis caching for team member counts with configurable TTL
• Adds partial database index for optimized COUNT queries
• Reduces /admin/ P95 latency from 14s to <500ms under load

Problem

Load testing with 3000 concurrent users revealed critical performance issues:

• 11,429+ individual COUNT queries for team member counts
• 60+ idle-in-transaction connections causing timeouts
• 7.8 second average response time for /admin/ endpoint
• 21 idle transaction timeout errors during test runs

Root cause: EmailTeam.get_member_count() executes a separate COUNT query for each team, creating an N+1 pattern when listing teams.

Solution

Phase 1: Batch Query Methods

Three new methods in TeamManagementService:

• get_member_counts_batch() - batch fetch member counts
• get_user_roles_batch() - batch fetch user roles
• get_pending_join_requests_batch() - batch fetch pending join requests

Phase 2: Call Site Updates

Updated 6 call sites to use batch methods:

• admin.py:admin_ui()
• admin.py:_render_unified_teams_view()
• admin.py:admin_list_teams()
• routers/teams.py:list_teams()
• routers/teams.py:discover_teams()

Phase 3: Database Index

Added partial index for PostgreSQL:

CREATE INDEX idx_email_team_members_team_active_partial
ON email_team_members(team_id) WHERE is_active = true;

Phase 4: Redis Caching

• get_member_counts_batch_cached() with configurable TTL
• Automatic cache invalidation on membership changes
• Graceful fallback when Redis unavailable

Configuration

New settings (all optional, sensible defaults):

TEAM_MEMBER_COUNT_CACHE_ENABLED=true   # Enable/disable caching
TEAM_MEMBER_COUNT_CACHE_TTL=300        # TTL in seconds (30-3600)

Performance Impact

Files Changed

Configuration

• mcpgateway/config.py - New cache settings
• .env.example - Documentation
• docker-compose.yml - Default values
• charts/mcp-stack/values.yaml - Helm chart values
• charts/mcp-stack/values.schema.json - Schema validation
• docs/docs/manage/configuration.md - User documentation

Implementation

• mcpgateway/services/team_management_service.py - Batch methods + caching
• mcpgateway/admin.py - Updated call sites
• mcpgateway/routers/teams.py - Updated call sites
• mcpgateway/alembic/versions/ - Index migration

Tests

• tests/unit/mcpgateway/services/test_team_management_service.py
• tests/integration/ - Cache invalidation tests

Closes

Closes #1892
Closes #1893
Contributes to #1894
Contributes to #1862

Testing

• Unit tests for all 3 batch methods
• Unit tests for cached method (enabled/disabled/Redis unavailable)
• Unit tests for cache invalidation
• Integration tests for membership change invalidation
• Load test confirms N+1 elimination
• Load test confirms cache hit rate >80%
• Helm chart deploys successfully