Published / updated29 Aug 2026

Guided MongoDB Project

Before you start

You need: all of Articles 01–07, and an application to integrate from.

Time: 6–10 hours.

Goal

Demonstrate that you can design a document model from access patterns, enforce what a flexible schema does not, index for real queries, and integrate it from an application.

Assignment

Build a task management database for NexCoding Academy — staff assign and track tasks — plus an application layer over it.

Deliverable	Contents
01-collections.js	Collections with schema validation
02-indexes.js	Indexes, each commented with the query it serves
03-seed.js	Realistic seed data
04-queries.js	The reports below
src/	Repository layer in C# or Python
MODEL.md	Design decisions, with reasons

Every script must run from an empty database, in order, with no manual editing.

Requirements

A task belongs to a school, has a title and description, a status, a priority, a due date, an assignee and a creator. Tasks carry comments and an activity history. Staff have a name, email and role.

• schoolId on every document, in every query, and leading every index
• Unique per school, not globally — a task reference like NCA-T-0012
• Decimal128 for any monetary field
• ISODate for every date — never a string
• Schema validation with bsonType, enum and pattern
• Soft delete — no hard deletes on referenced documents
• A deliberate embed versus reference decision for comments and for history, each justified
• Every report backed by an index, proven with explain

Reports required

1. Open tasks for one assignee, most overdue first.
2. Task counts by status for a school.
3. Tasks due in the next seven days.
4. Tasks with more than five comments.
5. Average days to completion, by priority.
6. Tasks mentioning a search term in the title or description.
7. Staff with no open tasks.
8. Activity for one task, newest first.

Worked example: the model

Start from the reports, not the entities. Reports 1, 2, 3 and 6 all read the task list; report 8 reads one task's history. That split decides the model.

// tasks
{
    _id: ObjectId("t1"),
    schoolId: 1,
    reference: "NCA-T-0012",
    title: "Prepare class 10 mid-term question paper",
    description: "Mathematics, 80 marks, due before the exam board meeting",
    status: "InProgress",
    priority: "High",
    dueDate: ISODate("2024-07-15T00:00:00Z"),

    assignee: {
        staffId: ObjectId("st1"),
        name: "Dr. Mehta",
        email: "mehta@nexcoding.in"
    },

    createdBy: { staffId: ObjectId("st2"), name: "Mrs. Rao" },
    createdAt: ISODate("2024-06-20T09:00:00Z"),
    completedAt: null,

    tags: ["exams", "class-10"],

    recentComments: [
        {
            commentId: ObjectId("c9"),
            staffName: "Mrs. Rao",
            text: "Draft shared for review",
            createdAt: ISODate("2024-06-28T11:00:00Z")
        }
    ],
    commentCount: 7,

    isDeleted: false
}

// comments — referenced, unbounded
{
    _id: ObjectId("c9"),
    schoolId: 1,
    taskId: ObjectId("t1"),
    staffId: ObjectId("st2"),
    staffName: "Mrs. Rao",
    text: "Draft shared for review",
    createdAt: ISODate("2024-06-28T11:00:00Z")
}

// taskActivity — referenced, append-only
{
    _id: ObjectId("a1"),
    schoolId: 1,
    taskId: ObjectId("t1"),
    action: "StatusChanged",
    fromValue: "Open",
    toValue: "InProgress",
    byStaffId: ObjectId("st1"),
    byStaffName: "Dr. Mehta",
    at: ISODate("2024-06-25T14:30:00Z")
}

The decisions, and why:

Decision	Reason
Assignee embedded as a snapshot	The list shows the name; embedding avoids a $lookup on every read
staffId kept alongside the name	The source of truth stays reachable
Comments referenced	Unbounded — the 16 MB limit makes embedding a design error
recentComments capped at three	The task detail page reads one document
commentCount stored	Report 4 has no $size comparison to run
Activity referenced, append-only	Grows without limit, read only on one screen
tags embedded	Bounded, small, always read with the task
isDeleted flag	No foreign keys — a hard delete orphans comments and activity
schoolId everywhere	Nothing else enforces the tenant boundary

