Library metadata is the catalogue an AI assistant needs

11 October 2026

Tags: Relational databasesSearch and vector storesAIDesktopCommand line

The company’s policy assistant was asked by someone in finance what the limit on client hospitality was. It answered quickly, cited a policy document, and quoted a figure that compliance had replaced eighteen months earlier. The document it quoted was still in the Policies library, as it should be: its Document status column said Withdrawn. The text of the file said nothing of the kind, and the assistant only ever read the text.

A document library carries two kinds of information, and most AI pipelines over SharePoint use only one. The file holds the words. Columns around it hold the facts that decide whether the words apply: is this the approved version, which department does it cover, is it past its review date, who owns it. The Copilot Studio CAT team’s post “SharePoint Metadata Filtering in Copilot Studio” makes the same point with a Country column: the word “Canada” may never appear in a benefits policy, only in the library’s metadata, so a search over content alone can miss the right document or mix policies meant for different countries.

Every AI project over the library re-scraped its metadata

Our compliance team keeps its policies in one library on the Compliance site. There are about 2,400 documents in it. Some use a Policy content type and some use Procedure. There are folders by year. Each document has a Document status choice (Draft, In review, Approved, Withdrawn), a Department choice, a Review date, and a Policy owner person column. The library has been in use for years, so most of the 2,400 documents are drafts, superseded versions and procedures that should never be quoted.

The policy assistant was the second AI project to touch this library. First came a contract-review pilot in legal, with a script that paged through the library with Microsoft Graph and saved the metadata to a JSON file. The assistant’s team wrote another, because they wanted different columns, and a records audit later wrote a third. Each script was correct on the day it ran and stale the next.

The assistant’s pipeline did what many retrieval pipelines do. It downloaded every file, extracted the text, split it into chunks, embedded the chunks and stored them with a few metadata fields copied in at index time. When compliance withdrew the old hospitality policy, they changed one column. The file did not change, so the pipeline saw nothing to update, and the chunk still carried “Approved” in its stored metadata.

What makes filtering library columns through Graph so awkward?

The obvious fix is to ask SharePoint at question time: give me the approved Finance policies whose review date has not passed. Through Graph that is a $filter on the list items endpoint, and on most libraries the first attempt fails with a 400 error:

Field 'DocumentStatus' cannot be referenced in filter or orderby as it is not indexed.
Provide the 'Prefer: HonorNonIndexedQueriesWarningMayFailRandomly' header to allow
this, but be warned that such queries may fail on large lists.

Microsoft Q&A threads quote that error word for word, and the header’s own name tells you how much to trust it. Microsoft Learn’s page on listing items says filtering works best on indexed columns and that the service can only filter one indexed field at a time. So your options are to ask a site owner to index the columns you care about, send the header and hope the library stays small, or page through every item and filter in your own code, which is what the scripts above ended up doing.

The values that come back need work too. A person column arrives as a lookup id (PolicyOwnerLookupId), which has to be resolved to a name in a second step. Every one of those pages counts against the tenant’s throttling budget, and an assistant that runs this on every question spends that budget on metadata it fetched an hour ago.

The library as a table

SPSync replicates a document library the same way it replicates a list. The first run reads every item, later runs read only the changes, and the result is one table with a row per item. For the Policies library, with a table prefix of compliance, the table is compliance_policies. Column names come from SharePoint internal names, never display names, lowercased and split at each word boundary. Our columns were created with the internal names DocumentStatus, Department, ReviewDate and PolicyOwner, so the table looks like this:

Every row also carries sp_item_id, sp_list_id and sp_site_id, which together are enough to ask Graph for that file later. Person columns resolve into one shared sp_users table with a display name and login, so “who owns this policy” is a join against a table you already have. A multi-choice column, such as an Applies to column listing several departments, becomes a child table with one row per value, ready to join.

Check the internal names before you write any SQL. A column created in the browser as “Review date” can get the internal name Reviewdate, which becomes reviewdate in the replica, while one created by a script as ReviewDate becomes review_date. spsync discover schema prints each column’s internal name beside its display name, so one run tells you which you have.

