Ticket Confirmation

{ "edges": [ { "to": "c9ed6968-dbad-4990-bbdd-cd97f3b09d28", "from": "647ddb9c-73dc-42b4-bf8b-cc0ef7fe34f1", "toPort": "0", "fromPort": "ai_languageModel" }, { "to": "2624805e-f8f9-4cb7-8de2-c3ae7cd2f975", "from": "c66b99ac-24f0-47e4-bf7e-bb9055c1438f", "toPort": "0", "fromPort": "0" }, { "to": "37b78d8c-d365-408e-a10d-74f624f7d4f6", "from": "c9ed6968-dbad-4990-bbdd-cd97f3b09d28", "toPort": "0", "fromPort": "0" }, { "to": "09dbcfce-663e-4164-944c-aaaa5bbbd6cf", "from": "c9ed6968-dbad-4990-bbdd-cd97f3b09d28", "toPort": "0", "fromPort": "1" }, { "to": "d27192a8-7fd7-43e5-a1b9-0038bd37c582", "from": "c9ed6968-dbad-4990-bbdd-cd97f3b09d28", "toPort": "0", "fromPort": "2" }, { "to": "62cc5b11-2887-4417-81c1-7fae3026f9dc", "from": "c9ed6968-dbad-4990-bbdd-cd97f3b09d28", "toPort": "0", "fromPort": "3" }, { "to": "d57bf31f-b6f3-442b-b3ea-6de3553f79a3", "from": "c9ed6968-dbad-4990-bbdd-cd97f3b09d28", "toPort": "0", "fromPort": "4" }, { "to": "c9ed6968-dbad-4990-bbdd-cd97f3b09d28", "from": "2624805e-f8f9-4cb7-8de2-c3ae7cd2f975", "toPort": "0", "fromPort": "0" }, { "to": "9baf8647-e6b9-4320-9f1a-ba2ecad19093", "from": "5eeb559e-759c-4566-aaa6-670c091f336a", "toPort": "0", "fromPort": "0" }, { "to": "ff05d10c-ff67-418b-8b0c-fe31473e5ee5", "from": "063c92c3-4ba6-472a-95ab-bd0bb6ead7db", "toPort": "0", "fromPort": "0" }, { "to": "5eeb559e-759c-4566-aaa6-670c091f336a", "from": "37b78d8c-d365-408e-a10d-74f624f7d4f6", "toPort": "0", "fromPort": "0" }, { "to": "5eeb559e-759c-4566-aaa6-670c091f336a", "from": "09dbcfce-663e-4164-944c-aaaa5bbbd6cf", "toPort": "0", "fromPort": "0" }, { "to": "5eeb559e-759c-4566-aaa6-670c091f336a", "from": "d27192a8-7fd7-43e5-a1b9-0038bd37c582", "toPort": "0", "fromPort": "0" }, { "to": "5eeb559e-759c-4566-aaa6-670c091f336a", "from": "62cc5b11-2887-4417-81c1-7fae3026f9dc", "toPort": "0", "fromPort": "0" }, { "to": "5eeb559e-759c-4566-aaa6-670c091f336a", "from": "d57bf31f-b6f3-442b-b3ea-6de3553f79a3", "toPort": "0", "fromPort": "0" }, { "to": "79c08a8a-3724-4d30-ac92-8944bf0ef1ae", "from": "832507a4-bce5-4d85-af99-fe775b2e53bf", "toPort": "0", "fromPort": "0" }, { "to": "33cc911f-732a-4039-abd3-2bb0bbb11654", "from": "79c08a8a-3724-4d30-ac92-8944bf0ef1ae", "toPort": "0", "fromPort": "0" }, { "to": "063c92c3-4ba6-472a-95ab-bd0bb6ead7db", "from": "33cc911f-732a-4039-abd3-2bb0bbb11654", "toPort": "0", "fromPort": "0" }, { "to": "832507a4-bce5-4d85-af99-fe775b2e53bf", "from": "9baf8647-e6b9-4320-9f1a-ba2ecad19093", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "2624805e-f8f9-4cb7-8de2-c3ae7cd2f975", "name": "Get many messages", "type": "integration", "params": { "limit": 1, "filters": {}, "service": "gmail", "operation": "getAll" }, "position": [ -576, -240 ] }, { "id": "c9ed6968-dbad-4990-bbdd-cd97f3b09d28", "name": "Text Classifier", "type": "llm", "params": { "prompt": "", "options": { "systemPromptTemplate": "You are an intelligent email and support ticket classifier.\n\nClassify the user's message into exactly ONE of these categories:{categories}\n" }, "provider": "openai", "inputText": "={{ $json.Subject }}", "categories": { "categories": [ { "category": "Payment Issue", "description": "Issues related to payment failure, duplicate payment, refund requests, subscription activation, invoice problems, or money deducted from customer account." }, { "category": "Login Failure", "description": "Issues where users cannot log in, forgot password, account locked, OTP problems, authentication failure, or access denied." }, { "category": "Enhancement Request", "description": "Requests for new features, UI improvements, integrations, performance enhancements, or product suggestions." }, { "category": "Fraud Alert", "description": "Unauthorized access attempts, suspicious transactions, account hacking concerns, phishing reports, or security-related complaints." }, { "category": "Other", "description": "Unclassified " } ] }, "approximated": true }, "position": [ -304, -288 ] }, { "id": "647ddb9c-73dc-42b4-bf8b-cc0ef7fe34f1", "name": "Ollama Model", "type": "integration", "params": { "model": "llama3:latest", "options": { "lowVram": true }, "service": "lmollama", "operation": "run", "__originalType": "@n8n/n8n-nodes-langchain.lmOllama" }, "position": [ -304, 112 ] }, { "id": "832507a4-bce5-4d85-af99-fe775b2e53bf", "name": "Insert rows in a table", "type": "integration", "params": { "table": { "__rl": true, "mode": "list", "value": "tickets", "cachedResultName": "tickets" }, "schema": { "__rl": true, "mode": "list", "value": "public", "cachedResultName": "public" }, "columns": { "value": { "status": "Active", "message": "={{ $('Get many messages').item.json.snippet }}", "subject": "={{ $('Get many messages').item.json.Subject }}", "category": "Oher Unclassified", "priority": "Less", "sentiment": "NA", "assigned_to": "={{ $json.id }}", "ticket_number": "={{ $('Code in JavaScript').item.json.ticket_number }}", "customer_email": "={{ $('Get many messages').item.json.From }}" }, "schema": [ { "id": "id", "type": "number", "display": true, "removed": true, "required": false, "displayName": "id", "defaultMatch": true, "canBeUsedToMatch": true }, { "id": "ticket_number", "type": "string", "display": true, "required": true, "displayName": "ticket_number", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "customer_email", "type": "string", "display": true, "required": true, "displayName": "customer_email", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "subject", "type": "string", "display": true, "required": false, "displayName": "subject", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "message", "type": "string", "display": true, "required": false, "displayName": "message", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "category", "type": "string", "display": true, "required": false, "displayName": "category", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "priority", "type": "string", "display": true, "required": false, "displayName": "priority", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "sentiment", "type": "string", "display": true, "required": false, "displayName": "sentiment", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "status", "type": "string", "display": true, "required": false, "displayName": "status", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "assigned_to", "type": "number", "display": true, "required": false, "displayName": "assigned_to", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "created_at", "type": "dateTime", "display": true, "required": false, "displayName": "created_at", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "updated_at", "type": "dateTime", "display": true, "required": false, "displayName": "updated_at", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [ "id" ], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "postgres", "operation": "read" }, "position": [ 1408, -256 ] }, { "id": "5eeb559e-759c-4566-aaa6-670c091f336a", "name": "Code in JavaScript", "type": "code", "params": { "jsCode": "for (const item of $input.all()) {\n\n const now = new Date();\n\n const year = now.getFullYear();\n const month = String(now.getMonth() + 1).padStart(2, '0');\n const day = String(now.getDate()).padStart(2, '0');\n const hours = String(now.getHours()).padStart(2, '0');\n const minutes = String(now.getMinutes()).padStart(2, '0');\n const seconds = String(now.getSeconds()).padStart(2, '0');\n const random = Math.floor(100 + Math.random() * 900);\n\n const ticketId =\n `TKT[Other]-${year}${month}${day}-${hours}${minutes}${seconds}-${random}`;\n\n item.json.ticket_number = ticketId;\n\n\n item.json.myNewField = $input.first().json.myNewField;\n}\n\nreturn $input.all();" }, "position": [ 960, -256 ] }, { "id": "9baf8647-e6b9-4320-9f1a-ba2ecad19093", "name": "Select rows from a table", "type": "integration", "params": { "table": { "__rl": true, "mode": "list", "value": "support_persons", "cachedResultName": "support_persons" }, "where": { "values": [ { "value": "={{ $json.myNewField }}", "column": "skill_category", "condition": "LIKE" }, { "value": "5", "column": "active_tickets", "condition": "<" } ] }, "schema": { "__rl": true, "mode": "list", "value": "public" }, "options": {}, "service": "postgres", "operation": "select" }, "position": [ 1184, -256 ] }, { "id": "79c08a8a-3724-4d30-ac92-8944bf0ef1ae", "name": "Update rows in a table", "type": "integration", "params": { "table": { "__rl": true, "mode": "list", "value": "support_persons", "cachedResultName": "support_persons" }, "schema": { "__rl": true, "mode": "list", "value": "public" }, "columns": { "value": { "id": "={{ $('Select rows from a table').item.json.id }}", "active_tickets": "={{ Number($('Select rows from a table').item.json.active_tickets) + 1 }}" }, "schema": [ { "id": "id", "type": "number", "display": true, "removed": false, "required": false, "displayName": "id", "defaultMatch": true, "canBeUsedToMatch": true }, { "id": "name", "type": "string", "display": true, "removed": true, "required": true, "displayName": "name", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "email", "type": "string", "display": true, "removed": true, "required": true, "displayName": "email", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "department", "type": "string", "display": true, "removed": true, "required": false, "displayName": "department", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "skill_category", "type": "string", "display": true, "removed": true, "required": false, "displayName": "skill_category", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "status", "type": "string", "display": true, "removed": true, "required": false, "displayName": "status", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "active_tickets", "type": "number", "display": true, "required": false, "displayName": "active_tickets", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "max_tickets", "type": "number", "display": true, "removed": true, "required": false, "displayName": "max_tickets", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "created_at", "type": "dateTime", "display": true, "removed": true, "required": false, "displayName": "created_at", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [ "id" ], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "postgres", "operation": "update" }, "position": [ 1632, -256 ] }, { "id": "33cc911f-732a-4039-abd3-2bb0bbb11654", "name": "Insert rows in a table1", "type": "integration", "params": { "table": { "__rl": true, "mode": "list", "value": "ticket_assignment_logs", "cachedResultName": "ticket_assignment_logs" }, "schema": { "__rl": true, "mode": "list", "value": "public" }, "columns": { "value": { "ticket_id": "={{ $('Code in JavaScript').item.json.ticket_number }}", "assignment_reason": "={{ $('Select rows from a table').item.json.status }}", "support_person_id": "={{ $('Select rows from a table').item.json.id }}" }, "schema": [ { "id": "id", "type": "number", "display": true, "required": false, "displayName": "id", "defaultMatch": true, "canBeUsedToMatch": true }, { "id": "ticket_id", "type": "string", "display": true, "required": true, "displayName": "ticket_id", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "support_person_id", "type": "number", "display": true, "required": true, "displayName": "support_person_id", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "assigned_at", "type": "dateTime", "display": true, "required": false, "displayName": "assigned_at", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "assignment_reason", "type": "string", "display": true, "required": false, "displayName": "assignment_reason", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [ "id" ], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "postgres", "operation": "read" }, "position": [ 1856, -256 ] }, { "id": "063c92c3-4ba6-472a-95ab-bd0bb6ead7db", "name": "Reply to a message", "type": "integration", "params": { "message": "=\n\n\n \n Ticket Confirmation\n\n\n\n

Support Ticket Generated Successfully

\n\n

Dear Customer,

\n\n

Thank you for contacting our support team. We have successfully received your request and created a support ticket for your issue.

\n\n

Ticket Details

\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n

Ticket ID	{{ $('Code in JavaScript').item.json.ticket_number }}
Support Person	{{ $('Select rows from a table').item.json.name }}
Support Person Contact	{{ $('Select rows from a table').item.json.email }}
Status	Under Review

\n\n

\n We appreciate your patience. You will receive another update once the issue is resolved.\n

\n\n
\n\n

Regards,
\n Support Team

\n\n\n", "options": { "ccList": "={{ $('Select rows from a table').item.json.email }}" }, "service": "gmail", "messageId": "={{ $('Get many messages').item.json.id }}", "operation": "reply" }, "position": [ 2080, -256 ] }, { "id": "37b78d8c-d365-408e-a10d-74f624f7d4f6", "name": "Code in JavaScript1", "type": "code", "params": { "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nfor (const item of $input.all()) {\n item.json.myNewField = 'Payment Issue';\n}\n\nreturn $input.all();" }, "position": [ 400, -624 ] }, { "id": "09dbcfce-663e-4164-944c-aaaa5bbbd6cf", "name": "Code in JavaScript2", "type": "code", "params": { "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nfor (const item of $input.all()) {\n item.json.myNewField = 'Bug Report';\n}\n\nreturn $input.all();" }, "position": [ 400, -432 ] }, { "id": "d27192a8-7fd7-43e5-a1b9-0038bd37c582", "name": "Code in JavaScript3", "type": "code", "params": { "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nfor (const item of $input.all()) {\n item.json.myNewField = 'General Query';\n}\n\nreturn $input.all();" }, "position": [ 400, -240 ] }, { "id": "62cc5b11-2887-4417-81c1-7fae3026f9dc", "name": "Code in JavaScript4", "type": "code", "params": { "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nfor (const item of $input.all()) {\n item.json.myNewField = 'Fraud Alert';\n}\n\nreturn $input.all();" }, "position": [ 400, -48 ] }, { "id": "d57bf31f-b6f3-442b-b3ea-6de3553f79a3", "name": "Code in JavaScript5", "type": "code", "params": { "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nfor (const item of $input.all()) {\n item.json.myNewField = 'General';\n}\n\nreturn $input.all();" }, "position": [ 400, 144 ] }, { "id": "ff05d10c-ff67-418b-8b0c-fe31473e5ee5", "name": "No Operation, do nothing", "type": "passthrough", "params": { "mode": "noOp" }, "position": [ 2352, -256 ] }, { "id": "c66b99ac-24f0-47e4-bf7e-bb9055c1438f", "name": "Gmail Trigger", "type": "trigger", "params": { "kind": "n8n-nodes-base.gmailTrigger", "filters": {}, "pollTimes": { "item": [ { "mode": "everyMinute" } ] } }, "position": [ -848, -240 ] } ] }

Classify Gmail support emails with AI and store tickets in PostgreSQL