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`, `workspace_id`, `session_id`, `total_turns`, `last_message_at`, `email_captured`, `demo_proposed`, `cta_clicked`, `demo_booked`, `topics`, `keywords`, `first_message_source`
`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`

first_message_source is null when the visitor typed the first question in the widget (user_typed is almost never set). ai_section_search_bar is typed too; dynamic_question, follow_up_question and ai_section_button are clicked suggestions in Rose's own wording.

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 made a content-gating email offer (💌, `content_gating` config); in-chat booking email requests don't count
`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`

Seeing a test conversation in the Conversations list¶

By default the list shows only widget traffic (deployment_target widget or null), defaults to Environment: Production, and the staff non-admin view hides other environments. Playground chats (preprod from the standalone playground, backoffice-playground from the backoffice one) appear only when the Playground option is selected in the environment filter (services/widgetTraffic.ts).

Use a demo workspace such as cactusinbound.com; Supabase is shared, so the test row is a production row. Then, in admin view, either:

chat from the playground and select the conversation's environment plus Playground; or
POST to the Test API's /api/lightrag/query with "deploymentTarget": "widget" and the flags under test (for example "isFormAssistant": true), then open /conversations?domain=<domain>&environment=all.

The standalone playground's Google sign-in returns to VITE_AUTH_REDIRECT_URL, or to <origin>/auth/callback. With frontend/.env.test, a sign-in from localhost:3001 was observed landing on app.userose.ai instead; use the API path when that happens.

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)
Internal visitors (visitors.is_internal is set, or visitors.ip_hashes matches an enabled IP list)

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

Internal visitors (`visitors.is_internal`)¶

flag_if_internal (ixdata/clients/internal_visitors.py) sets the flag on a chat turn or an impression when the request is in widget test mode (below), or its IP is in the client's analytics.internal_ip_filter or the staff's internal_ip_filter. The flag holds no IP, so the visitor stays out of stats after IX-5982 clears their hashes on a Snitcher identification. Experiment results and hygiene read it too.

It is sticky. Nothing unsets it: not ?rose_test=0, not turning the extension off, not removing an IP or turning a list off. A visitor's later visits to that site stay excluded.

A visitor who never chats reaches no backend write unless enrich_all_visitors is on, so they are not flagged by what they do. The hash match at read time still catches a visitor whose IP joined a list after their visit, unless Snitcher identified them.

Self-serve "exclude my own browser" switch (`//` or `?rose_test=1`)¶

The above is account/IP-driven and excludes whole companies. For an individual who wants their own browsing kept out of production numbers (a client testing their live widget, or a Rose teammate dogfooding), there is a per-browser switch.

Two ways to turn it on:

Start a chat message with // — e.g. // just testing. This is the easiest path: no URL editing, and it reuses the existing // convention (the backend already treats //-prefixed messages as comments and never stores them — see _is_comment_message in ixchat/service.py). The widget catches the // at send time, sets the flag, and retags the session immediately.
Visit with ?rose_test=1 appended to the URL — handy to flag a browser before chatting at all, so even the first rw_widget_impression is excluded.

To turn it off: visit with ?rose_test=0. (Chat has no off-switch — the URL param is the unlock.) This only stops new events being tagged: once the backend has seen the browser in test mode, its visitor stays flagged.

Testing a conversion end to end on a client's live site: a flagged browser's test conversation and conversion are stored but never appear in the backoffice (no filter shows widget-test). To see the test there, use a browser profile that has never been in test mode on that site, from an IP on no internal list: ?rose_test=0 is not enough for a browser already flagged. Mark every form field as a test (TEST Rose - ne pas traiter). That test then counts in the client's production stats, so warn the client.

What the flag does:

Writes a rose_widget_test flag to localStorage and reports deployment_target = 'widget-test' instead of 'widget' on every event and conversation from that browser.
Conversation stats filter deployment_target = 'widget' (and PostHog dashboards filter rw_deployment_target = 'widget'). The widget-displayed metric, Rose impact's "without Rose" group and experiment results have no such filter: they leave this browser out through visitors.is_internal, which the backend sets when it sees widget-test.

Scope and caveats:

Per-browser, not per-person. Only the browser that set the flag is excluded. Phone, incognito, or another laptop still count — use excluded_ips (config) for whole-team exclusion.
The ?rose_test=1 path applies at widget boot before analytics init, so the first rw_widget_impression is already tagged. The // path flips mid-session, so an impression already fired on that page load stays tagged widget, but the flag set by that message excludes the visitor from then on.
Implementation: the persisted flag lives in frontend/shared/src/config/deployment.ts (isWidgetTestModeStored / setWidgetTestMode); the ?rose_test URL path is isWidgetTestMode() in frontend/widget/src/config/deployment.ts (wired in main.tsx); the // path is in InboundXService.createRequestBody in frontend/shared/src/services/inboundx.ts.

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);
})