Describe the problem/error/question
We are building a finance buddy chatbot using n8n (version 1.114.3, self-hosted) that responds to user questions by dynamically generating and executing MySQL queries against a financial summary view. The chatbot workflow triggers on chat messages, generates SQL via an AI agent, executes it with another AI agent, and processes results for analysis.
What is the error message (if any)?
Example Queries and Behavior:
-
“Who is our customer?” → Returns correct distinct customer list.
-
“Who is our salesman and their total sales?” → Returns correct aggregated sales by salesperson.
-
“What is the sales by month?” → Unexpectedly, the response indicates it can only retrieve distinct salesmen, not grouped monthly sales.
Please share your workflow
Workflow Design and System Messages:
-
Generate_Tool AI agent converts user queries into clean, executable MySQL queries strictly querying the
n8n_invoice_summaryview, following detailed SQL generation rules (e.g., aggregation, filtering, grouping). -
Execute_Tool AI agent receives generated SQL, cleans it (removes code fences and newlines), executes the query against MySQL, and outputs JSON results.
-
After Execute_Tool, we use Code nodes to prepare queries for further SQL execution and combine results for analysis.
Issue / Question:
While our approach works well for simple listing and aggregation queries, complex grouping queries sometimes return incomplete or unexpected results. Also, the Execute_Tool’s query execution sometimes appears not to fully utilize the MySQL executor tool, requiring additional code nodes to clean and re-run queries.
Given this, what are the recommended best practices or architectural patterns in n8n for building an AI-driven conversational finance analytics bot that:
-
Reliably generates accurate and robust SQL for various analytical queries?
-
Executes queries smoothly without redundant code nodes?
-
Handles grouping and aggregations dynamically based on user intent?
Any guidance, workflow examples, or alternative tooling suggestions are highly appreciated.
{
"nodes": [
{
"parameters": {
"options": {}
},
"type": "@n8n/n8n-nodes-langchain.chatTrigger",
"typeVersion": 1.3,
"position": [
-896,
192
],
"id": "5a9a0081-8387-4e54-9358-cc2890facc64",
"name": "When chat message received",
"webhookId": "cc6aca39-fcf4-45b8-8bb3-b0e8a98fea9d"
},
{
"parameters": {
"options": {
"systemMessage": "# Overview\nYou are an orchestrator agent. Your only job is to delegate the task to the Generate_Tool. Then pass the output (SQL query) of Generate_Tool to Execute_Tool for the execution process."
}
},
"type": "@n8n/n8n-nodes-langchain.agent",
"typeVersion": 2.2,
"position": [
-560,
192
],
"id": "6e7425f1-a31b-4dcb-a42a-820405aa137c",
"name": "AI Agent"
},
{
"parameters": {},
"type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
"typeVersion": 1.3,
"position": [
-544,
416
],
"id": "48800407-df0b-4bb0-8dac-b71253be1993",
"name": "Simple Memory"
},
{
"parameters": {
"description": "Use this tool to generate the SQL query, no limit unless 'Top' or 'First' or similar keywords mentioned.",
"workflowId": {
"__rl": true,
"value": "9G8KEgROZUJyK5c5",
"mode": "list",
"cachedResultUrl": "/workflow/9G8KEgROZUJyK5c5",
"cachedResultName": "FIN Test 20251111 - generate"
},
"workflowInputs": {
"mappingMode": "defineBelow",
"value": {},
"matchingColumns": [],
"schema": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
}
},
"type": "@n8n/n8n-nodes-langchain.toolWorkflow",
"typeVersion": 2.2,
"position": [
-416,
416
],
"id": "00d64508-64f2-478d-b7c5-5674a00dc55c",
"name": "Generate_Tool"
},
{
"parameters": {
"options": {}
},
"type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
"typeVersion": 1,
"position": [
-672,
416
],
"id": "0e68d008-e981-445a-8d03-007eda7c9ff8",
"name": "Google Gemini Chat Model",
"credentials": {
"googlePalmApi": {
"id": "DHKkpMZIWrbBRaVY",
"name": "Google Gemini(PaLM) Api account 2"
}
}
},
{
"parameters": {
"description": "=Use this tool to execute the SQL query from the output of Generate_Tool.",
"workflowId": {
"__rl": true,
"value": "ZcFjrOgNK38ri4JR",
"mode": "list",
"cachedResultUrl": "/workflow/ZcFjrOgNK38ri4JR",
"cachedResultName": "FIN Test 20251111 - execute"
},
"workflowInputs": {
"mappingMode": "defineBelow",
"value": {},
"matchingColumns": [],
"schema": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
}
},
"type": "@n8n/n8n-nodes-langchain.toolWorkflow",
"typeVersion": 2.2,
"position": [
-288,
416
],
"id": "9aaededc-7019-4d95-bc00-68fb443fa08f",
"name": "Execute_Tool"
}
],
"connections": {
"When chat message received": {
"main": [
[
{
"node": "AI Agent",
"type": "main",
"index": 0
}
]
]
},
"Simple Memory": {
"ai_memory": [
[
{
"node": "AI Agent",
"type": "ai_memory",
"index": 0
}
]
]
},
"Generate_Tool": {
"ai_tool": [
[
{
"node": "AI Agent",
"type": "ai_tool",
"index": 0
}
]
]
},
"Google Gemini Chat Model": {
"ai_languageModel": [
[
{
"node": "AI Agent",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Execute_Tool": {
"ai_tool": [
[
{
"node": "AI Agent",
"type": "ai_tool",
"index": 0
}
]
]
}
},
"pinData": {},
"meta": {
"templateCredsSetupCompleted": true,
"instanceId": "2b64543853882a0137722fa2441cf5515e0ac494607c7430d768e94d12265bd3"
}
}
{
"nodes": [
{
"parameters": {
"inputSource": "passthrough"
},
"id": "c055762a-8fe7-4141-a639-df2372f30060",
"typeVersion": 1.1,
"name": "When Executed by Another Workflow",
"type": "n8n-nodes-base.executeWorkflowTrigger",
"position": [
272,
352
],
"alwaysOutputData": true
},
{
"parameters": {
"promptType": "define",
"text": "=Generate an SQL query with data available in n8n_invoice_summary.",
"options": {
"systemMessage": "# Overview\nYou are a financial data analyst specialized in sales and invoicing analytics.\nYour purpose is to convert user questions into clean and executable MySQL queries on the view n8n_invoice_summary.\n\n# Tools\n- MySQL executor: Runs SQL queries directly on the n8n_invoice_summary view. Use it for all data retrieval.\n\n# Rules\nQuery Source and Structure\n- Always query only from n8n_invoice_summary.\n- Always produce a valid MySQL query that can be executed as-is.\n- Always use standard MySQL syntax.\n- Keep SQL minimal and clean.\n\nTransaction Counting\n- Always count unique invoices using COUNT(DISTINCT Invoice_ID) when counting entries.\n\nMonetary Fields\n- If the query involves any monetary fields (columns with \"Amount\", \"total_invoice\", \"SST\", case-insensitive), include Currency.\n- If more than one currency exists in results, group by Currency in the query.\n\nText Filters\n- If user specifies a column, filter that column with LIKE '%keyword%'.\n- If user gives a free text keyword without specifying the field, search in these columns with OR conditions: Customer, Project_Code, Salesperson, Description\n- Example: WHERE Customer LIKE '%x%' OR Project_Code LIKE '%x%' OR Salesperson LIKE '%x%' OR Description LIKE '%x%'\n- Never use exact text match for descriptive columns unless user explicitly requests it.\n\nAggregation and Grouping\n- Use SUM, AVG, COUNT, etc when needed.\n- Use TRIM() when grouping text fields to avoid whitespace issues.\n\nInterpretation Logic\n- Identify user intent: list, count, sum, average, compare, top N, filter by date, group by.\n- If user means total but does not say \"total\", assume total when asking for an amount.\n- Avoid assumptions about specific text columns unless user specifies.\n\nIf unsure\n- Choose the safest interpretation that returns useful aggregated business output.\n- Prioritize total value queries when the question implies totals.\n- Relate similar column to the user request (Example: If user mention about cost, use the column \"Costing_Amount\")\n\n\n# Output format\nReturn only a valid SQL query with no explanation.\nExample output:\nSELECT Customer, Currency, SUM(Sales_Amount) AS Total_Sales FROM n8n_invoice_summary WHERE invoice_date BETWEEN '2025-03-01' AND '2025-03-31' GROUP BY Customer, Currency;\n\n\n# Examples\n\nUser: Who are the customers\nAgent: SELECT DISTINCT Customer FROM n8n_invoice_summary;\n\nUser: How many invoices in 2025\nAgent: SELECT COUNT(DISTINCT Invoice_ID) AS Total_Invoices FROM n8n_invoice_summary WHERE YEAR(invoice_date) = 2025 AND invoice_type = 'INVOICE';\n\nUser: Top 5 projects by sales\nAgent: SELECT Project_Code, Currency, SUM(Sales_Amount) AS Total_Sales FROM n8n_invoice_summary GROUP BY Project_Code, Currency ORDER BY Total_Sales DESC LIMIT 5;\n\nUser: Sales for maintenance in March\nAgent: SELECT Currency, SUM(Sales_Amount) AS Total_Sales FROM n8n_invoice_summary\nWHERE invoice_date BETWEEN '2025-03-01' AND '2025-03-31' AND (Customer LIKE '%maintenance%' OR Project_Code LIKE '%maintenance%' OR Salesperson LIKE '%maintenance%' OR Description LIKE '%maintenance%') GROUP BY Currency;"
}
},
"type": "@n8n/n8n-nodes-langchain.agent",
"typeVersion": 2.2,
"position": [
560,
352
],
"id": "cb88bea6-d01a-4687-a776-abd8948b4573",
"name": "AI Agent"
},
{
"parameters": {
"descriptionType": "manual",
"toolDescription": "Use this tool to run SQL queries on the database view `n8n_invoice_summary`.\n\nThis view combines invoice, customer, project, and salesperson data. \nIt already includes all necessary joins.\n\nAvailable columns:\n- invoicing_company, department_code, Invoice_no, invoice_date,\n- Customer_Code, Customer, Project_Code, Project_Status,\n- description, invoice_type, Currency, total_invoice, SST,\n- Sales_Amount, STQ_Amount, SOG_SYS_Amount, GP_Amount,\n- costing_amount, l133_amount, l181_amount, man_power_amount,\n- Salesperson, customer_p_o, category, detail_category,\n- payment_status, payment_date, terms_days, payment_amount.\n\nYou can:\n- Filter results by any column (for example, by customer, project, date, or department)\n- Count transactions or customers\n- Calculate totals, averages, or ratios such as Gross Profit Percentage (GP%) using:\n (SUM(GP_Amount) / SUM(Sales_Amount)) * 100\n- Group results by customer, project, or other columns\n- Limit results using ORDER BY and LIMIT if needed\n\nAlways write a complete SQL query that starts with SELECT and ends with a semicolon (;).",
"operation": "select",
"table": {
"__rl": true,
"value": "n8n_invoice_summary",
"mode": "list",
"cachedResultName": "n8n_invoice_summary"
},
"returnAll": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('Return_All', ``, 'boolean') }}",
"where": {
"values": [
{
"column": "Invoice_ID",
"condition": "IS NOT NULL"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.mySqlTool",
"typeVersion": 2.5,
"position": [
624,
576
],
"id": "be35ec64-43f1-4959-b1e4-ca6d0ddfd867",
"name": "MySQL executor",
"credentials": {
"mySql": {
"id": "7IJk4x66oTi1TJWO",
"name": "MySQL StrateqCM"
}
}
},
{
"parameters": {
"options": {}
},
"type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
"typeVersion": 1,
"position": [
496,
576
],
"id": "36bf8586-5e62-4e9f-af81-cdb85cdd652c",
"name": "Google Gemini Chat Model",
"credentials": {
"googlePalmApi": {
"id": "DHKkpMZIWrbBRaVY",
"name": "Google Gemini(PaLM) Api account 2"
}
}
},
{
"parameters": {},
"type": "@n8n/n8n-nodes-langchain.toolCode",
"typeVersion": 1.3,
"position": [
752,
576
],
"id": "e3fd89f6-8846-495f-86f5-b5aa4fb418be",
"name": "Code Tool"
}
],
"connections": {
"When Executed by Another Workflow": {
"main": [
[
{
"node": "AI Agent",
"type": "main",
"index": 0
}
]
]
},
"AI Agent": {
"main": [
[]
]
},
"MySQL executor": {
"ai_tool": [
[
{
"node": "AI Agent",
"type": "ai_tool",
"index": 0
}
]
]
},
"Google Gemini Chat Model": {
"ai_languageModel": [
[
{
"node": "AI Agent",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Code Tool": {
"ai_tool": [
[
{
"node": "AI Agent",
"type": "ai_tool",
"index": 0
}
]
]
}
},
"pinData": {},
"meta": {
"templateCredsSetupCompleted": true,
"instanceId": "2b64543853882a0137722fa2441cf5515e0ac494607c7430d768e94d12265bd3"
}
}
{
"nodes": [
{
"parameters": {
"inputSource": "passthrough"
},
"id": "c055762a-8fe7-4141-a639-df2372f30060",
"typeVersion": 1.1,
"name": "When Executed by Another Workflow",
"type": "n8n-nodes-base.executeWorkflowTrigger",
"position": [
272,
352
]
},
{
"parameters": {
"promptType": "define",
"text": "=Refer the data from MySQL executor to run this query: {{ $json.query }}",
"options": {
"systemMessage": "#Overview\nYou are a database execution assistant specialized in running SQL queries generated by another AI Agent.\nYour purpose is to safely execute valid SQL queries on the MySQL view n8n_invoice_summary and return the results as structured JSON for further analysis.\n\n#Tools\nMySQL Execution Tool:\nExecutes SQL queries directly on the connected MySQL database.\nUse this tool only when a valid SQL query is provided.\n\n#Rules\nAlways expect your input to contain a query generated by another AI Agent (e.g., Generate SQL AI Agent Tool).\nThe query will usually arrive as a formatted string or inside a Markdown code block (```sql ... ```).\nBefore execution, you must clean it by:\n- Removing code block markers (sql and ).\n- Removing newline characters (\\n).\n- Trimming leading/trailing spaces.\nNever modify the SQL query logic. Only clean it.\n\nIf no valid SELECT statement is found, return a JSON response:\n{ \"error\": \"No valid SQL query found to execute.\" }\n\nIf query execution fails, return:\n{ \"error\": \"Query execution failed or returned no result.\" }\n\nAlways return results as a JSON array of objects.\n\n#Output format\nWhen the query executes successfully, output should follow this JSON format:\n{\n \"query\": \"SELECT DISTINCT Customer FROM n8n_invoice_summary;\",\n \"result\": [\n { \"Customer\": \"ABC Sdn Bhd\" },\n { \"Customer\": \"XYZ Berhad\" }\n ]\n}\nIf an error occurs:\n{\n \"query\": \"SELECT DISTINCT Customer FROM n8n_invoice_summary;\",\n \"error\": \"Query execution failed or returned no result.\"\n}\n\n#Internal Logic (Expression Setup)\nUse this expression for the Query field inside the MySQL Tool:\n{{ \n (\n $fromAI('Generate SQL AI Agent Tool').response?.[0]?.output || ''\n )\n .replace(/```sql|```|\\n/g, '')\n .trim() \n}}\nThis ensures:\n- Code fences and newlines are stripped.\n- Query is passed directly to MySQL cleanly.\n\n#Examples\nExample 1\nInput from Generate SQL AI Agent Tool:\n{\n \"response\": [\n {\n \"output\": \"```sql\\nSELECT DISTINCT Customer FROM n8n_invoice_summary;\\n```\"\n }\n ]\n}\nExecute SQL AI Agent Output:\n{\n \"query\": \"SELECT DISTINCT Customer FROM n8n_invoice_summary;\",\n \"result\": [\n { \"Customer\": \"ABC Sdn Bhd\" },\n { \"Customer\": \"XYZ Berhad\" }\n ]\n}"
}
},
"type": "@n8n/n8n-nodes-langchain.agent",
"typeVersion": 2.2,
"position": [
512,
352
],
"id": "6c06d23a-3f7e-4e23-92e9-d27791927f80",
"name": "AI Agent"
},
{
"parameters": {
"descriptionType": "manual",
"toolDescription": "=This is the main database for the query to execute.",
"operation": "executeQuery",
"query": "{{ $json.query }}",
"options": {}
},
"type": "n8n-nodes-base.mySqlTool",
"typeVersion": 2.5,
"position": [
720,
576
],
"id": "30853d85-14f1-40ca-8eb8-76359482c23b",
"name": "MySQL executor",
"credentials": {
"mySql": {
"id": "7IJk4x66oTi1TJWO",
"name": "MySQL StrateqCM"
}
}
},
{
"parameters": {
"options": {}
},
"type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
"typeVersion": 1,
"position": [
512,
576
],
"id": "dedc41b7-49eb-4b5b-8001-2d496e4b3da8",
"name": "Google Gemini Chat Model",
"credentials": {
"googlePalmApi": {
"id": "DHKkpMZIWrbBRaVY",
"name": "Google Gemini(PaLM) Api account 2"
}
}
},
{
"parameters": {
"operation": "executeQuery",
"query": "{{ $('Prepare Query').item.json.query }}",
"options": {}
},
"type": "n8n-nodes-base.mySql",
"typeVersion": 2.5,
"position": [
1056,
416
],
"id": "2157a557-f59b-498a-a2fb-f74a442819d6",
"name": "Execute a SQL query1",
"alwaysOutputData": false,
"credentials": {
"mySql": {
"id": "7IJk4x66oTi1TJWO",
"name": "MySQL StrateqCM"
}
}
},
{
"parameters": {
"jsCode": "// Combine all SQL rows into one array\nconst allRows = $items().map(item => item.json);\n\n// Send as a single item\nreturn [{\n context: JSON.stringify(allRows, null, 2),\n rowCount: allRows.length\n}];\n"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
1280,
416
],
"id": "e707346a-982d-4d36-b38b-4acb2c5de731",
"name": "Combine Results"
},
{
"parameters": {
"jsCode": "// Extract SQL string from AI Agent output\nconst raw = $json.output || \"\";\n\n// Match SQL inside markdown block or plain text\nconst match = raw.match(/SELECT[\\s\\S]*?;/i);\n\n// If SQL exists, clean it — remove line breaks and extra spaces\nlet query = match ? match[0] : \"SELECT 'No valid SQL found' AS error;\";\n\n// Clean formatting: remove newlines, trim spaces, collapse double spaces\nquery = query\n .replace(/\\n/g, \" \")\n .replace(/\\s+/g, \" \")\n .trim();\n\nreturn { query };"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
832,
352
],
"id": "406e6d5c-af2f-43c9-914f-6afc75c40003",
"name": "Prepare Query"
}
],
"connections": {
"When Executed by Another Workflow": {
"main": [
[
{
"node": "AI Agent",
"type": "main",
"index": 0
}
]
]
},
"AI Agent": {
"main": [
[
{
"node": "Prepare Query",
"type": "main",
"index": 0
}
]
]
},
"MySQL executor": {
"ai_tool": [
[
{
"node": "AI Agent",
"type": "ai_tool",
"index": 0
}
]
]
},
"Google Gemini Chat Model": {
"ai_languageModel": [
[
{
"node": "AI Agent",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Execute a SQL query1": {
"main": [
[
{
"node": "Combine Results",
"type": "main",
"index": 0
}
]
]
},
"Prepare Query": {
"main": [
[
{
"node": "Execute a SQL query1",
"type": "main",
"index": 0
}
]
]
}
},
"pinData": {},
"meta": {
"instanceId": "2b64543853882a0137722fa2441cf5515e0ac494607c7430d768e94d12265bd3"
}
}
Share the output returned by the last node
Information on your n8n setup
- n8n version: 1.114.3 (self-hosted)
- Database (default: SQLite): MySQL with a view named
n8n_invoice_summary - n8n EXECUTIONS_PROCESS setting (default: own, main):
- Running n8n via (Docker, npm, n8n cloud, desktop app):
- Operating system: