Four Tools

The mcp-server module serves as the central processing unit for various business operations, exposing a set of well-defined tools that encapsulate specific functionalities. This document delves into the implementation details of these tools, explaining their purpose, how they interact with the database, and the design choices that shaped their current form. Understanding these tools is crucial for anyone looking to extend or integrate with the mcp (Money App Core Platform) backend.

Overview

The mcp-server currently defines a total of 4 distinct tools, each designed to address a specific business need. These tools are declared in mcp-server/src/tools.ts and are intended to be callable by external systems, potentially including AI agents or other microservices. The tools are:

• get_appointments_today
• get_customer_by_phone
• get_revenue_summary
• update_appointment_status

Each tool comes with a clear description and a structured input schema, making them self-documenting and easy to integrate.

Tool Implementations

The mcp-server/src/tools.ts file is the heart of these tool definitions. It not only defines the input schemas using Zod but also provides the asynchronous handler functions that execute the actual business logic, primarily interacting with the Drizzle ORM for database operations.

get_appointments_today

This tool is designed to retrieve all appointments scheduled for a specific professional on the current day, taking into account a given IANA timezone.

The input for this tool is validated by GetAppointmentsTodayInput, which expects a professionalId (a UUID string) and an optional timezone (defaulting to "America/Sao_Paulo"). The TOOL_DEFINITIONS array provides a corresponding JSON Schema representation for external consumption.

The getAppointmentsToday handler function first parses its input. A key challenge in this function is accurately determining "today" across different timezones. It achieves this by:

1. Getting the current Date.
2. Formatting this date into a YYYY-MM-DD string using Intl.DateTimeFormat with the specified timezone. This gives a "naive" date string for the target timezone.
3. Constructing "naive" start and end Date objects (e.g., 2024-01-01T00:00:00Z and 2024-01-01T23:59:59.999Z).
4. Calculating the timezone offset using the custom tzOffsetMs helper function. This function determines the difference in milliseconds between a UTC date and its representation in the target timezone.
5. Adjusting the "naive" start and end dates by this offset to get the correct UTC timestamps that correspond to the beginning and end of "today" in the specified timezone.

With the correct start and end timestamps, the function then queries the database using Drizzle ORM. It selects id, startsAt, status from appointments, name from clients, and profession from professionals. It performs innerJoin operations between appointments, clients, and professionals tables. The where clause filters appointments by professionalId and ensures startsAt falls within the calculated start and end range. Results are ordered by startsAt.

The function returns a structured object containing the professionalId, timezone, the date string, and an array of appointments, each mapped to a consistent output format. Basic try/catch error handling is in place to return an error message if a database issue occurs.

get_customer_by_phone

This tool is designed to look up customer records based on a phone number, providing flexibility for various input formats.

The input is validated by GetCustomerByPhoneInput, which requires a phone string with a minimum length of 4 characters.

The getCustomerByPhone handler function first normalizes the input phone number using normalizeBrazilianPhone from mcp-server/src/phone.ts. This helper function strips non-digit characters and ensures the number is prefixed with "55" if it's a Brazilian number and not already in E.164 format. It then generates variants of the phone number using getPhoneVariants, which includes both the full E.164 format (e.g., "5511987654321") and a shorter version without the "55" prefix (e.g., "11987654321") if applicable. This is crucial for matching phone numbers that might be stored in different formats in the database.

The function then queries the clients table, using inArray to match any of the generated phone variants. If no clients are found, it returns an empty matches array. If clients are found, it iterates through each matching client. For each client, it performs a separate query to fetch their last 5 appointments, ordered by startsAt in descending order. This results in a list of client details along with their recent appointment history.

The output includes the normalized phone number and an array of matches, each containing client details and their appointments. Error handling is present to catch database issues.

get_revenue_summary

This tool provides a summary of revenue for a given professional over a specified rolling period (week, month, or year).

The input is validated by GetRevenueSummaryInput, requiring a professionalId and a period enum value ("week", "month", or "year").

The getRevenueSummary handler function calculates the start date for the summary period based on the period input. "Week" covers the last 7 days, "month" the last 30, and "year" the last 365.

It first fetches the sessionPrice for the specified professionalId from the professionals table. This is a critical step, as the revenue calculation relies on this value. If the professional is not found, it returns an error.

Next, it performs an aggregate query on the appointments table. It counts appointments that belong to the professionalId, have a paymentStatus of "captured", and fall within the calculated start and now date range. The sql<number> template literal is used to cast the count(*) result to an integer.

The appointmentCount is then used, along with the professional.sessionPrice, to calculate totalRevenueCents and averageTicketCents. A check prevents division by zero for averageTicketCents if no appointments are found.

The function returns a summary object including professionalId, period, windowStart, windowEnd, appointmentCount, totalRevenueCents, averageTicketCents, and a fixed currency of "BRL". Error handling is included for database operations or if the professional is not found.

update_appointment_status

This tool allows for updating the status of a specific appointment, with built-in ownership verification and idempotency.

