Verfolgung von Rohstoffpreistrends über API, Datenbanken und Slack mit intelligenten Einkaufsempfehlungen

• •PostgreSQL-Datenbankverbindungsdaten
• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich

{
  "id": "wOBVDQjh99n0BuRm",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281",
    "templateCredsSetupCompleted": true
  },
  "name": "Track Ingredient Price Trends & Get Smart Buying Suggestions via API, Database & Slack",
  "tags": [],
  "nodes": [
    {
      "id": "192d198b-cdfe-4356-a0f3-173f0ec4d5c5",
      "name": "Tägliche Preisprüfung",
      "type": "n8n-nodes-base.cron",
      "position": [
        0,
        100
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "c97114c9-4e78-4e13-94a2-1b4442b1e8da",
      "name": "Fetch API Prices",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        220,
        0
      ],
      "parameters": {
        "url": "https://api.example-food-prices.com/ingredients",
        "options": {}
      },
      "typeVersion": 4.1
    },
    {
      "id": "a55e9aa2-4641-4f39-9e8c-e6b6498df4d3",
      "name": "Preisdaten speichern",
      "type": "n8n-nodes-base.postgres",
      "position": [
        440,
        100
      ],
      "parameters": {
        "table": "price_history",
        "schema": {
          "__rl": true,
          "mode": "list",
          "value": "public"
        },
        "columns": {
          "0": "i",
          "1": "n",
          "2": "g",
          "3": "r",
          "4": "e",
          "5": "d",
          "6": "i",
          "7": "e",
          "8": "n",
          "9": "t",
          "10": ",",
          "11": " ",
          "12": "p",
          "13": "r",
          "14": "i",
          "15": "c",
          "16": "e",
          "17": ",",
          "18": " ",
          "19": "u",
          "20": "n",
          "21": "i",
          "22": "t",
          "23": ",",
          "24": " ",
          "25": "s",
          "26": "u",
          "27": "p",
          "28": "p",
          "29": "l",
          "30": "i",
          "31": "e",
          "32": "r",
          "33": ",",
          "34": " ",
          "35": "t",
          "36": "i",
          "37": "m",
          "38": "e",
          "39": "s",
          "40": "t",
          "41": "a",
          "42": "m",
          "43": "p",
          "44": ",",
          "45": " ",
          "46": "c",
          "47": "r",
          "48": "e",
          "49": "a",
          "50": "t",
          "51": "e",
          "52": "d",
          "53": "_",
          "54": "a",
          "55": "t",
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {}
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "448ff71f-7fee-40d8-a733-0f3d426cd8a2",
      "name": "Trends berechnen",
      "type": "n8n-nodes-base.postgres",
      "position": [
        660,
        100
      ],
      "parameters": {
        "query": "WITH price_trends AS (\n  SELECT \n    ingredient,\n    price,\n    timestamp,\n    LAG(price) OVER (PARTITION BY ingredient ORDER BY timestamp) as prev_price,\n    LAG(timestamp) OVER (PARTITION BY ingredient ORDER BY timestamp) as prev_timestamp\n  FROM price_history\n  WHERE timestamp >= NOW() - INTERVAL '30 days'\n)\nSELECT \n  ingredient,\n  price as current_price,\n  prev_price,\n  CASE \n    WHEN prev_price IS NULL THEN 0\n    ELSE ((price - prev_price) / prev_price) * 100\n  END as price_change_percent,\n  timestamp,\n  CASE \n    WHEN price < prev_price THEN 'DECREASING'\n    WHEN price > prev_price THEN 'INCREASING'\n    ELSE 'STABLE'\n  END as trend\nFROM price_trends\nWHERE prev_price IS NOT NULL\nORDER BY ingredient, timestamp DESC;",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "61a33bca-5a1b-4525-b15a-67493c313613",
      "name": "Empfehlungen generieren",
      "type": "n8n-nodes-base.code",
      "position": [
        880,
        100
      ],
      "parameters": {
        "jsCode": "// Analyze trends and generate buying recommendations\nconst items = $input.all();\nconst recommendations = [];\n\nfor (const item of items) {\n  const data = item.json;\n  let recommendation = {\n    ingredient: data.ingredient,\n    current_price: data.current_price,\n    price_change_percent: data.price_change_percent,\n    trend: data.trend,\n    recommendation: '',\n    urgency: '',\n    reason: ''\n  };\n  \n  // Generate recommendations based on price trends\n  if (data.price_change_percent < -10) {\n    recommendation.recommendation = 'BUY NOW';\n    recommendation.urgency = 'HIGH';\n    recommendation.reason = `Price dropped by ${Math.abs(data.price_change_percent).toFixed(1)}% - excellent buying opportunity`;\n  } else if (data.price_change_percent < -5) {\n    recommendation.recommendation = 'CONSIDER BUYING';\n    recommendation.urgency = 'MEDIUM';\n    recommendation.reason = `Price decreased by ${Math.abs(data.price_change_percent).toFixed(1)}% - good time to stock up`;\n  } else if (data.price_change_percent > 15) {\n    recommendation.recommendation = 'AVOID BUYING';\n    recommendation.urgency = 'HIGH';\n    recommendation.reason = `Price increased by ${data.price_change_percent.toFixed(1)}% - wait for better prices`;\n  } else if (data.price_change_percent > 5) {\n    recommendation.recommendation = 'WAIT';\n    recommendation.urgency = 'MEDIUM';\n    recommendation.reason = `Price increased by ${data.price_change_percent.toFixed(1)}% - consider delaying purchase`;\n  } else {\n    recommendation.recommendation = 'MONITOR';\n    recommendation.urgency = 'LOW';\n    recommendation.reason = 'Price stable - normal purchasing timing';\n  }\n  \n  recommendations.push(recommendation);\n}\n\nreturn recommendations.map(rec => ({ json: rec }));"
      },
      "typeVersion": 2
    },
    {
      "id": "c1d236c2-059c-48da-a767-bb4d18772c05",
      "name": "Empfehlungen speichern",
      "type": "n8n-nodes-base.postgres",
      "position": [
        1100,
        100
      ],
      "parameters": {
        "table": "buying_recommendations",
        "schema": {
          "__rl": true,
          "mode": "list",
          "value": "public"
        },
        "columns": {
          "0": "i",
          "1": "n",
          "2": "g",
          "3": "r",
          "4": "e",
          "5": "d",
          "6": "i",
          "7": "e",
          "8": "n",
          "9": "t",
          "10": ",",
          "11": " ",
          "12": "c",
          "13": "u",
          "14": "r",
          "15": "r",
          "16": "e",
          "17": "n",
          "18": "t",
          "19": "_",
          "20": "p",
          "21": "r",
          "22": "i",
          "23": "c",
          "24": "e",
          "25": ",",
          "26": " ",
          "27": "p",
          "28": "r",
          "29": "i",
          "30": "c",
          "31": "e",
          "32": "_",
          "33": "c",
          "34": "h",
          "35": "a",
          "36": "n",
          "37": "g",
          "38": "e",
          "39": "_",
          "40": "p",
          "41": "e",
          "42": "r",
          "43": "c",
          "44": "e",
          "45": "n",
          "46": "t",
          "47": ",",
          "48": " ",
          "49": "t",
          "50": "r",
          "51": "e",
          "52": "n",
          "53": "d",
          "54": ",",
          "55": " ",
          "56": "r",
          "57": "e",
          "58": "c",
          "59": "o",
          "60": "m",
          "61": "m",
          "62": "e",
          "63": "n",
          "64": "d",
          "65": "a",
          "66": "t",
          "67": "i",
          "68": "o",
          "69": "n",
          "70": ",",
          "71": " ",
          "72": "u",
          "73": "r",
          "74": "g",
          "75": "e",
          "76": "n",
          "77": "c",
          "78": "y",
          "79": ",",
          "80": " ",
          "81": "r",
          "82": "e",
          "83": "a",
          "84": "s",
          "85": "o",
          "86": "n",
          "87": ",",
          "88": " ",
          "89": "g",
          "90": "e",
          "91": "n",
          "92": "e",
          "93": "r",
          "94": "a",
          "95": "t",
          "96": "e",
          "97": "d",
          "98": "_",
          "99": "a",
          "100": "t",
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {}
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "298a4e63-584c-472a-af2b-320cc5278529",
      "name": "Dashboard-Daten abrufen",
      "type": "n8n-nodes-base.postgres",
      "position": [
        1320,
        100
      ],
      "parameters": {
        "query": "SELECT \n  ingredient,\n  current_price,\n  price_change_percent,\n  trend,\n  recommendation,\n  urgency,\n  reason,\n  generated_at\nFROM buying_recommendations \nWHERE generated_at >= CURRENT_DATE\nORDER BY \n  CASE urgency \n    WHEN 'HIGH' THEN 1 \n    WHEN 'MEDIUM' THEN 2 \n    WHEN 'LOW' THEN 3 \n  END,\n  price_change_percent ASC;",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "656ed3fc-386b-4fb1-b34e-a558ad8a3edf",
      "name": "Dashboard-HTML generieren",
      "type": "n8n-nodes-base.code",
      "position": [
        1540,
        100
      ],
      "parameters": {
        "jsCode": "// Create HTML dashboard\nconst items = $input.all();\nconst data = items.map(item => item.json);\n\nconst html = `\n<!DOCTYPE html>\n<html>\n<head>\n    <title>Price Fluctuation Dashboard</title>\n    <style>\n        body { font-family: Arial, sans-serif; margin: 20px; background-color: #f5f5f5; }\n        .container { max-width: 1200px; margin: 0 auto; }\n        .header { background: #2c3e50; color: white; padding: 20px; border-radius: 8px; margin-bottom: 20px; }\n        .card { background: white; padding: 20px; margin: 10px 0; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }\n        .high-urgency { border-left: 5px solid #e74c3c; }\n        .medium-urgency { border-left: 5px solid #f39c12; }\n        .low-urgency { border-left: 5px solid #27ae60; }\n        .price-info { display: flex; justify-content: space-between; align-items: center; }\n        .price-change { font-weight: bold; }\n        .positive { color: #e74c3c; }\n        .negative { color: #27ae60; }\n        .recommendation { font-size: 1.1em; font-weight: bold; }\n        .buy-now { color: #27ae60; }\n        .consider-buying { color: #f39c12; }\n        .wait, .avoid-buying { color: #e74c3c; }\n        .monitor { color: #3498db; }\n        .summary { display: grid; grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); gap: 20px; margin-bottom: 20px; }\n        .stat-card { background: #3498db; color: white; text-align: center; padding: 20px; border-radius: 8px; }\n    </style>\n</head>\n<body>\n    <div class=\"container\">\n        <div class=\"header\">\n            <h1>🍽️ Price Fluctuation Dashboard</h1>\n            <p>Food Industry Ingredient Price Monitoring & Buying Recommendations</p>\n            <p>Last Updated: ${new Date().toLocaleString()}</p>\n        </div>\n        \n        <div class=\"summary\">\n            <div class=\"stat-card\">\n                <h3>${data.filter(d => d.urgency === 'HIGH').length}</h3>\n                <p>High Priority Items</p>\n            </div>\n            <div class=\"stat-card\">\n                <h3>${data.filter(d => d.recommendation === 'BUY NOW').length}</h3>\n                <p>Buy Now Opportunities</p>\n            </div>\n            <div class=\"stat-card\">\n                <h3>${data.filter(d => d.trend === 'DECREASING').length}</h3>\n                <p>Decreasing Prices</p>\n            </div>\n            <div class=\"stat-card\">\n                <h3>${data.filter(d => d.trend === 'INCREASING').length}</h3>\n                <p>Increasing Prices</p>\n            </div>\n        </div>\n        \n        <div class=\"recommendations\">\n            ${data.map(item => `\n                <div class=\"card ${item.urgency.toLowerCase()}-urgency\">\n                    <div class=\"price-info\">\n                        <div>\n                            <h3>${item.ingredient}</h3>\n                            <p class=\"recommendation ${item.recommendation.toLowerCase().replace(/\\s+/g, '-')}\">\n                                ${item.recommendation}\n                            </p>\n                        </div>\n                        <div>\n                            <div>Current Price: $${item.current_price}</div>\n                            <div class=\"price-change ${item.price_change_percent < 0 ? 'negative' : 'positive'}\">\n                                ${item.price_change_percent > 0 ? '+' : ''}${item.price_change_percent.toFixed(1)}%\n                            </div>\n                            <div>Trend: ${item.trend}</div>\n                        </div>\n                    </div>\n                    <div style=\"margin-top: 10px; padding: 10px; background: #f8f9fa; border-radius: 4px;\">\n                        <strong>Reason:</strong> ${item.reason}\n                    </div>\n                    <div style=\"margin-top: 5px; font-size: 0.9em; color: #666;\">\n                        <strong>Urgency:</strong> ${item.urgency}\n                    </div>\n                </div>\n            `).join('')}\n        </div>\n    </div>\n</body>\n</html>\n`;\n\nreturn [{ json: { html: html } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "804a042f-f37f-426e-8f0f-334b8358a34e",
      "name": "E-Mail-Bericht senden",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        1760,
        0
      ],
      "webhookId": "1bb53760-d90c-4bea-b80f-56f0a767eefb",
      "parameters": {
        "options": {
          "attachments": "data:text/html;base64,{{ $json.html | base64 }}"
        },
        "subject": "Daily Price Fluctuation Report - {{ $now.format('YYYY-MM-DD') }}",
        "toEmail": "abc@gmail.com",
        "fromEmail": "xyz@gmail.com"
      },
      "credentials": {
        "smtp": {
          "id": "3QSx1pWoS0BZcK4c",
          "name": "SMTP account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "357cc9b4-9d9c-4e25-b5b4-8cf9c233e2ed",
      "name": "Send Slack Alert",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1760,
        200
      ],
      "parameters": {
        "url": "https://hooks.slack.com/services/YOUR/SLACK/WEBHOOK",
        "options": {},
        "sendBody": true,
        "bodyParameters": {
          "parameters": [
            {
              "name": "text",
              "value": "🍽️ Daily Price Update: {{ $('Get Dashboard Data').all().filter(item => item.json.urgency === 'HIGH').length }} high priority items need attention!"
            },
            {
              "name": "attachments",
              "value": "={{ $('Get Dashboard Data').all().filter(item => item.json.urgency === 'HIGH').map(item => ({ color: item.json.recommendation === 'BUY NOW' ? 'good' : 'danger', fields: [{ title: item.json.ingredient, value: `${item.json.recommendation} - ${item.json.reason}`, short: false }] })) }}"
            }
          ]
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "5aef607f-be87-4792-a423-e17cc4410e02",
      "name": "Datenbank einrichten",
      "type": "n8n-nodes-base.postgres",
      "position": [
        220,
        200
      ],
      "parameters": {
        "query": "CREATE TABLE IF NOT EXISTS price_history (\n  id SERIAL PRIMARY KEY,\n  ingredient VARCHAR(100) NOT NULL,\n  price DECIMAL(10,2) NOT NULL,\n  unit VARCHAR(50) NOT NULL,\n  supplier VARCHAR(100),\n  timestamp TIMESTAMP NOT NULL,\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE IF NOT EXISTS buying_recommendations (\n  id SERIAL PRIMARY KEY,\n  ingredient VARCHAR(100) NOT NULL,\n  current_price DECIMAL(10,2) NOT NULL,\n  price_change_percent DECIMAL(5,2),\n  trend VARCHAR(20),\n  recommendation VARCHAR(50),\n  urgency VARCHAR(20),\n  reason TEXT,\n  generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE INDEX IF NOT EXISTS idx_price_history_ingredient_timestamp ON price_history(ingredient, timestamp);\nCREATE INDEX IF NOT EXISTS idx_recommendations_generated_at ON buying_recommendations(generated_at);",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "d84be769-2983-45a2-854c-d7bd972ecc36",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        380,
        -560
      ],
      "parameters": {
        "width": 1000,
        "height": 520,
        "content": "## 📌 Workflow Overview: Price Fluctuation Dashboard\n\n- Daily Price Check – Triggers the workflow daily to monitor price changes.\n\n- Fetch API Prices – Retrieves the latest prices from an external API.\n\n- Setup Database – Ensures database tables are ready before inserting data.\n\n- Store Price Data – Saves current prices to the database for tracking.\n\n- Calculate Trends – Analyzes historical prices to detect patterns.\n\n- Generate Recommendations – Suggests actions based on price trends (e.g., buy/sell).\n\n- Store Recommendations – Saves recommendations for future reporting.\n\n- Get Dashboard Data – Gathers the necessary data for dashboard generation.\n\n- Generate Dashboard HTML – Builds an HTML dashboard to visualize insights.\n\n- Send Email Report – Emails the dashboard report to users.\n\n- Send Slack Alert – Sends key alerts or recommendations to Slack channels."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "38fb136e-f3ea-4656-91fd-ecd473469990",
  "connections": {
    "5aef607f-be87-4792-a423-e17cc4410e02": {
      "main": [
        [
          {
            "node": "a55e9aa2-4641-4f39-9e8c-e6b6498df4d3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "448ff71f-7fee-40d8-a733-0f3d426cd8a2": {
      "main": [
        [
          {
            "node": "61a33bca-5a1b-4525-b15a-67493c313613",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c97114c9-4e78-4e13-94a2-1b4442b1e8da": {
      "main": [
        [
          {
            "node": "a55e9aa2-4641-4f39-9e8c-e6b6498df4d3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a55e9aa2-4641-4f39-9e8c-e6b6498df4d3": {
      "main": [
        [
          {
            "node": "448ff71f-7fee-40d8-a733-0f3d426cd8a2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "192d198b-cdfe-4356-a0f3-173f0ec4d5c5": {
      "main": [
        [
          {
            "node": "c97114c9-4e78-4e13-94a2-1b4442b1e8da",
            "type": "main",
            "index": 0
          },
          {
            "node": "5aef607f-be87-4792-a423-e17cc4410e02",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "298a4e63-584c-472a-af2b-320cc5278529": {
      "main": [
        [
          {
            "node": "656ed3fc-386b-4fb1-b34e-a558ad8a3edf",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c1d236c2-059c-48da-a767-bb4d18772c05": {
      "main": [
        [
          {
            "node": "298a4e63-584c-472a-af2b-320cc5278529",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "656ed3fc-386b-4fb1-b34e-a558ad8a3edf": {
      "main": [
        [
          {
            "node": "804a042f-f37f-426e-8f0f-334b8358a34e",
            "type": "main",
            "index": 0
          },
          {
            "node": "357cc9b4-9d9c-4e25-b5b4-8cf9c233e2ed",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "61a33bca-5a1b-4525-b15a-67493c313613": {
      "main": [
        [
          {
            "node": "c1d236c2-059c-48da-a767-bb4d18772c05",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}