Consuming the Data

:::caution Pre-release This is a pre-1.0 library (v0.1.1) — API may change without notice. :::

Events are persisted to your raw_event table via the sink callback you provide. This guide covers how to query and consume that data.

Your Sink Implementation

Define where and how events are stored:

const handler = createCollectHandler({
  tokenSecrets: { /* ... */ },
  ipSalt: process.env.NS_IP_SALT!,
  sink: {
    async storeEvents(batch) {
      // Example: Drizzle ORM
      await db
        .insert(rawEvent)
        .values(
          batch.events.map((e) => ({
            session_id: batch.sessionId,
            event_type: e.event_type,
            route_pattern: e.route_pattern,
            click_target_label: e.click_target_label,
            click_count: e.click_count,
            effect_latency_ms: e.effect_latency_ms,
            conversion_source: e.conversion_source,
            conversion_name: e.conversion_name,
            ts: new Date(e.ts),
            // ... other columns ...
          }))
        );
    },
  },
});

The batch object contains:

{
  sessionId: string;      // Session identifier
  batch_id: string;       // Unique batch identifier
  events: JourneyEvent[]; // Array of 1-10 events
  truncated: boolean;     // true if session hit 200-event cap
}

Control Plane: Agent API

For AI agents and automated systems, the apps/agent-api package provides a query surface over your raw events. This is the recommended entry point for machine learning pipelines and control-plane logic.

Note: Deep-diving the agent API is out of scope for this docs site. See the apps/agent-api README for its schema and query interface.

Direct Database Queries

If you prefer direct SQL access, query raw_event directly:

-- All page views
SELECT * FROM raw_event
WHERE event_type = 'page_view'
ORDER BY ts DESC
LIMIT 1000;

-- Rage clicks (frustration signal)
SELECT
  route_pattern,
  click_target_label,
  COUNT(*) as count,
  AVG(click_count) as avg_click_count
FROM raw_event
WHERE event_type = 'rage_click'
GROUP BY route_pattern, click_target_label
ORDER BY count DESC;

-- Dead clicks (UX friction)
SELECT
  route_pattern,
  click_target_label,
  COUNT(*) as count
FROM raw_event
WHERE event_type = 'dead_click'
GROUP BY route_pattern, click_target_label
ORDER BY count DESC;

-- Conversions
SELECT
  conversion_source,
  conversion_name,
  COUNT(*) as total,
  COUNT(DISTINCT session_id) as sessions
FROM raw_event
WHERE event_type = 'conversion'
GROUP BY conversion_source, conversion_name
ORDER BY total DESC;

Data Retention and Partitioning

Raw events are stored in a partitioned table:

-- Check partition status
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE tablename LIKE 'raw_event_%'
ORDER BY tablename DESC;

Partition lifecycle:

• New partition: Created daily for that day's UTC date.
• Active: Used for 24-30 hours (configurable).
• Archived: Old partitions remain queryable for 30 days (configurable).
• Dropped: After retention period, the partition is dropped (not individual rows deleted).

For GDPR compliance, deletion is scoped to raw_event and session_summary tables. Aggregated tables are unaffected.

Monitoring and Analytics

Common queries for operational insight:

-- Events per minute (throughput)
SELECT
  DATE_TRUNC('minute', ts) as minute,
  COUNT(*) as event_count
FROM raw_event
WHERE ts > NOW() - INTERVAL '1 hour'
GROUP BY minute
ORDER BY minute DESC;

-- Session duration (from first page_view to last event)
SELECT
  session_id,
  MIN(ts) as session_start,
  MAX(ts) as session_end,
  (MAX(ts) - MIN(ts)) / 1000.0 as session_duration_seconds,
  COUNT(*) as event_count
FROM raw_event
WHERE event_type IN ('page_view', 'rage_click', 'dead_click')
GROUP BY session_id
HAVING COUNT(*) > 1
ORDER BY session_duration_seconds DESC
LIMIT 100;

-- Conversion funnel
SELECT
  event_type,
  COUNT(DISTINCT session_id) as unique_sessions,
  COUNT(*) as total_events
FROM raw_event
WHERE event_type IN ('page_view', 'conversion')
GROUP BY event_type
ORDER BY CASE WHEN event_type = 'page_view' THEN 1 ELSE 2 END;

Schema Notes

The exact table structure depends on your sink implementation. Common columns:

• id: Primary key (UUID or auto-increment).
• session_id: Browser session identifier.
• event_type: One of "page_view", "rage_click", "dead_click", "conversion".
• route_pattern: Normalized URL (no query/hash).
• click_target_label: Scrubbed button/input label, or null.
• click_count: For rage clicks, the number of rapid clicks.
• effect_latency_ms: Milliseconds until UI change, or null.
• conversion_source: "route_pattern" or "explicit".
• conversion_name: User-supplied conversion name, or null.
• ts: Client timestamp (event occurred at).
• created_at: Server timestamp (event stored at).
• hashed_ip: Daily-rotating-salt hash of the client IP.
• site_id: From the verified token's origin.

Privacy reminder: raw_event contains the most sensitive data (per-user events). Ensure database access is restricted to authorized services only.

---

What's Next

• Query patterns for your specific use case
• Aggregate events into dashboards or reports
• Feed events to AI agents via apps/agent-api
• Set up monitoring alerts on rage/dead click spikes
• Measure conversion funnel performance