diff options
| -rw-r--r-- | .gitignore | 4 | ||||
| -rw-r--r-- | supabase/migrations/001_initial_schema.sql | 218 |
2 files changed, 222 insertions, 0 deletions
@@ -4,3 +4,7 @@ dist/ .turbo/ *.log .DS_Store + +.env +.env.local +.env.*.local diff --git a/supabase/migrations/001_initial_schema.sql b/supabase/migrations/001_initial_schema.sql new file mode 100644 index 0000000..09b028e --- /dev/null +++ b/supabase/migrations/001_initial_schema.sql @@ -0,0 +1,218 @@ +create extension if not exists vector; + +create table public.users ( + id uuid primary key references auth.users(id) on delete cascade, + email text not null, + display_name text, + created_at timestamptz not null default now(), + updated_at timestamptz not null default now() +); + +create table public.projects ( + id uuid primary key default gen_random_uuid(), + user_id uuid not null references public.users(id) on delete cascade, + name text not null, + description text, + is_global boolean not null default false, + created_at timestamptz not null default now(), + updated_at timestamptz not null default now() +); + +create table public.folders ( + id uuid primary key default gen_random_uuid(), + project_id uuid not null references public.projects(id) on delete cascade, + name text not null, + description text, + created_at timestamptz not null default now(), + updated_at timestamptz not null default now() +); + +create table public.memories ( + id uuid primary key default gen_random_uuid(), + user_id uuid not null references public.users(id) on delete cascade, + project_id uuid not null references public.projects(id) on delete cascade, + folder_id uuid references public.folders(id) on delete set null, + content text not null, + tags jsonb not null default '[]'::jsonb, + metadata jsonb not null default '{}'::jsonb, + embedding vector(1536), + created_at timestamptz not null default now(), + updated_at timestamptz not null default now() +); + +create index memories_user_id_idx on public.memories(user_id); +create index memories_project_id_idx on public.memories(project_id); +create index memories_folder_id_idx on public.memories(folder_id); +create index memories_tags_idx on public.memories using gin(tags); +create index memories_embedding_idx on public.memories using ivfflat (embedding vector_cosine_ops) with (lists = 100); + +create index projects_user_id_idx on public.projects(user_id); +create index folders_project_id_idx on public.folders(project_id); + +create or replace function public.handle_updated_at() +returns trigger as $$ +begin + new.updated_at = now(); + + return new; +end; +$$ language plpgsql; + +create trigger users_updated_at + before update on public.users + for each row execute function public.handle_updated_at(); + +create trigger projects_updated_at + before update on public.projects + for each row execute function public.handle_updated_at(); + +create trigger folders_updated_at + before update on public.folders + for each row execute function public.handle_updated_at(); + +create trigger memories_updated_at + before update on public.memories + for each row execute function public.handle_updated_at(); + +alter table public.users enable row level security; +alter table public.projects enable row level security; +alter table public.folders enable row level security; +alter table public.memories enable row level security; + +create policy "Users can view own profile" + on public.users for select + using (auth.uid() = id); + +create policy "Users can update own profile" + on public.users for update + using (auth.uid() = id); + +create policy "Users can view own projects" + on public.projects for select + using (auth.uid() = user_id); + +create policy "Users can create own projects" + on public.projects for insert + with check (auth.uid() = user_id); + +create policy "Users can update own projects" + on public.projects for update + using (auth.uid() = user_id); + +create policy "Users can delete own projects" + on public.projects for delete + using (auth.uid() = user_id); + +create policy "Users can view folders in own projects" + on public.folders for select + using ( + exists ( + select 1 from public.projects + where projects.id = folders.project_id + and projects.user_id = auth.uid() + ) + ); + +create policy "Users can create folders in own projects" + on public.folders for insert + with check ( + exists ( + select 1 from public.projects + where projects.id = folders.project_id + and projects.user_id = auth.uid() + ) + ); + +create policy "Users can update folders in own projects" + on public.folders for update + using ( + exists ( + select 1 from public.projects + where projects.id = folders.project_id + and projects.user_id = auth.uid() + ) + ); + +create policy "Users can delete folders in own projects" + on public.folders for delete + using ( + exists ( + select 1 from public.projects + where projects.id = folders.project_id + and projects.user_id = auth.uid() + ) + ); + +create policy "Users can view own memories" + on public.memories for select + using (auth.uid() = user_id); + +create policy "Users can create own memories" + on public.memories for insert + with check (auth.uid() = user_id); + +create policy "Users can update own memories" + on public.memories for update + using (auth.uid() = user_id); + +create policy "Users can delete own memories" + on public.memories for delete + using (auth.uid() = user_id); + +create or replace function public.handle_new_user() +returns trigger as $$ +begin + insert into public.users (id, email) + values (new.id, new.email); + + return new; +end; +$$ language plpgsql security definer; + +create trigger on_auth_user_created + after insert on auth.users + for each row execute function public.handle_new_user(); + +create or replace function public.search_memories( + query_embedding vector(1536), + match_threshold float default 0.7, + match_count int default 10, + filter_project_id uuid default null, + filter_folder_id uuid default null +) +returns table ( + id uuid, + content text, + project_id uuid, + folder_id uuid, + tags jsonb, + metadata jsonb, + similarity float, + created_at timestamptz, + updated_at timestamptz +) +language plpgsql +security definer +as $$ +begin + return query + select + m.id, + m.content, + m.project_id, + m.folder_id, + m.tags, + m.metadata, + 1 - (m.embedding <=> query_embedding) as similarity, + m.created_at, + m.updated_at + from public.memories m + where m.user_id = auth.uid() + and m.embedding is not null + and 1 - (m.embedding <=> query_embedding) > match_threshold + and (filter_project_id is null or m.project_id = filter_project_id) + and (filter_folder_id is null or m.folder_id = filter_folder_id) + order by m.embedding <=> query_embedding + limit match_count; +end; +$$; |