DEV Community: Kaspars Jurjāns

Index-Time ACLs or Live Checks: A One-Folder Test for SharePoint AI Assistants

Kaspars Jurjāns — Wed, 04 Nov 2026 08:51:48 +0000

Every assistant that answers from SharePoint works from an index: a copy of your documents, split into passages. What matters for access control is whether that index also stores who may see each passage.

Design 1: the permission list lives in the index

Azure AI Search has a SharePoint indexer with permission metadata ingestion, a preview feature of Azure AI Search, not of Microsoft Copilot. During ingestion the indexer "computes the effective ACL for each file" and "stores the permissions as metadata on each indexed document". At query time the engine matches the asking person against that stored list.

That list is a snapshot. Microsoft documents what refreshes it:

Unique permissions: "Starting in the 2026-05-01-preview REST API, ACL changes for items with unique permissions are detected and refreshed on each successful indexer run."
Group membership: Microsoft Entra group memberships and SharePoint site groups are resolved at query time, so removing someone from a group does not wait for an indexer run.
Inheritance: "Parent-scope permission changes aren't picked up automatically on subsequent indexer runs." A change on a site, library, list or folder waits for a resync, a document reset or an edit to the file itself.

Inheritance is the SharePoint default; removing a person from a library is a parent-scope change. Microsoft's warning is literal: "If you change SharePoint permissions without triggering an update mechanism, the index serves stale ACL data for previously ingested files."

Design 2: no permission list in the index

The other design stores nothing about access: after ranking, the tool asks Microsoft 365, for each candidate document, whether this person may open it now.

The call runs as the person. In the OAuth 2.0 on-behalf-of flow "the intent is to pass a user's identity and permissions through the request chain", and in delegated access "the application can't access anything the signed-in user couldn't access".

There is no copy to refresh; the costs move to question time:

Time. Every question adds Microsoft Graph calls, so the check has to be limited to the documents that can reach the answer, and bounded.
Failure. If Graph is slow or returns an error, a strict design leaves that document out, possibly the one that mattered most.

Azure AI Search also fails closed, for the whole query: "If ACL evaluation fails (for example, the Graph API is unavailable), the service returns 5xx and does not return a partially filtered result set."

Neither design repairs permissions that are too wide: Microsoft's deployment guidance for Microsoft Copilot starts with "remediating oversharing".

The test: one person, one folder

An afternoon's work, plus indexing time, and no vendor needed:

In an indexed library, create a test folder. Grant permission on it directly, by name, to a test person with no other access to that site: no group, no single-file share.
Add one document that answers a specific question, for example a fictional payment term. After the next indexing run, ask as that person and confirm the document appears in the answer or its sources.
Remove the person's permission on the folder itself, without editing the file: only what the file inherits changes, the parent-scope case.
Wait for the next indexing run, or longer than your vendor's stated refresh interval.
Once SharePoint refuses the person the file, ask again as that person, in a new chat.

If the document still appears, the assistant is most likely serving a stored permission list that nobody refreshed. If it is gone, note how long that took and whether anyone had to run anything.

Removing the person from a group tests a different path: Azure AI Search resolves group membership at query time.

Kaspars Jurjāns has worked with SharePoint since 2006 and builds Answergrove, an assistant that uses the second design. The Microsoft sources and what remains unknown are in the full article: What Happens to AI Answers When SharePoint Permissions Change After Indexing?.

Drafted with AI assistance, checked against the cited Microsoft sources and approved by Kaspars Jurjāns.

Read access to one SharePoint site needs FullControl first: how our AI indexer handles it

Kaspars Jurjāns — Wed, 28 Oct 2026 08:20:06 +0000

The easy way to let an AI assistant read SharePoint is one consent: the Microsoft Graph application permission Sites.Read.All. Microsoft describes it in one line: "Allows the app to read documents and list items in all site collections without a signed in user." The setup finishes, the crawler never meets a closed site, and nobody keeps a list of sites.

The narrow way is Sites.Selected. It has one awkward step, and this post is about that step.

Sites.Selected starts at zero

Selected scopes "require an explicit assignment action", so an app that has only been consented "would initially have no access." Three things must be true before the indexer can read a site:

The app is consented for Sites.Selected in Entra ID.
The site is granted to that app with a role, through POST /sites/{siteId}/permissions.
The token the app presents carries the scope.

Miss one and "the application doesn't have access." An indexer needs the read role and nothing more: "Read the metadata and contents of the resource."

The awkward step

Creating the site grant needs a very large key. The call requires Sites.FullControl.All, and Microsoft explains why: "Because you can grant full control permissions to a site collection by using Sites.Selected, this requirement is necessarily high."

So to give one app read access to a few sites, something must hold full control for a moment. This is what our installation script does with that moment:

