Data Sources Reference¶

This document provides a complete reference of all data sources used by the Client Backoffice frontend, including Supabase tables, views, RPC functions, and React Query hooks.

Architecture Overview¶

The Client Backoffice uses a direct Supabase connection (not through the API proxy) to support OAuth authentication. Data is fetched through:

Direct table queries via the Supabase JavaScript SDK
PostgreSQL views for pre-computed aggregations
RPC functions for complex server-side calculations
React Query hooks for client-side caching and state management

┌─────────────────────────────────────────────────────────────┐
│                    Client Backoffice UI                     │
│              (React + React Query + TypeScript)             │
└──────────────────────────┬──────────────────────────────────┘
                           │
           ┌───────────────┼───────────────┐
           ▼               ▼               ▼
    ┌──────────┐    ┌──────────┐    ┌──────────┐
    │  Tables  │    │  Views   │    │   RPCs   │
    └──────────┘    └──────────┘    └──────────┘
           │               │               │
           └───────────────┼───────────────┘
                           ▼
              ┌─────────────────────────┐
              │    PostgreSQL (Supabase) │
              └─────────────────────────┘

1. Supabase Tables¶

Primary Tables¶

Table	Purpose	Key Columns
`conversations`	Chat conversations between visitors and AI	`id`, `visitor_id`, `session_id`, `total_turns`, `last_message_at`, `email_captured`, `demo_proposed`, `cta_clicked`, `demo_booked`
`messages`	Individual chat messages	`id`, `conversation_id`, `role`, `content`, `created_at`
`visitors`	Visitor identity and profile	`id`, `email`, `account_id`, `first_seen_at`, `enrichment_data`, `site_domain`
`accounts`	Company-level data from enrichment	`id`, `company_name`, `company_domain`, `enrichment_data`
`visitor_sessions`	Browser session tracking	`id`, `session_id`, `site_domain`, `conversation_id`, `first_event_at`, `last_activity_at`
`visitor_page_views`	Page navigation history	`id`, `session_id`, `page_url`, `timestamp`
`session_events`	Session-level events (CTA clicks, form submissions)	`id`, `session_id`, `event_type`, `timestamp`

Table Relationships¶

accounts (1) ──────────────┐
                           │
visitors (1) ──────────────┼──── (N) conversations ──── (N) messages
     │                     │
     └── (N) visitor_sessions ──── (N) visitor_page_views
                           │
                           └──── (N) session_events

2. Supabase Views¶

`visitor_session_metrics`¶

A PostgreSQL view that aggregates session data per visitor for analytics display.

Column	Type	Description
`visitor_id`	UUID	Foreign key to visitors
`person_id`	string	PostHog person identifier
`site_domain`	string	Normalized site domain
`total_sessions`	int	Count of distinct sessions
`total_page_views`	int	Sum of page views across sessions
`engagement_time_seconds`	int	Total engagement time
`pages_visited`	string[]	Array of unique pages visited

Usage: Used for engagement metrics, session duration, and activity tracking without expensive client-side aggregation.

Stale Time: 5 minutes in React Query cache.

3. RPC Functions¶

`get_visitor_stats_with_intent`¶

The primary data source for the Visitors table. Performs server-side aggregation to avoid N+1 queries and URL length issues.

Parameters:

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

Returns:

Field	Source	Description
`visitor_id`	visitors	Visitor identifier
`email`	visitors	Captured email
`account_id`	visitors	Associated account FK
`account_name`	accounts	Company name
`company_domain`	accounts	Company website
`conversation_count`	computed	Number of conversations
`total_messages`	computed	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%)

`get_account_stats_with_intent`¶

Aggregates account-level statistics with intent scoring.

Parameters:

Parameter	Type	Required	Description
`p_domain`	string	Yes	Site domain to query
`p_date_start`	timestamp	No	Filter by date range start
`p_environment`	string	No	Filter by environment
`p_min_messages`	int	No	Minimum message count

Returns:

