Introduction

Recently, I have been told an interesting topic, given a background that there's already some data existing, a chatbot that takes human's natural language, and the knowledge of the given data (schemas, background, etc.), and gives SQL query as output. And then the SQL will be executed somehow, and generate visualized data, and present to the user whoever made the prompt.

Given such a requirement, here come the problems to be solved:

• How to safely generate SQL query from natural language?
• How to safely execute SQL query?
• How to visualize the data, and how to present the outcome?
• How to deliver the best experience to the user?

Plan

To solve these problems one by one, we need to first design the system architecture; the good foundation can help us draw a clear roadmap.

Architecture Diagram

The diagram is a simple draft, that proposes two options

• Option 1: Handle all the chatbot logic in a single server, including generate query, communicate with superset and the frontend.
• Option 2: Distribute the different functions into serverless functions, such as edge functions/workers.

As for early stage, we want to keep things simple, and prevent building too many wheels, so we will go for the option 2: Serverless solution, eliminating the need for developing and maintaining a single server, and we can focus on the core logic of the chatbot. To keep that way, we can decide the services to use to build our first MVP.

Choices of Service Provider

We will be choosing different services for LLM Service, Database Service, Visualization Service, and Frontend Service.

LLM Service

For implementing the serverless solution, one promising approach is to use Cloudflare AI Gateway for handling AI requests securely and efficiently. This allows us to offload the AI query generation (e.g., natural language to SQL) to a scalable, managed environment, while keeping the data access and visualization logic decoupled and secure.

By using Cloudflare AI Gateway, we can:

• Proxy and monitor AI API requests (e.g., OpenAI, Anthropic, etc.)
• Enforce rate limits and authentication
• Log and audit requests for security and compliance

Database Service

For the chat application, we can use Supabase for its powerful features and ease of use. Supabase provides a comprehensive database solution with real-time data synchronization, authentication, and a flexible data model. Not to mention its RLS (Row Level Security) feature, that can help us to secure the data access.

By using Supabase, we can:

• Store the chat history and the metadata of the chat
• Store the user preferences, settings and profile

Also, we need a vector database to store the data for the chatbot to use. We can use Chroma DB as a self-hosted solution; or Pinecone as a fully managed service.

The vector database will be used to create the vector embeddings of the data for the chatbot to use:

• Store the database schema
• Store the database related information
• Store the conversation history

Visualization Service

For data visualization, we will use Apache Superset as the primary platform. Superset is a modern data exploration and visualization platform that supports a vast range of charts, dashboards, and data source integrations. It can be self-hosted by provided containerized images.

By using Superset, we can:

• Visualize the data in a rich, interactive way
• Create dashboards and reports
• Share the visualizations with the user
• Import Data easily from database or files
• Execute the SQL query

Frontend Service

For the frontend service, we will build a simple web application from scratch using Next.js as the framework, it is a great framework for building modern web applications, I used it a lot in my previous projects.

In the web application, these features will be implemented to make the experience of the application align to real-world application:

• Chat:
  • Conversation: Message Cell, Input, Loading, Error, fallback, etc.
  • History: Search, Managing, Loading, Sharing, etc.
• User:
  • Authentication and Authorization
  • Settings and Preferences
• Organize:
  • Folder or Workspace
  • Label/Tag: Allow users to tag or categorize conversations for flexible organization.

Conclusion

The chatbot sounds pretty simple, but as one of the most popular topics nowadays, there are quite a lot of things to consider. By making choices above, we can quickly establish an MVP in the shortest time, and then we can polish them one by one. By building the project in a Cloud-Native way, we can start fast and polish them afterwards.

In the next few blogs, the mentioned service will be architected, implemented and tested one by one.