Clinic LINE appointment system case: concurrency control, real-time synchronization, and 130+ testing

Background of the Problem

The biggest risk with the appointment system is not the visual appearance of the interface, but the competition in the data layer: two patients submitting the same time slot at the same time, the clinic temporarily closes but the patient still sees the old time slot, or the front desk updates the appointment rules but the LINE still allows it. These scenarios will not occur during demonstrations, but will definitely occur under real traffic. The core of this project is to treat the "appearance of being able to book" and the "actually booking in the database" as two separate tasks, and to ensure that any changes in the backend are immediately reflected on the patient's end.

Implementation Methods

• —Booking with database locking and transaction processing: lock the time slot within the transaction when booking, and only the first party to obtain the lock will succeed, while the other party will receive a clear failure message, preventing the false appearance of both parties succeeding.
• —Frontend Status: The available time slots displayed are just a reference. The only definitive confirmation of a booking is a re-check by the backend system – whether the slot is still open, whether the patient meets the criteria, and whether there is a conflict with existing bookings. This re-check is performed on the server-side.
• —Using Supabase Realtime for Synchronization: When the backend changes clinic hours or rules, changes are immediately pushed to the open patient interface, reducing information discrepancies. However, the backend check remains the final authority. Realtime is responsible for the user experience, not for accuracy.
• —Writing Exception Scenarios as End-to-End Tests: Scenarios such as simultaneous booking attempts, immediate cancellation after booking, temporary clinic closures by the backend, re-checks after rule changes, and re-sending after connection loss should all be created as repeatable tests, allowing for full verification each time a change is made.

Falcon's Actual Scope of Responsibility

• Patient Booking Process via LINE LIFF
• Clinic Time and Booking Management Backend
• Supabase Realtime Real-time Synchronization
• Database Concurrency Control and End-to-End Testing

LINE Booking and Concurrency Control Process

1. 01The patient opens the LINE LIFF booking interface, which loads the currently available time slots. This list is only a visual reference and not the final authority.
2. 02When a booking is submitted, the backend re-checks the availability of the time slot, booking rules, and potential conflicts within the same transaction. Only if all checks pass is the booking written.
3. 03When two people simultaneously attempt to book the same time slot, the database transaction determines the winner. The unsuccessful end receives a clear message and reloads the latest available time slot.
4. 04Realtime synchronizes time slot changes and backend updates to both the patient and clinic management interface, ensuring that both sides see the same status.

Limitations, Failures, and Alternatives

• —When multiple people simultaneously select the same time slot, the backend transaction result determines the winner. The unsuccessful end must re-select, without assuming "first come, first served."
• —When a connection is interrupted, it is not assumed that the booking was successful. The display must re-query the backend status before displaying the results.
• —The effective time for rule changes is determined by the server-side, and if the patient's display has not been updated, the booking will still be rejected by the backend check.
• —The anonymous case does not disclose patient, clinic, booking volume, or medical information, nor does it claim any medical or operational results.

How to Verify Evidence

• ✓The 130+ end-to-end testing scenarios cover: normal booking and cancellation, simultaneous booking attempts with concurrency, backend time slot changes and temporary closures, booking checks after rule changes, connection loss and re-sending.
• ✓The test numbers represent the number of cases documented in existing public works, not a guarantee of zero defects.
• ✓Database locking, transactions, and Realtime describe system design, not any operational commitment.
• ✓Customer names, patient data, booking volume, and operational metrics are not disclosed.

Publicly Available Measurements and Capabilities