How to Build an AI-Assisted ATS with Supabase and OpenAI

LEVEL: Intermediate

In this tutorial, we will be building an AI-assisted Applicant Tracking System (ATS) using Supabase and OpenAI. By the end of this tutorial, you will have a functional ATS that uses AI to manage jobs and candidates for recruitment.

The ATS will be capable of doing the following operations:

• Uploading candidate resumes, which will be scanned and processed using AI to extract relevant information.
• Displaying candidate details, including status updates based on job requirements.
• Integrating with Supabase for data storage and retrieval.
• Using OpenAI APIs to parse resumes and score them.

Feel free to adjust the design, structure and functionality of the application to match your exact needs.

Check out this tutorial to learn how to build a Fleet Management System with ToolJetDB.

• ToolJet Account (https://tooljet.com): ToolJet is an open-source, low-code business application builder. Sign up for a free ToolJet cloud account or run ToolJet on your local machine using Docker.
• OpenAI Account: You will need access to OpenAI’s API to parse resumes.
• Supabase Account: An account and basic knowledge of Supabase for database management.
• Barebones knowledge of APIs and how LLM prompts work.

This tutorial will walk you through how to build an ATS on ToolJet. If you prefer to watch a video tutorial, visit this video.

This is how the complete application will look like:

Start by creating a project in Supabase to serve as your ATS’s backend. Create two tables:

• Jobs: To store job postings.
• Candidates: To store candidate details.

Jobs Table Schema:

• id (Primary Key)
• role (Text)
• experience (Text)
• skills (Text)
• description (Text)

Candidates Table Schema:

• id (Primary Key)
• name (Text)
• phone (Text)
• email (Text)
• current_org (Text)
• experience (Text)
• compatibility (Text)
• status (Text): Shortlisted/Rejected
• comments (Text)

---

Before we can get started with building the ATS, we need to set up Supabse as a Data Source on ToolJet. Head to the Data Sources Tab in ToolJet and search for Supabase. Click on the + Add Button. This opens a Configuration Page for the Supabase Plugin. Let’s name the plugin connection as AI ATS. We need to add a Project URL and Service Role Secret to complete the connection.

1. Enter Project URL:
   • Go to your Supabase dashboard.
   • Click on “Project Settings” in the sidebar.
   • Navigate to the “API” section where you’ll find your Project URL.
   • Copy this URL and paste it into the Project URL field.
2. Get Service Role Secret:
   • In the same API section of your Project Settings, locate the Service Role Secret under Project API Keys.
   • Click “Reveal” next to the key and Copy the secret key.
   • Paste this key into the Service Role Secret field.

Test the connection first and then Save. Now your Supabse Project is connected to ToolJet as a Data Source.

Now that we have our database schema set up in Supabase and connected with ToolJet, let’s move on to building the user interface in ToolJet.

1. Log into your ToolJet account and create a new application. You get a blank canvas when you initially launch a new application. 
2. In the app builder interface, click on Pages and create three pages named:
   • Job Board
   • Add Candidate
   • Candidates

These will serve as the UI for the application. 

• Drag a Container Component onto your Job Board page.
• Set its background color (e.g., blue) and add a Text component labeled “Applicant Tracking System”.
• Add Three Button Components and place them in the Header. These will help the user navigate the application.

1. A “Job Board” Button Component with an On Click Event Handler and Action as Switch Page. Setthe Page asJob Boardunder Action Options.
2. A  “Add Candidate” Button Component with an On Click Event Handler and Action as Switch Page. Setthe Page asAdd Candidate under Action Options..
3. A  “Candidates” Button Component with an On Click Event Handler and Action as Switch Page. Setthe Page as Job Board under Action Options.

Copy this Header on other Pages as well.

• Add a Header for the Job Board Page labeled Job Board.
• Drag and Drop a Table Component.
• Add a Modal Component for adding new jobs and Inside the modal, add Text Input Components for:
  • Job Title
  • Experience
  • Skills

• Add a Textarea Component for the Job Description
• Add a Button Component that triggers an event handler to save new jobs into Supabase when clicked. Label it as Submit.

You can edit these to fit your specific requirements.

Expand the Query Panel at the bottom of the screen. Click on the + Add button to create a new query. You can learn more about queries.

• Rename this query to getJobs.
• Choose AI ATS as the Data Source.
• For the Operation, choose Get Rows.
• Select the jobs table.

Connect the getJobs Query:

In the Table Properties, set the Data field to {{queries.getJobs.data}}. This will connect the data fetched by getFleets to the table. To indicate that data is being fetched, we will add a Loader. Under Additional Actions, add the getJobs query as {{queries.getJobs.isLoading}} to the active Loading state. This will display a loader while data is being retrieved.

• Rename this query to addJob.
• Choose AI ATS as the Data Source.
• For the Operation, choose Create Rows.
• Select the jobs table.
• In the Body, add the data in the following structure:

[
  {
    “role: “”,
    "experience”: “” ,
    "skills": “”,
    "job_description": ""
  }
]

Use the {{components.<component_name>.value}} syntax in the values to link each column of the Table to its respective Input Component as shown below.

Connect the addJob Query to Submit:

Event Handlers let us manage triggers and responses in ToolJet straightforwardly. For this Modal, we’ll set upthree event handlers:

1. Show a popup when a job is successfully added.
2. Close the Modal after the job is added.
3. Refetch the data by running the getJobs query again.

Now we will build the Add Candidate Page. This includes a File Picker Section and a section that shows the data extracted from the Resume uploaded using OpenAI API. Head over to the Add Candidate Page

For Upload Section:

• Add a Container Component where users can upload resumes.
• Add a Divider Component.
• Add a Text Component labeled Upload Resume.
• Add a Dropdown Component and label it Role. This will let us select the role we want to add a Candidate for.
• Now add a File Picker Component and a Button Component labeled Scan.

Components for the section that shows the data extracted:

• Add another Container Component in which we will add other components
• Add the following Text Input Components:
  • Name
  • Phone Number
  • Email
  • Current Org.
  • Experience
  • Compatibility (%)
  • AI Suggestions (as Textarea)
• Add a Button Component with the label Add.

For the role to show the current jobs, we need to update it dynamically. To do that select the Role Dropdown Component.  Under Options, turn on Dynamic Options. Now under Schema, add the following structure: 

{{queries.getJobs.data.map(option => ({

  label: option.role,

  value: option.id,

  disable: false,

  visible: true

}))}}

Expand the Query Panel at the bottom of the screen. Click on the + Add button to create a new query. 

• Create a REST API Query.
• Rename this query to extractData.
• For the Request, choose POST as Method.
• Add the OpenAI API URL.
• Add the following header Key-Value Pairs:
  • Content-Type: application/json
  • Authorization: Bearer YOUR_OPENAI_API_KEY (replace YOUR_OPENAI_API_KEY with your actual OpenAI API key)
• Now in the Body Param add the following Prompt

{

    "model": "gpt-4o-mini",

    "messages": [

      {

        "role": "user",

        "content": [

          {

            "type": "text",

            "text": "In this image of a resume of a developer, analyze the receipt image and return the following information in JSON format without any formatting or syntax highlighting: Current org, Total experience in years (you have to calculate this based on the experience section, it must be mentioned like when the candidate stared working in different roles, add the experience and then return the total year you calculated keep the key as total_experience_years. Considering we are in 2024 calculate based on that.), skill of programming language (give me just list like react,python etc. make sure you just give me skills in the resume. just in resume. no other skill you should add from your end. make sure it is one string with commas separated skills), name , phone number and email . You also have to give the compatibility score in percentage (for example 35%, here you should calculate based on the skills in the resume and skills in the description given below also check job description to check if the candidate is good fit. do not give any false value. be authentic as this is very important use - compatibility_score as the key in the json) and AI Suggestion (Please provide a real suggestion, no unnecessary explanations. Understand the skills the company needs and what the candidate possess, as well as the experience the company requires and what the resume shows.Tell us the strength and weakness and will it be a good fit or not. No need to defend the candidate. If is is really a good fit as per the experience asked and the candidate has. Be real and brutal) based on the information i give you for the job information i provide you. Here is the job role -  {{queries.getJobs.data.find(i=>i.id ===components.dropdown1.value).role}}. Here is the job description - {{queries.getJobs.data.find(i=>i.id ===components.dropdown1.value).job_description}} and here are the skills {{queries.getJobs.data.find(i=>i.id ===components.dropdown1.value).skills}} and here is the experience required {{queries.getJobs.data.find(i=>i.id ===components.dropdown1.value).experience}}. Make sure all are strings. i do not want any array also in skills, you just give me one sting only with all the skills in it. not make this false now. Return one JSON object, and all the names of keys should be in snake_case. Provide only the proper JSON object, nothing else. Avoid any unnecessary comments or extra text. Do not use apostrophes anywhere in the response, including contractions such as it's, he's, there's, who's, don't, doesn't, or any similar forms. "

          },

          {

            "type": "image_url",

            "image_url": {

              "url":  "data:image/jpeg;base64, {{components.filepicker1.file[0].base64Data}}"

            }

          }

        ]

      }

    ],

    "max_tokens": 300

  }

Feel free to edit the prompt to suit your specific use cases.

Now we can connect the response from OpenAI API to the Input components in the Add Candidate Page. To do that, select each input component and change the Default value to the corresponding part of the response. Here’s a table to see the correct Vlaue to put in each field-

Component Name	Default Value
Name	{{JSON.parse(queries.extractData.data.choices[0].message.content).name}}
Phone	{{JSON.parse(queries.extractData.data.choices[0].message.content).phone_number}}
Email	{{JSON.parse(queries.extractData.data.choices[0].message.content).email}}
CurrentOrg	{{JSON.parse(queries.extractData.data.choices[0].message.content).current_org}}
Experience	{{JSON.parse(queries.extractData.data.choices[0].message.content).total_experience_years}}
Compatibility	{{JSON.parse(queries.extractData.data.choices[0].message.content).compatibility_score}}
AI Suggestion	{{JSON.parse(queries.extractData.data.choices[0].message.content).ai_suggestion}}

Once we are done, we need to connect the Scan Button to the extractData Query. Add an On Click Event Handler for the Scan Button that runs the extractData Query. Under Additional Actions in the Scan Button, add the extractData query as {{queries.extractData.isLoading}} to the active Loading state. This will display a loader while data is being retrieved.

• Rename this query to addCandidate.
• Choose AI ATS as the Data Source.
• For the Operation, choose Create Rows.
• Select the jobs table.
• In the Body, add the data in the following structure:

[ {
"name": "",

 "phone": "",

 "email": "",

"current_org": "",

"experience": "",

 "compatibility": "",

"ai_suggestion": "",

 "status": ""{{parseFloat(components.textinput6.value.replace('%', '')) >= 60 ? 'Shortlisted' : 'Rejected'}}" }" 
} ]

Use the {{components.<component_name>.value}} syntax in the values to link each column of the Table to its respective Input Component as shown below. The status field is dynamically determined based on the candidate’s compatibility percentage. If the compatibility is 60% or higher, the candidate is marked as ‘Shortlisted’; otherwise, they are labeled as ‘Rejected.’

• Rename this query to getCandidate.
• Choose AI ATS as the Data Source.
• For the Operation, choose Get Rows.
• Select the candidate table.
• Enable Run this query on application load in Settings to ensure the query runs whenever the application loads.

Once we are done, we need to connect the Add Button to the addCandidate Query. Add an On Click Event Handler for the Add Button that runs the addCandidate Query. Under Additional Actions in the Add Button, add the addCandidate query as {{queries.addcandidate.isLoading}} to the active Loading state. This will display a loader while data is being retrieved.

We also need to set up two event handlers for the addCandiate query to fetch current data whenever we add a new candidate. Head to Settings in the query and add these:

1. A Query Success Event with Action as Show Alert with message “Candidate Added successfully” Alert Type as Success.
2. A Query Success Event with Action as Run Query and it runs getCandidate Query

Now we will build the Candidates Page. We’ll add a Text Component as a Header, and add a Table Component. We don’t need the id column here so remove that and put the status column at the top so that it’s easier to know which candidates are shortlisted. We can also set the colors for Rejected candidates to Red to visually indicate the status. To do that:

• In Table Properties, under Columns, locate the status column and click on it.
• Go to Styles Tab, and add a condition for Text Color  – {{cellValue == “Rejected” ? “#d0021bff” : “#027148”}}

Now if the cell value is “Rejected”, the Text Colour will be Red.

Let’s add an Update Button to the Candidates list so that we can update details manually as needed.

• In Table Properties, under Action Buttons, click on New action button.
• Set the Button Text to “Update”
• Change background colors and Text Colors as per your requirement.

Let’s create a Modal Component that opens when we click on the Update.

• Drag and Drop a Modal Component.
• Give it the title “Update Candidate Details”.
• Disable the “Use default trigger button” because we’ll use the Update Button to open Modal.
• Click on the Update Button in the Table
• Click on New Event Handler.
• Choose Open Modal as Action. Select the correct Modal and we can launch it using the Update Button.
• Let’s set the Modal size as small and height as 700px.

The Components to add are the same as from the Add Components Page. Copy the following Input Components for the Modal:

• Name (Text Input)
• Phone (Text Input)
• Email (Text Input)
• Current Org. (Text Input)
• Experience (Text Input)
• Status (Dropdown)
• Compatibility (Text Input)
• AI Suggestions (as Textarea)
• Comments (as Textarea)

Finally, add a Button Component with the label Update. To allow updating existing candidate details, you need to link the input components in the Modal to the selected row in the Table. This ensures that when you click Update on a candidate’s row, their details are pre-filled in the modal for editing. For example – Select the Name Input Component, and set the Default Value to – {{components.table1.selectedRow.name}}. Repeat this for all other Text Input and Textarea Components.

The Status Dropdown Component will again have dynamic values. Select the Dropdown Component. Under Options, turn on Dynamic Options. Now under Schema, add the following structure: 

{{["Rejected", "Shortlisted", "L1 Interview", "L2 Interview", "HR Interview", "Hired"].map(

  option => ({

    label: option,

    value: option,

    disable: false,

    visible: true,

    default: option === components.table1.selectedRow.status

  }))

}}

In the end, the Modal will look like this:

This query runs when the Update Button is clicked. It will make the required changes in the Table.

• Rename this query to updateCandidate.
• Choose AI ATS as the Data Source.
• For the Operation, choose Update Rows.
• Select the candidate table.
• For the Where, add a Condition where:
  • Column Name –  id
  • Select Operation – Equals
  • Enter Value – {{components.table1.selectedRow.id}}
• Map Input Components to Columns

All columns in the candidate table need to be linked to the corresponding input field in the Update Candidate Details Modal. Use the {{components.<component_name>.value}} syntax in the Key input field of Column to link each column to its respective input component.

For example:

Column Name: email

Input Component Name: textinput3

Key Value for “company” column: {{components.textinput3.value}}

For this Modal, we’ll set up three event handlers as well:

• A Query Success Event with Action as Show Alert with the message “Candidate updated successfully” Alert Type as Success.
• A Query Success Event with Action as Close Modal.
• A Success Event with Action as Run Query and it runs getCandidate Query.

Connect the Update Button by adding an Event Handler that runs the updateCandidate Query. Add a Loading State as well.

This concludes this tutorial on building an AI-assisted ATS with Supabase and OpenAI with ToolJet. Kudos for following along so well in this long tutorial. Have a Cookie 🍪. If you have any doubts or feedback, feel free to share your thoughts in our Slack Community.