MongoDB Schema Design skill for Codex

Done well means: each collection’s shape is justified by a written list of the queries it serves, data read together is stored together, nothing grows without bound inside one document, the database enforces the shape with a validator, and every frequent query has an index that matches its equality, sort and range order.

Workflow

• Read AGENTS.md; find the driver or ODM (Node driver, Mongoose, PyMongo/Motor, Spring Data, etc.) and the MongoDB server version (db.version()).
• Write the access-pattern table (below) with the user before proposing documents.
• Sketch documents per entity using the embed/reference rules.
• Apply patterns only where a row in the table calls for them.
• Add a $jsonSchema validator and the index plan.
• Check growth: max array lengths, document size at the 99th percentile in a year.
• Verify with explain plans on realistic sample data.

Access-pattern table

#	Operation	Frequency	Filter	Sort	Returns	Latency target
1	Product page	2k/s	slug	-	product + 10 latest reviews + seller name	< 20 ms
2	Seller dashboard	5/s	sellerId, status	updatedAt desc	product summaries	< 100 ms
3	Add review	50/s	productId	-	write	-

Writes and their frequency matter as much as reads: a field copied into 1M documents is cheap to read and expensive to change.

Embed or reference

Signal	Embed	Reference
Read together on the hot path	Yes	Only if size/growth forbids embedding
Cardinality of the child side	Few, bounded (tens to low hundreds)	Many or unbounded
Child has its own lifecycle or is queried alone	No	Yes
Child is shared by many parents	No (duplication)	Yes, or extended reference
Child changes often, parent read often	Careful: rewrites the whole parent	Yes
Needs atomic update with parent	Yes: single-document writes are atomic	Needs a transaction

Shapes:

• One-to-few (addresses on a user): embed an array.
• One-to-many (orders of a customer): reference from the child (customerId on the order) and index it.
• One-to-squillions (log events of a host): child references parent; never an array on the parent.
• Many-to-many (students and courses): arrays of ids on the side that is queried, kept bounded; or a link collection when both sides are large.

Patterns, and when each earns its place

Pattern	Use when	Shape
Extended reference	A read needs a few fields of a referenced document	Copy {_id, name} of the seller into the product; update copies when the source changes
Subset	Parent has a large child list but the page shows the latest N	Embed the newest 10 reviews; full list in reviews collection
Computed	An aggregate is read far more often than its inputs change	Store reviewCount, ratingAvg; update with $inc on write or a scheduled job
Bucket	Time series or high-volume children	One document per sensor per hour with an array of readings (or use a native time series collection)
Outlier	A few parents have far more children than the rest	Normal embedding plus hasOverflow: true and overflow documents
Polymorphic	Entities share most fields and are queried together	One collection with a type discriminator
Attribute	Many sparse, similar fields that need indexing	attrs: [{k: "color", v: "red"}] with an index on {"attrs.k": 1, "attrs.v": 1}
Schema versioning	Shape must change without a big-bang migration	schemaVersion field; code reads all live versions and upgrades on write
Archive	Old data rarely read	Move to an archive collection or Online Archive by date

For time series, prefer a time series collection (db.createCollection("metrics", { timeseries: { timeField: "ts", metaField: "meta", granularity: "minutes" } })) over a hand-rolled bucket unless you need update patterns it does not support.

Example: product with subset and extended reference

{
  _id: ObjectId("..."),
  slug: "trail-runner-2",
  name: "Trail Runner 2",
  priceCents: 12900,
  seller: { _id: ObjectId("..."), name: "North Gear" },   // extended reference
  ratingAvg: 4.6, reviewCount: 1832,                        // computed
  recentReviews: [                                          // subset, capped at 10
    { _id: ObjectId("..."), userName: "ana", stars: 5, text: "...", at: ISODate("...") }
  ],
  schemaVersion: 2,
  updatedAt: ISODate("...")
}

Keep the subset capped on write:

db.products.updateOne(
  { _id: productId },
  {
    $push: { recentReviews: { $each: [review], $sort: { at: -1 }, $slice: 10 } },
    $inc: { reviewCount: 1 }
  }
);

Validation

db.runCommand({
  collMod: "products",
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["slug", "name", "priceCents", "seller", "schemaVersion"],
    properties: {
      slug: { bsonType: "string", pattern: "^[a-z0-9-]+$" },
      priceCents: { bsonType: ["int", "long"], minimum: 0 },
      recentReviews: { bsonType: "array", maxItems: 10 },
      schemaVersion: { bsonType: "int" }
    }
  }},
  validationLevel: "moderate",   // existing invalid docs are not blocked on unrelated updates
  validationAction: "error"
});

Start with validationAction: "warn" on an existing collection, check logs for violations, fix data, then switch to "error". Mongoose schemas validate only writes made through Mongoose; the server validator covers every client.

Indexes: the ESR rule

Order compound index keys as Equality, then Sort, then Range:

// Query: { sellerId: X, status: "active", priceCents: { $lt: 5000 } } sort { updatedAt: -1 }
db.products.createIndex({ sellerId: 1, status: 1, updatedAt: -1, priceCents: 1 });
db.products.createIndex({ slug: 1 }, { unique: true });
db.orders.createIndex({ customerId: 1, createdAt: -1 });

• Every reference field used for lookups needs an index; MongoDB never creates them for you.
• Partial indexes (partialFilterExpression) for queries on a subset, such as active items.
• Multikey indexes on arrays multiply index entries by array length; another reason to bound arrays. A compound index can include at most one array field per document.
• Limit: 64 indexes per collection; each one slows writes. Remove unused ones after checking $indexStats over a full business cycle.
• Building indexes on large production collections needs confirmation and a quiet window.

Sharding (only if needed)

Shard key = a field in almost every query filter, high cardinality, and writes spread evenly. Monotonic keys (ObjectId, timestamps) alone create a hot shard; use a compound key ({ tenantId: 1, _id: 1 }) or hashed key. Changing a shard key later (reshardCollection) is expensive; decide with the access-pattern table.

Anti-patterns to flag in review

• Arrays that grow with usage (comments, events, followers) on a parent document.
• Documents approaching the 16 MB BSON limit, or routinely above a few hundred KB.
• Relational normalization copied one-to-one: a page needing five $lookup stages.
• Duplicated fields with no plan for updating copies.
• Case-insensitive lookups without a collation index or a normalized field.
• Storing money as double; use integer minor units or Decimal128.
• Relying on multi-document transactions for hot paths; reshape so one document holds what must change atomically.

Verification

• For each access-pattern row, run the query with db.collection.find(filter).sort(s).explain("executionStats") on sample data: IXSCAN, totalDocsExamined close to nReturned, no SORT stage for indexed sorts.
• Object.bsonsize(doc) on the largest realistic document stays well below 16 MB.
• Validator rejects a deliberately bad insert in a test.
• Mongoose or ODM models match the validator (no field the server would reject).

Output

## Model: <collection>
**Serves access patterns:** #1, #2
**Document shape:** <example JSON>
**Patterns used:** subset (recentReviews <= 10), extended reference (seller.name)
**Copies to maintain:** seller.name <- sellers.name, updated by <job/trigger>
**Indexes:** { sellerId: 1, status: 1, updatedAt: -1 } for #2
**Growth bounds:** <array caps, size estimate>
**Validator:** $jsonSchema, action error|warn