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

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 FieldDescriptionRequired
nameevent_nameEvent identifierYes
external_iduser_idUser identifierYes
timestampevent_timestampWhen event occurredNo
propertiesevent_dataNo

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

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.