Build a RAG Pipeline with Supabase

Build an automated RAG backend with pgvector, triggers, and semantic search.

Introduction

30 Second Summary

When you ask an AI chatbot a question about a website, it needs to find the most relevant content instantly. The secret is a retrieval system that understands meaning, not just keywords.

In this project, you will build a RAG (Retrieval-Augmented Generation) backend that automatically converts website content into searchable vector embeddings. You will also create a semantic search function that any AI chatbot or search bar can use to find the most relevant answers.

What You'll Build

A complete backend pipeline that ingests multi-type website content into Supabase, automatically generates vector embeddings via Edge Functions, and exposes a semantic search function for AI-powered retrieval.

By the end of this project, you'll have:

A site_documents table with pgvector storing chunked website content (events, projects, positions, FAQs, blogs, tutorials) as vector embeddings with rich metadata.
A deployed Edge Function that automatically re-embeds content whenever it is inserted or updated via database triggers and pgmq queues.
A semantic search RPC function (match_documents) that retrieves the most relevant chunks for any natural language query with source attribution.
Secret Mission: Add hybrid search combining semantic similarity with full-text keyword search using Reciprocal Rank Fusion.

Are there any prerequisites?

This project assumes basic SQL knowledge, basic JavaScript/TypeScript familiarity, and comfort with the command line. You do not need prior experience with vector databases or AI concepts.

Set Up Your Environment

Before you can build a RAG pipeline, you need the right tools in place. Your pipeline will live in a Supabase project and use Node.js scripts to ingest content.

In this step, you will install the tools, create your cloud project, and wire everything together so that later steps can focus on building the actual pipeline.

In this step, get ready to:

Install Node.js and the Supabase CLI.
Create a free Supabase project and get your API credentials.
Initialize a local project folder and link it to Supabase.

Install Node.js and the Supabase CLI

Node.js runs the ingestion and search scripts you will write later. The Supabase CLI lets you manage your cloud database from the terminal.

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type Terminal and press Enter to open it.
Check whether Node.js is installed by running this command:

node --version

✔️ I see v18 or higher

Your Node.js version is ready to go.

ⓧ I see a version lower than 18

You need to upgrade to Node.js 18 or higher.

Visit the Node.js downloads page and download the LTS installer for your operating system.
Run the installer and follow the prompts.
Close and reopen your terminal, then run node --version again to confirm.

ⓧ Command not found

Node.js is not installed yet.

Visit the Node.js downloads page and download the LTS installer for your operating system.
Run the installer and follow the prompts.
Close and reopen your terminal, then run node --version again to confirm you see a version number starting with v18 or higher.

Next, install the Supabase CLI. The recommended approach for global usage is Homebrew (macOS/Linux) or Scoop (Windows).

macOS

Install the Supabase CLI using Homebrew by running:

brew install supabase/tap/supabase

Windows

Install the Supabase CLI using Scoop by running:

scoop bucket add supabase https://github.com/supabase/scoop-bucket.git
scoop install supabase

Verify the Supabase CLI installation by running:

supabase --version

You should see a version number printed in the terminal.

Not seeing a version number?

If you get a "command not found" error, close your terminal and reopen it. This refreshes your shell so it picks up newly installed commands.

On macOS, if you do not have Homebrew installed, visit brew.sh to install it first.

Still stuck? Help me install the Supabase CLI on my machine.

Create a Supabase project and get your API keys

Your RAG pipeline needs a cloud database to store content and embeddings. Supabase provides a free-tier PostgreSQL database with vector search built in.

Open supabase.com in your browser.
Sign up for a free account (or sign in if you already have one).
Click New Project.
Choose an organization (or create one if prompted).
Enter a project name (for example, rag-pipeline).
Set a strong database password and save it somewhere safe.
Select a region closest to you.
Click Create new project.
Wait for the project to finish provisioning (about 1-2 minutes).

Now collect the two values you need from the project settings.

In the left sidebar of your Supabase Dashboard, click the gear icon to open Project Settings.
Click API in the settings menu.
Copy the value under Project URL and save it. This is your SUPABASE_URL.
Scroll down to Project API keys.
Copy the service_role key (not the anon key) and save it. This is your SUPABASE_SERVICE_ROLE_KEY.

Why the service_role key?

The service_role key bypasses Row Level Security, giving your ingestion scripts full access to insert and update content. The anon key is meant for frontend apps where you want security policies enforced.

Keep this key secret. Never commit it to version control or share it publicly.

Finally, get your OpenAI API key for generating vector embeddings.

Open platform.openai.com in your browser.
Sign in or create an account.
Navigate to API keys in the left sidebar.
Click Create new secret key.
Copy the key immediately (it will not be shown again) and save it. This is your OPENAI_API_KEY.

What is this key for?

The OpenAI API key lets your Edge Function call the text-embedding-3-small model to convert text into vector embeddings. Embedding the sample data in this project costs less than one cent.

Initialize your project folder and link to Supabase

Now you will create a local folder for your project files and connect it to the Supabase cloud project you just created.

Move to your Desktop by running this command:

cd ~/Desktop

Create your project folder and move into it by running these commands:

mkdir supabase-rag-pipeline
cd supabase-rag-pipeline

Initialize a Supabase project inside this folder by running:

supabase init

You should see a new supabase directory created inside your folder.

What does supabase init do?

This command creates a supabase/ directory containing a config.toml file. The CLI uses this directory to track migrations, Edge Functions, and project configuration.

supabase login

This opens your browser to authenticate. Approve the login request when prompted.

Link your local folder to your cloud Supabase project by running:

supabase link --project-ref [[PROJECT_REF="your Supabase project reference ID"]]

Where do I find my project reference?

Your project reference is the string in your Supabase Dashboard URL after /project/. For example, if your dashboard URL is https://supabase.com/dashboard/project/abcdefghijk, then your project reference is abcdefghijk.

When prompted for the database password, enter the password you set when creating the project.

Create a .env file to store your credentials by running:

touch .env

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type Visual Studio Code and press Enter to open it.
In VS Code, click File in the top menu bar.
Click Open Folder.
Navigate to your Desktop and select the supabase-rag-pipeline folder, then click Open.
In the Explorer sidebar, click .env to open it.
Paste the following content into .env, replacing each placeholder with your actual values:

SUPABASE_URL=your-supabase-project-url-here
SUPABASE_SERVICE_ROLE_KEY=your-supabase-service-role-key-here
OPENAI_API_KEY=your-openai-api-key-here

What does this file do?

The .env file stores environment variables that your scripts read at runtime. This keeps secrets out of your code files so you never accidentally commit them to version control.

Save .env by pressing Cmd+S (macOS) or Ctrl+S (Windows).
Confirm your folder structure is correct by running this in the terminal:

ls

You should see .env and a supabase directory listed in the output.

Don't see the supabase folder?

Make sure you ran supabase init from inside the supabase-rag-pipeline folder, not from your Desktop. Run pwd to check your current directory.

If you are in the wrong folder, run cd ~/Desktop/supabase-rag-pipeline and try supabase init again.

Still stuck? Help me set up my Supabase project folder.

✔️ Awesome, I've got everything!

Great. Double check you have saved your .env file with all three values filled in.

ⓧ I'd like to double check the full code

Here is what your .env file should look like (with your real values in place of the placeholders):

SUPABASE_URL=your-supabase-project-url-here
SUPABASE_SERVICE_ROLE_KEY=your-supabase-service-role-key-here
OPENAI_API_KEY=your-openai-api-key-here

Your environment is ready. Next up, you will design the database schema that stores your website content and vector embeddings.

Design the Database Schema and Enable Extensions

Your environment is linked to Supabase and ready to go. Now you need a database that can store website content as vector embeddings and search across them by meaning.

In this step, you will enable the Postgres extensions that give your database vector search and async processing superpowers. You will also create the table that holds all your website content and build the utility functions that power the automatic embedding pipeline.

In this step, get ready to:

Enable Postgres extensions for vector search and async processing.
Create the documents table with vector embedding support.
Build utility functions for the automated embedding pipeline.

Enable the required Postgres extensions

Your Supabase database is standard Postgres out of the box. To add vector search, message queuing, scheduled jobs, and HTTP calls, you need to enable a handful of extensions first.

In your Supabase Dashboard, click SQL Editor in the left sidebar.
Click New Query to open a blank editor pane.
Paste the following SQL and click Run (or press Cmd+Enter on macOS, Ctrl+Enter on Windows):

-- Enable extensions for vector search and async processing
create extension if not exists vector with schema extensions;
create extension if not exists pgmq;
create extension if not exists pg_net with schema extensions;
create extension if not exists pg_cron;
create extension if not exists hstore with schema extensions;

