perf: Fix N+1 queries in get_users_for_notifications() with eager loading

[YaronZaki]

Problem Statement

UserDBConnector.get_users_for_notifications() in quantara/web_app/db/crud/user.py:26-43 joins User, Position, and TelegramUser tables but uses default lazy loading, triggering N+1 queries when accessing related attributes.

Evidence

# quantara/web_app/db/crud/user.py:26-43
def get_users_for_notifications(self) -> list[tuple[str, str]]:
    results = (
        db.query(User.contract_address, TelegramUser.telegram_id)
        .join(Position, Position.user_id == User.id)
        .join(TelegramUser, TelegramUser.wallet_id == User.wallet_id)
        .filter(
            Position.status == Status.OPENED.value,
            TelegramUser.is_allowed_notification == True,
        )
        .distinct()
        .all()
    )
    return results  # No joinedload — lazy loading will trigger N+1

Impact

Medium — unnecessary database load. When notification dispatch iterates over results and accesses related objects (User, Position, TelegramUser), each access triggers a separate query. With N users having open positions, this results in N+1 queries instead of 1.

Proposed Solution

Add sqlalchemy.orm.joinedload() to eagerly load related Position and TelegramUser data in the initial query, reducing N+1 queries to a single JOIN query. Use .options(joinedload(...)) on the query.

Acceptance Criteria

• joinedload() added to eagerly load related Position and TelegramUser data
• Query plan verified — single query with JOINs instead of N+1
• Existing behavior preserved (same return type)
• All existing tests pass

File Map

• quantara/web_app/db/crud/user.py:26-43 — add joinedload() to query

Testing Strategy

• Unit: Verify query with joinedload produces correct results
• Integration: Enable SQLAlchemy query logging, verify single query instead of N+1

Security Considerations

No security impact.

Definition of Done

• Code implemented and peer-reviewed
• Tests written and passing
• PR linked and merged

---

Labels: performance
Priority: Medium
Difficulty: Intermediate
Estimated Effort: 1h

[chuks68]

good day, i would love to work on this issue, having worked on previous issues, i am confident i can resolve this issue
My ETA is 4-5 hours

[grantfox-oss]

🦊 GrantFox — @chuks68 has been assigned to this issue as part of the Official Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #65)
2. Your PR will be reviewed by the Quantarq maintainers

Good luck! Track your progress on GrantFox.