formulation/docs/culinary-data-model.md

# Culinary data model

## Direction

Schema version 2 is a vendor-neutral culinary domain model designed to map
cleanly to relational tables while remaining portable, reviewable YAML. Astro is
a presentation consumer only. It must not become the authority for quantities,
conversions, nutrition, allergens, purchasing, or calculated values.

`schema_version` describes a document's data contract. Recipes represent their
current canonical state. Names used for search or display belong in ingredient
`aliases`; canonical references always use stable entity IDs.

## Canonical and derived boundaries

Canonical writable records live in SQLite. They include authored or observed
facts: recipes, ingredients, measurements, provenance, suppliers, packages,
and price observations. YAML under `culinary/` is portable seed/interchange
data, while generated site projections and exports are downstream products.
Neither is an independently writable source of truth.

Derived recipe records contain reproducible nutrition, allergen rollups, and
costs. They identify the recipe, calculation version, calculation time, and an
input fingerprint. Derived values are disposable and must never
be copied back into canonical source fields as though they were observations.

## Entity model

The schema set in `container/schemas/v2/` defines these prospective relational
entities:

| Entity | Main relationships |
| --- | --- |
| Recipe | components; ordered lines and steps; ingredients or sub-recipes |
| Ingredient | aliases; sourced density/measure observations; nutrition/allergen mappings |
| Unit | dimension and conversion to a base unit within that dimension |
| Prep action | optional default edible-yield factor |
| Source mapping | ingredient, purchasing-item, or recipe nutrition/allergen evidence |
| Supplier | purchasing items |
| Purchasing item | ingredient, supplier, package, dated prices, product mappings |
| Equipment | referenced by recipe steps |
| Collection | ordered recipe references for menus or collections |
| Derived recipe | calculated cost, nutrition, allergens, completeness, and warnings |

Recipe components, component lines, and preparation steps are embedded because
they form a recipe aggregate and are edited together. Reusable facts
remain independent records referenced by stable IDs.

Equipment is a reusable, vendor-neutral entity. A recipe may declare equipment
needed anywhere in the process with `equipment_ids`; individual steps may add
their own `equipment_ids` to show when each item is used. The displayed setup
list is the union of both sets, without duplicating equipment definitions in
recipe files.

Allergen mappings and suppliers are optional capabilities. A purchasing item
may record an exact product, package, and price without identifying a formal
supplier. Recipes and ingredients never require allergen or supplier records.

## Quantities, scaling, and yield

An `amount` is always a numeric quantity plus a normalized `unit_id`. A recipe
declares amount, percentage, or hybrid scaling. Hybrid records include both
canonical amounts and percentages; calculation tooling must verify agreement
against the declared basis.

Recipe yield is explicit and identifies whether it is measured, estimated, or
theoretical. A finished measured yield is preferred for per-100-gram nutrition
and costing. Serving count and serving size are separate optional facts.

Prep actions may override their default `yield_factor` on a recipe line. The
recipe amount is the usable quantity entering the formula; the factor determines
the larger as-purchased quantity required for costing. For example, 100 grams of
usable onion at an 80% prep yield requires purchasing 125 grams. Cooking loss
that affects the entire recipe belongs in recipe yield, not an ingredient
purchase factor.

In operational recipe management, the finished recipe yield is
authoritative for costing, portioning, and per-weight nutrition. Record the
actual finished weight after cooking and handling; this single measurement
captures evaporation, absorption, rendering, and material left on equipment.
Ingredient-specific trimming, peeling, and draining remain explicit prep-action
yield factors. Cooking water deliberately added to rice is a normal recipe input.

## Units, density, and household measures

Universal unit conversion applies only within a dimension: mass-to-mass,
volume-to-volume, time-to-time, and so on. Volume-to-mass conversion requires
ingredient-specific evidence.

For normalized units, `base_conversion` means:
`base value = source value × factor + offset`. Mass resolves to grams, volume
to milliliters, time to minutes, count to each, length to millimeters, and
temperature to Celsius. US volume units are identified explicitly; an
unqualified international "cup" is not assumed.

An ingredient density measurement stores a mass, a volume, physical state,
temperature when relevant, and provenance. This is intentionally represented
as an observed ratio rather than a context-free `density` scalar. A rounded
culinary approximation can coexist with a laboratory measurement without
pretending they have equal precision.

Ingredient `measure_conversions` cover non-density measures such as one clove,
one packed cup, or one drained can. Preparation state belongs on the conversion
because sifted, packed, chopped, and whole materials may have different bulk
measures.

## USDA FoodData Central

USDA FoodData Central mappings are provenance records, not attributes that
define an ingredient. A reviewed mapping records the FDC ID, FoodData Central
data type, exact description, retrieval date, and the normalized nutrient
values imported from that record. Candidate mappings remain distinguishable
from reviewed mappings.

Prefer Foundation Foods for suitable basic foods, followed by SR Legacy or
FNDDS when they are a better semantic match. Use a Branded record only for the
specific purchasing item or when the canonical ingredient intentionally means
that exact product. Prepared components should normally derive nutrition from
their recipes instead of being forced onto an approximate FDC record.

The USDA API key must never be stored in the repository. Future import tooling
should propose candidates, cache source payloads or normalized facts with their
retrieval dates, and require review before a mapping becomes authoritative.

## Canonical storage

SQLite is the canonical application store. The files in `culinary/` are a
portable import/export dataset and initial seed, not a second writable source of
truth. Recipes without authored instructions contain one explicit TODO step.
Formula-only conversions use a nominal 100 g basis and a
theoretical yield until those values are replaced by observed production data.

Application and agent changes must use validated domain save functions and
SQLite transactions. Direct SQL is reserved for schema-aware maintenance where
no domain operation exists. Reset/import commands flow from YAML into SQLite and
therefore overwrite the current store; export commands flow from SQLite into a
portable representation.

Astro reads SQLite through the application data layer. Generated projections
support read-only presentation, and interactive calculators are Preact islands
supplied with resolved, typed recipe data. Astro, Preact, and projections remain
presentation consumers; culinary calculations and editing originate in SQLite
and shared domain tools.