When We Finally Added RAG — and Why pgvector, Not Pinecone

Five weeks after we published a post titled “Do You Need RAG for Your AI Agent? Most Don’t,” we shipped RAG. The v0.6.0 release of the AI Agent Starter Kit added pgvector-backed document retrieval. We owe you the reason that isn’t a contradiction.

The earlier post made a narrow argument: most SaaS agents don’t need a vector database, because their data is structured and reachable by tool calls, and the rest of their context fits in a 1M-token window. That argument still holds. What we added in v0.6.0 sits on the other side of a line we drew in that same post — the line where uploaded, unstructured, frequently-changing corpora stop fitting in memory and context. This post is about exactly where that line is, why pgvector was the right tool once we crossed it, and the build that made retrieval feel boring instead of like a second database to babysit.

What Changed: We Shipped RAG in v0.6.0

The feature that triggered it was document upload. People building on the starter kit kept asking for the same thing: let a customer drop a folder of PDFs, contracts, internal wikis, or product manuals into the app, and have the agent answer questions from them. Not the builder’s own docs baked in at deploy time — the end user’s documents, uploaded at runtime, different for every organization on the platform.

That request breaks the file-based memory pattern in three specific ways. The corpus is large: a single customer might upload hundreds of documents totaling far more than any context window holds, and listing every file in an index stops being useful when nobody knows which file has the answer. The corpus is not fresh in the agent’s terms: it’s a body of reference material that changes on the user’s schedule, not summarized learnings the agent curates as it goes. And it’s per-tenant by definition: org A’s documents must never surface in org B’s answers, which means retrieval and access control have to be the same operation.

File reads against a markdown index handle an agent’s working knowledge of a user. They do not handle “search 400 uploaded PDFs you’ve never seen for the paragraph that answers this question.” That’s the job RAG was built for, and it’s the job we shipped in v0.6.0. The honest version of our earlier claim was never “RAG is never needed” — it was “don’t reach for it by default.” Document upload is the case that earns it.

When RAG Earns Its Place

The decision rule we published on May 10, 2026 is the same one we used to scope v0.6.0. Nothing about it changed; we just landed on the other side of it. Here it is restated against what we actually built.

Skip RAG when a 1M-token window plus tool calls plus summaries suffice. If the agent works over your own structured tables, a SELECT beats a similarity search every time — it’s exact, it’s fresh the instant a row is written, and it inherits your database’s access controls. For the unstructured parts (instructions, conventions, accumulated learnings, conversation summaries), file-based memory and a large context window carry the load. Anthropic’s context-engineering guidance names the failure mode that makes people over-retrieve in the first place: “context rot,” where a model’s recall degrades as its context fills (Anthropic 2025). Retrieving aggressively makes that worse, not better.

Reach for RAG when one of three things is true. First, big uploaded corpora: tens to hundreds of documents per tenant whose titles don’t tell you what’s inside, so exact lookup and index-listing both fail. Second, freshness on the user’s schedule: a reference set that the customer updates, re-uploads, and reorganizes, where you need new content searchable as soon as it lands. Third, per-tenant isolation over that content, where retrieval and authorization must be one query. Document upload hits all three at once, which is why it, and not some abstract scale milestone, is what moved us. RAG’s pull here is not hypothetical: it’s the second-most-common enterprise customization technique in Menlo Ventures’ 2025 enterprise AI report (Menlo Ventures 2025), behind only fine-tuning-adjacent approaches.

If your use case doesn’t hit one of those three, the earlier post still applies in full, and you should read it before adding a vector column to your schema.

Why pgvector, Not Pinecone

Once we’d decided to add retrieval, the question was where the vectors live. The starter kit already runs Postgres for everything else — users, organizations, billing, jobs. Adding Pinecone would have meant a second datastore to provision, secure, back up, and keep in sync with the rows it describes. pgvector is a Postgres extension: vectors become a column type, similarity search becomes an operator in a normal SQL query, and the embeddings sit in the same database, the same transaction, and the same backup as the documents they belong to.

