Read SharePoint once and let the AI pipeline query the copy

11 October 2026

Tags: Relational databasesSearch and vector storesAIDesktopCommand line

The finance assistant worked well for most of the month. People asked it which supplier invoices were waiting for approval and what was left on the close checklist, and it answered from three SharePoint lists. Then month-end came round. On the first working day the assistant started replying “I couldn’t reach SharePoint, please try again”. The nightly ingestion job was still running at nine in the morning, and its log was a wall of 429 Too Many Requests.

If you have built anything AI-shaped on top of SharePoint, you have probably seen a version of this. SharePoint is designed to push back on heavy, repetitive readers, and retrieval jobs and agents are that kind of reader. Our approach is to read SharePoint once, carefully, and let every AI consumer query a replica.

Why AI workloads get throttled faster than reports

Here is the setup in our example. A company with about 3,800 Microsoft 365 licences runs its finance operations from one SharePoint site. The Invoices list holds about 42,000 items, with a Supplier lookup, an Amount, an Approval Status choice and a Received date. A Suppliers list holds the supplier records the lookup points at. The Close Tasks list is the month-end checklist, a few hundred items, each with an owner and a long Instructions column explaining how to do the step.

The assistant was fed two ways. A nightly Python script read all three lists through Microsoft Graph, fetched each invoice’s permissions so answers could be filtered by who was asking, embedded the text and wrote everything into a vector store. For questions that needed today’s status, the agent also had a live tool that called Graph directly. Both used the same app registration.

A Power BI report over the same lists reads them once per refresh, on a timetable. The AI workload reads them far more often than the data changes. The ingestion script re-reads every invoice every night whether it changed or not, and the agent tool re-reads the list for every question, sometimes several times per question. Month-end is when finance staff edit invoices fastest and ask the assistant the most, so the reads pile up just when SharePoint is busiest.

What does a 429 with Retry-After mean?

Microsoft Learn’s page “Avoid getting throttled or blocked in SharePoint Online” (updated August 2026) sets out the rules. When an application goes over its limits, SharePoint answers with HTTP 429, “Too many requests”, or 503, “Server too busy”. Both carry a Retry-After header giving the number of seconds to wait. The page is blunt about what happens next: throttled requests count towards usage limits, so an application that ignores Retry-After gets throttled for longer, and one that keeps exceeding its limits can be blocked outright, with a notice in the Microsoft 365 Message Center.

The limits are measured in resource units. Each Graph request has a published cost: a single item read or a delta request with a token is 1 resource unit, a multi-item query such as a page of list items is 2 resource units, and any operation on the permissions resource, including $expand=permissions, is 5 resource units. Each application gets a budget per tenant that depends on the tenant’s licence count. For a tenant between 1,001 and 5,000 licences, like our finance example, that is 2,500 resource units per minute and 2,400,000 resource units per 24 hours. There is also a tenant-wide limit, 37,500 resource units per 5 minutes at that size, and the same page notes that apps in one tenant share that resource bucket.

Two details catch people out. SharePoint Online does not send RateLimit headers, even though the page’s own checklist mentions them, so there is no early warning: the first signal you get is the 429 itself. And delegated calls are also capped per user across every app, at 3,000 requests per 5 minutes, so an agent tool running as the signed-in user shares that allowance with the user’s own browser, Excel and Teams.

What a bad retry does to the whole tenant

Now run the arithmetic for the nightly script, at 200 items a page. Paging through all three lists costs a few hundred resource units, which is nothing. The permissions call per invoice is what hurts: 42,000 invoices at 5 resource units each is 210,000 resource units, every night. At the app’s ceiling of 2,500 resource units per minute that is 84 minutes of the app’s entire budget even if every request succeeds first time.

They do not all succeed. The script runs eight threads, goes over the per-minute limit within seconds and starts getting 429s. Its retry loop waits two seconds instead of the Retry-After value, so it spends more of its budget on requests that fail, and the overrun stretches into the working day. Because the agent tool uses the same app registration, it is now competing with the script for the same per-minute allowance.

The cost spreads beyond the assistant. Everything else reading SharePoint draws on the same tenant-wide bucket, including the backup tool and every Power Automate approval flow, so a misbehaving AI job slows down the people closing the books as well as itself.

