Parts and stock, under the hood

The problem

Parts that leave without paperwork are parts that never get charged. And you only find out stock ran out when the car is already up on the lift.

The model

Parts with stock, minimum, cost and price. Every stock change is a movement: out to a repair, or in from an order.

Table	What it holds
parts	Reference, supplier, stock, minimum, cost, price and bin.
movements	Every stock change: out to a repair or in from an order.
purchase_orders	Order per supplier, with its lines and whether it has arrived.

Orders

Whatever is below minimum is grouped by supplier, with enough to get back to twice the minimum. On receipt, stock goes up and an incoming movement is logged.

// O que está abaixo do mínimo agrupa-se por fornecedor,
// com quantidade para voltar ao dobro do mínimo.
for (const p of parts.filter(p => p.stock < p.min)) {
  const lines = bySupplier.get(p.supplier) ?? [];
  lines.push({ ref: p.ref, qty: p.min * 2 - p.stock });
  bySupplier.set(p.supplier, lines);
}

What I'd do differently in production

Stock would be the sum of the movements rather than an editable number, so the system and the history can never disagree.

sql

-- Em produção o stock é a soma dos movimentos,
-- nunca um número que se edita à mão.
create view part_stock as
select ref, sum(case kind when 'in' then qty else -qty end) as stock
  from movements
 group by ref;