Text to SQL with Supabase

This article introduces how to use Supabase for Text to SQL in MaiAgent

What Is Supabase?

Supabase is an open-source platform service designed to simplify modern application development. Its core features include:

• Database: Store various types of data.

• Real-time updates: Your application is notified immediately when data changes.

• Account management: Manage user accounts and passwords.

• Built-in authentication mechanism: A built-in authentication mechanism that simplifies user identity management and provides multiple authentication methods.

• Auto-generated API (data access endpoints): Access your database data through simple methods.

Benefits of Integrating Supabase

• Cross-table queries: Supabase supports multi-table relationships, allowing you to query relationships between Table A and Table B.

• Indexing for improved query performance: You can create indexes on frequently queried content to help the AI assistant find what you need more precisely.

Set Up Your Supabase

1. Create a Supabase Account

• First, go to the Supabase website and click "Sign in / Start your project."

If you haven't registered yet, please create an account first to proceed with the following steps

• After logging in, you can create a new organization or use an existing one. Within the organization, create a project — each project has its own independent database.

2. Navigate to the Database Page

After entering the project, click Database > Tables in the left navigation bar to add data

Create a New Table

Click "New Table" to create a table and name it:

Supabase offers several ways to create a new table:

• Manually add columns: Suitable for designing the table structure from scratch. You can add columns one by one and set each column's data type, default value, etc.

• Import .csv/.tsv or plain text: Suitable for quickly creating tables, especially when you already have existing data.

  • Notes:

    • The first row of a plain text file must contain column names, with columns separated by commas (CSV) or tabs (TSV).

Here, choose to import data: click "Import data from CSV" and paste a tab-separated text file. Scroll down to see the table result. After import is complete, click "Save."

Primary Key

After the import is complete, you are redirected to the settings page where you must specify a primary key. A primary key is like an ID number — it uniquely identifies each record. Here, we select Customer ID as the primary key.

Foreign Key

Scroll down to see the Foreign Key settings. A Foreign Key is like an address that points to the source of a record or more detailed data associated with it.

Suppose we have two tables: "Customers" and "Orders."

• Customers Table:

  • CustomerID - Primary Key

  • CustomerName

  • Phone

  • Address

• Orders Table:

  • OrderID - Primary Key

  • CustomerID - Foreign Key (references CustomerID in the Customers table)

  • OrderDate

  • TotalAmount

In this example, the "CustomerID" in the Orders table is a foreign key that references the primary key "CustomerID" in the Customers table. Through this foreign key, we can identify which customer placed each order.

In this example, the foreign key should be placed in the Order Table:

Relationship direction:

• One customer → can have multiple orders (one-to-many relationship)

• One order → belongs to only one customer

Foreign key principle:

The foreign key should be placed on the "many" side

Therefore:

• The Order Table has a customer_id (foreign key), because it is the "many" side in the one-to-many relationship

• The Customer Table does not need to store order information

Therefore, we set up the Order Table to link the Customer ID in the Customer Table to the Customer ID in the Order Table.

After the relationship is configured, click "Save" to establish the database relationship.

3. Setup Complete

Once the tables are created, you have a complete database and can query data within it using SQL syntax.

How to Create a Supabase Tool

To use the Supabase tool on MaiAgent, you need to create it as an MCP tool so the AI assistant can access Supabase functionality:

For tool introduction, see: Tool Overview

1

Create a Server on an MCP Service Platform and Connect to Supabase

For how to connect MCP tools, see: Remote MCP Service Overview. Currently, only the Composio Platform supports Supabase integration.

2

Enable Available Features in Composio

When connecting to Composio, enable basic database operations such as create, delete, query, etc.

Composio has basic options pre-selected under Important by default. You do not need additional configuration — just make sure the Important options remain checked.

3

Add the Configured Supabase Tool to the Available Tools List

For how to create MCP tools, see: Create MCP Tools

Supabase Tool Connection URL

After creating the server service on the MCP service platform, process the URL as follows:

1. Original URL (obtained from the MCP Server): https://backend.composio.dev/v3/mcp/12345678/mcp?include_composio_helper_actions=true

2. Remove "?include_composio_helper_actions=true" entirely

3. New URL (to paste on the MaiAgent tools page): https://backend.composio.dev/v3/mcp/12345678/mcp