Why does an agent tool over Graph make it worse?

An agent tool looks cheap because each call is small. The question “which invoices above 10,000 GBP have been waiting more than 30 days for approval?” needs a filter on Approval Status and Amount. Neither column is indexed, and on a 42,000-item list SharePoint refuses a filter on a non-indexed column past the 5,000-item view threshold. So the tool does what most tools do and pages through the whole list, about 210 pages, or 420 resource units, then lets the model filter the results.

Language models do not call a tool once. They call it, look at the result, and call it again with a narrower argument, or call two tools in parallel to compare. Three calls for one question is ordinary, which puts a single question at well over a thousand resource units. Two people asking in the same minute exceed the app’s per-minute budget on their own. A tool that also checks item permissions to trim the answer multiplies every read by five.

Agent frameworks also retry on their own. When the tool returns an error, the model often tries again straight away, and a tool that wraps its own retry loop around the Graph call adds a second layer. Neither layer usually reads Retry-After. The page on dev.to, “SharePoint Online throttling internals, limits and survival patterns, Copilot-era edition”, describes this pattern well. The usual fixes are hand-written backoff, lower concurrency, a caching layer and an off-peak window, and every one of them is code your team now owns.

Reading SharePoint once

The alternative is to make one well-behaved reader responsible for SharePoint, and to make it cheap. SPSync replicates the three lists into a database: a full load the first time, then a Graph delta query on every round after that. A delta request with a token returns only the items that changed or were deleted since the last round, and costs 1 resource unit per page on Microsoft’s table. On a round where nothing changed, SPSync spends 1 RU per list. Its scheduling estimate below allows three per list, to leave room for the extra requests a round with changes makes.

Do the same sum as before. Three lists synced every five minutes, with no changes, is 288 rounds a day and 864 resource units, a rounding error against the daily budget. On the busiest close day, a few thousand invoice edits come back as extra pages of changes spread across the day, and the delta query never re-reads an invoice that was left alone.

SPSync handles throttling the way Microsoft’s page asks. On a 429 or 503 it waits for exactly the Retry-After value before trying again, and it records the resource-unit cost SharePoint reports on every response against a per-tenant meter, throttled responses included. By default it keeps at most four requests in flight against a tenant, and it slows down on its own once it has used most of the per-minute or daily budget. The command-line tool also estimates a safe sync interval from the tenant’s resource-unit budget, aimed at half of the per-minute allowance: spsync doctor shows that interval and how it was worked out, and your scheduler runs spsync run at that cadence or a longer one:

adaptive-interval  ok  30s: 3 list(s) x 3 RU = 9 RU/round; target 50% of 1250 RU/min (625 RU/min) -> 30.0s (floor, computed 0.9s)

That estimate assumes the smallest licence tier, so it errs on the cautious side, and 30 seconds is the floor. For lists like these we usually set five minutes. Set SPSYNC_LOG_LEVEL=debug and every Graph request is logged with its resource-unit cost, and every throttle with its Retry-After, which is useful the first time a security or SharePoint administrator asks what the sync actually does.

The heavy reads are the first full load and the reconcile, a check that compares every item id with SharePoint, weekly by default. Microsoft’s page says throttling is less likely off-peak, meaning nights and weekends in the tenant’s region, so that is where they belong. With the command-line tool, run the first spsync run on a Saturday and put spsync reconcile on a Sunday-night cron entry. In the desktop app and SPSync in your Azure the reconcile runs on its own schedule, so start the first full load on a quiet evening.

Here is the replication for the finance site, with the Close Tasks instructions embedded for retrieval. Apart from the sign-in block, this is the whole configuration:

site_url: https://contoso.sharepoint.com/sites/finance
pipeline_name: spsync_finance
prefix: fin
lists:
  - name: Invoices
  - name: Suppliers
  - name: CloseTasks
destination:
  kind: pgvector
  connection_string_env: FINANCE_REPLICA_DB_URL
  dataset_name: finance
  pgvector_embedding_provider: azure-openai
  pgvector_embedding_model: text-embedding-3-small   # the deployment name
  pgvector_embedding_dimensions: 1536
  pgvector_embedding_columns: [instructions]
  pgvector_api_key_env: FINANCE_AOAI_KEY
  pgvector_azure_endpoint_env: FINANCE_AOAI_ENDPOINT
