Build a cool AI agent with Supabase, Hyperdrive and Cloudflare Worker

Learn how to build an AI chat agent that queries a Supabase database using Hyperdrive and the Agent SDK. This step-by-step guide covers setup, code, and real-world applications, with examples for natural language queries and data insights.

Imagine having an AI that not only chats with you but also dives into your database, runs queries, and delivers insights in plain English. That’s exactly what we’re exploring today: an AI chat agent powered by a Supabase database and Hyperdrive, capable of querying data and performing actions on your behalf. This guide walks you through how it works, how to build one, and ways to extend it for your own projects. Let’s dive into the details with a focus on clarity and practicality.

What Is This AI Chat Agent?

At its core, this AI chat agent is a conversational tool that interacts with users and connects directly to a database—in this case, a Supabase database. Unlike a standard chatbot that’s limited to answering questions based on pre-trained knowledge, this agent can execute SQL queries, fetch data, and even perform calculations to provide meaningful insights. It’s built using the Workers platform and the Agent SDK, with Hyperdrive enabling fast, secure database connections.

The agent uses a large language model (like GPT-4) to interpret natural language inputs, generate appropriate SQL queries, and return results in a human-readable format. For example, you can ask, “What are my top-selling books?” or “What percentage of sales come from my top three products?” and the agent will query the database, process the data, and respond with clear answers.

Why Supabase and Hyperdrive?

Supabase is an open-source Firebase alternative that provides a PostgreSQL database with a user-friendly interface. It’s great for developers because it combines the power of a relational database with real-time capabilities and easy-to-use APIs. Hyperdrive, on the other hand, is a tool that optimizes database connections for serverless environments like Workers, reducing latency and ensuring secure, direct access to your database.

Together, they make a perfect pair for building scalable, performant applications. The agent in this guide connects to a Supabase database via Hyperdrive, allowing it to run SQL queries directly without relying on REST APIs. This direct connection is faster and gives you more control over the queries being executed.

How the Agent Works: A Quick Demo

To understand the agent’s capabilities, let’s look at a practical example. Suppose you have a Supabase database with two tables: books and elements. The books table contains fields like id, book_name, price, quantity_sold, and publication_date. You can interact with the agent through a simple chat interface and ask questions about your data.

Example 1: Listing Tables

If you ask, “What tables do I have in my database?” the agent responds by querying the database’s schema and returning a list of tables: books and elements. Behind the scenes, it generates an SQL query like SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; and executes it using a tool called queryDatabase.

Example 2: Fetching Data

Want to see what’s in the books table? Just ask, “What’s in the books table?” The agent runs a SELECT * FROM books; query and displays the results in the chat interface. You can see details like book names, prices, and quantities sold, all pulled directly from the database.

Example 3: Advanced Queries

Now let’s get more complex. Ask, “What are the top best-selling books?” The agent generates a query like SELECT book_name, quantity_sold FROM books ORDER BY quantity_sold DESC LIMIT 5; to rank books by sales. It then formats the results into a readable list, such as:

• “The Great Novel” – 500 units sold
• “Tech Trends” – 450 units sold
• “Mystery Manor” – 400 units sold

Example 4: Analytical Insights

For something even more advanced, try asking, “What percentage of total sales comes from the top three best-selling books?” This requires multiple steps:

1. Identify the top three books by sales: SELECT book_name, quantity_sold FROM books ORDER BY quantity_sold DESC LIMIT 3;
2. Calculate their total sales: SELECT SUM(quantity_sold) FROM books WHERE book_name IN (...);
3. Calculate the overall sales: SELECT SUM(quantity_sold) FROM books;
4. Compute the percentage: (top_three_sales / total_sales) * 100.

The agent handles all of this, returning a clear answer like, “The top three books account for 40% of total sales.” This shows the power of combining natural language processing with database access.

Debugging the Agent

To see what’s happening under the hood, you can enable debugging mode in the chat interface. This reveals the agent’s thought process:

• It selects the queryDatabase tool.
• It generates the appropriate SQL query.
• It executes the query via Hyperdrive.
• It processes the results and formats them for the user.

