# How to Add User Activity Analytics to Your App — Copy-Paste Prompts Inside

- Tool: App Features
- Last updated: July 2026

## TL;DR

User activity analytics means an event ingestion endpoint, a Supabase events table with materialized views for cohorts and funnels, and an admin-only dashboard built with Next.js + Recharts or Lovable + shadcn/ui. You can ship a working system in 2–4 days using V0 or Lovable for $0/month at launch. The two hardest parts are keeping the dashboard admin-only (RLS on the events table) and keeping materialized views current (pg_cron refresh every hour).

## What User Activity Analytics Actually Is

User activity analytics is an internal system that records every meaningful thing users do — page views, button clicks, feature opens, session starts — and then aggregates that data into a dashboard only admins can see. Unlike third-party tools (PostHog, Mixpanel), building it yourself means zero per-event fees, full data ownership, and the ability to query anything about your own users without paying for a seat license. The architecture has four layers: a tracking endpoint (Supabase Edge Function) that receives events from your app; an events table with materialized views for daily active users, retention cohorts, and funnel conversion; an admin dashboard UI built with Recharts and shadcn/ui DataTable; and access controls (Supabase RLS with an admin role check) that ensure regular users can never read each other's behavior. The most expensive mistake is skipping the RLS layer — AI tools routinely generate event queries with the anon key and no role filter, making all user data readable by anyone who is logged in.

## Anatomy of the Feature

Six components. V0 or Lovable builds the dashboard UI and event ingestion correctly on the first prompt. The materialized views and pg_cron schedule are what must be configured manually in Supabase — AI tools cannot reach the Supabase Dashboard to set these up for you.

- **Event ingestion layer** (backend): A Supabase Edge Function (Deno) deployed at POST /track receives event payloads with {event_name, user_id, session_id, properties, timestamp}. It validates the payload, batches inserts into the events table using the Supabase service_role key, and rate-limits ingestion per user via an Upstash Redis counter (free tier). Never use the anon key for server-side writes to the events table.
- **Events table and materialized views** (data): The events table stores every raw event. Three materialized views aggregate it: mv_daily_active_users (distinct user_ids per day), mv_retention_cohorts (first-event date bucketed by week, with day 1/7/30 counts), and mv_funnel_steps (event sequence counts for a configurable funnel). Views are refreshed every hour via a pg_cron job. All three are indexed on (user_id, event_name, created_at) for fast dashboard queries.
- **Admin dashboard UI** (ui): A Next.js Server Component page (V0 path) or Lovable React page protected by a Supabase Auth role check. Uses Recharts for line charts (DAU trend), bar charts (event frequency), and FunnelChart (conversion funnel). The cohort retention table uses shadcn/ui DataTable. The date range picker uses react-day-picker with presets. All data is fetched server-side via Supabase RPC calls to avoid exposing the service_role key on the client.
- **Funnel builder** (ui): A drag-and-drop ordered list where the admin selects event names (from a dropdown populated by SELECT DISTINCT event_name from events) and defines the funnel sequence. On submit, the UI calls a Supabase RPC function calculate_funnel(event_array text[], start_date date, end_date date) that returns step counts and conversion rates. The result renders as a Recharts FunnelChart with percentage labels between steps.
- **Real-time active user counter** (backend): A Supabase Realtime channel subscription on the events table (INSERT events only) increments an in-memory count on the admin dashboard. A sliding window query — SELECT COUNT(DISTINCT user_id) FROM events WHERE created_at > now() - interval '5 minutes' — provides the initial count on page load and serves as a fallback when the Realtime subscription disconnects.
- **CSV export** (service): A Next.js Server Action (or Supabase Edge Function for non-Next.js builds) runs a parameterized query against the events table for the selected date range and streams the result as a CSV file using Papa Parse on the client. The export respects the same admin-only RLS as the dashboard — the service_role key is used server-side only.

## Data model

Two core tables plus three materialized views. Run this in the Supabase SQL Editor (Dashboard → SQL Editor → New query). The RLS policies on the events table are the most critical part — get them wrong and all user behavior data is readable by any logged-in user.

