From Messy Menu Imports to Safer Data Modelling for Hospitality SaaS

A menu dump can contain three versions of the same drink, an item without a category and a price that is not really a price yet. The first import may appear to work; the second is where the data model starts asking questions.

Problem

The import pipeline had to handle records that might be incomplete, ambiguous, or duplicated. The goal was to turn messy source data into a useful menu model without losing the ability to recover when the import was only partially correct.

Constraints and risks

The main risk is bad data becoming a hidden source of truth. If a category maps incorrectly, a service period disappears, or a price is guessed incorrectly, staff will have to fix it later under pressure.

I also had to be careful not to make the importer too strict. Hospitality data is messy in normal ways, so the system should flag problems without rejecting everything that is not perfect.

Design / Investigation

I split the model into clearer parts:

Model part	What it holds
items	The menu item itself
categories	Grouping and navigation
service_periods	Breakfast, lunch, dinner, or custom windows
allergens	Safety and disclosure data
translations	Localized labels and descriptions
promotions	Discounts or offers attached to items or groups

That separation makes validation easier because each layer can fail independently without corrupting the whole import.

Example validation pattern:

const ImportedMenuItem = z.object({
  name: z.string().min(1),
  category: z.string().optional(),
  price: z.number().nullable(),
  allergens: z.array(z.string()).default([]),
});

Implementation approach

I prefer a staged import:

1. Parse the source file.
2. Validate obvious shape errors.
3. Detect duplicates by a natural key.
4. Stage unresolved records for review.
5. Let the admin fix problems before publishing.

That staged flow matters because menu import failures are usually operational, not theoretical. Staff need to know what imported, what did not, and what can be edited safely after the fact.

For incomplete pricing, I keep the state explicit instead of guessing:

Price state	Meaning
known	The source gave a clear price
unknown	The price was missing
estimated	A temporary value was supplied for review
hidden	The item should not show a price yet

What I would do differently

I would make duplicate detection more visible to the admin earlier in the flow. It is better to catch a duplicate before a publish step than to explain it after the menu has already been edited by hand.

I would also keep the importer log more structured so the recovery screen can show exactly why a row failed without forcing staff to read raw validation output.

The correction

The investigation found browser-side sequential upserts, fresh UUIDs on every import and silent suppression of missing promotion targets. The replacement design adds source-identity tracking and an import_menu_dump PostgreSQL RPC with an explicit matching order. The remaining work is to carry that transaction-safe path through the importer UI and tests.

I now prefer an import that stops with a legible mismatch to one that succeeds by making uncertain data look permanent.