Debugging mode is especially useful for developers who want to ensure the agent is generating correct queries or to troubleshoot issues with complex requests.

Building the Agent

Now that you’ve seen the agent in action, let’s explore how to build one. The application is a Workers-based project using the Agent SDK, with JavaScript (or TypeScript) as the primary language. Below is a step-by-step breakdown of the code structure and key components.

Project Setup

The project is structured as a Workers application, which means it runs in a serverless environment. The main files you’ll work with are:

• server.ts: Handles incoming requests and routes them to the agent.
• agent.js: Defines the agent’s behavior and integrates tools like queryDatabase.
• tools/queryDatabase.js: Contains the logic for querying the Supabase database.

You’ll also need a Supabase account and a Hyperdrive configuration to connect to your database. The Supabase dashboard provides the connection details (host, port, database name, user, and password), which you’ll use to set up Hyperdrive.

Connecting to Supabase with Hyperdrive

Hyperdrive is configured in the Workers dashboard or via a wrangler.toml file. Here’s an example configuration:

[[hyperdrive]]
name = "supabase"
database = "your_database_name"
host = "your_supabase_host"
port = 5432
user = "your_username"
password = "your_password"

This configuration allows the agent to connect to your Supabase instance with minimal latency. Hyperdrive caches connections and optimizes them for serverless environments, ensuring fast query execution.

The Server File (server.ts)

The server.ts file is the entry point for the application. It receives HTTP or WebSocket requests and routes them to the agent. Here’s a simplified version:

import { Agent } from './agent';

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const agent = new Agent(env);
    return agent.handleRequest(request);
  },
};

The env object contains environment variables, including the Hyperdrive connection details. The agent processes the request, which could be a chat message or a WebSocket connection for real-time interaction.

The Agent Class (agent.js)

The agent is a JavaScript class that orchestrates the chat experience and tool execution. It uses a large language model (e.g., GPT-4) to interpret user inputs and decide which tools to call. Here’s a basic example:

import { queryDatabase } from './tools/queryDatabase';

class Agent {
  constructor(env) {
    this.env = env;
    this.model = 'gpt-4'; // Can be swapped for other models
    this.tools = [queryDatabase];
  }

  async handleRequest(request) {
    // Handle HTTP or WebSocket requests
    const message = await this.parseRequest(request);
    const response = await this.processMessage(message);
    return new Response(response);
  }

  async processMessage(message) {
    // Use the model to interpret the message
    const toolCall = await this.model.generateToolCall(message, this.tools);
    if (toolCall) {
      const result = await this.executeTool(toolCall);
      return this.formatResponse(result);
    }
    return 'No action required.';
  }

  async executeTool(toolCall) {
    const tool = this.tools.find(t => t.name === toolCall.name);
    if (tool) {
      return await tool.execute(toolCall.parameters);
    }
    throw new Error('Tool not found.');
  }
}

export { Agent };

The agent:

• Parses incoming messages.
• Uses the model to decide if a tool (like queryDatabase) needs to be called.
• Executes the tool and formats the response.

The queryDatabase Tool (tools/queryDatabase.js)

The queryDatabase tool is where the magic happens. It defines how the agent interacts with the Supabase database. Here’s an example implementation:

import { createClient } from '@supabase/supabase-js';

const queryDatabase = {
  name: 'queryDatabase',
  description: 'Executes an SQL query on the Supabase database.',
  parameters: {
    query: { type: 'string', description: 'The SQL query to execute.' },
  },
  async execute({ query }, env) {
    const supabase = createClient(
      env.HYPERDRIVE.connectionString,
      env.SUPABASE_KEY
    );
    const { data, error } = await supabase.rpc('execute_query', { query });
    if (error) throw new Error(error.message);
    return data;
  },
};

export { queryDatabase };

This tool:

• Takes an SQL query as input.
• Uses the Supabase client (via Hyperdrive) to execute the query.
• Returns the results or throws an error if something goes wrong.

Adding Confirmation for Sensitive Queries

In a production environment, you might want the agent to ask for confirmation before running certain queries (e.g., DELETE or UPDATE statements). You can implement this by modifying the tool to return a confirmation prompt instead of executing the query directly:

