perf(worker): N+1 query fetching notification preferences per mentioned user

Start in worker/src/features/notifications/commentMentionHandler.ts, focusing on the mention loop and its notificationPreference lookup. Add coverage for one batched preference query across multiple mentions and verify that users with enabled: false are skipped while missing preferences remain enabled.

Summary

handleCommentMentionNotification batches project users into a lookup map to avoid per-user queries, then immediately does a per-user prisma.notificationPreference.findUnique() inside the mention loop. A comment mentioning N users issues N sequential preference queries.

Observed on main @ ae84d6a39 (v4.3.1).

Affected code

worker/src/features/notifications/commentMentionHandler.ts

The batching intent is explicit at line ~120:

// Create lookup map for O(1) access
const userMap = new Map(projectUsers.map((u) => [u.id, u]));

and the loop even references it — "from our single query above" at line ~143. But then, at line 161:

for (const userId of mentionedUserIds) {
  ...
  // Check notification preference (default: enabled)
  const preference = await prisma.notificationPreference.findUnique({
    where: {
      userId_projectId_channel_type: {
        userId, projectId, channel: "EMAIL", type: "COMMENT_MENTION",
      },
    },
  });

The preference lookup is the one query that didn't get the batching treatment the rest of the function received.

Why this matters

• Inconsistent with the code immediately above it. The author clearly established the batch-then-map pattern; this is a single missed call rather than a design choice.
• Latency is serial. Each iteration also awaits buildCommentLink and sendCommentMentionEmail, so a comment mentioning a whole team serialises a round trip per person on top of the query.
• The unique key is a natural in filter. (userId, projectId, channel, type) with fixed projectId/channel/type collapses cleanly to a single findMany.

Suggested fix

Hoist the preference lookup out of the loop, mirroring the existing userMap pattern:

const preferences = await prisma.notificationPreference.findMany({
  where: {
    projectId,
    channel: "EMAIL",
    type: "COMMENT_MENTION",
    userId: { in: mentionedUserIds },
  },
});
const disabledUserIds = new Set(
  preferences.filter((p) => !p.enabled).map((p) => p.userId),
);

then replace the in-loop query with a disabledUserIds.has(userId) check. This preserves the existing default-enabled semantics (a missing row means enabled), which the current comment documents explicitly.

Optionally, the per-user email sends could be parallelised with a bounded Promise.all, though that's a larger behavioural change and probably belongs in a separate PR.

Suggested labels

back-end-performance, feat-comments, size:S

Happy to submit a PR. This one is easy to cover with a test asserting a single findMany for an N-mention comment, and that a user with enabled: false is still skipped.