实时航空公司时刻表监控和乘客警报系统

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

{
  "id": "OBiYfJPbWSpeTYaU",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281",
    "templateCredsSetupCompleted": true
  },
  "name": "Real-Time Airline Schedule Monitor & Passenger Alert System",
  "tags": [],
  "nodes": [
    {
      "id": "6f8a4ef8-4e58-4500-b546-693430ee840e",
      "name": "定时触发器",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1100,
        100
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "minutes",
              "minutesInterval": 30
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "fdc28d13-a8bc-47bc-a66b-c666272e37cb",
      "name": "Fetch Airline Data",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -880,
        100
      ],
      "parameters": {
        "url": "https://api.aviationstack.com/v1/flights",
        "options": {},
        "authentication": "genericCredentialType",
        "genericAuthType": "httpQueryAuth"
      },
      "credentials": {
        "httpQueryAuth": {
          "id": "xA2e6hA40RZ8bzrI",
          "name": "Query Auth account - test"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "f170d7aa-3158-48e0-8229-f9f8126ddc95",
      "name": "Get Current Schedules",
      "type": "n8n-nodes-base.postgres",
      "position": [
        -660,
        100
      ],
      "parameters": {
        "query": "SELECT flight_number, departure_time, arrival_time, status, gate, terminal FROM flight_schedules WHERE updated_at > NOW() - INTERVAL 1 HOUR",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "b6763438-4675-4127-afaa-8382872251fe",
      "name": "Process Changes",
      "type": "n8n-nodes-base.code",
      "position": [
        -440,
        100
      ],
      "parameters": {
        "jsCode": "// Compare current API data with database records\nconst apiData = $input.first().json.data;\nconst dbData = $input.last().json;\n\nconst changes = [];\nconst notifications = [];\n\n// Create a map of current DB flights for quick lookup\nconst dbFlights = new Map();\ndbData.forEach(flight => {\n  dbFlights.set(flight.flight_number, flight);\n});\n\n// Process each flight from API\napiData.forEach(apiFlight => {\n  const flightNumber = apiFlight.flight.iata;\n  const dbFlight = dbFlights.get(flightNumber);\n  \n  if (dbFlight) {\n    // Check for changes\n    const apiDeparture = new Date(apiFlight.departure.scheduled).toISOString();\n    const apiArrival = new Date(apiFlight.arrival.scheduled).toISOString();\n    const dbDeparture = new Date(dbFlight.departure_time).toISOString();\n    const dbArrival = new Date(dbFlight.arrival_time).toISOString();\n    \n    let hasChanges = false;\n    const changeDetails = {\n      flight_number: flightNumber,\n      changes: [],\n      severity: 'low'\n    };\n    \n    // Check departure time\n    if (apiDeparture !== dbDeparture) {\n      hasChanges = true;\n      const timeDiff = Math.abs(new Date(apiDeparture) - new Date(dbDeparture)) / (1000 * 60);\n      changeDetails.changes.push({\n        type: 'departure_time',\n        old_value: dbDeparture,\n        new_value: apiDeparture,\n        time_difference_minutes: Math.round(timeDiff)\n      });\n      if (timeDiff > 60) changeDetails.severity = 'high';\n      else if (timeDiff > 30) changeDetails.severity = 'medium';\n    }\n    \n    // Check arrival time\n    if (apiArrival !== dbArrival) {\n      hasChanges = true;\n      const timeDiff = Math.abs(new Date(apiArrival) - new Date(dbArrival)) / (1000 * 60);\n      changeDetails.changes.push({\n        type: 'arrival_time',\n        old_value: dbArrival,\n        new_value: apiArrival,\n        time_difference_minutes: Math.round(timeDiff)\n      });\n      if (timeDiff > 60) changeDetails.severity = 'high';\n      else if (timeDiff > 30) changeDetails.severity = 'medium';\n    }\n    \n    // Check status\n    if (apiFlight.flight_status !== dbFlight.status) {\n      hasChanges = true;\n      changeDetails.changes.push({\n        type: 'status',\n        old_value: dbFlight.status,\n        new_value: apiFlight.flight_status\n      });\n      if (['cancelled', 'diverted'].includes(apiFlight.flight_status)) {\n        changeDetails.severity = 'critical';\n      }\n    }\n    \n    // Check gate\n    if (apiFlight.departure.gate !== dbFlight.gate) {\n      hasChanges = true;\n      changeDetails.changes.push({\n        type: 'gate',\n        old_value: dbFlight.gate,\n        new_value: apiFlight.departure.gate\n      });\n    }\n    \n    if (hasChanges) {\n      changes.push({\n        ...changeDetails,\n        airline: apiFlight.airline.name,\n        route: `${apiFlight.departure.airport} → ${apiFlight.arrival.airport}`,\n        updated_at: new Date().toISOString()\n      });\n    }\n  }\n});\n\n// Generate notifications based on severity\nchanges.forEach(change => {\n  if (['high', 'critical'].includes(change.severity)) {\n    notifications.push({\n      type: 'urgent',\n      flight_number: change.flight_number,\n      message: `Flight ${change.flight_number} has ${change.severity} changes: ${change.changes.map(c => c.type).join(', ')}`,\n      severity: change.severity,\n      changes: change.changes,\n      timestamp: new Date().toISOString()\n    });\n  }\n});\n\nreturn [{\n  json: {\n    total_changes: changes.length,\n    changes: changes,\n    notifications: notifications,\n    processed_at: new Date().toISOString()\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "1383587e-cff6-4048-aaf4-e892e48ffcf8",
      "name": "Check for Changes",
      "type": "n8n-nodes-base.if",
      "position": [
        -220,
        100
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "has-changes",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $json.total_changes }}",
              "rightValue": 0
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "862d4239-2029-49e3-8772-ab6bce4a905f",
      "name": "Update Database",
      "type": "n8n-nodes-base.postgres",
      "position": [
        0,
        0
      ],
      "parameters": {
        "query": "UPDATE flight_schedules SET \n  departure_time = $1,\n  arrival_time = $2,\n  status = $3,\n  gate = $4,\n  terminal = $5,\n  updated_at = NOW()\nWHERE flight_number = $6",
        "options": {
          "queryBatching": "independently"
        },
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "e9946c82-6cb0-4ac8-b201-8698e853d828",
      "name": "Notify Slack Channel",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        220,
        0
      ],
      "parameters": {
        "url": "https://api.slack.com/api/chat.postMessage",
        "options": {},
        "sendBody": true,
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "channel",
              "value": "#flight-operations"
            },
            {
              "name": "text",
              "value": "🔄 *Flight Schedule Updates*\\n\\n{{ $json.total_changes }} flight(s) updated:\\n\\n{{ $json.changes.map(c => `✈️ *${c.flight_number}* (${c.airline})\\n   Route: ${c.route}\\n   Severity: ${c.severity.toUpperCase()}\\n   Changes: ${c.changes.map(ch => ch.type).join(', ')}\\n`).join('\\n') }}"
            }
          ]
        },
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "Bearer {{ $credentials.slack.token }}"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "KCqBydsOZHvzNKAI",
          "name": "Header Auth account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "2d4e1f9f-3b92-4ef4-b45d-93b06e4e759a",
      "name": "Check Urgent Notifications",
      "type": "n8n-nodes-base.if",
      "position": [
        0,
        200
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "has-notifications",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $json.notifications.length }}",
              "rightValue": 0
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "87962dd7-526b-4dc4-8f2e-b41e1fb607b7",
      "name": "Get Affected Passengers",
      "type": "n8n-nodes-base.postgres",
      "position": [
        220,
        300
      ],
      "parameters": {
        "query": "SELECT DISTINCT p.email, p.phone, p.name, t.flight_number \nFROM passengers p \nJOIN tickets t ON p.passenger_id = t.passenger_id \nWHERE t.flight_number = ANY($1) \nAND t.flight_date >= CURRENT_DATE \nAND t.status = 'confirmed'",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "0a4ed5f3-7456-46b6-b54e-9b8afdc8d436",
      "name": "Send Email Notifications",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        440,
        300
      ],
      "parameters": {
        "url": "https://api.sendgrid.com/v3/mail/send",
        "options": {
          "batching": {
            "batch": {
              "batchSize": 10
            }
          }
        },
        "sendBody": true,
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "personalizations",
              "value": "={{ [{ \"to\": [{ \"email\": $json.email, \"name\": $json.name }], \"dynamic_template_data\": { \"passenger_name\": $json.name, \"flight_number\": $json.flight_number, \"changes\": $('process-changes').item.json.notifications.find(n => n.flight_number === $json.flight_number).changes } }] }}"
            },
            {
              "name": "from",
              "value": "{ \"email\": \"noreply@airline.com\", \"name\": \"Airline Notifications\" }"
            },
            {
              "name": "template_id",
              "value": "d-flight-schedule-change"
            }
          ]
        },
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "Bearer {{ $credentials.sendgrid.api_key }}"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "KCqBydsOZHvzNKAI",
          "name": "Header Auth account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "d91cfbf5-0281-4566-b8bd-3f97ee7e7036",
      "name": "Send SMS (Critical Only)",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        660,
        300
      ],
      "parameters": {
        "url": "https://api.twilio.com/2010-04-01/Accounts/{{ $credentials.twilio.account_sid }}/Messages.json",
        "options": {
          "batching": {
            "batch": {
              "batchSize": 5,
              "batchInterval": 2000
            }
          }
        },
        "sendBody": true,
        "authentication": "genericCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "To",
              "value": "={{ $json.phone }}"
            },
            {
              "name": "From",
              "value": "{{ $credentials.twilio.phone_number }}"
            },
            {
              "name": "Body",
              "value": "🚨 URGENT: Flight {{ $json.flight_number }} schedule change. Check your email for details or visit our app. Changes: {{ $('process-changes').item.json.notifications.find(n => n.flight_number === $json.flight_number).changes.map(c => c.type).join(', ') }}"
            }
          ]
        },
        "genericAuthType": "httpBasicAuth"
      },
      "credentials": {
        "httpBasicAuth": {
          "id": "SS8MHWya3vb8KVFr",
          "name": "temporary cred"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "caf2c18a-4ae5-4cba-8b09-2ffce1fe0d98",
      "name": "Update Internal Systems",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        440,
        0
      ],
      "parameters": {
        "url": "https://hooks.zapier.com/hooks/catch/internal-system-webhook/",
        "options": {},
        "sendBody": true,
        "bodyParameters": {
          "parameters": [
            {
              "name": "event_type",
              "value": "flight_schedule_sync"
            },
            {
              "name": "total_changes",
              "value": "={{ $json.total_changes }}"
            },
            {
              "name": "changes",
              "value": "={{ $json.changes }}"
            },
            {
              "name": "sync_timestamp",
              "value": "={{ $json.processed_at }}"
            },
            {
              "name": "workflow_id",
              "value": "airline-schedule-sync"
            }
          ]
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "b81033e9-6098-49d3-8bf9-921d9a934ee3",
      "name": "Log Sync Activity",
      "type": "n8n-nodes-base.postgres",
      "position": [
        660,
        0
      ],
      "parameters": {
        "query": "INSERT INTO sync_logs (workflow_name, total_changes, sync_status, sync_timestamp, details) VALUES ('airline-schedule-sync', $1, 'completed', NOW(), $2)",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4Y4qEFGqF2krfRHZ",
          "name": "Postgres-test"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "e70dfc0b-2c01-4ef0-80d1-5374d21f1670",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1060,
        -480
      ],
      "parameters": {
        "width": 760,
        "height": 400,
        "content": "## **How It Works**\n\n1. **Schedule Trigger** - Automatically runs every 30 minutes to check for flight schedule updates\n2. **Fetch Airline Data** - Retrieves current flight information from aviation APIs\n3. **Get Current Schedules** - Pulls existing schedule data from the internal database\n4. **Process Changes** - Compares API data with database records to identify schedule changes\n5. **Check for Changes** - Determines if any updates require processing and notifications\n6. **Update Database** - Saves schedule changes to the internal flight database\n7. **Notify Slack Channel** - Sends operational updates to the flight operations team\n8. **Check Urgent Notifications** - Identifies critical changes requiring immediate passenger alerts\n9. **Get Affected Passengers** - Retrieves contact information for passengers on changed flights\n10. **Send Email Notifications** - Dispatches detailed schedule change emails via SendGrid\n11. **Send SMS (Critical Only)** - Sends urgent text alerts for cancellations and major delays\n12. **Update Internal Systems** - Syncs changes with other airline systems via webhooks\n13. **Log Sync Activity** - Records all synchronization activities for audit and monitoring\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2a9a267e-44f4-4a44-889f-63d544d38b8f",
      "name": "便签1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -80,
        -560
      ],
      "parameters": {
        "color": 3,
        "width": 520,
        "height": 180,
        "content": "**Introduction:**\n\nThis automated n8n workflow continuously monitors airline schedule changes by fetching real-time flight data, comparing it with stored schedules, and instantly notifying both internal teams and affected passengers through multiple communication channels. The system ensures stakeholders are immediately informed of any flight delays, cancellations, gate changes, or other critical updates."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "4c5fabca-33be-4659-9e71-582e03d38143",
  "connections": {
    "Process Changes": {
      "main": [
        [
          {
            "node": "Check for Changes",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Database": {
      "main": [
        [
          {
            "node": "Notify Slack Channel",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Fetch Airline Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check for Changes": {
      "main": [
        [
          {
            "node": "Update Database",
            "type": "main",
            "index": 0
          },
          {
            "node": "Check Urgent Notifications",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Get Current Schedules",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Airline Data": {
      "main": [
        [
          {
            "node": "Get Current Schedules",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Notify Slack Channel": {
      "main": [
        [
          {
            "node": "Update Internal Systems",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Current Schedules": {
      "main": [
        [
          {
            "node": "Process Changes",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Affected Passengers": {
      "main": [
        [
          {
            "node": "Send Email Notifications",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Internal Systems": {
      "main": [
        [
          {
            "node": "Log Sync Activity",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send Email Notifications": {
      "main": [
        [
          {
            "node": "Send SMS (Critical Only)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Urgent Notifications": {
      "main": [
        [
          {
            "node": "Get Affected Passengers",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Get Current Schedules",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}