chore(root): Add Supabase schema migration

1 files changed, 218 insertions, 0 deletions

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;
+$$;