How to Add Virtual Workshops to Your App — Copy-Paste Prompts Included

1create table public.workshops (
2  id uuid primary key default gen_random_uuid(),
3  host_id uuid references auth.users(id) on delete cascade not null,
4  title text not null,
5  description text,
6  scheduled_at timestamptz not null,
7  duration_min int not null default 60,
8  price_cents int not null default 0,
9  max_attendees int not null default 50,
10  daily_room_name text,
11  stripe_product_id text,
12  recording_url text,
13  status text not null default 'scheduled' check (status in ('scheduled','live','ended','cancelled')),
14  created_at timestamptz not null default now()
15);
16
17create table public.registrations (
18  id uuid primary key default gen_random_uuid(),
19  workshop_id uuid references public.workshops(id) on delete cascade not null,
20  user_id uuid references auth.users(id) on delete cascade not null,
21  stripe_payment_intent_id text,
22  payment_status text not null default 'pending' check (payment_status in ('pending','confirmed','refunded')),
23  joined_at timestamptz,
24  created_at timestamptz not null default now(),
25  unique(workshop_id, user_id)
26);
27
28create table public.workshop_chat (
29  id uuid primary key default gen_random_uuid(),
30  workshop_id uuid references public.workshops(id) on delete cascade not null,
31  user_id uuid references auth.users(id) on delete cascade not null,
32  message text not null,
33  is_question bool not null default false,
34  upvote_count int not null default 0,
35  created_at timestamptz not null default now()
36);
37
38create table public.workshop_polls (
39  id uuid primary key default gen_random_uuid(),
40  workshop_id uuid references public.workshops(id) on delete cascade not null,
41  question text not null,
42  options jsonb not null,
43  responses jsonb not null default '{}',
44  is_active bool not null default false,
45  created_at timestamptz not null default now()
46);
47
48alter table public.workshops enable row level security;
49alter table public.registrations enable row level security;
50alter table public.workshop_chat enable row level security;
51alter table public.workshop_polls enable row level security;
52
53-- Workshops: hosts see all their own; attendees see scheduled/live/ended
54create policy "Host manages own workshops"
55  on public.workshops for all
56  using (auth.uid() = host_id);
57
58create policy "Anyone can view public workshops"
59  on public.workshops for select
60  using (status in ('scheduled', 'live', 'ended'));
61
62-- Registrations: each user sees their own; host sees registrations for their workshops
63create policy "User sees own registrations"
64  on public.registrations for select
65  using (auth.uid() = user_id);
66
67create policy "Host sees workshop registrations"
68  on public.registrations for select
69  using (
70    auth.uid() = (select host_id from public.workshops where id = workshop_id)
71  );
72
73create policy "User can register"
74  on public.registrations for insert
75  with check (auth.uid() = user_id);
76
77-- Chat: only confirmed attendees or host can read/write
78create policy "Confirmed attendees and host can chat"
79  on public.workshop_chat for select
80  using (
81    auth.uid() in (
82      select user_id from public.registrations
83      where workshop_id = workshop_chat.workshop_id and payment_status = 'confirmed'
84    )
85    or auth.uid() = (select host_id from public.workshops where id = workshop_chat.workshop_id)
86  );
87
88create policy "Confirmed attendees and host can send messages"
89  on public.workshop_chat for insert
90  with check (
91    auth.uid() in (
92      select user_id from public.registrations
93      where workshop_id = workshop_chat.workshop_id and payment_status = 'confirmed'
94    )
95    or auth.uid() = (select host_id from public.workshops where id = workshop_chat.workshop_id)
96  );
97
98-- Polls: visible to confirmed attendees and host
99create policy "Confirmed attendees and host can view polls"
100  on public.workshop_polls for select
101  using (
102    auth.uid() in (
103      select user_id from public.registrations
104      where workshop_id = workshop_polls.workshop_id and payment_status = 'confirmed'
105    )
106    or auth.uid() = (select host_id from public.workshops where id = workshop_polls.workshop_id)
107  );
108
109create index registrations_workshop_status_idx
110  on public.registrations (workshop_id, payment_status);
111
112create index chat_workshop_created_idx
113  on public.workshop_chat (workshop_id, created_at);