Keeping embeddings in step with source documents that change weekly
A logistics client runs a support portal where agents paste shipment references and get back matching SOP entries, exception notes, and regulatory clauses. The search backend is pgvector with OpenAI `text-embedding-3-small` embeddings, roughly 14,000 chunks across 2,200 source documents. The documents live in a Postgres table with a `content` column, an `updated_at` timestamp, and a foreign key the operations team uses to push edits through their CMS.
The operations team updates between 40 and 120 documents every week. Most edits are small — a revised rate threshold, a new pin code added to a service zone, a clause reworded after legal review. A few are substantial rewrites. The question is how to regenerate embeddings so the search results reflect current content without rebuilding the entire index every Monday.
The straightforward answer is to track which documents changed since the last embedding run and reprocess only those. We keep a separate `embedding_runs` table with `document_id`, `chunk_count`, `embedded_at`, and a `content_hash` for each chunk. On the weekly run, a worker selects documents where `updated_at > last_embedded_at`, deletes existing chunks for those documents, rechunks the content, and inserts new embeddings.
The content hash matters because not every CMS save changes the text meaningfully. If an editor opens a document, fixes a typo in the title, and saves, the `updated_at` moves forward but the chunks may be byte-identical. We compute SHA-256 of each chunk's text and skip re-embedding if the hash matches what is already in `embedding_runs`. On a typical week, this cuts the number of API calls from 120 documents down to 60 or 70, because the CMS fires `updated_at` even on no-op saves.
The tradeoff is chunking. If a document grows by one paragraph at the top, every subsequent chunk boundary shifts and all hashes change, even though the content is largely the same. We accept this cost. Re-embedding a 4,000-token document at `text-embedding-3-small` rates is close to nothing, and trying to detect partial changes adds complexity that does not pay for itself. The hash skip handles the common case of no-op saves; the rechunking handles the rest.
One detail worth getting right: the delete-then-insert for a changed document needs to happen in a transaction. If the worker crashes after deleting old chunks but before inserting new ones, the document disappears from search results until the next run. We wrap the per-document operation in a transaction and log failures to a `embedding_errors` table with the document ID and the error message. A failed document gets retried on the next run automatically because its `embedded_at` never updated.
For the vector index itself, we use HNSW in pgvector. Rebuilding the HNSW index after every batch of changes is not necessary. HNSW handles inserts and deletes incrementally. We rebuild the index once a quarter during a scheduled maintenance window, which keeps search latency predictable. Weekly latency stays under 15ms for the client's query volume, which is around 300 searches per day.
Explainability for the operations team means showing which version of a document produced a search result. We store `embedded_at` on each chunk and surface it in the admin panel as "indexed on" alongside the source document's `updated_at`. If an agent reports that search results do not match the document they see in the CMS, the two timestamps tell us whether the embedding run missed the update or whether the chunking logic split the content at an unhelpful boundary.
The weekly run takes about four minutes on a 2-vCPU worker. The cost is negligible against the monthly OpenAI bill, which is dominated by the chat completions used for answer synthesis rather than embeddings.
The reliable signal for whether this approach is working is simple: search results should never contain text that contradicts the current version of a source document. When that happens, the timestamps on the chunk tell you whether the problem is a missed run or a chunking issue, and the fix is a targeted re-embedding of one document rather than a full rebuild.