All jobs

Add Vector Search to a Supabase App

Yes. An agent enables pgvector, adds an embedding column and an RLS-respecting similarity function, writes an embedding Edge Function, adds search and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent starts with the schema and the policies, because vector search is only useful if it returns the caller's own rows. In an app like Razikus/supabase-nextjs-template, supabase/config.toml names project sasstemplate on Postgres major version 15 and exposes the public and graphql_public schemas. Four migrations exist: one creates the authenticative schema with is_user_authenticated(), a SECURITY DEFINER function that checks the JWT's aal against the user's verified MFA factors; one creates the private files storage bucket; one adds four storage policies scoped to the caller's folder; and 20250130181641_todo_list.sql creates todo_list with id, created_at, title, urgent, description, done, done_at and owner (a foreign key to auth.users), enables row level security, and adds one policy, Owner can do everything, for authenticated where owner = auth.uid() and the MFA check passes. There is no supabase/functions directory and no tests. The Next.js 15 app in nextjs/ uses @supabase/ssr and supabase-js 2.47 through SassClient in src/lib/supabase/unified.ts, which wraps getMyTodoList (a range and order query with an optional done filter), createTask, removeTask and updateAsDone; src/lib/types.ts is the generated Database type with an empty Functions map; src/app/app/table/page.tsx renders the tasks with a create dialog and three filter buttons; .env.template lists the public URL and anon key plus PRIVATE_SUPABASE_SERVICE_KEY for the admin client. The README applies migrations with npx supabase link and npx supabase migrations up --linked.

The migration the agent writes does four things in one file: create extension if not exists vector with schema extensions, an embedding extensions.vector(384) column on todo_list, an HNSW index with vector_cosine_ops on it, and a function search_todos(query_embedding extensions.vector(384), match_count int) that returns setof todo_list, written as language sql stable security invoker with where owner = auth.uid() and embedding is not null order by embedding <=> query_embedding limit match_count. Invoker rights matter: the function runs under the caller's role, so the existing Owner can do everything policy still applies and a user cannot search another user's tasks even if the where clause were wrong. Execute is granted to authenticated and revoked from anon. Then supabase gen types typescript regenerates src/lib/types.ts, and Functions gains search_todos with typed arguments.

Embeddings come from an Edge Function, supabase/functions/embed/index.ts, because the model has to run somewhere the service key never reaches the browser. It builds new Supabase.ai.Session('gte-small'), whose vectors have 384 dimensions, which is why the column is sized that way, and it creates its Supabase client with the caller's Authorization header forwarded, so every update it makes goes through RLS as that user. It handles two actions: task, which takes an id, embeds the title and description together and writes the vector back, and query, which embeds free text and returns the vector. config.toml gets a [functions.embed] section with JWT verification on. SassClient gains embedTask(id) and searchTasks(text, count), the first calling functions.invoke('embed'), the second invoking the query action and then .rpc('search_todos', ...); createTask calls embedTask after a successful insert, and editing a title or description would do the same, though the template has no edit path today. The table page gets a search input above the filter buttons; while it has text, the list shows searchTasks results in similarity order instead of the filtered query.

