Semantic search

Keyword search finds rows that contain the words someone typed. Semantic search finds rows that mean something similar, so "cosy winter soup" can match a recipe titled "Roasted squash and ginger broth" that shares none of those words.

The round trip has three steps:

1. Embed the search text with the same model you used for your rows.
2. Ask Postgres for the rows whose embeddings are closest to that vector.
3. Return them, filtered by Row Level Security, to the user.

This page builds that for the recipes table from Vector columns with pgvector, with embeddings filled in as shown in Generating embeddings.

The match function

Put the similarity query in a database function. The Data API can't sort by a vector operator itself, but it can call a function with rpc().

create or replace function public.match_recipes(
  query_embedding extensions.vector(1536),
  match_count int default 10,
  max_distance float8 default 0.7
)
returns table (id bigint, title text, similarity float8)
language sql
stable
security invoker
set search_path = ''
as $$
  select
    r.id,
    r.title,
    1 - (r.embedding operator(extensions.<=>) query_embedding) as similarity
  from public.recipes r
  where r.embedding is not null
    and r.embedding operator(extensions.<=>) query_embedding < max_distance
  order by r.embedding operator(extensions.<=>) query_embedding
  limit least(match_count, 50);
$$;

A few choices here are deliberate:

• security invoker: the function runs as the caller, so the RLS policy on recipes still applies. A user only finds their own recipes and public ones.
• Cosine distance (<=>): must match the operator class of your vector index, or the index won't be used. See Vector indexes.
• order by ... limit: this is the shape a vector index can speed up. The max_distance filter then drops weak matches.
• A cap on match_count: stops a client from asking for thousands of rows.

Good distance cut-offs depend on the model and your content. Run a few real queries, look at the similarity values of good and bad matches, and set the threshold between them.

Embed the query in an Edge Function

The search text has to be embedded with your provider's API key, so do it on the server. This function, search-recipes, embeds the query and calls the match function as the signed-in user by forwarding their Authorization header, so RLS applies.

import { createClient } from 'npm:@supabase/supabase-js@2'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Access-Control-Allow-Methods': 'POST, OPTIONS',
}

async function embed(text: string): Promise<number[]> {
  const res = await fetch('https://api.openai.com/v1/embeddings', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Authorization: `Bearer ${Deno.env.get('OPENAI_API_KEY')}`,
    },
    body: JSON.stringify({ model: 'text-embedding-3-small', input: text }),
  })
  if (!res.ok) throw new Error(`Embeddings API ${res.status}`)
  const json = await res.json()
  return json.data[0].embedding
}

Deno.serve(async (req) => {
  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })

  const { query } = await req.json()
  if (typeof query !== 'string' || !query.trim() || query.length > 500) {
    return Response.json({ error: 'query must be 1-500 characters' }, { status: 400, headers: corsHeaders })
  }

  // Act as the caller so Row Level Security applies.
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_ANON_KEY')!,
    { global: { headers: { Authorization: req.headers.get('Authorization')! } } }
  )

  const queryEmbedding = await embed(query.trim())

  const { data, error } = await supabase.rpc('match_recipes', {
    query_embedding: queryEmbedding,
    match_count: 10,
  })
  if (error) return Response.json({ error: error.message }, { status: 500, headers: corsHeaders })

  return Response.json({ results: data }, { headers: corsHeaders })
})

Deploy it from Dashboard → your project → Functions tab with Require JWT on, and add OPENAI_API_KEY under Secrets if you haven't already. In production, set Access-Control-Allow-Origin to your site's origin rather than *; see CORS for browser calls.

Call it from your app

functions.invoke sends the user's session token automatically:

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  'https://app-one.acme.supahost.space',
  import.meta.env.VITE_SUPABASE_ANON_KEY
)

const { data, error } = await supabase.functions.invoke('search-recipes', {
  body: { query: 'cosy winter soup' },
})

// data.results: [{ id: 31, title: 'Roasted squash and ginger broth', similarity: 0.62 }, ...]

If the user isn't signed in, the request runs as anon, and with the policy above they see no recipes. Add an anon policy for public recipes if logged-out visitors should be able to search.

Add filters

Most real searches combine meaning with ordinary conditions: a category, a date range, a tenant. Add them as function arguments. This version assumes recipes also has a cuisine text column:

create or replace function public.match_recipes_in_cuisine(
  query_embedding extensions.vector(1536),
  cuisine_filter text,
  match_count int default 10
)
returns table (id bigint, title text, similarity float8)
language sql
stable
security invoker
set search_path = ''
as $$
  select r.id, r.title, 1 - (r.embedding operator(extensions.<=>) query_embedding)
  from public.recipes r
  where r.embedding is not null
    and r.cuisine = cuisine_filter
  order by r.embedding operator(extensions.<=>) query_embedding
  limit least(match_count, 50);
$$;

With an approximate index, a selective filter can return fewer rows than the limit, because the index hands back the nearest candidates before the filter runs. Vector indexes covers iterative scans and other fixes.

Keep it fast

• Add an HNSW index once the table has more than a few thousand rows. Without one, each search compares the query with every row. See Faster vector search with an HNSW index.
• Keep limit small. Ten to fifty results is typical.
• Don't return the embedding column. It's large and the client doesn't need it.

The Data API runs at most five queries at once per project, so a slow search holds up other requests. See Connections and timeouts.

When semantic search isn't enough

Embeddings are weak at exact terms: product codes, names, rare words. If users search for those, combine vector results with keyword results: see Hybrid search.

Related

• Calling functions from your app
• Authentication and JWT verification
• Retrieval-augmented generation (RAG)