Materialize

Sync custom events from Materialize to OneSignal to trigger automated Journeys and personalized messaging campaigns based on real-time user behavior.

Overview

The OneSignal + Materialize integration enables automatic syncing of custom events from your Materialize streaming database to OneSignal to trigger automated messaging campaigns and Journeys based on real-time user behavior.

Materialize is a PostgreSQL-compatible streaming database that maintains incrementally updated views of your data, enabling real-time analytics and event processing.

---

Requirements

• Access to Custom Events (currently in beta)
• Updated Account Plan (not available on free apps).

Materialize

• Materialize account with console access
• App Password for external tool authentication
• Materialized views or tables containing event data
• Event data accessible in your Materialize database

---

Setup

1. Get Materialize connection details

   Sign in to the Materialize console and navigate to the Connect page to find your connection details.

2. Create App Password

   In the Materialize console, create a new App Password for OneSignal to use for authentication.

3. Connect to OneSignal

   In OneSignal, go to Data > Integrations and click Add Integration.

   Select Materialize and provide:

   • Host: Your Materialize host name (found under External Tools in the Materialize console Connect page)
   • Username: Your email address (used to sign in to Materialize)
   • Password: The App Password created in Step 2
   • Database: Database name (optional, defaults to materialize)
4. Test connection

   Click Test to verify the connection is working correctly.

---

Event data mapping

Map your to OneSignal’s custom events format:

OneSignal Field	Description	Required
name	event_name	Event identifier	Yes
external_id	user_id	User identifier	Yes
timestamp	event_timestamp	When event occurred	No
properties	event_data	No

Example Real-time Event View

-- Real-time materialized view for recent events
CREATE MATERIALIZED VIEW analytics.recent_user_events AS
SELECT
    event_name,
    user_id,
    event_timestamp,
    event_properties,
    session_id,
    device_type
FROM raw_events.stream
WHERE event_timestamp >= NOW() - INTERVAL '1 day';

---

Processing Modes

Materialized Views (Recommended)

Leverage Materialize’s real-time processing by syncing from materialized views that automatically update as new data arrives:

-- High-value events materialized view
CREATE MATERIALIZED VIEW analytics.high_value_events AS
SELECT
    event_name,
    user_id,
    event_timestamp,
    event_properties || jsonb_build_object(
        'source', 'materialize',
        'value_tier', 'high'
    ) as event_properties
FROM raw_events.stream
WHERE (event_properties->>'value')::numeric > 100;

SQL Query Mode

Write custom PostgreSQL-compatible queries to transform your event data:

-- Real-time user activity summary
SELECT
    'activity_summary' as event_name,
    user_id,
    NOW() as event_timestamp,
    jsonb_build_object(
        'events_last_hour', COUNT(*),
        'unique_sessions', COUNT(DISTINCT session_id),
        'total_value', SUM((event_properties->>'value')::numeric),
        'last_seen', MAX(event_timestamp)
    ) as event_properties
FROM analytics.recent_user_events
WHERE event_timestamp >= NOW() - INTERVAL '1 hour'
GROUP BY user_id
HAVING COUNT(*) >= 5;

Real-time Stream Processing

-- Progressive profiling view
CREATE MATERIALIZED VIEW analytics.user_progression AS
SELECT
    user_id,
    COUNT(*) as total_events,
    COUNT(DISTINCT event_name) as unique_event_types,
    MAX(event_timestamp) as last_activity,
    CASE
        WHEN COUNT(*) >= 50 THEN 'power_user'
        WHEN COUNT(*) >= 20 THEN 'active_user'
        WHEN COUNT(*) >= 5 THEN 'engaged_user'
        ELSE 'new_user'
    END as user_segment
FROM raw_events.stream
GROUP BY user_id;

---

Limitations

• Materialize only supports the Basic Sync Engine
• Real-time queries may consume more compute resources
• Complex joins across large datasets should be optimized
• Materialized views require ongoing cluster resources

---

FAQ

How do I optimize real-time performance in Materialize?

Use indexes on frequently queried columns and consider partitioning large event datasets by time ranges for better performance.

Can I sync from both tables and materialized views?

Yes, OneSignal can read from both static tables and real-time materialized views in Materialize.

How does real-time syncing work?

Materialize maintains incrementally updated views, so OneSignal will always read the latest state of your data without additional processing overhead.

---