What do these extensions do?

vector (pgvector) adds vector column types and similarity search operators for embeddings.
pgmq provides a lightweight message queue inside Postgres for batching embedding jobs.
pg_net lets Postgres make HTTP requests directly, so it can call Edge Functions.
pg_cron schedules recurring tasks (like processing the embedding queue every 10 seconds).
hstore provides key-value operators used to dynamically clear columns on update.

Confirm the query ran without errors in the results panel below the editor.

Seeing a permission error?

Make sure you are running the query against your cloud project (not a local database). Extensions like pgmq and pg_cron are pre-installed on Supabase-hosted projects but need to be enabled with create extension.

help me troubleshoot a permission error when enabling extensions in the Supabase SQL Editor

Create the site_documents table

All your website content will live in a single table with a discriminator column for content type. This keeps the embedding pipeline simple because one trigger and one function handle every type of content.

In the same SQL Editor pane, clear the previous query, paste the following SQL, and click Run:

-- Create the site_documents table
create table site_documents (
  id integer primary key generated always as identity,
  content_type text not null check (content_type in ('event', 'project', 'position', 'faq', 'blog', 'tutorial')),
  title text not null,
  content text not null,
  page_source text not null,
  section_title text,
  last_updated timestamp with time zone default now(),
  embedding extensions.halfvec(1536),
  created_at timestamp with time zone default now()
);

-- HNSW index for vector similarity search
create index on site_documents using hnsw (embedding halfvec_cosine_ops);

What does this schema do?

content_type uses a CHECK constraint to restrict values to six known types: event, project, position, faq, blog, and tutorial.
page_source and section_title store where the content came from, so search results can link back to the original page.
embedding extensions.halfvec(1536) stores the vector embedding in half precision (16-bit floats), saving storage while retaining search quality.
The HNSW index with halfvec_cosine_ops makes cosine similarity searches fast, even with thousands of documents.

Why halfvec instead of vector?

The halfvec type stores each float in 16 bits instead of 32. For 1536-dimension embeddings from OpenAI's text-embedding-3-small model, this cuts storage in half with negligible impact on search accuracy. HNSW indexes support up to 4000 dimensions for halfvec, so 1536 is well within limits.

Confirm the query ran without errors.
Click Table Editor in the left sidebar. You should see site_documents listed in the public schema.

Table not showing up?

Make sure you ran the extensions SQL from the previous substep first. The embedding column depends on the vector extension being enabled. If you skipped it, you will see an error about the halfvec type not existing.

help me fix my site_documents table creation in the Supabase SQL Editor

Create the utility schema and functions

The automatic embedding pipeline relies on four utility functions that handle everything from reading secrets to queuing jobs. These functions are generic and reusable, so you put them in a separate util schema to keep things organized.

Return to the SQL Editor and open a new query.
Create the utility schema and the project_url() function by pasting the following SQL and clicking Run:

-- Create utility schema
create schema if not exists util;

-- Utility: get project URL from Vault
create or replace function util.project_url()
returns text
language plpgsql
security definer
as $$
declare
  secret_value text;
begin
  select decrypted_secret into secret_value from vault.decrypted_secrets where name = 'project_url';
  return secret_value;
end;
$$;

What does util.project_url() do?

This function reads your Supabase project URL from Vault (Supabase's built-in secrets manager). Other functions call it to construct Edge Function URLs without hardcoding your project address in SQL.

Confirm the query ran without errors.
Now create the invoke_edge_function utility by pasting the following SQL and clicking Run:

-- Utility: invoke Edge Function
create or replace function util.invoke_edge_function(
  name text,
  body jsonb,
  timeout_milliseconds int = 5 * 60 * 1000
)
returns void
language plpgsql
as $$
declare
  headers_raw text;
  auth_header text;
begin
  headers_raw := current_setting('request.headers', true);
  auth_header := case
    when headers_raw is not null then
      (headers_raw::json->>'authorization')
    else
      null
  end;

  perform net.http_post(
    url => util.project_url() || '/functions/v1/' || name,
    headers => jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', auth_header
    ),
    body => body,
    timeout_milliseconds => timeout_milliseconds
  );
end;
$$;

What does invoke_edge_function do?

This function uses pg_net to make an HTTP POST request to any of your Edge Functions. It constructs the URL from your project URL, passes the authorization header through, and sends whatever JSON body you give it. The embedding processor will call this to trigger the embed function later.

Confirm the query ran without errors.
Finally, create the two trigger utility functions by pasting the following SQL and clicking Run:

-- Utility: clear column on update
create or replace function util.clear_column()
returns trigger
language plpgsql as $$
declare
    clear_column text := TG_ARGV[0];
begin
    NEW := NEW #= hstore(clear_column, NULL);
    return NEW;
end;
$$;

-- Utility: queue embedding jobs
create or replace function util.queue_embeddings()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  content_function text = TG_ARGV[0];
  embedding_column text = TG_ARGV[1];
begin
  perform pgmq.send(
    queue_name => 'embedding_jobs',
    msg => jsonb_build_object(
      'id', NEW.id,
      'schema', TG_TABLE_SCHEMA,
      'table', TG_TABLE_NAME,
      'contentFunction', content_function,
      'embeddingColumn', embedding_column
    )
  );
  return NEW;
end;
$$;

What do these trigger functions do?

util.clear_column() runs before an update and sets a specified column to NULL. You will attach it to the embedding column so that any time content changes, the old embedding is cleared and marked for re-generation.

util.queue_embeddings() runs after an insert or update and sends a job to the embedding_jobs queue via pgmq. The job message includes the row ID, table name, and which function should generate the embedding text. A scheduled worker picks these up in batches.

Confirm the query ran without errors.

Seeing an error about hstore or pgmq?

These functions depend on the extensions you enabled in the first substep. If you see an error mentioning hstore or pgmq not existing, go back and re-run the extensions SQL. Make sure all five create extension statements completed successfully.

help me troubleshoot missing extension errors in my Supabase utility functions

Now store your project URL in Vault so that util.project_url() can retrieve it at runtime.

In a new SQL Editor query, run the following (replacing the URL with your own Supabase project URL from Step 1):

select vault.create_secret('[[SUPABASE_URL="your Supabase project URL (e.g. https://abcdefg.supabase.co)"]]', 'project_url');

Where do I find my project URL?

Your project URL is the SUPABASE_URL value you saved in your .env file during Step 1. It looks like https://abcdefg.supabase.co.

Confirm the query returns a result row (the secret ID). This means your project URL is safely stored in Vault.

The final step is to save all this SQL locally as a migration file for version control.

Switch back to VS Code.
Create a new file at supabase/migrations/001_setup.sql inside your project folder.
Paste the complete SQL below into that file and save it:

-- Step 2: Enable extensions
create extension if not exists vector with schema extensions;
create extension if not exists pgmq;
create extension if not exists pg_net with schema extensions;
create extension if not exists pg_cron;
create extension if not exists hstore with schema extensions;

-- Create utility schema
create schema if not exists util;

-- Utility: get project URL from Vault
create or replace function util.project_url()
returns text
language plpgsql
security definer
as $$
declare
  secret_value text;
begin
  select decrypted_secret into secret_value from vault.decrypted_secrets where name = 'project_url';
  return secret_value;
end;
$$;

-- Utility: invoke Edge Function
create or replace function util.invoke_edge_function(
  name text,
  body jsonb,
  timeout_milliseconds int = 5 * 60 * 1000
)
returns void
language plpgsql
as $$
declare
  headers_raw text;
  auth_header text;
begin
  headers_raw := current_setting('request.headers', true);
  auth_header := case
    when headers_raw is not null then
      (headers_raw::json->>'authorization')
    else
      null
  end;

  perform net.http_post(
    url => util.project_url() || '/functions/v1/' || name,
    headers => jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', auth_header
    ),
    body => body,
    timeout_milliseconds => timeout_milliseconds
  );
end;
$$;

-- Utility: clear column on update
create or replace function util.clear_column()
returns trigger
language plpgsql as $$
declare
    clear_column text := TG_ARGV[0];
begin
    NEW := NEW #= hstore(clear_column, NULL);
    return NEW;
end;
$$;

-- Utility: queue embedding jobs
create or replace function util.queue_embeddings()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  content_function text = TG_ARGV[0];
  embedding_column text = TG_ARGV[1];
begin
  perform pgmq.send(
    queue_name => 'embedding_jobs',
    msg => jsonb_build_object(
      'id', NEW.id,
      'schema', TG_TABLE_SCHEMA,
      'table', TG_TABLE_NAME,
      'contentFunction', content_function,
      'embeddingColumn', embedding_column
    )
  );
  return NEW;