The data backs the instinct. pgvector is the most-used vector database in Retool’s 2025 State of AI report at 21.3%, ahead of every dedicated vector store (Retool State of AI 2025). Among the people most likely to be building exactly what the starter kit targets — agent builders — the Stack Overflow 2025 survey puts pgvector at 17.9% versus Pinecone’s 11.2%, with Postgres the most-used database overall at 55.6% (Stack Overflow 2025). The default datastore and the default vector store are the same system. That’s not a coincidence; it’s teams declining to operate two databases when one will do.

The market is moving the same direction. In May 2025 Databricks agreed to acquire Neon, the serverless Postgres company, for around $1 billion, noting that more than 80% of the databases provisioned on Neon were created automatically by AI agents (Databricks 2025). When agents pick their own infrastructure, they pick Postgres. Choosing pgvector kept the starter kit on one database and on the path the rest of the ecosystem is already walking.

The Build — Copy-Pasteable

Here’s the whole thing, minus the framework glue: a Prisma model with a vector column and an index, a parameterized cosine query scoped by tenant, the AI tool the agent calls, and the background pipeline that gets documents into the table. It’s deliberately minimal — this is the shape that shipped, not a maximal reference architecture.

The Prisma model

One row per chunk. The embedding column uses Prisma’s Unsupported type because vector(1536) isn’t a native Prisma type; the dimension matches text-embedding-3-small. The composite index on organizationId keeps the tenant filter fast, and the ivfflat index on the embedding (added via raw SQL migration, since Prisma won’t emit it) makes the similarity sort scale.

// prisma/schema.prisma
model DocumentChunk {
  id             String                       @id @default(cuid())
  organizationId String
  documentId     String
  content        String                       @db.Text
  // pgvector column; dimension matches text-embedding-3-small
  embedding      Unsupported("vector(1536)")?
  createdAt      DateTime                     @default(now())

  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  document       Document     @relation(fields: [documentId], references: [id], onDelete: Cascade)

  @@index([organizationId])
  @@index([documentId])
}

// migration.sql (raw — Prisma can't emit pgvector indexes)
// CREATE EXTENSION IF NOT EXISTS vector;
// CREATE INDEX document_chunk_embedding_idx
//   ON "DocumentChunk" USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

The retrieval query

This is the only place raw SQL is necessary, because pgvector’s <=> cosine-distance operator has no Prisma abstraction. Every value is a parameter, so there’s no string interpolation and no injection surface. The organizationId filter runs before the similarity sort, which is what makes retrieval and tenant isolation a single operation. We return a normalized similarity score (1 - distance) so the caller can threshold on it.

// src/lib/rag/search.ts
import { prisma } from "@/lib/prisma";

export async function retrieveChunks(orgId: string, queryEmbedding: number[]) {
  const vec = `[${queryEmbedding.join(",")}]`;

  return prisma.$queryRaw<Array<{ content: string; score: number }>>`
    SELECT content, 1 - (embedding <=> ${vec}::vector) AS score
    FROM "DocumentChunk"
    WHERE "organizationId" = ${orgId}
    ORDER BY embedding <=> ${vec}::vector
    LIMIT 5
  `;
}

The searchKnowledgeBase tool

This is what the agent actually calls. It embeds the user’s query with the same model used at ingest time, runs the scoped retrieval, and hands back the top chunks. If retrieval comes back empty, it returns a sentinel string instead of fabricating context — the agent is instructed to say it has no answer rather than guess. The orgId is bound from the authenticated session by the caller, never taken from model output.

// src/lib/ai/tools/searchKnowledgeBase.ts
import { tool } from "ai";
import { z } from "zod";
import { embedText } from "@/lib/rag/embed";
import { retrieveChunks } from "@/lib/rag/search";

export const searchKnowledgeBase = (orgId: string) =>
  tool({
    description: "Search the organization's uploaded documents for relevant passages.",
    inputSchema: z.object({ query: z.string() }),
    execute: async ({ query }) => {
      const [vec] = await embedText([query]); // text-embedding-3-small
      const chunks = await retrieveChunks(orgId, vec);
      if (chunks.length === 0) {
        return { result: "NO_RELEVANT_DOCUMENTS" };
      }
      return { passages: chunks.map((c) => c.content) };
    },
  });

