Your AI Is Retrieving Documents You Already Deleted

• Book: AI That Reads
• The series: AI in TypeScript — 5 books, from your first LLM call to agents in production — all five here
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

---

Someone deletes an internal doc — an old pricing sheet, a policy that changed,
a page a customer asked to have removed. It disappears from the app. Everyone
moves on.

Weeks later your AI assistant quotes it.

The delete removed the source of truth and left the derived copy behind. That
gap is structural in every RAG system, because indexing is a pipeline and
pipelines have no automatic reverse.

Where the copies are

By the time a document is answerable, its content exists in more places than
people track:

• the source row or file
• the chunk rows, with text duplicated for display
• the vector index
• the embedding cache, keyed by content hash
• any answer already stored in a conversation history

A delete usually removes the first. Sometimes the second. Almost never the
third through fifth.

Soft-delete is what makes it silent

await db.document.update({
  where: { id },
  data: { deletedAt: new Date() },
});

Perfectly standard, and the reason the assistant keeps answering: the chunk
rows still exist with their vectors, and the retrieval query never learned to
care.

// still returns chunks of the deleted document
ORDER BY embedding <=> $1 LIMIT 10

The immediate fix is a join and a filter, which is also the reason to keep
chunks in the same database as documents where you can:

SELECT c.id, c.text, c.doc_id
FROM chunks c
JOIN documents d ON d.id = c.doc_id
WHERE d.deleted_at IS NULL
  AND d.tenant_id = $2
ORDER BY c.embedding <=> $1
LIMIT $3;

If your vectors live in a separate store, you cannot join, and that is the
real argument for keeping them in Postgres alongside the data they describe.
Otherwise deletion correctness becomes a distributed-systems problem you have
to solve by hand.

Delete through one path

The usual cause of drift is two code paths: the app deletes a document, and
some other job deletes chunks, and only one of them runs.

Give deletion a single owner:

export async function deleteDocument(id: string, scope: Scope) {
  await db.$transaction(async (tx) => {
    await tx.chunk.deleteMany({ where: { docId: id, tenantId: scope.tenantId } });
    await tx.document.update({
      where: { id, tenantId: scope.tenantId },
      data: { deletedAt: new Date(), contentHash: null },
    });
    await tx.outbox.create({
      data: { kind: "document.deleted", payload: { id }, tenantId: scope.tenantId },
    });
  });
}

Chunks are hard-deleted in the same transaction as the soft-delete of the
document. The outbox row is what lets anything outside the database — a cache,
an external index — catch up without a second best-effort call that can fail
silently.

Never delete chunks in an afterResponse hook or a fire-and-forget promise.
If it is not in the transaction, it does not happen reliably.

Reconcile, because the transaction is not enough

Processes crash between statements. Migrations bypass application code.
Somebody runs a manual DELETE. You need a job that finds drift rather than
assuming it cannot happen.

export async function reconcile(tenantId: string) {
  const orphans = await db.$queryRaw<{ id: string }[]>`
    SELECT c.id FROM chunks c
    LEFT JOIN documents d ON d.id = c.doc_id
    WHERE c.tenant_id = ${tenantId}
      AND (d.id IS NULL OR d.deleted_at IS NOT NULL)
    LIMIT 5000`;

  if (orphans.length) {
    await db.chunk.deleteMany({ where: { id: { in: orphans.map((o) => o.id) } } });
    logger.warn("reconcile: removed orphan chunks", {
      tenantId, count: orphans.length,
    });
  }

  const missing = await db.$queryRaw<{ id: string }[]>`
    SELECT d.id FROM documents d
    LEFT JOIN chunks c ON c.doc_id = d.id
    WHERE d.tenant_id = ${tenantId}
      AND d.deleted_at IS NULL
      AND c.id IS NULL`;

  for (const d of missing) await enqueueReindex(d.id);
  return { orphans: orphans.length, missing: missing.length };
}

Both directions. Orphan chunks are the privacy problem; missing chunks are the
"why doesn't it know about this page" problem, and the same query finds them.

Emit orphans as a metric. It should sit at zero. Any sustained non-zero
value means a delete path is bypassing deleteDocument, and you want to know
which before it matters.

Right-to-erasure needs hard deletion

If a delete exists to satisfy a data-subject request, soft-delete is not
enough. Chunk rows carry a copy of the text, so the content survives your
deletedAt flag.

export async function eraseDocument(id: string, scope: Scope) {
  await db.$transaction(async (tx) => {
    await tx.chunk.deleteMany({ where: { docId: id } });
    await tx.document.delete({ where: { id } });
    await tx.embeddingCache.deleteMany({ where: { docId: id } });
  });
  await purgeFromConversations(id);   // stored answers quoting this source
  await purgeBackups(id);             // whatever your retention policy allows
}