end;
$$;

-- Create the site_documents table
create table site_documents (
  id integer primary key generated always as identity,
  content_type text not null check (content_type in ('event', 'project', 'position', 'faq', 'blog', 'tutorial')),
  title text not null,
  content text not null,
  page_source text not null,
  section_title text,
  last_updated timestamp with time zone default now(),
  embedding extensions.halfvec(1536),
  created_at timestamp with time zone default now()
);

-- HNSW index for vector similarity search
create index on site_documents using hnsw (embedding halfvec_cosine_ops);

Why save a local migration file?

You already ran this SQL against your cloud database. The local migration file is for version control. It lets you recreate the same schema on a fresh database or share the project with others. The vault.create_secret command is intentionally excluded because it contains your project-specific URL.

Save supabase/migrations/001_setup.sql.
Confirm the file exists by running:

ls supabase/migrations/

You should see 001_setup.sql in the output.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file and that the Table Editor still shows site_documents in the Supabase Dashboard.

ⓧ I'd like to double check the full code

Here is the complete supabase/migrations/001_setup.sql file. Compare yours against it:

-- Step 2: Enable extensions
create extension if not exists vector with schema extensions;
create extension if not exists pgmq;
create extension if not exists pg_net with schema extensions;
create extension if not exists pg_cron;
create extension if not exists hstore with schema extensions;

-- Create utility schema
create schema if not exists util;

-- Utility: get project URL from Vault
create or replace function util.project_url()
returns text
language plpgsql
security definer
as $$
declare
  secret_value text;
begin
  select decrypted_secret into secret_value from vault.decrypted_secrets where name = 'project_url';
  return secret_value;
end;
$$;

-- Utility: invoke Edge Function
create or replace function util.invoke_edge_function(
  name text,
  body jsonb,
  timeout_milliseconds int = 5 * 60 * 1000
)
returns void
language plpgsql
as $$
declare
  headers_raw text;
  auth_header text;
begin
  headers_raw := current_setting('request.headers', true);
  auth_header := case
    when headers_raw is not null then
      (headers_raw::json->>'authorization')
    else
      null
  end;

  perform net.http_post(
    url => util.project_url() || '/functions/v1/' || name,
    headers => jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', auth_header
    ),
    body => body,
    timeout_milliseconds => timeout_milliseconds
  );
end;
$$;

-- Utility: clear column on update
create or replace function util.clear_column()
returns trigger
language plpgsql as $$
declare
    clear_column text := TG_ARGV[0];
begin
    NEW := NEW #= hstore(clear_column, NULL);
    return NEW;
end;
$$;

-- Utility: queue embedding jobs
create or replace function util.queue_embeddings()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  content_function text = TG_ARGV[0];
  embedding_column text = TG_ARGV[1];
begin
  perform pgmq.send(
    queue_name => 'embedding_jobs',
    msg => jsonb_build_object(
      'id', NEW.id,
      'schema', TG_TABLE_SCHEMA,
      'table', TG_TABLE_NAME,
      'contentFunction', content_function,
      'embeddingColumn', embedding_column
    )
  );
  return NEW;
end;
$$;

-- Create the site_documents table
create table site_documents (
  id integer primary key generated always as identity,
  content_type text not null check (content_type in ('event', 'project', 'position', 'faq', 'blog', 'tutorial')),
  title text not null,
  content text not null,
  page_source text not null,
  section_title text,
  last_updated timestamp with time zone default now(),
  embedding extensions.halfvec(1536),
  created_at timestamp with time zone default now()
);

-- HNSW index for vector similarity search
create index on site_documents using hnsw (embedding halfvec_cosine_ops);

Your database is now equipped with vector superpowers and a full set of utility functions. Next up, you will wire these utilities into an automatic pipeline that generates embeddings every time content is inserted or updated.

Build the Automatic Embedding Pipeline

Your database schema and utility functions are in place. Now you need the engine that actually generates embeddings whenever content changes.

In this step, you will wire up a background job system using pgmq queues, pg_cron scheduling, and a Supabase Edge Function that calls OpenAI to produce vector embeddings. The result is a fully automatic pipeline where inserting or updating content triggers re-embedding without any manual intervention.

In this step, get ready to:

Create the job queue, processing function, and cron schedule.
Create the embedding input function and database triggers.
Build and deploy the embed Edge Function.

Create the job queue and processing function

The embedding pipeline needs a way to queue up work and process it in batches. You will create a pgmq queue to hold embedding jobs, a function that reads jobs from the queue and dispatches them to an Edge Function, and a cron schedule that runs the processor every 10 seconds.

In your Supabase Dashboard, go to the SQL Editor section.
Click New Query.
Create the pgmq queue and the processing function by pasting and running this SQL:

-- Create pgmq queue
select pgmq.create('embedding_jobs');

-- Process embeddings function
create or replace function util.process_embeddings(
  batch_size int = 10,
  max_requests int = 10,
  timeout_milliseconds int = 5 * 60 * 1000
)
returns void
language plpgsql
as $$
declare
  job_batches jsonb[];
  batch jsonb;
begin
  with
    numbered_jobs as (
      select
        message || jsonb_build_object('jobId', msg_id) as job_info,
        (row_number() over (order by 1) - 1) / batch_size as batch_num
      from pgmq.read(
        queue_name => 'embedding_jobs',
        vt => timeout_milliseconds / 1000,
        qty => max_requests * batch_size
      )
    ),
    batched_jobs as (
      select
        jsonb_agg(job_info) as batch_array,
        batch_num
      from numbered_jobs
      group by batch_num
    )
  select coalesce(array_agg(batch_array), array[]::jsonb[])
  from batched_jobs
  into job_batches;

  foreach batch in array job_batches loop
    perform util.invoke_edge_function(
      name => 'embed',
      body => batch,
      timeout_milliseconds => timeout_milliseconds
    );
  end loop;
end;
$$;

What does this code do?

The first line creates a message queue called embedding_jobs using pgmq. This queue holds pending embedding work.

The util.process_embeddings() function reads jobs from the queue, groups them into batches (default 10 per batch), and sends each batch to the embed Edge Function via util.invoke_edge_function. Batching prevents overwhelming the OpenAI API with too many individual requests.

Click Run or press Cmd+Enter (macOS) or Ctrl+Enter (Windows).
You should see a success result in the output panel with no errors.

Schedule the processing function to run every 10 seconds by running this SQL in a new query:

-- Schedule embedding processing every 10 seconds
select
  cron.schedule(
    'process-embeddings',
    '10 seconds',
    $$
    select util.process_embeddings();
    $$
  );

What does this schedule do?

pg_cron calls util.process_embeddings() every 10 seconds. If the queue has pending jobs, they get batched and sent to the Edge Function. If the queue is empty, nothing happens. This creates a reliable polling loop without manual intervention.

Click Run. You should see a successful result confirming the schedule was created.

Seeing a permission error?

Make sure the pg_cron extension was enabled in Step 2. If you skipped that, run create extension if not exists pg_cron; first, then retry the schedule command.

Help me fix a pg_cron permission error in Supabase

Create the embedding input function and triggers

Before the pipeline can generate embeddings, it needs to know how to format content for the embedding model. You also need triggers that fire whenever a row is inserted or updated, so the pipeline activates automatically.

Create the embedding input function by running this SQL in the SQL Editor:

-- Embedding input function
create or replace function embedding_input(doc site_documents)
returns text
language plpgsql
immutable
as $$
begin
  return '[' || doc.content_type || '] # ' || doc.title || E'\n\n' || doc.content;
end;
$$;

What does this function produce?

For a document with content_type = 'blog', title = 'My Post', and some content, the function returns: [blog] # My Post\n\nThe content here.... Prepending the content type and title gives the embedding model richer context about what the text represents.

Click Run. You should see a success confirmation.
Now create the three triggers that connect the table to the pipeline by running this SQL:

-- Trigger for insert events
create trigger embed_site_documents_on_insert
  after insert
  on site_documents
  for each row
  execute function util.queue_embeddings('embedding_input', 'embedding');

-- Trigger for update events
create trigger embed_site_documents_on_update
  after update of title, content, content_type
  on site_documents
  for each row
  execute function util.queue_embeddings('embedding_input', 'embedding');

-- Trigger to clear embedding on content update
create trigger clear_site_document_embedding_on_update
  before update of title, content, content_type
  on site_documents
  for each row
  execute function util.clear_column('embedding');

How do these triggers work together?

embed_site_documents_on_insert fires after every new row is inserted. It queues an embedding job immediately.
embed_site_documents_on_update fires when the title, content, or content_type changes. It queues a fresh embedding job for the updated content.
clear_site_document_embedding_on_update fires before an update and sets the embedding column to NULL. This marks the row as stale until the new embedding arrives.

