How to: Open Ai Assistants read your Database and more

design.agx · 2024-09-10T12:07:27.201Z

Hello, I have seen some people asking how to let an AI assistant read the content of their Bubble database and let their users ‘chat’ with their data.

I will show you how to let an AI assistant query different data types from your database and perform more actions using a chat interface.

Recording2024-09-10135859-ezgif.com-video-to-gif-converter

Sorry for the gif quality

You can test the chat in this link:

Bubble

Bubble - Visual Programming

Build stuff without code and launch a startup without a tech-cofounder! Bubble is a visual programing language. Instead of typing code, use a visual editor to build applications.

Overview

We will create 2 backend workflows in our Bubleapp which will be triggered by the Ai assistant. We will build the assistant using the Open Ai Assistant node from Buildship and learn how to integrate it in our app.

Overview workflow2093×1508 193 KB

Database Overview

We will use 2 tables for this example to keep things simple. One table is Contacts and the other one Company

Database Overview2972×1997 329 KB

Setting up Backend Workflows

The first backend workflow is for creating a Contact. It’s a POST API call that accepts some information to create a contact in our database.

For this test, the checkbox to run the workflow without authentication and ignore privacy rules was selected. However, for a live application, privacy rules should not be ignored, nor should it run without authentication.

BWF-Overview1878×1344 130 KB

This workflow includes two actions: one to create the contact and another to return data to the client.

BWF-Actions11668×1344 243 KB

I want to be able to create a contact and link it to a company, for that I have set up a Search for Companies and added a filter for the name of the company.
These values will be filled by our Ai assistant

The Return data action is for letting know the AI that the request was successful and we pass the data of the new contact.

The second backend workflow is for searching through the Contacts and Company datatype. It’s also a POST API Call and it accepts the name of the ‘table’ we want to search through.

BWF-Overview21002×1596 164 KB

I use the ‘table’ parameter to create conditions for the actions of this workflow.

BWF-Actions21628×1344 254 KB

We have 2 'Return data from API actions, each of them does a search but in different datatypes depending on the condition. Our AI assistant will decide what ‘table’ to search for and fill in the value when making the POST API call to this workflow.

Setting up the AI Assistant

For setting up our Assistant I will use Buildship, it’s a low-code backend that will handle all the API calls necessary to interact with Open AI Assistants API.

You don’t need to use Buildship for this; it can also be achieved with Bubble by setting up all the API calls necessary. I use it because it handles also the ‘function callings’ automatically and that would take me more time to do in Bubble.

If you want to give it a quick try, you can clone the workflow that I made for this guide:

buildship.app

BuildShip - Visual backend workflow builder

Visually build workflows and powerful backend logic for your apps. Powered by AI, connect to anything with or without code.

This is the overview of our workflow in Buildship. This workflow (series of actions) is triggered by a POST API call to the endpoint I called ‘/assistants’.

Buildship-overview1280×1856 168 KB

You must add your open AI API key for this workflow & your Assistant ID to work. You can do it by clicking the input field.

You can get your API key here: https://platform.openai.com/api-keys
You can get your Assistant ID here: https://platform.openai.com/assistants

You can add your API key as a secret and the assistant ID as an environmental variable

image-content-21700×752 113 KB

The user prompt is assigned to the variable ‘message’ from the body of the endpoint, while the thread ID is set to the variable ‘threadId,’ also from the body of the endpoint.

These are the instructions I added to my assistant. Notice I tell it to use the tools and how:

Instructions
You can retrieve information by using the tool 'search database'. Its a POST API call, where you can pass the name of the table in the body: {"table": "string"} You can query the tables: - contacts - company You can pass the name of the table to get the information of that table You can also create a contact by using the tool 'create contact'. This is the body structure to create a new contact {"title": 'string', "name": 'string', "company": 'string' }

The OpenAI Assistants node comes with another part where you can start adding more nodes (actions). In our case, we want to add 2 API Calls that send a request to Bubble Backend workflows.

One API call is called 'search database’ and the other ‘create contact’.

Assistants Node1308×1344 130 KB

For search database, I made it a POST request, filled the URL and content_type.
The body is being filled by the AI, the blue spark icon indicates that the input is AI filled

ai-action-11920×875 83.3 KB

When clicking on the sparks icon, a popup appears to customize the instructions of that node. In my example, I wrote the options the AI can choose from: contacts & company.

Intructions:
The body to send with the API call. names of the table to querry are 'contacts' and 'company'

This is what triggers the right actions in our ‘search’ backend workflow in Bubble

For create contact , is the same logic but the instructions of the body are different. I specify the JSON format that needs to be passed.

ai-action-21920×855 82 KB

Instructions:
The body to send with the API call. {"title": 'string', "name": 'string', "company": 'string' }

