Purchasing, from the inside

The problem

Orders happen by phone and WhatsApp, and the price is whatever the supplier puts on the invoice. When the meat arrives nobody checks the note against what was asked for: it gets signed and put away. Two kilos short, a damaged crate, and it is found the following week, if at all. And when chicken goes up six percent, the dish stays at the same price because nobody noticed.

Data model

Suppliers and prices are reference data, shared with stock and food cost. An order belongs to a room and has lines: ingredient, quantity ordered, price fixed at the time, and later quantity received, damaged, and the batch it generated. The price is stored on the line so a future rise never rewrites old orders.

Table	What it holds
demo_suppliers	Who supplies, with lead time and minimum order. Shared with stock.
demo_supplier_prices	One price per supplier, ingredient and date. The current one is the latest. Shared with food cost.
demo_purchase_orders	One order per room: supplier, status, when it was sent and received and by whom.
demo_purchase_lines	The lines: ordered, price fixed at the time, received, damaged and the stock batch it generated.

The price is born on the server

The browser says what it wants and how much. The function fetches that supplier's latest price for that ingredient and writes it on the line. A supplier that does not sell the ingredient is refused. The supplier's minimum order is checked in the function, not by a warning on screen, and an order below it is not created.

sql

-- O browser diz o que quer e quanto. O preço vem daqui.
v_price := demo_current_price(p_supplier, (v_line->>'ingredient_id')::integer);
if v_price is null then
  raise exception 'fornecedor nao vende este ingrediente';
end if;

insert into demo_purchase_lines (order_id, ingredient_id, qty_ordered, unit_price_cents)
values (v_row.id, ..., (v_line->>'qty')::numeric, v_price);

-- Encomenda mínima do fornecedor: na função, não num aviso no ecrã.
if v_total < v_sup.min_order_cents then
  raise exception 'abaixo da encomenda minima';
end if;

The note is checked line by line

Receiving means comparing ordered with received, line by line, and saying how much came damaged. If everything matches the order is received; if not it is partial, and the difference is written down. And this is where purchasing touches stock: what arrived in good condition enters as a batch, at the order price, with the ingredient's typical shelf life, and the receiving movement references the order. Nobody copies numbers from one system to another.

sql

-- Encomendado contra recebido, linha a linha.
v_good := v_recv - v_dmg;
if v_recv <> v_line.qty_ordered or v_dmg > 0 then v_all_ok := false; end if;

-- É aqui que as compras tocam no stock: o que chegou em condições
-- entra como lote, ao preço da encomenda, com a validade do ingrediente.
if v_good > 0 then
  v_batch := demo_stock_receive(
    p_room, v_line.ingredient_id, v_good, v_line.unit_price_cents,
    v_today + v_ing.shelf_days, <local pela categoria>, v_po.supplier_id, p_by);
  update demo_purchase_lines set batch_id = v_batch.id where id = v_line.id;
end if;

update demo_purchase_orders
   set status = case when v_all_ok then 'recebida' else 'parcial' end;

Prices with a history

Every price has a date. The current price is the latest, the earlier ones stay. That is what lets you see chicken went from 5.80 to 6.20, and compare suppliers side by side, with lead time and minimum order next to the price, because the cheapest per kilo with a thirty kilo minimum and two days' wait is not always the better buy.

sql

-- Cada preço tem uma data. O actual é o mais recente; os outros ficam.
create table demo_supplier_prices (
  supplier_id    integer references demo_suppliers(id),
  ingredient_id  integer references demo_ingredients(id),
  price_cents    integer not null check (price_cents > 0),
  since          date not null default current_date,
  unique (supplier_id, ingredient_id, since)
);

create function demo_current_price(p_supplier int, p_ingredient int)
returns integer language sql stable as $$
  select price_cents from demo_supplier_prices
   where supplier_id = p_supplier and ingredient_id = p_ingredient
   order by since desc limit 1;
$$;

What I would do differently for a real client

Sending the order to the supplier by email from the system, with a PDF. Importing the supplier's invoice to check price as well as quantity. Suggested orders from low stock and expected consumption. And approval: orders above a threshold go past the owner before they leave.