Shifts and hours, from the inside

The rota is a sheet on the wall, the clock is a notebook, and labour cost turns up at month end from the accountant, when nothing can be done about it. Nobody knows Marta did fourteen extra minutes every day until it is seven hours at the end of the month. And when someone forgets to clock out, the manager writes in a time by eye, with no record of having done so.

Three tables. Employees, with hourly cost and contracted hours. Shifts, which are the plan: who, which day, from when to when, with what break. Time entries, which are reality: in, out, and if the manager corrected it, the correction beside the punched value, never over it. Separating planned from actual is the decision that makes the rest work.

An employee cannot be rostered on two shifts at once. The rule is not a warning on screen: it is an exclusion constraint in Postgres over each shift's time range. A 20:00 to 23:30 shift for someone already on 17:00 to 23:00 is refused by the database. A 23:00 to 23:30 one goes in, because it does not overlap.

-- Um empregado não está em dois turnos ao mesmo tempo.
-- Restrição de exclusão sobre o intervalo de cada turno, na base de
-- dados, não num aviso no ecrã.
create extension if not exists btree_gist;

alter table demo_shifts add constraint demo_shifts_no_overlap
  exclude using gist (
    room_code   with =,
    employee_id with =,
    tsrange(day + starts, day + ends) with &&
  );

One function returns the timesheet already calculated: planned minutes from the shift, actual minutes from the clock, both net of the break. Lateness is clock-in against the shift start. Overtime is actual minus planned, never negative. Cost is actual at the hourly rate. One source of truth, and the screen only displays it.

-- Planeado vem do turno, real vem do ponto. Uma só fonte de verdade.
scheduled_min := (extract(epoch from (s.ends - s.starts)) / 60) - s.break_min;
actual_min    := (extract(epoch from (coalesce(e.adjusted_out, e.clock_out) - e.clock_in)) / 60)
                 - s.break_min;
late_min      := greatest(0, extract(epoch from (e.clock_in - (s.day + s.starts))) / 60);
overtime_min  := greatest(0, actual_min - scheduled_min);
cost_cents    := round(actual_min / 60.0 * emp.hourly_cents);

-- Repare no coalesce: se o gestor corrigiu a saída, é a corrigida
-- que conta. A picada fica na tabela, ao lado, como estava.

When the clock-out is missing, the manager enters one: with a mandatory reason, signed and dated. The punched clock-out stays as it was; the corrected one sits beside it, and the corrected one feeds the calculation. Approving requires a clock-out, punched or corrected, and once approved there are no more corrections. The constraint is on the table, not only in the function.

-- Corrigir não é apagar. A correcção fica ao lado do valor picado,
-- com motivo obrigatório, assinada e datada.
check (adjusted_out is null or adjust_reason is not null)

-- Não se aprova sem saída, picada ou corrigida:
check (approved_at is null or clock_out is not null or adjusted_out is not null)

-- E depois de aprovado não se corrige mais:
update demo_time_entries set adjusted_out = p_out, ...
 where id = p_entry and room_code = p_room and approved_at is null;