Stock, from the inside

The problem

A restaurant's stock lives on a whiteboard or in the chef's head. Roughly how much chicken there is, everyone knows. Which of the three batches expires tomorrow, nobody does, and when someone opens the cold room they take what is nearest, which is what arrived yesterday. The one from the day before spoils at the back. Waste is never measured because it is never recorded, so it looks like it does not exist.

Data model

Ingredients, suppliers, prices and recipes are reference data, shared with purchasing and food cost. Batches belong to a room: each knows how much came in, how much is left, at what cost, its use-by date and where it sits. And every change to a batch is a row in the movements table. The batch quantity is a derived number; the history is the truth.

Table	What it holds
demo_ingredients	What the house buys: name, unit and category. Shared with purchasing and food cost.
demo_suppliers	Who supplies, with lead time and minimum order. Shared with purchasing.
demo_recipes	The recipes: portions, selling price and ingredient lines. Shared with food cost.
demo_stock_batches	One batch per delivery: how much came in, how much is left, cost, use-by date and location. Quantity is derived from movements.
demo_stock_movements	Every change to stock, with kind, quantity, reason and reference. Never deleted.

sql

-- A quantidade do lote nunca se escreve à mão.
-- Cada alteração é uma linha aqui; o lote é a soma.
create table demo_stock_movements (
  batch_id   uuid references demo_stock_batches(id),
  kind       demo_movement_kind not null,   -- entrada, consumo, desperdicio, ajuste
  qty        numeric(9,3) not null check (qty > 0),
  reason     demo_waste_reason,
  ref        text,                          -- a receita, a guia, o motivo
  created_at timestamptz default now(),
  -- Desperdício sem motivo não é desperdício, é um buraco no stock.
  constraint demo_waste_reason_needed
    check (kind <> 'desperdicio' or reason is not null)
);

Consumption picks the batch

When the kitchen asks for six kilos of chicken, the browser does not say where to take it from. The function walks that ingredient's batches by use-by date, soonest first, and draws from each until the quantity is met. If the first batch only has four, it takes four from it and two from the next, writing one movement per batch. A lock per room and ingredient stops two simultaneous requests taking the same kilo. Batches with no use-by date go last: that is FIFO inside FEFO.

sql

-- O browser não diz de onde tirar. A função percorre os lotes
-- por validade, do que expira primeiro para o que expira depois.
perform pg_advisory_xact_lock(hashtext(p_room || '|stock|' || p_ingredient));

for v_batch in
  select * from demo_stock_batches
   where room_code = p_room and ingredient_id = p_ingredient and qty > 0
   order by expiry asc nulls last, received_at asc
   for update
loop
  exit when v_left <= 0;
  v_take := least(v_batch.qty, v_left);

  update demo_stock_batches set qty = qty - v_take where id = v_batch.id;
  insert into demo_stock_movements (batch_id, kind, qty, ref)
  values (v_batch.id, 'consumo', v_take, p_ref);

  v_alloc := v_alloc || jsonb_build_object(
    'batch', v_batch.code, 'qty', v_take, 'left', v_batch.qty - v_take);
  v_left := v_left - v_take;
end loop;

return v_alloc;   -- exactamente de que lote saiu cada quantidade

Cooking is consumption by recipe

Cooking twenty portions of roast chicken becomes the recipe's ingredients scaled by portions, and each one goes through the consumption function. It is all or nothing: if the olive oil is short, the chicken is not taken. The function returns exactly which batch each quantity came from, and that is what the screen shows, so nobody has to take the system's word for it.

sql

-- Cozinhar vinte doses converte-se nos ingredientes da receita,
-- escalados pelas doses, cada um pela função de consumo.
for v_line in select * from demo_recipe_lines where recipe_id = p_recipe
loop
  v_need := round(v_line.qty * p_portions / v_recipe.portions, 3);
  v_out := v_out || jsonb_build_object(
    'ingredient', v_line.name_pt, 'needed', v_need,
    'allocation', demo_stock_consume(p_room, v_line.ingredient_id, v_need, v_recipe.name_pt));
end loop;

-- Tudo ou nada: a função corre numa transacção. Se o azeite faltar
-- na terceira linha, o frango da primeira volta atrás.

Waste with a reason

Recording waste without a reason is refused by a table constraint, not a warning on screen. Without a reason the number is useless: you cannot tell whether the problem is dates, preparation or over-ordering. With one, the panel shows where the money is going, in euros, at the batch's real cost.

What I would do differently for a real client

A barcode scanner at receiving, so batch and date come in without typing. A link to the KDS, so consumption happens when the dish goes out rather than when someone remembers. Periodic physical stock counts with signed adjustments, because reality never quite matches the system. And authentication: here any visitor receives and throws away, because it is a demo.