Click Run. You should see all three triggers created successfully.

Trigger creation failed?

If you see an error about site_documents not existing, make sure you created the table in Step 2. If you see an error about util.queue_embeddings not existing, run the utility functions from Step 2 first.

Help me debug a trigger creation error in Supabase

Add all the SQL you just ran (queue creation, process_embeddings, cron schedule, embedding_input, and the three triggers) to the bottom of your supabase/migrations/001_setup.sql file in VS Code. This keeps your migration file as the single source of truth.

Create and deploy the embed Edge Function

The queue and triggers are ready, but there is no function to actually generate the embeddings yet. You will now create a Supabase Edge Function that receives batched jobs, calls OpenAI's text-embedding-3-small model, and writes the resulting vectors back to the database.

Switch back to your terminal in VS Code.
Scaffold the new Edge Function by running this command:

supabase functions new embed

This creates a supabase/functions/embed/index.ts file with starter code.
Open supabase/functions/embed/index.ts in VS Code and delete all the starter code.
Add the imports, client setup, and schema definitions by pasting this code:

import 'jsr:@supabase/functions-js/edge-runtime.d.ts'
import OpenAI from 'jsr:@openai/openai'
import { z } from 'npm:zod'
import postgres from 'https://deno.land/x/postgresjs@v3.4.5/mod.js'

// Initialize OpenAI client using the secret we'll set later
const openai = new OpenAI({
  apiKey: Deno.env.get('OPENAI_API_KEY'),
})

// Connect directly to the Supabase database
const sql = postgres(Deno.env.get('SUPABASE_DB_URL')!)

// Schema for validating incoming job payloads
const jobSchema = z.object({
  jobId: z.number(),
  id: z.number(),
  schema: z.string(),
  table: z.string(),
  contentFunction: z.string(),
  embeddingColumn: z.string(),
})

const failedJobSchema = jobSchema.extend({
  error: z.string(),
})

type Job = z.infer<typeof jobSchema>
type FailedJob = z.infer<typeof failedJobSchema>

type Row = {
  id: string
  content: unknown
}

// Must match the pgmq queue name
const QUEUE_NAME = 'embedding_jobs'

What does this setup section do?

The imports bring in the OpenAI SDK, Zod for request validation, and a Postgres driver for direct database access.
SUPABASE_DB_URL is a built-in environment variable available in all Edge Functions. No manual setup needed.
The jobSchema validates each job payload from the queue, ensuring the Edge Function only processes well-formed requests.

Add the main request handler below the setup code:

Deno.serve(async (req) => {
  if (req.method !== 'POST') {
    return new Response('expected POST request', { status: 405 })
  }

  if (req.headers.get('content-type') !== 'application/json') {
    return new Response('expected json body', { status: 400 })
  }

  // Validate the incoming array of jobs
  const parseResult = z.array(jobSchema).safeParse(await req.json())

  if (parseResult.error) {
    return new Response(`invalid request body: ${parseResult.error.message}`, {
      status: 400,
    })
  }

  const pendingJobs = parseResult.data
  const completedJobs: Job[] = []
  const failedJobs: FailedJob[] = []

  // Process each job sequentially
  async function processJobs() {
    let currentJob: Job | undefined

    while ((currentJob = pendingJobs.shift()) !== undefined) {
      try {
        await processJob(currentJob)
        completedJobs.push(currentJob)
      } catch (error) {
        failedJobs.push({
          ...currentJob,
          error: error instanceof Error ? error.message : JSON.stringify(error),
        })
      }
    }
  }

  // Race between job processing and function shutdown
  try {
    await Promise.race([processJobs(), catchUnload()])
  } catch (error) {
    failedJobs.push(
      ...pendingJobs.map((job) => ({
        ...job,
        error: error instanceof Error ? error.message : JSON.stringify(error),
      }))
    )
  }

  console.log('finished processing jobs:', {
    completedJobs: completedJobs.length,
    failedJobs: failedJobs.length,
  })

  // Return a summary of completed and failed jobs
  return new Response(
    JSON.stringify({
      completedJobs,
      failedJobs,
    }),
    {
      status: 200,
      headers: {
        'content-type': 'application/json',
        'x-completed-jobs': completedJobs.length.toString(),
        'x-failed-jobs': failedJobs.length.toString(),
      },
    }
  )
})

What does the request handler do?

The handler validates the incoming POST request, then loops through each job in the batch. If the Edge Function is about to shut down (due to timeout), catchUnload() catches that event and marks remaining jobs as failed so they stay in the queue for retry.

Failed jobs are not deleted from the queue. They become visible again after the visibility timeout expires, so pg_cron picks them up on the next cycle. This gives you automatic retry for free.

Add the helper functions below the request handler:

// Generate a vector embedding for the given text
async function generateEmbedding(text: string) {
  const response = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: text,
  })
  const [data] = response.data

  if (!data) {
    throw new Error('failed to generate embedding')
  }

  return data.embedding
}

// Process a single job: read content, generate embedding, write back
async function processJob(job: Job) {
  const { jobId, id, schema, table, contentFunction, embeddingColumn } = job

  // Call the content function (embedding_input) to get formatted text
  const [row]: [Row] = await sql`
    select
      id,
      ${sql(contentFunction)}(t) as content
    from
      ${sql(schema)}.${sql(table)} t
    where
      id = ${id}
  `

  if (!row) {
    throw new Error(`row not found: ${schema}.${table}/${id}`)
  }

  if (typeof row.content !== 'string') {
    throw new Error(`invalid content - expected string: ${schema}.${table}/${id}`)
  }

  // Generate the embedding via OpenAI
  const embedding = await generateEmbedding(row.content)

  // Write the embedding back to the row
  await sql`
    update
      ${sql(schema)}.${sql(table)}
    set
      ${sql(embeddingColumn)} = ${JSON.stringify(embedding)}
    where
      id = ${id}
  `

  // Delete the completed job from the queue
  await sql`
    select pgmq.delete(${QUEUE_NAME}, ${jobId}::bigint)
  `
}

// Catch Edge Function shutdown to gracefully handle remaining jobs
function catchUnload() {
  return new Promise((reject) => {
    addEventListener('beforeunload', (ev: any) => {
      reject(new Error(ev.detail?.reason))
    })
  })
}

What do these helper functions do?

generateEmbedding sends text to OpenAI's text-embedding-3-small model and returns a 1536-dimensional vector.
processJob reads the row's content using the embedding_input function you created earlier, generates the embedding, writes it to the database, then deletes the job from the queue.
catchUnload listens for the beforeunload event, which Supabase fires when an Edge Function is about to be shut down.

Save supabase/functions/embed/index.ts.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file before deploying.

ⓧ I'd like to double check the full code

import 'jsr:@supabase/functions-js/edge-runtime.d.ts'
import OpenAI from 'jsr:@openai/openai'
import { z } from 'npm:zod'
import postgres from 'https://deno.land/x/postgresjs@v3.4.5/mod.js'

const openai = new OpenAI({
  apiKey: Deno.env.get('OPENAI_API_KEY'),
})

const sql = postgres(Deno.env.get('SUPABASE_DB_URL')!)

const jobSchema = z.object({
  jobId: z.number(),
  id: z.number(),
  schema: z.string(),
  table: z.string(),
  contentFunction: z.string(),
  embeddingColumn: z.string(),
})

const failedJobSchema = jobSchema.extend({
  error: z.string(),
})

type Job = z.infer<typeof jobSchema>
type FailedJob = z.infer<typeof failedJobSchema>

type Row = {
  id: string
  content: unknown
}

const QUEUE_NAME = 'embedding_jobs'

Deno.serve(async (req) => {
  if (req.method !== 'POST') {
    return new Response('expected POST request', { status: 405 })
  }

  if (req.headers.get('content-type') !== 'application/json') {
    return new Response('expected json body', { status: 400 })
  }

  const parseResult = z.array(jobSchema).safeParse(await req.json())

  if (parseResult.error) {
    return new Response(`invalid request body: ${parseResult.error.message}`, {
      status: 400,
    })
  }

  const pendingJobs = parseResult.data
  const completedJobs: Job[] = []
  const failedJobs: FailedJob[] = []

  async function processJobs() {
    let currentJob: Job | undefined

    while ((currentJob = pendingJobs.shift()) !== undefined) {
      try {
        await processJob(currentJob)
        completedJobs.push(currentJob)
      } catch (error) {
        failedJobs.push({
          ...currentJob,
          error: error instanceof Error ? error.message : JSON.stringify(error),
        })
      }
    }
  }

  try {
    await Promise.race([processJobs(), catchUnload()])
  } catch (error) {
    failedJobs.push(
      ...pendingJobs.map((job) => ({
        ...job,
        error: error instanceof Error ? error.message : JSON.stringify(error),
      }))
    )
  }

  console.log('finished processing jobs:', {
    completedJobs: completedJobs.length,
    failedJobs: failedJobs.length,
  })

  return new Response(
    JSON.stringify({
      completedJobs,
      failedJobs,
    }),
    {
      status: 200,
      headers: {
        'content-type': 'application/json',
        'x-completed-jobs': completedJobs.length.toString(),
        'x-failed-jobs': failedJobs.length.toString(),
      },
    }
  )
})

