Add AI search to existing application

How to add semantic search to an existing app using an embedding model and pgvector

Saturday, September 12, 2026

robot search

TL;DR

Adding the most basic form of "AI search" to an existing app is three changes:

  1. Add a vector column to the table you want to search.
  2. When a row is created, send its text to an embedding model and store the numbers it returns in that column.
  3. On search, embed the search term the same way and ask the database which rows are closest.

No training. No fine-tuning. No GPU. It is one HTTP call and one order by.

Check the changes needed to add AI search

Introduction

I have a super simple todo app. A Next.js App Router frontend and a todos table in Postgres.


Aside from the usual CRUD operations, this simple todo app has search. It was the naive one everybody writes first: lowercase the query, lowercase the title, check includes. Type "apple" and you get "Purchase some apples"; type "laundry" and you get "Laundry day". It looks like it works, as long as you already know the words in the title.


Before adding AI: substring search only matches todos that literally contain the typed word

Now search that same list for "groceries". Nothing, even though "Purchase some apples" and "Buy bread and eggs" are sitting right there. Same for "cleaning" against "Laundry day" and "Do the dishes". The search is not looking at what the todos mean; it is looking at which letters they contain, and groceries is not a substring of anything.


That is the gap people reach for "AI" to fill. The surprise is how little is involved. The part that does the matching is not a model at all. It is arithmetic in your database.


If you want to follow along in code rather than read, the whole thing is on GitHub:



The diff between the two branches is, genuinely, the entire feature.


What an embedding actually is

Forget training, weights, and prompts for a minute.


An embedding model is a function. Text goes in, a fixed-length list of numbers comes out:

"Purchase some apples"  ->  [0.021, -0.043, 0.118, ... ]   (1536 numbers)"groceries"             ->  [0.019, -0.038, 0.121, ... ]   (1536 numbers)

Those numbers are coordinates. Not in 2D or 3D, but in a space with 1536 axes, which is impossible to picture and does not matter, because the rule is the same as on a map: things that mean similar things land near each other.


1536 is not a universal number. It is just the output width of the model I picked. Other models give you 768, 1024, 3072, and some let you ask for a shorter output. Whatever you pick becomes part of your schema, so treat it as a decision and not a constant.


So "Purchase some apples" sits near "groceries" and far from "renew passport". Nobody programmed that. The model was trained on a very large amount of text, and that placement is the leftover shape of the language it read.


Here is the part worth internalizing:

Once the text is numbers, matching is just measuring a distance. Your database does that. The AI ended at the point where you got the numbers back.


That is it. That is the whole trick. Everything below is plumbing.


Step 1: Add a vector column

Postgres cannot store a list of 1536 floats usefully on its own, so we use pgvector, an extension that adds a vector type and, crucially, distance operators that work in order by.

migrations/002_embeddings.sqlsql
123456-- pgvector is not part of stock Postgres. On Supabase it is available-- but not enabled until you ask for it.create extension if not exists vector;
-- 1536 is the native output width of OpenAI's text-embedding-3-small.alter table todos add column if not exists embedding vector(1536);

If you are picking a database right now, pick one where pgvector is available. docker run postgres:17 is the common trap; use pgvector/pgvector:pg17. Supabase has it out of the box, which is what I use.


Setting that up is not really part of this post, so it lives in my notes instead:



Either way you end up with a DATABASE_URL and a database that understands vector. The rest of this post does not care which one you picked.

Why the column is nullable

Two reasons, and both come up in any real app:



Remember 1536. It is the width of the model's output, and it is now part of your schema. Change the model, change the column.


Step 2: Turn text into numbers

The entire "AI dependency" is one HTTP POST. No SDK required.

