Section 5: The Pantry, the Recipe Card, and the Receipt - Data Modeling

Executive Summary: This section builds the schema that the rest of the design merely operates: ingredients as the atomic entity (D1), recipes as priced compositions with the required-vs-default distinction that makes the 86 cascade computable (D7), immutable published menu versions (D4/D6), the one-row-per-86 availability overlay that is the inventory model (D8), and orders whose lines carry both the delta (intent) and the resolved, priced snapshot (the receipt) in a single row (D2/D4). We write DDL for the four tables where the shape is the argument, run order #1042 and Marco's 12:35 avocado 86 through it, and defend the two choices interviewers always probe: the JSONB snapshot inside a relational order, and the inventory table that deliberately does not exist.

The interviewer caps the API discussion with the question this topic was always heading toward: "Show me the tables." Here is where Section 1's bet — that the ingredient, not the salad, is the atomic entity — either pays out or collapses. The Staff-level move is to let the access patterns from Sections 3 and 4 shape the schema before any CREATE TABLE is typed: menus are read as versioned documents from a CDN, availability changes one bit at a time on a human's clock, orders are written once inside a transaction and reread as receipts forever. Three different rhythms; the schema must honor all three without letting any one of them contaminate the others.

The Pantry: ingredients

CREATE TABLE ingredients (
    id           TEXT PRIMARY KEY,            -- 'avocado'
    name         TEXT NOT NULL,               -- 'Avocado'
    category     TEXT NOT NULL,               -- base | protein | cheese | crunch | fruit_veg | premium | dressing | extras
    price_cents  INTEGER NOT NULL,            -- 200: what an *add* costs; money is integers, never floats
    nutrition    JSONB NOT NULL,              -- {"calories": 234, "fat_g": 21, ...}
    allergens    TEXT[] NOT NULL DEFAULT '{}',-- {'dairy'}, {'tree_nut'} — queryable, not prose
    active       BOOLEAN NOT NULL DEFAULT TRUE
);

Every column earns its place downstream. price_cents is how D3's server-side pricing has something authoritative to compute from. nutrition is JSONB because its shape will grow (sodium today, added sugars after the next labeling law) and nothing ever queries into it at order time — it rolls up. allergens is a real array, not a description string, because Section 7's allergen filter is a query (WHERE NOT allergens && '{tree_nut}'), and a query can be tested; prose cannot. This table is the D1 hill: roughly 60 rows that pricing, availability, nutrition, allergens, and BYO all compute from.