# How to Track Analytics with Supabase

- Tool: Supabase
- Difficulty: Beginner
- Time required: 15-20 min
- Compatibility: Supabase (all plans), @supabase/supabase-js v2+
- Last updated: March 2026

## TL;DR

You can build a lightweight analytics system using Supabase by creating an events table, inserting tracking events from your frontend or Edge Functions, and querying aggregate data with SQL. Create a table with columns for event name, user ID, metadata (JSONB), and timestamp. Insert events via the JS client, and query them with SQL aggregate functions like COUNT, GROUP BY, and date_trunc. This approach avoids third-party analytics dependencies and keeps all data in your own database.

## Building a Lightweight Analytics System with Supabase

Instead of relying on third-party analytics services, you can use Supabase itself to track user behavior, page views, and custom events. The approach is straightforward: create an events table, insert tracking records from your application, and query the data with SQL. This gives you full ownership of your analytics data, no cookie consent issues with third-party scripts, and the flexibility to track exactly what matters to your application.

## Before you start

- A Supabase project with the JS client configured
- A frontend application where you want to add tracking
- Basic understanding of SQL aggregate functions (COUNT, GROUP BY)

## Step-by-step guide

### 1. Create the analytics events table

Start by creating a table to store analytics events. Use a JSONB column for flexible metadata so you can track different properties for different event types without schema changes. Add a timestamp with a default of now() so every event is automatically timestamped. Enable RLS and create a policy that allows authenticated users to insert events but not read other users' events. For public-facing analytics (like page views), you may also want an anon insert policy.

```
-- Create the events table
CREATE TABLE public.analytics_events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  event_name text NOT NULL,
  page_url text,
  metadata jsonb DEFAULT '{}'::jsonb,
  created_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE public.analytics_events ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to insert their own events
CREATE POLICY "Users can insert own events"
  ON public.analytics_events FOR INSERT
  TO authenticated
  WITH CHECK ((SELECT auth.uid()) = user_id);

-- Allow anonymous page view tracking
CREATE POLICY "Anonymous users can insert page views"
  ON public.analytics_events FOR INSERT
  TO anon
  WITH CHECK (user_id IS NULL AND event_name = 'page_view');

-- Add indexes for common queries
CREATE INDEX idx_events_name ON public.analytics_events (event_name);
CREATE INDEX idx_events_created ON public.analytics_events (created_at);
CREATE INDEX idx_events_user ON public.analytics_events (user_id);
```

**Expected result:** The analytics_events table is created with RLS policies allowing both authenticated and anonymous event insertion.

### 2. Track page views from the frontend

Create a tracking function that inserts a page view event every time the user navigates to a new page. In React apps, call this function inside a useEffect in your layout or router component. Include the page URL and any relevant metadata like the referrer, screen size, or UTM parameters. Keep the insert lightweight — fire and forget without awaiting the result to avoid slowing down page loads.

```
import { supabase } from '@/lib/supabase'

// Track a page view (fire and forget)
export function trackPageView(url: string, metadata?: Record<string, any>) {
  supabase
    .from('analytics_events')
    .insert({
      event_name: 'page_view',
      page_url: url,
      metadata: {
        referrer: document.referrer || null,
        screen_width: window.innerWidth,
        ...metadata,
      },
    })
    .then(({ error }) => {
      if (error) console.warn('Analytics error:', error.message)
    })
}

// Use in React (in your layout or router)
import { useEffect } from 'react'
import { useLocation } from 'react-router-dom'

function AnalyticsTracker() {
  const location = useLocation()

  useEffect(() => {
    trackPageView(location.pathname + location.search)
  }, [location])

  return null
}
```

**Expected result:** Every page navigation triggers an insert into the analytics_events table with the page URL and metadata.

### 3. Track custom events for user actions

Beyond page views, track specific user actions like button clicks, form submissions, feature usage, or purchases. Create a generic trackEvent function that accepts an event name and optional metadata. For authenticated users, include their user_id so you can analyze per-user behavior. Store action-specific data in the metadata JSONB column for flexible querying.

