Keeping a SharePoint vector index current as items change

11 October 2026

Tags: Search and vector storesAIDesktopCommand line

In March the annual leave carry-over limit went from five days to ten. The policy item in SharePoint says ten, and the internal assistant that answers HR questions still says five. Payroll noticed in May, after three people had planned holidays around the old answer.

Every component did its job. Retrieval found the carry-over policy with a high similarity score, and the language model answered confidently from text that was eight weeks old. If you run retrieval over SharePoint data, this is the failure you will meet first, and it leaves no trace in the logs.

How a RAG answer goes stale unnoticed

HR keeps its policies in a SharePoint list called Policies, with a Title, a short Summary, a Category choice column, a multi-line Body column holding the policy text, an Owner (a person column) and an Effective date. There are about 400 policies. An internal assistant answers questions like “how many days can I carry over?” by embedding the question, finding the nearest policy vectors, and handing the matching text to a language model.

Someone built the vectors once, in a script that read the list through Microsoft Graph and called an embedding model for every row. The demo went well. The list kept changing and the vectors did not.

It is hard to spot because every number you might monitor stays healthy. Retrieval returns the right policy, and the question matches the old text as well as the new. The post on tianpan.co, “The RAG freshness problem: how stale embeddings silently wreck retrieval quality”, makes the same point: staleness raises no alert, so it is found by a person who knows the right answer, usually late.

What makes embeddings go stale, and where do orphan vectors come from?

Stale embeddings are vectors computed from text that has since changed. There are three ways to get them, plus a fourth problem that behaves the same way. The tianpan.co post sets out the three kinds, calling the third chunk boundary drift.

Source drift is the one in our story. The SharePoint item changed and its vector did not. Every edit to Body or Summary widens the gap between what the assistant retrieves and what the policy says.

Model drift happens when someone upgrades the embedding model, or a provider retires the old one. Vectors from two models are not comparable, so a question embedded with the new model finds the old rows more or less at random.

Preprocessing drift is the quiet sibling of model drift. You change which columns are embedded, or the order they are joined in, or how text is chunked. Rows written before the change were embedded from different text than rows written after it.

Deletes are the fourth problem. HR withdraws the old remote working policy and deletes the item. If nothing removes its vector, it becomes an orphan vector: an embedding whose source row has gone. The assistant keeps quoting a policy that no longer exists, and this one is worse than an edit, because there is no current version in SharePoint for anyone to compare it with.

Why does a nightly full re-embed still leave you behind?

The usual first fix is a scheduled job that re-reads and re-embeds the whole list every night. On the Policies list a busy week might see a dozen edits, so over a week the job embeds thousands of unchanged rows to pick up a dozen changes. With a small embedding model the token bill for that is modest, and the job is easy to reason about. The real costs are elsewhere.

The job still leaves you a day behind. HR announced the carry-over change at ten o’clock and people asked the assistant at five past. Running the job more often multiplies the work, and every run reads the whole list through Graph against the tenant’s throttling budget.

What an incremental pipeline over SharePoint needs

The better pattern is incremental re-indexing: re-embed from changes, whenever they happen, so the vector index stays in sync with the list without a full rebuild. Pixeltable’s “Why your RAG is wrong” argues against periodic full re-indexing and for incremental updates on every insert, update and delete. The tianpan.co post recommends incremental updates too, with a weekly or monthly full reindex kept as a consistency checkpoint. For SharePoint data the incremental part takes four things.

Change detection comes first. You need to know which items changed since the last run without reading every item. Graph’s delta query on a list does this, returning changed and removed items since a stored token. You also need to know whether the change touched the text you embed: an edit to Owner should not cost an embedding call if Title, Summary and Body are the same.

Delete handling is next. To stop orphan vectors on delete, a deleted item has to stop being retrievable, either by removing its vector or by flagging it so retrieval can filter it out. A pipeline that only ever upserts never notices a delete. Model provenance is the third. Store the model name and the vector width with the vectors, so a model change is detected by the pipeline and not by a confused user. The fourth is a queryable home: a vector with no title or effective date beside it is hard to check and impossible to filter.

You can build this yourself. Many teams start with a Power Automate flow on “when an item is created or modified” that calls Azure OpenAI and writes the vector somewhere. That handles source drift for single edits well, but deletes need a second flow, a model change needs a backfill, and the flow has no record of what text produced which vector. LangChain’s indexing API with a record manager hashes documents and skips unchanged ones, which is a good design, though removing deleted items needs its full cleanup mode, and that means loading the whole list again. We have seen both run well at small scale.