The last two lines are the ones people forget. A stored assistant message that
quoted the document still contains the text, and it will be shown again the
next time that conversation is opened.

Keeping a sourceRefs column on stored messages is what makes that query
possible at all — without it, finding which past answers used a document means
scanning text.

Check for staleness, not just existence

A document that changed is a subtler version of the same problem: the chunks
describe the old content.

const stale = await db.$queryRaw`
  SELECT d.id FROM documents d
  JOIN chunks c ON c.doc_id = d.id
  WHERE d.deleted_at IS NULL
  GROUP BY d.id, d.content_hash
  HAVING max(c.source_hash) IS DISTINCT FROM d.content_hash`;

Storing source_hash on each chunk — the hash of the document version it came
from — turns "is my index current" into one query. Without it, the only honest
answer is a full re-embed.

The test

it("stops retrieving a document after deletion", async () => {
  const doc = await seed("the legacy pricing tier is 40 euros");
  expect(await search("legacy pricing", scope)).not.toHaveLength(0);

  await deleteDocument(doc.id, scope);

  const after = await search("legacy pricing", scope);
  expect(after).toHaveLength(0);
  expect(JSON.stringify(after)).not.toMatch(/40 euros/);
});

Assert on the text, not only the count. A filter that hides the chunk from
the result list but leaves it reachable through a citation resolver passes a
count assertion and fails this one.

The summary

One deletion path, inside a transaction, that removes chunks rather than
flagging them. A retrieval query that joins to documents. A reconciliation job
in both directions with a metric at zero. A separate erase path for legal
deletion that also reaches stored answers.

The pipeline that got content into the index does not run backwards on its
own, and the failure mode is an assistant confidently quoting something that
officially no longer exists.

---

If this was useful

AI That Reads covers the lifecycle of
a RAG corpus — ingest, update, delete, reconcile, and the provenance that
makes each of those answerable rather than approximate.

The full series is at
xgabriel.com/ai-in-typescript.

Comments

[Cenk KURTOĞLU]

The reconcile-in-both-directions point is the one I would put on a wall. Two things I would add, one about the retrieval query and one that is a genuine bug in the staleness check.

1. max(c.source_hash) misses partially stale documents.

HAVING max(c.source_hash) IS DISTINCT FROM d.content_hash

max() on a hash is lexicographic, and it collapses a set to one value. A document whose chunks carry ['3f9a...', 'b21e...'] while content_hash = 'b21e...' produces max = 'b21e...', compares equal, and is reported current — even though the first chunk is from the old version. That is exactly the state a partially failed re-embed leaves behind, which is the case the query exists to catch.

bool_and says what you actually mean:

HAVING bool_and(c.source_hash = d.content_hash) IS NOT TRUE

IS NOT TRUE rather than = false so a NULL source_hash on a chunk that predates the column also flags, instead of vanishing into three-valued logic.

2. The join filter runs after the ANN search, not inside it.

ORDER BY c.embedding <=> $1 LIMIT $3

With an HNSW or IVFFlat index this is a post-filter. The index walks its candidate list by distance, Postgres then drops the rows failing d.deleted_at IS NULL AND d.tenant_id = $2, and you are left with whatever survives — frequently fewer than $3, sometimes zero, while perfectly good chunks sit just past the candidate horizon.

This gets worse exactly as your post succeeds: a tenant with 2% of the corpus has ~2% of any candidate set, so after filtering, a LIMIT 10 can return two rows. The assistant does not error. It answers from a thinner context and sounds just as confident, which is the same silent-degradation shape as the deleted-document problem.

Three ways out, in increasing order of effort:

-- pgvector 0.8+, lets the index keep walking until the filter is satisfied
SET hnsw.iterative_scan = relaxed_order;

-- or a partial index per lifecycle state, so deleted rows are not candidates
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WHERE deleted_at IS NULL;

The partial index needs the flag denormalised onto chunks, which cuts against your single-owner deletion rule — though since you already hard-delete chunks in the transaction, the tenant dimension is the one that actually needs it. Per-tenant partitioning of the chunk table is the heavier version and worth it above a certain scale.

Worth measuring before choosing: log rows_returned against LIMIT. If they diverge as tenants grow, you are being filtered out of your own index.

Adjacent silence from the permissions side, if useful — the same "fewer rows than expected, no error" shape, except the filter is a policy: github.com/cekuu35/supabase-rls-le...