预订后票价追踪器 – 通过Amadeus和Skyscanner的自动提醒和退款检查

• •PostgreSQL 数据库连接信息
• •可能需要目标 API 的认证凭证

{
  "id": "Imsit8rJhCw7DEeD",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281",
    "templateCredsSetupCompleted": true
  },
  "name": "预订后票价追踪器 – 通过 Amadeus 和 Skyscanner 的自动提醒和退款检查",
  "tags": [],
  "nodes": [
    {
      "id": "bd086b06-0407-47a6-8589-a3352a6d974d",
      "name": "票价检查触发器",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1760,
        100
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours",
              "hoursInterval": 6
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "cadfad91-fbb4-4067-9b11-e851e4961850",
      "name": "获取追踪的预订",
      "type": "n8n-nodes-base.postgres",
      "position": [
        -1540,
        100
      ],
      "parameters": {
        "query": "SELECT \n  b.booking_id,\n  b.passenger_name,\n  b.email,\n  b.phone,\n  b.flight_number,\n  b.departure_date,\n  b.origin,\n  b.destination,\n  b.airline,\n  b.booking_class,\n  b.original_fare,\n  b.booking_date,\n  b.confirmation_code,\n  b.tracking_enabled,\n  b.last_checked,\n  COALESCE(ft.lowest_fare, b.original_fare) as current_lowest_fare,\n  COALESCE(ft.fare_trend, 'stable') as trend\nFROM bookings b\nLEFT JOIN fare_tracking ft ON b.booking_id = ft.booking_id\nWHERE b.departure_date > CURRENT_DATE + INTERVAL '1 day'\n  AND b.departure_date <= CURRENT_DATE + INTERVAL '90 days'\n  AND b.tracking_enabled = true\n  AND b.booking_status = 'confirmed'\n  AND (b.last_checked IS NULL OR b.last_checked < NOW() - INTERVAL '6 hours')\nORDER BY b.departure_date ASC\nLIMIT 50",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "130d2cfc-85d9-4666-b456-28d6bdb3b82c",
      "name": "准备票价搜索",
      "type": "n8n-nodes-base.code",
      "position": [
        -1320,
        100
      ],
      "parameters": {
        "jsCode": "// Process bookings for fare tracking\nconst bookings = $input.all()[0].json;\nconst processedBookings = [];\n\nfor (const booking of bookings) {\n  // Format dates and create search parameters\n  const departureDate = new Date(booking.departure_date).toISOString().split('T')[0];\n  const searchParams = {\n    booking_id: booking.booking_id,\n    origin: booking.origin,\n    destination: booking.destination,\n    departure_date: departureDate,\n    airline: booking.airline,\n    flight_number: booking.flight_number,\n    booking_class: booking.booking_class,\n    original_fare: parseFloat(booking.original_fare),\n    passenger_name: booking.passenger_name,\n    email: booking.email,\n    phone: booking.phone,\n    confirmation_code: booking.confirmation_code,\n    current_lowest_fare: parseFloat(booking.current_lowest_fare || booking.original_fare),\n    booking_date: booking.booking_date,\n    last_checked: new Date().toISOString()\n  };\n  \n  processedBookings.push(searchParams);\n}\n\nreturn processedBookings.map(booking => ({ json: booking }));"
      },
      "typeVersion": 2
    },
    {
      "id": "c1f499d6-e616-4da3-a4dc-1bbaa7492048",
      "name": "搜索当前票价",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -1100,
        100
      ],
      "parameters": {
        "url": "https://api.amadeus.com/v2/shopping/flight-offers",
        "options": {
          "batching": {
            "batch": {
              "batchSize": 5,
              "batchInterval": 2000
            }
          }
        },
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "Bearer {{ $credentials.amadeus.access_token }}"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "KCqBydsOZHvzNKAI",
          "name": "Header Auth account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "480dfb10-9c5c-4685-9681-403a49bcc48f",
      "name": "搜索 Skyscanner 票价",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -1100,
        260
      ],
      "parameters": {
        "url": "https://api.skyscanner.com/browse/v1.0/US/USD/en-US/{{ $json.origin }}/{{ $json.destination }}/{{ $json.departure_date }}",
        "options": {
          "batching": {
            "batch": {
              "batchSize": 3,
              "batchInterval": 3000
            }
          }
        },
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "x-api-key",
              "value": "{{ $credentials.skyscanner.api_key }}"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "KCqBydsOZHvzNKAI",
          "name": "Header Auth account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "e7d9abc5-d372-48d9-821d-cc535e3be820",
      "name": "分析票价下降",
      "type": "n8n-nodes-base.code",
      "position": [
        -880,
        100
      ],
      "parameters": {
        "jsCode": "// Analyze fare data and detect drops\nconst bookingData = $('prepare-fare-search').item.json;\nconst amadeusFares = $('search-current-fares').item.json.data || [];\nconst skyscannerFares = $('search-skyscanner-fares').item.json.Quotes || [];\n\nconst originalFare = bookingData.original_fare;\nconst currentLowestFare = bookingData.current_lowest_fare;\nlet newLowestFare = originalFare;\nlet fareSource = 'original';\nlet availableFares = [];\n\n// Process Amadeus fares\namadeusFares.forEach(offer => {\n  if (offer.price && offer.price.total) {\n    const fare = parseFloat(offer.price.total);\n    availableFares.push({\n      source: 'amadeus',\n      fare: fare,\n      airline: offer.validatingAirlineCodes?.[0] || 'Unknown',\n      flight_number: offer.itineraries?.[0]?.segments?.[0]?.number || 'N/A',\n      booking_class: offer.travelerPricings?.[0]?.fareDetailsBySegment?.[0]?.class || 'Unknown'\n    });\n    \n    if (fare < newLowestFare) {\n      newLowestFare = fare;\n      fareSource = 'amadeus';\n    }\n  }\n});\n\n// Process Skyscanner fares\nskyscannerFares.forEach(quote => {\n  if (quote.MinPrice) {\n    const fare = parseFloat(quote.MinPrice);\n    availableFares.push({\n      source: 'skyscanner',\n      fare: fare,\n      airline: 'Various',\n      flight_number: 'Multiple',\n      booking_class: 'Economy'\n    });\n    \n    if (fare < newLowestFare) {\n      newLowestFare = fare;\n      fareSource = 'skyscanner';\n    }\n  }\n});\n\n// Calculate savings and determine action\nconst originalSavings = originalFare - newLowestFare;\nconst currentSavings = currentLowestFare - newLowestFare;\nconst savingsPercentage = ((originalFare - newLowestFare) / originalFare * 100).toFixed(1);\n\n// Determine fare trend\nlet trend = 'stable';\nif (newLowestFare < currentLowestFare * 0.95) trend = 'dropping';\nelse if (newLowestFare > currentLowestFare * 1.05) trend = 'rising';\n\n// Determine notification priority\nlet priority = 'low';\nlet actionRecommended = false;\n\nif (originalSavings >= 100 || savingsPercentage >= 15) {\n  priority = 'high';\n  actionRecommended = true;\n} else if (originalSavings >= 50 || savingsPercentage >= 8) {\n  priority = 'medium';\n  actionRecommended = true;\n}\n\n// Check airline-specific refund policies\nconst refundEligible = checkRefundEligibility(bookingData.airline, originalSavings, bookingData.booking_date);\n\nfunction checkRefundEligibility(airline, savings, bookingDate) {\n  const daysSinceBooking = Math.floor((new Date() - new Date(bookingDate)) / (1000 * 60 * 60 * 24));\n  \n  // Airline-specific policies (simplified)\n  const policies = {\n    'AA': { min_savings: 50, max_days: 24 }, // American Airlines\n    'DL': { min_savings: 75, max_days: 24 }, // Delta\n    'UA': { min_savings: 50, max_days: 24 }, // United\n    'SW': { min_savings: 25, max_days: 365 }, // Southwest (more flexible)\n    'JB': { min_savings: 50, max_days: 24 }, // JetBlue\n  };\n  \n  const policy = policies[airline] || { min_savings: 75, max_days: 24 };\n  \n  return savings >= policy.min_savings && daysSinceBooking <= policy.max_days;\n}\n\nreturn [{\n  json: {\n    ...bookingData,\n    new_lowest_fare: newLowestFare,\n    fare_source: fareSource,\n    original_savings: Math.round(originalSavings * 100) / 100,\n    current_savings: Math.round(currentSavings * 100) / 100,\n    savings_percentage: parseFloat(savingsPercentage),\n    fare_trend: trend,\n    priority: priority,\n    action_recommended: actionRecommended,\n    refund_eligible: refundEligible,\n    available_fares: availableFares.sort((a, b) => a.fare - b.fare).slice(0, 5),\n    check_timestamp: new Date().toISOString(),\n    days_until_departure: Math.ceil((new Date(bookingData.departure_date) - new Date()) / (1000 * 60 * 60 * 24))\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "bae0410f-8047-4da1-a6ee-fd65bc0c3e04",
      "name": "更新票价追踪",
      "type": "n8n-nodes-base.postgres",
      "position": [
        -660,
        100
      ],
      "parameters": {
        "query": "INSERT INTO fare_tracking \n  (booking_id, check_date, lowest_fare, fare_source, savings_amount, savings_percentage, fare_trend, priority_level, action_recommended, refund_eligible, available_fares_json)\nVALUES \n  ($1, NOW(), $2, $3, $4, $5, $6, $7, $8, $9, $10)\nON CONFLICT (booking_id) \nDO UPDATE SET \n  check_date = NOW(),\n  lowest_fare = $2,\n  fare_source = $3,\n  savings_amount = $4,\n  savings_percentage = $5,\n  fare_trend = $6,\n  priority_level = $7,\n  action_recommended = $8,\n  refund_eligible = $9,\n  available_fares_json = $10,\n  updated_at = NOW()",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "82e08313-a74f-43d2-8546-fadac8905ad4",
      "name": "更新预订状态",
      "type": "n8n-nodes-base.postgres",
      "position": [
        -440,
        100
      ],
      "parameters": {
        "query": "UPDATE bookings SET \n  last_checked = NOW(),\n  current_lowest_fare = $1\nWHERE booking_id = $2",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "84deb9be-4d7c-4a71-8d35-dd4b7de81250",
      "name": "检查是否需要通知",
      "type": "n8n-nodes-base.if",
      "position": [
        -220,
        100
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "has-significant-savings",
              "operator": {
                "type": "boolean",
                "operation": "equal"
              },
              "leftValue": "={{ $json.action_recommended }}",
              "rightValue": true
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "999513d1-0494-4555-8106-c5e6beff8e71",
      "name": "发送票价下降邮件",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        0,
        0
      ],
      "parameters": {
        "url": "https://api.sendgrid.com/v3/mail/send",
        "options": {},
        "sendBody": true,
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "personalizations",
              "value": "={{ [{ \"to\": [{ \"email\": $json.email, \"name\": $json.passenger_name }], \"dynamic_template_data\": { \"passenger_name\": $json.passenger_name, \"flight_number\": $json.flight_number, \"route\": $json.origin + \" to \" + $json.destination, \"departure_date\": $json.departure_date, \"original_fare\": $json.original_fare, \"new_lowest_fare\": $json.new_lowest_fare, \"savings_amount\": $json.original_savings, \"savings_percentage\": $json.savings_percentage, \"refund_eligible\": $json.refund_eligible, \"confirmation_code\": $json.confirmation_code, \"priority\": $json.priority, \"available_fares\": $json.available_fares, \"days_until_departure\": $json.days_until_departure } }] }}"
            },
            {
              "name": "from",
              "value": "{ \"email\": \"fare-alerts@flighttracker.com\", \"name\": \"Flight Fare Tracker\" }"
            },
            {
              "name": "template_id",
              "value": "d-fare-drop-alert"
            }
          ]
        },
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "Bearer {{ $credentials.sendgrid.api_key }}"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "KCqBydsOZHvzNKAI",
          "name": "Header Auth account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "cc92b941-e37f-4e2d-8f2b-e40eb5bc08ce",
      "name": "检查是否需要短信",
      "type": "n8n-nodes-base.if",
      "position": [
        0,
        200
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "is-high-priority",
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.priority }}",
              "rightValue": "high"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "4159d4b7-c371-49ca-8529-74c20f82b3ac",
      "name": "发送短信提醒",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        220,
        300
      ],
      "parameters": {
        "url": "https://api.twilio.com/2010-04-01/Accounts/{{ $credentials.twilio.account_sid }}/Messages.json",
        "options": {},
        "sendBody": true,
        "authentication": "genericCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "To",
              "value": "={{ $json.phone }}"
            },
            {
              "name": "From",
              "value": "{{ $credentials.twilio.phone_number }}"
            },
            {
              "name": "Body",
              "value": "🔥 FARE DROP ALERT! Your flight {{ $json.flight_number }} ({{ $json.departure_date }}) dropped by ${{ $json.original_savings }} ({{ $json.savings_percentage }}%). {{ $json.refund_eligible ? 'You may be eligible for a refund!' : 'Consider rebooking.' }} Check email for details."
            }
          ]
        },
        "genericAuthType": "httpBasicAuth"
      },
      "credentials": {
        "httpBasicAuth": {
          "id": "SS8MHWya3vb8KVFr",
          "name": "temporary cred"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "73850d25-94e4-4843-9781-7cba5dcb6b00",
      "name": "通知 Slack 团队",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        220,
        100
      ],
      "parameters": {
        "url": "https://api.slack.com/api/chat.postMessage",
        "options": {},
        "sendBody": true,
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "channel",
              "value": "#fare-alerts"
            },
            {
              "name": "text",
              "value": "💰 *High-Value Fare Drop Detected*\\n\\n✈️ *Flight:* {{ $json.flight_number }}\\n👤 *Passenger:* {{ $json.passenger_name }}\\n🛫 *Route:* {{ $json.origin }} → {{ $json.destination }}\\n📅 *Date:* {{ $json.departure_date }}\\n\\n💵 *Original Fare:* ${{ $json.original_fare }}\\n🔻 *New Low:* ${{ $json.new_lowest_fare }}\\n💸 *Savings:* ${{ $json.original_savings }} ({{ $json.savings_percentage }}%)\\n\\n{{ $json.refund_eligible ? '✅ *Refund Eligible*' : '❌ *Refund Not Available*' }}\\n🔔 *Priority:* {{ $json.priority.toUpperCase() }}"
            }
          ]
        },
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "Bearer {{ $credentials.slack.token }}"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "KCqBydsOZHvzNKAI",
          "name": "Header Auth account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "d1110183-fd41-478a-8a2f-3190a8a9578b",
      "name": "检查退款资格",
      "type": "n8n-nodes-base.if",
      "position": [
        440,
        100
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "refund-eligible",
              "operator": {
                "type": "boolean",
                "operation": "equal"
              },
              "leftValue": "={{ $json.refund_eligible }}",
              "rightValue": true
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "e0b7f2b0-b5d0-4b94-8eb3-17514aa61f02",
      "name": "启动退款流程",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        660,
        200
      ],
      "parameters": {
        "url": "https://api.airline-booking-system.com/refund/initiate",
        "options": {},
        "sendBody": true,
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "confirmation_code",
              "value": "={{ $json.confirmation_code }}"
            },
            {
              "name": "passenger_email",
              "value": "={{ $json.email }}"
            },
            {
              "name": "reason",
              "value": "fare_drop_detected"
            },
            {
              "name": "original_fare",
              "value": "={{ $json.original_fare }}"
            },
            {
              "name": "current_fare",
              "value": "={{ $json.new_lowest_fare }}"
            },
            {
              "name": "savings_amount",
              "value": "={{ $json.original_savings }}"
            },
            {
              "name": "automated_request",
              "value": true
            }
          ]
        },
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "Bearer {{ $credentials.airline_system.api_token }}"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "KCqBydsOZHvzNKAI",
          "name": "Header Auth account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "669f4e48-28cf-45f3-941d-d6c13da22990",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1520,
        -420
      ],
      "parameters": {
        "color": 5,
        "width": 740,
        "height": 380,
        "content": ""
      },
      "typeVersion": 1
    },
    {
      "id": "7e9c2094-55fb-4004-8f08-79ce3288cf00",
      "name": "便签1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -460,
        -300
      ],
      "parameters": {
        "width": 420,
        "height": 180,
        "content": "## 所需资源"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "8457773b-5566-49ec-9c37-a3f097504269",
  "connections": {
    "Notify Slack Team": {
      "main": [
        [
          {
            "node": "Check Refund Eligible",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Analyze Fare Drops": {
      "main": [
        [
          {
            "node": "Update Fare Tracking",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fare Check Trigger": {
      "main": [
        [
          {
            "node": "Get Tracked Bookings",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check if SMS Needed": {
      "main": [
        [
          {
            "node": "Send SMS Alert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare Fare Search": {
      "main": [
        [
          {
            "node": "Search Current Fares",
            "type": "main",
            "index": 0
          },
          {
            "node": "Search Skyscanner Fares",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Tracked Bookings": {
      "main": [
        [
          {
            "node": "Prepare Fare Search",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Search Current Fares": {
      "main": [
        [
          {
            "node": "Analyze Fare Drops",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Fare Tracking": {
      "main": [
        [
          {
            "node": "Update Booking Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Refund Eligible": {
      "main": [
        [
          {
            "node": "Initiate Refund Process",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Booking Status": {
      "main": [
        [
          {
            "node": "Check if Notification Needed",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Search Skyscanner Fares": {
      "main": [
        [
          {
            "node": "Analyze Fare Drops",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check if Notification Needed": {
      "main": [
        [
          {
            "node": "Send Fare Drop Email",
            "type": "main",
            "index": 0
          },
          {
            "node": "Check if SMS Needed",
            "type": "main",
            "index": 0
          },
          {
            "node": "Notify Slack Team",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}