Building an intelligent CRM using ChatGPT, PostgreSQL, and ToolJet

In this tutorial, you'll learn how to build a CRM (Customer Relationship Management) application using ChatGPT, ToolJet, and PostgreSQL.

Introduction

The CRM application will allow you to add new users or customers to a PostgreSQL database, view their details on a dashboard, generate email templates using ChatGPT, and send them emails via Sendinblue.

We'll use ToolJet for the application interface in this tutorial.

What is ToolJet?

ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes. With ToolJet, you can create standalone fully-functional full-stack applications or embed applications into other websites.

ToolJet allows you to build applications that use relational and non-relational databases, REST APIs, OpenAI technologies, and cloud storage like Google Cloud Storage, AWS S3, and Minio. It is an excellent development tool helping individuals, developers, and businesses create and ship products faster.

Before we continue, I need your help? 😔
I would be super happy if you could give us a star! And let me also know in the comments section. ❤️

https://github.com/ToolJet/ToolJet

Setting up an ElephantSQL (PostgreSQL) database

ElephantSQL enables us to create a PostgreSQL database on the cloud instead of your local machine. Follow the steps below to create a PostgreSQL database:

Create an ElephantSQL account here.

Add a new database instance. No credit card or billing information is required.

Once you've created the database instance, your database information is displayed.

Congratulations! You've successfully created the database needed for this application. Next, let's build the application interface and connect the database to the application.

Building the application with ToolJet

Here, you'll learn how to create a ToolJet account and build a fully functional application with ToolJet.

If you are new to ToolJet, create an account.

Create a Workspace and a new app called CRM application.

Build a user interface similar to the image below.

To create the UI image above, you will first add a large container on the canvas that will house all the other UI elements of the application. Next, add the three container elements at the top of the page, the Add Lead button component and the Table component.

ToolJet enables you to create applications' interfaces by dragging and dropping various UI components on the canvas.

After recreating the UI, add an Action button with the text "Send Email" to the table. (We'll configure it later to enable us to email each user).

To complete the UI, we need an interface that allows us to add a user to the database and another for editing and generating the email content for each user.

To do this, we'll use the Modal component. We'll display a modal when we click on the Add Lead button - for adding new data and the "Send Email" action button - for entering the email content for the user.

The “Add New Lead” Modal

To display the Add New Lead Modal component, you need to create the modal and add an onClick event to the "Add Lead" button to show the modal.

Add the following components to the modal, and remember to close the modal when you click the modal button.

The “Send Email” modal

The Send Email modal allows us to enter the user's email, email subject, and content. You can also generate email content using the ChatGPT API. You'll learn how to do that in the upcoming sessions.

The toggle switch has an onChange event that hides the ChatGPT prompt input field and button whenever you don't need it.

Remember to close the modal when a user clicks the Send Email button.

Congratulations!🎉 You've completed building the UI for this application. In the upcoming sessions, you'll learn how to connect the user interface to a PostgreSQL database, communicate with ChatGPT, and send emails using Sendinblue.

How to communicate with a PostgreSQL database in ToolJet

ToolJet allows us to communicate with external resources or create custom functions via a panel known as Query Panel. In ToolJet, any function that interacts with a database, API, or cloud storage and runs a JavaScript or Python code is called a Query.

Here, you'll learn how to communicate with a PostgreSQL database in ToolJet. First, let me walk you through connecting a PostgreSQL database to ToolJet.

Connecting a PostgreSQL database to ToolJet

Select PostgreSQL from the list of databases under the Global Datasources panel, and provide the required information as shown below.

From the image above, the host is the same as the server name on ElephantSQL (excluding the brackets). The username and database name are the same, and copy and paste the password into its field.

Scroll down the page and click Test Connection.

If the connection is verified, we can start making queries to the database.

Querying a PostgreSQL database in ToolJet

Here, you'll learn how to query data from the database and display them accordingly within the application.

Before we begin, let's add some data to the database. Click PostgreSQL on the Query Panel, select SQL mode, and run the code snippet below to create a new table containing the data below.

CREATE TABLE Users (Name varchar(255), Email varchar(255), Phone varchar(255), Organisation varchar(255), Designation varchar(255), Status varchar(255));
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Teja', 'teja@gmail.com', '+555-34569', 'ToolJet', 'Developer Advocate', 'Customer');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Badri', 'badri@gmail.com', '+555-59659', 'ToolJet', 'Engineering Manager', 'Prospect');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Jack', 'jack@gmail.com', '+555-44449', 'ToolJet', 'Software Engineer', 'Lead');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Nora', 'nora@gmail.com', '+555-46249', 'ToolJet', 'Product Manager', 'Customer');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Emily', 'emily@gmail.com', '+555-47893', 'ToolJet', 'Product Designer', 'Prospect');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Dave', 'dave@gmail.com', '+555-49313', 'ToolJet', 'Data analyst', 'Lead');
SELECT * FROM Users;

