perf: `agentRuntimeTokens.tokenHash` is unindexed on the agent-auth hot path

[lilyshen0722]

middleware/agentRuntimeAuth.ts:62 and services/agentWebSocketService.ts:308 both resolve an agent's identity with

User.findOne({ 'agentRuntimeTokens.tokenHash': tokenHash, ... })

and there is no index on that path. backend/models/User.ts declares exactly two userSchema.index(...) calls (:365 authProviders, :366 the compound unique) plus field-level index: true on two entitlements.* keys. Nothing covers agentRuntimeTokens.tokenHash.

That query is on the hot path: it runs on every /api/agents/runtime/* request and on every agent WebSocket connect, so each one is a collection scan of User. lastUsedAt is then written back through the same unindexed predicate (:78, and agentWebSocketService.ts:322), so it is two scans per authenticated agent call, not one.

Fix is one line in the schema:

userSchema.index({ 'agentRuntimeTokens.tokenHash': 1 }, { sparse: true });

sparse matters here — most User rows carry agentRuntimeTokens: [] and have no value at that path, so a sparse multikey index only stores entries for rows that actually hold a token.

Scale caveat, stated rather than left to be inferred: at the current user count a scan is not a visible latency problem, and I have not measured one. This is filed as a correctness-of-shape issue on a path whose cost grows with total users while its working set grows with agents only.

One thing I am explicitly NOT asking for

I previously suggested this array should also get select: false, by symmetry with apiToken. That reasoning was wrong and I am withdrawing it:

• apiToken earns select: false (models/User.ts:234) because it is a plaintext cm_ bearer — an incidental findById() leaks a live credential.
• agentRuntimeTokens[] stores only tokenHash. agentRuntimeAuth.ts:67 matches on the hash; the plaintext is never persisted.
• The array is already stripped from responses by controllers/userController.ts:16 (SECRET_USER_FIELDS), with a regression test at __tests__/unit/controllers/userController.secretLeak.test.js:95.

select: false would still be defence in depth against a future route that returns a user without going through that controller, but the precedent I cited for it does not apply, and adding it means auditing every .select('agentRuntimeTokens...') call site (there are ~10) for a now-required +. Not obviously worth it; someone else's call.

Raised in the sprint pod at message 59725; filed here so it is durable somewhere other than pod prose.

[lilyshen0722]

The remedy in this body is no longer hypothetical: #1405 (538854f7, OPEN) adds the identical index for the sibling array — userSchema.index({ 'deviceTokens.tokenHash': 1 }), three lines above the block this issue is about. So the newer, cold array gets a covering index while agentRuntimeTokens.tokenHash — the hot one — still has none.

Re-measured at origin/main da5e90df:

• backend/models/User.ts declares exactly two userSchema.index(...) calls (authProviders.provider+providerId, and the digestUnsubscribeToken partial-unique). Neither covers this path. (The :365/:366 anchors in the body above have drifted to :369/:370 — cite by symbol.)
• Four non-test query sites, unchanged: middleware/agentRuntimeAuth.ts (the read at the auth predicate, and the lastUsedAt write-back) and services/agentWebSocketService.ts (same pair). The first pair runs on every /api/agents/runtime/* request; the second on every agent WebSocket connect.

Explain-aways killed before posting: no field-level index: true inside the agentRuntimeTokens sub-schema (it declares tokenHash/label/createdAt/lastUsedAt/expiresAt and nothing else — billing.customerId and billing.proEndedAt in the same file do carry index: true, so the idiom is in use and was not applied here); and git grep agentRuntimeTokens origin/main -- backend/migrations backend/scripts scripts is empty, so nothing creates it out-of-band.

One divergence worth settling between the two: #1405's index is not sparse, while the fix proposed above is. Both arrays default to [] on the overwhelming majority of User rows, so it is the same question in both places — whichever lands second should match the first rather than leave the file with two spellings of one decision.

[lilyshen0722]

Re-measured at origin/main today — the headline holds, and the line numbers have drifted since filing: the two hot-path scans are now middleware/agentRuntimeAuth.ts:91 and services/agentWebSocketService.ts:373. Still no covering index: userSchema.index(...) declares only authProviders.{provider,providerId} and the partial-unique digestUnsubscribeToken, and the subdocument field at models/User.ts:308 has no index: true.

One correction to the issue body, because it changes what the fix needs. The body says lastUsedAt "is then written back through the same unindexed predicate." It isn't. Both write-backs are _id-anchored:

• agentRuntimeAuth.ts:107-110 — updateOne({ _id: agentUser._id, 'agentRuntimeTokens.tokenHash': tokenHash }, { $set: { 'agentRuntimeTokens.$.lastUsedAt': ... } })
• agentWebSocketService.ts:387 — same shape.

The _id equality bounds those to a single document; the tokenHash term is there to position the $ update operator, not to drive the lookup. So there are two unindexed queries on this path, not four — the two findOnes that resolve identity from the hash alone.

Practical consequence: a plain single-key userSchema.index({ 'agentRuntimeTokens.tokenHash': 1 }) is sufficient. Nothing here motivates a compound index, which is the natural thing to reach for if you read the current body as saying the update also scans.

For reference, #1405 adds exactly that shape for the sibling collection — userSchema.index({ 'deviceTokens.tokenHash': 1 }) — so there is a precedent to match, and the two subdocument arrays otherwise have identical access patterns.

Raised again independently by @pod-architect in the sprint pod today as residue from the #1405 review; linking here so it doesn't get filed twice.

[lilyshen0722]

Re-derived this after @sprint-review corrected my site count in the pod. Their correction is right and mine was wrong, but the corrected number is 3, not 2 — the sweep and I missed the same site for different reasons.

Confirmed not scanning (my error). agentRuntimeAuth.ts:108 and :165 are both _id-anchored updateOnes — { _id: agentUser._id, 'agentRuntimeTokens.tokenHash': tokenHash } and { _id: installation._id, 'runtimeTokens.tokenHash': tokenHash }. The tokenHash term only positions the $ projection; the _id does the lookup. Neither needs an index and neither argues for a compound one. (:165 is a third write-back of the same shape, so the write-back count is 2, not 1.)

Confirmed scanning, and already in this issue's body:

• agentRuntimeAuth.ts:92 — User.findOne({ 'agentRuntimeTokens.tokenHash': … }). No index: userSchema declares nine, none on that path.
• agentRuntimeAuth.ts:150 — AgentInstallation.findOne({ 'runtimeTokens.tokenHash': … }). AgentInstallationSchema declares four, none on that path.

Third site, not in this issue and not in the correction: backend/middleware/appAuth.ts:14.

const inst = await AppInstallation.findOne({ tokenHash, status: 'active' });

backend/models/AppInstallation.ts declares no indexes at all — not one .index() call, no unique: true, no index: true — and nothing creates one outside the model (createIndex/ensureIndex return zero hits against any token field repo-wide, and no migration touches the collection). So every app-token authentication is a full collection scan of appinstallations.

It was missed because it isn't on the agent-runtime path: different model, different middleware, different auth surface. A sweep scoped to the agent token path can't reach it, which is why I'm reporting it here rather than filing separately.

Fix shape is unchanged and @sprint-review's point stands — a single-key index per site, matching #1405's deviceTokens one. Three of them, not two. AppInstallation.tokenHash is a plain top-level field, so it's the simplest of the three; it is not unique, since nothing guarantees one live token per installation.

What I did not verify: all of this is schema-declared state read off origin/main. I did not inspect the live indexes on the cluster, so if any of these were created by hand out-of-band, they'd be invisible to this method — including the AgentCredential.tokenHash unique index I'm relying on to rule the two AgentCredential.findOne call sites (agentRuntimeAuth.ts:70, daemonAuth.ts:39) out of scope. That exclusion is the load-bearing one, and it rests on a declaration rather than a measurement.