-- Create users table
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  glue_user_id TEXT UNIQUE NOT NULL,
  email TEXT,
  traits JSONB DEFAULT '{}'::jsonb,
  linked_ids TEXT[] DEFAULT ARRAY[]::TEXT[],
  first_seen TIMESTAMPTZ DEFAULT NOW(),
  last_seen TIMESTAMPTZ DEFAULT NOW(),
  first_touch_source TEXT,
  first_touch_medium TEXT,
  first_touch_campaign TEXT,
  last_touch_source TEXT,
  last_touch_medium TEXT,
  last_touch_campaign TEXT,
  total_sessions INTEGER DEFAULT 0,
  total_events INTEGER DEFAULT 0,
  identified BOOLEAN DEFAULT FALSE,
  identified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_glue_user_id ON users(glue_user_id);

-- Create events table
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  event_id TEXT UNIQUE NOT NULL,
  glue_user_id TEXT NOT NULL,
  session_id TEXT,
  event TEXT NOT NULL,
  properties JSONB DEFAULT '{}'::jsonb,
  timestamp TIMESTAMPTZ NOT NULL,
  email TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_events_glue_user_id ON events(glue_user_id);
CREATE INDEX idx_events_session_id ON events(session_id);
CREATE INDEX idx_events_event ON events(event);
CREATE INDEX idx_events_timestamp ON events(timestamp DESC);

Webhook (POST /webhook/glue/identify)
  ↓
Supabase: UPSERT users
  SET email, traits, last_seen, identified = true
  ON CONFLICT (glue_user_id) DO UPDATE
  ↓
Check for Email Duplicates
  SELECT * FROM users WHERE email = {{ email }} AND glue_user_id != {{ glue_user_id }}
  ↓
IF duplicates found:
  Supabase: UPDATE users
    SET linked_ids = array_append(linked_ids, {{ new_glue_user_id }})
  ↓
Respond: { success: true }

Webhook (POST /webhook/glue/track)
  ↓
Supabase: INSERT INTO events
  VALUES (event_id, glue_user_id, event, properties, timestamp)
  ↓
Supabase: UPDATE users
  SET last_seen = NOW(), total_events = total_events + 1
  ↓
Respond: { success: true }

Webhook (POST /webhook/glue/sync)
  ↓
Parse journey_data JSON
  ↓
Loop through events array
  ↓
Supabase: INSERT INTO events (ON CONFLICT DO NOTHING)
  ↓
Supabase: UPSERT user profile
  ↓
Respond: { success: true, events_synced: X }

<script
  src="https://api.dataglue.io/glue.min.js"
  profile-endpoint="https://your-n8n.app/webhook/glue"
  inject-full-journey="true"
></script>

npm install @supabase/supabase-js

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_KEY
)

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ error: 'Method not allowed' })
  }

  const { glue_user_id, email, traits, context } = req.body

  if (!glue_user_id || !email) {
    return res.status(400).json({ error: 'Missing required fields' })
  }

  try {
    // Check if user with this email already exists
    const { data: existingUser } = await supabase
      .from('users')
      .select('*')
      .eq('email', email)
      .single()

    if (existingUser && existingUser.glue_user_id !== glue_user_id) {
      // Cross-device: merge the IDs
      await supabase
        .from('users')
        .update({
          linked_ids: [...(existingUser.linked_ids || []), glue_user_id]
        })
        .eq('glue_user_id', existingUser.glue_user_id)

      // Update all events to point to primary ID
      await supabase
        .from('events')
        .update({ glue_user_id: existingUser.glue_user_id })
        .eq('glue_user_id', glue_user_id)
    } else {
      // Upsert user
      await supabase
        .from('users')
        .upsert({
          glue_user_id,
          email,
          traits,
          identified: true,
          identified_at: new Date().toISOString(),
          last_seen: new Date().toISOString(),
          last_touch_source: context?.utm_source,
          last_touch_medium: context?.utm_medium,
          last_touch_campaign: context?.utm_campaign,
        }, {
          onConflict: 'glue_user_id'
        })
    }

    res.json({ success: true, glue_user_id })
  } catch (error) {
    console.error('Identify error:', error)
    res.status(500).json({ error: error.message })
  }
}

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_KEY
)

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ error: 'Method not allowed' })
  }

  const { glue_user_id, session_id, event, properties, timestamp } = req.body

  try {
    // Insert event
    await supabase
      .from('events')
      .insert({
        event_id: `evt_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`,
        glue_user_id,
        session_id,
        event,
        properties,
        timestamp: new Date(timestamp)
      })

    // Update user stats
    await supabase
      .from('users')
      .update({
        last_seen: new Date().toISOString(),
        total_events: supabase.raw('total_events + 1')
      })
      .eq('glue_user_id', glue_user_id)

    res.json({ success: true })
  } catch (error) {
    console.error('Track error:', error)
    res.status(500).json({ error: error.message })
  }
}

