호텔 프론트 데스크 WhatsApp 접대 시스템

• •Redis 서버 연결 정보
• •MySQL 데이터베이스 연결 정보
• •Google Sheets API 인증 정보
• •Google Gemini API Key

{
  "meta": {
    "instanceId": "07df66e33836083697e8bd7e53a57a2a1f404ad7aabcc82daa752a2b5a254167"
  },
  "nodes": [
    {
      "id": "2ccdee26-1db9-4717-b1ad-79651d7d65dc",
      "name": "스티키 노트",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -368,
        -96
      ],
      "parameters": {
        "color": 4,
        "width": 336,
        "height": 224,
        "content": "💬 Workflow Overview\nThis workflow acts as an AI receptionist for hotels, built on n8n.\nGuests can message the hotel via WhatsApp, and the system automatically replies using AI with real booking or pricing details.\n\nIt combines WhatsApp → AI Agent → Database → WhatsApp in one seamless loop."
      },
      "typeVersion": 1
    },
    {
      "id": "750f8f4d-35dd-4a82-90fd-0ef01a89888b",
      "name": "AI 에이전트",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        1456,
        -16
      ],
      "parameters": {
        "text": "={{ $('WhatsApp Trigger').item.json.messages[0].text.body }}",
        "options": {
          "systemMessage": "You are a helpful AI assistant tasked with answering questions about hotel bookings.\nYou have access to a MySQL database with tables like 'bookings', 'guests', 'rooms', etc.\n\nIMPORTANT SECURITY RULE: YOU ARE STRICTLY FORBIDDEN FROM PERFORMING ANY DATABASE WRITE OPERATIONS (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, etc.).\nYou must ONLY generate valid SQL SELECT statements.\n\nWhen a user asks a question, translate it into an appropriate SQL SELECT query."
        },
        "promptType": "define"
      },
      "retryOnFail": false,
      "typeVersion": 2
    },
    {
      "id": "1a267886-51fa-4500-ab95-6200cc512a1a",
      "name": "심플 메모리",
      "type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
      "position": [
        1600,
        192
      ],
      "parameters": {
        "sessionKey": "={{ $('WhatsApp Trigger').item.json.contacts[0].wa_id }}",
        "sessionIdType": "customKey"
      },
      "typeVersion": 1.3
    },
    {
      "id": "75cab8e8-7d1d-4730-a4b4-f9ba7f4f651e",
      "name": "Google Gemini 채팅 모델",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        1280,
        384
      ],
      "parameters": {
        "options": {}
      },
      "credentials": {
        "googlePalmApi": {
          "id": "2CxiwuZKBBT7X4pw",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "c3229f34-a2d5-4472-b54d-9c960a3baa0f",
      "name": "가격 정책",
      "type": "n8n-nodes-base.googleSheetsTool",
      "position": [
        1728,
        192
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/13yvxKTOWC4lgMLPWtVW_RELRZWdZHwUon_xOk4rF1X0/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "13yvxKTOWC4lgMLPWtVW_RELRZWdZHwUon_xOk4rF1X0"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "CDun7Si7Sf8O9FcN",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "0ab80c8e-e0ae-48d3-ace7-7afa25cdadc1",
      "name": "Google Gemini 채팅 모델1",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        1456,
        384
      ],
      "parameters": {
        "options": {}
      },
      "credentials": {
        "googlePalmApi": {
          "id": "Vhf40F3r42Vi7ZiS",
          "name": "akzzyforza Gemini"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "1f14faf9-f819-4a3d-9ca5-8dd35d814e95",
      "name": "WhatsApp 트리거",
      "type": "n8n-nodes-base.whatsAppTrigger",
      "position": [
        80,
        -16
      ],
      "webhookId": "dbb71091-820e-4c6f-b925-f1632b03d751",
      "parameters": {
        "options": {},
        "updates": [
          "messages"
        ]
      },
      "credentials": {
        "whatsAppTriggerApi": {
          "id": "c3SMCxa8j0SOP79z",
          "name": "WhatsApp OAuth account Magic"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "78022ecc-79b5-4f44-a209-155dd7c9e967",
      "name": "모델 결정기",
      "type": "n8n-nodes-base.code",
      "position": [
        752,
        -16
      ],
      "parameters": {
        "jsCode": "let data;\ntry {\n  data = $json.value ? JSON.parse($json.value) : null;\n} catch (e) {\n  data = null;\n}\n\nlet modelIndex;\nlet shouldSet = false;\n\nif (data && typeof data.modelIndex === 'number') {\n  // Alternate: flip the previous modelIndex\n  modelIndex = data.modelIndex === 0 ? 1 : 0;\n  shouldSet = true; // store the new one\n} else {\n  // If no data, default to model 0 and store it\n  modelIndex = 0;\n  shouldSet = true;\n}\n\nreturn [\n  {\n    json: {\n      modelIndex,\n      shouldSet\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "707df459-df55-4686-b64e-3d99c1380e03",
      "name": "메시지 전송",
      "type": "n8n-nodes-base.whatsApp",
      "position": [
        2016,
        -16
      ],
      "webhookId": "df4ef464-f28e-441d-a9da-cfcd5f64c9c2",
      "parameters": {
        "textBody": "={{ $json.output }}",
        "operation": "send",
        "phoneNumberId": "723548604171403",
        "additionalFields": {},
        "recipientPhoneNumber": "={{ $('WhatsApp Trigger').item.json.messages[0].from }}"
      },
      "credentials": {
        "whatsAppApi": {
          "id": "9Il95VjdrW30AtrZ",
          "name": "WhatsApp account Magic valley"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "8a9c3637-4e4d-4445-9c3b-c584e24dbdb5",
      "name": "MySQL에서 SQL 쿼리 실행",
      "type": "n8n-nodes-base.mySqlTool",
      "position": [
        1856,
        192
      ],
      "parameters": {
        "query": "{{ $('AI Agent').item.json.query }}",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "mySql": {
          "id": "3bfA94Mi8Qx2AnYZ",
          "name": "MySQL account"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "e1b30ed3-0ad7-417f-95de-752ab618c89a",
      "name": "스티키 노트1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        528,
        -256
      ],
      "parameters": {
        "color": 5,
        "width": 544,
        "height": 176,
        "content": "⚙️ Model Switching System\nThe workflow uses Redis to track each user’s AI model assignment.\nEach user is automatically routed to a different Google Gemini model, helping:\nDistribute traffic evenly across models\nReduce overall API cost\nKeep performance fast and stable\nThis makes it ideal for large-scale or high-traffic hotel systems."
      },
      "typeVersion": 1
    },
    {
      "id": "13bbf86c-a3cb-4fec-b66f-9f8cd912f494",
      "name": "스티키 노트2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1216,
        -272
      ],
      "parameters": {
        "color": 6,
        "width": 448,
        "height": 208,
        "content": "🧠 AI-Powered Hotel Assistant\nThe AI Agent interprets the user’s query and converts it into a read-only SQL SELECT statement.\n\nIt fetches information like room availability, guest check-ins, or booking data from MySQL, formats it naturally, and sends it back to the guest instantly on WhatsApp.\n\nThe workflow ensures safety (no write/delete queries) and delivers accurate, real-time hotel insights."
      },
      "typeVersion": 1
    },
    {
      "id": "448b3618-9ccd-45d1-b905-bedf5540d877",
      "name": "메시지 확인",
      "type": "n8n-nodes-base.code",
      "position": [
        304,
        -16
      ],
      "parameters": {
        "jsCode": "const msg = $json.messages?.[0]?.text;\n\nif (!msg) {\n  // Exit early if no text message\n  return [];\n}\n\nreturn items;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "f13bf318-7585-4787-a7ca-c083617a9e8f",
      "name": "사용자 번호 저장",
      "type": "n8n-nodes-base.redis",
      "position": [
        976,
        -16
      ],
      "parameters": {
        "key": "=llm-user:{{ $('WhatsApp Trigger').item.json.contacts[0].wa_id }}",
        "ttl": 3600,
        "value": "={{ JSON.stringify({ modelIndex: $json.modelIndex }) }}",
        "expire": true,
        "operation": "set"
      },
      "credentials": {
        "redis": {
          "id": "7OPmbqMTy2N5aprY",
          "name": "Redis account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "ca6ba30a-da25-42e2-b9c0-f30b0438ab8e",
      "name": "사용자 번호 확인",
      "type": "n8n-nodes-base.redis",
      "position": [
        528,
        -16
      ],
      "parameters": {
        "key": "=llm-user:{{ $json.contacts[0].wa_id }}",
        "options": {},
        "operation": "get"
      },
      "credentials": {
        "redis": {
          "id": "7OPmbqMTy2N5aprY",
          "name": "Redis account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "eeeac186-0627-45f9-a122-c5b0ac50a537",
      "name": "모델 선택",
      "type": "@n8n/n8n-nodes-langchain.modelSelector",
      "position": [
        1328,
        192
      ],
      "parameters": {
        "rules": {
          "rule": [
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "61173b3f-c09e-4efb-aae0-5af12aed3b1e",
                    "operator": {
                      "type": "number",
                      "operation": "equals"
                    },
                    "leftValue": "={{$json.modelIndex}}",
                    "rightValue": 0
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "6fd19391-2c15-42a6-82db-152976b1d6d3",
                    "operator": {
                      "type": "number",
                      "operation": "equals"
                    },
                    "leftValue": "={{$json.modelIndex}}",
                    "rightValue": 1
                  }
                ]
              },
              "modelIndex": 2
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "a317cb5a-56a2-41e7-8ab8-3a0758c09c11",
      "name": "스티키 노트3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        448,
        160
      ],
      "parameters": {
        "color": 3,
        "width": 288,
        "height": 192,
        "content": "Redis Get Node\n\n🧩 What it does\nWhen a WhatsApp message arrives, this node checks Redis for a record matching that user’s WhatsApp ID.\nThat stored record tells the workflow which AI model this user was last assigned to (e.g., model 0 or model 1)."
      },
      "typeVersion": 1
    },
    {
      "id": "86bfc5ea-e65c-4a6c-bd53-1ef3d473b97f",
      "name": "스티키 노트4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        880,
        160
      ],
      "parameters": {
        "color": 3,
        "width": 288,
        "height": 192,
        "content": "Redis Set Node\n\n🧩 What it does\nAfter the Model Decider decides which model the user should use, this node stores that model index back into Redis.\nIt also sets an expiration (ttl = 3600 seconds → 1 hour), meaning the assignment lasts for one hour."
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "c3229f34-a2d5-4472-b54d-9c960a3baa0f": {
      "ai_tool": [
        [
          {
            "node": "750f8f4d-35dd-4a82-90fd-0ef01a89888b",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "750f8f4d-35dd-4a82-90fd-0ef01a89888b": {
      "main": [
        [
          {
            "node": "707df459-df55-4686-b64e-3d99c1380e03",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "eeeac186-0627-45f9-a122-c5b0ac50a537": {
      "ai_languageModel": [
        [
          {
            "node": "750f8f4d-35dd-4a82-90fd-0ef01a89888b",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "448b3618-9ccd-45d1-b905-bedf5540d877": {
      "main": [
        [
          {
            "node": "ca6ba30a-da25-42e2-b9c0-f30b0438ab8e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "78022ecc-79b5-4f44-a209-155dd7c9e967": {
      "main": [
        [
          {
            "node": "f13bf318-7585-4787-a7ca-c083617a9e8f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1a267886-51fa-4500-ab95-6200cc512a1a": {
      "ai_memory": [
        [
          {
            "node": "750f8f4d-35dd-4a82-90fd-0ef01a89888b",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "1f14faf9-f819-4a3d-9ca5-8dd35d814e95": {
      "main": [
        [
          {
            "node": "448b3618-9ccd-45d1-b905-bedf5540d877",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ca6ba30a-da25-42e2-b9c0-f30b0438ab8e": {
      "main": [
        [
          {
            "node": "78022ecc-79b5-4f44-a209-155dd7c9e967",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f13bf318-7585-4787-a7ca-c083617a9e8f": {
      "main": [
        [
          {
            "node": "750f8f4d-35dd-4a82-90fd-0ef01a89888b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "75cab8e8-7d1d-4730-a4b4-f9ba7f4f651e": {
      "ai_languageModel": [
        [
          {
            "node": "eeeac186-0627-45f9-a122-c5b0ac50a537",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "0ab80c8e-e0ae-48d3-ace7-7afa25cdadc1": {
      "ai_languageModel": [
        [
          {
            "node": "eeeac186-0627-45f9-a122-c5b0ac50a537",
            "type": "ai_languageModel",
            "index": 1
          }
        ]
      ]
    },
    "8a9c3637-4e4d-4445-9c3b-c584e24dbdb5": {
      "ai_tool": [
        [
          {
            "node": "750f8f4d-35dd-4a82-90fd-0ef01a89888b",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    }
  }
}