[Feature]: School store inventory with per-variant stock reservation and collection

[MOHITKOURAV01]

Problem Statement

The school sells textbooks, workbooks, uniforms, PE kit and stationery from a storeroom, and the storeroom is run on a ledger and a guess. Every year the same two things happen in the same week of term:

• Stock is sold that does not exist. A parent pays for a size 32 blazer, the receipt is written, and the blazer turns out to have gone that morning. The money is taken and the item is owed.
• Stock that exists cannot be found. Nobody knows there are forty size 26 shirts in the second cupboard, so forty more are ordered.

Both come from the same missing thing: there is no number anywhere that says how many of a variant are on the shelf right now, and no operation that decrements it in the same breath as taking the order.

Related but distinct: #282 (cafeteria wallet) is a prepaid balance for food and #284 (financial aid) is about who pays. This is inventory — a countable thing on a shelf, sized, reserved, collected.

Proposed Solution

Two models, because an item and an order genuinely have separate lifetimes and are queried separately.

backend/models/StoreItem.js

• name, sku (unique), category (textbook, workbook, uniform, sportswear, stationery, other), description, unitPrice, taxable
• variants[] — the sized dimension: { variantSku, label, size, stock, reserved, reorderLevel, active }
  • stock is what is physically on the shelf, including reserved units
  • reserved is what is spoken for but not yet handed over
  • available = stock - reserved, exposed as a virtual, never stored
• classesApplicable[], mandatory, status (active, discontinued), supplier, notes

Items without sizes get a single variant labelled standard, so there is exactly one code path.

backend/models/StoreOrder.js

• reference (server-issued), orderedBy / studentName / className
• lines[] — { item, itemName, variantSku, variantLabel, unitPrice, quantity, lineTotal }
• total, status (reserved, ready, collected, cancelled, expired), paymentStatus, reservedUntil, collectedAt, collectedBy, cancelReason

The three things this has to get right

1. Reserving stock is atomic, per variant. Each line is taken with a conditional update that matches the variant and requires the available count to still cover the quantity:

StoreItem.findOneAndUpdate(
  {
    _id: itemId,
    status: 'active',
    variants: {
      $elemMatch: {
        variantSku,
        active: true,
        $expr: { $gte: [{ $subtract: ['$stock', '$reserved'] }, quantity] },
      },
    },
  },
  { $inc: { 'variants.$[v].reserved': quantity } },
  { arrayFilters: [{ 'v.variantSku': variantSku }] }
)

Two parents taking the last blazer means one of them matches nothing and is told so, before any money is discussed.

2. A multi-line order that fails halfway must not leave stock reserved. There is no transaction to lean on, so the order handler reserves line by line and, if any line fails, releases every reservation it has already taken before returning the error. The compensating release is unconditional $inc in the other direction — it cannot itself fail on a guard — and it is the reason the reservation is a separate counter from stock rather than a direct decrement. Half-applied inventory is worse than a rejected order.

3. A reservation expires. reservedUntil is set from a configurable hold window. An expiry sweep endpoint releases anything past it back to available stock and marks the order expired, so an order somebody abandoned does not hold a uniform out of circulation for the rest of term. Collection moves the units out of reserved and out of stock in one update — that is the moment the item leaves the shelf, and it is the only place stock goes down.

Also

• Stock adjustments (receive, damage, correction) are an explicit endpoint with a reason and an actor, not a general PATCH on the item. An inventory that can be edited freely is an inventory nobody trusts.
• A low-stock report lists every variant at or below its reorderLevel, which is the ordering problem the school actually has.

API — /api/store

Method	Path	Who
POST	/items	admin
GET	/items	any signed-in user — catalogue with availability
GET	/items/:id	any signed-in user
PATCH	/items/:id	admin
POST	/items/:id/variants	admin
PATCH	/items/:id/variants/:variantSku/stock	admin — reason-coded adjustment
GET	/low-stock	admin
POST	/orders	any signed-in user — atomic, multi-line, compensating
GET	/orders	admin
GET	/my-orders	any signed-in user
GET	/orders/:id	owner / admin
PATCH	/orders/:id/ready	admin
PATCH	/orders/:id/collect	admin — moves stock, not just status
PATCH	/orders/:id/cancel	owner / admin — releases the reservation
POST	/orders/expire	admin — sweep
GET	/stats	admin

Frontend

A /store page. Families browse by category, see real availability per size, and place an order that gives them a reference and a collection window. Admins get the counter view: today's orders to hand over, the low-stock list, and stock adjustment with a reason.

Alternative Approaches

Decrement stock directly on order and skip reserved. Then a cancelled or abandoned order has to add stock back, and any failure between the two writes invents or destroys inventory. Keeping the reserved counter separate means the shelf count only ever changes when something physically moves.

One document per physical unit. Correct, and absurd for 400 identical exercise books.

Let the counter go negative and reconcile later. That is the current situation with extra steps.

Affected Area

• New page
• New component
• Authentication (owner-scoped orders, admin-scoped stock)
• Other: two new Mongoose models, controller and route module

Mockups / Additional Context

The compensating release in the multi-line order path is the part worth reviewing carefully. It is the closest thing to a transaction this codebase can have without assuming a replica set, and it is only correct because releasing a reservation can never be refused.