Folders replicate as rows of their own, with sp_content_type set to Folder, so a filter on content type keeps them out of the catalogue’s answers. Renaming or moving a document is an ordinary change: the row keeps its sp_item_id.

How does an assistant use the catalogue to choose which documents to read?

The assistant now works in two steps. First it decides which documents apply, using SQL against the replica. Then it reads only those. The asker’s department comes from their directory profile, and the query below runs against Postgres:

SELECT p.sp_site_id, p.sp_list_id, p.sp_item_id,
       p.title, p.department, p.review_date,
       owner.display_name AS policy_owner
FROM catalogue.compliance_policies AS p
LEFT JOIN catalogue.sp_users AS owner ON owner.id = p.policy_owner_user_id
WHERE p.sp_deleted_at IS NULL
  AND p.sp_content_type = 'Policy'
  AND p.document_status = 'Approved'
  AND p.department IN ('Finance', 'All staff')
  AND p.review_date >= CURRENT_DATE
ORDER BY p.sp_modified DESC;

On SQL Server the same query works with CAST(GETDATE() AS date) in place of CURRENT_DATE. For the finance question it returns a couple of dozen rows. The withdrawn hospitality policy is not among them, because its status column says Withdrawn, and the query never had to open a file to know that.

The second step fetches the text of those documents and nothing else. For each row, ask Graph for the item’s driveItem at /sites/{sp_site_id}/lists/{sp_list_id}/items/{sp_item_id}/driveItem, which returns the file’s name, its web URL for the citation and its drive and item ids, then download the bytes from /drives/{drive-id}/items/{item-id}/content. The name and URL come from Graph here because the replica’s own library file columns (file name, URL and folder path) are only relied on once they pass a live run. Use the asking user’s delegated token for this step. SharePoint then checks that the person asking may open each file, which the replica cannot do for you.

The assistant answers with a citation that links to the file itself. When an answer is wrong now, compliance checks one catalogue row, where before it had to search a vector store.

If the assistant is built on Azure AI Search, the replicated library becomes an index called compliance-policies, and choice, single-line text and date fields are filterable. The same choice of documents is an OData filter, and a deleted document is removed from the index on the next sync, so it stops matching without any deleted flag to filter on:

sp_content_type eq 'Policy' and document_status eq 'Approved'
  and (department eq 'Finance' or department eq 'All staff')
  and review_date ge 2026-10-11T00:00:00Z

Keeping the catalogue current

After the first full load, SPSync runs a delta sync on a schedule, typically every few minutes, and reads only the items that changed since the last round. When compliance marks a policy Withdrawn, that row’s document_status changes on the next sync. When a document is deleted, its row stays in the SQL replica with sp_deleted_at set, so the catalogue can tell “removed last week” from “never existed”.

A reconcile also compares every item id with SharePoint, weekly by default and on demand with the command-line tool. It is the check to run after an outage on your own server or in Microsoft 365, and it gives compliance a simple QA answer when an auditor asks whether the catalogue matches the library.

Give the assistant a view of the table, and a database role that can read only the view. On Postgres that is four statements, with the role’s password set separately:

CREATE ROLE policy_assistant LOGIN;

CREATE VIEW catalogue.assistant_policies AS
SELECT sp_site_id, sp_list_id, sp_item_id, title, department, review_date,
       document_status, policy_owner_user_id, sp_modified
FROM catalogue.compliance_policies
WHERE sp_deleted_at IS NULL
  AND sp_content_type = 'Policy';

GRANT USAGE ON SCHEMA catalogue TO policy_assistant;
GRANT SELECT ON catalogue.assistant_policies, catalogue.sp_users TO policy_assistant;

SPSync does not carry SharePoint item permissions into the replica, so the replica does not trim rows per user at query time. The view and the role decide which metadata the assistant may see, and the delegated fetch decides which file contents the asker may read. Row-level security on the replica can add per-department rules, but a plain Postgres view runs with its owner’s rights, so those rules only apply through the view if you create it WITH (security_invoker = true) (Postgres 15 or later) and grant the role SELECT on the base table too.

