Versioned Menus and Order-Time Snapshots: Protecting the Business Record

Most ordering systems treat a menu as a living document. You update the price of a latte, the old price is gone. You swap the milk supplier, the old ingredient list is gone. For a side project or a prototype, that's fine. For a business that serves food to real people, it's a liability.

I used to work as a backend engineer for an insurance company where historic data was treated as sacred. We had entire tables dedicated to tracking when a single field value changed. Before that, I worked at a pub where customers regularly made custom orders. Pricing was guesswork, records were incomplete, and if something went wrong — an allergic reaction, a pricing dispute — there was no reliable history to look back on. Those two experiences shaped how I approached the menu system for Dursley Donkey Coffee.

The Problem with Overwriting

Imagine a coffee shop updates a latte recipe — switching from one milk supplier to another, changing the allergen profile in the process. Under a simple overwrite model, the old recipe is gone. If a customer had an allergic reaction from a latte they ordered last month, and the shop needs to prove what ingredients were actually in that drink, the current menu item won't help. It reflects today's recipe, not the one that was served.

This isn't hypothetical. Food businesses have legal obligations around allergen disclosure. If you can't reconstruct what was served at the point of sale, you have a compliance gap. Beyond legal risk, there's the business analysis side — if prices change over time, historical revenue reports need to reflect what was actually charged, not what the current price happens to be.

The answer is versioning. Menu items aren't updated in place; they're versioned. And when an order is placed, the exact state of every ingredient, modification, and allergen is snapshotted into the order record. The menu can change freely without corrupting the historical record.

Menu Item Versioning

Every menu item belongs to a group identified by a UUID (menu_item_group_id). Within that group, each version gets an incrementing version number and a status — either draft or published:

CREATE TABLE menu_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    menu_item_group_id TEXT NOT NULL,
    version INTEGER NOT NULL DEFAULT 1,
    status TEXT NOT NULL DEFAULT 'draft' CHECK(status IN ('draft', 'published')),
    name TEXT NOT NULL,
    base_price_pence INTEGER NOT NULL,
    category TEXT NOT NULL CHECK(category IN ('hot_drinks', 'cold_drinks', 'food')),
    effective_from DATETIME,
    effective_until DATETIME,
    published_at DATETIME,
    published_by TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Published items cannot be edited — with the sole exception of toggling available on or off. If you need to change a published latte's recipe, you create a new version:

func handleNewMenuItemVersion(db *sql.DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		// ...
		// Check if a draft already exists for this group
		var existingDraftID int64
		err = db.QueryRow(
			"SELECT id FROM menu_items WHERE menu_item_group_id = ? AND status = 'draft'",
			source.MenuItemGroupID,
		).Scan(&existingDraftID)
		if err == nil {
			errorResponse(w, http.StatusConflict, "a draft version already exists for this item")
			return
		}

		// Get max version
		var maxVersion int
		db.QueryRow(
			"SELECT COALESCE(MAX(version), 0) FROM menu_items WHERE menu_item_group_id = ?",
			source.MenuItemGroupID,
		).Scan(&maxVersion)

		tx, err := db.Begin()
		// ...
		// Create new draft version
		result, err := tx.Exec(
			`INSERT INTO menu_items (menu_item_group_id, version, status, name, description,
			 base_price_pence, category, default_size_id, available, image_url)
			 VALUES (?, ?, 'draft', ?, ?, ?, ?, ?, 0, ?)`,
			source.MenuItemGroupID, maxVersion+1,
			source.Name, source.Description, source.BasePricePence,
			source.Category, source.DefaultSizeID, source.ImageURL,
		)

		newID, _ := result.LastInsertId()

		// Copy sizes, ingredients, and mod options from source
		tx.Exec("INSERT INTO menu_item_sizes ... SELECT ?, ... FROM menu_item_sizes WHERE menu_item_id = ?", newID, sourceID)
		tx.Exec("INSERT INTO menu_item_ingredients ... SELECT ?, ... FROM menu_item_ingredients WHERE menu_item_id = ?", newID, sourceID)
		tx.Exec("INSERT INTO menu_item_mod_options ... SELECT ?, ... FROM menu_item_mod_options WHERE menu_item_id = ?", newID, sourceID)
		// ...
	}
}

The new draft is a complete copy of the published version — sizes, ingredients, modification options, everything. The admin can modify it freely without affecting what's currently live. Only one draft can exist per group at a time, preventing divergent edits.