The ‘return’ node is the last we have. You can change the response to add more values if you need. In our case we are returning back the response of the Ai assistant and the Thread ID

return-aimessage3176×1344 246 KB

You can deploy your workflow by clicking the Blue Ship button, which will generate your Endpoint URL and a Chat Widget.

ship-workflow1920×704 73.5 KB

Integrating the Ai assistant

There are two ways to integrate this assistant: one is by setting up an API call to the Buildship endpoint from your Bubble app, and the other is by using the Chat Widget and embedding it in your Bubble app.

API Call to Buildship Endpoint
You can set it up as an POST API call, used as action and fill the body parameters. You can now use it across your app as you need!

api call to buildship1538×1312 122 KB

Chat Widget
When clicking the Chat widget button, you can customize it, test it and get the code for embedding the chat widget inside your app.

chat widget2182×1312 142 KB

Copy the Code Snippet into an HTML Element in Bubble. You will get a simple button that you can customize to the appearance you need. On preview mode, clicking the button will open the chat widget.

bubble chat widget integration3522×1312 509 KB

I hope you found this guide useful! And if you need any further assistance, I am available for freelance work or one hour consulting for both Bubble development and Buildship

You can check my portfolio here

contra.com

Sebastian Avelar → on Contra

View Sebastian Avelar →'s Profile on Contra, the Independent-first, commission-free freelance marketplace shaping the future of work. Hire a freelancer today!

georgecollier · 2024-09-10T12:24:06.007Z

Great guide!

design.agx · 2024-09-10T13:19:48.060Z

Thanks, George!
I have been exploring Buildship more and it’s a great add-on to Bubble!

nico.dicagno · 2024-09-10T18:28:38.211Z

Great guide and great write-up! Thank you for this and for sharing it with the community.

I know this guide is centered around connecting an Assistant and not around endpoint security, but i feel its still important to put a note that the endpoints need to be secured somehow. Creating a public endpoint that can run without authentication and also ignores privacy rules is a big vulnerability, especially if they create/return data.

You don’t need to talk about how to actually secure the endpoint - just leave a short disclaimer that something needs to be done but its outside the scope of the guide

design.agx · 2024-09-11T08:11:37.926Z

Hey @nico.dicagno , true!
Thanks for noticing it.
I added a disclaimer now

bwm561 · 2024-10-10T22:42:22.414Z

Hi, following up on this thread. I am hoping to set this up. Two questions:

The previous posts mention endpoint security for this. How is that achieved? I know how to do security and privacy rules in Bubble, but are extra steps needed when using Buildship?
Is there a guide for web scraping through Buildship? When users sign up, I want them to be able to pull existing data from their own website(s) or from LinkedIn. Preferably, with real time updates when their website(s) are updated.

Thanks!
Bryan

redvivi · 2024-10-11T05:04:16.157Z

bwm561:

Is there a guide for web scraping through Buildship? When users sign up, I want them to be able to pull existing data from their own website(s) or from LinkedIn. Preferably, with real time updates when their website(s) are updated.

I don’t see how a LLM could be the best solution for this? There are online specialized services returning this data.

simnikiwe · 2024-11-27T20:06:56.892Z

Hi,

Thank you for the topic. I am not certain any of the above solutions will work for me.

I currently have an application like thumbtack(mine niched to my location). The database I assume is not entirely different from mine. I want to include AI to the search part of the webapp, that reads the database and provide the user with results.

Example query: " I am looking for a house helper/maid, at Location X, at price X.
Then the output to be a list of the profiles close to the search.

Please help

design.agx · 2024-11-28T16:06:48.012Z

Hey @simnikiwe !
I’m working on a plugin for adding this feature very easily. You can try it next week I finish the first version!

Normally you would have the ai assistant return a list of ID’s and then doing a search using those ID’s in Bubble

salemmo409 · 2024-11-28T17:06:35.924Z

design.agx:

that would take me more time to do in Bubble

I agree, it took me a lot of time and experimentation to get it to work especially because I was learning, but I love building such complex things in Bubble, and once you have it you can duplicate it when needed.

salemmo409 · 2024-11-28T17:07:37.556Z

simnikiwe:

I am looking for a house helper/maid, at Location X, at price X.

I think you only need to add two parameters to the “search” backend workflow, one for the location and the other for the price, then you need to use them to add constraints to the “do a search for…”, if the search will be in one datatype “taple” you don’t need for the “table” parameter

redvivi · 2024-11-30T16:03:25.342Z

Of course. The world didn’t wait for AI to solve this use-case. Such a waste of computing power for a search.

One thing where AI might help here is semantic understanding of the query, meaning that from a search query written in natural language, AI may feedback through a tool/function normalised Do a search for... parameters in @salemmo409 's backend workflow.