Proof needs a database, and the sandbox has no Docker, so the agent installs it with sudo apt-get install -y docker.io, starts dockerd, and runs supabase start followed by supabase db reset, which applies all five migrations in order. It writes a pgTAP test in supabase/tests/vector_search.test.sql that inserts two tasks for two users with fixed vectors, sets the role and JWT claims for one of them, and asserts search_todos returns that user's row and never the other's; supabase test db runs it. supabase functions serve runs the Edge Function locally so the agent can embed a task and query it end to end, and in nextjs/ it runs yarn lint and yarn build so the new client methods type-check against the regenerated Database.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add semantic search over the todo_list table in this Supabase project. Read supabase/config.toml, every migration, nextjs/src/lib/supabase/unified.ts, client.ts and server.ts, nextjs/src/lib/types.ts, the table page under nextjs/src/app/app/table and .env.template first. Write one migration that enables the vector extension in the extensions schema, adds a 384-dimension embedding column with an HNSW cosine index, and defines a search_todos function returning setof todo_list as a security invoker SQL function filtered to the caller's rows, with execute granted to authenticated only. Write an Edge Function named embed using the built-in gte-small session that forwards the caller's Authorization header, with one action that embeds a task by id and writes the vector back and another that embeds query text; register it in config.toml with JWT verification. Regenerate the TypeScript types, add embedTask and searchTasks to the client wrapper, embed after creating a task, and add a search box to the table page that shows results in similarity order. Install Docker in the sandbox, start the local stack, reset the database, write a pgTAP test proving one user cannot see another user's rows through the function, serve the Edge Function and exercise it, run lint and build in nextjs, and open a pull request listing what each check reported.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request that adds one migration, supabase/functions/embed/index.ts, supabase/tests/vector_search.test.sql and a [functions.embed] section in config.toml, regenerates nextjs/src/lib/types.ts, and edits unified.ts and the table page. The description explains why the function is security invoker, why the column has 384 dimensions, and what the pgTAP test proves.

The run's record keeps the supabase db reset output showing the migration applied, the supabase test db result, the local Edge Function calls with their responses, and the yarn build output from the Next.js app.

Before merging, a reviewer reads the function body for the owner = auth.uid() clause and the grants, confirms the Edge Function forwards the user's token rather than using the service key, and after deploying runs supabase functions deploy embed and supabase migrations up --linked as the README already prescribes, then searches from the table page. Existing rows have no embedding until something embeds them, so the description includes a one-off script that embeds every row without one.

Run it on every Linear issue labelled search

A team that triages in Linear adds an automation with a linear trigger on linear.issue.labeled for the label search, the Project that holds this repository, and this prompt. Labelling the issue that asks for semantic search starts one run; the issue's text is added after the prompt, so a request to search a different table or to return more fields reaches the agent as the reporter wrote it.

Nothing in the prompt is filled in by the trigger, so the same automation serves the next search issue, a typo-tolerant title match or a filter by date, as long as the issue says what to search and who may see what.

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

Why run it on Rush?

Vector search touches the four layers that are easiest to get subtly wrong together: an extension, a function whose security mode decides whether row level security still applies, an Edge Function that must act as the user and not as the service role, and typed client code. The agent builds all four in its own sandbox with a real local Postgres and a real local Edge runtime, and proves the isolation property with a test that stays in the repository.

The pull request carries that proof, the migration output, the pgTAP result and the local function calls, so the reviewer's question is not whether it works but whether the policy reads the way they want, which is a question a human should answer.

Frequently asked questions

Why is search_todos a security invoker function?

A SECURITY DEFINER function runs as its owner and bypasses row level security, which would make the where owner = auth.uid() clause the only thing between one user and another's tasks. Invoker rights keep the existing Owner can do everything policy in force inside the function, so two independent checks guard the rows.

Why gte-small and 384 dimensions?

The Edge runtime ships gte-small as a built-in session, so embedding needs no external API key and runs locally under supabase functions serve for the test. Its vectors have 384 dimensions, so the column and the function argument are declared at that size; switching models later means a new migration that changes the column width and a re-embed of every row.

What about tasks that exist before this change?

They have a null embedding and the function skips them with embedding is not null, so search works for new tasks immediately. The pull request includes a one-off script that calls the Edge Function for each row without an embedding, to run once after deploying.

Does the sandbox really run Postgres and the Edge runtime?

It has no Docker by default, so the agent installs the docker.io package and starts the daemon before supabase start, and docker-compose is not needed because the Supabase CLI drives the containers itself. The migration, the pgTAP test and the function calls in the run's record all come from that local stack.

More Supabase jobs

More jobs like this: Turn a Linear label into a PR

Keep reading