Run this SQL in your Supabase SQL Editor to create all tables and enable auth.
Setup steps
Create a Supabase project at supabase.com (or use existing)
Go to SQL Editor → paste & run the SQL below
Go to Authentication → Providers → Email → enable, and DISABLE "Enable email signups" (admin invites only)
Go to Authentication → Users → click "Invite user" for each employee
Each employee gets an email link to set their password and log in
SQL Schema (run once)
-- TeaChat v7 schema (full + auth-ready)
create table if not exists boards (
id text primary key,
name text not null,
background text default 'blue',
columns jsonb default '[{"id":"todo","name":"To do"},{"id":"doing","name":"In progress"},{"id":"review","name":"Review"},{"id":"done","name":"Done"}]'::jsonb,
labels jsonb default '[{"id":"l_green","color":"#1f845a","name":""},{"id":"l_yellow","color":"#e2b203","name":""},{"id":"l_orange","color":"#e56910","name":""},{"id":"l_red","color":"#c9372c","name":""},{"id":"l_purple","color":"#6e5dc6","name":""},{"id":"l_blue","color":"#0c66e4","name":""}]'::jsonb,
members jsonb default '[]'::jsonb,
field_defs jsonb default '[]'::jsonb,
sort_position integer,
created_at timestamptz default now()
);
-- v11 migration for existing databases:
-- alter table boards add column if not exists sort_position integer;
create table if not exists channels (id text primary key, name text not null, created_at timestamptz default now());
create table if not exists cards (
id text primary key,
board_id text references boards(id) on delete cascade,
title text not null, description text default '', tag text default 'eng',
status text default 'todo', position double precision default 0,
due_date date, due_complete boolean default false,
assignees jsonb default '[]'::jsonb, attachments jsonb default '[]'::jsonb,
labels jsonb default '[]'::jsonb, checklists jsonb default '[]'::jsonb,
watchers jsonb default '[]'::jsonb, custom_fields jsonb default '{}'::jsonb,
cover text default '', archived boolean default false,
time_seconds integer default 0,
author text, author_id text, created_at timestamptz default now()
);
create table if not exists messages (
id text primary key,
channel_id text references channels(id) on delete cascade,
author text, author_id text, text text not null,
mentions jsonb default '[]'::jsonb, attachments jsonb default '[]'::jsonb,
thread_count integer default 0, read_by jsonb default '[]'::jsonb, saved_by jsonb default '[]'::jsonb,
created_at timestamptz default now()
);
create table if not exists comments (
id text primary key, card_id text references cards(id) on delete cascade,
author text, author_id text, text text not null, created_at timestamptz default now()
);
create table if not exists activity (
id text primary key, card_id text, board_id text,
actor text, actor_id text, action text not null, created_at timestamptz default now()
);
create table if not exists members (
id text primary key, name text not null, email text,
color text, role text default 'member',
status_emoji text default '', status_text text default '', dnd boolean default false,
last_seen timestamptz default now()
);
create table if not exists notifications (
id text primary key, user_id text not null, text text not null,
link_type text, link_id text, read boolean default false, created_at timestamptz default now()
);
create table if not exists dms (
id text primary key, participants jsonb not null,
last_message_at timestamptz default now(), created_at timestamptz default now()
);
create table if not exists dm_messages (
id text primary key, dm_id text references dms(id) on delete cascade,
author text, author_id text, text text not null,
attachments jsonb default '[]'::jsonb, read_by jsonb default '[]'::jsonb,
created_at timestamptz default now()
);
create table if not exists threads (
id text primary key, parent_id text not null, parent_type text not null, channel_id text,
author text, author_id text, text text not null,
mentions jsonb default '[]'::jsonb, attachments jsonb default '[]'::jsonb,
created_at timestamptz default now()
);
create table if not exists polls (
id text primary key, channel_id text, message_id text,
question text not null, options jsonb not null, votes jsonb default '{}'::jsonb,
multi boolean default false, closed boolean default false,
author text, author_id text, created_at timestamptz default now()
);
create table if not exists pins (
id text primary key, user_id text not null, item_type text not null, item_id text not null,
created_at timestamptz default now()
);
create table if not exists time_logs (
id text primary key, card_id text references cards(id) on delete cascade,
user_id text not null, user_name text,
start_at timestamptz not null, end_at timestamptz, seconds integer default 0,
note text default '', created_at timestamptz default now()
);
create table if not exists templates (
id text primary key, kind text not null, name text not null,
data jsonb not null, author_id text, created_at timestamptz default now()
);
-- ENABLE ROW LEVEL SECURITY
alter table boards enable row level security;
alter table channels enable row level security;
alter table cards enable row level security;
alter table messages enable row level security;
alter table comments enable row level security;
alter table activity enable row level security;
alter table members enable row level security;
alter table notifications enable row level security;
alter table dms enable row level security;
alter table dm_messages enable row level security;
alter table threads enable row level security;
alter table polls enable row level security;
alter table pins enable row level security;
alter table time_logs enable row level security;
alter table templates enable row level security;
-- AUTH-ONLY POLICIES (only authenticated users can read/write)
create policy "auth_all" on boards for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_all" on channels for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_all" on cards for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_all" on messages for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_all" on comments for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_all" on activity for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_all" on members for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_insert_notif" on notifications for insert to authenticated with check (true);
create policy "auth_select_own_notif" on notifications for select to authenticated using (auth.uid()::text = user_id);
create policy "auth_update_own_notif" on notifications for update to authenticated using (auth.uid()::text = user_id) with check (auth.uid()::text = user_id);
create policy "auth_delete_own_notif" on notifications for delete to authenticated using (auth.uid()::text = user_id);
-- DMs and DM messages: participant-only (privacy fix, Bug Fix 4)
-- participants is jsonb; the `?` operator checks if the string exists in the jsonb array.
create policy "dms_participant_select" on dms for select to authenticated using (participants ? (auth.uid())::text);
create policy "dms_participant_insert" on dms for insert to authenticated with check (participants ? (auth.uid())::text);
create policy "dms_participant_update" on dms for update to authenticated using (participants ? (auth.uid())::text) with check (participants ? (auth.uid())::text);
create policy "dms_participant_delete" on dms for delete to authenticated using (participants ? (auth.uid())::text);
create policy "dm_messages_participant_select" on dm_messages for select to authenticated using (exists (select 1 from dms d where d.id = dm_messages.dm_id and d.participants ? (auth.uid())::text));
create policy "dm_messages_author_insert" on dm_messages for insert to authenticated with check ((auth.uid())::text = author_id and exists (select 1 from dms d where d.id = dm_id and d.participants ? (auth.uid())::text));
create policy "dm_messages_participant_update" on dm_messages for update to authenticated using (exists (select 1 from dms d where d.id = dm_messages.dm_id and d.participants ? (auth.uid())::text)) with check (exists (select 1 from dms d where d.id = dm_messages.dm_id and d.participants ? (auth.uid())::text));
create policy "dm_messages_author_delete" on dm_messages for delete to authenticated using ((auth.uid())::text = author_id);
create policy "auth_all" on threads for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_all" on polls for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_own_pins" on pins for all using (auth.uid()::text = user_id) with check (auth.uid()::text = user_id);
create policy "auth_all" on time_logs for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "auth_all" on templates for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
-- Realtime
alter publication supabase_realtime add table boards;
alter publication supabase_realtime add table channels;
alter publication supabase_realtime add table cards;
alter publication supabase_realtime add table messages;
alter publication supabase_realtime add table comments;
alter publication supabase_realtime add table activity;
alter publication supabase_realtime add table members;
alter publication supabase_realtime add table notifications;
alter publication supabase_realtime add table dms;
alter publication supabase_realtime add table dm_messages;
alter publication supabase_realtime add table threads;
alter publication supabase_realtime add table polls;
alter publication supabase_realtime add table pins;
alter publication supabase_realtime add table time_logs;
alter publication supabase_realtime add table templates;
-- Storage bucket
insert into storage.buckets (id, name, public) values ('attachments', 'attachments', true) on conflict (id) do nothing;
create policy "auth_storage_read" on storage.objects for select using (bucket_id = 'attachments' and auth.role() = 'authenticated');
create policy "auth_storage_write" on storage.objects for insert with check (bucket_id = 'attachments' and auth.role() = 'authenticated');
create policy "auth_storage_update" on storage.objects for update using (bucket_id = 'attachments' and auth.role() = 'authenticated');
create policy "auth_storage_delete" on storage.objects for delete using (bucket_id = 'attachments' and auth.role() = 'authenticated');
-- IF YOU ALREADY HAVE OLD POLICIES, DROP THEM FIRST:
-- drop policy if exists "open" on boards;
-- drop policy if exists "open" on channels;
-- (etc. for each table)