From Stock Outage to Menu Recovery: Building a Swap System for a Coffee Shop

I used to work at a pub where custom orders were a constant challenge. Someone would ask for a substitution, and the person taking the order would have to guess the price difference. There was no system for tracking what modifications cost the business versus what the customer was charged. And when we ran out of something, the response was to cross it off a whiteboard and hope everyone noticed.

When I started building Dursley Donkey Coffee's ordering system, I knew modifications had to be first-class citizens — not bolted on later. If a customer can swap oat milk for whole milk in a latte, the system should know exactly what that swap costs, what to charge, and what happens to the menu if whole milk runs out entirely.

Modifications as a Data Model

Every menu item can have modification options. Each option belongs to a modification category (like "Milk" or "Syrup"), has a type (swap, add_on, or adjustment), and carries both a customer-facing price and a business-facing cost:

type MenuItemModOption struct {
	ID                   int64    `json:"id"`
	MenuItemID           int64    `json:"menu_item_id"`
	ModificationItemID   int64    `json:"modification_item_id"`
	SizeID               *int64   `json:"size_id"`
	PricePence           int      `json:"price_pence"`
	CostPence            int      `json:"cost_pence"`
	AddQuantity          *float64 `json:"add_quantity"`
	ModType              string   `json:"mod_type"`
	IsDefault            bool     `json:"is_default"`
	SwapsIngredientID    *int64   `json:"swaps_ingredient_id"`
}

The three types handle different real-world scenarios:

• Swap: Replace one ingredient with another. A swap knows which ingredient it replaces (swaps_ingredient_id) and keeps the original quantity — if the base recipe uses 200ml of whole milk, swapping to oat milk uses 200ml of oat milk.
• Add-on: Add a new ingredient. An extra shot of espresso, a pump of syrup. Has its own quantity that adds to the resolved ingredient list.
• Adjustment: A price-only change — no ingredient impact. Useful for things like "extra hot" where there's a charge but no ingredient difference.