Field	Description
`account_id`	Account identifier
`company_name`	Company name
`company_domain`	Company website
`last_seen_at`	Most recent activity
`last_message_at`	Most recent message
`message_count`	Total messages across visitors
`visitor_count`	Number of visitors in account
`first_visitor_email`	Email of first identified visitor
`max_intent_score`	Highest intent score among visitors
Aggregated booleans	`email_captured`, `demo_booked`, etc.

4. React Query Hooks¶

Query Key Structure¶

All queries use typed query keys organized by domain:

queryKeys.{domain}.{specific_query}(parameters)

Stats Hooks (`useStatsQueries.ts`)¶

Dashboard statistics with 30-minute cache.

Hook	Query Key Pattern	Returns
`useExcludedVisitorsQuery`	`['stats', 'excludedVisitors', domain]`	`{ visitorIds, personIds }`
`useConversationStatsQuery`	`['stats', 'conversations', domain, timeRange, ...]`	`{ currentCount, previousCount, percentageChange }`
`useWidgetDisplayStatsQuery`	`['stats', 'widgetDisplay', domain, ...]`	Display count with comparison
`useDemoBookedStatsQuery`	`['stats', 'demoBooked', domain, ...]`	Demo booking stats
`useCTAClickedStatsQuery`	`['stats', 'ctaClicked', domain, ...]`	CTA click stats
`useAccountStatsQuery`	`['stats', 'accounts', domain, ...]`	Account count stats
`useVisitorStatsQuery`	`['stats', 'visitors', domain, ...]`	Visitor count stats
`useEngagementRateQuery`	`['stats', 'engagementRate', domain, ...]`	`{ rate, previousRate, percentageChange }`
`useConversionRateQuery`	`['stats', 'conversionRate', domain, ...]`	Conversion rate
`useEmailCaptureRateQuery`	`['stats', 'emailCaptureRate', domain, ...]`	Email capture rate

Conversations Hooks (`useConversationsQueries.ts`)¶

Conversation data with 2-minute cache for lists, 30-minute for details.

Hook	Query Key Pattern	Returns
`useConversationsQuery`	`['conversations', 'list', domain, filters]`	`PaginatedConversationsResult`
`useConversationHighlightsQuery`	`['conversations', 'highlights', domain, limit]`	`ConversationHighlight[]`
`useConversationsByIdsQuery`	`['conversations', 'byIds', domain, ids]`	`Conversation[]`
`useConversationMessagesQuery`	`['conversations', 'messages', conversationId]`	`ConversationWithMessages`
`useConversationIdBySessionQuery`	`['conversations', 'bySessionId', sessionId]`	`string \| null`

Visitors Hooks (`useVisitorsQueries.ts`)¶

Hook	Query Key Pattern	Returns
`useVisitorsQuery`	`['visitors', 'list', domain, filters]`	`PaginatedVisitorsResult`
`useVisitorConversationsQuery`	`['visitors', 'conversations', visitorId]`	`VisitorConversation[]`

Accounts Hooks (`useAccountsQueries.ts`)¶

Hook	Query Key Pattern	Returns
`useAccountsQuery`	`['accounts', 'list', domain, filters]`	`PaginatedAccountsResult`
`useAccountByIdQuery`	`['accounts', 'byId', accountId]`	`{ id, company_name } \| null`
`useAccountTopicsKeywordsQuery`	`['accounts', 'topicsKeywords', accountId, domain]`	`AccountTopicsKeywords`

Session Metrics Hooks (`useSessionMetricsQueries.ts`)¶

Session and engagement data with 5-minute cache.