```
import { supabase } from '@/lib/supabase'

// Track any custom event
export async function trackEvent(
  eventName: string,
  metadata?: Record<string, any>
) {
  const { data: { user } } = await supabase.auth.getUser()

  supabase
    .from('analytics_events')
    .insert({
      event_name: eventName,
      user_id: user?.id || null,
      metadata: metadata || {},
    })
    .then(({ error }) => {
      if (error) console.warn('Track event error:', error.message)
    })
}

// Usage examples:
trackEvent('signup_completed', { plan: 'pro' })
trackEvent('project_created', { project_name: 'My App' })
trackEvent('feature_used', { feature: 'export_csv' })
trackEvent('button_clicked', { button: 'upgrade_plan', page: '/settings' })
```

**Expected result:** Custom events are inserted into the analytics_events table with the event name, user ID, and metadata.

### 4. Query analytics data with SQL aggregations

Use the SQL Editor or database functions to query your analytics data. Common queries include total page views per day, most popular pages, user activity counts, and event funnels. PostgreSQL's date_trunc function is invaluable for grouping events by time period. Create a database function for complex queries that you can call from your frontend via supabase.rpc().

```
-- Page views per day (last 30 days)
SELECT
  date_trunc('day', created_at)::date AS day,
  COUNT(*) AS views
FROM public.analytics_events
WHERE event_name = 'page_view'
  AND created_at >= now() - interval '30 days'
GROUP BY day
ORDER BY day DESC;

-- Most popular pages
SELECT
  page_url,
  COUNT(*) AS views
FROM public.analytics_events
WHERE event_name = 'page_view'
  AND created_at >= now() - interval '7 days'
GROUP BY page_url
ORDER BY views DESC
LIMIT 20;

-- Active users per day
SELECT
  date_trunc('day', created_at)::date AS day,
  COUNT(DISTINCT user_id) AS active_users
FROM public.analytics_events
WHERE user_id IS NOT NULL
  AND created_at >= now() - interval '30 days'
GROUP BY day
ORDER BY day DESC;

-- Event counts by type
SELECT
  event_name,
  COUNT(*) AS total
FROM public.analytics_events
WHERE created_at >= now() - interval '7 days'
GROUP BY event_name
ORDER BY total DESC;
```

**Expected result:** SQL queries return aggregated analytics data grouped by time period, page, user, or event type.

### 5. Create a database function for dashboard queries

Wrap your analytics queries in PostgreSQL functions so they can be called from the frontend via supabase.rpc(). This is more secure than exposing raw SELECT access to the events table, and it lets you predefine the exact queries your dashboard needs. Use security definer if the function needs to bypass RLS, and restrict execution to authenticated users.

```
-- Create a function to get daily page views
CREATE OR REPLACE FUNCTION public.get_daily_page_views(
  days_back integer DEFAULT 30
)
RETURNS TABLE (day date, views bigint)
LANGUAGE sql
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT
    date_trunc('day', created_at)::date AS day,
    COUNT(*) AS views
  FROM public.analytics_events
  WHERE event_name = 'page_view'
    AND created_at >= now() - make_interval(days => days_back)
  GROUP BY day
  ORDER BY day DESC;
$$;

-- Restrict to authenticated users only
REVOKE EXECUTE ON FUNCTION public.get_daily_page_views FROM anon;

-- Call from the frontend:
-- const { data } = await supabase.rpc('get_daily_page_views', { days_back: 30 })
```

**Expected result:** The function is callable via supabase.rpc() and returns aggregated analytics data for the specified time period.

## Complete code example

File: `analytics-tracker.ts`

