Supabase Analytics: Track User Behavior and Metrics

-- Events table for tracking all user actions
create table analytics_events (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  session_id uuid,
  event_name text not null,
  event_category text,
  properties jsonb default '{}'::jsonb,
  device_info jsonb,
  location jsonb,
  created_at timestamp with time zone default now()
);

-- Add indexes for common queries
create index idx_events_user_id on analytics_events(user_id);
create index idx_events_event_name on analytics_events(event_name);
create index idx_events_created_at on analytics_events(created_at desc);
create index idx_events_session_id on analytics_events(session_id);
create index idx_events_properties on analytics_events using gin(properties);

-- Page views tracking
create table page_views (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  session_id uuid not null,
  page_url text not null,
  page_title text,
  referrer text,
  duration_seconds int,
  created_at timestamp with time zone default now()
);

create index idx_page_views_user_id on page_views(user_id);
create index idx_page_views_session_id on page_views(session_id);
create index idx_page_views_created_at on page_views(created_at desc);

-- User sessions
create table user_sessions (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  started_at timestamp with time zone default now(),
  ended_at timestamp with time zone,
  device_type text,
  browser text,
  os text,
  ip_address inet,
  country text,
  city text
);

-- Daily metrics aggregation
create table daily_metrics (
  id uuid default gen_random_uuid() primary key,
  date date not null unique,
  total_users int default 0,
  active_users int default 0,
  new_users int default 0,
  total_sessions int default 0,
  total_page_views int default 0,
  avg_session_duration_seconds int default 0,
  bounce_rate decimal(5,2) default 0,
  calculated_at timestamp with time zone default now()
);

-- User engagement scores
create table user_engagement (
  user_id uuid primary key references auth.users(id) on delete cascade,
  last_active_at timestamp with time zone,
  total_sessions int default 0,
  total_events int default 0,
  days_active int default 0,
  engagement_score decimal(10,2) default 0,
  updated_at timestamp with time zone default now()
);

-- Feature usage tracking
create table feature_usage (
  id uuid default gen_random_uuid() primary key,
  feature_name text not null,
  user_id uuid references auth.users(id) on delete cascade,
  usage_count int default 1,
  first_used_at timestamp with time zone default now(),
  last_used_at timestamp with time zone default now(),
  unique(feature_name, user_id)
);

create index idx_feature_usage_feature on feature_usage(feature_name);

// lib/analytics.ts
import { supabase } from './supabase'

interface TrackEventParams {
  eventName: string
  category?: string
  properties?: Record<string, any>
}

class Analytics {
  private sessionId: string
  private deviceInfo: any

  constructor() {
    this.sessionId = this.getOrCreateSessionId()
    this.deviceInfo = this.getDeviceInfo()
  }

  private getOrCreateSessionId(): string {
    let sessionId = sessionStorage.getItem('analytics_session_id')
    if (!sessionId) {
      sessionId = crypto.randomUUID()
      sessionStorage.setItem('analytics_session_id', sessionId)
    }
    return sessionId
  }

  private getDeviceInfo() {
    return {
      userAgent: navigator.userAgent,
      language: navigator.language,
      screenWidth: window.screen.width,
      screenHeight: window.screen.height,
      timezone: Intl.DateTimeFormat().resolvedOptions().timeZone,
    }
  }

  async trackEvent({ eventName, category, properties = {} }: TrackEventParams) {
    const { data: { user } } = await supabase.auth.getUser()

    await supabase.from('analytics_events').insert({
      user_id: user?.id,
      session_id: this.sessionId,
      event_name: eventName,
      event_category: category,
      properties,
      device_info: this.deviceInfo,
    })
  }

  async trackPageView(pageUrl: string, pageTitle?: string) {
    const { data: { user } } = await supabase.auth.getUser()

    await supabase.from('page_views').insert({
      user_id: user?.id,
      session_id: this.sessionId,
      page_url: pageUrl,
      page_title: pageTitle || document.title,
      referrer: document.referrer,
    })
  }

  async updatePageViewDuration(pageViewId: string, duration: number) {
    await supabase
      .from('page_views')
      .update({ duration_seconds: Math.floor(duration) })
      .eq('id', pageViewId)
  }

  async trackFeatureUsage(featureName: string) {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) return

