Generating embeddings

An embedding is a list of numbers that represents the meaning of a piece of text. Similar texts get similar numbers, which is what makes semantic search work. You get embeddings by sending text to an embedding model, usually through a provider's HTTPS API.

On SupaHost, the natural place to make that call is an Edge Function: it keeps the provider's API key on the server, it can write the result straight into your table with the service_role key, and it runs without per-invocation fees.

This page builds a function that fills in a recipes.embedding column, then wires it up so new and edited recipes are embedded automatically. It assumes the table from Vector columns with pgvector.

Choose a model

The model fixes the vector size, and the column must match it.

The examples use OpenAI's text-embedding-3-small, which returns 1536 numbers by default. Any provider with an HTTP embeddings endpoint works the same way; only the URL, headers and response shape change.

Pick one model per column and stick to it. Vectors from different models aren't comparable, so switching models means re-embedding every row.

Store the API key as a function secret

Dashboard → your project → Functions tab → Secrets card. Add OPENAI_API_KEY with your key. Secrets are stored encrypted, never shown again, and reach your functions within about a minute. Names starting with SUPABASE_ are reserved. See Environment variables and secrets.

Write the function

Create a function named embed-recipes (Functions tab → New function) with this index.ts. It finds up to 50 recipes with no embedding, embeds them in a single API call, and saves the results.

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

const SUPABASE_URL = Deno.env.get('SUPABASE_URL')!
const SERVICE_ROLE_KEY = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
const OPENAI_API_KEY = Deno.env.get('OPENAI_API_KEY')!

const supabase = createClient(SUPABASE_URL, SERVICE_ROLE_KEY, {
  auth: { persistSession: false },
})

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

Deno.serve(async (req) => {
  // Any valid project JWT passes "Require JWT", including the anon key,
  // so only accept calls made with the service_role key.
  if (req.headers.get('Authorization') !== `Bearer ${SERVICE_ROLE_KEY}`) {
    return new Response('Forbidden', { status: 403 })
  }

  const { data: rows, error } = await supabase
    .from('recipes')
    .select('id, title, body')
    .is('embedding', null)
    .limit(50)
  if (error) return new Response(error.message, { status: 500 })
  if (!rows.length) return Response.json({ embedded: 0 })

  // Keep inputs well under the model's token limit.
  const texts = rows.map((r) => `${r.title}\n\n${r.body}`.slice(0, 8000))
  const vectors = await embed(texts)

  for (let i = 0; i < rows.length; i++) {
    const { error: updateError } = await supabase
      .from('recipes')
      .update({ embedding: vectors[i] })
      .eq('id', rows[i].id)
    if (updateError) console.error(`recipe ${rows[i].id}: ${updateError.message}`)
  }

  console.log(`embedded ${rows.length} recipes`)
  return Response.json({ embedded: rows.length })
})

Click Deploy function. Leave Require JWT on.

The Authorization check matters. With Require JWT on, the router accepts any valid JWT for the project, and the anon key is one. Without the extra check, anyone holding your public key could make you pay for embedding calls.

Try it from the Test a function panel with POST "as service_role". The response should be {"embedded": N}, and the function's log line appears in the Logs card.

Embed new and edited rows automatically

Two small pieces keep embeddings current without touching your app code.

First, clear the embedding whenever the text changes, so a stale vector never lingers:

create or replace function public.recipes_reset_embedding()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  if new.title is distinct from old.title or new.body is distinct from old.body then
    new.embedding := null;
  end if;
  return new;
end;
$$;

create trigger recipes_reset_embedding
  before update on public.recipes
  for each row execute function public.recipes_reset_embedding();

Then call the function every minute with pg_cron and pg_net. Store the service_role key in Vault first (it's under Connect tab → App, behind Reveal):

select vault.create_secret('<service_role key>', 'service_role_key');

select cron.schedule(
  'embed-recipes',
  '* * * * *',
  $$
  select net.http_post(
    url := 'https://app-one.acme.supahost.space/functions/v1/embed-recipes',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || (
        select decrypted_secret from vault.decrypted_secrets where name = 'service_role_key'
      )
    ),
    body := '{}'::jsonb
  );
  $$
);

New recipes are inserted with a null embedding and picked up within a minute. Failed calls simply leave rows null, so the next run retries them. Don't run create extension pg_cron; it's already built in. You can watch runs in the Cron tab. See Storing secrets with Vault and Running functions on a schedule.

For higher volumes, put row ids on a queue and have the function read batches: see Queues with pgmq.

Backfill an existing table

The same function handles a backfill: each call embeds 50 rows. For a large table, either let the cron job work through it, or raise the batch size. Embedding APIs accept many inputs per request, and a function run can take up to 150 seconds to start responding. Keep each run well inside that limit and within 150 MB of memory; a few hundred short texts per call is comfortable. See Edge Function limits.

Check progress with:

select count(*) filter (where embedding is null) as remaining,
       count(*) as total
from public.recipes;

Embedding search queries

Search needs the query embedded with the same model. Do that in an Edge Function too, so the key stays on the server, then pass the vector to a match function. Semantic search shows the full round trip.

Related

• Example: calling an AI model
• Using the database from a function
• Edge Function returns 401
• Debugging database webhooks and pg_net