```sql
-- Core events table
create table public.events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade not null,
  session_id uuid not null,
  event_name text not null,
  properties jsonb not null default '{}',
  created_at timestamptz not null default now()
);

-- User sessions table
create table public.user_sessions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade not null,
  started_at timestamptz not null default now(),
  ended_at timestamptz,
  page_count int not null default 0,
  platform text check (platform in ('web', 'ios', 'android'))
);

-- RLS
alter table public.events enable row level security;
alter table public.user_sessions enable row level security;

-- Events: authenticated users can insert their own events
create policy "Users can insert own events"
  on public.events for insert
  with check (auth.uid() = user_id);

-- Events: only admins can read any events
create policy "Admins can read all events"
  on public.events for select
  using (auth.jwt() ->> 'role' = 'admin');

-- Sessions: users can insert and update their own sessions
create policy "Users can manage own sessions"
  on public.user_sessions for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Sessions: only admins can read any session
create policy "Admins can read all sessions"
  on public.user_sessions for select
  using (auth.jwt() ->> 'role' = 'admin');

-- Indexes for dashboard query performance
create index events_user_event_time_idx
  on public.events (user_id, event_name, created_at desc);

create index events_created_at_idx
  on public.events (created_at desc);

create index events_session_idx
  on public.events (session_id, event_name);

-- Materialized view: daily active users
create materialized view public.mv_daily_active_users as
  select
    date_trunc('day', created_at) as day,
    count(distinct user_id) as dau
  from public.events
  where created_at >= now() - interval '90 days'
  group by 1
  order by 1 desc;

create unique index mv_dau_day_idx on public.mv_daily_active_users (day);

-- Materialized view: retention cohorts (day 0 = first event)
create materialized view public.mv_retention_cohorts as
  with first_events as (
    select user_id, min(created_at) as cohort_start
    from public.events
    group by user_id
  ),
  cohort_activity as (
    select
      fe.user_id,
      date_trunc('week', fe.cohort_start) as cohort_week,
      (e.created_at::date - fe.cohort_start::date) as days_since_start
    from first_events fe
    join public.events e on e.user_id = fe.user_id
  )
  select
    cohort_week,
    count(distinct user_id) as cohort_size,
    count(distinct case when days_since_start between 1 and 1 then user_id end) as day_1,
    count(distinct case when days_since_start between 7 and 7 then user_id end) as day_7,
    count(distinct case when days_since_start between 30 and 30 then user_id end) as day_30
  from cohort_activity
  group by cohort_week
  order by cohort_week desc;

create unique index mv_cohorts_week_idx on public.mv_retention_cohorts (cohort_week);

-- Materialized view: last_refreshed tracking
create table public.mv_refresh_log (
  view_name text primary key,
  last_refreshed_at timestamptz default now()
);

insert into public.mv_refresh_log (view_name) values
  ('mv_daily_active_users'),
  ('mv_retention_cohorts');

-- RPC function to refresh all views (callable from dashboard 'Refresh now' button)
create or replace function public.refresh_analytics_views()
returns void
language plpgsql
security definer
as $$
begin
  -- Only admins can call this
  if auth.jwt() ->> 'role' != 'admin' then
    raise exception 'Admin only';
  end if;
  refresh materialized view concurrently public.mv_daily_active_users;
  refresh materialized view concurrently public.mv_retention_cohorts;
  update public.mv_refresh_log set last_refreshed_at = now();
end;
$$;

-- RPC function for funnel calculation
create or replace function public.calculate_funnel(
  event_array text[],
  start_date date,
  end_date date
)
returns table(step_name text, step_index int, user_count bigint, conversion_rate numeric)
language plpgsql
security definer
as $$
declare
  i int;
  step text;
begin
  -- Only admins can call this
  if auth.jwt() ->> 'role' != 'admin' then
    raise exception 'Admin only';
  end if;

  for i in 1..array_length(event_array, 1) loop
    step := event_array[i];
    return query
      select
        step as step_name,
        i as step_index,
        count(distinct e.user_id) as user_count,
        round(
          count(distinct e.user_id)::numeric /
          nullif((select count(distinct user_id) from public.events
                  where event_name = event_array[1]
                  and created_at::date between start_date and end_date), 0) * 100,
          1
        ) as conversion_rate
      from public.events e
      where e.event_name = step
        and e.created_at::date between start_date and end_date
        and (i = 1 or exists (
          select 1 from public.events prev
          where prev.user_id = e.user_id
            and prev.event_name = event_array[i - 1]
            and prev.created_at < e.created_at
        ));
  end loop;
end;
$$;
```

After running this SQL, enable pg_cron in the Supabase Dashboard under Database → Extensions, then add a cron job: select cron.schedule('refresh-analytics', '0 * * * *', 'select refresh_analytics_views()'); — this runs the refresh every hour. The admin role in the RLS policy is a custom JWT claim; set it via a Supabase Auth hook or an Edge Function that updates the user's app_metadata when you promote them to admin.

