Visitor Data Flow¶

This document describes how chat, analytics, and enrichment data reaches the Supabase tables and RPCs consumed by the Client Backoffice.

Data Sources Overview¶

flowchart TD subgraph Collection["Collection"] W[Widget] -->|analytics proxy| PH[PostHog EU] W -->|chat and profile requests| API[Search API] end subgraph Batch["Production analytics batch path"] PH -->|Batch Export| RAW[(posthog_events_raw)] RAW --> PROC[posthog_batch_processor<br/>Cloud Run Job] end subgraph Storage["Supabase storage"] API --> V[(visitors)] API --> C[(conversations + messages)] API --> A[(accounts)] PROC --> VS[(visitor_sessions)] PROC --> SE[(session_events)] PROC --> PV[(visitor_page_views)] PROC --> C end subgraph ReadModel["Backoffice read model"] V --> VSM[visitor_session_metrics and analytics marts] VS --> VSM SE --> VSM PV --> VSM V --> GVS[get_visitor_stats_with_intent] C --> GVS VSM --> GVS A --> GVS GVS --> VT[VisitorsTable] VSM --> VT end

Core Tables¶

`visitors`¶

The primary table for visitor identity and profile data.

Column	Type	Description
`id`	UUID	Primary key (visitor_id)
`email`	string	Email if captured during conversation
`account_id`	UUID	FK to accounts table (set via enrichment)
`first_seen_at`	timestamp	When visitor was first created
`enrichment_data`	JSON	Raw enrichment data from providers
`site_domain`	string	The site this visitor belongs to

`visitor_sessions`¶

Tracks individual browsing sessions for analytics.

Column	Type	Description
`id`	UUID	Primary key
`session_id`	string	Browser session identifier
`site_domain`	string	Domain where session occurred
`conversation_id`	UUID	FK to conversations (if chat occurred)
`first_event_at`	timestamp	Start of session
`last_activity_at`	timestamp	Last activity in session

`conversations`¶

Chat conversations between visitors and the AI agent.

Column	Type	Description
`id`	UUID	Primary key
`visitor_id`	UUID	FK to visitors
`session_id`	string	Langfuse session ID
`total_turns`	int	Number of message exchanges
`last_message_at`	timestamp	Time of last message
`email_captured`	bool	Whether email was collected
`demo_proposed`	bool	Whether demo was offered
`cta_clicked`	bool	Whether CTA was clicked
`demo_booked`	bool	Whether demo was booked

`accounts`¶

Company-level aggregation for enriched visitors.

Column	Type	Description
`id`	UUID	Primary key
`company_name`	string	Company name from enrichment
`company_domain`	string	Company website domain
`enrichment_data`	JSON	Full enrichment payload

Computed View: `visitor_session_metrics`¶

This PostgreSQL view aggregates session data per visitor for analytics display.

-- Simplified view definition
CREATE VIEW visitor_session_metrics AS
SELECT
    v.id AS visitor_id,
    v.person_id,
    v.site_domain,
    COUNT(DISTINCT vs.id) AS total_sessions,
    SUM(page_views) AS total_page_views,
    AVG(session_duration) AS avg_session_duration_seconds,
    MAX(vs.last_activity_at) AS last_seen_at
FROM visitors v
LEFT JOIN visitor_sessions vs ON vs.site_domain = extract_root_domain(v.site_domain)
GROUP BY v.id, v.person_id, v.site_domain;

Domain Normalization¶

The extract_root_domain() function handles domain mismatches between tables:

visitor_sessions.site_domain may contain www.example.com
visitors.site_domain typically contains example.com

The function strips common prefixes (www., app., etc.) to enable accurate joins.

RPC Function: `get_visitor_stats_with_intent`¶

The primary data source for the Visitors table. This RPC function efficiently aggregates:

Visitor profile data
Conversation metrics (count, messages, timestamps)
Account association
Behavior signals aggregated across conversations
Session metrics including last_seen_at
Computed intent score

Parameters¶

Parameter	Type	Description
`p_domain`	string	Required - Site domain to query
`p_date_start`	timestamp	Filter by date range start
`p_date_end`	timestamp	Filter by date range end
`p_environment`	string	Filter by environment (production/staging)
`p_account_id`	UUID	Filter by specific account
`p_min_messages`	int	Minimum message count (default: 1)

Return Fields¶

Field	Source	Description
`visitor_id`	visitors	Visitor identifier
`email`	visitors	Captured email
`account_id`	visitors	Associated account
`account_name`	accounts	Company name
`company_domain`	accounts	Company website
`conversation_count`	conversations	Number of conversations
`total_messages`	conversations	Sum of all messages
`last_message_at`	conversations	Most recent message time
`last_seen_at`	visitor_session_metrics	Most recent session activity
`first_seen_at`	visitors	First visitor creation time
`email_asked`	conversations	Any conversation asked for email
`email_captured`	conversations	Any conversation captured email
`demo_proposed`	conversations	Any conversation proposed demo
`cta_clicked`	conversations	Any conversation had CTA click
`demo_booked`	conversations	Any conversation booked demo
`intent_score`	computed	Behavior (60%) + interest (40%)

Data Flow in the UI¶

VisitorsTable Component¶