async function generateEmbedding(text: string) {
  const response = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: text,
  })
  const [data] = response.data

  if (!data) {
    throw new Error('failed to generate embedding')
  }

  return data.embedding
}

async function processJob(job: Job) {
  const { jobId, id, schema, table, contentFunction, embeddingColumn } = job

  const [row]: [Row] = await sql`
    select
      id,
      ${sql(contentFunction)}(t) as content
    from
      ${sql(schema)}.${sql(table)} t
    where
      id = ${id}
  `

  if (!row) {
    throw new Error(`row not found: ${schema}.${table}/${id}`)
  }

  if (typeof row.content !== 'string') {
    throw new Error(`invalid content - expected string: ${schema}.${table}/${id}`)
  }

  const embedding = await generateEmbedding(row.content)

  await sql`
    update
      ${sql(schema)}.${sql(table)}
    set
      ${sql(embeddingColumn)} = ${JSON.stringify(embedding)}
    where
      id = ${id}
  `

  await sql`
    select pgmq.delete(${QUEUE_NAME}, ${jobId}::bigint)
  `
}

function catchUnload() {
  return new Promise((reject) => {
    addEventListener('beforeunload', (ev: any) => {
      reject(new Error(ev.detail?.reason))
    })
  })
}

Set your OpenAI API key as a secret for the Edge Function by running this command:

supabase secrets set OPENAI_API_KEY=your-openai-api-key-here

Replace your-openai-api-key-here with the actual API key you noted from platform.openai.com in Step 1.
Deploy the Edge Function by running:

supabase functions deploy --no-verify-jwt

Why use --no-verify-jwt?

The --no-verify-jwt flag disables JWT verification on the function. This is necessary because the function is called internally by pg_net (via the util.invoke_edge_function utility), not by an end user with a Supabase auth token.

Wait for the deployment to complete. You should see a success confirmation in the terminal indicating the function is live.

Deployment failed?

Make sure you are in your project root folder (the one containing the supabase/ directory). Also verify your project is linked by checking that supabase/config.toml exists.

Help me fix my Edge Function deployment error

Your automatic embedding pipeline is complete. Whenever content is inserted or updated in site_documents, the triggers queue a job, pg_cron picks it up within 10 seconds, and the Edge Function generates and stores the embedding. Next up, you will create a semantic search function that queries these embeddings.

Create the Semantic Search Function

Your embedding pipeline is now running automatically in the background. Every time content is inserted or updated, the triggers fire, jobs queue up, and the Edge Function generates embeddings.

Now you need a way to actually query those embeddings. In this step, you will create a match_documents RPC function that takes a query embedding and finds the most semantically similar documents in your database.

In this step, get ready to:

Create the match_documents semantic search function using cosine distance.
Test the full pipeline by inserting sample content and running a semantic search query.

Create the match_documents RPC function

A semantic search function needs to compare the meaning of a user's question against all stored embeddings. The pgvector extension provides the <=> operator for cosine distance, which measures how similar two vectors are.

In your Supabase Dashboard, open the SQL Editor from the left sidebar.
Create the match_documents function by pasting and running this SQL:

-- Semantic search function
create or replace function match_documents(
  query_embedding extensions.halfvec(1536),
  match_threshold float default 0.7,
  match_count int default 10,
  filter_content_type text default null
)
returns table (
  id integer,
  content_type text,
  title text,
  content text,
  page_source text,
  section_title text,
  last_updated timestamp with time zone,
  similarity float
)
language sql stable
as $$
  select
    site_documents.id,
    site_documents.content_type,
    site_documents.title,
    site_documents.content,
    site_documents.page_source,
    site_documents.section_title,
    site_documents.last_updated,
    1 - (site_documents.embedding <=> query_embedding) as similarity
  from site_documents
  where
    site_documents.embedding is not null
    and 1 - (site_documents.embedding <=> query_embedding) > match_threshold
    and (filter_content_type is null or site_documents.content_type = filter_content_type)
  order by site_documents.embedding <=> query_embedding asc
  limit least(match_count, 200);
$$;

What does this function do?

This function is what your chatbot or search UI will call to find relevant content. It works by comparing vector distances.

query_embedding is the vector representation of the user's question, generated by the same text-embedding-3-small model.
1 - (embedding <=> query_embedding) converts cosine distance into a similarity score (1.0 = identical, 0.0 = unrelated).
match_threshold filters out low-quality results. Only documents above this similarity score are returned.
filter_content_type lets callers narrow results to a single type (e.g., only faq or only blog).

Run the query. You should see a "Success" message confirming the function was created.

Seeing a "type halfvec does not exist" error?

This means the vector extension was not enabled in the extensions schema. Go back to the SQL Editor and run create extension if not exists vector with schema extensions; first, then try creating the function again.

Help me fix this halfvec error in my match_documents function

Now add this function to your migration file so it is tracked alongside the rest of your schema.

In VS Code, open supabase/migrations/001_setup.sql.
Scroll to the bottom of the file (after the clear_site_document_embedding_on_update trigger) and paste the same match_documents function SQL at the end.
Save 001_setup.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full code

-- Step 2: Enable extensions
create extension if not exists vector with schema extensions;
create extension if not exists pgmq;
create extension if not exists pg_net with schema extensions;
create extension if not exists pg_cron;
create extension if not exists hstore with schema extensions;

-- Create utility schema
create schema if not exists util;

-- Utility: get project URL from Vault
create or replace function util.project_url()
returns text
language plpgsql
security definer
as $$
declare
  secret_value text;
begin
  select decrypted_secret into secret_value from vault.decrypted_secrets where name = 'project_url';
  return secret_value;
end;
$$;

-- Utility: invoke Edge Function
create or replace function util.invoke_edge_function(
  name text,
  body jsonb,
  timeout_milliseconds int = 5 * 60 * 1000
)
returns void
language plpgsql
as $$
declare
  headers_raw text;
  auth_header text;
begin
  headers_raw := current_setting('request.headers', true);
  auth_header := case
    when headers_raw is not null then
      (headers_raw::json->>'authorization')
    else
      null
  end;

  perform net.http_post(
    url => util.project_url() || '/functions/v1/' || name,
    headers => jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', auth_header
    ),
    body => body,
    timeout_milliseconds => timeout_milliseconds
  );
end;
$$;

-- Utility: clear column on update
create or replace function util.clear_column()
returns trigger
language plpgsql as $$
declare
    clear_column text := TG_ARGV[0];
begin
    NEW := NEW #= hstore(clear_column, NULL);
    return NEW;
end;
$$;

-- Utility: queue embedding jobs
create or replace function util.queue_embeddings()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  content_function text = TG_ARGV[0];
  embedding_column text = TG_ARGV[1];
begin
  perform pgmq.send(
    queue_name => 'embedding_jobs',
    msg => jsonb_build_object(
      'id', NEW.id,
      'schema', TG_TABLE_SCHEMA,
      'table', TG_TABLE_NAME,
      'contentFunction', content_function,
      'embeddingColumn', embedding_column
    )
  );
  return NEW;
end;
$$;

-- Create the site_documents table
create table site_documents (
  id integer primary key generated always as identity,
  content_type text not null check (content_type in ('event', 'project', 'position', 'faq', 'blog', 'tutorial')),
  title text not null,
  content text not null,
  page_source text not null,
  section_title text,
  last_updated timestamp with time zone default now(),
  embedding extensions.halfvec(1536),
  created_at timestamp with time zone default now()
);

-- HNSW index for vector similarity search
create index on site_documents using hnsw (embedding halfvec_cosine_ops);

-- Create pgmq queue
select pgmq.create('embedding_jobs');

-- Process embeddings function
create or replace function util.process_embeddings(
  batch_size int = 10,
  max_requests int = 10,
  timeout_milliseconds int = 5 * 60 * 1000
)
returns void
language plpgsql
as $$
declare
  job_batches jsonb[];
  batch jsonb;