```typescript
// Complete analytics tracking module for Supabase
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

interface EventMetadata {
  [key: string]: string | number | boolean | null
}

// Track a page view (fire and forget)
export function trackPageView(
  url: string,
  metadata?: EventMetadata
) {
  const payload = {
    event_name: 'page_view',
    page_url: url,
    metadata: {
      referrer: typeof document !== 'undefined' ? document.referrer : null,
      ...metadata,
    },
  }

  supabase.from('analytics_events').insert(payload).then(({ error }) => {
    if (error) console.warn('[Analytics] Page view error:', error.message)
  })
}

// Track a custom event with user context
export async function trackEvent(
  eventName: string,
  metadata?: EventMetadata
) {
  const { data: { user } } = await supabase.auth.getUser()

  const payload = {
    event_name: eventName,
    user_id: user?.id || null,
    metadata: metadata || {},
  }

  supabase.from('analytics_events').insert(payload).then(({ error }) => {
    if (error) console.warn(`[Analytics] ${eventName} error:`, error.message)
  })
}

// Fetch daily page views for a dashboard
export async function getDailyPageViews(daysBack: number = 30) {
  const { data, error } = await supabase.rpc('get_daily_page_views', {
    days_back: daysBack,
  })
  if (error) throw new Error(`Analytics query failed: ${error.message}`)
  return data as { day: string; views: number }[]
}

// Fetch top pages
export async function getTopPages(limit: number = 20) {
  const { data, error } = await supabase.rpc('get_top_pages', {
    page_limit: limit,
  })
  if (error) throw new Error(`Top pages query failed: ${error.message}`)
  return data as { page_url: string; views: number }[]
}
```

## Common mistakes

- **Awaiting analytics inserts in the render path, which slows down page navigation** — undefined Fix: Use fire-and-forget pattern with .then() instead of await. Analytics writes should never block the user experience.
- **Not adding indexes on event_name and created_at columns, causing slow aggregate queries** — undefined Fix: Add btree indexes on event_name, created_at, and user_id. Without indexes, GROUP BY and date range queries scan the entire table.
- **Storing high-cardinality data like full request bodies in the metadata column, bloating the table** — undefined Fix: Only store relevant, low-cardinality metadata. Avoid storing full request/response bodies, large JSON objects, or PII.

## Best practices

- Use fire-and-forget pattern for analytics inserts so they never block the user experience
- Add indexes on event_name, created_at, and user_id for fast aggregate queries
- Use a consistent naming convention for events (action_object format) to simplify querying
- Store event-specific data in the JSONB metadata column for flexibility without schema changes
- Create database functions for dashboard queries and call them via supabase.rpc() for security
- Set up a pg_cron job to delete old analytics data (e.g., older than 90 days) to control table size
- Use ON DELETE SET NULL for the user_id foreign key to preserve analytics data when users are deleted
- Consider batching high-volume events (like scroll tracking) to reduce insert frequency

## Frequently asked questions

### Is Supabase a good choice for analytics tracking?

Supabase works well for lightweight analytics (page views, custom events, feature tracking) in small to medium apps. For high-traffic sites with millions of events per day, consider a dedicated analytics database like ClickHouse or a service like Segment.

### Will analytics inserts slow down my application?

No, if you use the fire-and-forget pattern. Do not await the insert — use .then() so the database write happens in the background and does not block page rendering or navigation.

### How do I handle analytics for unauthenticated users?

Create an RLS policy that allows the anon role to insert events with a null user_id. You can generate a random anonymous ID in the browser and store it in localStorage to track sessions across pages.

### How do I prevent the analytics table from growing too large?

Set up a pg_cron job to delete events older than your retention period (e.g., 90 days). You can also archive old data to a separate table or export it before deletion.

### Can I track events from Supabase Edge Functions?

Yes. Create a Supabase client inside your Edge Function using the service role key and insert events directly. This is useful for tracking server-side events like webhook receipts or scheduled job completions.

### How is this different from using Google Analytics?

This approach stores all data in your own database with no third-party scripts, no cookie consent banners needed, and full SQL access for custom queries. Google Analytics provides more out-of-the-box features but shares data with Google.

### Can RapidDev help me build a custom analytics dashboard with Supabase?

Yes. RapidDev can design your analytics schema, build tracking integrations, create SQL-powered dashboards, and set up data retention policies for your Supabase-based analytics system.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-track-analytics-with-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-track-analytics-with-supabase