The is_default flag matters. Some modifications are part of the base recipe — whole milk in a latte is a default swap option. Default mods don't add to the customer's price (they're already factored into the base price), but they're still tracked so the system knows the full ingredient composition.

Resolving Modifications at Order Time

When an order comes in, the handler resolves the complete ingredient list for each item. It starts with the base recipe for the selected size, then applies each modification:

switch mo.ModType {
case "swap":
	if mo.SwapsIngredientID != nil && modIngredientID != nil {
		for i := range ingredients {
			if ingredients[i].IngredientID == *mo.SwapsIngredientID {
				var newIng resolvedIngredient
				db.QueryRow(
					"SELECT id, name, unit, cost_pence, in_stock FROM ingredients WHERE id = ? AND effective_until IS NULL",
					*modIngredientID,
				).Scan(&newIng.IngredientID, &newIng.IngredientName, &newIng.Unit, &newIng.CostPence, &newIng.InStock)

				newIng.Quantity = ingredients[i].Quantity // Keep the same quantity from base recipe
				ingredients[i] = newIng
				break
			}
		}
	}

case "add_on":
	if modIngredientID != nil && mo.AddQuantity != nil {
		var addIng resolvedIngredient
		db.QueryRow(
			"SELECT id, name, unit, cost_pence, in_stock FROM ingredients WHERE id = ? AND effective_until IS NULL",
			*modIngredientID,
		).Scan(&addIng.IngredientID, &addIng.IngredientName, &addIng.Unit, &addIng.CostPence, &addIng.InStock)
		addIng.Quantity = *mo.AddQuantity

		// Merge if ingredient already exists in the list
		found := false
		for i := range ingredients {
			if ingredients[i].IngredientID == addIng.IngredientID {
				ingredients[i].Quantity += addIng.Quantity
				found = true
				break
			}
		}
		if !found {
			ingredients = append(ingredients, addIng)
		}
	}
}

One constraint enforced here: only one swap per modification category. You can't swap milk to oat and to soy in the same drink. The handler validates this before applying modifications:

swapsByCategory := map[int64]string{}
for _, info := range modInfos {
	if info.modType == "swap" {
		if _, exists := swapsByCategory[info.categoryID]; exists {
			errorResponse(w, http.StatusBadRequest, "only one swap allowed per category: "+info.catName)
			return
		}
		swapsByCategory[info.categoryID] = info.catName
	}
}

After all modifications are applied, costs are computed from the resolved ingredient list:

unitCostPence := 0
for _, ing := range ingredients {
	unitCostPence += int(math.Round(ing.Quantity * ing.CostPence))
}
for _, mod := range appliedMods {
	unitCostPence += mod.CostPence
}

This gives the business an accurate per-item cost that accounts for every modification. A latte with oat milk has a different cost than a latte with whole milk, and the system tracks that difference precisely.

When Things Run Out

The interesting problem isn't modifications — it's what happens when an ingredient runs out. If the shop is out of whole milk, every drink that uses whole milk is affected. But many of those drinks already have a swap option configured (oat milk, soy milk). Rather than pulling those drinks off the menu entirely, the system can activate the swap automatically.

When staff mark an ingredient as out of stock, the backend returns an impact analysis:

func buildStockImpact(db *sql.DB, ingredientID int64) StockImpactResponse {
	var resp StockImpactResponse
	resp.IngredientID = ingredientID
	db.QueryRow("SELECT name FROM ingredients WHERE id = ?", ingredientID).Scan(&resp.IngredientName)
	resp.AffectedItems = []StockImpactItem{}

	// Collect affected items first to avoid nested open cursors (SQLite locking)
	type affectedItem struct {
		id   int64
		name string
	}
	var affected []affectedItem

	itemRows, err := db.Query(
		`SELECT DISTINCT mi.id, mi.name FROM menu_items mi
		 JOIN menu_item_ingredients mii ON mii.menu_item_id = mi.id
		 WHERE mii.ingredient_id = ? AND mi.status = 'published' AND mi.effective_until IS NULL`,
		ingredientID,
	)
	// ... collect into slice, then close cursor before running dependent queries

	for _, a := range affected {
		item := StockImpactItem{MenuItemID: a.id, MenuItemName: a.name, SwapOptions: []StockSwapOption{}}

		// Find available swap options for this ingredient on this menu item
		swapRows, err := db.Query(
			`SELECT mmo.id, modi.name, COALESCE(ri.name, '')
			 FROM menu_item_mod_options mmo
			 JOIN modification_items modi ON modi.id = mmo.modification_item_id
			 LEFT JOIN ingredients ri ON ri.id = modi.ingredient_id
			 WHERE mmo.menu_item_id = ? AND mmo.swaps_ingredient_id = ? AND mmo.mod_type = 'swap'
			   AND modi.effective_until IS NULL`,
			a.id, ingredientID,
		)
		// ... collect swaps, check if already approved

		item.HasSwaps = len(item.SwapOptions) > 0
		resp.AffectedItems = append(resp.AffectedItems, item)
	}
	return resp
}

The response tells the admin exactly which menu items are affected and what swap alternatives exist for each one. The admin can then approve swaps selectively — maybe oat milk is fine for the latte but not for the hot chocolate.

The Override Cycle

The swap approval is where it gets interesting. When a swap is approved during a stock outage, the system needs to change the mod option's behaviour — make it the default, remove the charge (you shouldn't charge for oat milk if whole milk isn't available). But it also needs to remember the original state so it can restore everything when the ingredient comes back.

for _, approval := range req.Approvals {
	// Validate the swap and check replacement is in stock
	// ...

	// Read current state before overriding
	var currentIsDefault bool
	var currentPricePence int
	db.QueryRow("SELECT is_default, price_pence FROM menu_item_mod_options WHERE id = ?",
		approval.ModOptionID).Scan(&currentIsDefault, &currentPricePence)

	// Save original state
	tx.Exec(
		`INSERT OR IGNORE INTO stock_swap_overrides
		 (ingredient_id, menu_item_id, mod_option_id, was_default_before, was_price_before, approved_by)
		 VALUES (?, ?, ?, ?, ?, ?)`,
		id, approval.MenuItemID, approval.ModOptionID, currentIsDefault, currentPricePence, approvedBy,
	)

	// Activate swap: make it default and free
	tx.Exec("UPDATE menu_item_mod_options SET is_default = 1, price_pence = 0 WHERE id = ?",
		approval.ModOptionID)
}

When the ingredient comes back in stock, the original values are restored and the overrides are cleaned up:

// Restore original is_default and price_pence from overrides
for _, ov := range overrides {
	isDefault := 0
	if ov.wasDefaultBefore {
		isDefault = 1
	}
	db.Exec(
		"UPDATE menu_item_mod_options SET is_default = ?, price_pence = ? WHERE id = ?",
		isDefault, ov.wasPriceBefore, ov.modOptionID,
	)
}

// Remove all overrides for this ingredient
db.Exec("DELETE FROM stock_swap_overrides WHERE ingredient_id = ?", id)

The public-facing menu checks stock availability before listing items. An item with an out-of-stock ingredient is hidden — unless there's an approved swap override:

func isMenuItemStockAvailable(db *sql.DB, menuItemID int64) bool {
	// ...
	for _, is := range ingredients {
		if !is.inStock {
			var overrideCount int
			db.QueryRow(
				"SELECT COUNT(*) FROM stock_swap_overrides WHERE ingredient_id = ? AND menu_item_id = ?",
				is.id, menuItemID,
			).Scan(&overrideCount)
			if overrideCount == 0 {
				return false
			}
		}
	}
	return true
}

The effect is that the customer sees the menu adapt in real time. Whole milk runs out, the latte stays on the menu with oat milk as the new default, and no charge for what was previously an upcharge. When whole milk is restocked, everything reverts automatically.

Seeing the Cost Impact

Building the modification and stock system was only half the problem. The business owner needs to see what modifications actually cost. A swap might seem neutral — same drink, different milk — but oat milk costs more per litre than whole milk. If a popular modification is eating into margins, the owner needs to know.

The admin dashboard includes a stats page for each menu item with a modification impact calculator. The core calculation resolves cost per size, accounting for swap and add-on ingredient costs:

function getModCostForSize(modOption: MenuItemModOptionDetail, sizeId: number): number {
  const modItem = allModItems.find((m) => m.id === modOption.modification_item_id);
  const linkedIng = modItem?.ingredient_id
    ? allIngredients.find((i) => i.id === modItem.ingredient_id) : null;

  if (modOption.mod_type === "swap" && modOption.swaps_ingredient_id) {
    // Remove old ingredient cost, add new
    const oldIngCost = getIngredientCostForSize(sizeId)
      .find((i) => i.ingredientId === modOption.swaps_ingredient_id)?.cost ?? 0;
    if (!linkedIng) return -oldIngCost;
    // Infer quantity from original recipe or size volume
    const origRow = ingredients.find(
      (i) => i.ingredient_id === modOption.swaps_ingredient_id && i.size_id === sizeId
    );
    let qty = origRow ? origRow.quantity : linkedIng.unit === "ml" && sizeInfo?.amount_ml
      ? sizeInfo.amount_ml : 1;
    return qty * linkedIng.cost_pence - oldIngCost;
  } else if (modOption.mod_type === "add_on" && linkedIng) {
    const qty = modOption.add_quantity && modOption.add_quantity > 0 ? modOption.add_quantity : 1;
    return qty * linkedIng.cost_pence;
  }
  return 0;
}

Each modification shows a profit/loss indicator — green if the charge covers the cost increase, amber if it breaks even, red if the business loses money on that mod. Staff see this while configuring menu items, so pricing decisions are informed by real ingredient costs rather than guesswork.

The interactive calculator lets the owner toggle modifications on and off to see how combinations affect margins. Enable "oat milk swap" and "extra shot" together and watch the donut chart update — ingredient cost grows, profit shrinks, margin percentage changes in real time. If a modification is frequently requested but barely profitable, the owner has the data to adjust the price. If it's infrequently used and loss-making, maybe it should be removed to reduce stock waste.

This is the white-label product thinking. The software isn't just an ordering system — it's a tool that helps a business owner make better pricing decisions. In the future, frequently ordered modifications and popular combinations will be surfaced so prices can be adjusted to benefit both the business and the customer. High-volume mods might justify a lower per-unit price; low-volume ones might not justify the stock investment at all.

How It Works at the Counter

The intended workflow for a stock outage:

1. Staff member notices whole milk is running low, opens the ingredients page in the admin panel.
2. They toggle the stock status to "out of stock."
3. The system immediately shows which menu items are affected and what swap options exist for each.
4. Staff approves oat milk as the replacement for affected lattes and cappuccinos. The swap becomes the default at no charge.
5. The customer-facing menu updates in real time — lattes are still available, now with oat milk.
6. When whole milk is restocked, staff toggles it back. Original defaults and pricing are restored automatically.

In future updates, the stock outage will feed into a shopping list so the business knows what to reorder. And there's a path to push notifications — alerting customers with items in their basket that a stock change affects their order, or auto-offering cancellations for orders that can no longer be fulfilled.

Trade-offs

The stock swap system adds a table (stock_swap_overrides) and an approval workflow that a simpler system wouldn't need. For a coffee shop with a dozen ingredients, this might feel like over-engineering. But the alternative — pulling menu items entirely when one ingredient runs out — means lost sales. If a latte uses whole milk and whole milk is out, but oat milk is available and cheaper to the business, the swap system keeps revenue flowing.

The impact analysis queries hit multiple tables — menu items, ingredients, mod options, existing overrides. For a small menu this is fast. As the menu grows, the buildStockImpact function would benefit from fewer round trips, potentially using CTEs to walk the relationships in a single query. The explicit pattern of collecting rows into slices before running dependent queries is a practical concession to SQLite's single-writer architecture.

The modification cost calculator computes everything client-side from raw data. This keeps the implementation flexible — any filter combination works without a new API endpoint — but it means the browser does more work as the menu grows. For the target market of independent coffee shops with menus of 20-50 items, this is more than adequate. If the white-label product scales to larger operations, server-side aggregation would be the next step.

Lessons Learned

Build modifications in from the start, not as an afterthought. My pub experience taught me that customers will always want customisation. If the system doesn't support it natively, staff will work around it — and those workarounds won't be tracked, priced, or reported on. Modifications as first-class data means every custom order has an accurate cost, an accurate price, and a complete record.

Show the business the numbers, not just the options. An admin panel that lets you configure a swap is useful. An admin panel that shows you the swap costs more than you're charging for it is valuable. The profit/loss indicators turn configuration into a business decision, which is what this software is actually for.

Design for the real-world recovery cycle. Stock outages are temporary. The system needs to handle not just the outage but the return to normal — automatically, without manual cleanup. Recording the original state before an override and restoring it when the outage ends means the admin doesn't have to remember what prices were before. The system remembers for them.