How to Add Smart Notifications Based on User Behavior — Copy-Paste Prompts Inside

1-- User event tracking table
2create table public.user_events (
3  id uuid default gen_random_uuid() primary key,
4  user_id uuid references auth.users not null,
5  event_type text not null, -- 'view', 'search', 'favorite', 'add_to_cart', 'complete'
6  entity_id uuid,
7  entity_type text, -- 'product', 'article', 'course', etc.
8  metadata jsonb,
9  created_at timestamptz default now()
10);
11
12alter table public.user_events enable row level security;
13
14create policy "Users can insert own events"
15  on public.user_events for insert
16  with check (auth.uid() = user_id);
17
18create policy "Service role can read all events"
19  on public.user_events for select
20  to service_role using (true);
21
22-- Critical index for per-user aggregation performance
23create index user_events_user_type_time_idx
24  on public.user_events (user_id, event_type, created_at desc);
25
26-- Behavior summary: upserted hourly by aggregation job
27create table public.user_behavior_summary (
28  user_id uuid primary key references auth.users,
29  preferred_hour int default 10, -- 0-23, defaults to 10am
30  preferred_variant text default 'A', -- stable A/B assignment
31  events_last_7d int default 0,
32  last_active_at timestamptz,
33  updated_at timestamptz default now()
34);
35
36alter table public.user_behavior_summary enable row level security;
37
38create policy "Users can read own behavior summary"
39  on public.user_behavior_summary for select
40  using (auth.uid() = user_id);
41
42create policy "Service role can manage behavior summaries"
43  on public.user_behavior_summary for all
44  to service_role using (true);
45
46-- Notification candidates: scored and scheduled sends
47create table public.notification_candidates (
48  id uuid default gen_random_uuid() primary key,
49  user_id uuid references auth.users not null,
50  type text not null,
51  variant text,
52  score int,
53  scheduled_for timestamptz,
54  sent_at timestamptz,
55  opened_at timestamptz,
56  suppressed bool default false,
57  suppression_reason text
58);
59
60alter table public.notification_candidates enable row level security;
61
62create policy "Users can read own notification candidates"
63  on public.notification_candidates for select
64  using (auth.uid() = user_id);
65
66create policy "Service role can manage candidates"
67  on public.notification_candidates for all
68  to service_role using (true);
69
70create index notification_candidates_user_sent_idx
71  on public.notification_candidates (user_id, sent_at desc);
72
73-- Fatigue tracking
74create table public.notification_fatigue (
75  user_id uuid primary key references auth.users,
76  sent_24h int default 0,
77  ignored_streak int default 0,
78  last_reset timestamptz default now()
79);
80
81alter table public.notification_fatigue enable row level security;
82
83create policy "Service role manages fatigue records"
84  on public.notification_fatigue for all
85  to service_role using (true);
86
87-- Notification variants definition table
88create table public.notification_variants (
89  id uuid default gen_random_uuid() primary key,
90  type text not null,
91  variant text not null, -- 'A', 'B', 'C'
92  title_template text not null,
93  body_template text not null, -- supports {{entity_name}} placeholder
94  is_active bool default true,
95  unique(type, variant)
96);
97
98alter table public.notification_variants enable row level security;
99
100create policy "Authenticated users can read variants"
101  on public.notification_variants for select
102  to authenticated using (true);