You can view the data on ElephantSQL once it has been uploaded.

Next, rename the query to getTableData, update the SQL editor to display all the table contents, and save the query.

Update the Table component to display the data retrieved from the getTableData query.

Since we've displayed the contents on the table. Next, let's show the number of Customers, Leads, and Prospects available on the database.

Create three new queries based on the PostgreSQL database called getLeadsCount, getProspectsCount, and getCustomersCount.

Update the SQL editor for each query to retrieve the number of data under each category as done below, and save them.

SELECT COUNT(*)
FROM Users
WHERE Status IN ('Lead');
/*-- change 'Lead' for leads, 'Prospect' for prospects, 'Customer' for customers. ---*/

Display the number of data returned from each query by replacing the text content within the container as done below.

{{queries.getProspectsCount.data[0].count}}

//(Use {{queries.getLeadsCount.data[0].count}} for leads and {{queries.getCustomersCount.data[0].count}} for customers)

Finally, switch on the "Run this query on application load?" toggle for the getTableData and the other three queries.

Congratulations! You've learnt how to query and display the data received within the application. Next, I will walk you through adding new data to the database via the application interface.

Adding new users to the database

Here, you'll learn how to accept that data from the modal and add the user's data to the database.

Create a new query related to the PostgreSQL data source that accepts the values from the modal and sends an SQL query to add the data to the database.

Ensure that the code snippet below matches the components' names from the modal. Copy the code snippet below into the SQL editor panel and save the query.

INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('{{components.name.value}}', '{{components.email.value}}', '{{components.phone.value}}', '{{components.organisation.value}}', '{{components.designation.value}}', '{{components.status.value}}');

Finally, add an onClick event to the Add Lead button to run the query on button click.

How to communicate with ChatGPT in ToolJet

Here, you'll learn how to communicate with ChatGPT via OpenAI in ToolJet.

Setting up an OpenAI account

Log in or create an OpenAI account here.

Click Personal on the navigation bar and select View API keys from the menu bar to create a new secret key.

Generate a new API key and copy it somewhere on your computer. We'll use it in the upcoming section.

Communicating with the OpenAI API in ToolJet

Under the Global Datasources tab on your dashboard, click Plugins, and select OpenAI. Then, paste your API key and organization ID into the input fields and test the connection.

You can now access the OpenAI data source from the query panel.

If you don’t have an organization ID, use “Personal”.

Select OpenAI from the Query Panel and set its content to draft an email using ChatGPT.

Draft an email to {{components.recipient.value}} about {{components.chatgptprompt.value}}

Run the query when a user clicks the Ask button on the modal.

Finally, to display a selected user's email automatically on the modal, set the default value of the recipient's email input to {{components.table1.selectedRow.email}}. This automatically sets its value to the email of the selected user.

How to send emails via the Sendinblue API in ToolJet

Sendinblue is a digital marketing tool that provides Email, SMS, Facebook, Chat, and more, via one platform to help grow businesses by building stronger customer relationships.

In this section, you'll learn how to integrate and send emails via Sendinblue in ToolJet. First, you need to log in or create a SendinBlue account.

Select SMTP and API on your dashboard, generate an SMTP key, and copy it somewhere on your computer. You'll need it shortly.

Return to your ToolJet app, add a new SMTP data source, and fill in the required credentials. Your password is the generated SMTP key.

If successfully connected, it will display "Connection Verified". Then you can start sending emails.

Next, create the query for sending the emails. Provide your email, name, the recipient's email, subject, and title components.

Finally, save the query and run it when the Send Email button on the modal is clicked.

Here is a working demo of the application: https://tooljet-stny.onrender.com/applications/7a8867e3-3cea-49e3-89e7-8da39038b875

You can also download its JSON file and import it into a ToolJet app, but you'll need to provide your Sendinblue credentials and OpenAI API key.

Conclusion

So far, you've learnt how to

• add a PostgreSQL database to ToolJet
• send emails within a ToolJet application using Sendinblue,
• communicate with ChatGPT via OpenAI in ToolJet, and
• Build full-stack applications in a few minutes with ToolJet.

ToolJet is both an excellent development tool and open-source - meaning its code is readily available for everyone to modify and improve. It has a large community of developers and talented contributors constantly maintaining and improving the software. As a user, you can be sure of getting the best performance when you use ToolJet.

Are you interested in contributing to ToolJet? Feel free to check out our GitHub repo- https://github.com/ToolJet/ToolJet to contribute and raise issues about ToolJet.

Thank you for reading!