Trying to build a chatbot with database data

💬 App Building
1

Hi All,

I am trying to build a chatbot, it goes like this:

  1. Added an LLM chat component with following prompt:

LLM event handler is setLatestMessage.trigger()

"You are a helpful assistant helping recruiters find candidates from a hiring database.

Ask questions to gather filters such as:

  • Primary skill
  • Years of experience
  • Preferred location

Once you have this information, do not reply with SQL or JSON. Simply confirm:

"Thanks! Let me find candidates for you..."

Keep the conversation natural and user-friendly."

  1. Created a Retool DB from csv file

  2. Added a JS file
    const msg = (latestMessageText.value || '').toLowerCase().trim();

const currentFilters = tempState.getValue("filters") || { skill: '', experience: 0, location: '' };

let skill = currentFilters.skill;
let experience = currentFilters.experience;
let location = currentFilters.location;

// Skill detection
const skillsList = ['appian']; // add more skills as needed
for (const sk of skillsList) {
const skillRegex = new RegExp(\\b${sk}\\b, 'i');
if (skillRegex.test(msg)) {
skill = sk.charAt(0).toUpperCase() + sk.slice(1);
break;
}
}

// Experience detection
const expMatch = msg.match(/(\d+)\s*(years|yrs)\b/i);
if (expMatch) {
experience = parseInt(expMatch[1], 10);
}

// Location detection
const locMatch = msg.match(/(?:in|at|from)\s+([a-zA-Z\s]+?)(?:,|$|\sand|with|located|experience|years|yrs)/);
if (locMatch) {
location = locMatch[1].trim();
}

// Handle 'any' as no filter
if (skill.toLowerCase() === 'any') skill = '';
if (location.toLowerCase() === 'any') location = '';
if (msg.includes('any')) experience = 0; // if experience explicitly said as 'any'

// Or more robustly, check if the message contains 'any' in a relevant context,
// e.g. user says "any skill", "any experience", "any location".

// For example, if the whole message is "any" or "any any any", then reset all filters:
if (/^\sany(\s+any)\s*$/.test(msg)) {
skill = '';
experience = 0;
location = '';
}

tempState.setValue("filters", { skill, experience, location });

console.log("latestMessage:", msg);
console.log("Parsed filters →", { skill, experience, location });

getCandidatesQuery.trigger();

  1. Added a SQL query

SELECT *
FROM candidates
WHERE
({{ tempState.filters.skill || "''" }} = '' OR LOWER(primary_skills) LIKE LOWER({{ "'%" + tempState.filters.skill + "%'" }}))
AND ({{ tempState.filters.experience || 0 }} = 0 OR relevant_yrs >= {{ tempState.filters.experience }})
AND ({{ tempState.filters.location || "''" }} = '' OR LOWER(current_location) LIKE LOWER({{ "'%" + tempState.filters.location + "%'" }}))
LIMIT 10;

  1. Added setLatestMessage
    const messages = llmChat1.messageHistory;
    if (messages.length > 0) {
    const lastMessage = messages[messages.length - 1];
    if (lastMessage && lastMessage.role === "user") {
    latestMessageText.setValue(lastMessage.content);
    processCandidateSearch.trigger();
    }
    }
  2. latestMessagesText = ""
  3. filters = {}

Now when i type in a query in bot, its taking all inputs from user but its not able to frame the filter properly, hence not getting the output from SQL query

Data sources documentation

3

Hi @Pranjal_Vatsa,

Apologies for the delay here! Is this use case using AI Actions? It could be worth trying out this use case with our new Agents product: Retool Agents documentation | Retool Docs

Otherwise, seeing the app json export might help with troubleshooting