Hook	Query Key Pattern	Returns
`useVisitorSessionMetricsQuery`	`['sessionMetrics', 'visitor', visitorId]`	`VisitorSessionMetrics`
`useAccountSessionMetricsQuery`	`['sessionMetrics', 'account', accountId, domain]`	Aggregated metrics
`useSessionPageViewsQuery`	`['pageViews', 'session', sessionId]`	`VisitorPageView[]`
`useMultipleSessionsPageViewsQuery`	`['pageViews', 'multipleSessions', sessionIds]`	`Map<string, VisitorPageView[]>`
`useMultipleVisitorSessionMetricsQuery`	`['sessionMetrics', 'multipleVisitors', visitorIds]`	`Map<string, VisitorSessionMetrics>`
`useMultipleSessionsEventsQuery`	`['sessionEvents', 'multipleSessions', sessionIds]`	`Map<string, SessionEvent[]>`
`useVisitorSessionsQuery`	`['visitors', 'sessions', visitorId]`	`VisitorSession[]`

Topics Hooks (`useTopicsQueries.ts`)¶

Hook	Query Key Pattern	Returns
`useTopicsQuery`	`['topics', 'list', domain, dateRange, ...]`	`TopicStats[]`

5. Service Files¶

Service File	Responsibilities	Data Sources
`conversations.ts`	Conversation listing, highlighting, message fetching, topic stats	`conversations`, `messages`, `visitors`, `accounts` tables
`accounts.ts`	Account listing, excluded visitor detection	`accounts`, `visitors`, `conversations` tables; RPC `get_account_stats_with_intent`
`visitors.ts`	Widget display stats, engagement rates, conversion rates	`visitor_sessions`, `session_events`, `conversations` tables
`sessionMetrics.ts`	Session metrics, page views, session events	`visitor_session_metrics` view, `visitor_page_views`, `session_events`, `visitor_sessions` tables

6. Component Data Dependencies¶

Component	Query Hooks Used
Dashboard	All stats hooks, `useConversationHighlightsQuery`
ConversationsPage	`useConversationsQuery`, `useConversationHighlightsQuery`, `useConversationMessagesQuery`
VisitorsPage	`useVisitorsQuery`, `useVisitorConversationsQuery`, `useVisitorSessionMetricsQuery`
AccountsPage	`useAccountsQuery`, `useAccountByIdQuery`, `useAccountTopicsKeywordsQuery`
SessionEventItem	`useMultipleSessionsPageViewsQuery`, `useMultipleSessionsEventsQuery`
TimelinePanel	`useVisitorSessionsQuery`, `useMultipleSessionsPageViewsQuery`, `useMultipleSessionsEventsQuery`

7. Caching Strategy¶

Data Category	Stale Time	Rationale
List queries (conversations, visitors, accounts)	2 minutes	New data appears frequently
Session metrics	5 minutes	Moderate update frequency
Stats and details	30 minutes	Aggregate data changes slowly

8. Excluded Visitors¶

The backoffice automatically excludes certain visitors from statistics:

Rose employees (company_name = 'Rose')
Hexa team (company_name = 'Hexa')
Client's own company (account where site_domain matches the domain parameter)

This filtering is handled by fetchExcludedVisitors() which is a prerequisite for stats calculations.

9. Data Fetching Patterns¶

Pattern 1: Direct Table Query with Relations¶

supabase
  .from('conversations')
  .select(`
    id, visitor_id, total_turns, last_message_at,
    visitor:visitors (id, account_id, account:accounts (id, company_name))
  `)
  .eq('site_domain', domain)
  .order('last_message_at', { ascending: false })
  .range(offset, offset + pageSize - 1)

Pattern 2: RPC for Server-Side Aggregation¶

supabase.rpc('get_visitor_stats_with_intent', {
  p_domain: domain,
  p_date_start: dateStart?.toISOString(),
  p_environment: filters.environment,
  p_min_messages: filters.minMessages ?? 1,
})

Pattern 3: View Query¶

supabase
  .from('visitor_session_metrics')
  .select('*')
  .eq('visitor_id', visitorId)
  .maybeSingle()

Pattern 4: Client-Side Post-Processing¶

Some filters require client-side computation:

// Filtering by computed fields (funnel stage)
conversations.filter(conv => {
  const stage = getFunnelStage(conv);
  return filters.funnelStage?.includes(stage);
})