Summary

The tool_service.py makes separate database queries to fetch team names for each tool via _get_team_name(), causing N+1 query patterns in list operations and single-entity retrieval.

Problem

When listing or retrieving tools, the service either:

1. Calls _get_team_name(db, team_id) per tool (N+1 in get_tool())
2. Uses manual JOIN queries to batch-fetch team names (complex, error-prone)

# tool_service.py - Manual team lookup per tool
def _get_team_name(self, db: Session, team_id: Optional[str]) -> Optional[str]:
    if not team_id:
        return None
    team = db.query(EmailTeam).filter(EmailTeam.id == team_id, EmailTeam.is_active.is_(True)).first()
    return team.name if team else None

Proposed Fix

Add an email_team relationship to the Tool model with lazy="joined" for automatic eager loading:

# db.py - Add to Tool model
email_team: Mapped[Optional["EmailTeam"]] = relationship(
    "EmailTeam",
    primaryjoin="and_(Tool.team_id == EmailTeam.id, EmailTeam.is_active == True)",
    foreign_keys=[team_id],
    lazy="joined",
)

@property
def team(self) -> Optional[str]:
    return self.email_team.name if self.email_team else None

This eliminates:

• _get_team_name() method
• Manual JOIN queries in list_tools(), list_server_tools(), list_tools_for_user()

Files to Modify

• mcpgateway/db.py - Add relationship and property
• mcpgateway/services/tool_service.py - Remove manual team lookups

Acceptance Criteria

• Tool model has eager-loaded email_team relationship
• _get_team_name() removed from tool_service
• Manual JOIN queries for team names removed
• Existing tests pass