apps/backend/src/infrastructure/database/migrations/0020_sprint_j_inventory_foundation.sqlSQL
-- Sprint J — inventory, stock movements, counts, waste and reorder foundation.
create table if not exists stock_items (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
ingredient_id uuid references ingredients(id) on delete set null,
name text not null,
base_unit_code text not null references units(code) on delete restrict,
is_active boolean not null default true,
is_countable boolean not null default true,
is_trackable 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
);
create index if not exists idx_stock_items_org_active
on stock_items(organization_id, is_active);
create table if not exists storage_locations (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
name text not null,
storage_type text not null default 'other',
is_active boolean not null default true,
sort_order integer not null default 0,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint storage_locations_type_check check(
storage_type in ('dry','fridge','freezer','bar','cellar','kitchen','prep','other')
),
constraint storage_locations_location_name_unique unique(location_id, name)
);
create table if not exists stock_item_locations (
stock_item_id uuid not null references stock_items(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
default_storage_location_id uuid references storage_locations(id) on delete set null,
is_tracked boolean not null default true,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
primary key(stock_item_id, location_id)
);
create table if not exists stock_movements (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
stock_item_id uuid not null references stock_items(id) on delete restrict,
storage_location_id uuid not null references storage_locations(id) on delete restrict,
movement_type text not null,
quantity_delta numeric(24,8) not null,
unit_code text not null references units(code) on delete restrict,
normalized_quantity_delta numeric(24,8) not null,
normalized_unit_code text not null references units(code) on delete restrict,
source_type text,
source_id uuid,
consumption_generation integer not null default 1,
business_date date not null,
reason text,
created_by_employee_id uuid references employees(id) on delete set null,
created_at timestamptz not null default now(),
constraint stock_movement_delta_nonzero check(normalized_quantity_delta <> 0),
constraint stock_movement_type_check check(
movement_type in (
'purchase_receipt','sale_consumption','production',
'transfer_in','transfer_out','waste','return',
'manual_adjustment','inventory_count_adjustment',
'breakage','staff_meal'
)
)
);
create index if not exists idx_stock_movements_location_item_time
on stock_movements(location_id, stock_item_id, created_at);
create index if not exists idx_stock_movements_storage_item_time
on stock_movements(storage_location_id, stock_item_id, created_at);
create index if not exists idx_stock_movements_source
on stock_movements(source_type, source_id);
create unique index if not exists idx_stock_sale_consumption_idempotency
on stock_movements(source_id, stock_item_id, consumption_generation)
where source_type='order_item' and movement_type='sale_consumption';
create table if not exists stock_balance_projection (
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
stock_item_id uuid not null references stock_items(id) on delete restrict,
storage_location_id uuid not null references storage_locations(id) on delete restrict,
quantity_normalized numeric(24,8) not null default 0,
normalized_unit_code text not null references units(code) on delete restrict,
updated_at timestamptz not null default now(),
projection_version bigint not null default 1,
primary key(location_id, stock_item_id, storage_location_id)
);
create index if not exists idx_stock_balance_location_item
on stock_balance_projection(location_id, stock_item_id);
create index if not exists idx_stock_balance_storage_item
on stock_balance_projection(storage_location_id, stock_item_id);
create table if not exists reorder_rules (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
stock_item_id uuid not null references stock_items(id) on delete cascade,
reorder_point numeric(24,8) not null,
target_stock numeric(24,8),
unit_code text not null references units(code) on delete restrict,
is_active 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 reorder_rules_location_item_unique unique(location_id, stock_item_id),
constraint reorder_rule_nonnegative check(reorder_point >= 0 and (target_stock is null or target_stock >= 0))
);
create table if not exists inventory_counts (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
storage_location_id uuid references storage_locations(id) on delete restrict,
status text not null default 'draft',
scope jsonb not null default '{}'::jsonb,
started_at timestamptz,
snapshot_at timestamptz,
reviewed_at timestamptz,
posted_at timestamptz,
cancelled_at timestamptz,
started_by_employee_id uuid references employees(id) on delete set null,
posted_by_employee_id uuid references employees(id) on delete set null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint inventory_count_status_check check(
status in ('draft','in_progress','review','posted','cancelled')
)
);
create index if not exists idx_inventory_counts_location_status_time
on inventory_counts(location_id, status, started_at);
create table if not exists inventory_count_lines (
id uuid primary key default gen_random_uuid(),
inventory_count_id uuid not null references inventory_counts(id) on delete cascade,
stock_item_id uuid not null references stock_items(id) on delete restrict,
storage_location_id uuid not null references storage_locations(id) on delete restrict,
expected_quantity_at_snapshot numeric(24,8) not null,
counted_quantity numeric(24,8),
unit_code text not null references units(code) on delete restrict,
normalized_counted_quantity numeric(24,8),
difference_quantity numeric(24,8),
notes text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint inventory_count_line_unique unique(inventory_count_id, stock_item_id, storage_location_id)
);
create index if not exists idx_inventory_count_lines_count_item
on inventory_count_lines(inventory_count_id, stock_item_id);
create table if not exists waste_records (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
stock_item_id uuid not null references stock_items(id) on delete restrict,
storage_location_id uuid not null references storage_locations(id) on delete restrict,
quantity numeric(24,8) not null,
unit_code text not null references units(code) on delete restrict,
normalized_quantity numeric(24,8) not null,
reason text not null,
notes text,
source_type text,
source_id uuid,
recorded_by_employee_id uuid references employees(id) on delete set null,
recorded_at timestamptz not null default now(),
constraint waste_qty_positive check(quantity > 0),
constraint waste_reason_check check(
reason in (
'expired','damaged','prep','incorrect_order',
'guest_return','overproduction','spill','breakage','other'
)
)
);
create table if not exists stock_transfer_records (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
stock_item_id uuid not null references stock_items(id) on delete restrict,
from_storage_location_id uuid not null references storage_locations(id) on delete restrict,
to_storage_location_id uuid not null references storage_locations(id) on delete restrict,
quantity numeric(24,8) not null,
unit_code text not null references units(code) on delete restrict,
normalized_quantity numeric(24,8) not null,
reason text,
created_by_employee_id uuid references employees(id) on delete set null,
created_at timestamptz not null default now(),
constraint stock_transfer_qty_positive check(quantity > 0),
constraint stock_transfer_distinct_locations check(from_storage_location_id <> to_storage_location_id)
);
insert into permissions(permission_key, description) values
('inventory.read', 'Read stock, balances and movements'),
('inventory.cost.read', 'Read inventory costing fields'),
('inventory.adjustments.create', 'Create manual stock adjustments'),
('inventory.count.start', 'Start inventory counts'),
('inventory.count.edit', 'Edit inventory count lines'),
('inventory.count.post', 'Post inventory counts'),
('inventory.waste.create', 'Record waste'),
('inventory.reorder.manage', 'Manage reorder rules'),
('inventory.transfer.create', 'Transfer stock between storage locations')
on conflict(permission_key) do nothing;