path: root/supabase/migrations/20260204000001_initial_schema.sql

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