DirectFlow Documentation

Set up read-only PostgreSQL connection

Connect DirectFlow to your customer database for real-time audience targeting

Required Information:

• Host: Database server hostname or IP
• Port: Database port (default: 5432)
• Database Name: Target database name
• Username: Read-only database user
• Password: Securely stored in HashiCorp Vault

Create Read-Only User (PostgreSQL):

-- Create read-only user for DirectFlow
CREATE USER directflow_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE your_database TO directflow_readonly;
GRANT USAGE ON SCHEMA public TO directflow_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO directflow_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO directflow_readonly;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO directflow_readonly;

Define audience segments with SQL queries

Data sources define how to find and target your customers using three SQL queries

Three Required Queries:

1. Segment SQL (Discovery)

Shows available segments in the UI for marketers to choose from:

SELECT 
  id,
  community_name,
  user_count,
  plan_type
FROM communities 
WHERE active = true 
ORDER BY user_count DESC

2. Generator SQL (Audience)

Must return profile_id field plus contact info (email/phone):

SELECT 
  u.id AS profile_id,
  u.email,
  u.phone,
  u.first_name,
  u.last_name
FROM users u 
JOIN memberships m ON u.id = m.user_id 
WHERE m.community_id = '<%= community_id %>'
  AND u.email IS NOT NULL

3. Preload SQL (Personalization)

Gets additional data for template personalization:

SELECT 
  plan_name,
  last_login_at,
  total_purchases,
  preferred_language
FROM user_profiles 
WHERE user_id = '<%= profile_id %>'

Create conditional logic for automated flows

Conditions enable branching logic in marketing flows based on real-time data queries

Condition Types:

• Customer Database: Query your external database for real-time conditions
• DirectFlow Database: Built-in conditions for engagement tracking (handled automatically)

Customer Database Condition Example:

-- Check if user has premium subscription
SELECT 
  CASE 
    WHEN subscription_plan = 'premium' THEN true 
    ELSE false 
  END AS result
FROM users 
WHERE id = '<%= profile_id %>'
LIMIT 1

Supported PostgreSQL Features

DirectFlow supports advanced PostgreSQL features for complex audience targeting

Window Functions:

SELECT 
  u.id AS profile_id,
  u.email,
  ROW_NUMBER() OVER (PARTITION BY u.plan_type ORDER BY u.created_at) as user_rank
FROM users u
WHERE user_rank <= 100

JSON Operations:

SELECT 
  u.id AS profile_id,
  u.email,
  u.metadata->>'preferred_language' as language,
  (u.metadata->'settings'->>'notifications')::boolean as notifications_enabled
FROM users u
WHERE u.metadata ? 'preferences'

Date/Time Functions:

SELECT 
  u.id AS profile_id,
  u.email,
  DATE_TRUNC('month', u.last_login) as login_month
FROM users u
WHERE u.last_login > NOW() - INTERVAL '3 months'