Booking API

The /api/booking endpoint serves as the central hub for managing appointment scheduling within Odys. It exposes both GET and POST methods, allowing clients to query a professional's availability and to submit new appointment requests. This API is critical for the core functionality of the platform, connecting clients with professionals and orchestrating the initial steps of the service delivery workflow.

Overview

This API route, defined in src/app/api/booking/route.ts, orchestrates the complex logic required for appointment management. The GET request provides a read-only view of a professional's schedule, combining their defined availability rules with existing appointments to show what slots are open. The POST request handles the creation of new appointments, involving several steps: input validation, bot detection, professional lookup, plan limit checks, client upsertion, atomic appointment creation, and multi-channel notifications.

The system interacts with several Drizzle ORM-managed tables:

• professionals: To retrieve professional details like sessionDuration, autoConfirm, plan, and contact information for notifications.
• availability: To fetch the professional's weekly working hours.
• appointments: To check for existing bookings and to insert new ones.
• clients: To manage client records, either by finding an existing client or creating a new one.
• notifications: To send in-app alerts to professionals about new bookings.

GET Request: Fetching Availability

The GET method for /api/booking allows any visitor to query a professional's available slots for a specific date. It expects two URL search parameters: slug to identify the professional and date to specify the day for which availability is requested.

Upon receiving a request, the system first validates these parameters, returning a 400 status if they are missing. It then queries the professionals table using the provided slug. A crucial design choice here is the explicit selection of only the id and sessionDuration columns. This was a deliberate security enhancement; an earlier version returned all columns (SELECT *), which inadvertently exposed sensitive professional data like email, phone, userId, plan, trialEndsAt, stripeCustomerId, and stripeSubscriptionId to unauthenticated users. By limiting the selection, the API now only exposes the minimum necessary information for scheduling, preventing data leakage. If no professional is found, a 404 error is returned.

Once the professional is identified, the API performs two concurrent database queries using Promise.all for efficiency:

1. It fetches all availability rules for the professional, which define their regular working hours for each dayOfWeek, specified by startTime and endTime.
2. It retrieves all existing appointments for that professional within the requested date's dayStart and dayEnd. Importantly, it filters out appointments with status of rejected or cancelled, as these slots are effectively free.

The response combines these rules and existingAppointments, allowing the frontend to calculate and display the actual available time slots to the client. Any unexpected errors during this process are caught and handled by the serverError utility, returning a generic 500 status.

POST Request: Creating a Booking

The POST method to /api/booking is responsible for creating new appointments. This is a more complex operation, involving several layers of validation, business logic, and side effects.

Rate Limiting and Timing Oracle Mitigation

Before processing the request, a rate limit is applied using getBookingLimiter. This helps prevent abuse and ensures fair usage of the API. If the limit is exceeded, a 429 Too Many Requests response is returned.

A subtle but important security measure is the respondAfterFloor function. This introduces a minimum response time of 250ms for all POST requests. This design choice aims to blunt a potential "timing oracle" attack. Without it, an attacker could submit booking requests with various client phone numbers and emails, and by precisely timing the responses, infer whether a given client already exists in the database for that professional. The transaction is inherently faster when a client row exists (a SELECT followed by an UPDATE) than when it doesn't (a SELECT followed by an INSERT). By flooring the response time, this timing difference becomes negligible compared to network jitter, making such an attack impractical without noticeably degrading the user experience for legitimate users.

Input Validation and Honeypot

The incoming request body is validated against the bookingSchema using Zod. This schema ensures that slug, startsAt, clientName, and clientPhone are present and correctly formatted. clientEmail is optional.

A clever bot detection mechanism is implemented using a website field in the schema. This field is intended to be a "honeypot" – a hidden form field that only automated bots would typically fill. If the website field is present and non-empty in the submission, the API immediately returns a fake success response ({ success: true }) with a 200 OK status, without ever touching the database. This makes the bot believe its submission was successful, preventing it from learning that the field is a trap and discouraging further attempts.

Professional Lookup and Plan Limits

After validation, the professional is retrieved from the professionals table using the slug. If not found, a 404 is returned.

Next, the system checks if the professional's current plan imposes any limits on clients or appointments. The effectivePlan utility determines the professional's active plan (considering trialEndsAt), and PLANS provides the associated limits.

• If plan.limits.clients is not Infinity, the system counts existing clients for the professional. If the clientCount exceeds the limit and the incoming clientPhone does not match an existing client, a 403 Forbidden error is returned.
• Similarly, if plan.limits.appointmentsPerMonth is not Infinity, the system counts appointments within the current month. If monthlyCount exceeds the limit, a 403 Forbidden error is returned.

Atomic Appointment Creation

The core logic for creating the appointment is wrapped in a Drizzle ORM transaction with isolationLevel: "serializable". This is a critical design choice for data integrity. In a highly concurrent environment, multiple clients might attempt to book the same time slot simultaneously. Without a serializable transaction, both requests could pass an initial conflict check and then both attempt to insert an appointment, leading to a double-booking.

