Automating Airline Operations with Solace and RisingWave

Fahad Shah is a developer advocate with RisingWave Labs.

Implementing event-driven systems in the aviation industry can be complex, with traditional silos hindering real-time data processing and seamless information sharing across departments. It can be tricky to get data flowing between legacy and modern systems, making it difficult for airlines to respond promptly to key events such as flight status updates, boarding pass scans, and loyalty program milestones.

The Integration of Solace and RisingWave

The integration of Solace and RisingWave can power event-driven systems in aviation, enabling real-time connectivity and decision-making across the enterprise.

• Solace enables real-time connectivity and event-driven data movement across the enterprise. Its PubSub+ Platform provides a comprehensive solution for event streaming, management, and monitoring, equipping your organization with all the tools needed to design, deploy, and manage event-driven systems across cloud, on-premises, and IoT environments. By centralizing events on an “event mesh” powered by Solace’s platform, airlines can access and respond to events in real-time, improving operational efficiency and passenger experiences.
• RisingWave is a SQL data platform that is PostgreSQL-compatible and designed to build event-driven applications, real-time ETL pipelines, continuous analytics services, and feature stores for AI applications. RisingWave excels in extracting fresh and consistent insights from real-time event streams, database CDC, and time series data within sub-seconds. It unifies streaming and batch processing, enabling users to effortlessly ingest, join, and analyze both live and historical data at a cloud scale. With RisingWave, airlines can extract insights from streaming data, such as flight check-in updates or catering shortages, and drive real-time decision-making to enhance passenger experience and boost revenue.

Integration Scenarios Covered in this Article

In this blog post, I will explore two scenarios that demonstrate the benefits of integrating Solace and RisingWave:

1. Flight check-in notifications
2. Catering management alerts for meal shortages.

I will delve into the technical details of ingesting data from the departure control system (DCS) and catering management system (CMS) into the Solace event broker, and then processing it in RisingWave to create source tables and materialized views for continuous analytics. I will also cover how notifications are sent to Solace topics via the RisingWave sink connector for downstream applications, and how data is sent to Grafana for real-time dashboards, supporting informed decisions, operational efficiency, and an enhanced customer experience.

Setting the Stage

Set up Solace

Solace offers PubSub+ Platform, a comprehensive event-driven streaming solution for real-time enterprises, enabling the design, deployment, integration, and management of event-driven architectures (EDAs) across hybrid, multi-cloud, and IoT environments. It supports open protocols (MQTT, AMQP, JMS, REST) and provides micro-integrations that make it easy to connect legacy and SaaS applications, messaging services, databases, and AI agents to an event-driven layer, facilitating real-time data exchange.

To get started with the Solace PubSub+ Event Broker, you can either choose the free Software version using Docker or Solace PubSub+ Cloud. For more information, refer to the Solace documentation.

Solace PubSub+ Event Broker can receive message streams from DCS and CMS. Using the RisingWave MQTT source connector, you can read these data streams from Solace topics. Additionally, with the RisingWave MQTT sink connector, you can send the analyzed data back to Solace topics. This enables downstream applications and services to access these results from RisingWave and trigger the required notifications.

Ingest Data from Solace into RisingWave

We’ll use RisingWave to ingest and analyze the events. RisingWave offers two options: the open-source RisingWave and the managed service, RisingWave Cloud. I will focus here on using RisingWave Cloud, which offers a user-friendly experience and simplifies the operational aspects of managing and utilizing RisingWave for our solution for automating airline operations, specifically regarding check-in notifications and catering alerts.

Sign up for RisingWave Cloud

To create a RisingWave cluster in RisingWave Cloud and explore the various features it offers, you can sign up for the free plan available. The free plan allows you to test the functionalities of RisingWave without any cost.

RisingWave Cloud: Account Registration and Sign-In Process.

Create a RisingWave cluster

RisingWave supports various options for creating clusters, select the Developer cluster type that free tier for users.

• Cluster Name: Give your RisingWave cluster a unique name for identification.
• Cloud Provider: Select either AWS or Google Cloud for the RisingWave cluster.
• Region: Choose the region where your RisingWave cluster will be hosted.

Creating a RisingWave Cluster

Ingest Data Streams into RisingWave

Solace supports popular open protocols like AMQP, JMS, MQTT, REST, and WebSocket, and open APIs such as Paho and Qpid to enable users to interact with Solace PubSub+ Event Broker. This scenario will leverage the RisingWave MQTT connector to read and write data from the Solace event broker. With the RisingWave cluster up and running, navigate to the Workspace and connect to the data streams using the SQL statements to create tables, materialized views, and sinks.