What SPSync leaves to your own extraction step

SPSync replicates library metadata and does not copy file contents. There is no text column and no chunk table in the replica. Reading the words inside a PDF or a Word document, splitting them and embedding them is a job you run yourself, and the catalogue makes that job much smaller.

If you already run an extraction step, change it in one way: stop walking the library and start reading the catalogue. On each run it selects the rows whose sp_modified is later than its last run. Editing a column also moves Modified, so for each candidate the step reads the driveItem, as above, and compares its cTag with the one it stored. Graph documents cTag as the content eTag, which metadata edits leave alone. Where it differs, the step downloads the file, extracts the text with whatever library fits the formats (pypdf and python-docx are common, Azure Document Intelligence for scanned PDFs), and stores its chunks keyed by sp_item_id alongside the new cTag. A policy whose status changed needs no extraction at all, because its content tag is unchanged and the status filter runs in SQL at question time, against the current value.

Keep the chunks in the same database if you can. A chunk table keyed on sp_item_id joins the filter to the retrieval and shows which chunks to drop when a row gets an sp_deleted_at.

How much embedding spend does filtering first save?

Take the Policies library again. Chunk-everything means extracting and embedding all 2,400 documents. At an assumed average of 30 chunks per document that is about 72,000 chunks to embed, store and search, most of them from drafts, withdrawn versions and other departments’ procedures. Filtering first means extracting the 180 approved, in-date policies the assistant may ever quote, about 5,400 chunks, and only re-extracting one when its file changes.

The saving is bigger than the ratio suggests, because the expensive event in a chunk-everything pipeline is churn. In our library most edits are to columns: a status moving from In review to Approved, a review date pushed out a year, an owner changing when someone leaves. With the catalogue, none of those edits touches the vector store. The search is also better, because the retriever is choosing among two dozen relevant documents instead of the whole library.

If you replicate the library into Postgres with pgvector or into Azure AI Search, SPSync can also embed metadata columns such as Title and a Summary column on each sync, re-embedding only rows whose text changed. That gives the assistant semantic search over the catalogue (“anything about gifts from suppliers?”) before it opens a file. Elasticsearch, OpenSearch, Qdrant, Weaviate and LanceDB replicate the same table without a vector field.

Where Copilot, the SharePoint indexer or a loader fits better

If your assistant is a Copilot Studio agent, read the CAT team post above first. It describes newer Copilot Studio agents getting a built-in sharepoint_metadata_filter tool that filters a library’s files by author, dates, folder and custom columns, then scopes knowledge search to the matching documents. For a single agent over a single library, with no other consumer of the metadata, that may be all you need. The Microsoft 365 roadmap also lists column metadata support in Microsoft 365 Copilot, when a library or folder is attached to the query, as launched.

If you want the file text indexed and searchable in Azure, Microsoft’s SharePoint in Microsoft 365 indexer for Azure AI Search reads both files and metadata from document libraries, indexes incrementally and removes deleted files. It is in preview. Microsoft Learn lists its limits plainly: renaming a folder breaks incremental indexing and the folder’s contents are treated as new, tenants with Entra Conditional Access are not supported, and private endpoints are not supported. Where those limits are acceptable, it does the extraction step for you.

LangChain and LlamaIndex both have SharePoint loaders that read files through Graph, and either is a reasonable way to write the extraction step. LlamaIndex’s reader loads the files in a folder, recursively if you ask it to. LangChain’s SharePointLoader can take a list of object ids instead of a folder, and the driveItem step above gives you exactly those ids for the documents the catalogue chose.

SPSync fits when more than one project needs the same library metadata, or when you want it in SQL where it can join to other lists (a policy acknowledgements list, say). It also fits an assistant that is your own code or reaches a database over MCP. An MCP server for SharePoint data that queries SQL covers the read-only database role for an assistant in more detail, and Keeping a SharePoint vector index current as items change covers re-embedding only what changed. The document metadata for AI use case sets out the catalogue pattern, the PostgreSQL and SQL Server pages cover the relational destinations, and licensing explains how each destination family is licensed.