Classify Gmail Leads with Groq AI and Sync Sla Cases to Supabase via Redis

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "5c85d895-53b5-44d4-87dc-a93dab314752",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -720,
        -1312
      ],
      "parameters": {
        "width": 480,
        "height": 704,
        "content": "## email sort on SLA (supabase)\n\n### How it works\n\n1. Triggers initiate SLA workflows from Gmail and setup routines.\n2. Parses and routes incoming emails based on SLA.\n3. Executes a series of SQL queries to set up necessary SLA tables and functions in the database.\n4. Logs SLA cases to Supabase for record-keeping.\n\n### Setup steps\n\n- [ ] Ensure Redis is configured for Gmail flow trigger.\n- [ ] Set up PostgreSQL database credentials for SLA setup queries.\n- [ ] Ensure Supabase credentials are available for logging SLA cases.\n\n### Customization\n\nNodes like 'Route Emails by SLA' may require customization based on specific SLA criteria."
      },
      "typeVersion": 1
    },
    {
      "id": "97f224aa-f833-4ad4-85dd-e4e0f1de48be",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -160,
        -1312
      ],
      "parameters": {
        "color": 7,
        "height": 640,
        "content": "## Trigger from Gmail or manual\n\nIncludes nodes that initiate workflows from Gmail flow and manual SLA setup."
      },
      "typeVersion": 1
    },
    {
      "id": "8cba2f4a-0c09-49f2-8091-438d72f12b47",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        112,
        -1008
      ],
      "parameters": {
        "color": 7,
        "width": 416,
        "height": 400,
        "content": "## Parse and route emails\n\nParses email data and routes them based on SLA determinations."
      },
      "typeVersion": 1
    },
    {
      "id": "44baf733-07ce-42f1-b806-b16df2f37cb0",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        128,
        -1312
      ],
      "parameters": {
        "color": 7,
        "width": 672,
        "height": 272,
        "content": "## SLA database setup\n\nExecutes SQL commands to configure SLA-related tables, views, and functions in the database."
      },
      "typeVersion": 1
    },
    {
      "id": "ad52773d-a085-4697-b224-07ca07c5cac2",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        560,
        -992
      ],
      "parameters": {
        "color": 7,
        "height": 320,
        "content": "## Log SLA cases\n\nLogs SLA cases into the Supabase database after being routed."
      },
      "typeVersion": 1
    },
    {
      "id": "bea0fcca-039b-4fcb-a007-c54c924acffe",
      "name": "Gmail Redis Trigger",
      "type": "n8n-nodes-base.redisTrigger",
      "position": [
        -112,
        -832
      ],
      "parameters": {
        "options": {},
        "channels": "sla"
      },
      "credentials": {
        "redis": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "182b81a5-04ee-4ed5-ba95-e0932bf81889",
      "name": "Manual SLA Trigger",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -96,
        -1120
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "cbdc2fb7-8433-43a7-911d-5b21c2508c78",
      "name": "Direct Emails by SLA Status",
      "type": "n8n-nodes-base.switch",
      "position": [
        384,
        -880
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "3156237d-5f05-4197-bbc0-2469c4e862c6",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.message.sla_status }}",
                    "rightValue": "RED"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "d355986d-cdd7-441c-b3c2-71a3f2951920",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.message.sla_status }}",
                    "rightValue": "ORANGE"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "d62b26f2-048b-459c-a0cb-f80170947dfc",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.message.sla_status }}",
                    "rightValue": "YELLOW"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "1ac76077-2caf-4a41-9b2c-c6875be95ea4",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.message.sla_status }}",
                    "rightValue": "GREEN"
                  }
                ]
              }
            }
          ]
        },
        "options": {
          "fallbackOutput": "extra"
        }
      },
      "typeVersion": 3.2
    },
    {
      "id": "fc56d0bd-8df3-45f5-b471-4914fb8f7a4c",
      "name": "Log to Supabase",
      "type": "n8n-nodes-base.postgres",
      "position": [
        608,
        -832
      ],
      "parameters": {
        "query": "{{ (() => {\n  const msg = typeof $json.message === 'string'\n    ? JSON.parse($json.message.trim().replace(/,\\s*$/, ''))\n    : ($json.message ?? $json);\n\n  const execution = $json.execution ?? {};\n  const workflow = $json.workflow ?? {};\n\n  const payload = {\n    ...msg,\n\n    source: 'n8n',\n\n    n8n_execution_id: String(execution.id ?? $execution.id ?? ''),\n    n8n_execution_mode: execution.mode ?? '',\n    n8n_resume_url: execution.resumeUrl ?? '',\n    n8n_resume_form_url: execution.resumeFormUrl ?? '',\n\n    n8n_workflow_id: String(workflow.id ?? $workflow.id ?? ''),\n    n8n_workflow_name: workflow.name ?? $workflow.name ?? '',\n    n8n_workflow_active: workflow.active ?? null,\n\n    n8n_node_name: 'Log SLA Case to Supabase'\n  };\n\n  // Keep real SLA states: RED / ORANGE / YELLOW / GREEN\n  const currentSla = String(payload.sla_status || '').toUpperCase();\n\n  if (currentSla) {\n    payload.sla_status = currentSla;\n  }\n\n  // Normalize SLA labels, but do NOT convert ORANGE to YELLOW\n  if (Array.isArray(payload.labels)) {\n    payload.labels = payload.labels.map(label => {\n      const text = String(label || '');\n\n      if (text.toLowerCase() === 'sla:red') return 'sla:RED';\n      if (text.toLowerCase() === 'sla:orange') return 'sla:ORANGE';\n      if (text.toLowerCase() === 'sla:yellow') return 'sla:YELLOW';\n      if (text.toLowerCase() === 'sla:green') return 'sla:GREEN';\n\n      return label;\n    });\n  }\n\n  const safePayload = JSON.stringify(payload).replace(/'/g, \"''\");\n\n  return `SELECT public.process_email_sla_case('${safePayload}'::jsonb) AS result;`;\n})() }}",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.5
    },
    {
      "id": "cfe592dd-536f-44be-8216-88d54f7380e6",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2096,
        -2448
      ],
      "parameters": {
        "color": 7,
        "width": 544,
        "height": 880,
        "content": "## Secondary workflow operations\n\nTriggers secondary workflows or logs tasks based on email SLA routing."
      },
      "typeVersion": 1
    },
    {
      "id": "6aeda039-aae2-4302-bba1-9899a776ff86",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2688,
        -2192
      ],
      "parameters": {
        "color": 7,
        "width": 928,
        "height": 496,
        "content": "## Mem0 API integration\n\nBuilds payload and posts data to Mem0 API, then waits for completion."
      },
      "typeVersion": 1
    },
    {
      "id": "69bd59bf-30c8-423c-8dd4-f7bec01c20b4",
      "name": "Post to Mem0 API",
      "type": "n8n-nodes-base.httpRequest",
      "onError": "continueErrorOutput",
      "position": [
        3216,
        -2080
      ],
      "parameters": {
        "url": "https://api.mem0.ai/v1/memories/",
        "method": "POST",
        "options": {},
        "jsonBody": "={{ $json }}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "4e1f1d19-a7f2-4611-80dc-a4821b599a67",
      "name": "Pre-Filter Gmail by Labels",
      "type": "n8n-nodes-base.code",
      "position": [
        160,
        -1856
      ],
      "parameters": {
        "jsCode": "// Gmail Pre-Filter: skip emails with blocked labels\n// Add or remove labels from this list to customize\n\nconst items = $input.all();\nconst results = [];\n\nconst blockedLabels = [\n  'SENT',\n  'DRAFT',\n  'SPAM',\n  'TRASH',\n  'CATEGORY_PROMOTIONS',\n  'CATEGORY_SOCIAL',\n  'CATEGORY_UPDATES',\n  'CATEGORY_FORUMS',\n  'Marketing'\n];\n\nfor (const item of items) {\n  const labels = (item.json.labels || []).map(label => {\n    if (typeof label === 'string') return label;\n    return label.name || label.id || '';\n  }).filter(Boolean);\n\n  const hasBlockedLabel = labels.some(l => blockedLabels.includes(l));\n\n  if (!hasBlockedLabel) {\n    results.push(item);\n  }\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "1c5dba29-d984-40a1-b8a6-ebb53c278b48",
      "name": "Retrieve Gmail Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        608,
        -2032
      ],
      "parameters": {
        "simple": false,
        "options": {
          "downloadAttachments": false
        },
        "messageId": "={{ $json.id }}",
        "operation": "get"
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "c2ee5513-a5fd-4969-a0ee-53fd7fd13677",
      "name": "Extract Email Info for Classification",
      "type": "n8n-nodes-base.code",
      "position": [
        832,
        -2032
      ],
      "parameters": {
        "jsCode": "const MAX_BODY_CHARS = 3000;\nconst MAX_RAW_DECODE_CHARS = 800000; // safety cap before cleaning large emails\n\nconst items = $input.all();\n\nfunction toText(value, fallback = '') {\n  if (value === null || value === undefined) return fallback;\n  return typeof value === 'string' ? value : String(value);\n}\n\nfunction getHeader(headers, name) {\n  if (!Array.isArray(headers)) return '';\n  const found = headers.find(h => String(h.name || '').toLowerCase() === name.toLowerCase());\n  return found?.value || '';\n}\n\nfunction parseEmailAddress(raw) {\n  if (!raw) {\n    return {\n      raw: '',\n      email: 'unknown',\n      name: ''\n    };\n  }\n\n  if (typeof raw === 'object') {\n    if (Array.isArray(raw.value) && raw.value[0]) {\n      const address = raw.value[0].address || 'unknown';\n      const name = raw.value[0].name || '';\n\n      return {\n        raw: name ? `${name} <${address}>` : address,\n        email: address,\n        name\n      };\n    }\n\n    if (raw.address) {\n      const address = raw.address || 'unknown';\n      const name = raw.name || '';\n\n      return {\n        raw: name ? `${name} <${address}>` : address,\n        email: address,\n        name\n      };\n    }\n\n    return {\n      raw: JSON.stringify(raw),\n      email: 'unknown',\n      name: ''\n    };\n  }\n\n  const text = String(raw).trim();\n\n  const angleMatch = text.match(/<([^>]+)>/);\n  if (angleMatch) {\n    const email = angleMatch[1].trim();\n    const name = text.replace(/<[^>]+>/, '').replace(/^\"|\"$/g, '').trim();\n\n    return {\n      raw: text,\n      email,\n      name\n    };\n  }\n\n  const emailMatch = text.match(/[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,}/i);\n  if (emailMatch) {\n    return {\n      raw: text,\n      email: emailMatch[0].trim(),\n      name: ''\n    };\n  }\n\n  return {\n    raw: text,\n    email: text || 'unknown',\n    name: ''\n  };\n}\n\nfunction formatAddressField(value) {\n  if (value === null || value === undefined || value === '') return '';\n\n  if (typeof value === 'string') return value;\n\n  if (Array.isArray(value)) {\n    return value\n      .map(formatAddressField)\n      .filter(Boolean)\n      .join(', ');\n  }\n\n  if (typeof value === 'object') {\n    if (Array.isArray(value.value)) {\n      return value.value\n        .map(v => {\n          const address = v?.address || '';\n          const name = v?.name || '';\n          return name && address ? `${name} <${address}>` : address || name;\n        })\n        .filter(Boolean)\n        .join(', ');\n    }\n\n    if (Array.isArray(value.addresses)) {\n      return value.addresses\n        .map(formatAddressField)\n        .filter(Boolean)\n        .join(', ');\n    }\n\n    if (value.address) {\n      return value.name\n        ? `${value.name} <${value.address}>`\n        : value.address;\n    }\n\n    if (value.email) {\n      return value.name\n        ? `${value.name} <${value.email}>`\n        : value.email;\n    }\n\n    try {\n      return JSON.stringify(value);\n    } catch (error) {\n      return '';\n    }\n  }\n\n  return String(value);\n}\n\nfunction decodeBase64Url(data) {\n  if (!data || typeof data !== 'string') return '';\n\n  try {\n    let normalized = data.replace(/-/g, '+').replace(/_/g, '/');\n\n    while (normalized.length % 4) {\n      normalized += '=';\n    }\n\n    return Buffer.from(normalized, 'base64').toString('utf8');\n  } catch (error) {\n    return '';\n  }\n}\n\nfunction stripHtml(html) {\n  if (!html) return '';\n\n  return String(html)\n    .replace(/<script[\\s\\S]*?<\\/script>/gi, ' ')\n    .replace(/<style[\\s\\S]*?<\\/style>/gi, ' ')\n    .replace(/<br\\s*\\/?>/gi, '\\n')\n    .replace(/<\\/p>/gi, '\\n')\n    .replace(/<\\/div>/gi, '\\n')\n    .replace(/<\\/li>/gi, '\\n')\n    .replace(/<[^>]*>/g, ' ')\n    .replace(/&nbsp;/gi, ' ')\n    .replace(/&amp;/gi, '&')\n    .replace(/&lt;/gi, '<')\n    .replace(/&gt;/gi, '>')\n    .replace(/&quot;/gi, '\"')\n    .replace(/&#39;/gi, \"'\")\n    .replace(/\\s+\\n/g, '\\n')\n    .replace(/\\n\\s+/g, '\\n')\n    .replace(/[ \\t]{2,}/g, ' ')\n    .replace(/\\n{3,}/g, '\\n\\n')\n    .trim();\n}\n\nfunction walkParts(part, result = {\n  textPlain: [],\n  textHtml: [],\n  attachments: [],\n  mimeTypes: []\n}) {\n  if (!part || typeof part !== 'object') return result;\n\n  const mimeType = String(part.mimeType || '').toLowerCase();\n  const filename = part.filename || '';\n\n  if (mimeType) {\n    result.mimeTypes.push(mimeType);\n  }\n\n  if (filename) {\n    result.attachments.push({\n      filename,\n      mimeType: part.mimeType || '',\n      attachmentId: part.body?.attachmentId || '',\n      size: part.body?.size || 0\n    });\n  }\n\n  const bodyData = part.body?.data;\n  if (bodyData) {\n    const decoded = decodeBase64Url(bodyData);\n\n    if (decoded) {\n      if (mimeType === 'text/plain') {\n        result.textPlain.push(decoded);\n      } else if (mimeType === 'text/html') {\n        result.textHtml.push(decoded);\n      }\n    }\n  }\n\n  if (Array.isArray(part.parts)) {\n    for (const child of part.parts) {\n      walkParts(child, result);\n    }\n  }\n\n  return result;\n}\n\nfunction cleanEmailBody(body) {\n  let text = toText(body, '');\n\n  if (text.length > MAX_RAW_DECODE_CHARS) {\n    text = text.substring(0, MAX_RAW_DECODE_CHARS);\n  }\n\n  text = text\n    .replace(/https?:\\/\\/[^\\s]+/g, '[link]')\n    .replace(/www\\.[^\\s]+/g, '[link]')\n    .replace(/\\r\\n/g, '\\n')\n    .replace(/\\r/g, '\\n')\n    .replace(/[ \\t]{2,}/g, ' ')\n    .replace(/\\n{3,}/g, '\\n\\n')\n    .trim();\n\n  const originalLength = text.length;\n  let truncated = false;\n\n  if (text.length > MAX_BODY_CHARS) {\n    text = text.substring(0, MAX_BODY_CHARS) + '\\n[truncated]';\n    truncated = true;\n  }\n\n  return {\n    body: text,\n    originalLength,\n    truncated\n  };\n}\n\nfunction parseLabels(email) {\n  const labels = [];\n\n  if (Array.isArray(email.labels)) {\n    for (const label of email.labels) {\n      if (typeof label === 'string') labels.push(label);\n      else if (label?.name) labels.push(label.name);\n      else if (label?.id) labels.push(label.id);\n    }\n  }\n\n  if (Array.isArray(email.labelIds)) {\n    for (const id of email.labelIds) {\n      if (!labels.includes(id)) labels.push(id);\n    }\n  }\n\n  return labels;\n}\n\nfunction parseInternalDate(email) {\n  if (email.internalDate) {\n    const date = new Date(Number(email.internalDate));\n    if (!Number.isNaN(date.getTime())) return date.toISOString();\n  }\n\n  const dateHeader = getHeader(email.payload?.headers, 'Date') || email.Date || email.date;\n  if (dateHeader) {\n    const date = new Date(dateHeader);\n    if (!Number.isNaN(date.getTime())) return date.toISOString();\n  }\n\n  return new Date().toISOString();\n}\n\nreturn items.map(item => {\n  const email = item.json || {};\n  const headers = email.payload?.headers || email.headers || [];\n\n  const parseWarnings = [];\n\n  // Headers / basic fields\n  const fromRaw =\n    email.From ||\n    email.from ||\n    getHeader(headers, 'From') ||\n    '';\n\n  const parsedFrom = parseEmailAddress(fromRaw);\n\n  const toField =\n    email.To ||\n    email.to ||\n    getHeader(headers, 'To') ||\n    '';\n\n  const ccField =\n    email.Cc ||\n    email.cc ||\n    getHeader(headers, 'Cc') ||\n    '';\n\n  const subject =\n    email.Subject ||\n    email.subject ||\n    email.headers?.subject ||\n    getHeader(headers, 'Subject') ||\n    '(no subject)';\n\n  // Body parsing\n  let body = '';\n\n  // n8n simplified / alternative fields\n  if (typeof email.textPlain === 'string' && email.textPlain.trim()) {\n    body = email.textPlain;\n  } else if (typeof email.text === 'string' && email.text.trim()) {\n    body = email.text;\n  }\n\n  // Gmail full payload recursive parse\n  const walked = walkParts(email.payload || {});\n\n  if (!body && walked.textPlain.length > 0) {\n    body = walked.textPlain.join('\\n\\n');\n  }\n\n  if (!body && walked.textHtml.length > 0) {\n    body = stripHtml(walked.textHtml.join('\\n\\n'));\n  }\n\n  // Direct payload body fallback\n  if (!body && email.payload?.body?.data) {\n    const decoded = decodeBase64Url(email.payload.body.data);\n\n    if (String(email.payload.mimeType || '').toLowerCase() === 'text/html') {\n      body = stripHtml(decoded);\n    } else {\n      body = decoded;\n    }\n  }\n\n  // HTML fallback from n8n simplified fields\n  if (!body && typeof email.textHtml === 'string' && email.textHtml.trim()) {\n    body = stripHtml(email.textHtml);\n  }\n\n  if (!body && typeof email.html === 'string' && email.html.trim()) {\n    body = stripHtml(email.html);\n  }\n\n  // Snippet fallback\n  if (!body && email.snippet) {\n    body = email.snippet;\n    parseWarnings.push('body_from_snippet_only');\n  }\n\n  // Final fallback: don't break workflow; classify based on subject/from if body missing\n  if (!body || !body.trim()) {\n    body = `[No readable body found]\\nSubject: ${subject}\\nFrom: ${parsedFrom.email}`;\n    parseWarnings.push('empty_body_fallback_used');\n  }\n\n  const cleaned = cleanEmailBody(body);\n\n  // Attachments\n  let attachments = [];\n\n  if (Array.isArray(email.attachments)) {\n    attachments = email.attachments.map(a => ({\n      filename: a.filename || a.fileName || '',\n      mimeType: a.mimeType || '',\n      attachmentId: a.attachmentId || a.id || '',\n      size: a.size || 0\n    }));\n  }\n\n  if (walked.attachments.length > 0) {\n    attachments = [...attachments, ...walked.attachments];\n  }\n\n  // Deduplicate attachments\n  const attachmentMap = new Map();\n  for (const attachment of attachments) {\n    const key = `${attachment.filename || ''}:${attachment.attachmentId || ''}:${attachment.size || ''}`;\n    attachmentMap.set(key, attachment);\n  }\n  attachments = Array.from(attachmentMap.values());\n\n  const gmailLabels = parseLabels(email);\n  const receivedAt = parseInternalDate(email);\n  const processedAt = new Date().toISOString();\n\n  const messageId = email.id || email.message_id || getHeader(headers, 'Message-ID') || '';\n  const threadId = email.threadId || email.thread_id || '';\n\n  const toClean = formatAddressField(toField);\n  const ccClean = formatAddressField(ccField);\n\n  return {\n    json: {\n      event_id: messageId,\n\n      sender_email: parsedFrom.email,\n      sender_name: parsedFrom.name,\n      raw_from: parsedFrom.raw,\n\n      to: toClean,\n      cc: ccClean,\n\n      subject: toText(subject, '(no subject)'),\n      body: cleaned.body,\n\n      message_id: messageId,\n      inbox_id: 'gmail',\n      thread_id: threadId,\n\n      existing_labels: gmailLabels,\n      thread_labels: gmailLabels,\n      thread_message_count: 1,\n\n      has_attachments: attachments.length > 0,\n      attachment_count: attachments.length,\n      attachment_names: attachments.map(a => a.filename).filter(Boolean),\n      attachments_metadata: attachments,\n\n      gmail_size_estimate: email.sizeEstimate || null,\n      mime_types_seen: Array.from(new Set(walked.mimeTypes)).slice(0, 30),\n\n      body_original_length: cleaned.originalLength,\n      body_truncated: cleaned.truncated,\n      parse_warnings: parseWarnings,\n\n      received_at: receivedAt,\n      processed_at: processedAt\n    }\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "6c08a8b2-d707-4ec8-a6ba-a868a46eb944",
      "name": "Batch Process Emails",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        384,
        -1856
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "d7228c65-f3d5-466e-b1f6-1e5c7027275f",
      "name": "Classify Email Content",
      "type": "@n8n/n8n-nodes-langchain.informationExtractor",
      "position": [
        1056,
        -2032
      ],
      "parameters": {
        "text": "=Analyze this incoming email for a HVAC contractor business.\n\nFrom: {{ $json.sender_email }}\nTo: {{ $json.to }}\nSubject: {{ $json.subject }}\nContent: {{ $json.body }}\n\nThread context: This is message {{ $json.thread_message_count }} in this thread. Has attachments: {{ $json.has_attachments }}.",
        "options": {
          "batching": {},
          "systemPromptTemplate": "You are the 'Headless Brain' of StuccoOS \u2014 an intelligent triage system for plastering contractors.\n\nMission: Prevent LEAD LEAKAGE and SLEEPING REVENUE loss.\n\nRules:\n1. When in doubt, classify MORE urgent.\n2. New quote requests = minimum priority 'high' + 'NEW_LEAD'.\n3. Complaints about completed work = 'AFTERCARE' + minimum 'high'.\n4. Unknown specs = fill 'unknown', never leave empty.\n5. Words like 'urgent','ASAP','damage','leak','spoed','lekkage','schade','direct' = ALWAYS 'urgent'.\n6. helen_action 'true' for everything except pure admin/spam.\n7. SLEEPING_REVENUE = most valuable, always minimum 'high'.\n8. Spam/auto-replies = is_noise 'true'.\n9. sla_status determines entire downstream workflow. Be precise.\n10. Dual-intent messages = classify as MORE urgent.\n11. Works in Dutch and English.\n12. Only return values from allowed options."
        },
        "attributes": {
          "attributes": [
            {
              "name": "sentiment",
              "required": true,
              "description": "The emotional tone of the incoming message. Must be exactly one of: positive | neutral | negative. A message expressing frustration, anger, or disappointment is 'negative'. A message expressing gratitude, excitement, or satisfaction is 'positive'. Everything else is 'neutral'."
            },
            {
              "name": "category",
              "required": true,
              "description": "The communication type. Must be exactly one of: quote-request | follow-up | complaint | scheduling | invoice-question | praise | general-question | spam."
            },
            {
              "name": "stucco_priority",
              "required": true,
              "description": "SLA classification for the 48h Guard system. Must be exactly one of: urgent | high | normal | low. 'urgent' = immediate action needed: damage, leaks, complaints, legal threats, words like ASAP/spoed/lekkage/schade. 'high' = new lead, quote acceptance, time-sensitive. 'normal' = general admin, planning, invoices. 'low' = praise, spam, newsletters."
            },
            {
              "name": "inquiry_category",
              "required": true,
              "description": "StuccoOS pipeline stage. Must be exactly one of: NEW_LEAD | SLEEPING_REVENUE | ADMIN_FLOW | AFTERCARE. 'NEW_LEAD' = first-time inquiry. 'SLEEPING_REVENUE' = response to existing quote or follow-up (most revenue-critical). 'ADMIN_FLOW' = operational: invoices, scheduling. 'AFTERCARE' = post-completion: warranty, complaints, reviews."
            },
            {
              "name": "department",
              "required": true,
              "description": "Where to route. Must be exactly one of: sales | planning | billing | aftercare."
            },
            {
              "name": "technical_specs",
              "required": true,
              "description": "Extract technical details. Format: 'surface:[concrete|plasterboard|masonry|wood|unknown], finish:[stucco|plaster|decorative|spachtelputz|venetian|beton-cire|unknown], sqm:[number or unknown], rooms:[number or unknown]'. If nothing found: 'surface:unknown, finish:unknown, sqm:unknown, rooms:unknown'."
            },
            {
              "name": "property_type",
              "required": true,
              "description": "Must be exactly one of: existing_build | new_build | commercial | unknown."
            },
            {
              "name": "helen_action",
              "required": true,
              "description": "Should AI draft a response? Must be exactly one of: true | false. 'true' if emotional, new lead, sleeping revenue, or negative. 'false' only for pure admin or spam."
            },
            {
              "name": "sla_status",
              "required": true,
              "description": "Escalation color. Must be exactly one of: RED | ORANGE | YELLOW | GREEN. RED = negative+urgent, complaint+urgent, damage, legal. ORANGE = negative+high, urgent+non-negative, time-critical sleeping revenue. YELLOW = high+neutral, normal+negative, any new lead. GREEN = positive/neutral + normal/low."
            },
            {
              "name": "owner_alert",
              "required": true,
              "description": "Must be exactly one of: true | false. 'true' ONLY if sla_status RED, legal action, physical damage, or project >200sqm."
            },
            {
              "name": "response_deadline",
              "required": true,
              "description": "Must be exactly one of: 1h | 4h | 24h | 48h. RED=1h, ORANGE=4h, YELLOW=24h, GREEN=48h."
            },
            {
              "name": "is_noise",
              "required": true,
              "description": "Channel Contamination filter. Must be exactly one of: true | false. 'true' for auto-replies, spam, newsletters, system notifications. 'false' for all real client communication."
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "e0c70d67-925d-4878-b2b1-4d0b60bd2545",
      "name": "Filter Spam Emails",
      "type": "n8n-nodes-base.filter",
      "position": [
        1440,
        -2032
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "noise-filter",
              "operator": {
                "type": "string",
                "operation": "notEquals"
              },
              "leftValue": "={{ $json.output.is_noise }}",
              "rightValue": "true"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "1d30201b-d327-488f-a681-78972a025b86",
      "name": "Integrate Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatGroq",
      "position": [
        1136,
        -1808
      ],
      "parameters": {
        "model": "openai/gpt-oss-120b",
        "options": {}
      },
      "credentials": {
        "groqApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "87ea8afe-2613-4205-8670-83e3ba4f43d2",
      "name": "When Gmail Received",
      "type": "n8n-nodes-base.gmailTrigger",
      "position": [
        -112,
        -1856
      ],
      "parameters": {
        "filters": {},
        "pollTimes": {
          "item": [
            {
              "mode": "everyHour",
              "minute": 10
            }
          ]
        }
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "60303026-4451-410b-aaca-b7fb3478e897",
      "name": "Wait 12 Seconds",
      "type": "n8n-nodes-base.wait",
      "position": [
        3472,
        -1856
      ],
      "parameters": {
        "amount": 12
      },
      "typeVersion": 1.1
    },
    {
      "id": "6f20df1c-758a-42dc-b018-5388960e064e",
      "name": "Generate Email Labels",
      "type": "n8n-nodes-base.code",
      "position": [
        1664,
        -2032
      ],
      "parameters": {
        "jsCode": "// Build 4 lean labels + merge all data for downstream\n\nconst raw = $input.first().json;\nconst triage = raw.output || raw;\nconst labels = [];\n\n// === 4 LABELS ===\nif (triage.sla_status) labels.push(`sla:${triage.sla_status}`);\nif (triage.inquiry_category) labels.push(`pipeline:${triage.inquiry_category}`);\nif (triage.stucco_priority) labels.push(`priority:${triage.stucco_priority}`);\nif (triage.sentiment) labels.push(`sentiment:${triage.sentiment}`);\n\n// === GET EMAIL DATA (loop-safe) ===\nlet emailData;\ntry {\n  emailData = $('Extract Email Info for Classification').item.json;\n} catch (e) {\n  emailData = $('Extract Email Info for Classification').first().json;\n}\n\nreturn [{\n  json: {\n    labels: labels,\n    message_id: emailData.message_id,\n    inbox_id: emailData.inbox_id,\n    thread_id: emailData.thread_id,\n    event_id: emailData.event_id,\n    sla_status: triage.sla_status,\n    owner_alert: triage.owner_alert,\n    helen_action: triage.helen_action,\n    response_deadline: triage.response_deadline,\n    stucco_priority: triage.stucco_priority,\n    inquiry_category: triage.inquiry_category,\n    sentiment: triage.sentiment,\n    category: triage.category,\n    department: triage.department,\n    property_type: triage.property_type,\n    technical_specs: triage.technical_specs,\n    sender_email: emailData.sender_email,\n    subject: emailData.subject,\n    body: emailData.body,\n    received_at: emailData.received_at,\n    processed_at: new Date().toISOString()\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "12418e00-155e-445e-b6ba-f9b934077544",
      "name": "Create Mem0 API Payload",
      "type": "n8n-nodes-base.code",
      "position": [
        2736,
        -2080
      ],
      "parameters": {
        "jsCode": "// Build Mem0 payload directly from triage data\n// No CRM mapping needed \u2014 all context goes to memory\n\nconst items = $input.all();\nconst results = [];\n\nfor (const item of items) {\n  const data = item.json;\n\n  // === SAFE sender extraction ===\n  let senderEmail = 'unknown';\n  const rawFrom = data.raw_from || data.sender_email || '';\n\n  if (typeof rawFrom === 'string' && rawFrom.length > 0) {\n    if (rawFrom.includes('<') && rawFrom.includes('>')) {\n      senderEmail = rawFrom.split('<')[1].split('>')[0].trim();\n    } else {\n      senderEmail = rawFrom.trim();\n    }\n  } else if (typeof rawFrom === 'object' && rawFrom !== null) {\n    // Gmail object format: {value: [{address: '...', name: '...'}]}\n    if (rawFrom.value && rawFrom.value[0]) {\n      senderEmail = rawFrom.value[0].address || 'unknown';\n    } else if (rawFrom.address) {\n      senderEmail = rawFrom.address;\n    }\n  }\n\n  // === SAFE sender name ===\n  let contactName = senderEmail;\n  if (typeof rawFrom === 'string' && rawFrom.includes('<')) {\n    contactName = rawFrom.split('<')[0].trim() || senderEmail;\n  } else if (typeof rawFrom === 'object' && rawFrom?.value?.[0]?.name) {\n    contactName = rawFrom.value[0].name;\n  }\n\n  // === BUILD MEMORY CONTENT ===\n  const memoryContent = [\n    `Subject: ${data.subject || '(no subject)'}`,\n    `From: ${contactName} <${senderEmail}>`,\n    `SLA: ${data.sla_status || 'unknown'}`,\n    `Priority: ${data.stucco_priority || 'unknown'}`,\n    `Pipeline: ${data.inquiry_category || 'unknown'}`,\n    `Sentiment: ${data.sentiment || 'unknown'}`,\n    `Category: ${data.category || 'unknown'}`,\n    `Department: ${data.department || 'unknown'}`,\n    `Specs: ${data.technical_specs || 'unknown'}`,\n    `Property: ${data.property_type || 'unknown'}`,\n    `Deadline: ${data.response_deadline || 'unknown'}`,\n    '',\n    data.body || '(no body)'\n  ].join('\\n');\n\n  // === BUILD LABELS STRING ===\n  let labelsStr = '';\n  if (Array.isArray(data.labels)) {\n    labelsStr = data.labels.join(', ');\n  } else if (typeof data.labels === 'string') {\n    labelsStr = data.labels;\n  }\n\n  // === MEM0 PAYLOAD ===\n  results.push({\n    json: {\n      user_id: senderEmail,\n      agent_id: 'stuccoos_triage',\n      app_id: 'stuccoos_gmail',\n      run_id: data.thread_id || data.message_id || '',\n      messages: [\n        {\n          role: 'user',\n          content: memoryContent\n        }\n      ],\n      metadata: {\n        email_id: data.message_id || '',\n        thread_id: data.thread_id || '',\n        subject: data.subject || '',\n        sla_status: data.sla_status || '',\n        priority: data.stucco_priority || '',\n        pipeline: data.inquiry_category || '',\n        sentiment: data.sentiment || '',\n        category: data.category || '',\n        department: data.department || '',\n        property_type: data.property_type || '',\n        technical_specs: data.technical_specs || '',\n        response_deadline: data.response_deadline || '',\n        owner_alert: data.owner_alert || 'false',\n        helen_action: data.helen_action || 'false',\n        labels: labelsStr,\n        contact_name: contactName,\n        source: 'gmail_triage',\n        date: data.received_at || new Date().toISOString()\n      },\n      infer: true,\n      output_format: 'v1.1'\n    }\n  });\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "e9d26a90-ccee-4784-b68a-d84c25bf6d99",
      "name": "Route Emails Based on SLA",
      "type": "n8n-nodes-base.switch",
      "position": [
        1920,
        -2080
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "3156237d-5f05-4197-bbc0-2469c4e862c6",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ String($json.sla_status || '').toUpperCase() }}",
                    "rightValue": "RED"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "d355986d-cdd7-441c-b3c2-71a3f2951920",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ String($json.sla_status || '').toUpperCase() }}",
                    "rightValue": "ORANGE"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "d62b26f2-048b-459c-a0cb-f80170947dfc",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ String($json.sla_status || '').toUpperCase() }}",
                    "rightValue": "YELLOW"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "1ac76077-2caf-4a41-9b2c-c6875be95ea4",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ String($json.sla_status || '').toUpperCase() }}",
                    "rightValue": "GREEN"
                  }
                ]
              }
            }
          ]
        },
        "options": {
          "fallbackOutput": "extra"
        }
      },
      "typeVersion": 3.2
    },
    {
      "id": "169f6cdf-6c8e-4d2a-aeb4-9372b8be1fd3",
      "name": "Notify Owner Immediately",
      "type": "n8n-nodes-base.code",
      "position": [
        2144,
        -2320
      ],
      "parameters": {
        "jsCode": "const toText = (value, fallback = '') => {\n  if (value === null || value === undefined) return fallback;\n  return typeof value === 'string' ? value : String(value);\n};\n\nconst clip = (value, max = 200) => {\n  const text = toText(value, '');\n  return text.length > max ? `${text.substring(0, max)}...` : text;\n};\n\nreturn $input.all().map(item => {\n  const data = item.json || {};\n\n  const alertMessage = [\n    '\ud83d\udd34 RED ALERT \u2014 StuccoOS',\n    '\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501',\n    '',\n    `\ud83d\udce7 From: ${toText(data.sender_email, 'Unknown')}`,\n    `\ud83d\udccb Subject: ${toText(data.subject, 'No Subject')}`,\n    `\u23f0 Respond within: ${toText(data.response_deadline, 'ASAP')}`,\n    `\ud83c\udfd7\ufe0f Pipeline: ${toText(data.inquiry_category, 'Unknown')}`,\n    `\ud83d\udea8 Priority: ${toText(data.stucco_priority, 'urgent')}`,\n    `\ud83d\udcac Sentiment: ${toText(data.sentiment, 'Unknown')}`,\n    `\ud83d\udcc1 Category: ${toText(data.category, 'Unknown')}`,\n    '',\n    `\ud83d\udcdd ${clip(data.body, 200)}`,\n    '',\n    '\u26a0\ufe0f IMMEDIATE owner attention required.',\n    `\ud83d\udd17 Thread: ${toText(data.thread_id, 'Unknown')}`\n  ].join('\\n');\n\n  return {\n    json: {\n      ...data,\n      alert_message: alertMessage,\n      alert_type: 'RED',\n      action_type: 'owner_notification',\n      action_status: 'prepared',\n      channel: 'owner-direct',\n      action_created_at: new Date().toISOString()\n    }\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "46d0da83-397e-44cc-80c7-7413e42cd645",
      "name": "Create Urgent Task",
      "type": "n8n-nodes-base.code",
      "position": [
        2144,
        -2128
      ],
      "parameters": {
        "jsCode": "const toText = (value, fallback = '') => {\n  if (value === null || value === undefined) return fallback;\n  return typeof value === 'string' ? value : String(value);\n};\n\nconst clip = (value, max = 300) => {\n  const text = toText(value, '');\n  return text.length > max ? `${text.substring(0, max)}...` : text;\n};\n\nconst dueInHours = (hours) => {\n  return new Date(Date.now() + hours * 60 * 60 * 1000).toISOString();\n};\n\nreturn $input.all().map(item => {\n  const data = item.json || {};\n\n  return {\n    json: {\n      ...data,\n      task_title: `\ud83d\udfe0 [${toText(data.inquiry_category, 'NEW_LEAD')}] ${toText(data.subject, 'No Subject')}`,\n      task_description: [\n        `From: ${toText(data.sender_email, 'Unknown')}`,\n        `Deadline: ${toText(data.response_deadline, '4h')}`,\n        `Sentiment: ${toText(data.sentiment, 'Unknown')}`,\n        `Category: ${toText(data.category, 'Unknown')}`,\n        `Helen Draft: ${toText(data.helen_action, 'false')}`,\n        '',\n        clip(data.body, 300)\n      ].join('\\n'),\n      task_priority: 'high',\n      task_due: dueInHours(4),\n      action_type: 'urgent_task',\n      action_status: 'prepared',\n      action_created_at: new Date().toISOString()\n    }\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "8c27fbb2-8573-4fb6-a476-dc419497aec6",
      "name": "Create Standard Task",
      "type": "n8n-nodes-base.code",
      "position": [
        2144,
        -1936
      ],
      "parameters": {
        "jsCode": "const toText = (value, fallback = '') => {\n  if (value === null || value === undefined) return fallback;\n  return typeof value === 'string' ? value : String(value);\n};\n\nconst clip = (value, max = 300) => {\n  const text = toText(value, '');\n  return text.length > max ? `${text.substring(0, max)}...` : text;\n};\n\nconst parseDeadlineHours = (value, fallback = 24) => {\n  const match = toText(value, '').match(/(\\d+)/);\n  return match ? Number(match[1]) : fallback;\n};\n\nconst dueFromReceivedAt = (receivedAt, hours) => {\n  const base = receivedAt ? new Date(receivedAt) : new Date();\n  const safeBase = Number.isNaN(base.getTime()) ? new Date() : base;\n  return new Date(safeBase.getTime() + hours * 60 * 60 * 1000).toISOString();\n};\n\nreturn $input.all().map(item => {\n  const data = item.json || {};\n  const hours = parseDeadlineHours(data.response_deadline, 24);\n\n  return {\n    json: {\n      ...data,\n      task_title: `\ud83d\udfe1 [${toText(data.inquiry_category, 'UNASSIGNED')}] ${toText(data.subject, 'No Subject')}`,\n      task_description: [\n        `From: ${toText(data.sender_email, 'Unknown')}`,\n        `Deadline: ${toText(data.response_deadline, `${hours}h`)}`,\n        `Sentiment: ${toText(data.sentiment, 'Unknown')}`,\n        `Category: ${toText(data.category, 'Unknown')}`,\n        `Helen Draft: ${toText(data.helen_action, 'false')}`,\n        '',\n        clip(data.body, 300)\n      ].join('\\n'),\n      task_priority: 'normal',\n      task_due: data.task_due || dueFromReceivedAt(data.received_at, hours),\n      action_type: 'standard_task',\n      action_status: 'prepared',\n      action_created_at: new Date().toISOString()\n    }\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "02ef3f03-4843-4f6a-b6f3-3b9d12b2084e",
      "name": "Delay and Log Email 48h",
      "type": "n8n-nodes-base.code",
      "position": [
        2144,
        -1744
      ],
      "parameters": {
        "jsCode": "const toText = (value, fallback = '') => {\n  if (value === null || value === undefined) return fallback;\n  return typeof value === 'string' ? value : String(value);\n};\n\nconst nextCheckInHours = (hours) => {\n  return new Date(Date.now() + hours * 60 * 60 * 1000).toISOString();\n};\n\nreturn $input.all().map(item => {\n  const data = item.json || {};\n  const labels = Array.isArray(data.labels) ? data.labels : [];\n\n  return {\n    json: {\n      ...data,\n      status: 'logged',\n      note: `No immediate action needed. ${labels.length} labels applied. 48h timer started.`,\n      next_check: nextCheckInHours(48),\n      action_type: 'delay_48h',\n      action_status: 'logged',\n      action_created_at: new Date().toISOString(),\n      sender_email: toText(data.sender_email, 'Unknown'),\n      subject: toText(data.subject, 'No Subject')\n    }\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "17f4220a-9dae-4525-9891-4582e6611589",
      "name": "Trigger Secondary Workflow",
      "type": "n8n-nodes-base.redis",
      "position": [
        2496,
        -2080
      ],
      "parameters": {
        "channel": "log_draft_email",
        "operation": "publish",
        "messageData": "={{ JSON.stringify($json)}}"
      },
      "credentials": {
        "redis": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "374ae7be-ef0a-4026-9345-e4d7c3f08c35",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "disabled": true,
      "position": [
        -720,
        -2448
      ],
      "parameters": {
        "width": 480,
        "height": 880,
        "content": "## gmail sort on SLA\n\n### How it works\n\n1. Triggers on new Gmail inbox events and filters by labels.\n2. Processes emails in batches and retrieves full email content.\n3. Analyzes email content for classification and filters out spam.\n4. Builds and applies email labels based on content.\n5. Routes emails according to Service Level Agreements (SLAs).\n6. Executes secondary workflows or logs tasks based on routing outcomes.\n\n### Setup steps\n\n- [ ] Set up Gmail credentials with access to relevant labels.\n- [ ] Configure Mem0 API credentials for posting data.\n- [ ] Ensure proper Redis server settings for triggering secondary workflows.\n\n### Customization\n\nYou can adjust the filters to include or exclude specific email labels to refine the processing logic."
      },
      "typeVersion": 1
    },
    {
      "id": "52df10f8-7c5e-4cd8-98d9-df5ab3d5dfc1",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -160,
        -1968
      ],
      "parameters": {
        "color": 7,
        "width": 688,
        "height": 272,
        "content": "## Gmail trigger and filter\n\nTriggers on new Gmail messages and filters them by labels before batching."
      },
      "typeVersion": 1
    },
    {
      "id": "7a2754ec-6476-4916-bd6c-e502f08daeab",
      "name": "Sticky Note9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        560,
        -2160
      ],
      "parameters": {
        "color": 7,
        "width": 416,
        "height": 304,
        "content": "## Email retrieval and parsing\n\nRetrieves full email content and parses it for further classification."
      },
      "typeVersion": 1
    },
    {
      "id": "a023207b-a474-43e5-a807-9943d5eb87e2",
      "name": "Sticky Note10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1008,
        -2160
      ],
      "parameters": {
        "color": 7,
        "width": 576,
        "height": 512,
        "content": "## Email classification and filtering\n\nClassifies email content to filter out spam and prepares for routing."
      },
      "typeVersion": 1
    },
    {
      "id": "68d026f9-70b4-4692-9242-1cbba27d7d39",
      "name": "Sticky Note11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1616,
        -2208
      ],
      "parameters": {
        "color": 7,
        "width": 448,
        "height": 400,
        "content": "## Label and route emails\n\nBuilds email labels and routes them according to SLA."
      },
      "typeVersion": 1
    },
    {
      "id": "bf197177-11fb-496f-adac-dd793c5a822e",
      "name": "Setup SLA Database",
      "type": "n8n-nodes-base.postgres",
      "position": [
        176,
        -1200
      ],
      "parameters": {
        "query": "CREATE EXTENSION IF NOT EXISTS pgcrypto;\n\nCREATE OR REPLACE FUNCTION public.set_updated_at()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.updated_at = now();\n  RETURN NEW;\nEND;\n$$;\n\nCREATE TABLE IF NOT EXISTS public.email_sla_cases (\n  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),\n  message_id text NOT NULL,\n  inbox_id text NOT NULL DEFAULT 'gmail',\n  thread_id text NULL,\n  event_id text NULL,\n  n8n_execution_id text NULL,\n  n8n_workflow_id text NULL,\n  n8n_workflow_name text NULL,\n  n8n_execution_url text NULL,\n  sender_email text NULL,\n  subject text NULL,\n  body text NULL,\n  labels text[] NOT NULL DEFAULT '{}',\n  sla_status text NULL,\n  case_status text NOT NULL DEFAULT 'open',\n  owner_alert boolean NOT NULL DEFAULT false,\n  helen_action boolean NOT NULL DEFAULT false,\n  response_deadline text NULL,\n  response_deadline_hours integer NULL,\n  received_at timestamptz NOT NULL DEFAULT now(),\n  processed_at timestamptz NULL,\n  sla_due_at timestamptz NULL,\n  first_response_at timestamptz NULL,\n  closed_at timestamptz NULL,\n  stucco_priority text NULL,\n  inquiry_category text NULL,\n  sentiment text NULL,\n  category text NULL,\n  department text NULL,\n  property_type text NULL,\n  technical_specs text NULL,\n  raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,\n  created_at timestamptz NOT NULL DEFAULT now(),\n  updated_at timestamptz NOT NULL DEFAULT now(),\n  CONSTRAINT email_sla_cases_message_id_unique UNIQUE (message_id)\n);\n\nALTER TABLE public.email_sla_cases ADD COLUMN IF NOT EXISTS n8n_workflow_id text NULL;\nALTER TABLE public.email_sla_cases ADD COLUMN IF NOT EXISTS n8n_workflow_name text NULL;\nALTER TABLE public.email_sla_cases ADD COLUMN IF NOT EXISTS n8n_execution_url text NULL;\n\nALTER TABLE public.email_sla_cases DROP CONSTRAINT IF EXISTS email_sla_cases_sla_status_check;\nALTER TABLE public.email_sla_cases ADD CONSTRAINT email_sla_cases_sla_status_check CHECK (sla_status IS NULL OR sla_status IN ('GREEN','YELLOW','ORANGE','RED','DONE','BREACHED','UNKNOWN'));\n\nALTER TABLE public.email_sla_cases DROP CONSTRAINT IF EXISTS email_sla_cases_case_status_check;\nALTER TABLE public.email_sla_cases ADD CONSTRAINT email_sla_cases_case_status_check CHECK (case_status IN ('open','waiting_customer','responded','closed','spam','ignored'));\n\nALTER TABLE public.email_sla_cases DROP CONSTRAINT IF EXISTS email_sla_cases_priority_check;\nALTER TABLE public.email_sla_cases ADD CONSTRAINT email_sla_cases_priority_check CHECK (stucco_priority IS NULL OR stucco_priority IN ('low','normal','high','urgent'));\n\nALTER TABLE public.email_sla_cases DROP CONSTRAINT IF EXISTS email_sla_cases_sentiment_check;\nALTER TABLE public.email_sla_cases ADD CONSTRAINT email_sla_cases_sentiment_check CHECK (sentiment IS NULL OR sentiment IN ('positive','neutral','negative','unknown'));\n\nALTER TABLE public.email_sla_cases DROP CONSTRAINT IF EXISTS email_sla_cases_deadline_hours_check;\nALTER TABLE public.email_sla_cases ADD CONSTRAINT email_sla_cases_deadline_hours_check CHECK (response_deadline_hours IS NULL OR response_deadline_hours > 0);\n\nCREATE INDEX IF NOT EXISTS email_sla_cases_message_id_idx ON public.email_sla_cases (message_id);\nCREATE INDEX IF NOT EXISTS email_sla_cases_thread_id_idx ON public.email_sla_cases (thread_id);\nCREATE INDEX IF NOT EXISTS email_sla_cases_case_status_idx ON public.email_sla_cases (case_status);\nCREATE INDEX IF NOT EXISTS email_sla_cases_sla_status_idx ON public.email_sla_cases (sla_status);\nCREATE INDEX IF NOT EXISTS email_sla_cases_sla_due_at_idx ON public.email_sla_cases (sla_due_at);\nCREATE INDEX IF NOT EXISTS email_sla_cases_received_at_idx ON public.email_sla_cases (received_at DESC);\nCREATE INDEX IF NOT EXISTS email_sla_cases_sender_email_idx ON public.email_sla_cases (lower(sender_email));\nCREATE INDEX IF NOT EXISTS email_sla_cases_labels_idx ON public.email_sla_cases USING gin (labels);\nCREATE INDEX IF NOT EXISTS email_sla_cases_raw_payload_idx ON public.email_sla_cases USING gin (raw_payload);\nCREATE INDEX IF NOT EXISTS email_sla_cases_n8n_execution_id_idx ON public.email_sla_cases (n8n_execution_id);\nCREATE INDEX IF NOT EXISTS email_sla_cases_open_due_idx ON public.email_sla_cases (sla_due_at ASC) WHERE case_status IN ('open','waiting_customer') AND closed_at IS NULL;\n\nDROP TRIGGER IF EXISTS trg_email_sla_cases_updated_at ON public.email_sla_cases;\nCREATE TRIGGER trg_email_sla_cases_updated_at\n  BEFORE UPDATE ON public.email_sla_cases\n  FOR EACH ROW\n  EXECUTE FUNCTION public.set_updated_at();\n\nCREATE TABLE IF NOT EXISTS public.email_sla_events (\n  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),\n  case_id uuid NULL REFERENCES public.email_sla_cases(id) ON DELETE CASCADE,\n  message_id text NULL,\n  event_type text NOT NULL,\n  event_status text NOT NULL DEFAULT 'info',\n  source text NOT NULL DEFAULT 'n8n',\n  workflow_id text NULL,\n  workflow_name text NULL,\n  node_name text NULL,\n  execution_id text NULL,\n  execution_url text NULL,\n  note text NULL,\n  payload jsonb NOT NULL DEFAULT '{}'::jsonb,\n  error jsonb NULL,\n  created_at timestamptz NOT NULL DEFAULT now()\n);\n\nALTER TABLE public.email_sla_events ADD COLUMN IF NOT EXISTS workflow_id text NULL;\nALTER TABLE public.email_sla_events ADD COLUMN IF NOT EXISTS execution_url text NULL;\n\nALTER TABLE public.email_sla_events DROP CONSTRAINT IF EXISTS email_sla_events_event_type_check;\nALTER TABLE public.email_sla_events ADD CONSTRAINT email_sla_events_event_type_check CHECK (event_type IN ('created','updated','triaged','owner_alerted','helen_drafted','redis_published','mem0_sent','responded','closed','breached','error','note'));\n\nALTER TABLE public.email_sla_events DROP CONSTRAINT IF EXISTS email_sla_events_event_status_check;\nALTER TABLE public.email_sla_events ADD CONSTRAINT email_sla_events_event_status_check CHECK (event_status IN ('info','success','warning','error'));\n\nCREATE INDEX IF NOT EXISTS email_sla_events_case_id_idx ON public.email_sla_events (case_id);\nCREATE INDEX IF NOT EXISTS email_sla_events_message_id_idx ON public.email_sla_events (message_id);\nCREATE INDEX IF NOT EXISTS email_sla_events_event_type_idx ON public.email_sla_events (event_type);\nCREATE INDEX IF NOT EXISTS email_sla_events_execution_id_idx ON public.email_sla_events (execution_id);\nCREATE INDEX IF NOT EXISTS email_sla_events_workflow_id_idx ON public.email_sla_events (workflow_id);\nCREATE INDEX IF NOT EXISTS email_sla_events_created_at_idx ON public.email_sla_events (created_at DESC);\nCREATE INDEX IF NOT EXISTS email_sla_events_payload_idx ON public.email_sla_events USING gin (payload);\n\nDROP VIEW IF EXISTS public.v_sla_owner_alerts;\nDROP VIEW IF EXISTS public.v_sla_done;\nDROP VIEW IF EXISTS public.v_sla_green;\nDROP VIEW IF EXISTS public.v_sla_yellow;\nDROP VIEW IF EXISTS public.v_sla_orange;\nDROP VIEW IF EXISTS public.v_sla_red;\nDROP VIEW IF EXISTS public.v_email_sla_board;\n\nCREATE VIEW public.v_email_sla_board AS\nSELECT\n  c.*,\n  CASE\n    WHEN c.closed_at IS NOT NULL OR c.case_status = 'closed' THEN 'DONE'\n    WHEN c.case_status IN ('spam','ignored') THEN 'DONE'\n    WHEN c.sla_due_at IS NOT NULL AND c.closed_at IS NULL AND c.case_status NOT IN ('closed','spam','ignored') AND now() > c.sla_due_at THEN 'RED'\n    WHEN upper(coalesce(c.sla_status, '')) = 'RED' THEN 'RED'\n    WHEN upper(coalesce(c.sla_status, '')) = 'ORANGE' THEN 'ORANGE'\n    WHEN upper(coalesce(c.sla_status, '')) = 'YELLOW' THEN 'YELLOW'\n    WHEN upper(coalesce(c.sla_status, '')) = 'GREEN' THEN 'GREEN'\n    WHEN c.sla_due_at IS NULL THEN 'UNKNOWN'\n    WHEN now() >= c.sla_due_at - interval '1 hour' THEN 'ORANGE'\n    WHEN now() >= c.sla_due_at - interval '4 hours' THEN 'YELLOW'\n    ELSE 'GREEN'\n  END AS live_sla_color,\n  CASE WHEN c.sla_due_at IS NULL THEN NULL ELSE floor(extract(epoch FROM c.sla_due_at - now()) / 60)::integer END AS minutes_until_due,\n  CASE WHEN c.received_at IS NULL THEN NULL ELSE floor(extract(epoch FROM now() - c.received_at) / 60)::integer END AS age_minutes,\n  CASE WHEN c.sla_due_at IS NOT NULL AND c.closed_at IS NULL AND c.case_status NOT IN ('closed','spam','ignored') AND now() > c.sla_due_at THEN true ELSE false END AS is_breached,\n  CASE\n    WHEN c.closed_at IS NOT NULL AND c.sla_due_at IS NOT NULL AND c.closed_at <= c.sla_due_at THEN true\n    WHEN c.closed_at IS NOT NULL AND c.sla_due_at IS NOT NULL AND c.closed_at > c.sla_due_at THEN false\n    ELSE NULL\n  END AS closed_within_sla\nFROM public.email_sla_cases c;\n\nCREATE VIEW public.v_sla_red AS SELECT * FROM public.v_email_sla_board WHERE live_sla_color = 'RED' ORDER BY sla_due_at ASC NULLS LAST;\nCREATE VIEW public.v_sla_orange AS SELECT * FROM public.v_email_sla_board WHERE live_sla_color = 'ORANGE' ORDER BY sla_due_at ASC NULLS LAST;\nCREATE VIEW public.v_sla_yellow AS SELECT * FROM public.v_email_sla_board WHERE live_sla_color = 'YELLOW' ORDER BY sla_due_at ASC NULLS LAST;\nCREATE VIEW public.v_sla_green AS SELECT * FROM public.v_email_sla_board WHERE live_sla_color = 'GREEN' ORDER BY sla_due_at ASC NULLS LAST;\nCREATE VIEW public.v_sla_done AS SELECT * FROM public.v_email_sla_board WHERE live_sla_color = 'DONE' ORDER BY closed_at DESC NULLS LAST, updated_at DESC;\nCREATE VIEW public.v_sla_owner_alerts AS SELECT * FROM public.v_email_sla_board WHERE owner_alert = true AND case_status IN ('open','waiting_customer') AND closed_at IS NULL ORDER BY sla_due_at ASC NULLS LAST;\n\nCREATE OR REPLACE FUNCTION public.log_email_sla_event(p_event 

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• gmailOAuth2
• groqApi
• httpHeaderAuth
• postgres
• redis

About this workflow

This workflow is for contractors, freelancers, local service businesses, and small teams that receive leads and customer requests through Gmail but do not have a dedicated sales or admin team.

Source: https://n8n.io/workflows/15432/ — original creator credit. Request a take-down →

More Email & Gmail workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.