#280 Missing unique indexes on user.email and user.username allow duplicate users

Symptom

The user collection in production has only the default {_id_} index — no unique constraint on email or username. Two concurrent sign-up requests with the same email (or same OAuth-derived username) can both succeed.

> db.user.getIndexes()
[{ v: 2, key: { _id: 1 }, name: '_id_' }]

Why nothing currently rejects a duplicate

BetterAuth checks for an existing email at sign-up.mjs:158 (findUserByEmail) before calling createUser at :174. There is no DB-level unique constraint behind that check, so it is a TOCTOU race — two parallel /api/auth/sign-up/email requests can both pass the check and both insert a user doc.

Same shape on the username path: the username plugin's before hook (plugins/username/index.mjs:198-223) does a findOne on the user collection by username before insert. No DB-level unique guard backs it up either.

OAuth (/callback/<provider> → handleOAuthUserInfo → createOAuthUser) is wrapped in runWithTransaction, but the mongo adapter's transaction (adapters/mongodb-adapter/mongodb-adapter.mjs:319-336) is single-attempt — no retry loop — and a transaction does not synthesize uniqueness; that has to come from the index.

Evidence in production

Two user docs already exist with username: null, created 18 seconds apart:

{ "_id":"69ba3af949632acd11d06571", "email":"[email protected]",   "username":null, "createdAt":"2026-03-18T05:41:13.368Z" }
{ "_id":"69ba3b0b49632acd11d06573", "email":"[email protected]",   "username":null, "createdAt":"2026-03-18T05:41:31.341Z" }

Different emails, but both username: null. With a unique index on username, the second insert would have been rejected. Filed as a separate cleanup issue.

Reproduction

1. In a staging environment with auth + mongo, fire two concurrent POST /api/auth/sign-up/email with identical email/password and slightly different timing (within ~50ms is enough on Atlas).
2. Inspect db.user.find({ email: "<that email>" }) — both inserts land.
3. Repeat with the username plugin enabled and a sign-up payload that picks the same username — both inserts land.

Suggested fix

Add unique indexes on user.email and user.username to the BetterAuth-managed collection. Username is sometimes null during the OAuth → choose-username flow, so use a partial filter or sparse index for that one:

await db.collection("user").createIndex({ email: 1 }, { unique: true });
await db.collection("user").createIndex(
  { username: 1 },
  { unique: true, partialFilterExpression: { username: { $type: "string" } } },
);

Place the createIndex calls in startup code that runs once per deploy (the same pattern as lib/infrastructure/mongo-extension-scoring-event-queue.ts:180-189 ensureIndexes). Existing duplicates must be cleaned up first — see the companion issue.

Why this also matters for telemetry

This is not the cause of #278 (the discord-bot double-send) — that bug is the watcher-recovery race against discord_event_queue losing its dedup key after the bot drains. But while inspecting prod for #278 we discovered this index is missing, and it's a latent dup-user vector that should be closed independently.

Environment

• swamp-club main, production
• BetterAuth 1.4.18 with mongodb adapter