┌──────────────────────────────────────────────────────────────┐
│                      VisitorsTable                            │
├──────────────────────────────────────────────────────────────┤
│                                                               │
│  ┌─────────────────────────────────────────────────────────┐ │
│  │ useVisitors hook                                         │ │
│  │  └─► fetchVisitors()                                     │ │
│  │       └─► supabase.rpc('get_visitor_stats_with_intent')  │ │
│  │            └─► Returns: VisitorSummary[]                 │ │
│  └─────────────────────────────────────────────────────────┘ │
│                           │                                   │
│                           ▼                                   │
│  ┌─────────────────────────────────────────────────────────┐ │
│  │ Session Metrics (optional columns)                       │ │
│  │  └─► useMultipleVisitorSessionMetricsQuery()             │ │
│  │       └─► supabase.from('visitor_session_metrics')       │ │
│  │            └─► Returns: sessions, pageViews, duration    │ │
│  └─────────────────────────────────────────────────────────┘ │
│                           │                                   │
│                           ▼                                   │
│  ┌─────────────────────────────────────────────────────────┐ │
│  │ Display Columns                                          │ │
│  │  • Visitor (email or truncated ID)                       │ │
│  │  • Account (company name, clickable)                     │ │
│  │  • Messages (total_messages)                             │ │
│  │  • Last Activity (last_seen_at ?? last_message_at)       │ │
│  │  • Intent (computed score with badge)                    │ │
│  │  • Funnel Stage (computed from behavior flags)           │ │
│  │  • Sessions, Avg Duration, Page Views (from metrics)     │ │
│  └─────────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────┘

Last Activity Column¶

The "Last Activity" column shows when a visitor was last active:

Primary: last_seen_at from the RPC (derived from session metrics)
Fallback: last_message_at from conversations
Fallback: first_seen_at from visitor creation

This ensures visitors who browsed but didn't chat still show recent activity.

Sorting¶

Sort Option	Field Used
`lastActivity`	`last_seen_at` (falls back to `last_message_at`)
`messages`	`total_messages`
`name`	`account_name`
`intent`	`intent_score`

Enrichment Flow¶

When a visitor is enriched (via IP lookup or email), the flow is:

sequenceDiagram participant W as Widget participant API as Search API participant E as Enrichment Service participant DB as Supabase W->>API: Chat message with IP API->>E: Lookup company by IP E-->>API: Company data API->>DB: Upsert account API->>DB: Update visitor.account_id Note over DB: Visitor now linked to account

Enrichment data is stored in both:

visitors.enrichment_data - Raw enrichment response
accounts table - Normalized company information

Session Tracking¶

Sessions are materialized from PostHog's production batch export. The export writes raw events to posthog_events_raw; the production-only posthog-batch-processor Cloud Run Job transforms each batch into the live analytics tables.

sequenceDiagram participant W as Widget participant PX as API analytics proxy participant PH as PostHog participant RAW as posthog_events_raw participant JOB as PostHog batch processor participant DB as Supabase W->>PX: Send rw_* / page lifecycle event PX->>PH: Forward to PostHog EU PH->>RAW: Batch Export raw events JOB->>RAW: Claim next cursor batch JOB->>DB: Upsert sessions, events, page views, flags

Materialized Data¶

PostHog event	Batch processor result
`rw_widget_impression`	Creates or merges the `visitor_sessions` row and first-touch metadata
`$pageview`	Writes `visitor_page_views` and contributes to session aggregates
`$pageleave`	Contributes to deterministic session-duration calculation
`rw_*` interaction events	Writes normalized `session_events` with the `rw_` prefix removed
`rw_client_form_submitted`	Applies conversion filtering and patches conversation conversion flags
`$web_vitals`	Used for duration/parity processing but intentionally excluded from `session_events`

Idempotency¶

session_events and visitor_page_views use the PostHog event UUID as source_event_uuid when present.
visitor_sessions upserts on session_id and merges prior-batch state before recomputing aggregates.
Conversation changes are PATCH-style flag updates keyed by session_id.
A durable batch cursor advances only after the corresponding writes succeed.

The visitor_session_metrics view then aggregates these sessions per visitor.

Proposed first-party capture

Direct first-party self-capture is a proposed replacement, not the deployed source of this page's operational data flow. See the PostHog cost and removal ADR for its dual-run, parity, and cutover design.

Key Implementation Notes¶

Efficient Querying¶

The RPC function get_visitor_stats_with_intent was created to avoid:

N+1 queries for visitor stats
URL length issues with large .in() queries
Client-side aggregation overhead

Domain Normalization¶

The extract_root_domain() PostgreSQL function ensures accurate joins even when:

Sessions have www. prefix
Visitors have root domain
Different subdomains exist

Intent Score Calculation¶

Intent score combines:

Behavior signals (60%): email_captured, demo_proposed, cta_clicked, demo_booked
Interest score (40%): From conversation content analysis

This is computed in the RPC for efficient server-side calculation.

Visitor Data Flow¶

Data Sources Overview¶

Core Tables¶

visitors¶

visitor_sessions¶

conversations¶

accounts¶

Computed View: visitor_session_metrics¶

Domain Normalization¶

RPC Function: get_visitor_stats_with_intent¶

Parameters¶

Return Fields¶

Data Flow in the UI¶

VisitorsTable Component¶

Last Activity Column¶

Sorting¶

Enrichment Flow¶

Session Tracking¶

Materialized Data¶

Idempotency¶

Key Implementation Notes¶

Efficient Querying¶

Domain Normalization¶

Intent Score Calculation¶

`visitors`¶

`visitor_sessions`¶

`conversations`¶

`accounts`¶

Computed View: `visitor_session_metrics`¶

RPC Function: `get_visitor_stats_with_intent`¶