Silent Write Failures in MongoDB — 16MB Document Wall

// Connect to MongoDB using the Node.js driver (mongosh syntax works identically)
// Run this in mongosh or as a Node.js script with the mongodb package

// --- Step 1: Switch to (or create) our working database ---
use('ecommerce_store');

// --- Step 2: Insert two product documents with intentionally DIFFERENT shapes ---
// Notice: simple_mug has no variants; custom_tshirt has a nested variants array.
// In SQL this would require a separate 'product_variants' table and a JOIN.
// In MongoDB it's just an array field inside the document — one read gets everything.

db.products.insertMany([
  {
    // A simple product — flat structure, no variants needed
    name: 'Ceramic Coffee Mug',
    sku: 'MUG-001',
    price_usd: 12.99,
    stock_count: 150,
    category: 'kitchenware',
    tags: ['ceramic', 'handmade', 'dishwasher-safe'],
    created_at: new Date('2024-01-15')
    // No 'variants' field — and that's fine. MongoDB won't error on a missing field.
  },
  {
    // A complex product with nested variants (size + colour combinations)
    // This structure would require 3 tables in a relational schema.
    // Here it lives in one document — one read, no JOINs.
    name: 'Custom Logo T-Shirt',
    sku: 'TSH-042',
    base_price_usd: 24.99,
    category: 'apparel',
    tags: ['cotton', 'customizable', 'unisex'],
    variants: [
      { size: 'S',  color: 'black', stock: 80  },
      { size: 'M',  color: 'black', stock: 120 },
      { size: 'L',  color: 'white', stock: 60  }
    ],
    customization_options: {
      max_logo_size_cm: 10,
      allowed_positions: ['chest', 'back', 'sleeve']
    },
    created_at: new Date('2024-03-22')
  }
]);

// --- Step 3: Query all apparel products ---
// The filter object mirrors the document shape — just use the field name
const apparelProducts = db.products.find(
  { category: 'apparel' },                       // filter
  { name: 1, base_price_usd: 1, _id: 0 }        // projection: only return these fields
).toArray();

console.log('Apparel products found:', JSON.stringify(apparelProducts, null, 2));

// --- Step 4: Query inside a nested array using dot notation ---
// Find products that have a size 'M' variant in stock
const hasMedium = db.products.find(
  { 'variants.size': 'M' },   // dot-notation queries nested fields and array elements
  { name: 1, _id: 0 }
).toArray();

console.log('Products with size M variant:', JSON.stringify(hasMedium, null, 2));

use('saas_platform');

// ─────────────────────────────────────────
// CREATE — Register a new user
// insertOne returns an object with insertedId — always check it
// ─────────────────────────────────────────
const insertResult = db.users.insertOne({
  email: 'priya.sharma@example.com',
  display_name: 'Priya Sharma',
  hashed_password: '$2b$12$exampleHashedPasswordHere',
  plan: 'free',
  address: {
    city: 'Mumbai',
    country: 'IN'
  },
  permissions: ['read', 'comment'],
  joined_at: new Date(),
  last_login: null   // null is valid — she hasn't logged in yet
});

console.log('Inserted ID:', insertResult.insertedId);
// Inserted ID: ObjectId('664a1f3b2c1d4e5f6a7b8c9d')

// ─────────────────────────────────────────
// READ — Find users in Mumbai on the free plan
// Dot-notation queries nested sub-document fields directly
// Array field with a scalar value checks for array membership automatically
// ─────────────────────────────────────────
const mumbaiFreeUsers = db.users.find(
  {
    'address.city': 'Mumbai',  // dot-notation: queries the nested 'city' field
    plan: 'free'
  },
  { email: 1, display_name: 1, _id: 0 }  // projection: include only these fields
).toArray();

console.log('Mumbai free-plan users:', mumbaiFreeUsers);

// ─────────────────────────────────────────
// RANGE QUERY — Users who joined in the last 30 days
// ─────────────────────────────────────────
const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);
const recentUsers = db.users.find(
  { joined_at: { $gte: thirtyDaysAgo } },
  { email: 1, joined_at: 1, _id: 0 }
).sort({ joined_at: -1 }).toArray();

console.log('Recent signups:', recentUsers.length);