It creates a temporary app with Sites.FullControl.All and uses it only to make the grants.
It posts one grant per chosen site:

POST /sites/{siteId}/permissions
{
  "roles": ["read"],
  "grantedToIdentities": [
    { "application": { "id": "<indexer app id>", "displayName": "Answergrove Indexer" } }
  ]
}

It removes the temporary app's permission and secret, so that even a restored copy could do nothing.
It deletes the app and then removes it from the deleted items. Microsoft keeps a deleted application restorable "for up to 30 days", and after a permanent delete it "cannot be restored."
If any cleanup step fails, it stops with an error, names what is left and does not count the installation as finished.

The order matters. Take the permission away first and delete second, so that a failed permanent delete leaves only an empty shell in the recycle bin.

Two identities, one rule

The indexer runs on a schedule with nobody signed in. The part that answers people never holds the indexer's credential. Microsoft notes that application-only scenarios "have no user present and are considered higher risk", while with delegated access "the application can never exceed the current user's existing permissions."

For the indexer that means three things:

A certificate, not a client secret. The token request carries client_assertion instead of client_secret.
The private key in Azure Key Vault, where "key usage is logged in near real time."
A guard in the answering API. In our build the API refuses to start, with exit code 78, if indexer credential settings are present where it runs.

Test the denial, not the grant

A grant that works proves little. The useful test is the opposite one: with the indexer's own credential, request the drive of a site you never granted. The expected result is an access error, not a file listing.

Our script ends with that test and records the result before anything is indexed. The test proves the state of the tenant at the minute it ran, so it has to be repeated after every change to the site list or the app registrations.

One thing we have not measured: Microsoft states "Application access is lost as soon as a scope is revoked," but we do not know how quickly that holds for a token issued just before the revocation.

The full article covers the Entra admin center checks and lists every Microsoft source: Before an AI Reads Your SharePoint: Which Permissions Its Indexer Really Needs.

Drafted with AI assistance, checked against the cited Microsoft sources and approved by Kaspars Jurjāns.

Citing a spreadsheet: why our SharePoint AI names the sheet and the rows, not the file

Kaspars Jurjāns — Mon, 19 Oct 2026 11:57:07 +0000

We run an AI assistant over a construction company's SharePoint: 865 sites, about 400,000 documents. For a year it answered from PDF and Word only. This month we added Excel and PowerPoint. The extraction took an afternoon. The citation format took the real thinking, and that is what this post is about.

The problem with "see Budget_2026.xlsx"

An answer from a document assistant is only worth something if the reader can check it. For PDF that is easy: cite the page, the reader opens page 37 and compares. For a workbook, a link to the file is not a citation. Twelve sheets, four thousand rows, and the reader is expected to find the one cell the model used. Nobody does that. They either trust the number blindly or ignore the assistant. Both are bad.

So the rule we set: every citation must let a person verify the answer in seconds. Page for PDF. Sheet and row range for Excel. Slide number for PowerPoint.

What a spreadsheet chunk looks like

We read workbooks with openpyxl in read-only mode and walk each sheet row by row. Rows are grouped into chunks, and each chunk carries where it came from:

chunk = {
    "text": "...",                 # the rows, rendered as text
    "source_name": "Budget_2026.xlsx",
    "sheet": "Q3",
    "row_from": 118,
    "row_to": 142,
    "doc_version": "4.0",
    "modified": "2026-09-30T08:12:00Z",
}

Three details that mattered in practice:

Header rows travel with every chunk. A row like 45 | 1.2 | 2026-09-30 means nothing without the column names. We repeat the sheet's header row at the top of each chunk, so the model sees rate | factor | valid_from next to the values.
Rows, not cells. A cell reference like Q3!C119 is precise but useless for a human: the reader still has to find column C. A row range is enough to land on the right place, and the header tells the rest.
A ceiling per sheet. Some sheets are data dumps with 200,000 rows. We stop at 5,000 rows per sheet. Those sheets are rarely where a person's question lives, and indexing them makes every other answer slower.

For PowerPoint the slide number plays the role of the page: title, text frames, tables and notes are read slide by slide, and the citation names the slide.

The citation in the answer

The answer shows the source name, the sheet and the rows, and a link that opens the file in SharePoint. If content approval is enabled on the library, the approval status is shown too, because a draft workbook and an approved workbook can contain different numbers for the same thing.

This is the same contract we already had for PDF: document, version, exact location, and, through the signed-in user's own permissions, only documents that person may open.

Why bother

Because a fluent answer from the wrong place is worse than no answer. A reader who can check in seconds will check, and will catch the model when it picks the draft or the old version. A reader who cannot check will either stop trusting the tool or, worse, keep trusting it.