begin
  with
    numbered_jobs as (
      select
        message || jsonb_build_object('jobId', msg_id) as job_info,
        (row_number() over (order by 1) - 1) / batch_size as batch_num
      from pgmq.read(
        queue_name => 'embedding_jobs',
        vt => timeout_milliseconds / 1000,
        qty => max_requests * batch_size
      )
    ),
    batched_jobs as (
      select
        jsonb_agg(job_info) as batch_array,
        batch_num
      from numbered_jobs
      group by batch_num
    )
  select coalesce(array_agg(batch_array), array[]::jsonb[])
  from batched_jobs
  into job_batches;

  foreach batch in array job_batches loop
    perform util.invoke_edge_function(
      name => 'embed',
      body => batch,
      timeout_milliseconds => timeout_milliseconds
    );
  end loop;
end;
$$;

-- Schedule embedding processing every 10 seconds
select
  cron.schedule(
    'process-embeddings',
    '10 seconds',
    $$
    select util.process_embeddings();
    $$
  );

-- Embedding input function
create or replace function embedding_input(doc site_documents)
returns text
language plpgsql
immutable
as $$
begin
  return '[' || doc.content_type || '] # ' || doc.title || E'\n\n' || doc.content;
end;
$$;

-- Trigger for insert events
create trigger embed_site_documents_on_insert
  after insert
  on site_documents
  for each row
  execute function util.queue_embeddings('embedding_input', 'embedding');

-- Trigger for update events
create trigger embed_site_documents_on_update
  after update of title, content, content_type
  on site_documents
  for each row
  execute function util.queue_embeddings('embedding_input', 'embedding');

-- Trigger to clear embedding on content update
create trigger clear_site_document_embedding_on_update
  before update of title, content, content_type
  on site_documents
  for each row
  execute function util.clear_column('embedding');

-- Semantic search function
create or replace function match_documents(
  query_embedding extensions.halfvec(1536),
  match_threshold float default 0.7,
  match_count int default 10,
  filter_content_type text default null
)
returns table (
  id integer,
  content_type text,
  title text,
  content text,
  page_source text,
  section_title text,
  last_updated timestamp with time zone,
  similarity float
)
language sql stable
as $$
  select
    site_documents.id,
    site_documents.content_type,
    site_documents.title,
    site_documents.content,
    site_documents.page_source,
    site_documents.section_title,
    site_documents.last_updated,
    1 - (site_documents.embedding <=> query_embedding) as similarity
  from site_documents
  where
    site_documents.embedding is not null
    and 1 - (site_documents.embedding <=> query_embedding) > match_threshold
    and (filter_content_type is null or site_documents.content_type = filter_content_type)
  order by site_documents.embedding <=> query_embedding asc
  limit least(match_count, 200);
$$;

Test the search function end-to-end

To verify everything works, you will insert sample content, let the embedding pipeline process it, and then run a semantic search query against the results.

In the SQL Editor, insert two sample documents by running this query:

-- Insert sample content to test the pipeline
insert into site_documents (content_type, title, content, page_source, section_title)
values
  ('blog', 'Introduction to Vector Databases', 'Vector databases store data as high-dimensional vectors, enabling similarity search based on meaning rather than exact keywords. They are essential for modern AI applications like semantic search, recommendation systems, and RAG pipelines.', '/blog/intro-to-vector-databases', 'Understanding Vectors'),
  ('faq', 'How do I reset my password?', 'To reset your password, click the Forgot Password link on the login page. Enter your email address and we will send you a reset link. The link expires after 24 hours.', '/faq/account', 'Account Management');

What happens next?

The insert triggers embed_site_documents_on_insert fire for each row. Each trigger sends a job to the embedding_jobs queue. Within 10 seconds, pg_cron picks up the batch and calls your embed Edge Function, which generates the embeddings and writes them back.

Wait about 15 seconds for the embedding pipeline to process.
Verify that embeddings were generated by running this query:

-- Check that embeddings have been populated
select id, title, (embedding is not null) as has_embedding
from site_documents;

You should see both rows with has_embedding showing true. If they still show false, wait another 10 seconds and run the query again.

Embeddings still null after 30 seconds?

Check that your Edge Function is deployed by running supabase functions list in your terminal. You should see embed listed.
Verify the OPENAI_API_KEY secret is set correctly. Run supabase secrets list to confirm it exists.
Check the Edge Function logs in your Supabase Dashboard under Edge Functions > embed > Logs for error details.

Help me debug why my embeddings are not being generated

Now test the match_documents function. Since you cannot easily type a 1536-dimensional vector by hand, you will use an existing embedding from the database as the query.

Run this query to search using the first document's own embedding as the query vector:

-- Test match_documents using an existing embedding as the query
select id, content_type, title, similarity
from match_documents(
  (select embedding from site_documents limit 1),
  0.3,
  5,
  null
);

You should see both documents returned with similarity scores. The document whose embedding you used as the query will have a similarity of 1.0 (perfect match to itself).

Why use an existing embedding as the query?

In production, your application generates a query embedding from the user's natural language question using the same text-embedding-3-small model. You will build that query script in the next step. For now, reusing a stored embedding proves the function works correctly.

Getting zero results?

Make sure you lowered match_threshold to 0.3 in the test query. The default of 0.7 may filter out the second document since it covers a different topic.
Confirm the embedding column is not null on both rows by re-running the verification query above.

Help me troubleshoot why match_documents returns no results

Your semantic search function is live. In the next step, you will build a Node.js ingestion script to load real website content and a query script that generates embeddings from natural language questions.

Build the Content Ingestion Script

Your database schema, embedding pipeline, and Edge Function are all in place. The automatic pipeline is waiting for data to flow through it.

In this step, you will create a Node.js script that loads sample website content into your site_documents table and then test the full loop by querying it with natural language.

In this step, get ready to:

Create and run an ingestion script that inserts sample content for 6 content types.
Verify that embeddings are automatically generated by the pipeline.
Test end-to-end semantic search with a query script.

Create the ingestion script and its dependencies

Before writing the ingestion logic, you need a package.json to manage your Node.js dependencies and an .env file to store your connection credentials.

Create a package.json file in the root of your project folder by pasting the following code into it:

{
  "name": "supabase-rag-pipeline",
  "version": "1.0.0",
  "type": "module",
  "scripts": {
    "ingest": "node ingest.js",
    "search": "node search.js"
  },
  "dependencies": {
    "@supabase/supabase-js": "^2.49.0",
    "openai": "^4.96.0"
  }
}

What does this file do?

The package.json defines your project as an ES module ("type": "module") so you can use import statements. It also declares two dependencies: the Supabase client library for database operations and the OpenAI SDK for generating query embeddings.

The scripts section creates shortcuts so you can run your scripts with npm run ingest and npm run search.

Save package.json.
Install the dependencies by running:

npm install

Confirm the install succeeded by running:

ls node_modules

You should see folders listed including @supabase and openai.

npm install failing?

Make sure your terminal is in the same folder as package.json. Run ls to confirm you can see the file. If not, cd into the correct project folder and try again.

help me debug why npm install is failing in my project folder

Create a .env file in the root of your project folder and paste in the following:

SUPABASE_URL=your-supabase-project-url-here
SUPABASE_SERVICE_ROLE_KEY=your-supabase-service-role-key-here
OPENAI_API_KEY=your-openai-api-key-here

Replace your-supabase-project-url-here with your Supabase project URL from the API settings page.
Replace your-supabase-service-role-key-here with your service role key from the same page.
Replace your-openai-api-key-here with your OpenAI API key.
Save .env.

Why a .env file?

The .env file keeps your credentials out of your code. Your scripts read these values from process.env at runtime. Never commit this file to version control.

Now you will create the ingestion script itself. This script connects to Supabase, defines sample content for all 6 content types, and inserts them into the site_documents table.

Create a file called ingest.js in the root of your project folder. Start by pasting in the imports and client setup:

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

// Read connection details from environment variables
const supabaseUrl = process.env.SUPABASE_URL
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY

// Exit early if credentials are missing
if (!supabaseUrl || !supabaseKey) {
  console.error('Missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY environment variables')
  process.exit(1)
}

// Create the Supabase client with service role access
const supabase = createClient(supabaseUrl, supabaseKey)

What does this code do?

The script imports the Supabase client library, reads your project URL and service role key from environment variables, and creates an authenticated client. The service role key bypasses Row Level Security, which is necessary for server-side ingestion.

Below the client setup, paste in the sample content array that represents 6 different website content types:

