How to Add In-App Messaging to Your App (Copy-Paste Prompts Included)

1create table public.conversations (
2  id uuid primary key default gen_random_uuid(),
3  type text not null default 'direct' check (type in ('direct', 'group')),
4  name text,
5  created_at timestamptz not null default now()
6);
7
8create table public.conversation_participants (
9  conversation_id uuid references public.conversations(id) on delete cascade not null,
10  user_id uuid references auth.users(id) on delete cascade not null,
11  last_read_at timestamptz,
12  joined_at timestamptz not null default now(),
13  primary key (conversation_id, user_id)
14);
15
16create table public.messages (
17  id uuid primary key default gen_random_uuid(),
18  conversation_id uuid references public.conversations(id) on delete cascade not null,
19  sender_id uuid references auth.users(id) on delete set null,
20  body text,
21  attachment_url text,
22  deleted_at timestamptz,
23  created_at timestamptz not null default now()
24);
25
26alter table public.conversations enable row level security;
27alter table public.conversation_participants enable row level security;
28alter table public.messages enable row level security;
29
30-- Participants can read conversations they belong to
31create policy "participants can select conversations"
32  on public.conversations for select
33  using (
34    auth.uid() in (
35      select user_id from public.conversation_participants
36      where conversation_id = conversations.id
37    )
38  );
39
40-- Participants can insert into conversation_participants for their own user
41create policy "participants can insert"
42  on public.conversation_participants for insert
43  with check (auth.uid() = user_id);
44
45-- Participants can select their own participant rows
46create policy "participants can select own rows"
47  on public.conversation_participants for select
48  using (auth.uid() = user_id);
49
50-- Participants can update their own last_read_at
51create policy "participants can update own last_read_at"
52  on public.conversation_participants for update
53  using (auth.uid() = user_id);
54
55-- Participants can read messages in their conversations
56create policy "participants can select messages"
57  on public.messages for select
58  using (
59    auth.uid() in (
60      select user_id from public.conversation_participants
61      where conversation_id = messages.conversation_id
62    )
63  );
64
65-- Participants can send messages in their conversations
66create policy "participants can insert messages"
67  on public.messages for insert
68  with check (
69    auth.uid() = sender_id
70    and auth.uid() in (
71      select user_id from public.conversation_participants
72      where conversation_id = messages.conversation_id
73    )
74  );
75
76-- Users can soft-delete their own messages
77create policy "sender can soft delete messages"
78  on public.messages for update
79  using (auth.uid() = sender_id);
80
81create index messages_conversation_created_idx
82  on public.messages (conversation_id, created_at desc);
83
84create index conversation_participants_user_idx
85  on public.conversation_participants (user_id);