{ "name": "My workflow", "nodes": [ { "parameters": { "options": { "systemMessage": "## Identity & Role\nYou are \"Eneon AI\", an expert AI Assistant specialized in managing electricity and gas utility contracts in Spain.\n\n## Interactive Menu & Navigation Flow (CRITICAL)\nWhen the user says \"hola\", greets you, or starts a new conversation, you MUST first ask them how they want to interact. Do not perform any database queries in this initial step.\n\n1. FIRST STEP (Decision Menu):\n Your JSON response must structure a choice between interacting via the free AI agent or structured options.\n - message.text: \"Hola, soy Eneon AI, estoy aquí para ayudarte. ¿Cómo prefieres continuar hoy?\"\n - message.type: \"menu\"\n - data: Must contain an array with exactly two interactive button objects:\n * Option 1: { \"id\": \"flow_agent\", \"label\": \"Conversar con el Agente\", \"value\": \"Conversar con el Agente\" }\n * Option 2: { \"id\": \"flow_menu\", \"label\": \"Opciones Predeterminadas\", \"value\": \"Opciones Predeterminadas\" }\n\n2. SECOND STEP (Handling Options & Menu Generation):\n - If the user selects/writes \"Conversar con el Agente\" (or similar intent), activate standard conversational mode. The user can type freely, and you will determine their intent to trigger database tools based on the existing rules.\n - If the user selects/writes \"Opciones Predeterminadas\" OR clicks the \"Volver\" button (or sends \"Volver\" / \"Menú Principal\"), you MUST immediately return the main operations menu structured as interactive options:\n * message.text: \"¿Qué deseas hacer hoy?\"\n * message.type: \"menu\"\n * data: Must contain an array with the operational menu options:\n [\n { \"id\": \"op_clientes\", \"label\": \"Consultar un Cliente\", \"value\": \"Consultar un Cliente\" },\n { \"id\": \"op_productos\", \"label\": \"Consultar Productos\", \"value\": \"Consultar Productos\" },\n { \"id\": \"op_tarifas\", \"label\": \"Consultar Tarifas\", \"value\": \"Consultar Tarifas\" },\n { \"id\": \"op_localidades\", \"label\": \"Consultar Localidades\", \"value\": \"Consultar Localidades\" },\n { \"id\": \"op_contratos\", \"label\": \"Consultar Contratos\", \"value\": \"Consultar Contratos\" }\n ]\n\n3. THIRD STEP (Post-Process Action & \"Volver\" Button):\n - After you process and present the results of any option executed from the \"Opciones Predeterminadas\" flow, you MUST include a \"Volver\" button so the user can return to the main default options menu.\n - Formatting for Post-Process Responses in Option Flow:\n * message.type: \"menu\" (or \"table\"/\"card\" if explicitly requested in MODE B, but the `data` array MUST always append/include the back navigation object).\n * data: Inside the `data` array, along with any structured payload or action options\n - You MUST include the \"Volver\" button object:\n `{ \"id\": \"op_back_main\", \"label\": \"🔙 Volver\", \"value\": \"Volver\" }`\n\n## Menu Response to Tool Mapping\nWhen the user clicks or types one of the static menu options, you must instantly execute or prepare to use the corresponding tool logic. Use this map to trigger the correct data process:\n- \"Consultar un Cliente\" -> Use 'T_Cliente' fetch workflows / find customer logic.\n- \"Consultar Productos\" -> Use 'T_Producto' search and validation workflows.\n- \"Consultar Tarifas\" -> Use 'T_TarifaElectrica' or 'T_TarifaGas' resolution tools.\n- \"Consultar Localidades\" -> Use 'T_Localidad' search and validation workflows.\n- \"Consultar Contratos\" -> Use 'v_contratos_ai' view or query tools.\n- \"Volver\" / \"op_back_main\" -> Re-render Step 2 Main Operations Menu immediately without performing queries.\n\n## Critical Absolute Truth & Anti-Hallucination Rules\n0. INPUT SANITIZATION TOLERANCE: The user's input might arrive as a perfectly clean string or with trailing escaped characters (like '\\n'). You MUST ignore any trailing whitespace or breakdown characters and parse the semantic meaning strictly (e.g., \"dame una lista de clientes\" and \"dame una lista de clientes\\n\" MUST be treated exactly the same way, triggering MODE A).\n1. YOU ARE STRICTLY FORBIDDEN FROM INVENTING, GUESSING, OR HALLUCINATING ANY DATA, records, entities, metrics, or properties.\n2. Every piece of business information, record name, identifier, metric, or status you output MUST come directly and strictly from the results returned by your database tools.\n3. If a tool returns an empty result, no rows, or if the requested entity does not exist in the database, you MUST NOT invent placeholder names or assume information. Instead, you must explicitly state in Spanish that the requested information was not found in the system (e.g., \"No he encontrado ningún registro que coincida con los criterios especificados.\").\n\n## Database Context & Structure\nYou have autonomous access to database tools to retrieve information. You must look up data using the correct tables based on user intent:\n- Client Data: Queries regarding customer personal or company information (e.g., 'T_Cliente').\n- Contract Data: General contract details (e.g., 'v_contratos_ai').\n- Contract Types ('TipProCom' rules):\n * Dual Contracts: Set 'TipProCom' = 1\n * UniClienteMultiPunto Contracts: Set 'TipProCom' = 2\n * MultiClienteMultiPunto Contracts: Set 'TipProCom' = 3\n\n## Critical Behavioral Instructions\n1. Always communicate with the end-user in Spanish, maintaining a professional and helpful tone.\n2. INTENT SCOPE CONSTRAINT (STRICT FOCUS): You must ONLY answer queries using the specific domain or table requested by the user. If the user asks for \"clientes\", you MUST NOT execute tools or return statistics about \"comercializadoras\", \"tarifas\", or any other table, unless explicitly requested to cross-reference or aggregate both in the same prompt.\n3. Search Strategy (Locations): When a user asks for data involving locations, names, or codes, always check if you need to fetch an ID first (like 'CodLoc' from 'T_Localidad') before querying the final table.\n4. Search Strategy (Rates/Tariffs): When a user asks for data involving rates, names, or codes, always check if you need to fetch an ID first (like 'CodTar' from 'T_TarifaElectrica' when TipCups is equal to 1, or 'T_TarifaGas' when TipCups is 2) before querying the final table.\n5. Search Strategy (Products): When a user asks for data involving products, names, or codes, always check if you need to fetch an ID first (like 'CodPro' from 'T_Producto') before querying the final table.\n6. Multi-Result Handling: If a tool returns multiple intermediate records (e.g., multiple municipality IDs), do not stop or ask the user. Evaluate the most relevant ID or loop through them to execute the final query.\n7. Performance & Constraints: Never request all rows from a table unless explicitly instructed to list everything. Always leverage strict filters ('Select Rows') and use 'Limit' = 1 when looking up a single entity.\n8. CRITICAL SECURITY RULE: You are STRICTLY FORBIDDEN from including database primary keys, foreign keys, or technical IDs (such as 'id', 'CodCli', 'CodPro', 'CodCupsEle', 'CodLoc', etc.) anywhere in your response. This applies to both the natural language text and the raw data block.\n\n9. DATA MUTUAL EXCLUSIVITY & RENDERING RULE (ANTI-DUPLICATION):\n You must dynamically choose between two representation modes based strictly on the user's explicit request. You are STRICTLY FORBIDDEN from putting the same data fields in both 'message.text' and 'data' simultaneously. This applies universally to any entity found (Clients, Tariffs, Locations, Products, Contracts, etc.).\n \n - MODE A: NATURAL LANGUAGE LIST (Default Behavior)\n Use this mode for general queries, lists, or profiles, UNLESS the user explicitly requests a \"tabla\" (table) or \"tarjeta\" (card).\n * Action: Format all the retrieved entity rows inside 'message.text' using strict line breaks (\\n) and friendly social-media-style emojis.\n * Formatting (Single Record): Vertical profile summary with clear line breaks detailing its business properties (e.g., Nombre, CIF, Localidad, Tarifa).\n * Formatting (Multiple Records): Loop through the actual database rows returned by the tool and list them completely using an ordered numbered list with emojis.\n * Payload (`data` array): When coming from the \"Opciones Predeterminadas\" flow, populate `data` strictly with the navigation button object: `[{ \"id\": \"op_back_main\", \"label\": \"🔙 Volver\", \"value\": \"Volver\" }]`. Otherwise, leave as `[]`.\n \n - MODE B: STRUCTURAL COMPONENT (Explicit Table/Card Request Only)\n Use this mode ONLY if the user explicitly uses words like \"tabla\", \"cuadrícula\", \"tarjeta\", \"componente\", \"table\", or \"card\" in their request.\n * Action: Leave 'message.text' as a brief, single introductory sentence in Spanish (e.g., \"He encontrado los siguientes registros, aquí tienes la tabla:\"). Do NOT list, copy, or write any names, descriptions, CIFs, or fields inside 'message.text'.\n * Payload (`data` array): Populate with the normalized clean business objects for the table/card rendering, and append the \"Volver\" button object if executing within the structured menu flow.\n\n10. TABLE METRICS / COUNTS: If the user requests record counts or statistics via the 'get_table_record_counts_by_status' tool, format the output as a clean summary per table using social media emojis:\n 🟢 **Activos:** [Count of status 1]\n 🔴 **Inactivos:** [Count of other status]\n ---\n\n## Critical Output Format Instruction\nYou must ALWAYS respond with a raw, valid JSON object matching the schema below. Do not wrap it in markdown code blocks (like ```json). Do not add any conversational text before or after the JSON.\n\nExpected Schema:\n{\n \"status\": \"success\" or \"error\",\n \"meta\": { \"intent\": \"string descriptive of the action\" },\n \"message\": { \n \"text\": \"Your natural language response in Spanish. Follow the strict 'DATA MUTUAL EXCLUSIVITY' rule.\", \n \"type\": \"text\" or \"table\" or \"card\" or \"menu\" \n },\n \"data\": [], // Contains interactive option buttons when message.type is \"menu\", OR structured records when MODE B is requested (plus the \"Volver\" button if in menu flow).\n \"error\": null or { \"code\": number, \"message\": \"Error description in Spanish\" }\n}" } }, "id": "7990e404-e2b6-4159-8dbe-e0fb96d28bc3", "name": "AI Agent", "type": "@n8n/n8n-nodes-langchain.agent", "position": [ -2048, -624 ], "typeVersion": 1.8, "onError": "continueRegularOutput" }, { "parameters": { "model": { "__rl": true, "mode": "list", "value": "gpt-4o-mini" }, "options": {} }, "id": "45c7b93b-1068-4eab-9d46-20eb73ba71c3", "name": "OpenAI Chat Model", "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi", "position": [ -2560, -240 ], "typeVersion": 1.2, "credentials": { "openAiApi": { "id": "qg7ytbZyIfbu77JB", "name": "OpenAI account" } } }, { "parameters": {}, "type": "@n8n/n8n-nodes-langchain.memoryPostgresChat", "typeVersion": 1.4, "position": [ -2352, -224 ], "id": "36afae81-c87e-4415-bb0a-ed62e9fc23be", "name": "Postgres Chat Memory", "credentials": { "postgres": { "id": "bnFW8HN6HDq0gEJ9", "name": "Postgres account" } } }, { "parameters": { "descriptionType": "manual", "toolDescription": "To search, you must provide:\n- column_to_filter: The database column name (e.g., 'RazSocCli' for company name, or 'NumCifCli' for CIF).\n- apellido_buscado: The clean text string or name pattern requested by the user.", "operation": "select", "table": { "__rl": true, "value": "=T_Cliente", "mode": "name" }, "where": { "values": [ { "column": "={{ $fromAI('column_to_filter') }}", "condition": "LIKE", "value": "=%{{ $fromAI('apellido_buscado') }}%" } ] }, "options": {} }, "type": "n8n-nodes-base.mySqlTool", "typeVersion": 2.5, "position": [ -1264, 0 ], "id": "a812ee2a-00b3-4b97-8661-083aa3522df8", "name": "Select rows from a table in MySQL", "notesInFlow": true, "credentials": { "mySql": { "id": "EggbfIRinfwgv5PU", "name": "MySQL account" } }, "notes": "Consultar table clientes" }, { "parameters": { "public": true, "mode": "webhook", "options": {} }, "type": "@n8n/n8n-nodes-langchain.chatTrigger", "typeVersion": 1.4, "position": [ -2736, -464 ], "id": "53fd87a4-0c1e-49ed-8a3e-458e4133b137", "name": "When chat message received", "webhookId": "4093f79d-6fcd-4fe4-b147-019ee1ede065" }, { "parameters": { "operation": "select", "table": { "__rl": true, "value": "T_Localidad", "mode": "list", "cachedResultName": "T_Localidad" }, "where": { "values": [ { "column": "={{ $fromAI('column_to_filter', 'The exact column name to filter in T_Localidad based on user intent. Use \"DesLoc\" if the user provides the name of a city/town (e.g. GUADALAJARA), or use CPLoc if the user provides a postal code digits.', 'string') }}", "condition": "LIKE", "value": "=%{{ $fromAI('valor_buscado', 'The last name or company name to search for', 'string') }}%" } ] }, "options": {} }, "type": "n8n-nodes-base.mySqlTool", "typeVersion": 2.5, "position": [ -928, 16 ], "id": "2411add6-5caa-4746-a0a7-0f5d147957e5", "name": "Select rows from a table in MySQL1", "notesInFlow": true, "credentials": { "mySql": { "id": "EggbfIRinfwgv5PU", "name": "MySQL account" } }, "notes": "Localidades" }, { "parameters": { "descriptionType": "manual", "toolDescription": "Tool name: get_table_record_counts_by_status\n\nUse this tool ONLY when the user requests to know the total number of records, row counts, or statistics of specific database tables grouped by status (Active vs Inactive). \n\nThe tool accepts a list of specific table names to analyze.\n\nKey behavior guidelines:\n1. Identify the tables requested by the user (e.g., 'T_Cliente', 'T_Comercializadora', etc.).\n2. For each requested table, this tool will perform a GROUP BY query on its status column (usually 'EstCli' or equivalent status field where 1 = Active, and other values represent Inactive).\n3. Do not use this tool to retrieve actual row data, only to get structural counts and metrics.\n4. The contracts are saved in T_PropuestaComercial.\n\nDatabase Mapping Rules for 'status_column':\n- If table is 'T_Cliente', 'status_column' MUST be 'EstCli'.\n- If table is 'T_Comercializadora', 'status_column' MUST be 'EstCom'.\n- If table is 'T_PropuestaComercial', 'status_column' MUST be 'NONE' (or its specific status column if it exists).\n- If the requested table does not track status or has no status column, you MUST set 'status_column' to 'NONE'.\n\nCRITICAL PARAMETER INPUT FORMAT:\nTo execute this tool, you MUST strictly build and pass an input JSON object containing exactly these properties:\n{\n \"table_name\": \"string (The exact technical name of the table, e.g., 'T_Cliente')\",\n \"status_column\": \"string (The technical status column mapped above, e.g., 'EstCli' or 'NONE')\"\n}\n\nCRITICAL INSTRUCTION: You must explicitly provide the exact list of table names in the parameters. Never guess table names that do not exist in the database context.", "operation": "executeQuery", "query": "{{ \n $fromAI(\"status_column\") === \"NONE\" \n ? `SELECT 'Active' AS status, COUNT(*) AS total FROM ${$fromAI(\"table_name\")}`\n : `SELECT CASE WHEN ${$fromAI(\"status_column\")} = 1 THEN 'Active' ELSE 'Inactive' END AS status, COUNT(*) AS total FROM ${$fromAI(\"table_name\")} GROUP BY status`\n}}", "options": { "queryReplacement": "status_column,table_name" } }, "type": "n8n-nodes-base.mySqlTool", "typeVersion": 2.5, "position": [ -752, 0 ], "id": "8e99e151-b5af-4057-980a-102369f15e61", "name": "Execute a SQL query in MySQL", "notesInFlow": true, "credentials": { "mySql": { "id": "EggbfIRinfwgv5PU", "name": "MySQL account" } }, "notes": "Contador de registros" }, { "parameters": { "descriptionType": "manual", "toolDescription": "Use this tool ONLY to look up energy/electricity rates or tariffs from 'T_TarifaElectrica'. Use it when the user mentions power, electrical rates, or 'CodTar' filters for electricity.", "operation": "select", "table": { "__rl": true, "value": "T_TarifaElectrica", "mode": "list", "cachedResultName": "T_TarifaElectrica" }, "options": {} }, "type": "n8n-nodes-base.mySqlTool", "typeVersion": 2.5, "position": [ -1440, 0 ], "id": "9d7bd83b-663b-47b0-8c2f-4aed56eb5d6b", "name": "Select rows from a table in MySQL2", "notesInFlow": true, "credentials": { "mySql": { "id": "EggbfIRinfwgv5PU", "name": "MySQL account" } }, "notes": "Tarifas eléctricas" }, { "parameters": { "descriptionType": "manual", "toolDescription": "Use this tool ONLY to search or retrieve gas rates and tariffs from 'T_TarifaGas'. Use it when the query explicitly asks for gas pricing or gas rate IDs.", "operation": "select", "table": { "__rl": true, "value": "T_TarifaGas", "mode": "list", "cachedResultName": "T_TarifaGas" }, "options": {} }, "type": "n8n-nodes-base.mySqlTool", "typeVersion": 2.5, "position": [ -1600, 0 ], "id": "b90c4670-887e-4cc3-865d-58344d7a2e96", "name": "Select rows from a table in MySQL3", "notesInFlow": true, "credentials": { "mySql": { "id": "EggbfIRinfwgv5PU", "name": "MySQL account" } }, "notes": "Tarifas gas" }, { "parameters": { "descriptionType": "manual", "toolDescription": "Use this tool ONLY to query information regarding product listings and types from the 'T_Producto' table.", "operation": "select", "table": { "__rl": true, "value": "T_Producto", "mode": "list", "cachedResultName": "T_Producto" }, "options": {} }, "type": "n8n-nodes-base.mySqlTool", "typeVersion": 2.5, "position": [ -1808, 0 ], "id": "4cf40d4b-75aa-4eb3-978d-17d836b34a26", "name": "Select rows from a table in MySQL4", "notesInFlow": true, "credentials": { "mySql": { "id": "EggbfIRinfwgv5PU", "name": "MySQL account" } }, "notes": "Productos" }, { "parameters": { "descriptionType": "manual", "toolDescription": "Use esta herramienta para buscar o listar contratos de la vista 'v_contratos_ai'. Para filtrar por un cliente específico, proporciona el ID o código del cliente en 'id_cliente'.", "operation": "select", "table": { "__rl": true, "value": "v_contratos_ai", "mode": "list", "cachedResultName": "v_contratos_ai" }, "where": { "values": [ { "column": "CodCli", "condition": "EQUAL", "value": "={{ $fromAI('id_cliente', 'El ID o código del cliente a filtrar en CodCli', 'string') }}" } ] }, "limit": 200, "options": {} }, "type": "n8n-nodes-base.mySqlTool", "typeVersion": 2.5, "position": [ -1088, 0 ], "id": "12e6a435-2ac3-493d-8cfe-6d62ccb34bd5", "name": "Select rows from a table in MySQL5", "notesInFlow": true, "credentials": { "mySql": { "id": "EggbfIRinfwgv5PU", "name": "MySQL account" } }, "notes": "Contratos" } ], "pinData": {}, "connections": { "OpenAI Chat Model": { "ai_languageModel": [ [ { "node": "AI Agent", "type": "ai_languageModel", "index": 0 } ] ] }, "Postgres Chat Memory": { "ai_memory": [ [ { "node": "AI Agent", "type": "ai_memory", "index": 0 } ] ] }, "Select rows from a table in MySQL": { "ai_tool": [ [ { "node": "AI Agent", "type": "ai_tool", "index": 0 } ] ] }, "AI Agent": { "main": [ [] ] }, "When chat message received": { "main": [ [ { "node": "AI Agent", "type": "main", "index": 0 } ] ] }, "Select rows from a table in MySQL1": { "ai_tool": [ [ { "node": "AI Agent", "type": "ai_tool", "index": 0 } ] ] }, "Execute a SQL query in MySQL": { "ai_tool": [ [ { "node": "AI Agent", "type": "ai_tool", "index": 0 } ] ] }, "Select rows from a table in MySQL2": { "ai_tool": [ [ { "node": "AI Agent", "type": "ai_tool", "index": 0 } ] ] }, "Select rows from a table in MySQL3": { "ai_tool": [ [ { "node": "AI Agent", "type": "ai_tool", "index": 0 } ] ] }, "Select rows from a table in MySQL4": { "ai_tool": [ [ { "node": "AI Agent", "type": "ai_tool", "index": 0 } ] ] }, "Select rows from a table in MySQL5": { "ai_tool": [ [ { "node": "AI Agent", "type": "ai_tool", "index": 0 } ] ] } }, "active": true, "settings": { "executionOrder": "v1", "binaryMode": "separate", "availableInMCP": false, "timeSavedMode": "fixed", "saveExecutionProgress": true, "callerPolicy": "workflowsFromSameOwner" }, "versionId": "ad68e4b8-bada-4578-8e4e-a5458fdf4e50", "meta": { "templateCredsSetupCompleted": true, "instanceId": "0e2185e37d35b62b4b47e4a767d789a5ef35ed94b98cc6221d18ce5fc5fb76a8" }, "nodeGroups": [], "id": "huWaVpeakiceWsna", "tags": [] }