apps/backend/src/infrastructure/database/migrations/0006_sprint_c_menu_recipe_foundation.sqlSQL
-- Sprint C — units, allergens, ingredients, recipes, products, prices, modifiers and availability.
create table if not exists units (
code text primary key,
dimension text not null,
base_unit_code text,
factor_to_base numeric(24,12),
created_at timestamptz not null default now(),
constraint units_dimension_check check (dimension in ('mass','volume','count','other'))
);
create table if not exists unit_translations (
unit_code text not null references units(code) on delete cascade,
locale text not null,
display_name text not null,
abbreviation text not null,
primary key (unit_code, locale)
);
create table if not exists allergens (
code text primary key,
sort_order integer not null default 0,
created_at timestamptz not null default now()
);
create table if not exists allergen_translations (
allergen_code text not null references allergens(code) on delete cascade,
locale text not null,
display_name text not null,
short_name text,
primary key (allergen_code, locale)
);
create table if not exists ingredients (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
sku text,
base_unit_code text not null references units(code) on delete restrict,
name text not null,
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint ingredients_org_sku_unique unique (organization_id, sku),
constraint ingredients_status_check check (status in ('active','inactive','archived'))
);
create index if not exists idx_ingredients_org_status
on ingredients(organization_id, status);
create table if not exists ingredient_translations (
ingredient_id uuid not null references ingredients(id) on delete cascade,
locale text not null,
name text not null,
description text,
primary key (ingredient_id, locale)
);
create table if not exists ingredient_allergens (
ingredient_id uuid not null references ingredients(id) on delete cascade,
allergen_code text not null references allergens(code) on delete restrict,
relation text not null default 'contains',
source text not null default 'manual',
updated_at timestamptz not null default now(),
updated_by uuid references user_accounts(id) on delete set null,
primary key (ingredient_id, allergen_code),
constraint ingredient_allergens_relation_check check (relation in ('contains','may_contain')),
constraint ingredient_allergens_source_check check (source in ('manual','supplier','imported_verified'))
);
create table if not exists recipes (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
name text not null,
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint recipes_status_check check (status in ('active','archived'))
);
create table if not exists recipe_translations (
recipe_id uuid not null references recipes(id) on delete cascade,
locale text not null,
name text not null,
description text,
primary key (recipe_id, locale)
);
create table if not exists recipe_versions (
id uuid primary key default gen_random_uuid(),
recipe_id uuid not null references recipes(id) on delete restrict,
version_number integer not null,
status text not null default 'draft',
yield_quantity numeric(24,8) not null,
yield_unit_code text not null references units(code) on delete restrict,
notes text,
published_at timestamptz,
created_at timestamptz not null default now(),
created_by uuid references user_accounts(id) on delete set null,
constraint recipe_versions_recipe_version_unique unique (recipe_id, version_number),
constraint recipe_versions_status_check check (status in ('draft','published','retired')),
constraint recipe_versions_yield_positive check (yield_quantity > 0)
);
create unique index if not exists idx_recipe_one_published_version
on recipe_versions(recipe_id)
where status = 'published';
create table if not exists recipe_version_translations (
recipe_version_id uuid not null references recipe_versions(id) on delete cascade,
locale text not null,
instructions text,
notes text,
primary key (recipe_version_id, locale)
);
create table if not exists recipe_components (
id uuid primary key default gen_random_uuid(),
recipe_version_id uuid not null references recipe_versions(id) on delete cascade,
component_type text not null,
ingredient_id uuid references ingredients(id) on delete restrict,
subrecipe_id uuid references recipes(id) on delete restrict,
quantity numeric(24,8) not null,
unit_code text not null references units(code) on delete restrict,
waste_factor numeric(12,8) not null default 0,
sort_order integer not null default 0,
constraint recipe_components_type_check check (component_type in ('ingredient','subrecipe')),
constraint recipe_components_quantity_positive check (quantity > 0),
constraint recipe_components_waste_check check (waste_factor >= 0 and waste_factor < 1),
constraint recipe_components_target_check check (
(component_type='ingredient' and ingredient_id is not null and subrecipe_id is null)
or
(component_type='subrecipe' and subrecipe_id is not null and ingredient_id is null)
)
);
create index if not exists idx_recipe_components_version
on recipe_components(recipe_version_id, sort_order);
create table if not exists menu_categories (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
parent_id uuid references menu_categories(id) on delete restrict,
name text not null,
sort_order integer not null default 0,
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1
);
create table if not exists menu_category_translations (
category_id uuid not null references menu_categories(id) on delete cascade,
locale text not null,
name text not null,
description text,
primary key (category_id, locale)
);
create table if not exists products (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
category_id uuid references menu_categories(id) on delete restrict,
recipe_id uuid references recipes(id) on delete restrict,
sku text,
name text not null,
product_type text not null default 'dish',
status text not null default 'active',
kitchen_relevant boolean not null default true,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint products_org_sku_unique unique (organization_id, sku),
constraint products_type_check check (product_type in ('dish','drink','retail','service','other')),
constraint products_status_check check (status in ('active','inactive','archived'))
);
create index if not exists idx_products_org_status
on products(organization_id, status);
create table if not exists product_translations (
product_id uuid not null references products(id) on delete cascade,
locale text not null,
name text not null,
short_description text,
long_description text,
primary key (product_id, locale)
);
create table if not exists product_prices (
id uuid primary key default gen_random_uuid(),
product_id uuid not null references products(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
channel text not null default 'onsite',
currency_code char(3) not null,
amount_minor bigint not null,
valid_from timestamptz not null default now(),
valid_until timestamptz,
created_at timestamptz not null default now(),
constraint product_prices_amount_nonnegative check (amount_minor >= 0)
);
create index if not exists idx_product_prices_lookup
on product_prices(product_id, location_id, channel, valid_from desc);
create table if not exists modifier_groups (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
name text not null,
min_select integer not null default 0,
max_select integer,
sort_order integer not null default 0,
constraint modifier_group_min_check check (min_select >= 0),
constraint modifier_group_max_check check (max_select is null or max_select >= min_select)
);
create table if not exists modifier_group_translations (
modifier_group_id uuid not null references modifier_groups(id) on delete cascade,
locale text not null,
name text not null,
primary key (modifier_group_id, locale)
);
create table if not exists modifiers (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
name text not null,
recipe_id uuid references recipes(id) on delete restrict,
status text not null default 'active',
constraint modifiers_status_check check (status in ('active','inactive','archived'))
);
create table if not exists modifier_translations (
modifier_id uuid not null references modifiers(id) on delete cascade,
locale text not null,
name text not null,
primary key (modifier_id, locale)
);
create table if not exists modifier_group_items (
modifier_group_id uuid not null references modifier_groups(id) on delete cascade,
modifier_id uuid not null references modifiers(id) on delete cascade,
sort_order integer not null default 0,
price_delta_minor bigint not null default 0,
primary key (modifier_group_id, modifier_id)
);
create table if not exists product_modifier_groups (
product_id uuid not null references products(id) on delete cascade,
modifier_group_id uuid not null references modifier_groups(id) on delete cascade,
sort_order integer not null default 0,
primary key (product_id, modifier_group_id)
);
create table if not exists menu_availability (
product_id uuid not null references products(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
available boolean not null default true,
reason_code text,
updated_at timestamptz not null default now(),
updated_by uuid references user_accounts(id) on delete set null,
primary key (product_id, location_id)
);
create table if not exists product_allergen_projection (
product_id uuid not null references products(id) on delete cascade,
allergen_code text not null references allergens(code) on delete restrict,
relation text not null,
source_recipe_version_id uuid references recipe_versions(id) on delete set null,
generated_at timestamptz not null default now(),
primary key (product_id, allergen_code),
constraint product_allergen_relation_check check (relation in ('contains','may_contain'))
);
insert into permissions(permission_key, description) values
('menu.read', 'Read menu'),
('menu.manage', 'Manage menu products/categories'),
('menu.prices.manage', 'Manage product prices'),
('menu.availability.manage', 'Manage menu availability'),
('ingredients.read', 'Read ingredients'),
('ingredients.manage', 'Manage ingredients'),
('allergens.manage', 'Manage ingredient allergen facts'),
('recipes.read', 'Read recipes'),
('recipes.manage', 'Manage recipe drafts'),
('recipes.publish', 'Publish recipe versions')
on conflict (permission_key) do nothing;
insert into units(code, dimension, base_unit_code, factor_to_base) values
('g', 'mass', 'g', 1),
('kg', 'mass', 'g', 1000),
('ml', 'volume', 'ml', 1),
('l', 'volume', 'ml', 1000),
('pc', 'count', 'pc', 1)
on conflict (code) do nothing;
insert into unit_translations(unit_code, locale, display_name, abbreviation) values
('g','de-DE','Gramm','g'),
('g','en-GB','gram','g'),
('kg','de-DE','Kilogramm','kg'),
('kg','en-GB','kilogram','kg'),
('ml','de-DE','Milliliter','ml'),
('ml','en-GB','millilitre','ml'),
('l','de-DE','Liter','l'),
('l','en-GB','litre','l'),
('pc','de-DE','Stück','Stk.'),
('pc','en-GB','piece','pc')
on conflict do nothing;
insert into allergens(code, sort_order) values
('GLUTEN', 10),
('CRUSTACEANS', 20),
('EGGS', 30),
('FISH', 40),
('PEANUTS', 50),
('SOY', 60),
('MILK', 70),
('NUTS', 80),
('CELERY', 90),
('MUSTARD', 100),
('SESAME', 110),
('SULPHITES', 120),
('LUPIN', 130),
('MOLLUSCS', 140)
on conflict (code) do nothing;