## Build paths

### Lovable — fit 3/10, 2-4 days

Lovable rapidly scaffolds the Supabase schema and wires a React dashboard with Recharts charts. Best choice if you want an all-in-one build without managing a Next.js deploy separately. Requires manual configuration of materialized views and pg_cron in the Supabase Dashboard.

1. Create a new Lovable project with Lovable Cloud enabled so Supabase auth and tables are provisioned automatically
2. Paste the prompt below; Lovable will generate the events table, Edge Function for ingestion, and the admin dashboard page with Recharts charts
3. Open Supabase Dashboard → SQL Editor and run the materialized view and pg_cron SQL from this page — Lovable cannot configure these
4. Go to Supabase Dashboard → Auth → Users, find your admin account, and add the custom claim role: admin in app_metadata — this is what the RLS policy checks
5. Open Supabase Dashboard → Edge Functions, verify the /track function deployed correctly and has the service_role key set as a secret (not in code)
6. Test the dashboard by sending a few test events from your app to the /track endpoint, then confirm they appear in the events table and that non-admin users cannot read them

Starter prompt:

```
Build an admin analytics dashboard. Use Supabase as the backend. Create a Supabase Edge Function at POST /track that receives {event_name: string, user_id: string, session_id: string, properties: object, timestamp: string} and inserts into an events table (id uuid, user_id uuid references auth.users, session_id uuid, event_name text, properties jsonb default '{}', created_at timestamptz default now()). Also create user_sessions (id uuid, user_id uuid, started_at timestamptz, ended_at timestamptz, page_count int, platform text). Enable RLS on both tables: events INSERT allowed for auth.uid() = user_id; events SELECT only for users where auth.jwt() ->> 'role' = 'admin'. Build an admin dashboard page at /admin/analytics that is only accessible to users with the admin role. Dashboard sections: (1) a line chart using Recharts showing daily active users for the last 30 days, pulling from a Supabase query grouped by day; (2) a shadcn/ui DataTable showing the top 20 most frequent event names and their counts; (3) a user lookup input where the admin types an email address and sees that user's last 50 events in a timeline list; (4) a date range picker using react-day-picker with presets for 7d, 30d, 90d that filters all charts. Add a loading skeleton while data loads and an empty state if no events exist yet. Include a 'Export CSV' button that downloads the raw events for the selected date range using Papa Parse. Show an error state if the Edge Function ingestion fails. Default date range: last 30 days. Tag each event with platform: 'web' by default, or 'ios'/'android' from a platform field in the properties object.
```

Limitations:

- Materialized views for retention cohorts and funnel steps must be created manually in the Supabase SQL Editor — Lovable does not generate or refresh pg_cron jobs
- Real-time counter requires manual Supabase Realtime subscription code; Lovable may generate a polling interval instead
- AI may generate the dashboard using the anon key with no role filter on events reads — inspect generated code and correct to admin-only RLS before deploying
- Funnel builder drag-and-drop UI typically requires a follow-up prompt iteration; include it explicitly if needed from day one

### V0 — fit 4/10, 2-3 days

Strongest choice for the dashboard UI layer. V0 produces high-quality Next.js admin pages with shadcn/ui DataTable, Recharts, and react-day-picker out of the box. Server Components keep sensitive event data server-side. Pairs with Supabase or Neon for the data layer.

1. Prompt V0 with the spec below; it generates the admin page with Next.js Server Components, Recharts charts, and a shadcn/ui DataTable
2. In the V0 Vars panel, add NEXT_PUBLIC_SUPABASE_URL and SUPABASE_SERVICE_KEY (service role key — only ever used server-side, never in client components)
3. Run the full SQL schema from this page in the Supabase SQL Editor to create the events table, materialized views, RLS policies, and RPC functions
4. Enable pg_cron in Supabase Dashboard under Database → Extensions and add the hourly refresh cron job as described in the data model note
5. Deploy to Vercel via V0's Git panel; add SUPABASE_SERVICE_KEY as a Vercel environment variable (not a NEXT_PUBLIC_ variable — it must never reach the browser)
6. Set the admin role in your Supabase user's app_metadata, then verify the /admin/analytics route returns data and that a non-admin user gets a 403 redirect

Starter prompt:

