Find a free meeting room for a time window
Go · Go · intermediate · modification
Adds the endpoint behind the new "Find a room" panel. Until now people opened every room's calendar to find a gap; the panel asks for a time window and shows the rooms that are free for all of it. `GET /api/rooms/available` validates the window (RFC 3339, end after start, 24 hours at most) and runs one query: the organisation's active rooms with no active booking overlapping the window, ordered by name. An empty result is `[]` so the panel can show its "nothing free" state. Tried a few windows against the staging data.
Office booking API in Go 1.22 (stdlib `net/http` routing, `pgx/v5` pool, `log/slog`). `rooms.Room{ID, Name, Capacity}` (json tags `id`/`name`/`capacity`), `rooms.Store` (with `DB *pgxpool.Pool`), `rooms.Handler` (with `Store` and `Log`), `auth.FromContext` and the `httpx` JSON helpers exist and are unchanged; the `authed` middleware puts the signed-in member on the request context. PostgreSQL 14: `rooms(id, org_id, name, capacity, archived_at)` and `bookings(id, room_id, starts_at, ends_at, cancelled_at)`, both times `timestamptz`, `ends_at > starts_at` by a check constraint, btree indexes on `bookings(room_id, starts_at)` and `bookings(room_id, ends_at)` and an exclusion constraint on `(room_id WITH =, tstzrange(starts_at, ends_at) WITH &&) WHERE cancelled_at IS NULL`, so `POST /api/bookings` accepts a booking that starts when another one ends. An organisation has at most about 50 rooms. The web app sends whole-minute times from a 15-minute picker and builds the query string with `URLSearchParams`.
Requirements
- `GET /api/rooms/available?start=<RFC 3339>&end=<RFC 3339>` (signed-in members only) lists the rooms of the caller's organisation that are free for the whole window: not archived, and with no active (not cancelled) booking that overlaps it. The response is a JSON array of `{id, name, capacity}` ordered by room name, and `[]` when no room is free.
- Bookings and the requested window are half-open time ranges: a booking from 13:00 to 14:00 occupies the room up to 14:00, not at 14:00. Back-to-back meetings are allowed, so one meeting may start at the same instant another one ends in the same room.
- `start` and `end` are RFC 3339 timestamps with an offset; `end` must be after `start` and at most 24 hours later. A missing or malformed value, or a window that breaks these rules, is answered with 400 and a short JSON error. Past windows are accepted (the booking form enforces its own time rules).
Files touched
- internal/rooms/available.go
- cmd/api/routes.go
--- internal/rooms/available.go +package rooms + +import ( + "context" + "errors" + "net/http" + "time" + + "example.com/officeapp/internal/auth" + "example.com/officeapp/internal/httpx" +) + +const maxWindow = 24 * time.Hour + +const availableSQL = ` +SELECT r.id, r.name, r.capacity +FROM rooms r +WHERE r.org_id = $1 + AND r.archived_at IS NULL + AND NOT EXISTS ( + SELECT 1 + FROM bookings b + WHERE b.room_id = r.id + AND b.cancelled_at IS NULL + AND b.starts_at <= $3 + AND b.ends_at >= $2 + ) +ORDER BY r.name, r.id` +