Aphriell Art: Case Study: Automated Room Booking for Facility Management (ARES)

Category: Programming | Reading time: 8 minutes

One of the most heavily used modules in ARES — the facility management SaaS platform I'm building — is the room booking feature. It sounds simple ("just pick a room, pick a time"), but once you get into the implementation details, there's a lot that needs careful thought.

In this post, I want to share the thought process and technical decisions behind this feature — not a step-by-step tutorial, but more of a case study from a problem-solving angle.

The Problem Being Solved

Before ARES, the meeting room booking process at many offices (including ones I've worked in directly) was still manual: chat the admin on WhatsApp, admin checks an Excel schedule, manual confirmation, and double bookings happened often because two people would message the admin at nearly the same time.

The core requirements became clear:

Users need to see room availability in real time
The system must prevent double bookings automatically — not rely on a careful human
There needs to be an approval flow for certain rooms that require sign-off (e.g. a VIP meeting room)
It has to support multi-tenancy — different companies using the same ARES instance, with data fully separated

Design Decision #1: Preventing Double Bookings at the Database Level

This is the most critical part. The obvious temptation is to just check availability on the frontend before submitting — but that has an obvious race condition: two users could submit overlapping bookings within milliseconds of each other, before validation has a chance to run.

The solution I used: leverage a database-level constraint (Supabase/PostgreSQL), not just application-side validation. Using the btree_gist extension, I can create an exclusion constraint that physically prevents two rows with overlapping time ranges for the same room:

create extension if not exists btree_gist;

alter table bookings
add constraint no_overlapping_bookings
exclude using gist (
  room_id with =,
  tstzrange(start_time, end_time) with &&
);

With this constraint in place, even if two booking requests come in almost simultaneously, the database itself will reject one of them — regardless of how fast the requests arrive. This is far more reliable than manual validation in application code.

Design Decision #2: A Flexible Approval Flow

Not every room needs approval — small meeting rooms can usually be booked instantly, but special rooms (like an executive boardroom) need sign-off first. I designed this as a per-room property, not hardcoded:

alter table rooms add column requires_approval boolean default false;

If requires_approval = true, the booking comes in with a pending status and triggers a notification to the approver. If false, it goes straight to confirmed. This flexibility matters because every company using ARES has different policies about which rooms need approval.

Design Decision #3: Multi-Tenant Data Isolation

Since ARES is used by many different companies within the same system, one tenant's data must never "leak" to another tenant — even if there's a bug in the application code. The solution, as I also covered in the earlier Next.js + Supabase article, is to rely on Row Level Security:

create policy "Tenant isolation for bookings"
on bookings for select
using (tenant_id = (select tenant_id from users where id = auth.uid()));

So even if there's a gap in the frontend logic, the database itself ensures a query can only return data belonging to the currently logged-in tenant. I consider this a mandatory layer of security for any multi-tenant SaaS product.

Challenges Along the Way

Not everything went smoothly. A few things I had to revise after the initial version:

Timezone handling — I initially stored times without an explicit timezone, and it broke almost immediately once a tenant in a different timezone joined. The fix was moving everything to timestamptz and being explicit about timezone in every query.
UX for cancel/reschedule — cancelling a booking originally just deleted the row, which erased history. I changed it to a soft-delete with a cancelled status, so it could still be tracked for reporting purposes.
Real-time notifications — in the first version, users had to manually refresh to see a booking status update. I added Supabase Realtime so status changes reflect instantly without a refresh.

The Result

This feature is now one of the most stable modules in ARES, with a database constraint that structurally prevents schedule conflicts — not just relying on application-level validation that could have gaps.

The biggest lesson from this whole process: for problems that genuinely need to be "always correct" — like preventing double bookings — don't rely solely on application-level validation. Use database constraints that are specifically designed to guarantee data consistency.

Closing Thoughts

This case study is just one of several modules I've built in ARES — there's also a Catering module (QR/RFID-based meal ordering) and Visitor Management, each with its own technical challenges, which might be worth covering in separate posts down the line.

If you need a booking system, facility management platform, or a custom SaaS product with specific requirements like these, I'm open for freelance work. Check out the full ARES case study on my portfolio, or reach out via the Contact page.

Have questions about the technical implementation? Leave a comment and I'll go into more detail.

Case Study: Automated Room Booking for Facility Management (ARES)