```
Create a Next.js admin analytics dashboard at /admin/analytics. Protect the route: if the authenticated Supabase user's app_metadata.role is not 'admin', redirect to /. Use Next.js Server Components to fetch all data server-side with the Supabase service_role key (from process.env.SUPABASE_SERVICE_KEY). Never pass the service key to a client component. Dashboard layout: full-width header with 'User Activity Analytics' heading, date range controls, and an 'Export CSV' button. Content sections below: (1) a stat row with three KPI cards — DAU today, total events in range, active sessions in last 5 minutes (use a Supabase REST query: SELECT COUNT(DISTINCT user_id) FROM events WHERE created_at > now() - interval '5 minutes'); (2) a Recharts LineChart showing daily active users by day for the selected date range, data from SELECT date_trunc('day', created_at) as day, COUNT(DISTINCT user_id) as dau FROM events WHERE created_at BETWEEN start AND end GROUP BY 1 ORDER BY 1; (3) a shadcn/ui DataTable listing the top 20 event names by count with columns: event_name, count, percentage of total; (4) a retention cohort table from mv_retention_cohorts showing cohort_week, cohort_size, day_1 percentage, day_7 percentage, day_30 percentage — use a heatmap color scale on percentage cells (green = high, red = low); (5) a user lookup section: text input for email, on submit fetch SELECT * FROM events WHERE user_id = (SELECT id FROM auth.users WHERE email = $email) ORDER BY created_at DESC LIMIT 50 and render as a vertical timeline with event name, properties preview, and relative timestamp. Add a date range picker using react-day-picker with presets 7d, 30d, 90d, defaulting to last 30 days. The Export CSV button triggers a Server Action that queries events for the date range, uses Papa Parse to serialize, and returns as a file download response. Include loading skeleton, empty state 'No events recorded yet', and error state 'Failed to load analytics data'. Handle the localStorage SSR issue: never access localStorage in Server Components or during server render.
```

Limitations:

- V0 does not auto-provision Supabase materialized views or pg_cron jobs — these must be configured manually in the Supabase Dashboard after generation
- Funnel builder drag-and-drop may require additional prompt iterations; start with a static funnel selection if the drag interaction adds complexity
- Event ingestion endpoint (Supabase Edge Function) needs manual env var wiring in Vercel Dashboard for the service_role key
- Occasional shadcn/ui registry mismatches in generated imports require a follow-up 'fix the imports' prompt

### Flutterflow — fit 2/10, 3-5 days

Weak fit for this feature. FlutterFlow can build a mobile-facing view of a single user's own activity stats, but it is not designed for admin dashboards with complex SQL views, cross-user data funnels, and CSV exports. Only choose this path if the analytics view is user-facing, not admin-facing.

1. Create a FlutterFlow page for the user-facing stats screen: bind a Supabase query to SELECT COUNT(*) FROM events WHERE user_id = auth.uid() AND created_at > now() - interval '30 days' to show the user their own event count
2. Add an fl_chart LineChart widget to visualize the user's own daily activity, binding to a grouped Supabase query
3. For admin-only data access, add a Supabase RPC call to the calculate_funnel function with an admin check; handle the 'Admin only' error with a Flutter SnackBar
4. Implement CSV export as a custom Dart action that queries Supabase and uses the csv pub.dev package to write a file to the device download folder

Limitations:

- No native support for materialized views or pg_cron — all admin-side data prep must happen in Supabase, not FlutterFlow
- Admin-only dashboard behind role-based auth is cumbersome to implement visually in FlutterFlow — conditional page access requires multiple conditional actions
- CSV export requires a custom Dart action and file system permissions; not achievable via the visual builder alone
- The cohort retention table and funnel chart are significantly harder to build in fl_chart than in Recharts — no built-in funnel chart widget

### Custom — fit 5/10, 2-4 weeks

Correct choice for production-grade analytics: Next.js admin panel with Supabase materialized views, ClickHouse for high-volume event storage, and PostHog self-hosted for behavioral event querying. The right call when event volume exceeds 1M/day or compliance requirements demand it.

1. Next.js admin panel with Supabase as the primary database: materialized views for cohorts, pg_cron for hourly refresh, service_role key used server-side only in Server Components
2. Event ingestion via a Supabase Edge Function with Upstash Redis rate limiting; at 1M+ events/day, migrate the events table to ClickHouse (ClickHouse Cloud from $0 on free tier, then $0.006/GB ingested) for OLAP queries without blocking Postgres writes
3. Self-hosted PostHog (free) for event-level querying, funnel analysis, and cohort building — Postgres materialized views are replaced by PostHog's native query engine
4. GDPR right-to-erasure: a deletion pipeline that cascades deletes across events, user_sessions, and PostHog using the PostHog Persons API — tested under audit with a staging dataset

