Task: Implement Per-User Thread Isolation (Supabase + Tambo) #11
Description
Task: Implement Per-User Thread Isolation (Supabase + Tambo)
Objective
Implement strict per-user visibility for chat threads and messages so that each authenticated user can only access their own thread history. This includes database design, Row Level Security (RLS), backend APIs, and Tambo integration.
This task enforces multi-tenant isolation and eliminates cross-user data leakage.
Scope of Work
- Supabase schema updates
- Row Level Security (RLS) policies
- Backend thread/message creation & retrieval
- Tambo thread metadata alignment
- Legacy data cleanup
- Validation & testing
Assumptions
- Supabase Auth is enabled
auth.uid()is the source of truth foruser_id- Threads and messages are stored in Supabase (Postgres)
- Tambo is used for AI thread/message generation
Database Schema
1. Threads Table
create table if not exists threads (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references auth.users(id) on delete cascade,
created_at timestamptz default now()
);2. Messages Table
create table if not exists messages (
id uuid primary key default gen_random_uuid(),
thread_id uuid not null references threads(id) on delete cascade,
role text not null,
content jsonb not null,
created_at timestamptz default now()
);Row Level Security (CRITICAL)
Enable RLS
alter table threads enable row level security;
alter table messages enable row level security;Threads Policies
-- Read own threads
create policy "Users can read their own threads"
on threads for select
using (user_id = auth.uid());
-- Create thread for self
create policy "Users can create their own threads"
on threads for insert
with check (user_id = auth.uid());Messages Policies
-- Read messages only from owned threads
create policy "Users can read messages from their threads"
on messages for select
using (
exists (
select 1 from threads
where threads.id = messages.thread_id
and threads.user_id = auth.uid()
)
);
-- Insert messages only into owned threads
create policy "Users can insert messages into their threads"
on messages for insert
with check (
exists (
select 1 from threads
where threads.id = messages.thread_id
and threads.user_id = auth.uid()
)
);Backend Responsibilities
Thread Creation
- Fetch authenticated user ID
- Create thread with
user_id
const { data: thread } = await supabase
.from('threads')
.insert({ user_id: user.id })
.select()
.single();Message Creation
- Accept
thread_id - Supabase RLS must enforce ownership
await supabase.from('messages').insert({
thread_id,
role,
content,
});Fetch Threads
await supabase
.from('threads')
.select('*')
.order('created_at', { ascending: false });
⚠️ No manualwhere user_id = ...required when RLS is correct
Tambo Integration
Thread Initialization
When creating a Tambo thread:
tambo.createThread({
threadId,
metadata: {
user_id: user.id,
},
});Message Retrieval
Ensure backend fetches messages only through Supabase, not directly from Tambo without validation.
Tambo should be treated as stateless compute, Supabase as source of truth.
Legacy Data Cleanup (One-Time)
-- Remove threads without ownership
delete from threads where user_id is null;
-- Remove orphaned messages
delete from messages
where thread_id not in (select id from threads);Validation Checklist
- User A cannot see User B threads
- Direct thread_id access fails for unauthorized user
- RLS blocks cross-user inserts
- New threads always have
user_id - Tambo metadata includes
user_id - Old chats are removed or migrated
Non-Goals
- Shared threads
- Admin impersonation
- Cross-user collaboration
Success Criteria
- Each user sees only their own chat history
- Thread access is impossible without ownership
- No frontend-only filtering
- Security holds under direct API calls
Notes
This design mirrors production-grade SaaS isolation (Slack / Notion model). Any shortcut here is a future security incident.