const queryDatabaseWithConfirmation = {
  name: 'queryDatabaseWithConfirmation',
  description: 'Executes an SQL query after user confirmation.',
  parameters: {
    query: { type: 'string', description: 'The SQL query to execute.' },
  },
  async execute({ query }, env) {
    // Return a confirmation prompt instead of executing
    return {
      type: 'confirmation',
      message: `Are you sure you want to run this query: ${query}?`,
      query,
    };
  },
};

When the agent receives a confirmation response from the user, it can call the original queryDatabase tool to execute the query. This adds a layer of safety for sensitive operations.

Extending the Agent

The beauty of this setup is its flexibility. Here are some ways to extend the agent for your own use cases:

1. Add More Tools

You can create additional tools to perform other actions, such as:

• Data Updates: A tool to insert or update records in the database.
• External API Calls: A tool to fetch data from third-party APIs and combine it with database results.
• File Generation: A tool to generate CSV or PDF reports based on query results.

Each tool follows the same structure: a name, description, parameters, and an execute function.

2. Support Multiple Databases

While this example uses Supabase, you can adapt the queryDatabase tool to work with other databases like MySQL, MongoDB, or SQLite. Just update the connection logic and query syntax to match the target database.

3. Enhance the UI

The chat interface can be improved with features like:

• Syntax highlighting for SQL queries in debug mode.
• A table view for query results.
• Real-time query suggestions based on table schemas.

4. Integrate with Other Models

The agent uses GPT-4, but you can swap it for other models like LLaMA, Claude, or an open-source alternative. Just ensure the model supports tool-calling capabilities, which are essential for generating and executing SQL queries.

5. Add Authentication

For production use, add user authentication to restrict access to the agent. Supabase provides built-in authentication, which you can integrate with the Workers application using JWT tokens or session management.

Best Practices for Production

Before deploying this agent to a live environment, consider the following:

1. Query Safety

• Sanitize Inputs: Prevent SQL injection by validating queries or using parameterized queries.
• Limit Permissions: Use a read-only database user for the agent to minimize risks.
• Rate Limiting: Implement rate limits to prevent abuse of the chat interface.

2. Error Handling

• Catch and log errors from the database or model to aid debugging.
• Provide user-friendly error messages instead of raw SQL errors.

3. Performance Optimization

• Cache frequent queries using Workers KV or a similar key-value store.
• Use Hyperdrive’s connection pooling to handle high traffic efficiently.

4. Monitoring

• Log all queries and their execution times to monitor performance.
• Set up alerts for failed queries or unusual activity.

Real-World Applications

This AI chat agent can be adapted for various industries and use cases:

• E-commerce: Analyze sales data, track inventory, or generate reports on top products.
• Healthcare: Query patient records (with strict privacy controls) or summarize clinical data.
• Education: Provide insights into student performance or course enrollment trends.
• Finance: Monitor transactions, calculate portfolio performance, or detect anomalies.

By tailoring the tools and database schema, you can make the agent a powerful assistant for any data-driven application.

Getting Started

Ready to build your own AI chat agent? Here’s a quick checklist:

1. Set Up Supabase: Create a Supabase project and populate your database with sample data.
2. Configure Hyperdrive: Add your Supabase connection details to your Workers project.
3. Clone the Sample Code: Start with the GitHub repository for this project (link in the original guide) or write your own using the code snippets above.
4. Test Locally: Use wrangler dev to run the Workers application locally and test the chat interface.
5. Deploy: Deploy to Workers using wrangler deploy and share your agent with the world.

Conclusion

Building an AI chat agent that interacts with a database is a powerful way to combine natural language processing with data analysis. By leveraging Supabase, Hyperdrive, and the Agent SDK, you can create a tool that not only answers questions but also performs complex queries and delivers actionable insights. Whether you’re analyzing sales, managing inventory, or exploring new datasets, this agent can be customized to fit your needs.

Experiment with the code, add your own tools, and explore the possibilities. The combination of serverless computing, real-time database access, and AI makes this an exciting project for any developer.