Scenario 1: Automating Flight Check-in Notifications

Objective

Notify passengers precisely 48 hours before their flights’ departure that online check-in is open.

Problem Overview

Airlines must process continuous streams of flight and passenger data, sending “Check-in Open” notifications to passengers who have opted in for alerts. The goal is to automate this notification process:

• 72 hours before departure (D-72): Flight and passenger data begin arriving in the system.
• 48 hours before departure (D-48): A “Check-in Open” notification is triggered for eligible passengers.

Solution design

1. Event stream processing

The system receives continuous flight and passenger event streams via Solace from the Departure Control System (DCS). Each flight and passenger is tracked, and updates trigger real-time processing in RisingWave.

• Flight tracking: Unique flight identifiers are based on flight number, carrier code, flight date, and origin.
• Passenger tracking: Passengers are identified via a unique Passenger Reference Number (PRN).

2. Notification logic

Notifications are sent out 48 hours before a flight’s departure:

• Only passengers who have opted in to notifications receive them.
• Contact information is pulled from DCS, and enriched with passenger details before dispatching notifications.

Sample data: Flight and passenger details (Solace topic: passenger_full_details)

{
   "passenger_ref_number": "PRN026",
   "flight_id": "LH6456",
   "flight_number": "6456",
   "carrier_code": "LH",
   "flight_date": "2024-10-17",
   "origin": "LHR",
   "departure_time": "2024-10-17T04:40:00Z",
   "contact_info": "john.garcia@gmail.com",
   "opted_in": true
}

RisingWave Implementation

Step 1: Create a source table

This query creates a table named combined_passenger_flight_data to store detailed passenger and flight information. The data is sourced from the Solace topic passenger_full_details, connected through the Solace broker, with QoS level set to at least once and formatted as plain JSON.

CREATE TABLE combined_passenger_flight_data (
   flight_id VARCHAR,
   flight_number VARCHAR,
   carrier_code VARCHAR,
   flight_date DATE,
   origin VARCHAR,
   passenger_ref_number VARCHAR,
   departure_time TIMESTAMPTZ,
   opted_in BOOLEAN,
   contact_info VARCHAR
)
WITH (
   connector = 'mqtt',
   topic = 'passenger_full_details',
   url = 'ssl://xxxxxxxxxx:8883',
   username='solace-cloud-client',
   password='xxxxxxxxxxxx', 
   qos = 'at_least_once'
) FORMAT PLAIN ENCODE JSON;

Step 2: Materialized view to filter flights 48 hours before departure

This query creates a materialized view named checkin_open_notification that selects flight and passenger information for those who opted in and have flights departing within 48 to 72 hours from the current time.

CREATE MATERIALIZED VIEW checkin_open_notification AS
SELECT flight_id, passenger_ref_number, flight_number, carrier_code, departure_time, contact_info
FROM combined_passenger_flight_data
WHERE opted_in = TRUE
  AND departure_time <= NOW() - INTERVAL '48 hours'
  AND departure_time > NOW() - INTERVAL '72 hours';

Step 3: Create a sink to send notifications

This query creates a sink named checkin_notifications_sink, which streams data from the checkin_open_notification view to the Solace topic checkin_open_notification. The connection to the Solace server is established with at-least-once quality of service (QoS), and the data is formatted as plain JSON. The online check-in notification system then retrieves this information from the Solace topic to send notifications to the passengers.

Efficient Message Routing with Solace Using Dynamic and Hierarchical Topics: Solace supports dynamic and hierarchical topics, enabling efficient and flexible message routing. In this case, this capability is used to send notifications to the sub-topic flight_notifications/carrier_code. This structure ensures that notifications are dynamically routed based on the carrier code, such as AA (American Airlines), DL (Delta Airlines), or UA (United Airlines) etc.

Notifications are sent out 48 hours before a flight’s departure and are only delivered to passengers who have opted in to receive them. The contact information is fetched from DCS and enriched with passenger details before dispatching the notifications.

By leveraging this approach, you can ensure precise delivery of notifications, improve passenger engagement, and demonstrate the flexibility of Solace in handling complex, real-time messaging workflows.

CREATE SINK checkin_notifications_sink
FROM checkin_open_notification
WITH (
   connector = 'mqtt',
   topic = 'checkin_open_notification/carrier_code',
   url = 'ssl://xxxxxxxxxx:8883',
   username='solace-cloud-client',
   password='xxxxxxxxxxxx', 
   qos = 'at_least_once'
) FORMAT PLAIN ENCODE JSON;

