Using Supabase for Text to SQL

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

What is Supabase?

Supabase is an open-source platform service designed to simplify the development workflow of modern applications. Its core features include:

• Database: Can store various types of data.

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

• Account Management: Manages user accounts and passwords for you.

• Built-in Authentication Mechanism: Built-in authentication mechanism simplifies user identity management processes and provides multiple authentication methods.

• Auto-generated API (i.e., data access paths): Allows you to retrieve data from the database in a simple way.

What are the benefits of integrating Supabase?

• Cross-query multiple reports: Supabase supports multiple tables that are interrelated, allowing queries from value A in report A to find its relationship with report B

• Create indexes to improve query efficiency: You can create indexes for frequently queried content, allowing the AI assistant to find the content you need more precisely

Create Your Supabase

1. Create a Supabase Account

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

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

• After logging in, you can create a new organization or use an existing organization for operations. Within the organization, create a project - each project will have its own independent database.

2. Enter the Database Page

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

Create a New Table

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

Supabase provides multiple ways to create new tables:

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

• 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 be the column names, with columns separated by commas (CSV) or tabs (TSV, which is the space size when pressing the Tab key on the keyboard).

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

Choose import method

Import plain text

Preview results

Primary Key

After import is complete, you will be redirected to the settings page. At this point, you must specify a primary key. The primary key is like an ID number, serving as a unique value to identify each piece of data. Here, we choose the customer number as the primary key.

Foreign Key

Scrolling down, you can see the Foreign key designation. A Foreign Key is like an address that can correspond to the source location of this data or other more detailed information.

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

• Customers Table:

  • CustomerID - Primary Key

  • CustomerName

  • Phone

  • Address

• Orders Table:

  • OrderID - Primary Key

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

  • OrderDate

  • TotalAmount

In this example, "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:

Foreign keys should be placed on the "many" side

Therefore:

• ✅ Set customer_id (foreign key) in the Order Table, as it corresponds to the "many" side in the one-to-many relationship

• ❌ Customer Table doesn't need to store order information

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

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

3. Creation Complete

After the table is created, you have a complete database and can query data within the database using SQL syntax!

How to Create a Supabase Tool

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

For tool introduction, please refer to: Tool Function Overview

1

Create a Server on the MCP Service Platform and Connect with Supabase Service

For how to connect MCP tools, please refer to: Remote MCP Service Overview. Currently, only the Composio platform supports Supabase platform connection

2

Enable Available Functions in Composio

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

Composio has pre-selected basic content in Important, you don't need to configure it additionally, just make sure the Important option is checked

3

Add the Built Supabase Tool to the Available Tools List

For how to create MCP tools, please refer to: Creating MCP Tools

Supabase Tool Connection URL

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

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

2. Delete "?include_composio_helper_actions=true" directly

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

Please be sure to delete the above content, otherwise the AI assistant will not be able to use the supabase tool correctly

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

Please click Save on the tool usage page, otherwise the AI assistant still cannot use the Supabase tool

Effects of Using the Supabase Tool

Through the MaiAgent AI assistant combined with the Supabase tool, you only need to use everyday language to describe the data you want to query, and Supabase will automatically generate the corresponding SQL syntax and extract the required information from the relational database.

Example Database

🏢 1. Customers Table (Customer Information)

Column Name	Primary Key	Required	Description
CustomerID	✅	✅	Customer unique identifier
CustomerName	❌	❌	Customer company or personal name
CustomerType	❌	❌	Customer classification (e.g., retailer, restaurant, distributor)
ContactName	❌	❌	Primary contact name
Phone	❌	❌	Contact phone number
Email	❌	❌	Email address
Address	❌	❌	Customer address
Region	❌	❌	Geographic region (e.g., North, South)
CustomerLevel	❌	❌	Customer importance level (A, B, C tier)

🔑 Primary Key: CustomerID 🔗 Foreign Key Relationships: None

---

📦 2. Orders Table (Order Information)

Column Name	Primary Key	Required	Description
OrderID	✅	✅	Order unique identifier
CustomerID	❌	❌	Links to Customers table
OrderDate	❌	❌	Order creation date
DeliveryDate	❌	❌	Expected or actual delivery date
PaymentMethod	❌	❌	Payment method (cash, credit card, wire transfer)
OrderStatus	❌	❌	Order processing status
TotalAmount	❌	❌	Order total amount (numeric type)
ShippingFee	❌	❌	Shipping cost

🔑 Primary Key: OrderID 🔗 Foreign Key Relationships:

• CustomerID → Customers.CustomerID (CustomerID in Orders corresponds to CustomerID in the Customer table)

---

🛍️ 3. Products Table (Product Information)

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

🔑 Primary Key: ProductID 🔗 Foreign Key Relationships: None

Scenario 1: Tracking Incomplete Orders

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

• Natural Language Input: In the AI assistant's Q&A, you only need to enter: "Please tell me what incomplete orders there are". The AI assistant automatically invokes the tool and generates an SQL structured query statement.

• Supabase Automatic Query: The AI assistant will automatically invoke the Supabase tool, converting your natural language into an SQL query statement, for example:

Combining the query results with the AI assistant's analysis, the AI assistant will respond with the following order content, sorted by priority:

Through the collaboration of the Supabase tool and the AI assistant, you can easily track incomplete orders and receive analysis and sorting suggestions provided by the AI assistant to process orders more effectively and improve customer satisfaction.

Scenario 2: Querying Customer Contact Information for Incomplete Orders

• OR003 is an order still being processed, the customer is CU003, and the contact person is Huang Purchasing

• Natural Language Input: In the AI assistant's Q&A, enter: "For orders still being processed, who should I contact". The AI assistant automatically invokes the tool and generates an SQL structured query statement.

• Supabase Automatic Corresponding Query: You can see that although the conversation content with the AI assistant all relates to the order table, through the set foreign key correspondence relationship, supabase can know that the Customer ID here corresponds to the ID query in the Customers table, so the returned content is from the Customers table.

• AI Assistant Response: The AI assistant then comprehensively analyzes and responds with the correct contact information and other contact methods

Through the Supabase tool, you can fully utilize the table correspondence relationships in the database, easily extract information from multiple related tables, and receive a list of customer names provided by the AI assistant. The correspondence relationships between tables and clear definitions ensure data relevance and consistency, making query results more reliable.

Additional Notes:

• You can adjust the natural language input according to actual needs, for example: "Please tell me what incomplete orders there are today", "Please tell me what incomplete orders VIP customers have", etc. The Supabase tool can accurately parse and execute queries.

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

The tool can only call content you store in the database. If you have analysis needs, please be sure to upload the data to the database before starting analysis.