assignee.name is duplicated deliberately. A staff rename must update every task:

db.tasks.updateMany(
    { "assignee.staffId": staffId },
    { $set: { "assignee.name": newName } }
)

That is the extended-reference trade: fast reads, one write on a rare change, and one writer for the duplicated value.

commentCount is maintained atomically with the comment insert:

db.tasks.updateOne(
    { _id: taskId, schoolId },
    {
        $inc: { commentCount: 1 },
        $push: { recentComments: { $each: [comment], $sort: { createdAt: -1 }, $slice: 3 } }
    }
)

$slice: 3 keeps the array bounded. $inc is atomic, so two concurrent comments cannot lose a count.

Worked example: validation

db.createCollection("tasks", {
    validator: {
        $jsonSchema: {
            bsonType: "object",
            required: ["schoolId", "reference", "title", "status", "priority",
                       "dueDate", "createdAt", "isDeleted"],
            properties: {
                schoolId:  { bsonType: "int" },
                reference: { bsonType: "string", pattern: "^NCA-T-[0-9]{4}$" },
                title:     { bsonType: "string", minLength: 3, maxLength: 200 },
                status:    { enum: ["Open", "InProgress", "Blocked", "Completed", "Cancelled"] },
                priority:  { enum: ["Low", "Medium", "High", "Critical"] },
                dueDate:   { bsonType: "date" },
                completedAt: { bsonType: ["date", "null"] },
                commentCount: { bsonType: "int", minimum: 0 },
                isDeleted: { bsonType: "bool" },
                assignee: {
                    bsonType: "object",
                    required: ["staffId", "name"],
                    properties: {
                        staffId: { bsonType: "objectId" },
                        name:    { bsonType: "string" }
                    }
                }
            }
        }
    },
    validationLevel: "strict",
    validationAction: "error"
})

bsonType: "date" on dueDate is the most valuable rule here. A string date passes every application test until someone runs report 3, and by then the data is written.

enum on status and priority prevents the inconsistency flexible schema invites — "inprogress", "In Progress" and "InProgress" in one collection, with a report silently missing two thirds.

db.tasks.createIndex({ schoolId: 1, reference: 1 }, { unique: true })

Unique per school, not globally. A global constraint on reference stops the second school using a reference the first already has — invisible with one school of test data.

Worked example: indexes

// Report 1: open tasks for one assignee, most overdue first
// ESR: equality (schoolId, assignee, status), sort (dueDate)
db.tasks.createIndex(
    { schoolId: 1, "assignee.staffId": 1, status: 1, dueDate: 1 },
    { name: "idx_assignee_status_due" }
)

// Report 2: counts by status
db.tasks.createIndex({ schoolId: 1, status: 1 }, { name: "idx_school_status" })

// Report 3: due in the next seven days — range last
db.tasks.createIndex({ schoolId: 1, status: 1, dueDate: 1 }, { name: "idx_school_status_due" })

// Report 4: more than five comments
db.tasks.createIndex({ schoolId: 1, commentCount: -1 }, { name: "idx_school_commentcount" })

// Report 6: text search
db.tasks.createIndex({ title: "text", description: "text" }, { name: "idx_task_text" })

// Report 8: activity for one task, newest first
db.taskActivity.createIndex({ schoolId: 1, taskId: 1, at: -1 }, { name: "idx_activity_task" })

// Comments for a task
db.comments.createIndex({ schoolId: 1, taskId: 1, createdAt: -1 }, { name: "idx_comments_task" })

schoolId leads every index, because every query filters on it.

ESR order — equality, sort, range — in idx_assignee_status_due. Putting dueDate before status would break the prefix for report 2 and force an in-memory sort.

idx_school_status_due is a prefix of nothing else and serves report 3 with the range last.