// ─────────────────────────────────────────
// UPDATE — Priya upgrades to 'pro' and gets write permission
//
// $set: modifies ONLY the named fields — everything else is untouched
// $push: appends ONE item to an array without overwriting the array
// $addToSet: like $push but ignores the item if it already exists
//
// DANGER: updateOne(filter, { plan: 'pro' }) WITHOUT $set
// replaces the ENTIRE document with { plan: 'pro' } — email gone, everything gone
// ─────────────────────────────────────────
const updateResult = db.users.updateOne(
  { email: 'priya.sharma@example.com' },  // filter: which document to update
  {
    $set:  { plan: 'pro', last_login: new Date() },  // surgical field update
    $push: { permissions: 'write' }                   // append to array
  }
);

console.log('Matched:', updateResult.matchedCount, 'Modified:', updateResult.modifiedCount);
// Matched: 1  Modified: 1

// ─────────────────────────────────────────
// VERIFY — Read back the updated document to confirm
// ─────────────────────────────────────────
const updatedUser = db.users.findOne(
  { email: 'priya.sharma@example.com' },
  { email: 1, plan: 1, permissions: 1, last_login: 1, _id: 0 }
);

console.log('Updated user:', JSON.stringify(updatedUser, null, 2));

// ─────────────────────────────────────────
// UPSERT — Update if exists, insert if not
// { upsert: true } creates the document when the filter matches nothing
// Useful for 'create or update' patterns without a separate existence check
// ─────────────────────────────────────────
const upsertResult = db.users.updateOne(
  { email: 'new.user@example.com' },
  {
    $set: { display_name: 'New User', plan: 'free' },
    $setOnInsert: { joined_at: new Date(), permissions: ['read'] }  // only on new doc
  },
  { upsert: true }
);

console.log('Upserted:', upsertResult.upsertedCount === 1 ? 'inserted new doc' : 'updated existing');

// ─────────────────────────────────────────
// DELETE — Remove a test or spam account
// deleteOne removes the FIRST match only — it won't throw if nothing matches
// ─────────────────────────────────────────
const deleteResult = db.users.deleteOne({ email: 'spam-bot@junk.io' });
console.log('Deleted count:', deleteResult.deletedCount);
// Deleted count: 1 (or 0 if the email did not exist — no error thrown either way)

use('saas_platform');

// ─────────────────────────────────────────
// INDEXES — Match your indexes to your actual query patterns
// ─────────────────────────────────────────

// Single-field unique index: login lookups always filter by email
// unique: true enforces no duplicate emails at the database level
db.users.createIndex(
  { email: 1 },
  { unique: true, name: 'idx_users_email_unique' }
);

// Compound index for the admin dashboard: filters by plan, sorts by join date
// Field order: equality filter (plan) first, sort field (joined_at) second
// This allows the query to use the index for both filtering AND sorting
db.users.createIndex(
  { plan: 1, joined_at: -1 },
  { name: 'idx_users_plan_joined' }
);

// Sparse index: only indexes documents where last_login exists
// Useful when many documents don't have the field at all
db.users.createIndex(
  { last_login: -1 },
  { sparse: true, name: 'idx_users_last_login' }
);

// TTL index: automatically deletes documents after a time period
// Useful for session tokens, temporary verification codes, expiring cache docs
db.password_reset_tokens.createIndex(
  { created_at: 1 },
  { expireAfterSeconds: 3600, name: 'idx_tokens_ttl_1h' }  // auto-delete after 1 hour
);

// Text index: enables full-text search on multiple string fields
db.products.createIndex(
  { name: 'text', description: 'text' },
  { name: 'idx_products_text_search' }
);

// ─────────────────────────────────────────
// EXPLAIN — Verify every production query uses an index
// Run this BEFORE shipping a new query — never assume
// ─────────────────────────────────────────
const queryPlan = db.users
  .find({ plan: 'pro' })
  .sort({ joined_at: -1 })
  .explain('executionStats');

console.log('Winning stage:',    queryPlan.queryPlanner.winningPlan.inputStage.stage);
console.log('Docs examined:',    queryPlan.executionStats.totalDocsExamined);
console.log('Docs returned:',    queryPlan.executionStats.nReturned);
console.log('Execution time ms:', queryPlan.executionStats.executionTimeMillis);

