Prevent duplicate records when two requests arrive together

Two invitations arrive for the same email address in the same organization. The API checks whether an invitation exists, finds none, and inserts. Both requests perform the lookup before either insert completes. The code appears careful, yet the collection ends with two active invitations. A pre-insert read can improve a message; it cannot enforce a concurrency invariant.

This example assumes MongoDB and a Node server. The desired invariant is one invitation record per organization and normalized address for the chosen invitation lifecycle. The exact fields depend on the product. Before adding an index, define whether expired, revoked, or accepted invitations belong to that invariant.

Write the invariant in storage terms

A global unique email index would prevent the same address from being invited to different organizations, which is not the stated rule. A compound key on organization and normalized email expresses the intended scope. If only active invitations must be unique, the lifecycle representation and a possible partial index need deliberate design.

Normalization must also be explicit. Trimming surrounding whitespace may be straightforward, but email identity policy can involve case, internationalized domains, and existing conflicts. Do not silently collapse provider-specific dots or plus tags across all domains. See email normalization for the migration risk.

Decide what a repeated request should do: return the existing invitation, report conflict, or resend under a bounded policy. That user behavior is separate from the uniqueness mechanism. A database constraint prevents two records; it does not decide which response the product should display.

Reproduce the race deterministically

Create two requests for the same synthetic organization and address. Use a barrier in the test so both pre-insert lookups complete before either insert begins. This demonstrates the race without relying on lucky manual timing. Record the resulting count and response outcomes.

Also test two addresses and two organizations. A fix that rejects every concurrent invitation would satisfy the duplicate count while breaking normal use. The important assertion is scoped uniqueness, not serialization of all requests.

Ask an AI assistant to list every write path to the collection: API creation, background imports, administrative tools, and migrations. A lock around one endpoint cannot protect another process or another application instance. Storage-level enforcement covers those paths when they write the constrained fields.

Audit existing records before creating the index

A unique index cannot be created successfully while conflicting indexed values already exist. Run a read-only grouping query over the proposed key and review conflicts. Include missing and null fields because index behavior for those values may affect legacy records. Do not assume the schema declaration has already created the intended database index.

Choose a conflict resolution policy with the data owner. Two invitations might have different tokens, timestamps, or delivery history. Deleting one arbitrarily can invalidate a legitimate link. Produce a preview of changes and preserve enough evidence for recovery.

Apply any necessary normalization and conflict cleanup in a controlled migration. Then create the index and verify it exists in the actual database. For sharded deployments, check MongoDB's uniqueness restrictions relative to the shard key. A local unsharded example does not establish the same constraint is legal everywhere.

Add the constraint and map its outcome

For a simple lifecycle where each organization-address pair has one persistent invitation record, a compound unique index is clear. The example is a database operation, not a request-time index creation step.

await invitations.createIndex(
  { organizationId: 1, normalizedEmail: 1 },
  { unique: true, name: 'organization_email_unique' }
)

Keep the insert as the authoritative operation. A preliminary lookup may remain for an early friendly response, but the insert must still handle a duplicate-key outcome caused by a concurrent writer. Use the driver's documented error information and verify the relevant index or key before converting it into the intended domain conflict.

Do not map every duplicate-key exception to this invitation message. Another unique constraint may have failed for a different reason. Keep error translation in the storage adapter or a similarly narrow boundary so callers receive a meaningful domain result.

Consider upsert semantics carefully

An upsert can express creating a record if none exists, but it still needs the unique key when concurrent operations target the same logical entity. Review which fields belong in the filter and which belong only in insert values. A filter containing changing fields can accidentally stop matching the existing record and attempt another insert.

If repeating the request returns an existing invitation, avoid resetting its token or delivery state unintentionally. Updating all fields on every upsert can rotate a token after a previous email was sent. Use an explicit lifecycle operation for resending rather than hiding it in a generic create.

A single-document atomic update can manage some invitation state transitions. If a create also writes audit or outbox records that must agree, determine whether a transaction is needed. Transaction decisions separates single-document invariants from multi-record ones.

Verify with real storage enforcement

Run simultaneous inserts against an isolated MongoDB database with the index installed. Assert that exactly one logical record exists and that each request receives a documented outcome. A fake repository returning a conflict does not prove the actual database rejects duplicates. It can only verify response mapping.

Test case and whitespace variations according to the chosen normalization policy. Test missing normalized fields if legacy records or alternate write paths can produce them. Verify different organizations remain independent. Inspect the actual index definition as part of deployment evidence.

Then run the alternate write paths the assistant found. An import that stores raw email without the normalized key can evade a poorly designed invariant or trigger unexpected null conflicts. The application should construct the key consistently before persistence.

Weigh operational cost against correctness

An index adds storage and write work, but the purpose here is correctness rather than a speculative speed improvement. Measure performance separately if invitation volume requires it. Do not remove the constraint to make an import faster without replacing the invariant with an equally durable mechanism.

The smallest safe repair consists of a precise key, existing-data audit, installed constraint, and intentional conflict handling. It is more robust than an in-memory mutex because it works across instances and write paths.

Idempotent create endpoints solve a related problem: recognizing a repeated business operation after an ambiguous network result. Uniqueness protects stored identity; idempotency can preserve a request's result. They often complement each other rather than substitute for each other.