I wrote up the full set of checks we apply before an answer can be trusted, with the SharePoint and Graph details behind each one: Copilot, SharePoint and 400,000 construction documents: four checks before you trust an AI answer.

The assistant is Answergrove, built at SIA KSJ in Latvia. It runs alongside Microsoft 365 Copilot and does not need a licence per user.

No file may disappear silently: what indexing 400,000 SharePoint documents taught us about the Graph delta link

Kaspars Jurjāns — Sun, 18 Oct 2026 16:47:00 +0000

"No changes" can be the most dangerous answer an AI index gives. In a large SharePoint environment, documents can drop out of the index without any error anyone sees. In our case the fix was a rule about when not to move the Microsoft Graph delta link forward.

Originally published on ksj.lv.

The setting

We run a retrieval index over a production SharePoint environment belonging to a construction group. It covers 865 sites, about 400,000 documents and four languages. Indexing everything again each day is not realistic at that size, so the indexer keeps itself current with the Microsoft Graph driveItem delta API. Each document library has one saved deltaLink, and each cycle asks only for what has changed since that link.

Microsoft documents this pattern clearly. You call delta, follow @odata.nextLink until it stops appearing, and then receive an @odata.deltaLink, which is "used to read the next set of changes in the future". Microsoft's guidance on scanning at scale names search indexers as a target scenario and gives one instruction: always keep the URL returned in @odata.deltaLink so you can check for changes later.

The detail that matters is easy to miss. The delta API page says that once you have received all the changes, you may apply them to your local state. The deltaLink describes what Graph has delivered. It does not say whether you managed to store any of it.

What went wrong

Between 24 August and 2 September 2026, about 30 documents and about 20 deletions never reached the index. Nothing reported it. The vector index was a SQLite-based database stored on an Azure Files SMB share, and writes to it started failing with "database is locked". The delta job logged each failed file, skipped it and carried on. When the library was done, it saved the new deltaLink anyway.

Graph then never returned those changes again. As far as Graph knew, they had been delivered. The delta feed shows the latest state of each item, not a history of changes. An item that has been delivered once comes back only when it changes again. A document that nobody edits after that point stays missing until someone notices.

Nobody noticed it as missing documents, either. What we saw was slow cycles, taking between 20 and 87 minutes. One cycle reached the job's replica timeout of 5,400 seconds and was killed. In Azure Container Apps jobs, replicaTimeout is the maximum time in seconds to wait for a replica to complete. The alarm said "cycle crashed". That was true, and it pointed us at a symptom rather than the cause.

The storage cause

The first fix, on 2 September, was in the storage layer. Microsoft's recommended mountOptions for Azure Files SMB shares include nobrl, described as disabling byte range lock requests to the server, for applications that have trouble with POSIX locks. Azure Container Apps takes mountOptions on an Azure Files volume as a comma-separated string and links to that guidance. We added the option.

We should be honest about the wider picture. Azure App Service documentation says Microsoft does not recommend storage mounts for local databases such as SQLite, or for anything else that relies on file handles and locks. The mount option removed the immediate failure. It does not make a network share a good home for a database, and the indexer must not assume its writes always succeed.

The rule: hold the cursor when a write fails

The fix that matters more was made the same day, in the indexer. If any file in a library fails for an infrastructure reason, that library's deltaLink is not moved forward. The next cycle starts from the old link, and Graph returns the same changes again. Processing has to be safe to repeat for this to work. The delta documentation already says the same item may appear more than once in a feed and that you should use the last occurrence, so an indexer that follows the documentation is already built for this.

Getting the lost documents back took separate work. Delta would never return them, so we found the roughly 30 documents by file name through Microsoft Search and indexed them directly. If you advance a cursor too early, you have to go and find what you skipped by hand.

Not every failure should hold the cursor

The rule had a weakness of its own. If one file can never be processed, holding the deltaLink forever stalls the whole library. On 11 September we started classifying failures.

Broken files, whose content cannot be read, go to a visible list with the reason attached, and the library moves on. They are retried when SharePoint changes them. They no longer disappear quietly, and they no longer block the files around them.
Temporary failures, meaning HTTP 5xx, 429 and network errors, are retried in later cycles, up to 12 cycles by default. This fits Microsoft's throttling guidance. Graph returns 429 Too Many Requests with a Retry-After header, and the SharePoint scanning guidance treats 429 and 503 alike: wait for the Retry-After duration and pause further requests while you are throttled.
Everything else counts as infrastructure and holds the deltaLink.

The default sits on the safe side. A failure we have not classified does not get to advance the cursor.

The silent zero

The third change, on 21 September, dealt with a quieter version of the same problem. Sometimes no site answers at all. The certificate the app authenticates with has expired, or its Sites.Selected access has been removed. Sites.Selected needs an explicit grant on each resource, and Microsoft documents two ways an administrator can take that access away: delete the permission on the resource, or revoke the scope consent in Entra ID. Either way the application loses access.

