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

1-- Core events table
2create table public.events (
3  id uuid primary key default gen_random_uuid(),
4  user_id uuid references auth.users(id) on delete cascade not null,
5  session_id uuid not null,
6  event_name text not null,
7  properties jsonb not null default '{}',
8  created_at timestamptz not null default now()
9);
10
11-- User sessions table
12create table public.user_sessions (
13  id uuid primary key default gen_random_uuid(),
14  user_id uuid references auth.users(id) on delete cascade not null,
15  started_at timestamptz not null default now(),
16  ended_at timestamptz,
17  page_count int not null default 0,
18  platform text check (platform in ('web', 'ios', 'android'))
19);
20
21-- RLS
22alter table public.events enable row level security;
23alter table public.user_sessions enable row level security;
24
25-- Events: authenticated users can insert their own events
26create policy "Users can insert own events"
27  on public.events for insert
28  with check (auth.uid() = user_id);
29
30-- Events: only admins can read any events
31create policy "Admins can read all events"
32  on public.events for select
33  using (auth.jwt() ->> 'role' = 'admin');
34
35-- Sessions: users can insert and update their own sessions
36create policy "Users can manage own sessions"
37  on public.user_sessions for all
38  using (auth.uid() = user_id)
39  with check (auth.uid() = user_id);
40
41-- Sessions: only admins can read any session
42create policy "Admins can read all sessions"
43  on public.user_sessions for select
44  using (auth.jwt() ->> 'role' = 'admin');
45
46-- Indexes for dashboard query performance
47create index events_user_event_time_idx
48  on public.events (user_id, event_name, created_at desc);
49
50create index events_created_at_idx
51  on public.events (created_at desc);
52
53create index events_session_idx
54  on public.events (session_id, event_name);
55
56-- Materialized view: daily active users
57create materialized view public.mv_daily_active_users as
58  select
59    date_trunc('day', created_at) as day,
60    count(distinct user_id) as dau
61  from public.events
62  where created_at >= now() - interval '90 days'
63  group by 1
64  order by 1 desc;
65
66create unique index mv_dau_day_idx on public.mv_daily_active_users (day);
67
68-- Materialized view: retention cohorts (day 0 = first event)
69create materialized view public.mv_retention_cohorts as
70  with first_events as (
71    select user_id, min(created_at) as cohort_start
72    from public.events
73    group by user_id
74  ),
75  cohort_activity as (
76    select
77      fe.user_id,
78      date_trunc('week', fe.cohort_start) as cohort_week,
79      (e.created_at::date - fe.cohort_start::date) as days_since_start
80    from first_events fe
81    join public.events e on e.user_id = fe.user_id
82  )
83  select
84    cohort_week,
85    count(distinct user_id) as cohort_size,
86    count(distinct case when days_since_start between 1 and 1 then user_id end) as day_1,
87    count(distinct case when days_since_start between 7 and 7 then user_id end) as day_7,
88    count(distinct case when days_since_start between 30 and 30 then user_id end) as day_30
89  from cohort_activity
90  group by cohort_week
91  order by cohort_week desc;
92
93create unique index mv_cohorts_week_idx on public.mv_retention_cohorts (cohort_week);
94
95-- Materialized view: last_refreshed tracking
96create table public.mv_refresh_log (
97  view_name text primary key,
98  last_refreshed_at timestamptz default now()
99);
100
101insert into public.mv_refresh_log (view_name) values
102  ('mv_daily_active_users'),
103  ('mv_retention_cohorts');
104
105-- RPC function to refresh all views (callable from dashboard 'Refresh now' button)
106create or replace function public.refresh_analytics_views()
107returns void
108language plpgsql
109security definer
110as $$
111begin
112  -- Only admins can call this
113  if auth.jwt() ->> 'role' != 'admin' then
114    raise exception 'Admin only';
115  end if;
116  refresh materialized view concurrently public.mv_daily_active_users;
117  refresh materialized view concurrently public.mv_retention_cohorts;
118  update public.mv_refresh_log set last_refreshed_at = now();
119end;
120$$;
121
122-- RPC function for funnel calculation
123create or replace function public.calculate_funnel(
124  event_array text[],
125  start_date date,
126  end_date date
127)
128returns table(step_name text, step_index int, user_count bigint, conversion_rate numeric)
129language plpgsql
130security definer
131as $$
132declare
133  i int;
134  step text;
135begin
136  -- Only admins can call this
137  if auth.jwt() ->> 'role' != 'admin' then
138    raise exception 'Admin only';
139  end if;
140
141  for i in 1..array_length(event_array, 1) loop
142    step := event_array[i];
143    return query
144      select
145        step as step_name,
146        i as step_index,
147        count(distinct e.user_id) as user_count,
148        round(
149          count(distinct e.user_id)::numeric /
150          nullif((select count(distinct user_id) from public.events
151                  where event_name = event_array[1]
152                  and created_at::date between start_date and end_date), 0) * 100,
153          1
154        ) as conversion_rate
155      from public.events e
156      where e.event_name = step
157        and e.created_at::date between start_date and end_date
158        and (i = 1 or exists (
159          select 1 from public.events prev
160          where prev.user_id = e.user_id
161            and prev.event_name = event_array[i - 1]
162            and prev.created_at < e.created_at
163        ));
164  end loop;
165end;
166$$;