The serializable transaction ensures that:

1. Conflict Check: Inside the transaction, the system first checks the appointments table for any existing appointments that would overlap with the proposed startDate and endDate for the professional, excluding rejected or cancelled appointments. If a conflict is found, it throws a custom SLOT_TAKEN error.
2. Client Upsert: The system then attempts to find an existing client for the professional based on clientPhone or clientEmail. If a client is found, their record is updated (e.g., if an email was provided but missing from the existing record). If no client is found, a new entry is inserted into the clients table.
3. Appointment Insert: Finally, a new appointment is inserted into the appointments table, linking it to the professional and client. The status is set to confirmed if professional.autoConfirm is true, otherwise pending_confirmation. The paymentStatus defaults to none.

If the SLOT_TAKEN error is thrown, or if the database detects a serialization failure (SQLSTATE 40001), the transaction is rolled back, and the API returns a 409 Conflict response, informing the user that the slot was just taken.

Notifications and Analytics

After a successful booking, several side effects are triggered:

• An entry is inserted into the notifications table to alert the professional within the application.
• A WhatsApp message is sent to the professional's phone using the msgBookingRequest template, providing details of the new booking. This is handled asynchronously to avoid blocking the response.
• If the professional has an email configured, an email notification is sent via sendBookingRequestEmailToProfessional, also asynchronously.

Finally, analytics events are captured using capture and setOnce. The booking_received_server event tracks each booking, attributing it to the professional.id. The setOnce event records the first_booking_at timestamp for the professional, which is useful for funnel analysis without needing to re-query the database for this specific metric.

The POST request concludes by returning an equalized success response ({ success: true }) after the response-time floor, intentionally omitting any details about the newly created appointment to prevent information leakage that could aid attackers.

Design decisions

The design of the booking API reflects a careful balance between functionality, security, and performance:

• Security by Default: The GET endpoint's explicit column selection (id, sessionDuration) for professionals is a direct response to a potential data leakage vulnerability, prioritizing privacy. Similarly, the POST endpoint incorporates a respondAfterFloor mechanism to mitigate timing oracle attacks and a website honeypot field for bot detection, demonstrating a proactive approach to security.
• Data Integrity with Transactions: The use of a Drizzle transaction with isolationLevel: "serializable" for the POST request is fundamental. It ensures that concurrent booking attempts for the same slot are correctly handled, preventing double-bookings and maintaining the consistency of the appointments table. This is a trade-off for potentially higher latency during peak concurrency but is essential for correctness.
• User Experience through Clear Feedback: The API provides specific error messages for common scenarios like invalid parameters (400), professional not found (404), plan limits exceeded (403), and slot conflicts (409). This allows the frontend to give meaningful feedback to the user.
• Multi-channel Notifications: By sending notifications via in-app alerts, WhatsApp, and email, the system aims to ensure professionals are promptly informed of new bookings, catering to different communication preferences and increasing reliability.
• Analytics for Business Insights: Integrating capture and setOnce analytics events directly into the booking flow provides valuable data for understanding professional engagement, conversion funnels, and overall platform usage.

Potential improvements

1. Availability Time Representation: The availability table stores startTime and endTime as text columns. While functional, using a dedicated time type (if supported by the database and Drizzle) or storing times as integer minutes from midnight could simplify time-based calculations, comparisons, and prevent potential parsing issues or inconsistencies. This would make the logic for determining available slots in the GET request more robust and less prone to errors.
2. Client Upsert Logic Refinement: In the POST request, the client upsert logic uses or(...clientConditions) to find an existing client by phone OR email. If a client exists with a phone number but a different email is provided, the existing client's email is updated. This might not always be the desired behavior, especially if email is intended to be a unique identifier for a distinct client. A more explicit strategy, such as prioritizing phone as the primary unique identifier for upsert, or requiring a userId for logged-in clients, could prevent unintended client record merging.
3. Internationalization of Date/Time Formats: The POST endpoint hardcodes date and time formatting for notifications using toLocaleDateString("pt-BR") and toLocaleTimeString("pt-BR"). This assumes all professionals and clients prefer Brazilian Portuguese locale. For a global platform, these formats should be configurable based on the professional's or client's locale settings, or a standardized, locale-agnostic format should be used for internal messages.
4. Granular Error Handling: The serverError utility provides a generic 500 response for unexpected errors. While useful as a catch-all, more specific error types or codes could be returned for certain known failure modes (e.g., external API failures for WhatsApp/email, database connection issues). This would allow for more targeted error handling on the client side and better observability for debugging.

References

• src/app/api/booking/route.ts
• src/lib/db/schema.ts
• src/lib/whatsapp.ts
• src/lib/stripe.ts
• src/lib/plan-guard.ts
• src/lib/email.ts
• src/lib/ratelimit.ts
• src/lib/api.ts
• src/lib/analytics.ts