<script
  src="https://api.dataglue.io/glue.min.js"
  profile-endpoint="https://your-app.vercel.app/api/glue"
></script>

npm install express pg redis cors dotenv

const express = require('express')
const { Pool } = require('pg')
const cors = require('cors')

const app = express()
app.use(express.json())
app.use(cors())

// PostgreSQL connection
const pool = new Pool({
  connectionString: process.env.DATABASE_URL
})

// POST /glue/identify
app.post('/glue/identify', async (req, res) => {
  const { glue_user_id, email, traits, context } = req.body

  if (!glue_user_id || !email) {
    return res.status(400).json({ error: 'Missing required fields' })
  }

  try {
    // Check for existing user with this email
    const existing = await pool.query(
      'SELECT * FROM users WHERE email = $1 AND glue_user_id != $2',
      [email, glue_user_id]
    )

    if (existing.rows.length > 0) {
      // Cross-device merge
      const primaryUser = existing.rows[0]

      // Add to linked_ids
      await pool.query(
        'UPDATE users SET linked_ids = array_append(linked_ids, $1) WHERE glue_user_id = $2',
        [glue_user_id, primaryUser.glue_user_id]
      )

      // Merge events
      await pool.query(
        'UPDATE events SET glue_user_id = $1 WHERE glue_user_id = $2',
        [primaryUser.glue_user_id, glue_user_id]
      )
    } else {
      // Upsert user
      await pool.query(`
        INSERT INTO users (
          glue_user_id, email, traits, identified, identified_at,
          last_seen, last_touch_source, last_touch_medium
        ) VALUES ($1, $2, $3, true, NOW(), NOW(), $4, $5)
        ON CONFLICT (glue_user_id) DO UPDATE SET
          email = $2,
          traits = $3,
          identified = true,
          identified_at = COALESCE(users.identified_at, NOW()),
          last_seen = NOW(),
          last_touch_source = $4,
          last_touch_medium = $5
      `, [
        glue_user_id,
        email,
        JSON.stringify(traits),
        context?.utm_source,
        context?.utm_medium
      ])
    }

    res.json({ success: true, glue_user_id })
  } catch (error) {
    console.error('Identify error:', error)
    res.status(500).json({ error: error.message })
  }
})

// POST /glue/track
app.post('/glue/track', async (req, res) => {
  const { glue_user_id, session_id, event, properties, timestamp } = req.body

  try {
    // Insert event
    await pool.query(`
      INSERT INTO events (event_id, glue_user_id, session_id, event, properties, timestamp)
      VALUES ($1, $2, $3, $4, $5, $6)
    `, [
      `evt_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`,
      glue_user_id,
      session_id,
      event,
      JSON.stringify(properties),
      new Date(timestamp)
    ])

    // Update user
    await pool.query(
      'UPDATE users SET last_seen = NOW(), total_events = total_events + 1 WHERE glue_user_id = $1',
      [glue_user_id]
    )

    res.json({ success: true })
  } catch (error) {
    console.error('Track error:', error)
    res.status(500).json({ error: error.message })
  }
})