When the draft is ready, publishing it archives the previous version:

// Archive any existing published version for this group
tx.Exec(
	`UPDATE menu_items SET effective_until = CURRENT_TIMESTAMP, available = 0
	 WHERE menu_item_group_id = ? AND status = 'published'
	   AND effective_until IS NULL AND id != ?`,
	item.MenuItemGroupID, id,
)

// Publish this version
tx.Exec(
	`UPDATE menu_items SET status = 'published', available = 1,
	 effective_from = CURRENT_TIMESTAMP, published_at = CURRENT_TIMESTAMP,
	 published_by = ? WHERE id = ?`,
	userID, id,
)

The old version isn't deleted. It gets an effective_until timestamp and stays in the database. You can query the full version history for any menu item group and see exactly what was live at any point in time.

Ingredient Versioning

Ingredients follow the same temporal pattern. When a supplier changes or a price updates, the old ingredient is soft-closed and a new one is created:

// Close old version
tx.Exec("UPDATE ingredients SET effective_until = CURRENT_TIMESTAMP WHERE id = ?", id)

// Create new version with updated cost/supplier
result, err := tx.Exec(
	"INSERT INTO ingredients (name, cost_pence, unit, supplier, supplier_url, in_stock) VALUES (?, ?, ?, ?, ?, ?)",
	existing.Name, newCost, existing.Unit, newSupplier, newSupplierURL, existing.InStock,
)

newID, _ := result.LastInsertId()

// Copy allergen links
tx.Exec(
	"INSERT INTO ingredient_allergens (ingredient_id, allergen_id) SELECT ?, allergen_id FROM ingredient_allergens WHERE ingredient_id = ?",
	newID, id,
)

// Cascade FK updates to all active references
tx.Exec("UPDATE menu_item_ingredients SET ingredient_id = ? WHERE ingredient_id = ?", newID, id)
tx.Exec("UPDATE modification_items SET ingredient_id = ? WHERE ingredient_id = ?", newID, id)
tx.Exec("UPDATE menu_item_mod_options SET swaps_ingredient_id = ? WHERE swaps_ingredient_id = ?", newID, id)
tx.Exec("UPDATE stock_swap_overrides SET ingredient_id = ? WHERE ingredient_id = ?", newID, id)

The cascade of FK updates is critical. When whole milk gets a new version, every menu item, modification, and stock override that references it needs to point to the new ID — all in the same transaction. The old ingredient row stays in the database with its original cost and supplier data, so any order that referenced it at purchase time still has a valid trail.

One detail worth noting: ingredient costs are stored as REAL rather than INTEGER. Sub-penny precision matters when you're tracking per-unit costs — 0.12p per ml of milk is meaningless as an integer, but it adds up to a meaningful cost at 350ml per large latte.

Order-Time Snapshots

Versioning keeps the menu history intact, but orders need something more — a complete snapshot of exactly what was ordered, at the prices and costs that applied at that moment. The order doesn't reference the current menu item; it captures its own copy of everything.

When an order is placed, the handler resolves the full ingredient list (including any modifications), then writes everything into snapshot tables within a single transaction:

// 7. Insert snapshots (all in one DB transaction)
tx, err := db.Begin()
// ...

