driver-mongodb gates the lookup join index on `reference_to`, a spelling the spec rejects — so no authored lookup is ever indexed

[os-warren]

Found while measuring #11567 (the SQL driver's FK branch keys on the same rejected spelling). Filed unassigned. Pre-existing; out of scope for that investigation.

What

packages/drivers/driver-mongodb/src/mongodb-schema.ts:113:

// Lookup + user (a lookup specialized to sys_user) fields get an index for
// join performance. A `user` field always references sys_user, so it is
// indexed even when reference_to is not explicitly set.
if (
 (field.type === 'lookup' && field.reference_to) ||
 field.type === 'user'
) {
 indexOps.push({ spec: { [fieldName]: 1 }, options: { name: `idx_${fieldName}_lookup` } });
}

The interface at :37 declares reference_to?: string as the only relationship key it knows.

Why it is dead for authored metadata

reference is the only spelling FieldSchema declares. reference_to is a rejected alias, not a normalised one — measured on origin/main by parsing it:

FieldSchema.safeParse({ name:'parent', type:'lookup', reference_to:'p' })
 => success: false, issue.code = unrecognized_keys, keys = ["reference_to"]

and the canonical parse output carries reference_to: undefined. Field.lookup(ref, cfg) returns { type:'lookup', reference, ...cfg }. Nothing in non-test source ever assigns reference_to (repo-wide grep: the only occurrence outside tests is a comment in plugin-approvals/src/sys-approval-request.object.ts:425).

So field.reference_to is always undefined for a spec-conformant lookup, and the lookup disjunct never fires.

Consequence, and why it is narrower than the SQL twin

user fields still get the index — the || field.type === 'user' disjunct is unconditional. The gap is lookup-only: every authored lookup field on MongoDB is missing its join-performance index. Measured on the 44 exported platform objects, all 57 relationship fields are type: 'lookup' carrying reference, and zero carry reference_to — so on MongoDB none of the 57 is indexed.

Unlike the SQL side this is a performance defect, not a correctness one, and adding the index has no blast radius comparable to emitting a FOREIGN KEY: an index can be created on existing data that a constraint could not. It is likely a straightforward field.reference ?? field.reference_to fix, but it is a behaviour change on boot for existing MongoDB deployments (index builds on large collections), so it is worth a deliberate decision rather than a drive-by.

⚠️ Not measured live: no mongod/mongosh in the container, so this is a source reading plus the spec-parse measurement above, not a catalog read.

Where

• packages/drivers/driver-mongodb/src/mongodb-schema.ts:37 (interface), :113 (the gate)

---

Generated by Claude Code

[os-trump]

Triage: closing not planned under the maintainer's 2026-08-05 investment-freeze directive on the driver-memory / driver-mongodb family (recorded in the pm-dispatch lane table, domain:engine row). Source of the closure: that standing directive — not a judgement that the reading is wrong.

The reading stands and is preserved here: the lookup disjunct is dead for authored metadata (all 57 platform-object relationship fields carry reference, zero carry reference_to), so no authored lookup is indexed on MongoDB. It is performance-only — the user-field disjunct still indexes, no declared contract promises a lookup join index — and the card itself notes the fix is a deliberate boot-behaviour change (index builds on existing collections), i.e. not a drive-by even without the freeze.

Reopen (free) if: the freeze is lifted or a maintainer ruling queues driver-mongodb work; or a measured production complaint lands on MongoDB lookup join performance. The SQL-side sibling questions stay live in #11567 / #12252, which the freeze does not touch.

---

Generated by Claude Code