The Inngest ingest pipeline

Ingestion is a background job, not a request handler, because parsing and embedding a large upload is slow and needs retries. Each step is a durable, separately-retried unit: load the file from object storage, parse it to text, chunk it, embed the chunks in a batch, then persist. If the embed step fails on a flaky API call, Inngest retries that step alone without re-parsing the document.

// src/inngest/ingestDocument.ts
import { inngest } from "@/inngest/client";
import { loadFile, parseToText, chunkText } from "@/lib/rag/ingest";
import { embedText } from "@/lib/rag/embed";
import { prisma } from "@/lib/prisma";

export const ingestDocument = inngest.createFunction(
  { id: "ingest-document" },
  { event: "document/uploaded" },
  async ({ event, step }) => {
    const { documentId, organizationId, storageKey } = event.data;

    const file   = await step.run("load",  () => loadFile(storageKey));
    const text   = await step.run("parse", () => parseToText(file));
    const chunks = await step.run("chunk", () => chunkText(text));
    const vectors = await step.run("embed", () => embedText(chunks));

    await step.run("persist", () =>
      prisma.documentChunk.createMany({
        data: chunks.map((content, i) => ({
          organizationId,
          documentId,
          content,
          embedding: vectors[i],
        })),
      })
    );
  }
);

That’s the entire surface: one table, one query, one tool, one job. The full version with file-type parsers, chunk overlap, and an admin upload UI is documented in the Knowledge Base guide, but the skeleton above is what makes the rest legible.

Keeping It Honest

RAG fails quietly when nobody designs for its failure modes, so three constraints are baked into the build above rather than left as advice.

Empty results mean “no answer.” When retrieveChunks returns nothing above the result set, the tool returns NO_RELEVANT_DOCUMENTS and the system prompt instructs the agent to tell the user it couldn’t find anything in their documents. The worst RAG behavior is confident invention from an empty retrieval; we’d rather the agent admit the gap. This is the retrieval-side version of the same discipline the file-based memory post applied to writes.

Per-org isolation is pinned by a test. The WHERE "organizationId" = ${orgId} clause is the entire tenant boundary, which makes it exactly the kind of line a future refactor can delete without anyone noticing — until org B sees org A’s contract. So there’s a test that seeds two organizations, embeds a distinctive sentence into each, and asserts that a query from org A never returns org B’s chunk. It’s a few lines, and it’s the difference between “we filter by tenant” as a claim and as a guarantee. Context engineering is partly about controlling what can never enter the window, and this is that control made executable.

Embeddings are decoupled from the chat model. Documents are embedded with text-embedding-3-small, and that choice is independent of whichever LLM answers the question. The payoff is operational: you can swap the answering model — cheaper, faster, newer — without re-embedding a single document, because the vectors don’t depend on it. The two responsibilities, “turn text into a searchable vector” and “write the answer,” stay separate so they can change separately.

When Not to Reach for RAG

Shipping RAG didn’t change our advice for the starting line: don’t reach for it first. The sequence that holds up in production is to build the agent with tool calls against your structured data, carry the unstructured context in file-based memory and a large window, and watch how it actually fails before adding infrastructure. Add retrieval when a specific corpus demands it — uploaded documents, a public knowledge base where users phrase the same question fifty ways, a reference set that changes on someone else’s schedule — and not as a reflex because every tutorial opens with a vector database.

If you adopt the starter kit, the knowledge base is there when you need it and inert when you don’t. An agent that never sees an uploaded document never touches the DocumentChunk table; the cost is a migration you don’t run. That’s the right default: retrieval available, not assumed. For where this fits among the model, framework, memory, and jobs layers, see the full AI agent SaaS tech stack, and to start from a working agent, the AI Agent Starter Kit ships all of it wired together.

Frequently Asked Questions

Have more questions? See our full FAQ →