File size: 3,064 Bytes
69068b7 e655301 69068b7 e655301 69068b7 e655301 2883e00 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | -- Run this in Supabase SQL Editor
-- notebooks (existing)
create table if not exists notebooks (
id uuid primary key default gen_random_uuid(),
user_id text not null,
name varchar(255) not null default 'Untitled Notebook',
created_at timestamptz default now(),
updated_at timestamptz default now()
);
create index if not exists idx_notebooks_user_id on notebooks(user_id);
-- messages
create table if not exists messages (
id uuid primary key default gen_random_uuid(),
notebook_id uuid not null references notebooks(id) on delete cascade,
role text not null,
content text not null,
created_at timestamptz default now()
);
create index if not exists idx_messages_notebook_id on messages(notebook_id);
-- artifacts
create table if not exists artifacts (
id uuid primary key default gen_random_uuid(),
notebook_id uuid not null references notebooks(id) on delete cascade,
type text not null,
storage_path text not null,
created_at timestamptz default now()
);
create index if not exists idx_artifacts_notebook_id on artifacts(notebook_id);
-- pgvector extension for embeddings
create extension if not exists vector;
-- chunks with embeddings (for RAG) - 384 dims for MiniLM
create table if not exists chunks (
id uuid primary key default gen_random_uuid(),
notebook_id uuid not null references notebooks(id) on delete cascade,
source_id text,
content text not null,
embedding vector(384),
metadata jsonb,
created_at timestamptz default now()
);
create index if not exists idx_chunks_notebook_id on chunks(notebook_id);
-- Vector index for fast similarity search (run after chunks have data; ivfflat requires rows)
create index if not exists idx_chunks_embedding on chunks using ivfflat (embedding vector_cosine_ops) with (lists = 100);
-- RPC for RAG retrieval: top-k chunks by cosine similarity, filtered by notebook_id
create or replace function match_chunks(
query_embedding vector(384),
match_count int,
p_notebook_id uuid
)
returns table (id uuid, content text, metadata jsonb, similarity float)
language plpgsql as $$
begin
return query
select c.id, c.content, c.metadata,
1 - (c.embedding <=> query_embedding) as similarity
from chunks c
where c.notebook_id = p_notebook_id
and c.embedding is not null
order by c.embedding <=> query_embedding
limit match_count;
end;
$$;
-- sources table (ingestion pipeline)
create table if not exists sources (
id uuid primary key default gen_random_uuid(),
notebook_id uuid not null references notebooks(id) on delete cascade,
user_id text not null,
filename text not null,
file_type text not null,
status text not null default 'PENDING',
storage_path text,
extracted_text text,
metadata jsonb default '{}',
created_at timestamptz default now(),
updated_at timestamptz default now()
);
create index if not exists idx_sources_notebook_id on sources(notebook_id);
create index if not exists idx_sources_user_id on sources(user_id);
create index if not exists idx_sources_status on sources(status); |