How should I structure my MongoDB schema for a production app? · DpkDB
How should I structure my MongoDB schema for a production app?
A complete schema for a multi-tenant project tracker: collections, types, ESR indexes checked with explain(), validation, migrations, write concerns and sharding readiness.
Structure a production MongoDB schema in this order: write the query list, choose collections so that each frequent query reads one of them, fix field names and BSON types, design one compound index per query shape and confirm it with explain(), then add validation, a schema version field, and explicit write concerns for the writes that must not be lost. Sharding comes last, but the schema should be ready for it from the first day. This article does all of that for one application, a multi-tenant project tracker with users, projects, tasks, comments and an activity feed.
Seven ordered questions that decide embed, reference or a hybrid, the mechanical cost of each choice, and a help desk schema worked through end to end.
Must be unique per email while pending, must expire
Every operation happens inside one tenant.
2. Collections
Collection
Holds
Why separate
tenants
Plan, billing settings, limits
Read on every request, tiny, cached
users
One document per user per tenant, with embedded preferences
Queried by email, referenced everywhere
projects
Name, members (bounded array), settings
Listed on their own
tasks
The task, with assignee name and comment count copied in
The main entity, queried four ways
comments
One document per comment, with taskId
Unbounded per task, paged
activity
Append-only events
Unbounded, expires
invitations
Pending invites
Short-lived, needs its own uniqueness rule
Preferences are embedded in the user because they are one-to-one and always read together. Comments are not embedded in tasks because nothing bounds their number. The task carries assignee.name and commentCount so that the board (Q1) renders from tasks alone.
3. Multi-tenancy layout
There are three layouts: a database per tenant, a set of collections per tenant, or shared collections with a tenantId field. Use shared collections unless you have a contractual isolation requirement. Per-tenant databases and collections multiply every collection and index by the number of tenants, which is the "massive number of collections" anti-pattern.
The shared layout has two rules with no exceptions:
Every document in every tenant-owned collection has tenantId.
Every query filters on tenantId, and every index on those collections starts with it.
Enforce the first with the validator and the second in one place in the data access layer, not in each query.
4. Field names and types
_id. The choice is between three options.
Option
Use when
Watch for
ObjectId (default)
Almost always
Its first 4 bytes are a creation timestamp in seconds, readable with getTimestamp(), so ids exposed in URLs reveal creation time
UUID()
Ids are generated outside the database, or must reveal nothing
Store the binary type that UUID() produces, not the 36-character string. Random values insert all over the _id index instead of at its end
Natural key
The value is truly immutable, such as an ISO currency code
_id cannot be changed. An email address or a slug is not a safe _id
_id can be any BSON type except an array or a regular expression. If you use an embedded document as _id, remember that field order is significant when documents are compared, so { a: 1, b: 2 } and { b: 2, a: 1 } are different keys.
References must store exactly the same BSON type as the _id they point to. An ObjectId and its hex string do not match each other in a query or a $lookup.
Dates are BSON Date values, never strings. Money is Decimal128, because binary floating point cannot represent decimal fractions exactly. Enumerations are short strings checked by the validator. Names use one convention (camelCase here) and one suffix for time fields (createdAt, dueAt).
Order the keys of each compound index by the ESR guideline: fields matched by Equality first, then the Sort fields, then Range fields. tenantId is an equality field in every query, so it leads.
In Q2, status: { $in: ["todo", "in_progress"] } followed by a sort still counts as equality for index ordering as long as the list is short. The server expands a small $in into separate index scans and merges them in sorted order (a SORT_MERGE stage). With a list of roughly 200 values or more it behaves like a range, and the fields after it can no longer provide the sort.
In Q3, putting priority before dueAt avoids an in-memory sort at the cost of scanning index entries for dates outside the range. The manual's exception to ESR applies when the range is very selective: then { ..., dueAt: 1, priority: 1 } reads fewer keys and sorts a small result in memory. Decide with explain(), not by rule:
The winning plan uses IXSCAN on the index you intended and contains no SORT stage. A SORT stage means the index did not supply the order. How do I fix MongoDB "Sort exceeded memory limit"? covers what happens when that sort gets large.
totalKeysExamined is close to nReturned. A large gap means a range or non-selective field is making the scan read entries it discards.
totalDocsExamined is not above nReturned. If it is, a filter is being applied after fetching documents and belongs in the index.
// Q6: one account per email per tenant. The application lowercases email before writing.
db.users.createIndex({ tenantId: 1, email: 1 }, { unique: true })
// W3: at most one pending invitation per email per tenant
db.invitations.createIndex(
{ tenantId: 1, email: 1 },
{ unique: true, partialFilterExpression: { status: "pending" } }
)
// W3: remove invitations at the time stored in expiresAt
db.invitations.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 })
// Activity older than 180 days is deleted
db.activity.createIndex({ at: 1 }, { expireAfterSeconds: 15552000 })
A unique index treats a missing field as null, so two documents in the same tenant that both omit email collide. Make uniquely indexed fields required in the validator. With a partial filter, uniqueness applies only to documents that match the filter, which is what allows any number of accepted or revoked invitations for the same email.
TTL indexes must be single-field, which is why activity has a separate { at: 1 } index next to its compound one. The TTL task runs every 60 seconds, deletes only on the primary, and does not guarantee deletion at the exact expiry time, so application code must still check expiresAt when it accepts an invitation.
7. Validation
Define the schema as a variable so that the same object can be used to validate and to query:
maxItems on labels is a schema-level guard against an unbounded array. Use bsonType, not JSON Schema's type: MongoDB does not support the integer type and expects int or long. If your application writes null for absent values, each such field must list null as an allowed type. If you add additionalProperties: false, include _id in properties or every document is rejected.
Roll validation out in two steps on an existing collection:
validationAction: "warn" lets every write through and records violations in the server log.
validationLevel: "moderate" applies the rules to inserts and to updates of documents that already satisfy them, and leaves existing invalid documents updatable. strict, the default, applies them to all inserts and updates.
Adding a validator never checks existing documents. Count the ones that would fail:
db.tasks.countDocuments({ $nor: [taskSchema] })
When that is zero and the log is quiet, switch to validationLevel: "strict" and validationAction: "error".
8. Schema versions and online migrations
The schemaVersion field exists so that a shape can change without stopping the application. Suppose version 1 stored labels as a comma-separated string and version 2 stores an array. The sequence is:
Deploy code that reads both versions and writes only version 2.
Loosen the validator so both shapes pass.
Backfill version 1 documents in small batches.
Tighten the validator to version 2 and remove the version 1 code path.
The backfill, with a temporary partial index so that finding the remaining documents never scans the collection:
The example assumes every version 1 document holds a string in labels. Repeating schemaVersion: 1 in the update filter makes the job safe to run while the application is upgrading the same documents: a document already converted is skipped. The pause between batches gives secondaries time to keep up.
9. Write and read concerns
The implicit default write concern is w: "majority", except in replica sets with arbiters where the server may fall back to w: 1. Do not rely on a default for writes whose loss would be visible to a customer. State it, with a timeout:
A wtimeout error means the acknowledgement did not arrive in time. It does not undo the write, which may still replicate, so treat it as "unknown" and make the operation safe to retry.
The default read concern is "local", which can return data that is later rolled back after a failover. That is acceptable for a board. For a read that drives an irreversible action, such as checking a plan limit before provisioning, use read concern "majority". For "read my own write" behaviour when reads may go to secondaries, use a causally consistent session with majority read and write concerns.
W2 (insert a comment and increment commentCount) spans two documents. A counter that is briefly behind is harmless, so write the comment first, then $inc the task, and repair counts with a periodic job. Keep transactions for cases where a partial result is wrong, not merely stale.
10. Planning for sharding
Do not shard until one replica set is no longer enough, but keep the option open:
Every query already includes tenantId, so a shard key that starts with it lets the router target one shard for nearly every operation.
A key of tenantId alone cannot split a very large tenant across chunks. A compound key such as { tenantId: 1, _id: 1 } can.
A unique index on a sharded collection must contain the full shard key as a prefix. { tenantId: 1, email: 1 } satisfies that for a key of tenantId. A unique index on email alone would not.
A shard key can be refined by adding suffix fields, and a collection can be resharded, but resharding a large collection is a heavy operation that needs spare storage on every shard.
Common mistakes
A query without tenantId. It leaks data across tenants and cannot use any of the compound indexes.
An index per field. Three single-field indexes do not add up to one compound index, and each one slows writes.
Mixed id types.projectId stored as a string in one collection and an ObjectId in another makes joins silently return nothing.
Dates as strings and money as doubles. Both work until the first range query or the first rounding complaint.
Turning validation on with error on a live collection. Start with warn and count the violations first.
One giant migration update. An unbatched updateMany over a large collection produces a burst of oplog entries that secondaries may struggle to keep up with, and it cannot be paused.
Checklist
Write the query list with frequencies and shapes before creating a collection.
Use shared collections with tenantId in every document, query and index.
Choose _id deliberately and store references in the identical BSON type.
Use Date, Decimal128 and validated enum strings.
Build one compound index per query shape in ESR order and confirm each with explain("executionStats") on realistic data.
Add unique, partial and TTL indexes for the rules the application depends on.
Attach a $jsonSchema validator: warn first, count violations, then error.
Add schemaVersion on day one and migrate in batches with a guard in the filter.
Set w: "majority" with wtimeout explicitly on important writes.
Keep the leading index field suitable as a shard key prefix.