// Good: stage is IXSCAN, examined equals returned (ratio 1:1)
// Bad:  stage is COLLSCAN, examined >> returned (ratio 1000:1 or worse)

// ─────────────────────────────────────────
// SCHEMA DESIGN — Embed vs Reference examples side by side
// ─────────────────────────────────────────

// EMBED: Order stores its own line items
// Rationale: line items belong exclusively to this order, always read together,
// bounded in size (a realistic order has 1-50 items, never 50,000)
db.orders.insertOne({
  order_number: 'ORD-20240312-001',
  customer_id: ObjectId('664a1f3b2c1d4e5f6a7b8c9d'),  // reference to users
  status: 'shipped',
  placed_at: new Date('2024-03-12'),
  line_items: [
    // Embedded sub-documents — no separate collection needed for this pattern
    { sku: 'MUG-001', name: 'Ceramic Coffee Mug',   qty: 2, unit_price_usd: 12.99 },
    { sku: 'TSH-042', name: 'Custom Logo T-Shirt',  qty: 1, unit_price_usd: 24.99 }
  ],
  total_usd: 50.97
});

// REFERENCE: Blog post stores author as an ObjectId, not embedded author data
// Rationale: author exists independently, writes many posts.
// If we embedded author name and the author changes their name,
// we'd need to update every post they ever wrote — one update vs thousands.
db.blog_posts.insertOne({
  title: 'Getting Started with MongoDB Indexes',
  slug: 'mongodb-indexes-guide',
  author_id: ObjectId('664a1f3b2c1d4e5f6a7b8c9d'),  // reference — not embedded
  body: 'Indexes are the single biggest performance lever in MongoDB...',
  published_at: new Date('2024-04-01'),
  tags: ['mongodb', 'performance', 'indexing'],
  view_count: 0
});

// Create index on the foreign key so $lookup and find({author_id: ...}) is fast
db.blog_posts.createIndex({ author_id: 1 }, { name: 'idx_posts_author_id' });

console.log('Indexes created and schema examples inserted.');

use('ecommerce_store');

// ─────────────────────────────────────────
// EXAMPLE 1: Revenue by product for March 2024
// This replaces a multi-JOIN GROUP BY in SQL.
// Pipeline order: $match first (filter early), then $unwind, $group, $sort, $limit, $project
// ─────────────────────────────────────────
const revenueByProduct = db.orders.aggregate([

  // Stage 1 — $match: filter to March 2024 shipped/delivered orders
  // THIS MUST BE FIRST — reduces 2M docs to ~50K before any grouping
  {
    $match: {
      placed_at: {
        $gte: new Date('2024-03-01'),
        $lt:  new Date('2024-04-01')
      },
      status: { $in: ['shipped', 'delivered'] }  // exclude cancelled orders
    }
  },

  // Stage 2 — $unwind: flatten line_items array
  // Before: 1 order doc with 3 line items
  // After:  3 docs, one per line item, each carrying the parent order fields
  { $unwind: '$line_items' },

  // Stage 3 — $group: calculate revenue and units per SKU
  {
    $group: {
      _id: '$line_items.sku',
      total_revenue: {
        $sum: { $multiply: ['$line_items.qty', '$line_items.unit_price_usd'] }
      },
      total_units_sold: { $sum: '$line_items.qty' },
      order_count: { $sum: 1 }
    }
  },

  // Stage 4 — $sort: highest revenue first
  { $sort: { total_revenue: -1 } },

  // Stage 5 — $limit: top 5 products only
  { $limit: 5 },

  // Stage 6 — $project: rename _id to sku, round currency to 2 decimal places
  {
    $project: {
      _id: 0,
      sku: '$_id',
      total_revenue:    { $round: ['$total_revenue', 2] },
      total_units_sold: 1,
      order_count: 1
    }
  }

]).toArray();

console.log('Top 5 products by March revenue:');
console.log(JSON.stringify(revenueByProduct, null, 2));