Limitations:

- Significant upfront engineering cost — 2-4 weeks vs 2-4 days for an AI scaffold
- ClickHouse migration from Postgres requires a dual-write period and backfill — non-trivial operational work
- Self-hosted PostHog requires dedicated infrastructure and monitoring; a lapsed instance means losing event history

## Gotchas

- **Admin dashboard exposes all user events to any authenticated user** — Both Lovable and V0 commonly generate Supabase event queries using supabase.from('events').select('*') with the anon key and no user filter. Because the default Supabase RLS for a new table is disabled, or because the generated policy only checks auth.role() = 'authenticated', any logged-in user can read every other user's click history, session data, and behavior. This is a silent data breach — users have no indication it's happening. Fix: Add the RLS policy from this page's data model: USING (auth.jwt() ->> 'role' = 'admin') on the events SELECT policy. Set the admin role in Supabase Auth by adding role: 'admin' to the user's app_metadata via the Supabase Dashboard or a privileged Edge Function. On the client, use the service_role key only in Server Components or Edge Functions — never in browser-facing code.
- **Materialized view returns yesterday's data because pg_cron hasn't run yet** — The retention cohort table and DAU chart pull from materialized views refreshed by pg_cron every hour. When an admin first opens the dashboard in the morning, the views show data up to the last refresh point — potentially hours old. Without any indication of staleness, the admin concludes the feature is broken and files a bug report. Fix: Query the mv_refresh_log table alongside the materialized view and show 'Last updated X minutes ago' next to every chart that draws from a materialized view. Add a 'Refresh now' button that calls the refresh_analytics_views() Supabase RPC function — this triggers a CONCURRENTLY refresh so reads are not blocked. The RPC function is included in the data model SQL above.
- **Duplicate session_start events inflate cohort numbers in development** — React's Strict Mode in development intentionally mounts components twice, causing useEffect to fire twice and sending two session_start events per page load. In production (where Strict Mode effects fire once) the numbers are correct, but the development-mode inflated events end up in the same events table if you're pointing at a production or staging Supabase project during development. Fix: Generate a session_id UUID once per session using sessionStorage (not localStorage — localStorage persists across sessions): if (!sessionStorage.getItem('session_id')) { sessionStorage.setItem('session_id', crypto.randomUUID()); }. Add a UNIQUE(session_id, event_name) constraint for session-boundary events (session_start, session_end) so the database rejects duplicates. Use a separate Supabase project for local development to prevent test events from polluting production analytics.
- **Real-time active user counter freezes when the browser tab goes to the background** — Supabase Realtime WebSocket subscriptions are throttled or suspended by the browser after the tab becomes inactive for about 60 seconds. When the admin returns to the dashboard, the counter shows a stale number from before the inactivity period — new events that arrived while the tab was in the background are not reflected. The counter can be off by hundreds of events in a busy app. Fix: Listen to the document.visibilitychange event: when visibility changes to 'visible', re-subscribe to the Realtime channel and re-fetch the last-5-minutes count via a REST fallback query (SELECT COUNT(DISTINCT user_id) FROM events WHERE created_at > now() - interval '5 minutes'). This ensures the counter is always accurate within 1 request round-trip of the admin returning to the tab.
- **localStorage access crashes SSR in V0/Next.js generated code** — V0 sometimes generates code that reads localStorage (for persisting the selected date range) during the initial server render in a Next.js Server Component or during SSR of a Client Component. This throws 'ReferenceError: localStorage is not defined' because localStorage does not exist in the Node.js environment. The error only surfaces on first page load; client-side navigation works fine, which makes it hard to reproduce locally if you're not testing a cold SSR load. Fix: Wrap all localStorage access in a typeof window !== 'undefined' check before reading: const savedRange = typeof window !== 'undefined' ? localStorage.getItem('analytics_range') : null. For a cleaner solution, use cookies via Next.js next/headers to persist user preferences — cookies are available server-side and do not cause SSR errors.

## Best practices

