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

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.