commentCount stored as a field is what makes report 4 indexable. $size cannot be compared with $gt, and $expr: { $gt: [{ $size: "$comments" }, 5] } cannot use an index at all.

Worked example: a report

Report 1 — open tasks for one assignee, most overdue first.

db.tasks.find({
    schoolId: 1,
    "assignee.staffId": ObjectId("st1"),
    status: { $in: ["Open", "InProgress", "Blocked"] },
    isDeleted: false
})
.sort({ dueDate: 1, _id: 1 })
.limit(20)

Four things that would each be a defect if omitted:

Detail	What breaks without it
schoolId	Another school's tasks appear
isDeleted: false	Deleted tasks reappear — reported as "delete does not work"
_id in the sort	Tasks with the same due date repeat or vanish between pages
$in on status	Listing only "Open" misses in-progress work

Report 5 — average days to completion, by priority:

db.tasks.aggregate([
    { $match: { schoolId: 1, status: "Completed", isDeleted: false, completedAt: { $ne: null } } },
    { $project: {
        priority: 1,
        daysToComplete: {
            $divide: [{ $subtract: ["$completedAt", "$createdAt"] }, 1000 * 60 * 60 * 24]
        }
    }},
    { $group: {
        _id: "$priority",
        averageDays: { $avg: "$daysToComplete" },
        count: { $sum: 1 }
    }},
    { $sort: { averageDays: -1 } }
])

$match is first, so it uses idx_school_status. Moving it after $group would aggregate every task in the collection and discard most of the result.

completedAt: { $ne: null } excludes tasks marked complete without a timestamp — a real data condition that would otherwise produce a nonsense average.

Report 7 — staff with no open tasks:

db.staff.aggregate([
    { $match: { schoolId: 1, isActive: true } },
    { $lookup: {
        from: "tasks",
        let: { staffId: "$_id" },
        pipeline: [
            { $match: {
                $expr: { $eq: ["$assignee.staffId", "$$staffId"] },
                schoolId: 1,
                status: { $in: ["Open", "InProgress"] },
                isDeleted: false
            }},
            { $limit: 1 }
        ],
        as: "openTasks"
    }},
    { $match: { openTasks: { $size: 0 } } },
    { $project: { name: 1, email: 1 } }
])

$limit: 1 inside the lookup stops it fetching every task — only existence matters.

$lookup needs the foreign field indexed or it scans tasks once per staff member.

Submission template

MODEL.md

Access patterns:
  Each report, and what it reads:
  Read/write ratio per collection:

Model:
  Collection list and the shape of each:
  Embed versus reference, per relationship, with the reason:
  What is duplicated, why, and which code is its single writer:
  Cardinality of every array, and the bound on each:

Validation:
  Schema rules, and what each prevents:
  Unique indexes and why they are composite:
  What validation cannot enforce, and where that is handled instead:

Referential integrity:
  Where references exist and what maintains them:
  Deletion strategy, and the obligation it creates on reads:
  How orphans are handled if they occur:

Indexes:
  Index name → report it serves → field order and why:
  explain output for each report, before and after:

Tenant isolation:
  Where schoolId appears:
  The test that fails if it is removed:

Application layer:
  Client lifetime:
  Type mapping decisions, especially money and dates:
  Error translation table:

Compared with SQL Server:
  What this model does better:
  What it does worse:
  Which you would choose for this system, and why:

Deliberately not done, and why:

Verification

Scripts run clean. Drop the database, run 01 to 04 in order on an empty server. No manual edits, no errors.

Validation rejects bad data. Attempt each and record the error:

Attempt	Expected
dueDate as a string	Rejected — document failed validation
status: "inprogress"	Rejected — not in the enum
reference: "T-12"	Rejected — pattern mismatch
Missing schoolId	Rejected — required
Duplicate reference, same school	Rejected — unique index
Same reference, different school	Succeeds — proves the index is composite

