The whole data model is one table

A RAG knowledge base sounds like it should be complicated. It isn't. Every piece of knowledge is just a chunk of text plus the vector that represents its meaning, so the entire model is one flat table.

Create the migration:

supabase migration new init_rag_schema

Open the generated file under supabase/migrations/ and paste:

-- pgvector: the extension that adds the `vector` type + similarity operators.
create extension if not exists vector;
 
create table public.documents (
  id         bigint generated always as identity primary key,
  source     text   not null,             -- filename, URL, or "pasted text"
  content    text   not null,             -- the chunk's text
  embedding  vector(384) not null,        -- gte-small embedding (384 dims — must
                                          -- match the model's output size)
  created_at timestamptz not null default now()
);

source is the label the answer cites back to you. content is the chunk itself. embedding is where the meaning lives.

Why 384 is not a magic number

The vector(384) column type is a hard contract with the model. We embed with Supabase's built-in gte-small model (Step 3), which outputs 384 dimensions — so the column is vector(384). Those two numbers must be identical: if the model returns a 384-dim vector and the column expects 768, the insert fails. gte-small's small vectors keep the hnsw index lean and are plenty for retrieval quality.

Change one, change both. If you ever switch embedding models (a different dimension), you change the column type here and re-embed everything you'd already stored — old vectors of the wrong size can't be compared against new ones.

-- Approximate-nearest-neighbour index for fast cosine search at scale.
create index documents_embedding_idx
  on public.documents using hnsw (embedding vector_cosine_ops);

hnsw (Hierarchical Navigable Small World) is an approximate-nearest-neighbour index — it finds almost the closest vectors, very fast, instead of scanning every row. vector_cosine_ops tells it to rank by cosine distance, which is what our query uses.

The retrieval function

This is the "retrieval tool" the whole read phase leans on. Given a question's embedding, it returns the closest chunks:

create or replace function public.match_documents(
  query_embedding vector(384),
  match_count int default 5
)
returns table (
  id         bigint,
  source     text,
  content    text,
  similarity float
)
language sql stable
as $$
  select
    d.id,
    d.source,
    d.content,
    1 - (d.embedding <=> query_embedding) as similarity   -- <=> is cosine distance
  from public.documents d
  order by d.embedding <=> query_embedding                 -- nearest first
  limit match_count;
$$;

The <=> operator is cosine distance — 0 means "pointing the same direction" (identical meaning), larger means further apart. We order by it ascending so the nearest chunks come first, and return 1 - distance as a friendly similarity score where 1.0 is a perfect match. match_count defaults to 5, so a question comes back with its five best chunks. Cosine is magnitude-invariant, so there's no need to normalize the vectors first.

The fence: RLS on, no policies

alter table public.documents enable row level security;
-- ...and no `create policy` statements. On purpose.

This looks like an oversight. It's the security model of the whole build:

  • RLS enabled + zero policies = deny all for the anon and authenticated roles. Your project's anon key is public — it ships in the browser — so anyone could point supabase-js at your project and hit the REST API directly. With RLS on and no policies, every such attempt reads and writes nothing.
  • The service-role key bypasses RLS entirely. That's the key our Edge Functions hold (Step 3), and it lives only in the function's secrets.

Put together: the browser calls the functions, the functions hold the only key that opens the table. Nobody reaches documents by going around the functions. If you skipped enable row level security, the public anon key could read every chunk you stored. If you used the anon key inside the function instead of the service role, every query would silently come back empty.

Apply it

Push the migration to your cloud project (Step 6 covers supabase link if you haven't linked yet):

supabase db push

Confirm the extension, table, and RLS all landed:

-- In the Supabase SQL editor:
select relname, relrowsecurity
from pg_class
where relname = 'documents';
-- relrowsecurity should be `true`

If relrowsecurity is true, the fence is up. Step 3 builds the write phase — the code that fills this table.