The problem
Almost everyone quotes in a spreadsheet, and almost everyone loses the spreadsheet. Worse: the spreadsheet mixes material and labour into one cell, and at the end of the job nobody can say whether the money went on materials, on hours or on the machine. Without the three kept apart, budget against actual is arithmetic you cannot do, which is why this system starts at the composition and not at the PDF.
The tables
Four things. The cost base is deliberately shared with the day sheet: the same item you quote is the item the site books against, so both ends speak the same language.
| Table | What it holds |
|---|---|
| obra_cost_items | The cost base. Material, hours and equipment per unit, kept apart. Shared with the day sheet. |
| obra_quotes | Header: client, site, status, margin, VAT, validity. |
| obra_quote_lines | The measurement plus a copy of the composition at the moment the line was added. |
| demo_rooms.obra_hourly_cents | The hour the company budgets with. It lives on the room because it is the company's call. |
The line keeps the price
A quote line does not point at today's price: it keeps a copy of the composition from the moment it was added. If cement goes up tomorrow, a quote already sent still says what it said when the client read it. While it is a draft there is a function that refreshes it deliberately, and it returns how many lines it touched, so nobody is surprised in silence.
-- A linha guarda o que o artigo custava quando entrou.
-- Se o cimento subir amanhã, um orçamento já assinado não muda
-- debaixo dos pés de quem o assinou.
insert into obra_quote_lines
(quote_id, cost_item_id, code_snapshot, name_snapshot, unit, category,
qty, material_cents, labour_hours, equipment_cents, hourly_cents)
values
(p_quote, v_i.id, v_i.code, v_i.name_pt, v_i.unit, v_i.category,
round(p_qty, 3), v_i.material_cents, v_i.labour_hours,
v_i.equipment_cents, obra_room_hourly(p_room));The arithmetic is born on the server
The browser sends the item and the measurement. Direct cost, margin, VAT and total come out of a function, and the same function returns the arithmetic for every quote in the room at once, so the list does not run a query per row. Dragging the margin shows the total moving, but it only writes on release: a slider that saves on every pixel is one write per pixel.
-- Uma chamada devolve as contas de todos os orçamentos da sala.
-- O ecrã da lista não faz uma consulta por linha.
select id,
(mat + lab + eqp) as direct_cents,
round((mat + lab + eqp) * margin_pct / 100) as margin_cents,
(mat + lab + eqp)
+ round((mat + lab + eqp) * margin_pct / 100) as net_cents
from (select q.id, q.margin_pct,
sum(round(l.qty * l.material_cents)) as mat,
sum(round(l.qty * l.labour_hours * l.hourly_cents)) as lab,
sum(round(l.qty * l.equipment_cents)) as eqp
from obra_quotes q
left join obra_quote_lines l on l.quote_id = q.id
where q.room_code = p_room
group by q.id, q.margin_pct) base;The status shuts the door
Draft, sent, won or lost, and there is no going back. The rule that a sent quote no longer changes its lines lives inside the update, not inside the button: a stale screen open in another tab does not get around it, because the condition runs in the database and returns zero rows when it does not hold.
-- Um orçamento enviado já não muda de linhas. A regra está no
-- update, não no botão: um ecrã velho noutro separador não a contorna.
update obra_quotes set margin_pct = round(p_pct, 2)
where id = p_quote and room_code = p_room and status = 'rascunho'
returning * into v_row;
if not found then raise exception 'orcamento fechado'; end if;What I would do differently for a real client
A PDF with the measurement schedule and the payment terms, generated on the server and emailed with open tracking. Quote versions, so you know what changed between the proposal and the revision the client signed. Indexed price revision, which on a long job is real money. And authentication: here any visitor can mark a quote won; in a real business, only those who may.