    await supabase.rpc('increment_feature_usage', {
      p_feature_name: featureName,
      p_user_id: user.id,
    })
  }

  async startSession() {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) return

    const { data } = await supabase.from('user_sessions').insert({
      id: this.sessionId,
      user_id: user.id,
      device_type: this.getDeviceType(),
      browser: this.getBrowser(),
      os: this.getOS(),
    }).select().single()

    return data
  }

  async endSession() {
    await supabase
      .from('user_sessions')
      .update({ ended_at: new Date().toISOString() })
      .eq('id', this.sessionId)
  }

  private getDeviceType(): string {
    const ua = navigator.userAgent
    if (/mobile/i.test(ua)) return 'mobile'
    if (/tablet/i.test(ua)) return 'tablet'
    return 'desktop'
  }

  private getBrowser(): string {
    const ua = navigator.userAgent
    if (ua.includes('Chrome')) return 'Chrome'
    if (ua.includes('Firefox')) return 'Firefox'
    if (ua.includes('Safari')) return 'Safari'
    if (ua.includes('Edge')) return 'Edge'
    return 'Other'
  }

  private getOS(): string {
    const ua = navigator.userAgent
    if (ua.includes('Windows')) return 'Windows'
    if (ua.includes('Mac')) return 'macOS'
    if (ua.includes('Linux')) return 'Linux'
    if (ua.includes('Android')) return 'Android'
    if (ua.includes('iOS')) return 'iOS'
    return 'Other'
  }
}

export const analytics = new Analytics()

// Usage in React app
import { useEffect } from 'react'
import { analytics } from './lib/analytics'

function App() {
  useEffect(() => {
    // Start session on app load
    analytics.startSession()

    // Track page views
    analytics.trackPageView(window.location.pathname)

    // End session on unload
    const handleUnload = () => {
      analytics.endSession()
    }
    window.addEventListener('beforeunload', handleUnload)

    return () => {
      window.removeEventListener('beforeunload', handleUnload)
    }
  }, [])

  return <div>Your App</div>
}

// Track specific events
function ProductPage({ productId }: { productId: string }) {
  useEffect(() => {
    analytics.trackEvent({
      eventName: 'product_viewed',
      category: 'ecommerce',
      properties: { productId },
    })
  }, [productId])

  const handleAddToCart = () => {
    analytics.trackEvent({
      eventName: 'add_to_cart',
      category: 'ecommerce',
      properties: { productId, price: 29.99 },
    })
  }

  return <button onClick={handleAddToCart}>Add to Cart</button>
}

-- Function to calculate daily metrics
create or replace function calculate_daily_metrics(target_date date)
returns void as $$
begin
  insert into daily_metrics (
    date,
    total_users,
    active_users,
    new_users,
    total_sessions,
    total_page_views,
    avg_session_duration_seconds,
    bounce_rate
  )
  select
    target_date,
    (select count(*) from auth.users where created_at::date <= target_date),
    (select count(distinct user_id) from analytics_events where created_at::date = target_date),
    (select count(*) from auth.users where created_at::date = target_date),
    (select count(*) from user_sessions where started_at::date = target_date),
    (select count(*) from page_views where created_at::date = target_date),
    (select avg(extract(epoch from (ended_at - started_at)))::int 
     from user_sessions 
     where started_at::date = target_date and ended_at is not null),
    (select 
       (count(*) filter (where pv_count = 1)::float / nullif(count(*), 0) * 100)::decimal(5,2)
     from (
       select session_id, count(*) as pv_count
       from page_views
       where created_at::date = target_date
       group by session_id
     ) session_page_counts)
  on conflict (date) do update set
    total_users = excluded.total_users,
    active_users = excluded.active_users,
    new_users = excluded.new_users,
    total_sessions = excluded.total_sessions,
    total_page_views = excluded.total_page_views,
    avg_session_duration_seconds = excluded.avg_session_duration_seconds,
    bounce_rate = excluded.bounce_rate,
    calculated_at = now();
end;
$$ language plpgsql;

-- Calculate engagement score
create or replace function update_user_engagement()
returns void as $$
begin
  insert into user_engagement (
    user_id,
    last_active_at,
    total_sessions,
    total_events,
    days_active,
    engagement_score
  )
  select
    u.id,
    (select max(created_at) from analytics_events where user_id = u.id),
    (select count(*) from user_sessions where user_id = u.id),
    (select count(*) from analytics_events where user_id = u.id),
    (select count(distinct created_at::date) from analytics_events where user_id = u.id),
    -- Engagement score formula
    (
      (select count(distinct created_at::date) from analytics_events where user_id = u.id) * 10 +
      (select count(*) from analytics_events where user_id = u.id and created_at > now() - interval '7 days') * 2 +
      (select count(*) from user_sessions where user_id = u.id and started_at > now() - interval '7 days') * 5
    )
  from auth.users u
  where u.id in (select distinct user_id from analytics_events where created_at > now() - interval '30 days')
  on conflict (user_id) do update set
    last_active_at = excluded.last_active_at,
    total_sessions = excluded.total_sessions,
    total_events = excluded.total_events,
    days_active = excluded.days_active,
    engagement_score = excluded.engagement_score,
    updated_at = now();
