Wire the order-lookup tool into the support assistant
Python · LLM apps · advanced · greenfield
Wires the support console's order assistant to the database: the model gets the `lookup_orders` tool declaration, and when it asks for a call the endpoint parses the produced arguments, runs the lookup and feeds the result back per the tool-call contract — assistant message first, each result with its matching `tool_call_id` — looping until a final answer or the 5-round cap. An unknown tool name already comes back as a tool error, and provider outages as a clean 502. Exercised email lookups and status-filtered lookups against a seeded orders table and the assistant composed the answers from the returned rows.
Internal assistant on the support console: support agents sign in through the company SSO and are permitted to look up any customer's orders, so the tool needs no per-customer authorization. The model decides when to call the tool and with what arguments. `Order` rows carry `order_id` (int), `customer_email` (str), `status` (one of the four enum values), `total_cents` (int) and `created_at` (non-null timezone-aware `datetime`).
Requirements
- `POST /orders/chat` accepts `{"message": string}` (1–2000 characters, bounds enforced by FastAPI validation) and returns `{"answer": string}`. The assistant has exactly one tool, `lookup_orders`, declared by the JSON schema in `LOOKUP_ORDERS_TOOL`: required `customer_email` (non-empty string, `minLength: 1`), optional `status` (one of `placed`, `shipped`, `delivered`, `cancelled`), optional `limit` (integer 1–50, default 10), and `additionalProperties: false`.
- `tool_call.function.arguments` is an untrusted JSON **string** produced by the model. The parsed arguments must be validated against that declared schema BEFORE the tool runs, with JSON Schema semantics: `integer` accepts any number with an integral value (`5`, `5.0`), never a boolean, and the accepted value is normalised to a Python `int`. Invalid arguments — malformed JSON, a non-object, a missing or empty `customer_email`, wrong types (including `limit` as a bool or a string), `status` outside the enum, `limit` outside 1–50, or any unexpected property — must come back to the model as the content of the matching `role: "tool"` message in the form `{"error": "…"}` (so it can recover, typically by asking for the email). They must never raise an exception, fail the HTTP request, or reach the tool.
- `lookup_orders` itself may trust its keyword arguments — validation is the caller's job, as its docstring documents — and reaches the database only through fully parameterized SQLAlchemy queries.
- Message order follows the chat-completions contract: the assistant message carrying `tool_calls` is appended before any of its `role: "tool"` results, and each result carries its own `tool_call_id`. A model response without tool calls ends the loop and its content is returned (`""` when the content is `None`).
- At most `MAX_TOOL_ROUNDS = 5` model calls per request; if the model is still requesting tools after the fifth call the endpoint responds HTTP 502. Provider exceptions (`RateLimitError`, `APITimeoutError`, `APIStatusError`) also respond 502; the SDK's two built-in retries are the only retry mechanism.
- The model is the pinned dated snapshot `gpt-4o-mini-2024-07-18`.
Files touched
- app/order_tools.py
- app/orders_agent.py
--- app/order_tools.py
+"""Order-lookup tool: the schema declaration and the DB-backed implementation."""
+
+from __future__ import annotations
+
+from typing import Any
+
+from sqlalchemy import select
+from sqlalchemy.ext.asyncio import AsyncSession
+
+from app.models import Order
+
+ORDER_STATUSES = ("placed", "shipped", "delivered", "cancelled")
+
+# Tool declaration sent with every model call. Per the endpoint contract this
+# schema is also the validation contract for model-produced arguments.
+LOOKUP_ORDERS_TOOL: dict[str, Any] = {
+ "type": "function",
+ "function": {
+ "name": "lookup_orders",
+ "description": (
+ "Look up a customer's orders by exact email address, optionally "
+ "filtered by status."
+ ),
+ "parameters": {
+ "type": "object",
+ "properties": {
+ "customer_email": {
+ "type": "string",
+ "minLength": 1,