// Sample website content covering all 6 content types
const sampleContent = [
  {
    content_type: 'event',
    title: 'Annual Tech Conference 2026',
    content: 'Join us for our annual technology conference featuring workshops on AI, cloud computing, and web development. The event runs June 15-17 at the Convention Center. Early bird registration is open until May 30. Speakers include industry leaders from major tech companies.',
    page_source: '/events/annual-tech-conference-2026',
    section_title: 'Event Details',
  },
  {
    content_type: 'project',
    title: 'Build a Real-Time Chat Application',
    content: 'Learn to build a real-time chat application using WebSockets and React. This project covers authentication, message persistence, typing indicators, and online presence. Estimated completion time is 3 hours for intermediate developers.',
    page_source: '/projects/real-time-chat',
    section_title: 'Project Overview',
  },
  {
    content_type: 'position',
    title: 'Senior Backend Engineer',
    content: 'We are hiring a Senior Backend Engineer with experience in Node.js, PostgreSQL, and distributed systems. The role involves designing APIs, optimizing database queries, and mentoring junior engineers. Remote-friendly with flexible hours. Competitive salary and equity package.',
    page_source: '/careers/senior-backend-engineer',
    section_title: 'Job Description',
  },
  {
    content_type: 'faq',
    title: 'How do I reset my password?',
    content: 'To reset your password, click the "Forgot Password" link on the login page. Enter your email address and we will send you a reset link. The link expires after 24 hours. If you do not receive the email, check your spam folder or contact support.',
    page_source: '/faq/account',
    section_title: 'Account Management',
  },
  {
    content_type: 'blog',
    title: 'Introduction to Vector Databases',
    content: 'Vector databases store data as high-dimensional vectors, enabling similarity search based on meaning rather than exact keywords. They are essential for modern AI applications like semantic search, recommendation systems, and RAG pipelines. Popular options include pgvector for Postgres, Pinecone, and Weaviate.',
    page_source: '/blog/intro-to-vector-databases',
    section_title: 'Understanding Vectors',
  },
  {
    content_type: 'tutorial',
    title: 'Getting Started with Supabase Edge Functions',
    content: 'Supabase Edge Functions run on Deno and are deployed globally. They are perfect for server-side logic like processing webhooks, generating embeddings, and calling third-party APIs. Install the Supabase CLI, create a function with supabase functions new, and deploy with supabase functions deploy.',
    page_source: '/tutorials/supabase-edge-functions',
    section_title: 'Quick Start Guide',
  },
]

What does this data represent?

Each object represents a chunk of website content with its metadata. The content_type field matches one of the 6 types your schema allows. The page_source field tracks where the content lives on the website, and section_title identifies the section within that page.

When the match_documents function returns search results later, these metadata fields let you show users exactly where the answer came from.

Below the sampleContent array, paste in the ingest() function that does the actual insertion:

// Insert all sample documents into the site_documents table
async function ingest() {
  console.log(`Ingesting ${sampleContent.length} documents...`)

  const { data, error } = await supabase
    .from('site_documents')
    .insert(sampleContent)
    .select('id, title, content_type')

  if (error) {
    console.error('Error inserting documents:', error.message)
    process.exit(1)
  }

  console.log(`Successfully inserted ${data.length} documents:`)
  data.forEach((doc) => {
    console.log(`  [${doc.content_type}] ${doc.title} (id: ${doc.id})`)
  })

  console.log('\nEmbeddings will be generated automatically within ~10 seconds.')
  console.log('Run the search script after waiting for embeddings to populate.')
}

// Run the ingestion
ingest()

What does this function do?

The ingest() function uses the Supabase client to bulk-insert all 6 documents into site_documents. The .select() chain returns the inserted rows so you can confirm what was created.

As soon as rows land in the table, the insert trigger fires, queues embedding jobs via pgmq, and pg_cron picks them up within 10 seconds. Your Edge Function then generates the embeddings automatically.

Save ingest.js.

✔️ Awesome, I've got everything!

Great. Double check you have saved ingest.js and that it is in the same folder as package.json.

ⓧ I'd like to double check the full code

Here is the complete ingest.js for comparison:

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

const supabaseUrl = process.env.SUPABASE_URL
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY

if (!supabaseUrl || !supabaseKey) {
  console.error('Missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY environment variables')
  process.exit(1)
}

const supabase = createClient(supabaseUrl, supabaseKey)

const sampleContent = [
  {
    content_type: 'event',
    title: 'Annual Tech Conference 2026',
    content: 'Join us for our annual technology conference featuring workshops on AI, cloud computing, and web development. The event runs June 15-17 at the Convention Center. Early bird registration is open until May 30. Speakers include industry leaders from major tech companies.',
    page_source: '/events/annual-tech-conference-2026',
    section_title: 'Event Details',
  },
  {
    content_type: 'project',
    title: 'Build a Real-Time Chat Application',
    content: 'Learn to build a real-time chat application using WebSockets and React. This project covers authentication, message persistence, typing indicators, and online presence. Estimated completion time is 3 hours for intermediate developers.',
    page_source: '/projects/real-time-chat',
    section_title: 'Project Overview',
  },
  {
    content_type: 'position',
    title: 'Senior Backend Engineer',
    content: 'We are hiring a Senior Backend Engineer with experience in Node.js, PostgreSQL, and distributed systems. The role involves designing APIs, optimizing database queries, and mentoring junior engineers. Remote-friendly with flexible hours. Competitive salary and equity package.',
    page_source: '/careers/senior-backend-engineer',
    section_title: 'Job Description',
  },
  {
    content_type: 'faq',
    title: 'How do I reset my password?',
    content: 'To reset your password, click the "Forgot Password" link on the login page. Enter your email address and we will send you a reset link. The link expires after 24 hours. If you do not receive the email, check your spam folder or contact support.',
    page_source: '/faq/account',
    section_title: 'Account Management',
  },
  {
    content_type: 'blog',
    title: 'Introduction to Vector Databases',
    content: 'Vector databases store data as high-dimensional vectors, enabling similarity search based on meaning rather than exact keywords. They are essential for modern AI applications like semantic search, recommendation systems, and RAG pipelines. Popular options include pgvector for Postgres, Pinecone, and Weaviate.',
    page_source: '/blog/intro-to-vector-databases',
    section_title: 'Understanding Vectors',
  },
  {
    content_type: 'tutorial',
    title: 'Getting Started with Supabase Edge Functions',
    content: 'Supabase Edge Functions run on Deno and are deployed globally. They are perfect for server-side logic like processing webhooks, generating embeddings, and calling third-party APIs. Install the Supabase CLI, create a function with supabase functions new, and deploy with supabase functions deploy.',
    page_source: '/tutorials/supabase-edge-functions',
    section_title: 'Quick Start Guide',
  },
]

async function ingest() {
  console.log(`Ingesting ${sampleContent.length} documents...`)

  const { data, error } = await supabase
    .from('site_documents')
    .insert(sampleContent)
    .select('id, title, content_type')

  if (error) {
    console.error('Error inserting documents:', error.message)
    process.exit(1)
  }

  console.log(`Successfully inserted ${data.length} documents:`)
  data.forEach((doc) => {
    console.log(`  [${doc.content_type}] ${doc.title} (id: ${doc.id})`)
  })

  console.log('\nEmbeddings will be generated automatically within ~10 seconds.')
  console.log('Run the search script after waiting for embeddings to populate.')
}

ingest()

Run the ingestion script and verify embeddings

With ingest.js ready, you can now load the sample content into your database and watch the automatic embedding pipeline process it.

Run the ingestion script by executing this command:

node --env-file=.env ingest.js

What does --env-file do?

The --env-file=.env flag tells Node.js to load the variables from your .env file into process.env before running the script. This flag requires Node.js 20.6 or higher.

You should see output listing all 6 inserted documents with their content types, titles, and IDs.

Seeing an error about environment variables?

If you see the message about missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY, double check that your .env file has the correct values with no extra spaces around the = sign.

If you see an error about --env-file not being recognized, your Node.js version may be below 20.6. Run node --version to check. If needed, upgrade Node.js to version 20 or higher.

help me troubleshoot errors when running my ingest.js script with node --env-file=.env

Now verify that the embedding pipeline has processed your documents. The pg_cron job runs every 10 seconds, so wait about 15 seconds before checking.

Open the SQL Editor in your Supabase Dashboard.
Run this query to check how many rows have embeddings:

select id, title, content_type, (embedding is not null) as has_embedding
from site_documents;

You should see all 6 rows with has_embedding showing true. If any still show false, wait another 10 seconds and re-run the query.

Embeddings still showing false after 30 seconds?

Check that your Edge Function is deployed and has the OPENAI_API_KEY secret set. You can verify by navigating to Edge Functions in your Supabase Dashboard and checking the embed function's logs for errors.

Also confirm that the Vault secret project_url was set correctly in a previous step. The embedding pipeline uses it to call your Edge Function.

help me debug why my embeddings are not being generated automatically after inserting documents