# auth: the block export-config writes for your app registration

Column names come from each column’s SharePoint internal name, decoded and lowercased, so Approval_x0020_Status becomes approval_status. The Supplier lookup becomes a supplier_id column you join to fin_suppliers. Only the Close Tasks table has an instructions column, so it is the only table embedded, and a task is re-embedded only when its instructions text changes. We cover that side in more depth in Keeping a SharePoint vector index current as items change.

Querying the copy as often as people ask

Once the replica exists, the assistant stops talking to SharePoint. The month-end question that cost over a thousand resource units through Graph becomes one query against Postgres, with no Graph call at all:

SELECT i.title AS invoice, s.title AS supplier, i.amount, i.received
FROM finance.fin_invoices i
JOIN finance.fin_suppliers s ON s.sp_item_id = i.supplier_id
WHERE i.sp_deleted_at IS NULL
  AND i.approval_status = 'Awaiting approval'
  AND i.amount > 10000
  AND i.received < now() - interval '30 days'
ORDER BY i.amount DESC;

The agent can run that as often as people ask. Postgres does the filtering, so the model gets a short, precise answer and never has to read through 42,000 rows. Questions about how to do a close step go to the embedded instructions in pgvector, in the same database:

SELECT title, owner_user_id, instructions
FROM finance.fin_close_tasks
WHERE sp_deleted_at IS NULL
ORDER BY sp_embedding <=> %(question_vector)s
LIMIT 3;

If your search already runs on Azure AI Search, SPSync can replicate the same lists into an index there, with vectors generated on sync. Those two destinations are the ones that carry embeddings. Elasticsearch, OpenSearch, Qdrant, Weaviate and LanceDB receive the same rows without a vector field.

A replica is only as current as its last sync, so put the sync time where the agent can see it. sp_synced_at on each row records when SPSync wrote it, and a short line in the system prompt telling the model to quote it stops anyone mistaking a five-minute-old answer for a live read. For the full agent setup, An MCP server for SharePoint data that queries SQL walks through a read-only database role and an MCP server over it.

How do you tell the security team this is also the lower-privilege design?

Look at who holds which permission. Before, the ingestion script and the agent tool shared an app registration with Sites.Read.All, which can read every site in the tenant, and the agent could call it with arguments the model chose. After, only SPSync holds a Graph permission. It is read-only, there is no write call in the product, and it can be limited to the finance site with Sites.Selected, which an administrator grants per site.

The assistant holds a database login and nothing else. Give it a role with SELECT on a few views, and use row-level security if, for example, cost-centre managers should only see their own invoices. Be precise with the security team about one thing: SPSync does not copy SharePoint item permissions into the replica, so the replica does not trim answers per user at query time. The per-invoice permissions call in the old script was trying to do that, at 5 resource units an item. With a replica, the boundary is the set of lists you replicate plus the grants on the database, which a security reviewer can read in one place.

What still touches SharePoint

At query time, nothing. The retrieval job and the agent both read the replica. The only process that reads SharePoint is the sync itself, on its schedule, through one app registration, with the first full load and the reconcile run when the tenant is quiet. People still edit invoices in SharePoint, and those edits reach the replica on the next round.

Some workloads should stay on Graph. If your answers live inside Word and PDF files, the replica does not help with the text, because SPSync replicates document library metadata and not file contents. A loader that reads files through Graph, honouring Retry-After, is the right tool there. If you have one small list and a handful of questions a day, a direct Graph tool with a proper backoff is simpler than any replica. Microsoft also points broad site analytics at Microsoft Graph Data Connect, which is aimed at tenant-wide reporting more than at list item values. And Copilot Studio with SharePoint as knowledge does its own reading inside Microsoft 365, with limits of its own that Why Copilot cannot count the rows in your SharePoint list covers.

The RAG on SharePoint lists use case covers the retrieval side, AI agents and tools over SharePoint covers the agent side, and licensing explains how the Search and AI destinations are licensed. If you are deciding how to build the embedding step itself, Four ways to embed SharePoint list text, compared sets out the options.