How to Track Analytics with Supabase

1-- Create the events table
2CREATE TABLE public.analytics_events (
3  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
4  user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
5  event_name text NOT NULL,
6  page_url text,
7  metadata jsonb DEFAULT '{}'::jsonb,
8  created_at timestamptz DEFAULT now()
9);
10
11-- Enable RLS
12ALTER TABLE public.analytics_events ENABLE ROW LEVEL SECURITY;
13
14-- Allow authenticated users to insert their own events
15CREATE POLICY "Users can insert own events"
16  ON public.analytics_events FOR INSERT
17  TO authenticated
18  WITH CHECK ((SELECT auth.uid()) = user_id);
19
20-- Allow anonymous page view tracking
21CREATE POLICY "Anonymous users can insert page views"
22  ON public.analytics_events FOR INSERT
23  TO anon
24  WITH CHECK (user_id IS NULL AND event_name = 'page_view');
25
26-- Add indexes for common queries
27CREATE INDEX idx_events_name ON public.analytics_events (event_name);
28CREATE INDEX idx_events_created ON public.analytics_events (created_at);
29CREATE INDEX idx_events_user ON public.analytics_events (user_id);

1import { supabase } from '@/lib/supabase'
2
3// Track a page view (fire and forget)
4export function trackPageView(url: string, metadata?: Record<string, any>) {
5  supabase
6    .from('analytics_events')
7    .insert({
8      event_name: 'page_view',
9      page_url: url,
10      metadata: {
11        referrer: document.referrer || null,
12        screen_width: window.innerWidth,
13        ...metadata,
14      },
15    })
16    .then(({ error }) => {
17      if (error) console.warn('Analytics error:', error.message)
18    })
19}
20
21// Use in React (in your layout or router)
22import { useEffect } from 'react'
23import { useLocation } from 'react-router-dom'
24
25function AnalyticsTracker() {
26  const location = useLocation()
27
28  useEffect(() => {
29    trackPageView(location.pathname + location.search)
30  }, [location])
31
32  return null
33}

1import { supabase } from '@/lib/supabase'
2
3// Track any custom event
4export async function trackEvent(
5  eventName: string,
6  metadata?: Record<string, any>
7) {
8  const { data: { user } } = await supabase.auth.getUser()
9
10  supabase
11    .from('analytics_events')
12    .insert({
13      event_name: eventName,
14      user_id: user?.id || null,
15      metadata: metadata || {},
16    })
17    .then(({ error }) => {
18      if (error) console.warn('Track event error:', error.message)
19    })
20}
21
22// Usage examples:
23trackEvent('signup_completed', { plan: 'pro' })
24trackEvent('project_created', { project_name: 'My App' })
25trackEvent('feature_used', { feature: 'export_csv' })
26trackEvent('button_clicked', { button: 'upgrade_plan', page: '/settings' })

1-- Page views per day (last 30 days)
2SELECT
3  date_trunc('day', created_at)::date AS day,
4  COUNT(*) AS views
5FROM public.analytics_events
6WHERE event_name = 'page_view'
7  AND created_at >= now() - interval '30 days'
8GROUP BY day
9ORDER BY day DESC;
10
11-- Most popular pages
12SELECT
13  page_url,
14  COUNT(*) AS views
15FROM public.analytics_events
16WHERE event_name = 'page_view'
17  AND created_at >= now() - interval '7 days'
18GROUP BY page_url
19ORDER BY views DESC
20LIMIT 20;
21
22-- Active users per day
23SELECT
24  date_trunc('day', created_at)::date AS day,
25  COUNT(DISTINCT user_id) AS active_users
26FROM public.analytics_events
27WHERE user_id IS NOT NULL
28  AND created_at >= now() - interval '30 days'
29GROUP BY day
30ORDER BY day DESC;
31
32-- Event counts by type
33SELECT
34  event_name,
35  COUNT(*) AS total
36FROM public.analytics_events
37WHERE created_at >= now() - interval '7 days'
38GROUP BY event_name
39ORDER BY total DESC;

1-- Create a function to get daily page views
2CREATE OR REPLACE FUNCTION public.get_daily_page_views(
3  days_back integer DEFAULT 30
4)
5RETURNS TABLE (day date, views bigint)
6LANGUAGE sql
7SECURITY DEFINER
8SET search_path = ''
9AS $$
10  SELECT
11    date_trunc('day', created_at)::date AS day,
12    COUNT(*) AS views
13  FROM public.analytics_events
14  WHERE event_name = 'page_view'
15    AND created_at >= now() - make_interval(days => days_back)
16  GROUP BY day
17  ORDER BY day DESC;
18$$;
19
20-- Restrict to authenticated users only
21REVOKE EXECUTE ON FUNCTION public.get_daily_page_views FROM anon;
22
23-- Call from the frontend:
24-- const { data } = await supabase.rpc('get_daily_page_views', { days_back: 30 })