apps/backend/src/infrastructure/database/migrations/0008_sprint_c_completion.sqlSQL
-- Sprint C completion support.
create table if not exists recipe_steps (
id uuid primary key default gen_random_uuid(),
recipe_version_id uuid not null references recipe_versions(id) on delete cascade,
sort_order integer not null,
title text,
instruction text not null,
created_at timestamptz not null default now(),
constraint recipe_steps_order_unique unique (recipe_version_id, sort_order)
);
create table if not exists recipe_step_translations (
recipe_step_id uuid not null references recipe_steps(id) on delete cascade,
locale text not null,
title text,
instruction text not null,
primary key (recipe_step_id, locale)
);
create table if not exists menu_projection_jobs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
job_type text not null,
entity_id uuid not null,
status text not null default 'pending',
created_at timestamptz not null default now(),
processed_at timestamptz,
last_error text,
constraint menu_projection_jobs_status_check
check (status in ('pending','running','completed','failed'))
);
create unique index if not exists idx_menu_projection_job_pending_unique
on menu_projection_jobs(job_type, entity_id)
where status in ('pending','running');