Send the Data from RisingWave to Grafana for visualization

We’ll configure Grafana to read data from RisingWave and build visualizations. To utilize RisingWave as a data source in Grafana and create visualizations and dashboards, follow the instructions provided in Configure Grafana to read data from RisingWave.

Once the connection between RisingWave and Grafana is established, you can incorporate materialized views from RisingWave as tables to design charts and build a comprehensive dashboard.

The table chart lists passengers who opted in for notifications and have flights departing soon, showing flight_id, passenger_ref_number, flight_number, carrier_code, departure_time, and contact_info. It highlights passengers with flights departing before 48 from now, indicating that check-in is open.

Outcome

By using RisingWave, this system sends accurate, real-time notifications to passengers, enhancing the airline’s efficiency and customer experience.

Scenario 2: Catering Management Notifications for Meal Shortages

Objective

Notify the Catering Management System (CMS) when there aren’t enough meals for passengers based on check-ins.

Problem overview

To avoid meal shortages, the system needs to compare passenger check-ins against available meals and notify CMS in real-time:

• 72 hours before departure (D-72): Passenger check-ins begin arriving.
• 48 hours before departure (D-48): CMS sends data on meal availability.

When there is a meal shortage, the system must notify CMS. If additional meals become available or cancellations reduce the number of passengers, a “Sufficient Meals” notification is sent.

Solution design

1. Event stream processing

Continuous updates from the DCS (passenger check-ins) and CMS (meal availability) are ingested into Solace and then, read and correlated in RisingWave.

2. Correlating passenger check-ins with meal availability

• A “Meal Shortage” alert is triggered when 95% of passengers are checked in and there aren’t enough meals for any class (Economy, Business, First).
• If meal availability improves, a “Sufficient Meals” message is sent to CMS.

Sample data

Passenger Check-ins (Solace topic: passenger_checkin)

{
   "flight_id": "FL001",
   "flight_number": "AA123",
   "carrier_code": "AA",
   "flight_date": "2024-10-15T14:30:00",
   "passenger_ref_number": "PAX001",
   "checkin_time": "2024-10-13T10:00:00",
   "class": "Economy"
  }

Meal Availability (Solace topic:meal_availability)

This data tracks the number of meals available for each flight and class. It includes updates when the number of available meals changes.

{
   "flight_id": "FL002",
   "flight_number": "BA456",
   "carrier_code": "BA",
   "flight_date": "2024-10-16T09:00:00",
   "class": "Business",
   "available_meals": 20,
   "update_time": "2024-10-14T08:00:00"
  }

RisingWave Implementation

Step 1: Create a table for passenger check-ins

This query creates a table named passenger_checkin_data to store passenger check-in details, including flight_id, flight_number, carrier_code, flight_date, passenger_ref_number, checkin_time, and class. Data is sourced from the Solace topic passenger_checkin, with at least once QoS, and formatted as plain JSON.

CREATE TABLE passenger_checkin_data (
   flight_id VARCHAR,
   flight_number VARCHAR,
   carrier_code VARCHAR,
   flight_date DATE,
   passenger_ref_number VARCHAR,
   checkin_time TIMESTAMPTZ,
   class VARCHAR
) WITH (
   connector = 'mqtt',
   topic = 'passenger_checkin',
   url = 'ssl://xxxxxxxxxx:8883',
   username='solace-cloud-client',
   password='xxxxxxxxxxxx', 
   qos = 'at_least_once'
) FORMAT PLAIN ENCODE JSON;

Create a table for meal data

This query creates a table named meal_availability to store meal availability details per flight, including flight_id, flight_number, carrier_code, flight_date, class, available_meals, and update_time. Data is sourced from the MQTT topic meal_availability with at least once QoS, and formatted as plain JSON.

CREATE TABLE meal_availability (
   flight_id VARCHAR,
   flight_number VARCHAR,
   carrier_code VARCHAR,
   flight_date TIMESTAMP,
   class VARCHAR,
   available_meals INT,
   update_time TIMESTAMP
)
WITH (
   connector = 'MQTT',
   topic = 'meal_availability',
   url = 'ssl://xxxxxxxxxx:8883'
   username='solace-cloud-client',
   password='xxxxxxxxxxxx', 
   qos = 'at_least_once'
) FORMAT PLAIN ENCODE JSON;

Create a materialized view that correlates passenger check-ins with meal availability to compute whether there is a meal shortage.