- Name events in past tense with a noun-verb pattern — 'user_signed_up', 'feature_opened', 'export_clicked' — and document the schema in a shared events catalog before writing a line of code; renaming events mid-product breaks all historical funnel analysis
- Always use the service_role key server-side for dashboard queries and the anon key with RLS for client-side event ingestion — never use the service_role key in browser-facing code
- Add a last_refreshed_at display to every chart backed by a materialized view; admins who see stale data without timestamps will assume the feature is broken
- Tag every event with platform ('web', 'ios', 'android') and app_version in the properties JSONB from day one — retrofitting these dimensions into existing event data requires a full table scan
- Generate session_id once per browser session using sessionStorage and include it in every event payload — without session_id, you cannot calculate session duration, page depth, or funnel steps within a session
- Rate-limit the /track endpoint from the first deploy using Upstash Redis; a single automated test script that fires events in a tight loop can fill your Supabase free-tier DB in hours
- Implement GDPR right-to-erasure from the start: a single DELETE FROM events WHERE user_id = $id cascades through the foreign key, but you also need to purge the user from any materialized views — schedule a post-deletion view refresh
- Build the CSV export with streaming for date ranges that produce more than 10K rows — loading 100K events into memory before streaming kills the server function and produces a timeout

## Frequently asked questions

### How do I track events without slowing down my app?

Send events to the Supabase Edge Function asynchronously — fire and forget from the client without awaiting the response. On the client side, use navigator.sendBeacon() for session_end events (which fire when the user closes the tab) so they are not dropped. In the Edge Function, batch inserts using a single INSERT ... VALUES (row1), (row2), ... statement when multiple events arrive in quick succession. This keeps the tracking call off the critical rendering path.

### Can I see a specific user's full activity history?

Yes — the user lookup section of the dashboard queries SELECT * FROM events WHERE user_id = (SELECT id FROM auth.users WHERE email = $email) ORDER BY created_at DESC LIMIT 50. The RLS policy ensures only admins can run this query. In Supabase Dashboard you can also use the Table Editor with a user_id filter for ad-hoc lookups during debugging, since the Dashboard uses the service_role key and bypasses RLS.

### How do I build a funnel showing where users drop off during onboarding?

Define the onboarding funnel as an ordered array of event names — for example ['user_signed_up', 'profile_completed', 'first_feature_used', 'invite_sent']. Call the calculate_funnel(event_array, start_date, end_date) RPC function from the data model above, which returns step counts and conversion rates. Render the result as a Recharts FunnelChart. Name your events consistently from the start — you cannot retroactively reconstruct a funnel from events that were named differently in early builds.

### What is the difference between a daily active user and a session?

A daily active user (DAU) is a count of distinct user_ids with at least one event on a given calendar day — a user counts once no matter how many times they open the app. A session is a single continuous usage period with a start and end timestamp stored in user_sessions; one user can have multiple sessions in a day. DAU tells you reach; session data tells you depth of engagement. Both are useful and complementary.

### How do I export all event data to a spreadsheet?

Click the Export CSV button on the dashboard. It triggers a Server Action that queries the events table for the selected date range using the service_role key, passes the result through Papa Parse's unparse() function to generate CSV format, and returns the file as a download response with Content-Disposition: attachment. For date ranges that produce more than 10K rows, the Server Action streams the response in chunks — do not try to load the full result set into memory first.

### Can I track events from a mobile app and web app in the same dashboard?

Yes — include a platform field in every event's properties JSONB (set to 'web', 'ios', or 'android' depending on the client). The events table schema accepts all sources through the same /track Edge Function endpoint. On the dashboard, add a platform filter chip to segment charts by source. The retention cohort view automatically includes all platforms unless you add a WHERE properties->>'platform' = 'web' clause.

### How do I make sure admin-only data isn't exposed to regular users?

Two layers are required and both must be in place. First, RLS on the events table: add a SELECT policy with USING (auth.jwt() ->> 'role' = 'admin') — this blocks any client-side query from a non-admin user at the database level, regardless of what code they run. Second, route protection: in Next.js, check the admin role in the Server Component and redirect to / if the user is not an admin. Set the admin role by adding role: 'admin' to the user's app_metadata in the Supabase Auth Dashboard. Never rely on client-side redirects alone.

### Do I need PostHog or Mixpanel, or can I build this myself in Supabase?

For most apps under 1M events per day, building in Supabase is the right choice: zero per-event fees, full data ownership, and the dashboard you build is exactly what your team needs. PostHog or Mixpanel become worth considering when you need features like session recordings, heatmaps, or feature flags, or when your event volume makes Postgres OLAP queries too slow. PostHog self-hosted is a solid middle ground — open-source, free to run, and purpose-built for behavioral analytics while still connecting to your Supabase user table.

---

Source: https://www.rapidevelopers.com/app-features/user-activity-analytics
© RapidDev — https://www.rapidevelopers.com/app-features/user-activity-analytics