How SPSync re-embeds only the rows that changed

SPSync replicates the Policies list into a database: a full load the first time, then a delta sync every five minutes by default. SPSync generates embeddings during each sync for two destinations only, Postgres with pgvector and Azure AI Search. Elasticsearch, OpenSearch, Qdrant, Weaviate and LanceDB replicate the same lists without a vector field.

You choose the text columns to embed and the embedding model. For pgvector the destination settings look like this:

destination:
  kind: pgvector
  connection_string_env: HR_ASSISTANT_DB_URL
  dataset_name: hr
  pgvector_embedding_provider: azure-openai
  pgvector_embedding_model: text-embedding-3-small   # the deployment name
  pgvector_embedding_dimensions: 1536
  pgvector_embedding_columns: [title, summary, body]
  pgvector_api_key_env: HR_AOAI_KEY
  pgvector_azure_endpoint_env: HR_AOAI_ENDPOINT

The provider can be OpenAI, Azure OpenAI, or a local model run through fastembed, which needs no API key and downloads its weights on first use. Column names are the destination names spsync discover schema shows, so Body is body. The desktop app and SPSync in your Azure show the same fields.

On each sync, after updating hr_policies, SPSync joins each live row’s configured columns, in order, and computes a SHA-256 hash of that text folded together with the model name. It compares that with the hash stored beside the row’s vector in sp_embedding_hash. Only rows whose hash differs, or that were never embedded, go to the embedding model. When HR edits the carry-over policy, its Body changes, its hash changes, and its vector is rewritten on the next sync. When someone changes the Owner, the hash is the same and nothing is sent.

Each row gets one vector, from the configured columns joined together. SPSync does not split long Body text into chunks. For a policies list that is usually fine, and if a few policies are very long, you can embed Title and Summary for the whole list and pass the full Body to the language model at answer time from the same table.

The vector sits in an sp_embedding column on the same table as the title, the category and the effective date, with an HNSW index kept current. That makes the assistant’s retrieval query ordinary SQL:

SELECT sp_item_id, title, summary, effective_date
FROM hr.hr_policies
WHERE sp_deleted_at IS NULL
ORDER BY sp_embedding <=> %(question_vector)s
LIMIT 5;

The embedding cost now follows the number of edits. A dozen edits in a week means about a dozen rows re-embedded, whether the list holds 400 policies or forty thousand. Azure AI Search works the same way: SPSync reads the stored hashes back from the index before each merge and only embeds rows whose text changed, and an unchanged row’s document is merged without touching its stored vector.

What happens to a deleted policy in pgvector and in Azure AI Search?

In Azure AI Search, a deleted item’s document is removed from the index on the next sync, along with its documents in any child indexes for multi-value columns. The withdrawn remote working policy stops coming back.

In pgvector, SPSync keeps deleted rows as it does on every SQL destination: the row stays, with its vector, and sp_deleted_at is set to when it was deleted. That keeps an audit trail, and it means every retrieval query has to filter on sp_deleted_at IS NULL. A view the assistant reads instead of the table makes that automatic:

CREATE VIEW hr.assistant_policies AS
SELECT sp_item_id, title, summary, body, effective_date, sp_embedding
FROM hr.hr_policies
WHERE sp_deleted_at IS NULL;

GRANT USAGE ON SCHEMA hr TO hr_assistant;
GRANT SELECT ON hr.assistant_policies TO hr_assistant;
GRANT SELECT ON hr.sp_embedding_meta TO hr_assistant;

Give the assistant’s database role the view and the model record, and no access to the table itself. SPSync does not carry SharePoint item permissions into the vectors, so the replica does not trim results per user at query time. In Azure AI Search each index has a filterable permitted_groups field, and SPSync leaves it empty. Row-level security and a scoped role on the replica are how you bound what the assistant may retrieve.

Changing the embedding model without mixing vectors

SPSync records the model name and the vector width it used for each embedded table in a small table called sp_embedding_meta. If you change pgvector_embedding_model to a newer model with the same width, the next sync sees that the recorded model no longer matches, treats every live row’s hash as stale, and re-embeds the whole table under the new model. The hash itself includes the model name, so no row embedded under the old model can compare as current. You pay for one full re-embed, then it is incremental again.