CREATE MATERIALIZED VIEW flight_meal_status AS
SELECT
   p.flight_id,
   p.flight_number,
   p.class,
   COUNT(p.passenger_ref_number) AS checked_in_passengers,
   m.available_meals,
   CASE
     WHEN COUNT(p.passenger_ref_number) * 0.95 > m.available_meals THEN 'Meal Shortage'
     ELSE 'Sufficient Meals'
   END AS meal_status
FROM passenger_checkin AS p
LEFT JOIN meal_availability AS m
ON p.flight_id = m.flight_id AND p.class = m.class
GROUP BY p.flight_id, p.flight_number, p.class, m.available_meals;

Step 3: Filter and notify based on conditions

Create a materialized view to filter flights where 95% or more of passengers are checked in and the meal status is Meal Shortage.

CREATE MATERIALIZED VIEW meal_shortage_notification AS
SELECT
   flight_id,
   flight_number,
   class,
   checked_in_passengers,
   available_meals,
   meal_status
FROM flight_meal_status
WHERE meal_status = 'Meal Shortage';

This view continuously updates in real-time, tracking the meal status based on check-ins and available meals.

Step 3: Send alerts to CMS

This query creates an MQTT sink named meal_shortage_alert_sink, streaming data from the meal_shortage_alert view to the Solace topic meal_shortage_alert. It connects to the Solace server with at least once Quality of Service (QoS) and formats the data as plain JSON.

Efficient Message Routing with Solace Using Dynamic and Hierarchical Topics: Solace supports dynamic and hierarchical topics, allowing for efficient and flexible message routing. In this case, this feature is used to send data to the sub-topic meal_shortage_alert/class. This structure enables us to route messages dynamically based on the class of the passenger, such as Economy, Business, or First Class.

By utilizing this approach, you can ensure that alerts are appropriately categorized and delivered to the relevant systems or teams, streamlining the process of addressing meal shortages and enhancing operational efficiency. This method showcases the versatility of Solace in handling complex routing requirements in real-time messaging scenarios.

CREATE SINK meal_shortage_alert_sink
FROM meal_shortage_alert
WITH (
   connector = 'mqtt',
   topic = 'meal_shortage_alert/class',
   url = 'ssl://xxxxxxxxxx:8883',
   username='solace-cloud-client',
   password='xxxxxxxxxxxx', 
   qos = 'at_least_once'
) FORMAT PLAIN ENCODE JSON;

Send the data from RisingWave for visualization

We’ll use Grafana to read data from RisingWave and build visualizations. However, you can also use other BI tools, such as Metabase, Superset, or Looker, as RisingWave is PostgreSQL-compatible and supports all these tools. To utilize RisingWave as a data source in Grafana and create visualizations and dashboards, follow the instructions provided in Configure Grafana to read data from RisingWave.

Once the connection between RisingWave and Grafana is established, you can incorporate materialized views from RisingWave as tables to design charts and build a comprehensive dashboard.

The table chart displays flights with meal shortages, listing the flight_id, flight_number, class, number of checked_in_passengers, available_meals, and meal_status. Only flights where meal_status is marked as Meal Shortage are shown, providing a quick view of flights needing meal restocking.

Outcome

By integrating RisingWave and Solace, the system provides real-time alerts to CMS, ensuring that meal shortages are identified and addressed proactively.

Conclusion

In this blog post, I demonstrated how integrating Solace and RisingWave enables real-time event-driven architecture in the aviation industry. By walking through the setup process and using sample data and SQL queries, I showed how to unlock critical insights and automate notifications for flight check-in and meal shortage scenarios. This integration provides a solid foundation for airlines to build on, allowing them to take advantage of the benefits of event-driven architecture and make data-driven decisions in real-time.

You can create a RisingWave cluster in RisingWave Cloud and explore the various features it offers, you can sign up for the free plan available. For detailed instructions on how to create a RisingWave cluster and get started, you can refer to the official RisingWave documentation. It will provide you with step-by-step guidance to set up and explore the features of RisingWave.  If you need additional help with setting up this integration, join our active Slack community.

Acknowledgments

Thanks to Giri Venkatesan from Solace, who provided valuable feedback and suggestions on this post.

About the Author

Fahad Shah

A developer advocate with RisingWave Labs, Fahad is passionate about transforming real-time data into actionable insights and driving decisions that empower businesses. His core focus lies in stream processing, real-time data analytics, and industrial IoT, with a passion for real-time machine learning systems and exploring data-intensive systems in general.