Requirements & business rules
A Thai aesthetic clinic with 3 branches was managing all bookings through Facebook messages, LINE OA, and phone calls with no central system. Staff had to check multiple platforms to know who was coming in, and patient records were scattered across spreadsheets and LINE chats. I gathered requirements through a structured questionnaire with the clinic owners, which covered their services and durations, how they scheduled doctors across branches, what patient data they needed to store, how they wanted notifications to work, and what reports they cared about. The outcome of that process shaped the whole data model.
Architecture diagram
- -The backend runs on Railway instead of Vercel because the cron job that sends LINE reminders needs to run continuously. Vercel is serverless and has an execution timeout, which means a scheduled job would get cut off before it finishes.
- -The cron job lives inside the same Express process rather than a separate worker service. The clinic sees around 70-90 patients per month, so the volume doesn't justify the added complexity of a separate queue or worker.
- -REST was chosen over GraphQL because the scope was clear and the team was just me. There was no over-fetching problem that would make GraphQL worth the setup.
Data model & database design
PostgreSQL hosted on Railway, managed with Prisma ORM. The core models are Branch, Doctor, DoctorBranch (a junction since doctors work across multiple branches), Schedule (recurring weekly slots per doctor per branch), Service (with duration in minutes), Patient, Booking, and Notification. A few design decisions worth noting: Schedule uses a dayOfWeek integer with start and end time strings rather than storing individual date slots - the clinic runs on a consistent weekly pattern so generating available slots from a recurring schedule made more sense than maintaining a calendar of individual entries. Booking has a self-referencing rescheduledFrom field so you can trace the full chain of reschedules for any appointment. Notification stores scheduledAt and sentAt separately and logs errors per attempt, which supports the hourly cron job retrying failed LINE messages. There's also a PendingLineUser model for LINE webhook users who interact before a staff member has linked them to a patient record.
Entity relationship diagram
- -Doctor and Branch are connected through a DoctorBranch junction table instead of a direct FK. The client said doctors work across multiple branches on different days - a direct FK would only allow one branch per doctor.
- -Schedule stores dayOfWeek (0-6) with start and end times instead of individual date entries. The clinic runs on a consistent weekly pattern, so generating available slots from a recurring schedule made more sense than maintaining a calendar of individual dates.
- -Booking has a rescheduledFrom field that points back to itself. This lets you trace the full chain of reschedules for any appointment - the client wanted to see where a booking originally came from.
- -Notification stores scheduledAt and sentAt as separate fields and logs any errors per attempt. The cron job uses scheduledAt to know what to send, and sentAt to confirm it was actually delivered. Failures get logged so they can be retried.
- -PendingLineUser exists as a holding state for LINE users who message the clinic before being registered as a patient. Without it, incoming LINE messages from unregistered contacts had nowhere to go.
Technology stack
| Language | JavaScript |
|---|---|
| Frontend | React, Vite, shadcn/ui, MUI X DataGrid, Zustand |
| Backend | Node.js, Express, Prisma ORM, JWT |
| Database | PostgreSQL (Railway) |
| Services | LINE Messaging API |
| Infrastructure | Vercel, Railway |
React + Vite for the frontend - a fast SPA was the right fit since this is a staff-facing tool, not a public-facing site. MUI X DataGrid handled the sortable, paginated tables across most pages, and shadcn/ui covered forms and dialogs. Zustand managed auth state. On the backend, Express with Prisma in a Controller-Service-Router structure, with Zod schemas for request validation at the route layer. LINE Messaging API ran on an hourly cron job for appointment reminders - the clinic specifically wanted a day-before evening notification and a morning-of reminder at 8 AM. Railway for the backend since it needs to stay always-on for the cron, Vercel for the frontend.
Challenges & solutions
01 -Preventing doctor double-booking across branches
Problem
Doctors work at different branches on different days. The system needed to prevent a doctor from being scheduled at two branches simultaneously - but the booking flow had to guide staff to valid slots without exposing the constraint directly.
Investigation
The naive approach was checking availability on the frontend. But two concurrent requests could still commit a conflict, and the frontend can be bypassed entirely. The constraint had to live at the data layer.
Solution
The Schedule model uses a unique constraint on (branchId, doctorId, dayOfWeek), so each doctor can only have one working pattern per day per branch. Slot availability is calculated server-side from that recurring pattern. The booking controller re-validates the selected time against the doctor's schedule before inserting - so a conflict cannot be committed even under concurrent requests.
Result
Doctor double-booking is prevented at the database level, not the UI. The constraint holds regardless of how the booking request originates.
02 -Branch-scoped data access without leaking across branches
Problem
Staff at one branch should only see patients and bookings from their own branch. Hiding data in the UI is not access control - I needed the restriction enforced on the backend so it couldn't be bypassed by constructing a request manually.
Solution
Added a branchId to the User model. An effectiveBranchId helper is called at the top of every controller before any query. ADMIN and SUPERUSER return null, meaning no branch filter is applied. STAFF return their assigned branchId, which gets injected as a Prisma where condition on every query. Controllers pass intent - the helper resolves scope.
Result
Branch isolation is enforced server-side on every request. A staff member at Branch A cannot retrieve Branch B's data even if they construct the request directly.
03 -Handling LINE messages before a patient record exists
Problem
Patients often message the clinic via LINE before being registered in the system. The webhook was receiving LINE user IDs with no matching Patient record and had no way to handle that state cleanly.
Investigation
I mapped the full lifecycle: message received, check lineUserId on Patient, not found, dead end. There was a gap between 'LINE user exists' and 'patient record exists' with no holding state in between.
Solution
Created a PendingLineUser model. The webhook checks for a matching Patient first - if none exists, the LINE user ID is stored in PendingLineUser instead. Staff can link a pending user to an existing patient through the admin UI, moving the lineUserId to the Patient record and enabling notifications.
Result
No LINE interactions are lost during the window before a patient is registered. Staff have a clear workflow to connect LINE contacts to patient records when they come in.
Results & impact
Deployed on Vercel and Railway, currently piloted with the clinic. The system replaced a workflow that was spread across Facebook, LINE, phone calls, and spreadsheets with one place to manage bookings, patient records, doctor schedules, and LINE notifications. What shipped: multi-branch booking management, doctor scheduling, patient records with LINE integration, automated reminders via cron, a reports page with new vs returning patient breakdown, and three-role access control.
Screenshots
