By phase three of the lab ledger, accounts were no longer our problem — the university's LDAP handles that. This round was about equipment booking: who, which instrument, which time slot — without collisions.
The Conflict Was Trickier Than Expected
Our first take was too simple: the same device can't be booked twice with the same start time. A unique index on (device_id, slot_start) and done.
In real use it fell apart. Someone books 9:00–11:00, someone else books 10:00–12:00 — different start times, but the middle hour overlaps, and one instrument can't do two things at once.
So we switched to interval checks. Before every write we run:
SELECT 1 FROM bookings WHERE device_id = :device AND status = 'active' AND slot_start < :new_end AND slot_end > :new_start LIMIT 1
If a row comes back, we reply "this slot is already booked". The check runs inside a transaction, together with the unique index on device_id + slot_start: the index blocks identical duplicate submissions, the interval query blocks overlaps. Both layers matter — application-level checks alone can't stop concurrent writes.
How We Slice Time Slots
We don't let people type in arbitrary minutes. Each device books in 30-minute cells; the UI is a grid of clickable cells, 48 per day. That keeps slot_start and slot_end tidy, makes the overlap check simple, and exports look clean. A few instruments require a 2-hour minimum, so we added a per-device min_slots setting.
On the LDAP Side
A booker's identity comes straight from LDAP — student or staff ID — and we only store uid, name, and role locally. Undergraduates can only book weekday daytime slots; grad students and faculty have no restriction. That mapping lives in a role table, not hardcoded. When someone graduates and the university disables their account, booking access simply goes away.
Reminders
On a successful booking we push a WeCom message with the device name, time slot, and location, plus a reminder 30 minutes before it starts. If a push fails, the booking still writes to the database — we just log it. That rule dates back to a 2020 event site.
December 5
In the first week after launch, the conflict warning fired over 20 times — most turned out to be two devices in the same room being booked as one. There was no real conflict. Adding a room number column to the list made it obvious at a glance.
One instructor wanted weekend slots but was blocked by the undergraduate role. We didn't change any code — just added a per-person exception in the role table, with a note.