Test end-to-end semantic search

The final piece is a query script that converts a natural language question into a vector embedding and calls match_documents to retrieve the most relevant content.

Create a file called search.js in the root of your project folder. Start with the imports and client setup:

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

// Read all required environment variables
const supabaseUrl = process.env.SUPABASE_URL
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY
const openaiKey = process.env.OPENAI_API_KEY

// Exit if any credentials are missing
if (!supabaseUrl || !supabaseKey || !openaiKey) {
  console.error('Missing required environment variables: SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY, OPENAI_API_KEY')
  process.exit(1)
}

// Create clients for Supabase and OpenAI
const supabase = createClient(supabaseUrl, supabaseKey)
const openai = new OpenAI({ apiKey: openaiKey })

What does this code do?

This script needs both the Supabase client (to call the match_documents RPC function) and the OpenAI client (to generate an embedding for the search query). The query embedding must use the same model (text-embedding-3-small) as the stored embeddings for cosine similarity to work correctly.

Below the client setup, paste in the search() function:

// Search for documents similar to a natural language query
async function search(query, contentType = null) {
  console.log(`\nSearching for: "${query}"`)
  if (contentType) console.log(`  Filtering by content type: ${contentType}`)

  // Generate an embedding for the search query
  const embeddingResponse = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: query,
  })

  const queryEmbedding = embeddingResponse.data[0].embedding

  // Call the match_documents RPC function with the query embedding
  const { data: results, error } = await supabase.rpc('match_documents', {
    query_embedding: queryEmbedding,
    match_threshold: 0.3,
    match_count: 5,
    filter_content_type: contentType,
  })

  if (error) {
    console.error('Search error:', error.message)
    return
  }

  // Display results with similarity scores and metadata
  console.log(`  Found ${results.length} results:\n`)
  results.forEach((result, index) => {
    console.log(`  ${index + 1}. [${result.content_type}] ${result.title}`)
    console.log(`     Similarity: ${(result.similarity * 100).toFixed(1)}%`)
    console.log(`     Source: ${result.page_source}`)
    console.log(`     Section: ${result.section_title}`)
    console.log(`     Content: ${result.content.substring(0, 100)}...`)
    console.log()
  })
}

What does the search function do?

The search() function first converts your plain-English query into a 1536-dimensional vector using OpenAI. It then calls the match_documents RPC function you created earlier, passing the embedding along with threshold and count parameters.

The optional filter_content_type parameter lets you narrow results to a specific type (like only job positions or only FAQs).

Below the search() function, paste in the main() function that runs three test queries:

// Run three test queries to demonstrate semantic search
async function main() {
  await search('How can I join your upcoming events?')
  await search('I want to apply for an engineering job', 'position')
  await search('What is a vector database and how does it work?')
}

main()

Why these three queries?

Each query tests a different aspect of semantic search. The first query uses casual language that does not match any document title exactly but should match the event content by meaning. The second filters by content_type to show metadata filtering in action. The third tests a conceptual question against the blog content.

Save search.js.

✔️ Awesome, I've got everything!

Great. Double check you have saved search.js and that it is in the same folder as package.json.

ⓧ I'd like to double check the full code

Here is the complete search.js for comparison:

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

const supabaseUrl = process.env.SUPABASE_URL
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY
const openaiKey = process.env.OPENAI_API_KEY

if (!supabaseUrl || !supabaseKey || !openaiKey) {
  console.error('Missing required environment variables: SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY, OPENAI_API_KEY')
  process.exit(1)
}

const supabase = createClient(supabaseUrl, supabaseKey)
const openai = new OpenAI({ apiKey: openaiKey })

async function search(query, contentType = null) {
  console.log(`\nSearching for: "${query}"`)
  if (contentType) console.log(`  Filtering by content type: ${contentType}`)

  const embeddingResponse = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: query,
  })

  const queryEmbedding = embeddingResponse.data[0].embedding

  const { data: results, error } = await supabase.rpc('match_documents', {
    query_embedding: queryEmbedding,
    match_threshold: 0.3,
    match_count: 5,
    filter_content_type: contentType,
  })

  if (error) {
    console.error('Search error:', error.message)
    return
  }

  console.log(`  Found ${results.length} results:\n`)
  results.forEach((result, index) => {
    console.log(`  ${index + 1}. [${result.content_type}] ${result.title}`)
    console.log(`     Similarity: ${(result.similarity * 100).toFixed(1)}%`)
    console.log(`     Source: ${result.page_source}`)
    console.log(`     Section: ${result.section_title}`)
    console.log(`     Content: ${result.content.substring(0, 100)}...`)
    console.log()
  })
}

async function main() {
  await search('How can I join your upcoming events?')
  await search('I want to apply for an engineering job', 'position')
  await search('What is a vector database and how does it work?')
}

main()

Run the search script by executing:

node --env-file=.env search.js

You should see results for each of the three queries with similarity percentages, source pages, and content previews.

Notice how the first query ("How can I join your upcoming events?") matches the tech conference document even though none of those exact words appear in the title. That is semantic search in action.

Getting zero results?

If all three queries return 0 results, the embeddings may not have finished generating. Go back to the SQL Editor and re-run the has_embedding check from the previous substep. All 6 rows must show true before search will work.

If you see an error about the match_documents function, make sure the RPC was created successfully in Step 4 by checking the SQL Editor for any migration errors.

help me debug why my semantic search query is returning zero results from match_documents

Your RAG pipeline is now fully operational. Content goes in, embeddings are generated automatically, and semantic search returns the most relevant results with full source attribution. Next up, you will tackle the Secret Mission and add hybrid search for even better retrieval.

Secret mission

Add Hybrid Search with Reciprocal Rank Fusion

Your semantic search works great for meaning-based queries. But what happens when someone searches for an exact name like "Annual Tech Conference 2026"? Pure vector similarity can miss exact keyword matches. Combine both approaches using Reciprocal Rank Fusion to get the best of both worlds.

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

This project uses Supabase's Free tier for the database and Edge Functions. The OpenAI API costs for embedding the sample data are negligible (a few cents at most). The pg_cron job checks for embedding work every 10 seconds but does nothing when the queue is empty, so there are no meaningful ongoing costs.

Resources you used:

Supabase project (database, Edge Functions, triggers, pg_cron job, pgmq queue, stored data).
OpenAI API key (used for generating embeddings).
Local project folder with ingest.js, search.js, package.json, .env, and the supabase/ directory.

Keep everything running

No action needed. Choose this if you are still actively building on the RAG pipeline or plan to connect a frontend.

Your Supabase project stays on the Free tier. The pg_cron job checks for embedding work every 10 seconds but only invokes the Edge Function when there are actual jobs in the queue.
Your OpenAI API key remains active. No charges accrue unless you run the ingestion or search scripts again.
Your local project files stay on your machine ready for future development.

Pause - I'll come back to this later

Free up resources without losing your work. Supabase Free tier projects automatically pause after 7 days of inactivity, but you can also disable the cron job manually.

In the Supabase Dashboard SQL Editor, run this to stop the embedding cron job:

select cron.unschedule('process-embeddings');

Your database, Edge Functions, and data all remain intact. When you come back, re-enable the cron job by running the original cron.schedule statement from your migration file.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is permanent.

Delete your Supabase project. In the Supabase Dashboard, open your project, go to Project Settings, scroll to the General section, and click Delete project. This removes the database, Edge Functions, triggers, cron jobs, and all stored data.
Revoke your OpenAI API key. Go to platform.openai.com, navigate to API keys, find the key you created for this project, and delete it.
Delete your local project folder by running these commands:

macOS

cd ~/Desktop
rm -rf supabase-rag-pipeline

Windows

cd ~\Desktop
rmdir /s /q supabase-rag-pipeline

Confirm the folder is gone by running ls (macOS) or dir (Windows) on your Desktop. You should no longer see supabase-rag-pipeline in the output.

Nice Work!

You just built a production-ready RAG pipeline from scratch using Supabase. Your backend automatically converts website content into searchable vector embeddings and retrieves the most relevant chunks for any natural language query.

Here's what you accomplished:

Designed a multi-content-type schema with rich chunk metadata and built an automatic embedding pipeline using Postgres triggers, pgmq, pg_cron, and Edge Functions.
Created a semantic search RPC function (match_documents) that retrieves relevant documents using cosine distance with metadata filtering.
Built a content ingestion script and a query script that prove end-to-end retrieval works: content goes in, embeddings generate automatically, and search returns relevant results with source attribution.
Secret Mission: Added hybrid search combining semantic similarity with full-text keyword matching using Reciprocal Rank Fusion scoring.

Ready to quiz yourself?