That last row is the multi-tenant check, and it is the one most often got wrong.

Reports return correct data. For each of the eight, hand-check at least one row against the seed data. Report 7 must include a staff member with only completed tasks.

Every report uses an index. Run explain("executionStats") on all eight. Record stage, totalDocsExamined and nReturned. No COLLSCAN, and no SORT stage.

Index field order matters. Reorder one index to break ESR and confirm a SORT stage appears.

Tenant isolation holds. Seed two schools. Confirm every query and every repository method filters by schoolId. Then remove it from one and confirm a test fails — an isolation you cannot break on purpose is one you have not tested.

Soft delete works. Delete a task, confirm it disappears from every report, and confirm its comments and activity still exist. Then remove isDeleted: false from one report and confirm it reappears.

Hard delete is demonstrated as wrong. Hard-delete a task with ten comments. Run the comment query and confirm the orphans. Restore and use soft delete.

Money is exact. If a task carries a cost, store it as Decimal128 and sum a hundred of them. Then store it as a plain number and compare.

Comment count stays correct. Add ten comments concurrently from two shells and confirm commentCount is ten — proving $inc rather than read-modify-write.

recentComments stays bounded. Add fifty comments and confirm the array holds three.

Document size is bounded. Add 50,000 activity records for one task. Confirm the task document size is unchanged, then model activity as an embedded array instead and watch it grow.

Application layer. CRUD works, schoolId is in every filter, an invalid id string returns 404 rather than 500, and a duplicate reference surfaces as a domain exception.

Client lifetime. Load-test with a client per request, then a singleton. Compare.

AI practice

Two AI exercises from this track's syllabus. Do both after the project works, and apply Track 18's discipline — every answer is a hypothesis until you have run it.

1. Compare SQL and document models. Ask for the trade-offs between storing exam results as a separate collection and embedding them in the student document — the trade-off, not a recommendation. Then argue your choice using your own document sizes and query patterns. The 16 MB document limit and the cost of updating one element of a large array are the two facts to check for.
2. Review an AI-generated query for unintended matches. Ask for a query finding students with a fee balance. Check three things: does it filter by schoolId? Does a query on an array field match documents where any element matches, when you meant all? Does a missing field match null in a way you did not intend? All three return results that look correct.

Also check that money fields use NumberDecimal, not a plain double. A fee total off by paise is the same failure as FLOAT in SQL Server, in a different syntax.

Track 18 — Reviewing AI-generated code — has the full checklist.

Self-assessment

Your submission is complete when someone can run your scripts on an empty server, read MODEL.md, run each report, and see which decisions were deliberate.

Four specific tests of quality:

• Does the same reference succeed for a second school? If not, the unique index is not composite, and the product cannot onboard a second customer.
• Does an explain show an index for all eight reports? A report with a COLLSCAN on 100,000 documents is a report that will fail in production.
• Does MODEL.md justify every embed-versus-reference decision by cardinality and access pattern? "It seemed simpler" is not a reason.
• Does it say where SQL Server would be the better choice? A submission concluding MongoDB wins everything has not thought about transactions or cross-entity reporting.

Track completion criteria

You understand practical NoSQL concepts, can write common MongoDB CRUD and queries, model nested data, and integrate MongoDB with .NET or Python.

Specifically, you can:

• Explain what a document database stores and how it differs from relational
• Perform every CRUD operation and interpret what each result reports
• Query precisely with operators, projection, sorting and paging
• Use $elemMatch where a naive array query returns the wrong documents
• Decide embed versus reference from cardinality and access pattern
• Add schema validation and composite unique indexes
• Create indexes that measurably change a plan, verified with explain
• Read an explain plan and find the cause of a slow query
• Integrate from an application with correct client lifetime and type mapping
• Say, for a given system, whether a document or relational model is the better fit

The syllabus recommends Track 10 — ASP.NET Core Development or Track 13 — Python Programming Foundation next — the two application platforms this track integrates with.