If the new model has a different width, say moving from the local 384-dimension default to a 1536-dimension Azure OpenAI model, pgvector cannot change a vector column’s width in place. SPSync writes the new vectors to a fresh sp_embedding_v2 column with its own hash and its own entry in sp_embedding_meta, and leaves the old column alone. Point the retrieval query at sp_embedding_v2 and recreate the view once the sync has finished. Azure AI Search does the equivalent: a change of model re-embeds every row, and a change of dimensions creates a new -v2 index for the subsequent syncs.

Changing which columns you embed is handled the same way. Add category to pgvector_embedding_columns and every row’s text changes, so every hash changes and the next sync re-embeds them all, once.

One thing stays your job. The assistant embeds each question before searching, and it must use the same model the table was embedded with. Read the model from sp_embedding_meta at start-up, so it is never hard-coded in two places.

How fresh a SharePoint vector index is between syncs

A vector index is only as current as the replica that feeds it, and the replica is as current as the sync interval. With the default of five minutes, an edit HR saves at ten o’clock is in hr_policies with a new vector by about five past. Until then the assistant answers from the previous text. For a policies list that gap does no harm. If a list changes during a conversation, a ticket status say, shorten the interval and accept more delta calls against the tenant’s throttling budget.

The reconcile, weekly by default, is the correctness check behind the delta. It compares every item id with SharePoint, and you can start one with the command-line tool whenever you want that check, after an outage on your own server or in Microsoft 365 for instance, or before a quarterly audit of the assistant’s answers. Freshness between runs still comes from the interval.

Every row carries sp_synced_at beside sp_modified, so the assistant can tell people how old its copy is. Have it state max(sp_synced_at) whenever a date matters to the answer. When someone asks about an edit they made a minute ago, the honest reply is that the copy may be one interval behind, with a pointer to the item in SharePoint. Any agent reading the replica should follow the same rule, and the agent data layer use case shows how such an agent queries it.

How do you check that your vector index is current?

After a sync, first confirm the model on record is the one your question-embedding code calls:

SELECT table_name, embedding_column, model_name, dimensions
FROM hr.sp_embedding_meta;

Second, confirm no live row with text is missing a vector. After a sync this should be zero:

SELECT count(*) AS live_rows_without_vector
FROM hr.hr_policies
WHERE sp_deleted_at IS NULL
  AND coalesce(title, summary, body) IS NOT NULL
  AND sp_embedding_hash IS NULL;

Third, confirm the most recently modified policies each have a vector. sp_modified is SharePoint’s own modified time, so the top of this list should match what HR edited today:

SELECT sp_item_id, title, sp_modified, sp_embedding_hash IS NOT NULL AS embedded
FROM hr.hr_policies
WHERE sp_deleted_at IS NULL
ORDER BY sp_modified DESC
LIMIT 10;

The questions matter more than the queries, because they test what users see. Keep a handful of golden questions with the item each should retrieve: “how many days can I carry over?” should return the carry-over policy, and “can I work from abroad?” should not return the withdrawn remote working policy. Run them in your assistant’s tests. The queries tell you the pipeline ran, and the golden questions tell you the answers changed.

Where a flow, a loader or the SharePoint indexer fits

If your answers live inside Word and PDF files in a document library, SPSync is the wrong tool for the text, because it replicates library metadata and not file contents. A LangChain or LlamaIndex loader over Graph can read file contents, and SPSync can still supply the catalogue beside it: folder, content type, owner and modified date as columns you filter on.

The most direct alternative for lists is Microsoft’s SharePoint in Microsoft 365 indexer for Azure AI Search, which is in preview. It indexes document libraries and, from the 2026-05-01-preview REST API, SharePoint lists and their item field values. It indexes incrementally and detects deleted content, so if Azure AI Search is your only target and a preview feature is acceptable, try it first. An indexer polls: it runs on demand or on a schedule, and the shortest interval Azure AI Search allows is five minutes, the longest 24 hours. Each run picks up what changed since the previous one, so a list indexed on that schedule is as stale as the gap between runs, the same trade a sync interval makes. Where SPSync differs is that the same data also arrives in a SQL table you can join and filter, SPSync records the embedding model and handles a model change.

If you have one small list, a single assistant and a team that already owns a Power Automate estate, a flow on item changes plus a second flow for deletes may be all you need. Write down which model it uses and plan the backfill before you need it.

SPSync fits when the text lives in list columns, the list changes often, and you would sooner not own an embedding job. What it costs to keep a SharePoint export script running compares building a pipeline with running a replication. RAG on SharePoint lists covers the wider design, and licensing explains how the Search and AI destinations are licensed.