end;
$$ language plpgsql;

-- Feature usage increment function
create or replace function increment_feature_usage(
  p_feature_name text,
  p_user_id uuid
)
returns void as $$
begin
  insert into feature_usage (feature_name, user_id, usage_count, first_used_at, last_used_at)
  values (p_feature_name, p_user_id, 1, now(), now())
  on conflict (feature_name, user_id) do update set
    usage_count = feature_usage.usage_count + 1,
    last_used_at = now();
end;
$$ language plpgsql;

-- Retention cohort analysis
create or replace function cohort_retention_analysis(
  cohort_start date,
  cohort_end date
)
returns table (
  cohort_month text,
  cohort_size bigint,
  week_0 bigint,
  week_1 bigint,
  week_2 bigint,
  week_3 bigint,
  week_4 bigint
) as $$
begin
  return query
  with user_cohorts as (
    select
      id as user_id,
      date_trunc('month', created_at) as cohort_month
    from auth.users
    where created_at::date between cohort_start and cohort_end
  )
  select
    to_char(uc.cohort_month, 'YYYY-MM') as cohort_month,
    count(distinct uc.user_id) as cohort_size,
    count(distinct case when ae.week_number = 0 then ae.user_id end) as week_0,
    count(distinct case when ae.week_number = 1 then ae.user_id end) as week_1,
    count(distinct case when ae.week_number = 2 then ae.user_id end) as week_2,
    count(distinct case when ae.week_number = 3 then ae.user_id end) as week_3,
    count(distinct case when ae.week_number = 4 then ae.user_id end) as week_4
  from user_cohorts uc
  left join (
    select
      user_id,
      floor(extract(epoch from (created_at - u.created_at)) / (7 * 24 * 60 * 60))::int as week_number
    from analytics_events ae
    join auth.users u on u.id = ae.user_id
  ) ae on ae.user_id = uc.user_id
  group by uc.cohort_month
  order by uc.cohort_month;
end;
$$ language plpgsql;

// components/AnalyticsDashboard.tsx
import { useEffect, useState } from 'react'
import { supabase } from '../lib/supabase'

interface DashboardMetrics {
  activeUsers: number
  totalSessions: number
  totalPageViews: number
  avgSessionDuration: number
}

export function AnalyticsDashboard() {
  const [metrics, setMetrics] = useState<DashboardMetrics>({
    activeUsers: 0,
    totalSessions: 0,
    totalPageViews: 0,
    avgSessionDuration: 0,
  })
  const [recentEvents, setRecentEvents] = useState<any[]>([])

  useEffect(() => {
    loadMetrics()
    loadRecentEvents()

    // Subscribe to real-time events
    const eventsChannel = supabase
      .channel('analytics-events')
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'analytics_events',
        },
        (payload) => {
          setRecentEvents((current) => [payload.new, ...current.slice(0, 9)])
          loadMetrics() // Refresh metrics
        }
      )
      .subscribe()

    return () => {
      supabase.removeChannel(eventsChannel)
    }
  }, [])

  async function loadMetrics() {
    const today = new Date().toISOString().split('T')[0]

    // Active users today
    const { count: activeUsers } = await supabase
      .from('analytics_events')
      .select('user_id', { count: 'exact', head: true })
      .gte('created_at', today)

    // Total sessions today
    const { count: totalSessions } = await supabase
      .from('user_sessions')
      .select('*', { count: 'exact', head: true })
      .gte('started_at', today)

    // Total page views today
    const { count: totalPageViews } = await supabase
      .from('page_views')
      .select('*', { count: 'exact', head: true })
      .gte('created_at', today)

    // Average session duration
    const { data: sessions } = await supabase
      .from('user_sessions')
      .select('started_at, ended_at')
      .gte('started_at', today)
      .not('ended_at', 'is', null)

    const avgDuration = sessions?.length
      ? sessions.reduce((acc, s) => {
          const duration =
            (new Date(s.ended_at).getTime() - new Date(s.started_at).getTime()) / 1000
          return acc + duration
        }, 0) / sessions.length
      : 0

    setMetrics({
      activeUsers: activeUsers || 0,
      totalSessions: totalSessions || 0,
      totalPageViews: totalPageViews || 0,
      avgSessionDuration: Math.floor(avgDuration),
    })
  }

  async function loadRecentEvents() {
    const { data } = await supabase
      .from('analytics_events')
      .select('*')
      .order('created_at', { ascending: false })
      .limit(10)

    if (data) setRecentEvents(data)
  }

  return (
    <div className="p-6">
      <h1 className="text-2xl font-bold mb-6">Analytics Dashboard</h1>

      {/* Metrics Cards */}
      <div className="grid grid-cols-1 md:grid-cols-4 gap-4 mb-8">
        <MetricCard title="Active Users" value={metrics.activeUsers} />
        <MetricCard title="Total Sessions" value={metrics.totalSessions} />
        <MetricCard title="Page Views" value={metrics.totalPageViews} />
        <MetricCard
          title="Avg Session Duration"
          value={`${metrics.avgSessionDuration}s`}
        />
      </div>

      {/* Recent Events */}
      <div className="bg-white rounded-lg shadow p-6">
        <h2 className="text-xl font-semibold mb-4">Recent Events</h2>
        <div className="space-y-2">
          {recentEvents.map((event) => (
            <div
              key={event.id}
              className="flex justify-between items-center p-3 bg-gray-50 rounded"
            >
              <span className="font-medium">{event.event_name}</span>
              <span className="text-sm text-gray-500">
                {new Date(event.created_at).toLocaleTimeString()}
              </span>
            </div>
          ))}
        </div>
      </div>
    </div>
  )
}

