#1071 Slow Mongo: subscription.findByCollectiveId N+1 fan-out inflates p95 (lab issue list)

Summary

billing_subscription lookups via subscriptionRepo.findByCollectiveId are executed as an N+1 concurrent fan-out, saturating the Mongo connection pool and inflating overall Mongo tail latency. This surfaced through the [sc] Mongo latency high (p95) alert on 2026-07-10.

Evidence (Axiom swamp_club_prod)

• Mongo span p95 climbed to 256ms (last 10m), 162ms (30m), 120ms (60m) vs a ~37ms 24h baseline (~7x) — sustained and worsening over ~1h.
• Dominant driver by volume x latency: [HOST-1] — p95 218ms across ~2,389 calls/hr.
• Secondary: extension.search (312ms, low volume — Atlas Search, expected), collective-queries.listCollectivesForUsers (199ms), several lab-issue.* list queries (100-125ms).
• It is query-specific, not a global Mongo outage (e.g. listUserCollectives still 22ms).

Root cause

findByCollectiveId itself is billing_subscription.findOne({ _id: collectiveId }) — a lookup on the always-indexed _id, so a single call should be sub-ms. The problem is the caller: lib/app/lab/enrich-author-plans.ts fans out one lookup per collective:

await Promise.all(allCollectiveIds.map(async (id) => {
  const sub = await [HOST-2](id);
  ...
}));

For a lab-issue-list request enriching many authors' plans this fires N concurrent single-document round-trips, contending on the connection pool and driving the 218ms p95 (a per-op cost that is really pool/queue wait, not query plan). The lab issues list endpoint (GET /api/v1/lab/issues) is the hot path.

Proposed fix

Collapse the N+1 into a single batched query:

• Add findManyByCollectiveIds(ids: string[]): Promise<Map<string, Subscription>> to SubscriptionRepository, implemented as billing_subscription.find({ _id: { $in: ids } }) in mongo-subscription-repository.ts.
• Replace the Promise.all(...map(findByCollectiveId)) in enrich-author-plans.ts with one call, building planByCollective from the result.
• Same N+1 shape is worth auditing in the billing paths that call findByCollectiveId per-collective (change-subscription, sync-subscription, start-checkout, open-billing-portal), though those are lower-volume.

Impact

User-facing latency on the lab issues list and author-plan enrichment; also adds avoidable load to prod Mongo under normal browsing.

Monitoring note

The Axiom monitor [sc] Mongo latency high (p95) was temporarily relaxed from 200ms -> 500ms (2026-07-10) so this known degradation stops paging. Please RESTORE its threshold to 200000000 (200ms) once this issue is resolved — the monitor's description already carries this reminder.