// POST /glue/sync
app.post('/glue/sync', async (req, res) => {
  const { glue_user_id, data } = req.body

  try {
    // Upsert user
    await pool.query(`
      INSERT INTO users (glue_user_id, email, traits, first_seen, last_seen)
      VALUES ($1, $2, $3, $4, NOW())
      ON CONFLICT (glue_user_id) DO UPDATE SET
        email = COALESCE(users.email, $2),
        traits = $3,
        last_seen = NOW()
    `, [
      glue_user_id,
      data.profile.email,
      JSON.stringify(data.profile.traits),
      data.profile.first_seen
    ])

    // Bulk insert events
    for (const event of data.events) {
      await pool.query(`
        INSERT INTO events (event_id, glue_user_id, session_id, event, properties, timestamp)
        VALUES ($1, $2, $3, $4, $5, $6)
        ON CONFLICT (event_id) DO NOTHING
      `, [
        event.event_id,
        glue_user_id,
        event.session_id,
        event.event,
        JSON.stringify(event.properties),
        event.timestamp
      ])
    }

    res.json({ success: true, events_synced: data.events.length })
  } catch (error) {
    console.error('Sync error:', error)
    res.status(500).json({ error: error.message })
  }
})

const PORT = process.env.PORT || 3000
app.listen(PORT, () => {
  console.log(`DataGlue server running on port ${PORT}`)
})

# Railway
railway up

# Or Render
render deploy

# Or Docker
docker build -t dataglue-server .
docker run -p 3000:3000 dataglue-server

-- When user identifies on any device
-- Step 1: Check if email exists
SELECT * FROM users WHERE email = 'john@example.com';

-- Step 2a: If exists, merge devices
UPDATE users
SET linked_ids = array_append(linked_ids, 'NEW-DEVICE-ID')
WHERE email = 'john@example.com';

-- Step 2b: Unify events under primary ID
UPDATE events
SET glue_user_id = 'PRIMARY-ID'
WHERE glue_user_id = 'NEW-DEVICE-ID';

-- Step 3: Query complete journey
SELECT * FROM events
WHERE glue_user_id IN (
  SELECT glue_user_id FROM users WHERE email = 'john@example.com'
  UNION
  SELECT unnest(linked_ids) FROM users WHERE email = 'john@example.com'
)
ORDER BY timestamp ASC;

-- Get complete user journey
WITH user_profile AS (
  SELECT * FROM users WHERE email = 'user@example.com'
),
all_ids AS (
  SELECT glue_user_id FROM user_profile
  UNION
  SELECT unnest(linked_ids) FROM user_profile
)
SELECT
  e.timestamp,
  e.event,
  e.properties->>'url' as page,
  e.session_id
FROM events e
WHERE e.glue_user_id IN (SELECT * FROM all_ids)
ORDER BY e.timestamp ASC;

-- Users by first touch source
SELECT
  first_touch_source,
  COUNT(*) as users,
  COUNT(*) FILTER (WHERE identified = true) as converted
FROM users
GROUP BY first_touch_source
ORDER BY users DESC;

-- Funnel analysis
WITH funnel AS (
  SELECT
    COUNT(DISTINCT CASE WHEN event = 'page_view' AND properties->>'path' = '/landing' THEN glue_user_id END) as landed,
    COUNT(DISTINCT CASE WHEN event = 'form_submit' THEN glue_user_id END) as submitted,
    COUNT(DISTINCT CASE WHEN event = 'page_view' AND properties->>'path' = '/thank-you' THEN glue_user_id END) as converted
  FROM events
)
SELECT
  landed,
  submitted,
  converted,
  ROUND(100.0 * submitted / landed, 2) as submit_rate,
  ROUND(100.0 * converted / submitted, 2) as conversion_rate
FROM funnel;