How to Add Customer Support Chat to Your App (Copy-Paste Prompts Included)

1create sequence public.message_seq;
2
3create type public.conversation_status as enum ('open', 'assigned', 'closed');
4create type public.sender_role as enum ('user', 'agent', 'bot');
5create type public.agent_status as enum ('online', 'away', 'offline');
6
7create table public.conversations (
8  id uuid primary key default gen_random_uuid(),
9  user_id uuid references auth.users(id) on delete cascade not null,
10  agent_id uuid references auth.users(id) on delete set null,
11  status conversation_status not null default 'open',
12  channel text not null default 'in_app',
13  created_at timestamptz not null default now(),
14  updated_at timestamptz not null default now()
15);
16
17create table public.messages (
18  id uuid primary key default gen_random_uuid(),
19  conversation_id uuid references public.conversations(id) on delete cascade not null,
20  sender_id uuid references auth.users(id) on delete set null,
21  sender_role sender_role not null,
22  content text,
23  attachment_url text,
24  read_at timestamptz,
25  sequence_number bigint not null default nextval('public.message_seq'),
26  created_at timestamptz not null default now(),
27  constraint messages_content_or_attachment check (content is not null or attachment_url is not null)
28);
29
30create table public.agents (
31  id uuid primary key default gen_random_uuid(),
32  user_id uuid references auth.users(id) on delete cascade not null unique,
33  display_name text not null,
34  status agent_status not null default 'offline',
35  updated_at timestamptz not null default now()
36);
37
38alter table public.conversations enable row level security;
39alter table public.messages enable row level security;
40alter table public.agents enable row level security;
41
42create policy "Users see only their own conversations"
43  on public.conversations for select
44  using (auth.uid() = user_id);
45
46create policy "Users can create their own conversations"
47  on public.conversations for insert
48  with check (auth.uid() = user_id);
49
50create policy "Agents see all conversations"
51  on public.conversations for select
52  using (exists (select 1 from public.agents where user_id = auth.uid()));
53
54create policy "Agents can update conversation status and assignment"
55  on public.conversations for update
56  using (exists (select 1 from public.agents where user_id = auth.uid()));
57
58create policy "Users see messages in their own conversations"
59  on public.messages for select
60  using (
61    exists (
62      select 1 from public.conversations
63      where id = conversation_id and user_id = auth.uid()
64    )
65  );
66
67create policy "Agents see all messages"
68  on public.messages for select
69  using (exists (select 1 from public.agents where user_id = auth.uid()));
70
71create policy "Users can insert messages in their own conversations"
72  on public.messages for insert
73  with check (
74    auth.uid() = sender_id
75    and sender_role = 'user'
76    and exists (
77      select 1 from public.conversations
78      where id = conversation_id and user_id = auth.uid() and status != 'closed'
79    )
80  );
81
82create policy "Agents can insert messages"
83  on public.messages for insert
84  with check (
85    auth.uid() = sender_id
86    and sender_role = 'agent'
87    and exists (select 1 from public.agents where user_id = auth.uid())
88  );
89
90create policy "Users can update read_at on messages in their conversations"
91  on public.messages for update
92  using (
93    exists (
94      select 1 from public.conversations
95      where id = conversation_id and user_id = auth.uid()
96    )
97  );
98
99create policy "Anyone can read agent status"
100  on public.agents for select
101  using (true);
102
103create index messages_conversation_id_idx
104  on public.messages (conversation_id, created_at asc, sequence_number asc);
105
106create index conversations_user_id_idx
107  on public.conversations (user_id, created_at desc);
108
109create index conversations_status_idx
110  on public.conversations (status) where status != 'closed';