Before the change, a cycle like that ended as "0 files, 0 deleted", which looked exactly like a quiet day with no changes. Meanwhile the answering service kept answering confidently from an index that was getting older. Now a cycle in which no site answered raises an alarm. "Nothing changed" and "nothing answered" produce the same counts, but they mean very different things for anyone relying on the answers.

The general lesson

In incremental sync, "the cursor moved" and "the work was done" have to be the same event. Move the cursor only after the write has succeeded. Treat a failure you cannot classify as a reason to stay where you are. Make the difference between "nothing changed" and "nothing answered" explicit, both in code and in alerts.

For administrators evaluating a search or RAG solution over SharePoint, these points turn into questions worth asking any implementer:

When a document fails to index, where does it show up, and who can see it?
Is the delta position saved before or after the index write is confirmed?
What does the system report when an expired credential or a removed site permission makes every site return nothing?
Does an alert fire on the cause, or only on a crash that happens hours later?

None of this is exotic. Microsoft describes the delta semantics, the throttling behaviour and the storage caveats. The failure happens in the space between them, where a log line says "skipped" and the cursor moves on regardless.

How this applies to our work

Holding the delta link after an infrastructure failure, keeping broken files visible, and alarming on a silent zero are now part of how Answergrove, developed by SIA KSJ, indexes SharePoint. More about the approach is at ksj.lv/en/answergrove.

Sources

I built an AI assistant that answers from SharePoint and shows where each answer came from

Kaspars Jurjāns — Sun, 28 Jun 2026 08:21:34 +0000

Update, October 2026: the product was first called Privault. It is now Answergrove.

For the last while I've been building Answergrove, a private AI assistant that answers questions from a company's own SharePoint. Every answer names the document it came from, its SharePoint version and, for PDFs, the page. SharePoint permissions are checked before anything is shown, and the data stays in the EU.

Here's the architecture and the judgment calls behind it.

The problem

Document-heavy organisations (construction, engineering, 50+ knowledge workers) want an AI that actually knows their SharePoint. For many EU companies the first questions are not about capability:

Where does the data go?
Which version of a document is in force, and was it approved?
Can every employee use it, without a per-user licence?

The architecture

Retrieval: semantic search over SharePoint document libraries and lists through Microsoft Graph. Each answer cites the document, its version and the page for PDFs. Where a library uses content approval, the answer shows the approval status.
Permissions: checked before anything is shown, so each person sees only what they are allowed to open.
Actions: Answergrove also prepares e-mails, tasks and meeting invitations from the chat, in your own name. You check each one and send it yourself.
Languages: any European language. A Swedish procedure can answer a question asked in English.
Models: Azure OpenAI in the EU Data Zone, for both reasoning and embeddings.
Where it runs: a two-week pilot runs in our Azure environment in Sweden Central (EU). After rollout the index, the AI and the logs run in the customer's own Azure subscription.

Answers are logged and cited, which turns "is the AI trustworthy?" into something you can actually audit.

The judgment call: pro-code vs low-code

This is where most "just use Power Automate" advice falls apart. Power Automate is great until you need real retrieval, custom tool calls, and control over how the model grounds its answers. That's a Python service. Knowing which one a given workflow needs is the whole game. Getting it wrong is how you end up with a flow nobody can maintain.

What surprised me

The first production environment, a construction group with 865 SharePoint sites, about 400,000 documents and four languages, showed that the hardest part wasn't the RAG. It was grounding + citations under messy, real-world input: bilingual requests, half-formed questions, documents that contradict each other. A demo that parses clean input is easy. Production is where "correct and in the EU" gets earned.

Answergrove runs alongside Copilot without interfering with it, and gives every employee, with or without a Copilot seat, answers from the documents they must get right. More on Answergrove here.

How We Automate SharePoint and Microsoft 365 Workflows for Small Businesses

Kaspars Jurjāns — Wed, 10 Dec 2025 10:30:34 +0000

Many small and medium businesses still use Microsoft 365 and SharePoint only as simple file storage or document folders.
However, with the right configuration, SharePoint + Power Automate can replace entire manual workflows.

Here are some examples from my recent projects:

invoice approval flows built on SharePoint lists and Power Automate
secure document routing and version-controlled processes
automated notifications and reminders for approvers
custom forms (Power Apps) and Power BI reports on top of SharePoint data

These solutions reduce manual work, eliminate human errors and improve productivity inside an organization.

If you're interested in practical automation ideas or need a Microsoft 365 / SharePoint solution for your company, this is my approach (in Latvian): SharePoint and Microsoft 365 automation