The input is validated by UpdateAppointmentStatusInput, which requires appointmentId, professionalId, and a status from the APPOINTMENT_STATUSES enum. An optional reason string can also be provided.

The updateAppointmentStatus handler function first parses its input. It then fetches the existing appointment record using the appointmentId. Several checks are performed:

1. Appointment existence: If no appointment is found, an error is returned.
2. Ownership verification: It checks if the professionalId provided in the input matches the professionalId of the existing appointment. If they don't match, an "Unauthorized" error is returned, preventing a professional from modifying another's appointments.
3. Idempotency: If the existing.status is already the same as the target status, the function returns success: true and noChange: true, indicating that no database write was necessary.

If the status needs updating, the function proceeds to update the appointments table. It sets the status to the new value. If a reason is provided, it also updates the notes column of the appointment. The returning() clause ensures that the updated record is returned from the database operation.

Finally, it returns success: true along with the appointment details, serialized using the serializeAppointment helper function. Error handling is in place for database issues.

Design Decisions

Several key design decisions underpin the structure and functionality of these tools:

• Separation of Concerns (Zod vs. TOOL_DEFINITIONS): The input validation schemas are defined using Zod (e.g., GetAppointmentsTodayInput). This provides robust runtime validation for incoming requests. Separately, the TOOL_DEFINITIONS array provides a JSON Schema-like representation of these inputs and tool descriptions. This separation allows the Zod schemas to be used directly in TypeScript for type safety and validation, while TOOL_DEFINITIONS can be exposed to external systems (like an LLM agent or an OpenAPI specification) that require a standardized, descriptive format for tool discovery and invocation. While this introduces some duplication, it caters to different consumers effectively.

• Database Interaction with Drizzle ORM: All database operations are performed using Drizzle ORM. This choice provides a type-safe and fluent API for interacting with the PostgreSQL database, reducing the likelihood of SQL injection vulnerabilities and improving developer experience compared to raw SQL queries. The use of db and schema from mcp-server/src/db.js centralizes database access.

• Explicit Timezone Handling: The getAppointmentsToday tool explicitly handles timezones using Intl.DateTimeFormat and a custom tzOffsetMs function. This is a deliberate choice to ensure that "today" is resolved correctly based on the professional's local time, rather than relying on server-local time or UTC, which could lead to incorrect results for users in different geographical locations.

• Robust Phone Number Matching: The getCustomerByPhone tool leverages normalizeBrazilianPhone and getPhoneVariants from mcp-server/src/phone.ts. This acknowledges the real-world complexity of phone number formats and aims to maximize match rates by trying multiple common representations, improving the user experience for customer lookups.

• Idempotency in updateAppointmentStatus: The updateAppointmentStatus tool includes an explicit check (if (existing.status === status)) to prevent unnecessary database writes if the appointment's status is already the target status. This makes the operation idempotent, which is a good practice for API design, especially in distributed systems where retries might occur.

• Centralized Status Definitions: The APPOINTMENT_STATUSES array is defined once as a const and then used to create a Zod enum (z.enum(APPOINTMENT_STATUSES)). This ensures consistency across the application regarding valid appointment statuses and provides type safety.

Potential Improvements

While the current implementation is functional, several areas could be improved to enhance robustness, maintainability, and performance:

1. Automate TOOL_DEFINITIONS Generation: The TOOL_DEFINITIONS array manually duplicates information already present in the Zod schemas (e.g., GetAppointmentsTodayInput). This is a source of potential inconsistencies. A build-time script or a utility function could automatically derive the JSON Schema representations for TOOL_DEFINITIONS directly from the Zod schemas, ensuring they always stay in sync. This would reduce boilerplate and the risk of human error in mcp-server/src/tools.ts.

2. Optimize getCustomerByPhone for N+1 Queries: In getCustomerByPhone, when multiple clients match a phone number, the code performs a separate database query for each client to fetch their last 5 appointments (clients.map(async (client) => { ... await db.select(...) })). This leads to an N+1 query problem, where N is the number of matching clients. A more efficient approach would be to fetch all relevant appointments in a single query using a WHERE IN clause on clientId after retrieving the initial clients, and then group them by client in application code. This would significantly reduce database round trips and improve performance, especially for phone numbers that might match many clients.

3. Refine Timezone Handling with a Library: The custom tzOffsetMs function in mcp-server/src/tools.ts is a bespoke solution for timezone offset calculation. While it works, timezone logic can be notoriously complex due to daylight saving changes, historical data, and edge cases. Replacing this custom function with a well-tested, dedicated date-time library (like date-fns-tz or luxon) would likely provide more robust and accurate timezone calculations, reducing the risk of subtle bugs and improving maintainability.

4. Centralize Error Handling: The try/catch blocks in each tool handler (e.g., getAppointmentsToday, getCustomerByPhone) are largely repetitive, returning an object with an error property. A higher-order function or a centralized error handling utility could wrap the core logic of each handler, abstracting away the repetitive try/catch boilerplate and ensuring consistent error response formats across all tools.

References

• mcp-server/src/tools.ts
• mcp-server/src/phone.ts
• mcp-server/src/db.js