You must remove the above content; otherwise, the AI assistant will not be able to use the Supabase tool correctly.

After the tool is created, go to the AI assistant settings and add the Supabase tool to the assistant's available tools list:

Make sure to click Save on the tools page; otherwise, the AI assistant still cannot use the Supabase tool.

Using the Supabase Tool

With MaiAgent AI assistant and the Supabase tool, simply describe the data you want to query in everyday language, and Supabase automatically generates the corresponding SQL syntax to extract the information you need from the relational database.

Sample Database

1. Customers Table

Column Name	Primary Key	Required	Description
CustomerID	Yes	Yes	Unique customer identifier
CustomerName	No	No	Customer company or individual name
CustomerType	No	No	Customer category (e.g., Retailer, Restaurant, Distributor)
ContactName	No	No	Primary contact name
Phone	No	No	Contact phone number
Email	No	No	Email address
Address	No	No	Customer address
Region	No	No	Geographic region (e.g., North, South)
CustomerLevel	No	No	Customer importance level (A, B, C)

Primary Key: CustomerID Foreign Key Relationships: None

---

2. Orders Table

Column Name	Primary Key	Required	Description
OrderID	Yes	Yes	Unique order identifier
CustomerID	No	No	References the Customers table
OrderDate	No	No	Order creation date
DeliveryDate	No	No	Expected or actual delivery date
PaymentMethod	No	No	Payment method (Cash, Credit Card, Wire Transfer)
OrderStatus	No	No	Order processing status
TotalAmount	No	No	Order total amount (numeric)
ShippingFee	No	No	Shipping cost

Primary Key: OrderID Foreign Key Relationships:

• CustomerID → Customers.CustomerID (The CustomerID in Orders references the CustomerID in the Customers table)

---

3. Products Table

Column Name	Primary Key	Required	Description
ProductID	Yes	Yes	Unique product identifier
ProductName	No	No	Product name
Description	No	No	Detailed product description
Category	No	No	Product category
Brand	No	No	Product brand
Size	No	No	Product specification or size
Cost	No	No	Product cost (numeric)
Price	No	No	Product selling price (numeric)
StockQuantity	No	No	Current stock quantity (numeric)

Primary Key: ProductID Foreign Key Relationships: None

Scenario 1: Track Incomplete Orders

• Database state: The Orders table has two incomplete orders: OR003 and OR004.

• Natural language input: In the AI assistant's chat, simply type: "Tell me which orders are not yet completed." The AI assistant automatically invokes the tool and generates a structured SQL query.

• Supabase automatic query: The AI assistant automatically invokes the Supabase tool, converting your natural language into an SQL query, such as:

Combining the query results with the AI assistant's analysis, the AI assistant responds with the following order information, sorted by priority:

Through the collaboration of the Supabase tool and the AI assistant, you can easily track incomplete orders and receive analysis and prioritization suggestions from the AI assistant, enabling more efficient order processing and improved customer satisfaction.

Scenario 2: Query Contact Information for Incomplete Orders

• Order OR003 is still being processed, with customer CU003 and the contact person being Huang Procurement

• Natural language input: In the AI assistant's chat, type: "Who should I contact for the orders still being processed?" The AI assistant automatically invokes the tool and generates a structured SQL query.

• Supabase automatic relational query: Although the conversation with the AI assistant is about the Orders table, through the configured foreign key relationships, Supabase knows that the Customer ID here references the ID in the Customers table, so the returned content comes from the Customers table.

• AI assistant response: The AI assistant then provides the correct contact information and other contact methods based on its analysis.

Through the Supabase tool, you can fully leverage table relationships in the database to easily extract information from multiple related tables and receive a customer name list from the AI assistant. The clearly defined relationships between tables ensure data consistency and reliability, making query results more trustworthy.

Additional Notes:

• You can adjust the natural language input based on your actual needs, such as: "Tell me about today's incomplete orders" or "Tell me about VIP customers' incomplete orders." The Supabase tool can accurately parse and execute these queries.

• The AI assistant can further integrate other information, such as inventory status and logistics information, to provide more comprehensive order analysis.

The tool can only access data stored in your database. If you have analysis needs, make sure to upload the data to the database before starting the analysis.