#924 Collscan/index-gap pack: unindexed $or id branches (every registry request), computed-field sort kills extension browse indexes, $expr blocks pull counts, apikey.userId, workflow_runs, 1100-doc profile fetch

From the 2026-07-02 full-stack performance audit: the remaining collscans / index gaps / over-aggregations on hot request paths, outside the auth, /lab, and leaderboard issues (filed separately). Index inventory was cross-referenced against migrations 001-011, every ensureIndexes() in lib/infrastructure/, and services/telemetry/lib/db.ts. BetterAuth's Mongo adapter creates no indexes itself.

1. Unindexed id field inside $or → whole-query collscans on organization/member/invitation

Mongo only index-unions an $or when every branch is indexable; id (string mirror of _id) has no index on any of these collections (only organization._id_str from migration 007 — a different field). Query shape {$or: [{id: {$in}}, {_id: {$in}}]} at:

• mongo-namespace-ownership-repository.ts:75-81 (listUserOrgIds) — called from lib/app/search-visibility.ts:50, i.e. every authenticated extension search/registry request
• better-auth-collective-queries.ts:114-116, 173-175, 244-246 (lab page views, /orgs, whoami)
• better-auth-collective-repository.ts:65-67; better-auth-collective-queries.ts:333-336, 394-397 (invitation/member management)

organization row count ≈ user count (personal orgs). Fix: sparse {id: 1} indexes — or verify BetterAuth's Mongo adapter never writes a separate id field (it doesn't) and delete the {id:…} branches, which also removes the $or.

2. Extension registry browse: $sort on computed _isDeprecated defeats every index

mongo-extension-repository.ts:966-976, 1003-1013, 1217-1227 (sort doc :597-619, computed field :627-637): $addFields {_isDeprecated} → $sort {_isDeprecated: 1, updatedAt: -1}. The {updatedAt:-1}, {pullCount:-1,name:1}, {createdAt:-1} indexes are all dead because the leading sort key is computed per request → in-memory sort of all public extensions on every /extensions browse and pattern-less search.

Fix: persist isDeprecated: 0|1 (write in deprecate/undeprecate :1435-1474 + one-time backfill), add {isDeprecated:1, updatedAt:-1} / {isDeprecated:1, pullCount:-1, name:1} / {isDeprecated:1, name:1}.

3. getFacetCounts: whole-collection $facet with 3× $unwind+$group per registry render

mongo-extension-repository.ts:813-896, caller routes/extensions/index.tsx:41. Negation-only match is unindexable; each facet sub-pipeline re-traverses every doc, identical result for every anonymous viewer. Fix: 30-60s in-process cache keyed by visibility (orgIds hash), or maintain counters incrementally on publish/yank.

4. apikey.userId unindexed countDocuments per public profile view

better-auth-operative-queries.ts:83-88, called from routes/u/[username].tsx:181. No index on apikey exists anywhere. Fix: createIndex({userId: 1}).

5. extension_pulls $expr self-pull filter blocks covered counts

mongo-extension-pull-repository.ts:116-121 (countPulls, extension detail page via routes/extensions/[...name].tsx:179) and :47-114, 173-205 (getAuthorStats/countAuthorPulls, run inside refresh-user-score.ts:63,193 — i.e. per score-refresh queue event): the field-to-field $expr {$ne: ["$pulledByUserId", "$authorUserId"]} forces fetching every pull doc for the extension/author. A 100k-pull extension = 100k doc fetches per detail-page count; a popular author's entire lifetime pull history is re-aggregated every time they trigger scoring — cost grows monotonically forever.

Fix: stamp selfPull: boolean at insert (recordPull:32-45 has both IDs in hand) + backfill; index {extensionName:1, selfPull:1} / {authorUsername:1, selfPull:1} for covered counts. The ledger rework (#847) removes the per-refresh recount entirely.

6. swamp_workflow_runs: zero indexes

mongo-workflow-run-repository.ts has no ensureIndexes. findByIssue:31-40 collscans per GitHub webhook queue event (5s poll); listRecent:137-143 collscan + in-memory sort. Fix: {workflowName:1, "inputs.issues.repo":1, "inputs.issues.issueNumber":1} + {startedAt:-1}.

7. Profile page fetches 1,100 daily snapshot docs per view

routes/u/[username].tsx:125-128 → mongo-metric-snapshot-repository.ts:63-80: up to 1,100 daily docs (with full eventCounts/categoryTotals maps) fetched, mapped, and serialized into island props per profile view — hundreds of KB per render. Fix: project only chart fields; downsample beyond 90 days server-side; or lazy-load history via API from the island.

8. Username-change migration: ~1,400 serial round trips inside the request

better-auth-confirm-username-repository.ts:275-322 (migrateMetricSnapshots): per-doc insertOne + deleteOne in a serial loop. A 2-year-active account ≈ 700 daily docs → 1-2 minutes inside the confirm-username request. Same pattern (smaller N) in migrateBadgeAwards:163-256. Fix: one bulkWrite (ordered:false), chunked ~1k.

9. Latent (flagging before it ships): findTopByCategory

mongo-user-score-repository.ts:181-191 sorts on unindexed categoryTotals.<cat> — no production caller today; add per-category indexes before wiring category boards.

Verified clean: all five queue watchers poll indexed shapes; notification repo is a model citizen (cursor pagination, partial unread index); feed_posts fully indexed; score_grants covered; better-auth batch username/id lookups are single $ins with projections.