function MetricCard({ title, value }: { title: string; value: number | string }) {
  return (
    <div className="bg-white rounded-lg shadow p-6">
      <h3 className="text-sm text-gray-500 mb-2">{title}</h3>
      <p className="text-3xl font-bold">{value}</p>
    </div>
  )
}

-- Create funnel analysis view
create or replace function analyze_conversion_funnel(
  start_date timestamp with time zone,
  end_date timestamp with time zone
)
returns table (
  step text,
  users bigint,
  conversion_rate decimal(5,2)
) as $$
with funnel_steps as (
  select 'Visited Landing Page' as step, 1 as step_order,
    count(distinct user_id) as users
  from page_views
  where page_url = '/' and created_at between start_date and end_date
  
  union all
  
  select 'Signed Up' as step, 2 as step_order,
    count(distinct user_id) as users
  from analytics_events
  where event_name = 'user_signed_up' and created_at between start_date and end_date
  
  union all
  
  select 'Completed Onboarding' as step, 3 as step_order,
    count(distinct user_id) as users
  from analytics_events
  where event_name = 'onboarding_completed' and created_at between start_date and end_date
  
  union all
  
  select 'Made First Purchase' as step, 4 as step_order,
    count(distinct user_id) as users
  from analytics_events
  where event_name = 'purchase_completed' and created_at between start_date and end_date
),
first_step as (
  select users as initial_users
  from funnel_steps
  where step_order = 1
)
select
  fs.step,
  fs.users,
  (fs.users::decimal / first_step.initial_users * 100)::decimal(5,2) as conversion_rate
from funnel_steps fs
cross join first_step
order by fs.step_order;
$$ language sql;

-- Usage
select * from analyze_conversion_funnel(
  '2026-01-01'::timestamp,
  '2026-01-31'::timestamp
);

// React component for funnel visualization
import { useEffect, useState } from 'react'
import { supabase } from '../lib/supabase'

interface FunnelStep {
  step: string
  users: number
  conversion_rate: number
}

export function ConversionFunnel() {
  const [funnelData, setFunnelData] = useState<FunnelStep[]>([])

  useEffect(() => {
    loadFunnelData()
  }, [])

  async function loadFunnelData() {
    const { data, error } = await supabase.rpc('analyze_conversion_funnel', {
      start_date: new Date('2026-01-01').toISOString(),
      end_date: new Date('2026-01-31').toISOString(),
    })

    if (data) setFunnelData(data)
  }

  return (
    <div className="space-y-4">
      {funnelData.map((step, index) => (
        <div key={step.step} className="relative">
          <div
            className="bg-blue-500 text-white p-4 rounded"
            style={{
              width: `${step.conversion_rate}%`,
              minWidth: '200px',
            }}
          >
            <div className="flex justify-between">
              <span className="font-medium">{step.step}</span>
              <span>{step.users} users</span>
            </div>
            <div className="text-sm">{step.conversion_rate}%</div>
          </div>
          {index < funnelData.length - 1 && (
            <div className="text-red-500 text-sm mt-1">
              Drop-off: {(funnelData[index].users - funnelData[index + 1].users)} users
            </div>
          )}
        </div>
      ))}
    </div>
  )
}