AI Chatbot with MySQL & Google Gemini

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 →

{
  "name": "cji102_chatbot",
  "nodes": [
    {
      "parameters": {
        "method": "POST",
        "url": "=https://cji102-24-yolo-589190189996.asia-east1.run.app/predict",
        "sendBody": true,
        "contentType": "multipart-form-data",
        "bodyParameters": {
          "parameters": [
            {
              "parameterType": "formBinaryData",
              "name": "file",
              "inputDataFieldName": "photo"
            }
          ]
        },
        "options": {}
      },
      "id": "b847ecd3-8eb3-4d09-b845-6dcf395adb24",
      "name": "request(YOLO)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.1,
      "position": [
        -1424,
        2128
      ]
    },
    {
      "parameters": {
        "operation": "upsert",
        "table": {
          "__rl": true,
          "value": "questionnaire_answers_tags",
          "mode": "list",
          "cachedResultName": "questionnaire_answers_tags"
        },
        "dataMode": "defineBelow",
        "columnToMatchOn": "session_id",
        "valueToMatchOn": "={{ $json.session_id }}",
        "valuesToSend": {
          "values": [
            {
              "column": "answer1_tag",
              "value": "={{ $json.answers.answer1_tag }}"
            },
            {
              "column": "answer2_tag",
              "value": "={{ $json.answers.answer2_tag }}"
            },
            {
              "column": "answer3_tag",
              "value": "={{ $json.answers.answer3_tag }}"
            },
            {
              "column": "answer4_tag",
              "value": "={{ $json.answers.answer4_tag }}"
            },
            {
              "column": "answer5_tag",
              "value": "={{ $json.answers.answer5_tag }}"
            },
            {
              "column": "answer6_tag",
              "value": "={{ $json.answers.answer6_tag }}"
            },
            {
              "column": "answer7_tag",
              "value": "={{ $json.answers.answer7_tag }}"
            },
            {
              "column": "answer8_tag",
              "value": "={{ $json.answers.answer8_tag }}"
            },
            {
              "column": "answer9_tag",
              "value": "={{ $json.answers.answer9_tag }}"
            }
          ]
        },
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        -208,
        2272
      ],
      "id": "332965e0-8eee-4e83-818c-b5be471656f3",
      "name": "questionnaire_answers_tags",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "table": {
          "__rl": true,
          "value": "sessions",
          "mode": "list",
          "cachedResultName": "sessions"
        },
        "dataMode": "defineBelow",
        "valuesToSend": {
          "values": [
            {
              "column": "user_id",
              "value": "={{ $json.user_id }}"
            },
            {
              "column": "original_photo_url",
              "value": "={{ $('request(YOLO)').item.json.old_image_url }}"
            },
            {
              "column": "analyzed_photo_url",
              "value": "={{ $('request(YOLO)').item.json.new_image_url }}"
            },
            {
              "column": "session_id",
              "value": "0"
            },
            {
              "column": "status",
              "value": "analyzing"
            }
          ]
        },
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        -944,
        2128
      ],
      "id": "5e908d79-1dfa-4b48-9935-ebf196a0e062",
      "name": "session_images",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "INSERT INTO users (line_id)\nSELECT '{{ $json.body.line_id }}'\nFROM DUAL\nWHERE NOT EXISTS (\n    SELECT 1 FROM users WHERE line_id = '{{ $json.body.line_id }}'\n);",
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        -1440,
        1936
      ],
      "id": "cac3ec60-4738-40a1-b062-a71e3d797cb6",
      "name": "line_id",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT user_id FROM users\n  WHERE line_id='{{ $('analyze').item.json.body.line_id }}';\n",
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        -1168,
        2128
      ],
      "id": "3ff76927-162d-429b-9323-5cc808e0cf0b",
      "name": "select_userid",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT session_id FROM sessions\nORDER BY session_id DESC \nLIMIT 1;",
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        -720,
        2128
      ],
      "id": "5f3a2d6d-9d65-4236-b807-855809f3e85a",
      "name": "select_session_id",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "operation": "upsert",
        "table": {
          "__rl": true,
          "value": "session_skin_scores",
          "mode": "list",
          "cachedResultName": "session_skin_scores"
        },
        "dataMode": "defineBelow",
        "columnToMatchOn": "session_id",
        "valueToMatchOn": "={{ $json.session_id }}",
        "valuesToSend": {
          "values": [
            {
              "column": "acne_score",
              "value": "={{ $('request(YOLO)').item.json.count_acne }}"
            },
            {
              "column": "comedone_score",
              "value": "={{ $('request(YOLO)').item.json.count_comedones }}"
            },
            {
              "column": "wrinkle_score",
              "value": "={{ $('request(YOLO)').item.json.Wrinkle_confidence }}"
            },
            {
              "column": "spot_score",
              "value": "={{ $('request(YOLO)').item.json.count_spots }}"
            },
            {
              "column": "dark_circle_score",
              "value": "={{ $('request(YOLO)').item.json.DarkCircle_confidence }}"
            },
            {
              "column": "top_1_issues",
              "value": "={{ $('request(YOLO)').item.json.top_1 }}"
            },
            {
              "column": "top_2_issues",
              "value": "={{ $('request(YOLO)').item.json.top_2 }}"
            },
            {
              "column": "llm_1_text",
              "value": "={{ $('request(YOLO)').item.json.top_1_text }}"
            },
            {
              "column": "llm_2_text",
              "value": "={{ $('request(YOLO)').item.json.top_2_text }}"
            }
          ]
        },
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        -208,
        2480
      ],
      "id": "75f58997-a7fb-4db8-8739-0097472ee63e",
      "name": "session_skin_scores",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "CALL sp_recommend_funnel_full_v6({{ $('select_session_id').item.json.session_id }})",
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        32,
        2480
      ],
      "id": "9157f7f1-e435-408b-8cce-c19c5cb7f0e7",
      "name": "sp_recommend_funnel_full_v6",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "httpMethod": "POST",
        "path": "analyze",
        "responseMode": "responseNode",
        "options": {}
      },
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 2.1,
      "position": [
        -1760,
        2128
      ],
      "id": "279205f8-43ed-4bd3-a658-2626e7c24d64",
      "name": "analyze"
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "UPDATE sessions\nSET status = 'completed'\nWHERE session_id = {{ $('select_session_id').item.json.session_id }};",
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        336,
        2480
      ],
      "id": "6d766b3e-0e44-4d83-a23d-6cbb45e160cb",
      "name": "Execute a SQL query",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "path": "result",
        "responseMode": "responseNode",
        "options": {
          "allowedOrigins": "*"
        }
      },
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 2.1,
      "position": [
        -1696,
        2416
      ],
      "id": "ac43fc64-04da-4d50-adf5-6167609e14b5",
      "name": "result"
    },
    {
      "parameters": {
        "jsCode": "// n8n Code \u7bc0\u9ede - GET /webhook/result\n// \u4fee\u6b63\uff1a\u8655\u7406 MySQL Stored Procedure \u56de\u50b3\u7684\u5de2\u72c0\u9663\u5217\n\nconst items = $input.all();\n\n// MySQL CALL \u56de\u50b3\u683c\u5f0f\u662f [[row], {metadata}]\n// \u7b2c\u4e00\u500b\u5143\u7d20\u662f\u9663\u5217\uff0c\u88e1\u9762\u624d\u662f\u771f\u6b63\u7684\u8cc7\u6599\nlet row = null;\n\nif (items && items.length > 0) {\n  const firstItem = items[0].json;\n  \n  // \u5982\u679c\u662f\u9663\u5217\uff08Stored Procedure \u7684\u7d50\u679c\uff09\n  if (Array.isArray(firstItem)) {\n    row = firstItem[0];\n  } else {\n    row = firstItem;\n  }\n}\n\n// \u5982\u679c\u6c92\u6709\u8cc7\u6599\nif (!row || !row.session_id) {\n  return [{\n    json: {\n      status: \"pending\",\n      message: \"\u5206\u6790\u4e2d\uff0c\u8acb\u7a0d\u5019...\"\n    }\n  }];\n}\n\n// ===== \u8f14\u52a9\u51fd\u5f0f =====\n\nfunction parseJsonField(field) {\n  if (!field) return null;\n  if (typeof field === 'string') {\n    try {\n      return JSON.parse(field);\n    } catch (e) {\n      return null;\n    }\n  }\n  // \u5df2\u7d93\u662f\u7269\u4ef6\u6216\u9663\u5217\uff0c\u76f4\u63a5\u56de\u50b3\n  return field;\n}\n\nconst ISSUE_LABELS = {\n  'acne': '\u75d8\u75d8',\n  'comedone': '\u7c89\u523a',\n  'darkCircle': '\u9ed1\u773c\u5708',\n  'dark_circle': '\u9ed1\u773c\u5708',\n  'spot': '\u6591\u9ede',\n  'wrinkle': '\u7d30\u7d0b'\n};\n\nfunction translateIssueName(englishName) {\n  if (!englishName) return '';\n  return ISSUE_LABELS[englishName] || englishName;\n}\n\n// \u8655\u7406 ingredients\uff08\u53ef\u80fd\u662f\u5b57\u4e32\u9663\u5217\u6216\u7269\u4ef6\u9663\u5217\uff09\nfunction normalizeIngredients(ingredients) {\n  const parsed = parseJsonField(ingredients);\n  if (!parsed) return [];\n  \n  // \u5982\u679c\u662f\u5b57\u4e32\u9663\u5217 [\"\u83f8\u9e7c\u91af\u80fa\", \"\u7518\u8349\u8403\u53d6\"]\n  if (Array.isArray(parsed) && typeof parsed[0] === 'string') {\n    return parsed.map(name => ({ name: name, description: '' }));\n  }\n  \n  // \u5982\u679c\u5df2\u7d93\u662f\u7269\u4ef6\u9663\u5217 [{name: \"xxx\"}]\n  return parsed;\n}\n\n// ===== \u7d44\u88dd\u7d50\u679c =====\n\nconst result = {\n  status: \"completed\",\n  data: {\n    sectionA: {\n      photoUrl: row.original_photo_url || '',\n      score: Number(row.a_score) || 0,\n      evaluation: row.a_complexion_text || '',\n      massages: parseJsonField(row.a_massage_action) || []\n    },\n    sectionB: {\n      maskedPhotoUrl: row.analyzed_photo_url || row.original_photo_url || '',\n      scores: {\n        acne: Number(row.acne_score) || 0,\n        comedone: Number(row.comedone_score) || 0,\n        darkCircle: Number(row.dark_circle_score) || 0,\n        spot: Number(row.spot_score) || 0,\n        wrinkle: Number(row.wrinkle_score) || 0\n      },\n      topIssues: [\n        {\n          id: row.top_1_issues || '',\n          name: translateIssueName(row.top_1_issues),\n          description: row.llm_1_text || ''\n        },\n        {\n          id: row.top_2_issues || '',\n          name: translateIssueName(row.top_2_issues),\n          description: row.llm_2_text || ''\n        }\n      ].filter(issue => issue.name)\n    },\n    sectionC: {\n      suggestions: parseJsonField(row.dietary_advice) || []\n    },\n    sectionD: {\n      ingredients: normalizeIngredients(row.matched_ingredients),\n      products: parseJsonField(row.products_snapshot) || []\n    },\n    questionnaire: {\n      answer3_tag: row.answer3_tag || ''\n    }\n  }\n};\n\nreturn [{\n  json: result\n}];\n"
      },
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        -1248,
        2416
      ],
      "id": "f64433d7-2ed9-48c0-9afb-42de79550654",
      "name": "Code in JavaScript1"
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "CALL GetSessionResult({{ Number($json.query.session_id) }})",
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        -1472,
        2416
      ],
      "id": "e4c4254c-5e1b-4d43-b178-e60e2a9dc818",
      "name": "Execute a SQL query1",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "options": {}
      },
      "type": "n8n-nodes-base.respondToWebhook",
      "typeVersion": 1.5,
      "position": [
        -1024,
        2416
      ],
      "id": "0424a82b-49d9-41e2-87b3-6998e4d81269",
      "name": "result Respond to Webhook"
    },
    {
      "parameters": {
        "respondWith": "json",
        "responseBody": "={\n  \"success\": true,\n  \"session_id\": {{ $('select_session_id').item.json.session_id }}\n}",
        "options": {}
      },
      "type": "n8n-nodes-base.respondToWebhook",
      "typeVersion": 1.5,
      "position": [
        608,
        2480
      ],
      "id": "a9b5ea06-60cc-4503-8045-336dfb1eb5e9",
      "name": "analyze Respond to Webhook"
    },
    {
      "parameters": {
        "assignments": {
          "assignments": [
            {
              "id": "a7df52fe-f522-4b92-be63-932a04a71392",
              "name": "imageUrl",
              "value": "={{ $('request(YOLO)').item.json.old_image_url }}",
              "type": "string"
            },
            {
              "id": "0b59e167-eaf5-43ae-b54c-4aa30c18da9b",
              "name": "answers",
              "value": "={{ $('analyze').item.json.body.answers }}",
              "type": "object"
            }
          ]
        },
        "includeOtherFields": true,
        "options": {}
      },
      "type": "n8n-nodes-base.set",
      "typeVersion": 3.4,
      "position": [
        -464,
        2064
      ],
      "id": "cda18d6b-ba3b-457b-956e-505d3681d731",
      "name": "prepare_ai_input"
    },
    {
      "parameters": {
        "promptType": "define",
        "text": "=\u4f60\u662f\u5c08\u696d\u7684\u6c23\u8272\u5206\u6790\u5e2b\u8207\u71df\u990a\u9867\u554f\u3002\u8acb\u540c\u6642\u5b8c\u6210\u4ee5\u4e0b\u5169\u9805\u4efb\u52d9\uff1a\n========================================\n\u3010\u4efb\u52d9 1\uff1a\u6c23\u8272\u5206\u6790\u3011\n========================================\n\u8acb\u5206\u6790\u9019\u5f35\u7167\u7247\uff0c\u5f9e\u4ee5\u4e0b 10 \u500b\u56fa\u5b9a\u7dad\u5ea6\u8a55\u4f30\uff1a\n1. \u9762\u90e8\u660e\u6697\u5206\u5e03\n2. \u9762\u90e8\u7d05\u6f64\u5ea6\n3. \u795e\u7d93\u808c\u8089\u5f35\u529b\n4. \u773c\u795e\u805a\u7126\u611f\n5. \u7d44\u7e54\u6c34\u5206\u611f\n6. \u9762\u90e8\u8f2a\u5ed3\u7dda\n7. \u7709\u5fc3\u7dca\u7e2e\u5ea6\n8. \u9f3b\u7ffc\u547c\u5438\u611f\n9. \u9838\u90e8\u7dda\u689d\n10. \u773c\u795e\u900f\u4eae\u5ea6\n\u8a55\u5206\u6a19\u6e96\uff1a\n- score\uff1a\u5fc5\u9808\u662f \u6700\u4f4e60\u5206\u6700\u9ad895\u5206 \u4e4b\u9593\u7684\u6574\u6578\n- overall_review\uff1a\u7e3d\u5b57\u6578\u56b4\u683c\u63a7\u5236\u5728 80 \u5b57\u4ee5\u5167\n- \u7d50\u69cb\uff1a\u4e00\u53e5\u6574\u9ad4\u5370\u8c61 + \u4e00\u500b\u512a\u9ede + \u5169\u500b\u9700\u6539\u5584\u9ede\n- \u8a9e\u6c23\u7c21\u6f54\u5c08\u696d\uff0c\u4e0d\u56c9\u55e6\n- \u7bc4\u4f8b\uff1a\"\u59b3\u7684\u76ae\u819a\u57fa\u790e\u975e\u5e38\u597d\uff0c\u819a\u8272\u5747\u52fb\u4e14\u6563\u767c\u81ea\u7136\u5149\u6fa4\uff0c\u7d66\u4eba\u4e00\u7a2e\u77e5\u6027\u7a69\u5b9a\u7684\u7f8e\u611f\u3002\u76ee\u524d\u8f03\u660e\u986f\u7684\u72c0\u6cc1\u662f\u773c\u795e\u805a\u7126\u611f\u7565\u986f\u75b2\u618a\uff0c\u4ee5\u53ca\u9762\u90e8\u660e\u6697\u5206\u5e03\u53d7\u9650\u65bc\u5149\u6e90\uff0c\u986f\u5f97\u9f3b\u7ffc\u8207\u773c\u5468\u7a0d\u6709\u9670\u5f71\uff0c\u9069\u5ea6\u653e\u9b06\u6703\u66f4\u4eae\u773c\u3002\"\n\u26a0\ufe0f top_issues \u9078\u64c7\u898f\u5247\uff08\u975e\u5e38\u91cd\u8981\uff09\uff1a\n- \u5fc5\u9808\u5f9e\u4e0a\u8ff0 10 \u500b\u56fa\u5b9a\u540d\u7a31\u4e2d\u9078\u64c7\uff0c\u4e00\u5b57\u4e0d\u5dee\n- \u4e0d\u53ef\u4ee5\u81ea\u5df1\u5275\u9020\u6216\u4fee\u6539\u540d\u7a31\uff08\u4f8b\u5982\uff1a\u4e0d\u80fd\u7528 \"\u81c9\u90e8\u660e\u6697\" \u6216 \"\u9762\u90e8\u660e\u6697\u611f\"\uff09\n- \u9805\u76ee\u540d\u7a31\u5fc5\u9808\u5b8c\u5168\u8907\u88fd\uff0c\u5305\u542b\u6a19\u9ede\u7b26\u865f\n- \u53ea\u9078\u64c7\u6700\u56b4\u91cd\u7684 2 \u500b\u554f\u984c\n- \u6309\u56b4\u91cd\u7a0b\u5ea6\u6392\u5e8f\uff0c\u6700\u56b4\u91cd\u7684\u653e\u7b2c\u4e00\u500b\n========================================\n\u3010\u4efb\u52d9 2\uff1a\u98f2\u98df\u5efa\u8b70\u3011\n========================================\n\u6839\u64da\u4ee5\u4e0b\u4f7f\u7528\u8005\u8cc7\u6599\uff0c\u751f\u6210 3 \u7d44\u7cbe\u6e96\u7684\u98df\u6750\u7d44\u5408\u5efa\u8b70\uff1a\n\u3010\u4f7f\u7528\u8005\u554f\u5377\u8cc7\u6599\u3011\n\u5e74\u9f61\uff1a{{ $json.age }}\n\u819a\u8cea\u72c0\u614b\uff1a{{ $json.skinCondition }}\n\u654f\u611f\u5ea6\uff1a{{ $json.sensitivity }}\n\u4fdd\u990a\u6b65\u9a5f\uff1a{{ $json.rhythm }}\n\u7761\u7720\u6642\u9593\uff1a{{ $json.sleepHours }}\n\u58d3\u529b\u6307\u6578\uff1a{{ $json.stress }}\n\u904b\u52d5\u983b\u7387\uff08\u6bcf\u9031\uff09\uff1a{{ $json.exercise }}\n\u6cb9\u70b8\u98df\u7269\u651d\u53d6\uff08\u6bcf\u9031\uff09\uff1a{{ $json.friedFood }}\n\u852c\u679c\u651d\u53d6\uff1a{{ $json.diet }}\n\u6bcf\u7d44\u5efa\u8b70\u8981\u6c42\uff1a\n- title\uff1a10 \u5b57\u4ee5\u5167\uff0c\u7528\u53e3\u8a9e\u5316\u540d\u7a31\uff08\u4f8b\u5982\uff1a\u300c\u5403\u9019\u4e9b\u6297\u75d8\u300d\u300c\u7761\u597d\u76ae\u819a\u597d\u300d\u300c\u63a7\u6cb9\u5fc5\u5403\u300d\uff09\n- ingredients\uff1a2-5 \u7a2e\u5177\u9ad4\u98df\u6750\n- content\uff1a2-3 \u53e5\u8a71\uff0c\u7528\u5c08\u696d\u4f46\u597d\u61c2\u7684\u65b9\u5f0f\u8aaa\u660e\u529f\u6548\n========================================\n\u3010\u8f38\u51fa\u683c\u5f0f\u3011\n========================================\n\u26a0\ufe0f \u95dc\u9375\u8981\u6c42\uff08\u5fc5\u9808\u9075\u5b88\uff09\uff1a\n1. \u76f4\u63a5\u8f38\u51fa JSON \u7269\u4ef6\uff0c\u5f9e { \u958b\u59cb\uff0c\u5230 } \u7d50\u675f\n2. \u4e0d\u8981\u4f7f\u7528 ```json \u6216 ``` \u6216\u4efb\u4f55 markdown \u6a19\u8a18\n3. \u4e0d\u8981\u6709\u4efb\u4f55\u984d\u5916\u7684\u89e3\u91cb\u6587\u5b57\u6216\u7b26\u865f\n4. \u8f38\u51fa\u5fc5\u9808\u662f\u6709\u6548\u7684 JSON \u683c\u5f0f\uff08\u53ef\u4ee5\u88ab JSON.parse() \u89e3\u6790\uff09\n5. overall_review \u5fc5\u9808\u5728 80 \u5b57\u4ee5\u5167\n6. top_issues \u5fc5\u9808\u5b8c\u5168\u4e00\u81f4\u65bc\u4e0a\u8ff0 10 \u500b\u7dad\u5ea6\u540d\u7a31\n\u6a19\u6e96\u683c\u5f0f\uff08\u76f4\u63a5\u8f38\u51fa\u9019\u6a23\u7684\u7d50\u69cb\uff09\uff1a\n{\"complexion\":{\"score\":80,\"overall_review\":\"\u6574\u9ad4\u6c23\u8272\u81ea\u7136\u826f\u597d\uff0c\u819a\u8cea\u72c0\u614b\u4f73\u3002\u773c\u795e\u7565\u986f\u75b2\u618a\uff0c\u4e14\u9762\u90e8\u660e\u6697\u5206\u5e03\u53d7\u5149\u7dda\u5f71\u97ff\uff0c\u5efa\u8b70\u591a\u4f11\u606f\u4e26\u6ce8\u610f\u7167\u660e\u3002\",\"top_issues\":[\"\u9762\u90e8\u660e\u6697\u5206\u5e03\",\"\u773c\u795e\u805a\u7126\u611f\"]},\"dietary\":{\"suggestions\":[{\"type\":\"food\",\"title\":\"\u63a7\u6cb9\u5fc5\u5403\",\"ingredients\":[\"\u9bad\u9b5a\",\"\u83e0\u83dc\",\"\u5805\u679c\"],\"content\":\"\u5bcc\u542b Omega-3 \u7684\u98df\u7269\u53ef\u4ee5\u8abf\u7bc0\u76ae\u8102\u5206\u6ccc\uff0c\u6e1b\u5c11\u6cb9\u5149\u554f\u984c\u3002\u5efa\u8b70\u6bcf\u9031\u5403 2-3 \u6b21\u6df1\u6d77\u9b5a\u985e\u3002\"},{\"type\":\"food\",\"title\":\"\u7f8e\u5bb9\u89ba\u6642\u9593\",\"ingredients\":[\"\u9999\u8549\",\"\u6838\u6843\",\"\u6eab\u725b\u5976\"],\"content\":\"\u9019\u4e9b\u98df\u7269\u5bcc\u542b\u8272\u6c28\u9178\u548c\u9382\uff0c\u53ef\u4ee5\u5e6b\u52a9\u653e\u9b06\u795e\u7d93\u3001\u6539\u5584\u7761\u7720\u54c1\u8cea\u3002\u7761\u524d 1 \u5c0f\u6642\u98df\u7528\u6548\u679c\u6700\u597d\u3002\"},{\"type\":\"food\",\"title\":\"\u6de1\u6591\u5fc5\u5099\",\"ingredients\":[\"\u756a\u8304\",\"\u82ad\u6a02\",\"\u5947\u7570\u679c\",\"\u7d05\u6912\"],\"content\":\"\u7dad\u4ed6\u547d C \u8c50\u5bcc\u7684\u98df\u6750\u6709\u52a9\u65bc\u6291\u5236\u9ed1\u8272\u7d20\u751f\u6210\uff0c\u5efa\u8b70\u6bcf\u5929\u651d\u53d6\u81f3\u5c11 2 \u7a2e\u3002\"}]}}\n\u26a0\ufe0f \u6700\u5f8c\u63d0\u9192\uff1a\n- \u76f4\u63a5\u8f38\u51fa JSON\uff0c\u4e0d\u8981\u6709\u4efb\u4f55\u524d\u7db4\u6216\u5f8c\u7db4\n- \u4e0d\u8981\u4f7f\u7528\u7a0b\u5f0f\u78bc\u5340\u584a\u6a19\u8a18\n- \u78ba\u4fdd JSON \u683c\u5f0f\u6b63\u78ba\n- top_issues \u7684\u540d\u7a31\u5fc5\u9808\u5b8c\u5168\u4e00\u81f4\uff08\u4e00\u5b57\u4e0d\u5dee\uff09",
        "options": {}
      },
      "type": "@n8n/n8n-nodes-langchain.agent",
      "typeVersion": 3.1,
      "position": [
        -272,
        1872
      ],
      "id": "4895b3ba-ea6c-4d7f-acd6-9696a7d2b15a",
      "name": "AI_Combined"
    },
    {
      "parameters": {
        "jsCode": "// \u6309\u6469\u52d5\u4f5c\u5c0d\u7167\u8868\nconst issueMapping = {\n  \"\u9762\u90e8\u660e\u6697\u5206\u5e03\": {\n    name: \"\u9762\u90e8\u660e\u6697\u5206\u5e03\",\n    effect: \"\u81c9\u4e0a\u7684\u6697\u5f71\u6703\u660e\u986f\u6e1b\u5c11,\u770b\u8d77\u4f86\u6bd4\u8f03\u5e73\u6574\u3001\u4e0d\u75b2\u7d2f,\u6574\u5f35\u81c9\u66f4\u958b\u95ca\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/1_\u9762\u90e8\u660e\u6697\u5206\u5e03.mp4\",\n    description: \"\u5c07\u8eab\u9ad4\u8207\u81c9\u90e8\u8f49\u5411\u4e3b\u8981\u5149\u6e90,\u8b93\u5149\u7dda\u81ea\u7136\u7167\u5728\u81c9\u7684\u6b63\u524d\u65b9\u3002\"\n  },\n  \"\u9762\u90e8\u7d05\u6f64\u5ea6\": {\n    name: \"\u9762\u90e8\u7d05\u6f64\u5ea6\",\n    effect: \"\u81c9\u9830\u5f88\u5feb\u6703\u51fa\u73fe\u81ea\u7136\u7684\u8840\u8272,\u770b\u8d77\u4f86\u6bd4\u8f03\u6709\u7cbe\u795e\u3001\u4e0d\u84bc\u767d\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/2_\u9762\u90e8\u7d05\u6f64\u5ea6.mp4\",\n    description: \"\u8f15\u8f15\u7531\u4e0b\u5f80\u4e0a\u62cd\u6253\u81c9\u9830,\u76f4\u5230\u611f\u89ba\u81c9\u90e8\u5fae\u5fae\u6eab\u71b1\u5373\u53ef\u3002\"\n  },\n  \"\u795e\u7d93\u808c\u8089\u5f35\u529b\": {\n    name: \"\u795e\u7d93\u808c\u8089\u5f35\u529b\",\n    effect: \"\u4e0b\u5df4\u8207\u81c9\u90e8\u7dda\u689d\u6703\u653e\u9b06,\u6574\u500b\u8868\u60c5\u5f9e\u7dca\u7e43\u8b8a\u5f97\u6bd4\u8f03\u81ea\u4fe1\u3001\u597d\u89aa\u8fd1\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/3_\u795e\u7d93\u808c\u8089\u5f35\u529b.mp4\",\n    description: \"\u6162\u6162\u505a\u4e00\u500b\u5927\u5927\u7684\u300c\u4e00\u300d\u5b57\u53e3\u578b,\u7dad\u6301 10 \u79d2,\u540c\u6642\u6df1\u547c\u5438\u653e\u9b06\u4e0b\u5df4\u3002\"\n  },\n  \"\u773c\u795e\u805a\u7126\u611f\": {\n    name: \"\u773c\u795e\u805a\u7126\u611f\",\n    effect: \"\u773c\u775b\u4e0d\u518d\u90a3\u9ebc\u9178\u6f80,\u8996\u7dda\u66f4\u96c6\u4e2d,\u770b\u8d77\u4f86\u6bd4\u8f03\u6709\u795e\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/4_\u773c\u795e\u805a\u7126\u611f.mp4\",\n    description: \"\u8b93\u773c\u775b\u4f9d\u5e8f\u5f80\u4e0a\u4e0b\u5de6\u53f3\u79fb\u52d5,\u63a5\u8457\u9060\u671b\u7a97\u5916\u7d04 20 \u79d2\u3002\"\n  },\n  \"\u7d44\u7e54\u6c34\u5206\u611f\": {\n    name: \"\u7d44\u7e54\u6c34\u5206\u611f\",\n    effect: \"\u81c9\u90e8\u4e7e\u6241\u611f\u6703\u6162\u6162\u6d88\u5931,\u819a\u6cc1\u770b\u8d77\u4f86\u6bd4\u8f03\u98fd\u6eff\u3001\u6709\u5149\u6fa4\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/5_\u7d44\u7e54\u6c34\u5206\u611f.mp4\",\n    description: \"\u5206\u5e7e\u6b21\u559d\u5b8c\u7d04 200ml \u7684\u6eab\u71b1\u6c34,\u6eab\u5ea6\u63a5\u8fd1\u9ad4\u6eab\u5373\u53ef\u3002\"\n  },\n  \"\u9762\u90e8\u8f2a\u5ed3\u7dda\": {\n    name: \"\u9762\u90e8\u8f2a\u5ed3\u7dda\",\n    effect: \"\u81c9\u90e8\u6d6e\u816b\u611f\u4e0b\u964d,\u8f2a\u5ed3\u7dda\u6703\u8b8a\u6e05\u695a,\u4e94\u5b98\u7acb\u9ad4\u5ea6\u66f4\u660e\u986f\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/6_\u9762\u90e8\u8f2a\u5ed3\u7dda.mp4\",\n    description: \"\u7528\u6307\u95dc\u7bc0\u5f9e\u9aee\u969b\u7dda\u5f80\u5f8c,\u9806\u8457\u982d\u76ae\u8f15\u67d4\u6309\u58d3\u5230\u5f8c\u8166\u52fa\u3002\"\n  },\n  \"\u7709\u5fc3\u7dca\u7e2e\u5ea6\": {\n    name: \"\u7709\u5fc3\u7dca\u7e2e\u5ea6\",\n    effect: \"\u7709\u5fc3\u4e0d\u518d\u7dca\u76ba,\u8868\u60c5\u770b\u8d77\u4f86\u6bd4\u8f03\u653e\u9b06\u3001\u5e73\u975c\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/7_\u7709\u5fc3\u7dca\u7e2e\u5ea6.mp4\",\n    description: \"\u7528\u98df\u6307\u6307\u8179\u5f9e\u7709\u5fc3\u5411\u5de6\u53f3\u7709\u5c3e\u5e73\u63a8,\u91cd\u8907\u7d04 10 \u6b21\u3002\"\n  },\n  \"\u9f3b\u7ffc\u547c\u5438\u611f\": {\n    name: \"\u9f3b\u7ffc\u547c\u5438\u611f\",\n    effect: \"\u547c\u5438\u8b8a\u5f97\u66f4\u9806,\u6574\u500b\u4eba\u611f\u89ba\u6bd4\u8f03\u6e05\u9192,\u81c9\u8272\u770b\u8d77\u4f86\u66f4\u5065\u5eb7\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/8_\u9f3b\u7ffc\u547c\u5438\u611f.mp4\",\n    description: \"\u8ddf\u8457\u7bc0\u594f\u9032\u884c 4-7-8 \u547c\u5438\u6cd5,\u5c08\u6ce8\u547c\u5438\u5373\u53ef\u3002\"\n  },\n  \"\u9838\u90e8\u7dda\u689d\": {\n    name: \"\u9838\u90e8\u7dda\u689d\",\n    effect: \"\u9838\u90e8\u62c9\u958b\u5f8c,\u81c9\u90e8\u6697\u6c88\u611f\u6e1b\u8f15,\u770b\u8d77\u4f86\u6bd4\u8f03\u633a\u3001\u6bd4\u8f03\u4eae\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/9_\u9838\u90e8\u7dda\u689d.mp4\",\n    description: \"\u96d9\u624b\u4ea4\u53c9\u653e\u5728\u80f8\u524d,\u982d\u90e8\u7de9\u6162\u5411\u5f8c\u4ef0,\u505c\u7559\u7d04 10 \u79d2\u3002\"\n  },\n  \"\u773c\u795e\u900f\u4eae\u5ea6\": {\n    name: \"\u773c\u795e\u900f\u4eae\u5ea6\",\n    effect: \"\u773c\u775b\u7684\u7d05\u816b\u8207\u75b2\u52de\u611f\u4e0b\u964d,\u773c\u795e\u770b\u8d77\u4f86\u66f4\u4e7e\u6de8\u3001\u6709\u7cbe\u795e\u3002\",\n    gifUrl: \"https://storage.googleapis.com/skincare-app-assets/actions/10_\u773c\u795e\u900f\u4eae\u5ea6.mp4\",\n    description: \"\u96d9\u624b\u638c\u5fc3\u8f15\u8986\u96d9\u773c,\u9589\u773c\u653e\u9b06\u7d04 1 \u5206\u9418\u3002\"\n  }\n};\n// \u53d6\u5f97 AI Agent \u7684\u56de\u61c9\nconst input = $input.all()[0].json;\nlet aiResponse;\ntry {\n  // \u5617\u8a66\u5f9e\u4e0d\u540c\u4f4d\u7f6e\u53d6\u5f97\u8cc7\u6599\n  aiResponse = input.output || input.text || input;\n  \n  // \u5982\u679c\u662f\u5b57\u4e32\uff0c\u6e05\u7406 markdown \u6a19\u8a18\u4e26\u89e3\u6790\n  if (typeof aiResponse === 'string') {\n    aiResponse = aiResponse\n      .replace(/```json\\s*/g, '')  // \u79fb\u9664 ```json\n      .replace(/```\\s*/g, '')      // \u79fb\u9664 ```\n      .trim();\n    \n    aiResponse = JSON.parse(aiResponse);\n  }\n  \n  // \u8a18\u9304\u89e3\u6790\u5f8c\u7684\u8cc7\u6599\uff08\u9664\u932f\u7528\uff09\n  console.log('\u89e3\u6790\u5f8c\u7684 AI \u56de\u61c9:', JSON.stringify(aiResponse).substring(0, 200));\n  \n} catch (error) {\n  // \u8a18\u9304\u539f\u59cb\u8cc7\u6599\u4ee5\u4fbf\u9664\u932f\n  console.log('\u539f\u59cb\u8f38\u5165:', JSON.stringify(input).substring(0, 500));\n  throw new Error('\u7121\u6cd5\u89e3\u6790 AI \u56de\u61c9: ' + error.message);\n}\n// \u9a57\u8b49\u5fc5\u8981\u6b04\u4f4d\nif (!aiResponse.complexion || !aiResponse.dietary) {\n  console.log('\u8cc7\u6599\u7d50\u69cb\u932f\u8aa4:', JSON.stringify(aiResponse).substring(0, 500));\n  throw new Error('AI \u56de\u61c9\u7f3a\u5c11 complexion \u6216 dietary \u6b04\u4f4d');\n}\nif (!aiResponse.complexion.score || !aiResponse.complexion.overall_review || !aiResponse.complexion.top_issues) {\n  throw new Error('complexion \u8cc7\u6599\u4e0d\u5b8c\u6574');\n}\nif (!aiResponse.dietary.suggestions) {\n  throw new Error('dietary \u8cc7\u6599\u4e0d\u5b8c\u6574');\n}\n// \u7d44\u5408\u6309\u6469\u5efa\u8b70\nconst topIssues = aiResponse.complexion.top_issues || [];\nconst massages = topIssues.map(issue => issueMapping[issue]).filter(Boolean);\n// \u56de\u50b3\u7d50\u679c\nreturn {\n  json: {\n    sectionA: {\n      photoUrl: $('request(YOLO)').item.json.old_image_url,\n      score: aiResponse.complexion.score,\n      evaluation: aiResponse.complexion.overall_review,\n      massages: massages,\n      topIssues: topIssues\n    },\n    suggestions: aiResponse.dietary.suggestions,\n    analyzed_at: new Date().toISOString()\n  }\n};"
      },
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        176,
        1872
      ],
      "id": "d4ecfa9b-f075-4423-9d39-65c635276ac7",
      "name": "save_section_a"
    },
    {
      "parameters": {
        "operation": "upsert",
        "table": {
          "__rl": true,
          "value": "session_llm_analysis",
          "mode": "list",
          "cachedResultName": "session_llm_analysis"
        },
        "dataMode": "defineBelow",
        "columnToMatchOn": "session_id",
        "valueToMatchOn": "={{ $('select_session_id').item.json.session_id }}",
        "valuesToSend": {
          "values": [
            {
              "column": "a_score",
              "value": "={{ $json.sectionA.score }}"
            },
            {
              "column": "a_complexion_text",
              "value": "={{ $json.sectionA.evaluation }}"
            },
            {
              "column": "a_massage_action",
              "value": "={{ JSON.stringify($json.sectionA.massages) }}"
            },
            {
              "column": "dietary_advice",
              "value": "={{ JSON.stringify($json.suggestions) }}"
            }
          ]
        },
        "options": {}
      },
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        480,
        1872
      ],
      "id": "122fe0c9-666a-4650-b5b4-7bd689e4137d",
      "name": "session_llm_analysis1",
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "options": {}
      },
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "typeVersion": 1,
      "position": [
        -192,
        2096
      ],
      "id": "1647ab8d-d7f8-4382-b5ab-0caa12403474",
      "name": "Google Gemini Chat Model",
      "credentials": {
        "googlePalmApi": {
          "name": "<your credential>"
        }
      }
    }
  ],
  "connections": {
    "request(YOLO)": {
      "main": [
        [
          {
            "node": "select_userid",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "session_images": {
      "main": [
        [
          {
            "node": "select_session_id",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "select_userid": {
      "main": [
        [
          {
            "node": "session_images",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "select_session_id": {
      "main": [
        [
          {
            "node": "prepare_ai_input",
            "type": "main",
            "index": 0
          },
          {
            "node": "session_skin_scores",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "session_skin_scores": {
      "main": [
        [
          {
            "node": "sp_recommend_funnel_full_v6",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "sp_recommend_funnel_full_v6": {
      "main": [
        [
          {
            "node": "Execute a SQL query",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "analyze": {
      "main": [
        [
          {
            "node": "request(YOLO)",
            "type": "main",
            "index": 0
          },
          {
            "node": "line_id",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Execute a SQL query": {
      "main": [
        [
          {
            "node": "analyze Respond to Webhook",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "result": {
      "main": [
        [
          {
            "node": "Execute a SQL query1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Code in JavaScript1": {
      "main": [
        [
          {
            "node": "result Respond to Webhook",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Execute a SQL query1": {
      "main": [
        [
          {
            "node": "Code in JavaScript1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "prepare_ai_input": {
      "main": [
        [
          {
            "node": "AI_Combined",
            "type": "main",
            "index": 0
          },
          {
            "node": "questionnaire_answers_tags",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "AI_Combined": {
      "main": [
        [
          {
            "node": "save_section_a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "save_section_a": {
      "main": [
        [
          {
            "node": "session_llm_analysis1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Gemini Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "AI_Combined",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": true,
  "settings": {
    "executionOrder": "v1",
    "availableInMCP": false
  },
  "versionId": "d92c2c23-6af4-48db-96be-ea2be9e2f97b",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "id": "ZjZfHRDOvTBkLfiP1OpYD",
  "tags": []
}

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.

• googlePalmApi
• mySql

How this works

This workflow powers a sophisticated chatbot that delivers personalised skincare recommendations based on user interactions, helping individuals achieve clearer skin through tailored advice from Google's Gemini AI. It's ideal for beauty brands or wellness apps seeking to automate customer consultations without manual intervention. The process begins with a webhook trigger capturing user queries, followed by MySQL queries to retrieve session data like questionnaire answers and skin scores, before the AI agent analyses this to generate precise, context-aware responses.

Use this when handling high-volume chatbot queries that require database-driven personalisation, such as in e-commerce support or ongoing user coaching. Avoid it for simple Q&A bots lacking user history, or if real-time processing isn't essential. Common variations include swapping Gemini for another LLM or adding email notifications for follow-up recommendations.

About this workflow

cji102_chatbot. Uses httpRequest, mySql, agent, lmChatGoogleGemini. Webhook trigger; 20 nodes.

Source: https://github.com/JaniceYangYintine/AI-SkinPerfection-Project/blob/aafef956b57648df65d51ab591bd7feb97739c87/n8n/cji102_chatbot.json — original creator credit. Request a take-down →

More AI & RAG workflows → · Browse all categories →

Related workflows

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