Your RAG Agent Is Quoting Deleted Documents

Your AI agent just quoted a contract to a customer. That contract was terminated six weeks ago and deleted from Postgres the same day. Its embedding wasn't. It was still sitting in a separate vector database, and nobody owned the sync job.

▶️ Watch the video: https://www.youtube.com/watch?v=HlT40sMLex0

Two databases, one sync job

Most RAG tutorials start the same way: documents live in Postgres, and their embeddings go to a dedicated vector database. Two systems means a sync job. Every insert, update and delete has to reach both, in order, or your agent retrieves things that no longer exist.

It also means two security models. Postgres knows this user can only see their own company's contracts. The vector database doesn't, so permissions get copied into metadata filters, and the copies drift. And every question pays two round trips: one to find similar chunks, another to fetch the real rows.

For most enterprise workloads there's a simpler answer: keep the embeddings next to the data they describe.

One database: data and embeddings

One database: data + embeddings

One Postgres database holds the documents, the tenants, the permissions and, with the pgvector extension, the embeddings. Same rows, same transactions, same backups.

When a document changes, the change and an outbox event commit together. An embedding worker picks up the event, calls the embedding model and writes the vectors back. It's the same queue-and-worker pattern from the previous article on AI middleware.

Delete a document and its embeddings go with it; there's nothing left to orphan. At query time the RAG API runs one SQL query that applies similarity, business filters and permissions together. Row-level security covers vectors exactly like it covers everything else. The best chunks then go to the LLM as context.

The schema

Store chunks, not one embedding per document. A 40-page contract has to be split, or the one paragraph that matters gets averaged away.

Each chunk references its document with ON DELETE CASCADE. Delete the contract, and every chunk and every vector goes with it, in the same transaction. That's the bug from the opening, gone at the schema level.

Three more details:

The vector dimension must match your embedding model (1536 here).
An HNSW index with cosine distance. Without it, every query scans every row.
Row-level security, so every query on this table, vector search included, only sees the current tenant's rows.

-- schema.sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE doc_chunks (
  id        bigserial PRIMARY KEY,
  doc_id    uuid NOT NULL REFERENCES enterprise_docs(id) ON DELETE CASCADE,
  tenant_id uuid NOT NULL,
  content   text NOT NULL,
  embedding vector(1536)
);

CREATE INDEX ON doc_chunks USING hnsw (embedding vector_cosine_ops);

ALTER TABLE doc_chunks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON doc_chunks
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Retrieval in one query

Embed the question with the same model you used for the documents. Then open a transaction and set the tenant for this request. The true at the end of set_config scopes the setting to the transaction, so a pooled connection never carries one customer's tenant into the next request.

In the query, <=> is cosine distance: order by it and take the top five. Because it's plain SQL, the chunks join the business data in the same statement: only active documents, only chunks that already have an embedding. No second system, no metadata copy.

The last line applies a distance threshold. If nothing is close enough, return nothing. An agent with no context should say "I don't know", not improvise from the fifth-best match.

// retrieve.ts
export async function retrieve(tenantId: string, question: string) {
  const embedding = await embed(question);

  return db.transaction(async (tx) => {
    await tx.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);

    const { rows } = await tx.query(
      `SELECT c.content, d.title, c.embedding <=> $1 AS distance
         FROM doc_chunks c
         JOIN enterprise_docs d ON d.id = c.doc_id
        WHERE d.status = 'active' AND c.embedding IS NOT NULL
        ORDER BY c.embedding <=> $1
        LIMIT 5`,
      [toSql(embedding)],
    );

    return rows.filter((r) => r.distance < 0.35);
  });
}

Keeping embeddings fresh

When a document changes, three things happen in one transaction: the new content is saved, the old chunks are removed, and an outbox event is written. Either all of it commits or none of it does. For a few seconds the document has no chunks, so it simply isn't retrieved. Missing context is safe; stale context is not.

The worker does the slow part outside the transaction: split, embed, write back. If the document was deleted in the meantime, there's nothing to do. And the write-back checks the version, so a slow job can never overwrite a newer document with old vectors.

// reindex.ts
// 1. The change and its event commit together
await db.transaction(async (tx) => {
  await tx.query('UPDATE enterprise_docs SET content = $2, updated_at = now() WHERE id = $1',
    [id, content]);
  await tx.query('DELETE FROM doc_chunks WHERE doc_id = $1', [id]);
  await tx.query("INSERT INTO outbox (topic, doc_id) VALUES ('doc.changed', $1)", [id]);
});

// 2. The embedding worker does the slow part outside the transaction
new Worker('embeddings', async (job) => {
  const doc = await docs.get(job.data.docId);
  if (!doc) return; // deleted: its chunks went with it

  const chunks = split(doc.content, { maxTokens: 500, overlap: 50 });
  const vectors = await embedMany(chunks);

  await docs.replaceChunks(doc.id, doc.updatedAt, chunks, vectors); // skipped if the doc changed
});

Where this breaks

Filtered search. HNSW finds the nearest neighbours first and applies your filter after. With a selective filter, like one small tenant in a huge table, you ask for five results and get two. pgvector 0.8 added iterative index scans for exactly this; turn them on:

SET hnsw.iterative_scan = relaxed_order;

Index memory. HNSW is fast when the index fits in RAM. Size the instance for it, and expect index builds on millions of rows to take a while.

Exact identifiers. Ask for contract C-4471 and vector search returns contracts that look similar. Embeddings are bad at codes and SKUs. Postgres already has full-text search, so combine both rankings in one query.

Real scale. For most companies, with thousands to tens of millions of chunks, Postgres is enough. At billions of vectors or thousands of queries per second, a dedicated vector database earns its cost. Choose it for that reason, not by default.

What the business gets

One system of record for data and embeddings: one backup, one audit trail, and one access model your security team has already reviewed.
No sync job, so no orphaned vectors and no agent quoting deleted documents.
One query per question instead of two round trips, and one less vendor to contract, secure and monitor.

You don't need a new database to build RAG. You need the one you already have, used properly.

The numbers in the video are targets for an illustrative scenario, not measurements. The code here is simplified; the full version is in the repository.

▶️ Watch the video: https://www.youtube.com/watch?v=HlT40sMLex0

💻 Full code: https://github.com/NodeNeuralAI/examples/tree/main/v2-rag-pgvector

🔔 More enterprise AI architecture every week: https://www.youtube.com/@NodeNeuralAI?sub_confirmation=1