Bookings and bays, under the hood

The problem

A garage booking book always fails in the same place: a car gets booked when the bay is free but the mechanic isn't, or the other way round. The system treats a booking as two reservations at once, one for the bay and one for the person.

The model

A booking has a day, a start, a duration (from the service), a bay and a mechanic. Nothing else is stored: occupancy and capacity are worked out from that.

Table	What it holds
bookings	Day, start, service, bay, mechanic, plate and status. Duration comes from the service.
services	Service catalogue with the duration in minutes.
bays / mechanics	The bays and the people. A booking takes one of each.

The overlap rule

Two bookings clash when they share a bay or a mechanic and their times overlap. The same check validates the form and finds the first free slot.

// Duas marcações chocam quando partilham rampa ou mecânico
// e os intervalos se cruzam. A mesma função valida o formulário
// e procura a primeira vaga.
export function clashFor(bookings, candidate, ignoreId) {
  const end = candidate.start + SERVICES[candidate.service];
  if (end > CLOSE || candidate.start < OPEN) return "closing";
  const sameDay = bookings.filter(b => b.day === candidate.day && b.id !== ignoreId);
  const overlaps = b => b.start < end && candidate.start < endOf(b);
  if (sameDay.some(b => b.ramp === candidate.ramp && overlaps(b))) return "ramp";
  if (sameDay.some(b => b.mechanic === candidate.mechanic && overlaps(b))) return "mechanic";
  return null;
}

What I'd do differently in production

In this demo the data lives in the browser. In a real garage it would live in a database with a range exclusion constraint, so two phones at once could never book the same bay.

sql

-- Em produção a regra vive na base de dados: dois telemóveis
-- ao mesmo tempo não conseguem marcar a mesma rampa.
alter table bookings add constraint no_bay_overlap
  exclude using gist (bay with =, slot with &&);
alter table bookings add constraint no_mechanic_overlap
  exclude using gist (mechanic with =, slot with &&);