Dashboard AI Assistant

This page details the implementation of the in-dashboard AI chat assistant, covering its API endpoint, integration with the Groq SDK, and the data retrieval tools it uses to respond to user queries.

API Endpoint

The AI assistant's logic is implemented in src/app/api/ai/chat/route.ts, a Next.js App Router API route that handles POST requests. It orchestrates authentication, authorization, AI model interaction via Groq, and execution of data-fetching tools.

Authentication and Authorization

Access control is enforced at the start of the POST handler:

• getUser() from src/lib/api retrieves the authenticated user from the session.
• getProfessional() fetches the associated professional profile using the user ID.
• If either check fails, the route returns a 401 Unauthorized via unauthorized().
• Feature access is validated using canUseFeature() from src/lib/plan-guard, which checks the professional's current plan and trial status. Users without the required plan receive a 403 Forbidden response.

AI Model and Prompting

The assistant uses the Groq SDK (groq-sdk) initialized with process.env.GROQ_API_KEY. The model used is llama-3.3-70b-versatile.

A SYSTEM_PROMPT string defines the assistant's behavior:

• Respond in Portuguese.
• Use tools for data retrieval; never invent numbers.
• Format financial values in BRL.
• Apply specific formatting rules for "no-show rate" and "monthly summary" responses.

The assistant is equipped with three tools defined in the TOOLS array, each corresponding to a data-fetching function.

AI Tools and Data Retrieval

The assistant can invoke three tools to retrieve real data from the database using Drizzle ORM. All queries are scoped to the authenticated professional's professionalId.

get_stats

Retrieves appointment statistics over the last six months.

• Function: getStats(professionalId, sessionPrice)
• Query: Selects all appointments from the last six months where professionalId matches.
• Processing:
  • Calculates global totals: completed appointments, no-shows, no-show rate, and revenue (in BRL).
  • Breaks down data month-by-month, computing the same metrics per month.
  • Returns both global and per-month summaries.
• Use case: Triggered for queries about no-show rates, monthly summaries, revenue, or trends.

get_upcoming

Fetches upcoming appointments for the next 7 days.

• Function: getUpcoming(professionalId)
• Query: Joins appointments and clients tables to get client names.
• Filters: Appointments starting from now up to 7 days ahead.
• Order: By startsAt ascending.
• Limit: 20 results.
• Output: Client name, date, end time, and status.
• Use case: "What do I have scheduled this week?"

get_no_show_clients

Ranks clients by no-show frequency over the last 6 months.

• Function: getNoShowClients(professionalId)
• Query: Groups appointments by client, counting total appointments and no-shows using SQL filter clauses.
• Filters: Appointments from the last 6 months.
• Order: By no-show count descending.
• Limit: 10 clients.
• Post-processing: Filters out clients with zero no-shows and computes individual no-show rates.
• Use case: "Which clients miss the most appointments?"

Known Gaps

The current implementation of authentication for API routes, including src/app/api/ai/chat/route.ts, relies on explicit calls to getUser() and getProfessional() within each route handler. This approach, while explicit, places the burden of authentication on every new route author. A more centralized authentication mechanism, such as a middleware.ts file, could reduce boilerplate and potential for oversight, as flagged by security audits.

Why this shape

The design prioritizes factual accuracy by restricting the AI to a fixed set of tools that execute verified database queries. This prevents hallucination and ensures responses are grounded in real data. The use of Groq was selected for low-latency tool calling and response generation. By centralizing business logic in the API route and using Drizzle ORM for type-safe queries, the implementation maintains consistency with the rest of the App Router architecture. The absence of middleware keeps the control flow explicit and auditable, aligning with the project's pragmatic security model.