Project: Smart Customer Agent with Database Querying

In this project, we will build a smart AI customer support agent. The agent can answer conversational questions, but when asked about order statuses, it will automatically query our local PostgreSQL database using tool calling.

1. System Interaction Architecture

graph TD
    A[User: Where is my order LS-1002?] --> B[Agent endpoint reads database tools schema]
    B --> C[Model requests call: queryDatabaseForOrder]
    C --> D[Prisma looks up Order details in PostgreSQL]
    D --> E[Model gets DB results: Status shipped]
    E --> F[Agent: Your order LS-1002 was shipped!]

2. Implementing the Smart Agent Endpoint

Create a route endpoint that processes the query and handles database lookup tools:

// app/api/support/agent/route.ts
import { NextResponse } from "next/server";
import { openai } from "../../../../lib/openai";
import { prisma } from "../../../../lib/prisma";
import { ChatCompletionTool } from "openai/resources/chat/completions";

// 1. Declare the database tool schema
const supportTools: ChatCompletionTool[] = [
  {
    type: "function",
    function: {
      name: "queryOrderDetails",
      description: "Lookup shipping and payment details for a specific order number.",
      parameters: {
        type: "object",
        properties: {
          orderNumber: {
            type: "string",
            description: "The unique order identification code (e.g. LS-1025).",
          },
        },
        required: ["orderNumber"],
        additionalProperties: false,
      },
    },
  },
];

// 2. Define the local function that runs on your server
async function queryOrderDetails(orderNumber: string) {
  const order = await prisma.order.findUnique({
    where: { orderNumber },
  });

  if (!order) {
    return JSON.stringify({ error: "Order not found" });
  }

  return JSON.stringify({
    orderNumber: order.orderNumber,
    status: order.status, // e.g. "shipped", "paid"
    amount: order.amount,
  });
}

export async function POST(req: Request) {
  const { userMessage } = await req.json();

  const messages: any[] = [
    { 
      role: "system", 
      content: "You are a customer support agent. Use tools to look up order details when asked. Be friendly." 
    },
    { role: "user", content: userMessage },
  ];

  try {
    // Call completions with support tools registered
    const completion = await openai.chat.completions.create({
      model: "gpt-4o-mini",
      messages,
      tools: supportTools,
    });

    const responseMessage = completion.choices[0].message;
    const toolCalls = responseMessage.tool_calls;

    if (toolCalls) {
      messages.push(responseMessage); // Save assistant message

      for (const toolCall of toolCalls) {
        if (toolCall.function.name === "queryOrderDetails") {
          const args = JSON.parse(toolCall.function.arguments);
          const result = await queryOrderDetails(args.orderNumber);

          messages.push({
            role: "tool",
            tool_call_id: toolCall.id,
            name: "queryOrderDetails",
            content: result,
          });
        }
      }

      // Re-submit results back to OpenAI
      const finalCompletion = await openai.chat.completions.create({
        model: "gpt-4o-mini",
        messages,
      });

      return NextResponse.json({ reply: finalCompletion.choices[0].message.content });
    }

    return NextResponse.json({ reply: responseMessage.content });
  } catch (err: any) {
    return NextResponse.json({ error: err.message }, { status: 500 });
  }
}

3. Frontend Chat Test Action

Call the /api/support/agent endpoint from a chat interface. Ask "What is the status of my order LS-9922?" and confirm that the agent queries the database and gives a correct answer.