Telegram-Raumbuchungsassistent

• •Telegram Bot Token
• •Google-Konto + Gmail API-Anmeldedaten
• •Google Sheets API-Anmeldedaten
• •Google Gemini API Key
• •PostgreSQL-Datenbankverbindungsdaten

{
  "id": "TQT1s777PZUJUvxu",
  "meta": {
    "instanceId": "0bb95f247e6cda467b7bfa0ecb34ff52cee89ad78f51d5982d5e97b8ca9b00d0",
    "templateCredsSetupCompleted": true
  },
  "name": "telegram court agent",
  "tags": [],
  "nodes": [
    {
      "id": "cdee9b4c-c94d-47c7-802b-0825919db984",
      "name": "Telegram-Trigger",
      "type": "n8n-nodes-base.telegramTrigger",
      "position": [
        -540,
        -160
      ],
      "webhookId": "c2f4be19-1555-454d-bd08-46096d4521af",
      "parameters": {
        "updates": [
          "message"
        ],
        "additionalFields": {}
      },
      "credentials": {
        "telegramApi": {
          "id": "uQzLbJvkCgXKAVZ1",
          "name": "Telegram account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "24052a80-a551-4347-9fbc-5cf4498885b1",
      "name": "Google Gemini-Chat-Modell",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        -100,
        60
      ],
      "parameters": {
        "options": {},
        "modelName": "models/gemini-2.5-flash-preview-04-17"
      },
      "credentials": {
        "googlePalmApi": {
          "id": "gKoqNwYHaqu5v6po",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "409972f9-cdce-4f99-9071-ee3171daf28c",
      "name": "Platzinformationen",
      "type": "n8n-nodes-base.googleSheetsTool",
      "position": [
        140,
        60
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1MhuNGRokppbeqwLx9aBjbh3CmZqwWlmJ7UZwU3jMjXs/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1MhuNGRokppbeqwLx9aBjbh3CmZqwWlmJ7UZwU3jMjXs",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1MhuNGRokppbeqwLx9aBjbh3CmZqwWlmJ7UZwU3jMjXs/edit?usp=drivesdk",
          "cachedResultName": "court info"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "VZfUluT9AxDUBYoj",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "605d1d9c-8164-4cd1-8c47-e1a13748ce31",
      "name": "Gmail",
      "type": "n8n-nodes-base.gmailTool",
      "position": [
        260,
        60
      ],
      "webhookId": "4b1cefb4-9d37-412a-a5f4-a1c47afa8843",
      "parameters": {
        "sendTo": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('To', ``, 'string') }}",
        "message": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('Message', ``, 'string') }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('Subject', ``, 'string') }}",
        "emailType": "text"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "BsRlq32Gqq1TxxG0",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "01d625c7-17e7-4cdc-9463-346eef53397e",
      "name": "Telegram",
      "type": "n8n-nodes-base.telegram",
      "position": [
        580,
        -160
      ],
      "webhookId": "4c856a8c-484d-43ad-8405-7136d637f499",
      "parameters": {
        "text": "={{ $json.output }}",
        "chatId": "={{ $('Telegram Trigger').item.json.message.chat.id }}",
        "additionalFields": {
          "appendAttribution": false
        }
      },
      "credentials": {
        "telegramApi": {
          "id": "uQzLbJvkCgXKAVZ1",
          "name": "Telegram account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "3cf98881-4c56-41fc-9025-96aee9ee6954",
      "name": "KI-Agent",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        52,
        -160
      ],
      "parameters": {
        "text": "={{ $json.finalPrompt }}",
        "options": {
          "systemMessage": "\n\nYou are a helpful Court Reservation Assistant for Black Ball Sporting Club. Your role is to help players book training courts through a simple, guided process. Keep responses concise and friendly.\n\n## Core Behavior\n\n- Always respond with complete, helpful messages\n- If you don't understand something, ask for clarification politely\n- Handle errors gracefully without technical jargon\n- Stay focused on court reservations\n\n## Reservation Process\n\n### 1. Welcome New Users\n\nWhen someone starts a conversation:\n\n- Greet them warmly\n- Explain you help with court bookings\n- Ask for their reservation details\n\n### 2. Collect Information\n\nAsk users to provide these details (they can send multiple messages):\n\n- **Date** (YYYY-MM-DD format, e.g., 2025-06-15)\n- **Full Name**\n- **Email Address**\n- **Court Number** (check available courts using the court info tool)\n- **Start Time** (24-hour format, e.g., 14:30)\n- **End Time** (24-hour format, e.g., 16:00)\n\n**Example:** \"I need: Date, Your Name, Email, Court Number, Start Time, and End Time. You can send these in separate messages or all together.\"\n\n### 3. Validation & Conflict Check\n\nBefore confirming any booking:\n\n- Verify date format is YYYY-MM-DD\n- Check start time is before end time\n- Ensure email looks valid (contains @ symbol)\n- Use the court confirmation tool to check for scheduling conflicts\n- If there's a conflict, suggest alternative times or courts\n\n### 4. Booking Confirmation\n\nOnce details are validated and no conflicts exist:\n\n- Save the reservation using the court confirmation tool\n- Send confirmation email using the Gmail tool\n- Confirm success to the user in Telegram\n\n## Error Handling\n\n**Invalid Formats:**\n\"I need the date as YYYY-MM-DD (like 2025-06-15) and times as HH:MM (like 14:30). Could you try again?\"\n\n**Time Conflicts:**\n\"Sorry, that time slot is already booked. Here are some available alternatives: [suggest 2-3 nearby time slots]\"\n\n**Missing Information:**\n\"I still need [list missing items]. Could you provide those details?\"\n\n**System Errors:**\n\"I'm having trouble accessing the booking system right now. Please try again in a moment.\"\n\n## Important Rules\n\n- Never send empty or incomplete responses\n- Always acknowledge what the user sent before asking for more info\n- If using tools fails, explain the issue simply\n- Offer helpful alternatives when possible\n- Keep the conversation moving forward\n- Don't repeat the same instructions multiple times\n\n## Tone\n\n- Friendly but professional\n- Encouraging and supportive\n- Clear and direct\n- Solution-focused"
        },
        "promptType": "define"
      },
      "typeVersion": 1.9
    },
    {
      "id": "f1aa7109-5ecc-433f-96a5-c65f80cca591",
      "name": "Google Tabellen",
      "type": "n8n-nodes-base.googleSheetsTool",
      "position": [
        380,
        60
      ],
      "parameters": {
        "columns": {
          "value": {
            "Date": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('Date__using_to_match_', ``, 'string') }}",
            "name": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('name', ``, 'string') }}",
            "court": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('court', ``, 'string') }}",
            "email": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('email', ``, 'string') }}",
            "end time": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('end_time', ``, 'string') }}",
            "confirmed": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('confirmed', ``, 'string') }}",
            "start time": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('start_time', ``, 'string') }}"
          },
          "schema": [
            {
              "id": "Date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "court",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "court",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "start time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "start time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "end time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "end time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "confirmed",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "confirmed",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Date"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1t4W_a2n6yveUCitx2Q2i5jNFMV08_Fos2xqpBAUnbPo/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1t4W_a2n6yveUCitx2Q2i5jNFMV08_Fos2xqpBAUnbPo",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1t4W_a2n6yveUCitx2Q2i5jNFMV08_Fos2xqpBAUnbPo/edit?usp=drivesdk",
          "cachedResultName": "court confirmations"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "VZfUluT9AxDUBYoj",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "718b57b6-15bb-47cb-8371-77c8e1997572",
      "name": "Prompt vorbereiten",
      "type": "n8n-nodes-base.aiTransform",
      "position": [
        -320,
        -160
      ],
      "parameters": {
        "jsCode": "const items = $input.all();\nconst today = new Date();\nconst formattedDate = `${today.toLocaleString(\"default\", { month: \"long\" })} ${today.getDate()}, ${today.getFullYear()}`;\n\nconst finalPrompt = items.map((item) => {\n  const telegramMessage = item?.json?.message?.text;\n  return {\n    finalPrompt: `Today's date is: ${formattedDate}\\n\\nUser's question:\\n${telegramMessage}`,\n  };\n});\n\nreturn finalPrompt;\n",
        "instructions": "Write code to:\n• Get today’s date formatted “Month Day, Year”\n• Extract the telegram message from chat.text\n• Build a field finalPrompt exactly as:\n\nvbnet\nCopy\nEdit\nToday's date is: [date]\n\n\n\nUser's question:\n[body]\n• Return finalPrompt only.",
        "codeGeneratedForPrompt": "Write code to:\n• Get today’s date formatted “Month Day, Year”\n• Extract the telegram message from chat.text\n• Build a field finalPrompt exactly as:\n\nvbnet\nCopy\nEdit\nToday's date is: [date]\n\n\n\nUser's question:\n[body]\n• Return finalPrompt only."
      },
      "typeVersion": 1
    },
    {
      "id": "04fbff80-7046-4530-948b-30c8cdacf77a",
      "name": "Postgres Chat Speicher",
      "type": "@n8n/n8n-nodes-langchain.memoryPostgresChat",
      "position": [
        20,
        60
      ],
      "parameters": {
        "sessionKey": "={{ $('Telegram Trigger').item.json.message.chat.id }}",
        "sessionIdType": "customKey",
        "contextWindowLength": 20
      },
      "credentials": {
        "postgres": {
          "id": "IFWkABdGl1rdn7hc",
          "name": "Postgres account"
        }
      },
      "typeVersion": 1.3
    },
    {
      "id": "53ebf4e3-b3dc-4160-afe7-ecfb7b353337",
      "name": "Notizzettel",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1080,
        -460
      ],
      "parameters": {
        "width": 360,
        "height": 1300,
        "content": "🧠 Purpose\nThis workflow powers a general-purpose reservation bot using Telegram, Google Sheets, and Email. Users send one message to request a reservation—it's validated against existing bookings, stored, and confirmed automatically.\n\n📥 Input Collected (in one go):\n\nDate\n\nName\n\nEmail\n\nResource (court, room, etc.)\n\nStart Time\n\nEnd Time\n\nConfirm (by replying \"yes\")\n\n🧩 Main Logic Flow\n\nTelegram Trigger → captures full request\n\nFunction → parses message fields\n\nGoogle Sheets → checks for availability\n\nDecision Node → validates time slot\n\nGoogle Sheets → appends confirmed data\n\nEmail Node → sends confirmation\n\nTelegram → replies with confirmation summary\n\n📄 Google Sheets Setup Required\n\nResource Info sheet (optional reference)\n\nReservation Log sheet with these headers:\n\n\nCopy\nEdit\nDate | Name | Email | Resource | Start Time | End Time | Status\n⚠️ Notes\n\nAll data is captured from a single user input (no multi-step flow).\n\nTime conflict logic uses date + resource + time slot.\n\nYou can rename \"Resource\" to anything (Room, Coach, Session, etc.)\n\n📬 Publisher Support\nNeed help customizing or deploying this bot?\n📧 tharwat.elsayed.hamad@gmail.com"
      },
      "typeVersion": 1
    }
  ],
  "active": true,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "4f5c1642-5090-42ab-aa01-54633931c7ee",
  "connections": {
    "605d1d9c-8164-4cd1-8c47-e1a13748ce31": {
      "ai_tool": [
        [
          {
            "node": "3cf98881-4c56-41fc-9025-96aee9ee6954",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "3cf98881-4c56-41fc-9025-96aee9ee6954": {
      "main": [
        [
          {
            "node": "01d625c7-17e7-4cdc-9463-346eef53397e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "409972f9-cdce-4f99-9071-ee3171daf28c": {
      "ai_tool": [
        [
          {
            "node": "3cf98881-4c56-41fc-9025-96aee9ee6954",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "Google Sheets": {
      "ai_tool": [
        [
          {
            "node": "3cf98881-4c56-41fc-9025-96aee9ee6954",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "718b57b6-15bb-47cb-8371-77c8e1997572": {
      "main": [
        [
          {
            "node": "3cf98881-4c56-41fc-9025-96aee9ee6954",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Telegram Trigger": {
      "main": [
        [
          {
            "node": "718b57b6-15bb-47cb-8371-77c8e1997572",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres Chat Memory": {
      "ai_memory": [
        [
          {
            "node": "3cf98881-4c56-41fc-9025-96aee9ee6954",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "Google Gemini Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "3cf98881-4c56-41fc-9025-96aee9ee6954",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    }
  }
}