Day sheets, from the inside

The problem

Hours live in a notebook in the van and reach the office on Friday, if they reach it. Materials get bought and nobody knows which job they went to. At the end, the job either paid or it did not, and there is no way to know why. What is missing is the record where it happens, made by whoever was there, on the day it happened.

The tables

One sheet and three line tables. All with RLS on and no read policy: what the crew costs never leaves through a select.

Table	What it holds
obra_daysheets	The sheet: job, phase, day, who, weather. And, on an adjustment, the sheet it corrects and why.
obra_daysheet_hours	Hours per person, with the hourly cost copied at the moment of booking.
obra_daysheet_materials	Materials used, against the same cost base the quote uses.
obra_daysheet_equipment	Machine hours, at the plant's hourly cost.

One sheet per phase per day

It is a partial unique index: unique when it is not an adjustment, free when it is. Two sheets for the same phase on the same day are not diligence, they are hours booked twice, and it is the database that says so, not the button.

sql

-- Unico quando nao e um acerto, livre quando e.
create unique index obra_daysheets_day_uniq
  on obra_daysheets (project_id, phase_id, day)
  where adjusts is null;

Sixteen hours, counted across every job

Nobody works twenty hours on two sites in one day, but two foremen filling sheets on two phones can book exactly that if each only looks at their own sheet. The count runs over every sheet for that day, inside an advisory lock, otherwise both pass the check at the same instant and both write.

sql

-- A conta e sobre todas as folhas daquele dia, nao so sobre esta.
-- O lock e para dois telemoveis nao passarem os dois ao mesmo tempo.
perform pg_advisory_xact_lock(
  hashtext(p_room || '|h|' || p_worker::text || '|' || v_s.day::text));

select coalesce(sum(h.hours), 0) into v_total
  from obra_daysheet_hours h
  join obra_daysheets d on d.id = h.sheet_id
 where h.worker_id = p_worker and d.day = v_s.day
   and h.sheet_id <> p_sheet;

if v_total + p_hours > 16 then
  raise exception 'mais de 16 horas no mesmo dia para %', v_w.name;
end if;

Correcting means signing, not editing

A submitted sheet closes. Correcting opens an adjustment sheet, with a reason, pointing at the original and able to carry negative hours. Both stay in the history and the sum gives the right number. It is the same principle as the HACCP corrective action and the clock-in correction, for the same reason: deleting a mistake also deletes the proof that it happened.

sql

-- Uma folha entregue nao se edita. Corrigir abre outra, com motivo,
-- que aponta para a original e pode levar horas negativas.
if v_s.submitted_at is null then raise exception 'folha ainda aberta'; end if;
if v_s.adjusts is not null then raise exception 'um acerto nao se acerta'; end if;

insert into obra_daysheets (project_id, phase_id, day, author, adjusts, reason)
values (v_s.project_id, v_s.phase_id, v_s.day, p_author, v_s.id, btrim(p_reason));

Budget against actual

The phase budget is direct cost with no margin, and the actual is what the sheets add up to at each person's and each machine's real cost. That is why the hour you budget with has to be a cost and not a sale price: if it were a price, actual would always beat budget and the report would be worthless.

sql

-- Orcado e custo directo; real e o que as folhas somam.
select p.id, p.name, p.budget_cents,
       coalesce(h.cents, 0) + coalesce(m.cents, 0) + coalesce(e.cents, 0) as actual_cents,
       p.budget_cents
         - (coalesce(h.cents, 0) + coalesce(m.cents, 0) + coalesce(e.cents, 0)) as diff_cents
  from obra_phases p
  left join (select d.phase_id, sum(round(x.hours * x.hourly_cents)) cents
               from obra_daysheet_hours x
               join obra_daysheets d on d.id = x.sheet_id
              group by d.phase_id) h on h.phase_id = p.id
  left join ... m on m.phase_id = p.id
  left join ... e on e.phase_id = p.id
 where p.room_code = p_room;

What I would do differently for a real client

Working without a connection, with sheets held on the phone and sent when there is signal, because plenty of sites have no coverage. A photo of the delivery note attached to the material line. A link to payroll, so hours are not typed twice. The foreman's signature on the screen. And authentication, with each foreman seeing only their own jobs.