// ─────────────────────────────────────────
// EXAMPLE 2: $lookup — Join blog posts with author display name
// MongoDB's LEFT JOIN equivalent — runs per-document, so foreign field MUST be indexed
// ─────────────────────────────────────────
const postsWithAuthors = db.blog_posts.aggregate([

  // $match first — filter before joining to reduce the number of $lookup operations
  { $match: { published_at: { $gte: new Date('2024-01-01') } } },

  {
    $lookup: {
      from: 'users',           // the collection to join against
      localField: 'author_id', // field in blog_posts
      foreignField: '_id',     // field in users — must be indexed
      as: 'author_details'     // result stored as an array field
    }
  },

  // $lookup always produces an array — unwind since each post has exactly one author
  { $unwind: '$author_details' },

  // $project: shape the final output — pull author name up from the nested join result
  {
    $project: {
      _id: 0,
      title: 1,
      slug: 1,
      author_name: '$author_details.display_name',
      published_at: 1,
      tags: 1
    }
  }

]).toArray();

console.log('Posts with authors:', JSON.stringify(postsWithAuthors, null, 2));

// ─────────────────────────────────────────
// EXAMPLE 3: Cohort analysis — users grouped by signup month
// Real-world reporting pattern used in SaaS dashboards
// ─────────────────────────────────────────
const signupCohorts = db.users.aggregate([
  {
    $group: {
      _id: {
        year:  { $year:  '$joined_at' },
        month: { $month: '$joined_at' }
      },
      new_users: { $sum: 1 },
      pro_users: {
        $sum: { $cond: [{ $eq: ['$plan', 'pro'] }, 1, 0] }  // conditional count
      }
    }
  },
  { $sort: { '_id.year': 1, '_id.month': 1 } },
  {
    $project: {
      _id: 0,
      month: {
        $concat: [
          { $toString: '$_id.year' }, '-',
          { $toString: '$_id.month' }
        ]
      },
      new_users: 1,
      pro_users: 1,
      conversion_rate: {
        $round: [{ $multiply: [{ $divide: ['$pro_users', '$new_users'] }, 100] }, 1]
      }
    }
  }
]).toArray();

console.log('Signup cohorts with conversion rates:', JSON.stringify(signupCohorts, null, 2));

// Using mongosh's GridFS methods or the Node.js driver
// The following examples work in mongosh directly

// ─────────────────────────────────────────
// WRITE a file to GridFS
// mongosh provides the 'mongofiles' command, but we can also use the 'fs' collection directly
// Here we use the 'GridFSBucket' pattern available in Node.js driver.

// For mongosh, use the 'mongofiles' shell utility or:
// In Node.js with the 'mongodb' package:

const { MongoClient, GridFSBucket } = require('mongodb');
const fs = require('fs');

async function uploadFile() {
  const client = new MongoClient('mongodb://localhost:27017');
  await client.connect();
  const db = client.db('myfiles');

  const bucket = new GridFSBucket(db, { bucketName: 'user_uploads' });

  // Upload a file; the driver splits it into 255KB chunks automatically
  const readableStream = fs.createReadStream('./profile_photo_hires.jpg');
  const uploadStream = bucket.openUploadStream('profile_photo_hires.jpg', {
    metadata: { userId: ObjectId('...') }  // attach arbitrary metadata
  });

  readableStream.pipe(uploadStream);

  uploadStream.on('finish', () => {
    console.log('File uploaded successfully. ID:', uploadStream.id);
    // For mongosh, you can verify:
    // db.user_uploads.files.findOne({ filename: '...' })
    client.close();
  });
}

// ─────────────────────────────────────────
// READ a file from GridFS
async function downloadFile(fileId) {
  const client = new MongoClient('mongodb://localhost:27017');
  await client.connect();
  const db = client.db('myfiles');
  const bucket = new GridFSBucket(db, { bucketName: 'user_uploads' });

  const downloadStream = bucket.openDownloadStream(ObjectId(fileId));
  const writeStream = fs.createWriteStream('./downloaded_photo.jpg');

  downloadStream.pipe(writeStream);

  writeStream.on('finish', () => {
    console.log('File downloaded successfully.');
    client.close();
  });
}

// ─────────────────────────────────────────
// LIST metadata for all files in a bucket
async function listFiles() {
  const client = new MongoClient('mongodb://localhost:27017');
  await client.connect();
  const db = client.db('myfiles');
  const cursor = db.collection('user_uploads.files').find({});
  await cursor.forEach(doc => {
    console.log(doc.filename, doc.length, doc.uploadDate);
  });
  client.close();
}