Case study
Turnos
Multi-role web application where clients book appointments with professionals.
- Role
- Individual project.
- Status
- Completed
Stack
- Python 3.12
- FastAPI
- SQLAlchemy 2.0
- Alembic
- Pydantic v2
- PostgreSQL 16
- PyJWT
- React
- TypeScript
- Vite
- Tailwind CSS
- TanStack Query
- Docker Compose
Problem
Coordinating appointments between clients and professionals without schedule clashes: availability rules, confirmation, reminders and different permissions for each type of user.
Solution
- Appointment booking based on the professional's weekly availability and date blocks.
- Booking rescheduling and calendar export in .ics format.
- Booking status flow (pending, confirmed, completed and cancelled) with transactional emails and reminders.
- Admin panel with metrics and downloadable reports in CSV, Excel and PDF.
- Light and dark theme, with responsive design.
Roles and flows
- Client
- Registers and verifies their email, picks a free time slot and books; can see their bookings, reschedule them, add them to their calendar and cancel them in advance.
- Professional
- Manages their weekly availability and blocks, sees their agenda by day or week and confirms, cancels or completes the bookings that belong to them.
- Admin
- Manages services and users, assigns services to professionals, sees all bookings and their metrics, and downloads reports by date range.
Architecture
Browser
The client, the professional or the admin.
Web
Vite and React, served as a static frontend.
API
FastAPI, with JWT authentication and a refresh cookie.
Database
PostgreSQL 16, with SQLAlchemy and Alembic.
Email
SMTP service for the transactional emails.
Technical decisions
- Double-booking prevention in the database
- A PostgreSQL exclusion constraint prevents overlapping pending or confirmed bookings for the same professional, even with simultaneous requests; the violation is translated into a time-slot-unavailable response.
- Refresh tokens with rotation and reuse detection
- The refresh token lives in an httpOnly cookie and is rotated on every use; if a revoked one is reused, its whole family is revoked. The access token only exists in the frontend's memory.
- Slot generation as a pure function
- The calculation of free slots starts from rules, blocks and bookings without touching the database, which makes it easy to test.
- Emails outside the transaction
- Notifications are sent after the booking is committed: an email failure never rolls back a booking.
- Two-layer authorization
- The role is enforced in the routers and ownership of the resource is checked in the services.
Challenges and lessons
- Avoiding double bookings
- Solved with an exclusion constraint in PostgreSQL, so the database itself guarantees there are no overlaps even when two requests arrive at the same time.
- Rescheduling without breaking that rule
- The current slot is released inside the same transaction to allow shifts that overlap with the booking itself; if the new slot is not valid, nothing is saved.
- Security audit
- Findings were fixed such as slot hoarding with unlimited pending bookings and the possibility of booking without verifying the email; each fix is covered by regression tests.
Next steps
- Payment gateway and deposits when booking.
- Reminders by SMS or WhatsApp.
- Two-way synchronization with Google Calendar.
- Internationalization and per-user time zone.
Screenshots