src/lib/embeddings.ts
1234567891011121314151617181920212223242526const ENDPOINT = 'https://api.openai.com/v1/embeddings';
export const EMBEDDING_MODEL = 'text-embedding-3-small';
export async function embed(text: string): Promise<number[]> {  const response = await fetch(ENDPOINT, {    method: 'POST',    headers: {      'Content-Type': 'application/json',      Authorization: `Bearer ${process.env.OPENAI_API_KEY}`,    },    body: JSON.stringify({model: EMBEDDING_MODEL, input: text}),    // A hung provider must not pin a request open forever.    signal: AbortSignal.timeout(10_000),  });
  if (!response.ok) {    throw new Error(`OpenAI embeddings failed: ${response.status}`);  }
  const payload = (await response.json()) as {    data: {index: number; embedding: number[]}[];  };
  return payload.data[0].embedding;}

That is the AI in "AI search". A string in, an array of numbers out.


OPENAI_API_KEY is the only credential involved. Getting one is a five minute detour, and the API platform is billed separately from ChatGPT Plus, which trips up most people the first time.


If you do not want to use OpenAI, nothing above is load-bearing except the shape of the function. Swap in Google, Voyage, Cohere, or a model running locally through Ollama. The alternatives and what changes when you switch is its own note. The two things that move are the dimension count in your schema and the fact that every vector you have already stored becomes stale.


pgvector's wire format happens to look exactly like a JSON array, so the bridge from JavaScript to Postgres is one line plus an explicit ::vector cast at the call site:

src/lib/embeddings.ts
123export function toVector(embedding: number[]): string {  return JSON.stringify(embedding);}

Write the vector when a todo is created

The important decision here is where the call goes. Creating a todo is the core feature; embedding it is not. If you await OpenAI before inserting the row, an OpenAI outage takes down todo creation.


So the row is inserted and returned first, and the embedding is written after the response has already gone out. Next.js gives you after() for exactly this:

src/app/api/todos/route.ts
123456789101112131415161718192021222324252627282930import {NextResponse, after} from 'next/server';
import {sql} from '@/db';import {embed, toVector} from '@/lib/embeddings';
export async function POST(request: Request) {  const {title} = createTodoSchema.parse(await request.json());
  const [created] = await sql`    insert into todos (title)    values (${title})    returning id, title, completed, created_at  `;
  after(async () => {    try {      const embedding = await embed(created.title);
      await sql`        update todos        set embedding = ${toVector(embedding)}::vector        where id = ${created.id}      `;    } catch (error) {      console.error(`Failed to embed todo ${created.id}`, error);    }  });
  return NextResponse.json(created, {status: 201});}

after() is best-effort, not a queue. No retries, and if the process dies mid-callback the work is lost. That is fine for a demo and not fine for production. See the caveats at the end.

What this costs

You now make one API call per todo, so it is worth knowing the bill before you write a backfill loop.


You are billed per input token, and only on the way in. There is no output cost, because the output is a vector and not text.


For an app this size the numbers are barely real. A todo title is about 8 tokens, so embedding 10,000 of them is around 80,000 tokens, which is under a fifth of a cent on text-embedding-3-small. Searches are even smaller.


So the thing to watch is not the price per token. It is re-embedding text that did not change, embedding on every keystroke, and hitting the tokens-per-minute rate limit in the middle of a backfill. How to count tokens and estimate the bill is in my notes, including the input length cap you will hit the moment your text is longer than a todo title.


Step 3: Search with the same model

This is the symmetry that makes the whole thing work, and it is the one sentence I would want a reader to keep:

The search term goes through the exact same embedding call as the stored text. Then you ask the database which stored vectors are nearest to that one.

src/app/api/todos/search/route.ts
1234567891011121314151617181920212223242526import {NextResponse} from 'next/server';
import {sql} from '@/db';import {embed, toVector} from '@/lib/embeddings';
const MAX_DISTANCE = 0.6;const MAX_RESULTS = 20;
export async function GET(request: Request) {  const q = new URL(request.url).searchParams.get('q')?.trim();
  const queryVector = toVector(await embed(q));
  const rows = await sql`    select      id, title, completed, created_at,      1 - (embedding <=> ${queryVector}::vector) as similarity    from todos    where embedding is not null      and (embedding <=> ${queryVector}::vector) < ${MAX_DISTANCE}    order by embedding <=> ${queryVector}::vector    limit ${MAX_RESULTS}  `;
  return NextResponse.json(rows);}

Reading that query

<=> is pgvector's cosine distance operator. It answers one question about two lists of numbers: how far apart do they point?



So order by embedding <=> $query is literally "closest first", and 1 - distance gives you a similarity between 0 and 1 that is friendlier to show and to reason about.


Notice what is not in that query: no model, no prompt, no API call. By the time Postgres is involved, the AI part is over. This is ordinary maths over a column, and it is why the feature is fast and cheap to run.

Why there has to be a cutoff

Nearest-neighbour search has no concept of "no results". Ask it for the top 20 and it hands you 20 rows, however unrelated, confidently ranked. Without MAX_DISTANCE, searching for asdfgh returns your entire todo list.


0.6 is a magic number picked by eye. It depends on your data, since short todo titles behave nothing like paragraphs of prose. That is why the endpoint returns similarity on every result: run a few searches with curl, see where the useful results stop, and move the number.


The payoff

Two searches, same data, same gesture:

# keyword mode: substring test on the title"groceries"  ->  (nothing)"cleaning"   ->  (nothing)
# AI mode: same queries, through embeddings"groceries"  ->  Purchase some apples   0.71                 Buy bread and eggs     0.68
"cleaning"   ->  Do the dishes          0.66                 Laundry day            0.64

And here are the queries that came up empty at the top of the post, after the three changes. The letters still do not match, but the coordinates are close, so the rows come back ranked by how near they landed:


After adding AI: searching groceries and cleaning returns related todos ranked by similarity

I kept both modes in the UI. I added a button next to the search box that toggles "AI" search on and off, so you can run the same query both ways and see the difference.


Both modes only search when you submit, not as you type. Use whatever strategy you like here. I just did not want to fire an embedding call on every keystroke.

Keyword search is not obsolete

Notice that I did not replace the old search. The toggle is the feature, and that is not just for the demo.


Vector search is bad at exact terms. Ticket IDs, product codes, names, acronyms, anything rare. Search for TODO-4821 and it will happily return four todos that feel vaguely related and none that match. Substring search gets that right every time.


So the two are not rivals. Keyword wins on exact hits, vectors win on meaning, and keeping both is the honest setup. The usual next step is to stop making the user choose: run both and merge the results. That is called hybrid search.


The part nobody warns you about: backfilling

Your table already has rows. The migration added an empty embedding column to all of them, and the search query skips rows where embedding is null.


So every todo that existed before today will never show up in AI search. It looks like the feature is broken, but it is just missing data.


Embedding on create only covers new todos. You still need something that goes back and fills in the old ones. Here it is as an API route:

src/app/api/todos/backfill/route.ts
1234567891011121314151617181920212223242526import {NextResponse} from 'next/server';
import {sql} from '@/db';import {embedMany, toVector} from '@/lib/embeddings';
export async function POST() {  const pending = await sql`    select id, title from todos    where embedding is null    order by created_at    limit 100  `;
  // The API takes an array, so 100 todos is one round trip, not 100.  const embeddings = await embedMany(pending.map(todo => todo.title));
  for (const [index, todo] of pending.entries()) {    await sql`      update todos      set embedding = ${toVector(embeddings[index])}::vector      where id = ${todo.id}    `;  }
  return NextResponse.json({embedded: pending.length});}
curl -X POST localhost:3000/api/todos/backfill# {"embedded":7}

An API route is just the easiest way to trigger this. A one-off script, a cron job, a queue worker, or a button in your admin page all work the same. The only part that matters is the query: find rows without an embedding, embed them, save them.

This is the same endpoint you will need again

Here is the idea worth carrying to your own app: an embedding is derived data. It is a function of the text and the model that produced it.


Switch to text-embedding-3-large, shorten the output to 512 dimensions, or move to another provider, and every vector in your table is stale. They are not wrong-looking, they are just no longer comparable to the vectors your new queries produce. Search quietly gets worse and nothing errors.


The fix is to re-embed everything with the new model. That is the same backfill code you just wrote, with a different where clause:

-- backfill: only the rows that never got an embeddingwhere embedding is null
-- re-embed: every row, because the model changedwhere true

So it is worth writing the backfill in a way you can run again later. You will need it the first time you change models.


So where is the "AI"?

Worth saying plainly, because the marketing muddies it:



You called someone else's model once per todo and once per search, and it handed you coordinates. Everything that feels smart, like "Purchase some apples" answering "groceries", is a consequence of where those coordinates landed, decided long before you showed up, and surfaced by an order by.


Thinking of it as a lookup table you query with meaning instead of with keys is much closer to the truth than thinking of it as a brain, and it makes the failure modes obvious. Bad results are not the model "misunderstanding" you. They are rows in the wrong place, a cutoff set too loose, or vectors made by a model you no longer use.


Caveats before you ship this

This is a demo app, so here is what I skipped:



Conclusion

"Add AI to the app" sounds like a project. In practice it was a column, an HTTP call, and a different order by, plus one honest bit of homework in the backfill.


The useful reframe is that the model is a translator, not a brain. It turns text into coordinates; your database does the rest with arithmetic it has always been good at. Once you see it that way, most of the AI features on that list stop looking like magic and start looking like plumbing you already know how to write.


If you want to try it yourself: clone starting-point, point DATABASE_URL and OPENAI_API_KEY at your own, and see how far you get before peeking at with-ai-search.


What's next

The column is there now, so a few features get much cheaper to add. Roughly in order of effort:


Hybrid search. Run the substring search and the vector search together and merge the results, so you stop making the user pick a mode.


Generating todos with structured output. Type "plan a birthday party" and get a list of todos back. This is a different kind of AI call: a chat model instead of an embedding model, with a JSON schema so the reply is a real array you can insert instead of prose you have to parse. The gotcha is that it is a slow, expensive call that can also just be wrong, so the generated todos should land in front of the user for review before they hit the table.


Asking questions about your todos, also known as RAG. "What do I still need to buy?" Search is already the hard half of this. You embed the question, pull the closest todos out of Postgres, paste them into a prompt, and ask a chat model to answer using only those. Retrieval Augmented Generation sounds like an architecture, but it is search plus a prompt. If the search is bad, the answer is bad, which is why it is worth getting the search right first.


Auto tagging. Ask a model to label each todo as groceries, chores, work, and so on when it is created. Cheaper and more predictable than it sounds, and it gives you plain filters that are easier to debug than distances.


Every one of these follows the same shape as this post: call a model, store what it gives you, and let your database do the rest.