for _, item := range resolved {
	// Snapshot the item with its name, size, price, and cost at time of order
	itemResult, err := tx.Exec(
		`INSERT INTO order_items (order_id, menu_item_id, size_id, quantity,
		 item_name, size_name, unit_price_pence, unit_cost_pence)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		orderID, item.MenuItemID, item.SizeID, item.Quantity,
		item.ItemName, item.SizeName, item.UnitPricePence, item.UnitCostPence,
	)
	orderItemID, _ := itemResult.LastInsertId()

	// Snapshot each resolved ingredient
	for _, ing := range item.Ingredients {
		lineCost := int(math.Round(ing.Quantity * ing.CostPence))
		tx.Exec(
			`INSERT INTO order_item_ingredients
			 (order_item_id, ingredient_id, ingredient_name, quantity, unit, cost_pence)
			 VALUES (?, ?, ?, ?, ?, ?)`,
			orderItemID, ing.IngredientID, ing.IngredientName, ing.Quantity, ing.Unit, lineCost,
		)
	}

	// Snapshot each applied modification
	for _, mod := range item.Modifications {
		tx.Exec(
			`INSERT INTO order_item_modifications
			 (order_item_id, modification_item_id, mod_option_id, modification_name,
			  mod_type, price_pence, cost_pence)
			 VALUES (?, ?, ?, ?, ?, ?, ?)`,
			orderItemID, mod.ModificationItemID, mod.ModOptionID,
			mod.ModificationName, mod.ModType, mod.PricePence, mod.CostPence,
		)
	}

	// Snapshot allergens — recalculated from the final post-modification ingredient set
	if len(ingredientIDs) > 0 {
		tx.Exec(
			`INSERT INTO order_item_allergens (order_item_id, allergen_id, allergen_name)
			 SELECT DISTINCT ?, ia.allergen_id, a.name
			 FROM ingredient_allergens ia
			 JOIN allergens a ON a.id = ia.allergen_id
			 WHERE ia.ingredient_id IN (` + placeholders + `)`,
			args...,
		)
	}
}

Four levels of snapshot data per order item: the item itself (name, price, cost), its resolved ingredients (with quantities and per-unit costs), its applied modifications (including default mods that weren't explicitly requested), and its allergens.

The allergen snapshot is particularly important. Allergens are recalculated from the final ingredient set — after swaps and add-ons have been applied. If someone orders a latte with oat milk swapped in, the allergen snapshot reflects oat milk's allergens, not the original dairy milk's. This means the order record shows exactly what allergens were present in what was actually served, not what the base recipe contains.

Why This Matters for the Business

The combination of versioning and snapshots creates a complete audit trail. At any point, you can answer:

• What recipe was live when this order was placed? Check the menu item's effective_from and effective_until against the order timestamp.
• What ingredients were actually in this drink? Check order_item_ingredients — it's the resolved list after modifications.
• What allergens were disclosed? Check order_item_allergens — computed from the post-modification ingredients.
• What was the cost to the business? Each order item has unit_cost_pence computed from ingredient quantities and costs at order time.
• How has pricing changed over time? Query the version history to see every price point a menu item has had.

For a coffee shop doing tax returns, this means revenue figures are exact — they reflect what was actually charged, not reconstructed from current prices. For allergen compliance, the record shows what was served, not what the menu says today.

The Draft/Published Workflow in the Admin

The admin dashboard enforces the versioning workflow directly. Published items show a lock banner — you can toggle availability but nothing else. To make changes, you create a new version, which copies everything into a draft:

The draft can be freely edited: change prices, add or remove ingredients, reconfigure modifications. Four independent dirty-state checks track whether details, sizes, ingredients, or mod options have unsaved changes, so the admin knows exactly which sections need saving.

When the draft is published, the old version archives and the new one goes live. The transition is atomic — one transaction archives the old, publishes the new. There's no window where both versions are active or neither is.

Trade-offs

The snapshot approach means data duplication. Every order stores ingredient names, modification names, and allergen names as strings, even though they exist in reference tables. That's deliberate — the reference data can change, but the order record must not. Storage is cheap; data integrity in a legal context is not.

The versioning model means the menu_items table grows over time. For a coffee shop with a modest menu, this is negligible. For a chain with thousands of items and frequent changes, you'd want a cleanup policy for archived versions past a certain age — but "past a certain age" in a food business context might be years, not months.

The N+1 query pattern during order placement — loading each modification individually — is a known trade-off of readable code over query efficiency. For a coffee shop where orders contain 1-5 items with 0-3 modifications each, this is imperceptible. For a high-throughput system, you'd batch-load modifications per item.

Lessons Learned

Historic data is not optional for businesses that serve food. My first instinct as a developer was to keep the schema simple — one row per menu item, update in place. My experience building systems for an insurance client taught me that the data you delete today is the data someone asks for tomorrow. For a food business, "tomorrow" might be a solicitor's letter about an allergic reaction.

Snapshots at the point of sale decouple the order from the menu. Once an order is placed, it doesn't matter what happens to the menu item, the ingredient, or the allergen list. The order record is self-contained. This is the same principle behind invoice line items in accounting — you don't reference the current product price, you record the price that was charged.

Versioning is cheaper than you think. The publish workflow adds one extra step for the admin, and the storage cost is negligible. What it buys is the ability to answer "what was this menu item six months ago?" without guessing. That's worth the extra transaction.