Real-time flight tracking with Aviation API, Slack & SendGrid/Twilio alerts

This 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. Good to Know Flight data accuracy depends on the aviation API provider's update frequency and reliability Critical notifications (cancellations, major delays) trigger immediate passenger alerts via SMS and email Internal Slack notifications keep operations teams informed in real-time Database logging maintains a complete audit trail of all schedule changes The system processes only confirmed schedule changes to avoid false notifications Passenger notifications are sent only to those with confirmed tickets for affected flights How It Works 1. Schedule Trigger - Automatically runs every 30 minutes to check for flight schedule updates 2. Fetch Airline Data - Retrieves current flight information from aviation APIs 3. Get Current Schedules - Pulls existing schedule data from the internal database 4. Process Changes - Compares API data with database records to identify schedule changes 5. Check for Changes - Determines if any updates require processing and notifications 6. Update Database - Saves schedule changes to the internal flight database 7. Notify Slack Channel - Sends operational updates to the flight operations team 8. Check Urgent Notifications - Identifies critical changes requiring immediate passenger alerts 9. Get Affected Passengers - Retrieves contact information for passengers on changed flights 10. Send Email Notifications - Dispatches detailed schedule change emails via SendGrid 11. Send SMS (Critical Only) - Sends urgent text alerts for cancellations and major delays 12. Update Internal Systems - Syncs changes with other airline systems via webhooks 13. Log Sync Activity - Records all synchronization activities for audit and monitoring Data Sources The workflow integrates with multiple data sources and systems: 1. Aviation API (Primary Data Source) Real-time flight status and schedule data Departure/arrival times, gates, terminals Flight status (on-time, delayed, cancelled, diverted) Aircraft and route information 2. Internal Flight Database flightschedules table - Current schedule data with columns: flightnumber (text) - Flight identifier (e.g., "AA123") departuretime (timestamp) - Scheduled departure time arrivaltime (timestamp) - Scheduled arrival time status (text) - Flight status (active, delayed, cancelled, diverted) gate (text) - Departure gate number terminal (text) - Terminal identifier airlinecode (text) - Airline IATA code originairport (text) - Departure airport code destinationairport (text) - Arrival airport code aircrafttype (text) - Aircraft model updatedat (timestamp) - Last update timestamp createdat (timestamp) - Record creation timestamp passengers table - Passenger contact information with columns: passengerid (integer) - Unique passenger identifier name (text) - Full passenger name email (text) - Email address for notifications phone (text) - Mobile phone number for SMS alerts notificationpreferences (json) - Communication preferences createdat (timestamp) - Registration timestamp updatedat (timestamp) - Last profile update tickets table - Booking and ticket status with columns: ticketid (integer) - Unique ticket identifier passengerid (integer) - Foreign key to passengers table flightnumber (text) - Flight identifier flightdate (date) - Travel date seatnumber (text) - Assigned seat ticketstatus (text) - Status (confirmed, cancelled, checked-in) bookingreference (text) - Booking confirmation code fareclass (text) - Ticket class (economy, business, first) createdat (timestamp) - Booking timestamp updatedat (timestamp) - Last modification timestamp synclogs table - Audit trail and system logs with columns: logid