The API Agent is a universal Multi-Modal Command Protocol (MCP) server designed to enable natural language querying and advanced data manipulation for GraphQL and REST APIs. It acts as an intelligent intermediary, transforming raw API data into structured, queryable information, even when the underlying API lacks sophisticated data processing capabilities.

The core problem it addresses is the limitation of direct API interaction for complex analytical tasks. Many APIs return unsorted or unaggregated data, making operations like ranking, filtering, joining across endpoints, or grouping by specific attributes difficult or impossible without significant client-side post-processing. The API Agent automates this by providing an intelligent layer that can fetch, store, and process API data.

Core Methodology and Technical Details:

1. Schema Introspection: Upon initial configuration with a target API (specified via X-Target-URL and X-API-Type headers), the API Agent automatically performs schema introspection. For GraphQL APIs, it queries the GraphQL schema. For REST APIs, it processes the OpenAPI/Swagger specification. This automated introspection eliminates the need for manual configuration or custom code per API, forming the foundational understanding of the API's available data and operations.

2. Natural Language Query Processing via LLMs:
   • When an MCP client sends a natural language question (e.g., "Show characters from Earth, only alive ones, group by species") to the API Agent's /mcp endpoint, the request is routed to the internal agent system (built using the OpenAI Agents SDK).
   • The agent, leveraging a configured Large Language Model (LLM, e.g., gpt-5.2), receives the user's natural language query along with the previously introspected API schema.
   • The LLM's role is to interpret the user's intent and translate it into a sequence of executable API calls (for GraphQL, typically a query and variables; for REST, method, path, and params). This involves mapping natural language concepts to schema types, fields, and operations.

3. Data Fetching and Ingestion into DuckDB:
   • The agent executes the LLM-generated API calls against the target API via an internal HTTP client.
   • The raw JSON or other structured data returned by the API is then ingested and stored in a local, embedded DuckDB instance. DuckDB serves as a high-performance, in-process analytical data management system, allowing the agent to treat API data as relational tables. This step is crucial as it creates a structured data environment for subsequent SQL operations.

4. SQL Post-processing with DuckDB:
   • After the raw API data is loaded into DuckDB, the agent (guided by the LLM's understanding of the user's query) generates and executes SQL queries against the DuckDB instance. This enables sophisticated data manipulation that might not be natively supported by the API. Examples include:
     • Ranking: SELECT ... ORDER BY ... LIMIT N
     • Filtering: SELECT ... WHERE condition
     • Aggregation: SELECT column_1, COUNT(*), SUM(column_2) FROM table_name GROUP BY column_1
     • Joins: Although not explicitly shown in simple examples, the architecture allows for joining data from multiple API calls or endpoints within DuckDB, effectively overcoming limitations of single-endpoint queries.
   • The result of these SQL operations, a refined and processed dataset, is then returned to the MCP client.

5. Recipe Learning and Caching:
   • A key differentiator is the "recipe learning" mechanism. When a complex natural language query is successfully executed (involving API calls and SQL post-processing), the agent extracts the underlying pipeline into a parameterized template, referred to as a "recipe."
   • These recipes are cached (e.g., $r_{recipe_slug}$) and exposed as new dynamic tools within the MCP server.
   • Subsequent calls using these $r_{recipe_slug}$ tools, with appropriate parameters, bypass the LLM reasoning phase entirely. This significantly reduces latency and cost for frequently executed or similar queries, as the execution directly triggers the cached API calls and DuckDB SQL operations. Recipes automatically expire if the underlying API schema changes.

Architectural Components:

• FastMCP: The underlying web server framework providing the MCP endpoint.
• OpenAI Agents SDK: Orchestrates the LLM interactions, tool selection (API calls, SQL execution), and overall agentic behavior.
• DuckDB: Provides the robust in-process analytical database for data storage and SQL-based post-processing.
• HTTP Client: For making calls to the target GraphQL/REST APIs.

Safety and Configuration:

• The agent operates in a "read-only" mode by default. Mutations (POST, PUT, DELETE, PATCH) are blocked unless explicitly allowed via the X-Allow-Unsafe-Paths header, which accepts a JSON array of fnmatch globs for specific paths.
• Configuration is managed via environment variables such as OPENAI_API_KEY, API_AGENT_MODEL_NAME, and API_AGENT_ENABLE_RECIPES. OpenTelemetry tracing is supported for observability.

In summary, the API Agent is an intelligent, schema-aware proxy that empowers users to interact with GraphQL and REST APIs using natural language, overcoming API limitations by intelligently fetching data, storing it in DuckDB, and performing complex SQL-driven transformations, while also learning and caching frequently used query patterns to optimize performance.