Add AI search to existing application

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

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 relevant metadata 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.

Introduction

I have a
super simple todo app
written in Next.js connected to a Postgres database.

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.

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.

Run the demo yourself

If you would rather try the app than read about it, both versions are on GitHub:

• starting-point: the plain todo app, substring search and all. Start here.
• with-ai-search: the same app after the three changes below.

To run either branch on your machine you only need two things: a Postgres
database with pgvector support and an OpenAI API key. Put them in .env as
DATABASE_URL and OPENAI_API_KEY, install the dependencies, and start the
app.

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)

Think of those numbers as a location on a map. On a real map, two places with
similar coordinates are close to each other. Same idea here, except this map has
1536 directions instead of two. You cannot picture that, and you do not need to.
Only the rule matters: text that means similar things ends up close
together.

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.sql

-- 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);

Postgres is not the only place you can keep vectors, it just happens to be
where my app already lived. I use Supabase, which is free and ships pgvector
out of the box. If you run Postgres yourself, note that
docker run postgres:17 does not include the extension; use
pgvector/pgvector:pg17 instead.
Where to store embeddings
compares the other options in my notes.

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

• Create a Supabase project and enable pgvector: the path I took.
• Run Postgres with pgvector locally using Docker: if you would rather not sign up for anything.

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:

• You are adding this column to a table that already has rows, and there is no sensible default vector for them.
• When a todo is created, the row is written first and the embedding is filled in a moment later. A todo is briefly embedding-less, or indefinitely so if the provider is down.

Step 2: Turn text into numbers

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

// src/lib/embeddings.ts
const 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.

You do not have to use OpenAI. Any embedding model works, as long as it takes
text and gives back numbers. Google, Voyage, Cohere, or a model running locally
through Ollama all fit in the same function.
The alternatives and what changes when you switch
is its own note.

One small detail: pgvector accepts a vector written as a plain JSON array, so
sending one from your code is just the array turned into a string, with an
explicit ::vector cast in the query. In the snippets below that is the
toVector helper.

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
import {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.

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
import {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?

• 0: same direction, effectively the same meaning.
• 1: at right angles, no relationship.
• 2: opposite directions.

So order by embedding <=> $query is literally "closest first", and
1 - distance flips it into a similarity, where 1 is a perfect match and 0
is unrelated. That is friendlier to show and to reason about. (Since distance can
in theory reach 2, the similarity can in theory go negative, but with real
embeddings you will not see it.)

Notice what is not in that query: no model, no prompt, no API call. The one API
call already happened — that embed(q) above. 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 result

Remember the two searches that returned nothing at the start of the post? Here
they are again, on the same todo list, with the old search and the new one side
by side:

# keyword: does the title contain these letters?
"groceries"  ->  (nothing)
"cleaning"   ->  (nothing)

# AI: which titles mean something close to this? (1 = identical meaning)
"groceries"  ->  Purchase some apples    0.71
                 Buy bread and eggs      0.68

"cleaning"   ->  Do the dishes           0.66
                 Laundry day             0.64

The letters still do not match. groceries is nowhere in "Purchase some
apples". But the two sit close together on that map, so the row comes back
anyway, with a number telling you how close:

Keyword search is not obsolete

Notice that I did not replace the old search. 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. The toggle is the feature, and that is not
just for the demo.

Vector search is bad at exact terms such as ticket IDs, product codes, names,
acronyms, anything rare. If we search for TODO-1234, it will 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.

One small detail: 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.

Backfill the rows that have no embedding

The search query skips rows where embedding is null, so a todo without an
embedding is invisible to AI search. Two things put rows in that state:

• Rows that already existed when you added the column.
• Rows whose embedding call failed after creation.

Both are fixed the same way: select the rows with a null embedding, embed them,
write the vectors back. Here it is as an API route, using an embedMany variant
of the earlier embed function that sends an array in one request:

// src/app/api/todos/backfill/route.ts
import {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}

The route is just the easiest trigger. A one-off script, a cron job, a queue
worker, or a button in your admin page do the same job. The limit keeps one
run bounded, so call it until it returns 0.

You will run this again when you change models

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 stored vector becomes stale. Nothing errors,
because the old vectors still look like valid numbers. They are simply no longer
comparable to the vectors your new queries produce, so search quietly gets
worse.

So write the backfill as something you can run again, not as a one-time
migration.

What this costs

Backfilling is the first time you send a lot of text to the model at once, so
this is the right moment to talk about the bill.

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.

Caveats before you ship this

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

• Embedding failures are silent. If the provider is down, the todo is still created. It just never shows up in AI search, and nothing tells you.
• The backfill endpoint is unauthenticated. Anyone who finds it can spend your API credits. Do not deploy it as-is.
• There is no index on the vector column. Fine at demo scale, since Postgres just scans every row. Once the table gets big, search gets slow and you will need a vector index.
• Titles are never re-embedded. Nothing can edit a title in my app. If yours can, re-embed on update, or the vector will describe text that no longer exists.
• 0.6 is tuned to my data, not yours. I also have no real way to tell if a change made search better. A handful of test queries with expected results would fix that.
• One todo fits in one embedding. If you are embedding long text, like documents or articles, you have to split it into chunks first, and picking how to split is most of the work.
• There is no "only my todos" filter. Once you add an index and a where user_id = ..., filtered vector search gets tricky, because the index finds the nearest rows globally and your filter then throws most of them away.

Conclusion

The demo app went from "search only finds words you already typed correctly" to
"search finds what you meant" with one migration, one embedding call on insert,
and one order by distance query. No new service, no separate search engine, no
rewrite. The database you already have does the hard part.

The piece worth keeping in your head is that the model is doing one job: turning
text into a position on a map. Everything else in this post is plumbing around
that. Once the numbers are in a column, groceries finding "Purchase some
apples" is just arithmetic.

What's next

• Hybrid search. Merge keyword and vector results so the user never picks a mode.
• Structured output. A chat model plus a JSON schema turns "plan a birthday party" into a real array of todos. Show them for review before inserting.
• Retrieval Augmented Generation (RAG). Embed the question, pull the closest todos, and let a chat model answer from those. It is search plus a prompt, so good search comes first.
• Classification. Label each todo as groceries, chores, or work on creation, and get plain filters that are easier to debug than distances.