Running a local AI model over SharePoint data in DuckDB

11 October 2026

Tags: Relational databasesWarehouses and lakesAIDesktopCommand line

A litigation team’s Matters list is about as sensitive as SharePoint data gets, and the partners still want to ask it questions in plain English. In the example this post follows (an illustrative firm, not a customer), the list holds about 6,200 matters, each with a Client lookup, a practice area, a responsible solicitor, a key date and a long Notes column where the history of each matter is written down. The firm’s information security policy says client data may not be sent to a public AI service, so the plan was a local model: Ollama on a senior associate’s laptop, with nothing leaving it.

The associate did what most people try first. She opened the list, chose Export, then Export to CSV, and loaded the file into a chat window running against a local model. The first answers looked plausible. Then she asked how many open matters the team had for one client and checked the number against the list view. The two did not agree, and there was no way to tell which was right.

Why a CSV export gives the model wrong answers

The export is the weak link, and Microsoft’s own Q&A forum is full of examples. In a thread titled “CSV export of a sharepoint list is including all versions”, a user with a list of about 5,000 items got a CSV of exactly 30,000 rows, because the export had written out every version of every item. In another, “Exporting a list view which only return 400 items”, a list of 33,000 items had a filtered view of under 400 items, and Export to Excel still refused it with the list view threshold error while Export to CSV worked. Export to Excel hands you a query file that Excel runs later against SharePoint, and other threads report it returning fewer rows than the CSV from the same view.

Even a clean export loses structure. The Client lookup comes out as the client’s name with no id beside it, so two clients trading under similar names are one typo apart. The view you export from decides which rows and columns the CSV holds, so two people exporting the same list can hand the model different files. And the file is out of date the moment you save it. The associate would have to repeat the export every morning, and the model would still be answering from yesterday’s copy whenever she forgot.

Then there is the model’s side. Ollama’s “Context length” page sets a default of 4k tokens on machines with under 24 GiB of VRAM, and recommends at least 64000 tokens for agents and tool use. A Matters export with its Notes column is far bigger than either, so the chat tool splits the CSV into chunks and retrieves a handful per question. Counting from retrieved chunks gives you the number of matching chunks that were retrieved, which is why her count was wrong. A model is good at writing SQL and poor at adding up rows it was shown, so the fix is to give it a database.

What does “the data stays on this machine” require?

Four hops need checking, and the security team will ask about each one. The first is SharePoint to the laptop. SPSync reads the list through Microsoft Graph over HTTPS, signed in as the associate, and writes the rows into a file on her disk. SPSync itself receives none of the list data, and the desktop app keeps its sign-in token in the operating system’s keychain.

The second hop is the file. Put it somewhere that is not synced back to the cloud. Many firms redirect Documents and Desktop to OneDrive with Known Folder Move, so a replica saved there is uploaded straight back to Microsoft 365, a copy of the whole list in a single file. A folder such as ~/SPSync/ on an encrypted disk (FileVault on a Mac, BitLocker on Windows) is the right home.

The third is the model runtime. Ollama binds to 127.0.0.1 on port 11434 by default, so nothing else on the network can reach it, but it also offers cloud-hosted models, and a prompt sent to one of those leaves the machine. The Ollama FAQ documents a local-only mode: set "disable_ollama_cloud": true in ~/.ollama/server.json, or the environment variable OLLAMA_NO_CLOUD=1, and restart Ollama. Its log then shows Ollama cloud disabled: true, which is a useful line to paste into the security review.

The fourth is the tool layer between the model and the file. An MCP client and an MCP server both run as local processes and talk over standard input and output. The client we use below, ollmcp, can also be pointed at hosted providers, so pin it to Ollama and leave the API key settings empty. If part of the setup has to run in the cloud, our post on keeping SharePoint data in region for AI maps where prompts, embeddings and indexes go at each hop.

A scheduled replica in a SQLite or DuckDB file

In the SPSync desktop app, the associate signs in with her own Microsoft 365 account. This is delegated access, so SPSync can read only what she can already read. She connects the legal site, ticks the Matters and Clients lists, and picks DuckDB as the destination with a file path of ~/SPSync/legal.duckdb. On the same step she turns on “Create reporting views”, checks the schema preview, and sets the schedule. In Settings, “Keep syncing in the background” keeps the schedule running with the window closed, through a launchd agent on macOS or a per-user Scheduled Task on Windows.

The first run is a full load. After that, each run reads only the items that changed since the last one. With delegated sign-in the desktop app’s automatic interval is every five minutes, so the file stays within a few minutes of the list while the laptop is on. A reconcile compares the whole list with the file, weekly by default, as a QA check and to catch up after the laptop has been closed for a long weekend.

The file holds a legal_matters table and a legal_clients table, one row per item, plus a shared sp_users table for people. Column names come from SharePoint’s internal names, which can differ from the names shown in the list. The column displayed as “Matter Number” was created years ago as “Ref” and renamed, so its internal name is still Ref and the replica column is ref. “Responsible Solicitor” started life as “Fee Earner”, so the person column is fee_earner_user_id, joining to sp_users.id. The Client lookup becomes client_id, which joins to legal_clients.sp_item_id, and client_value, the client’s name. Renaming a column in SharePoint later never renames the replica column, which matters once a model’s instructions refer to it.

The reporting view v_legal_matters does the person join for you, adding fee_earner_display_name and fee_earner_login, and it leaves out deleted matters. In the table itself, a deleted matter keeps its row with sp_deleted_at set.

From the command line, the same replica is a YAML file and one command. export-config writes it for you:

spsync auth login --client-id $SPSYNC_CLIENT_ID
export SPSYNC_SQLITE_PATH="$HOME/SPSync/legal.db"
spsync export-config --output legal.yaml --pipeline-name legal_matters \
  --prefix legal --destination-kind sqlite \
  --connection-string-env SPSYNC_SQLITE_PATH --dataset-name main \
  --client-id $SPSYNC_CLIENT_ID \
  --site-url https://contoso.sharepoint.com/sites/legal \
  --lists-by-name Matters,Clients --create-reporting-views
spsync run legal.yaml

For DuckDB, pass --destination-kind duckdb instead and point --connection-string-env at a variable holding a .duckdb path, for example export SPSYNC_DUCKDB_PATH="$HOME/SPSync/legal.duckdb". The rest of the command is the same, and the written destination block is kind: duckdb with connection_string_env: SPSYNC_DUCKDB_PATH.

spsync run does one round and exits, so launchd, cron or Task Scheduler sets the cadence.

Should the file be SQLite or DuckDB?

SQLite needs nothing installed, and every tool on the machine can open the file. SPSync writes it in WAL mode, so a reader is never blocked while a sync is writing. Two details matter for a model. Date-times are stored as text with a space between date and time and no time zone suffix, so tell the model to compare with SQLite’s datetime() function. And the SQLite MCP servers we tried are weak at staying read-only: the archived reference server exposes write_query and create_table tools, and SQLite Explorer accepts any statement that starts with SELECT or WITH, while SQLite happily runs WITH ... DELETE.

DuckDB is faster for the grouping and counting a model asks for, and the CLI’s DuckDB destination can also write a Parquet copy of every table after each round. The mcp-server-duckdb package has a --readonly flag that opens the file with DuckDB’s own read-only mode, so the database engine refuses any write. DuckDB lets one process write to a file, and other processes read it only while no process is writing, according to DuckDB’s “Concurrency” page. The server opens a fresh connection for each query unless you pass --keep-connection, which its README warns can hold an exclusive lock on the file, so leave that flag off. For a model, we pick DuckDB.

Querying the replica from a local model

She uses ollmcp, a terminal MCP client for Ollama. It reads a JSON file in the same shape as Claude Desktop’s configuration:

{
  "mcpServers": {
    "matters": {
      "command": "uvx",
      "args": [
        "mcp-server-duckdb",
        "--db-path",
        "/Users/you/SPSync/legal.duckdb",
        "--readonly"
      ]
    }
  }
}

She starts it with ollmcp --servers-json matters.json --model qwen3:14b. Any model tagged for tool use in Ollama’s library will do, and Qwen3 is one. The first message of each session carries the same short instructions:

Answer from the v_legal_matters view. ref is the matter number and
fee_earner_display_name is the responsible solicitor. Closed matters have the
status Closed. Say when the data was last synced, using max(sp_synced_at).

The first question is one she used to answer by filtering the list by hand: “Which open Harrow Logistics matters have a key date in the next two weeks, and who has them?” The model writes:

SELECT ref, title, key_date, fee_earner_display_name
FROM v_legal_matters
WHERE client_value = 'Harrow Logistics'
  AND status <> 'Closed'
  AND key_date BETWEEN current_date AND current_date + INTERVAL 14 DAY
ORDER BY key_date;

Three rows come back, and the model lists them with the solicitor against each and the sync time underneath. The second question is the one the CSV could never answer: “How many open employment matters mention a settlement agreement in the notes, per solicitor?”

SELECT fee_earner_display_name, count(*) AS matters
FROM v_legal_matters
WHERE practice_area = 'Employment'
  AND status <> 'Closed'
  AND notes ILIKE '%settlement agreement%'
GROUP BY fee_earner_display_name
ORDER BY matters DESC;

DuckDB scans every Notes value and returns five short rows, which fit in a 4k context with room to spare. SPSync stores Notes as Graph returns it, which for a rich-text column includes the HTML markup, so a phrase search still works but quoting a note back may show tags. When the partners ask which matters look like a given one, that is a semantic question, and keyword matching is the wrong tool; we come back to it below.

Files and attachments on a matter

SPSync replicates list items and document-library metadata. It does not copy file contents. If the Matters list has attachments switched on, the table gets an sp_has_attachments column, so the model can say that a matter has attachments, but it cannot read them. A matter’s documents in a library arrive as metadata rows (name, author, modified date and your own columns), never the text of the documents.

For semantic search over the Notes column, SQLite and DuckDB carry no vectors; SPSync generates embeddings only for pgvector and Azure AI Search. The on-device route is a local Postgres with the pgvector extension as the destination, with SPSync’s local embedding provider. That runs the fastembed library with the BAAI/bge-small-en-v1.5 model, needs no API key, and works offline after its weights are downloaded on first use. It is more to install than a single file, so start with SQL and add it when the semantic questions arrive.

What does the security review want to see?

The reviewer at the firm wanted answers they could check for themselves. SPSync reads SharePoint as the associate, with the delegated Sites.Read.All and User.Read scopes. There is no write call anywhere in the product, and nothing is installed in SharePoint. The data goes into one file at a path she chose, outside OneDrive, on an encrypted disk, and the sign-in token sits in the operating system’s keychain. The model can change nothing, because the DuckDB server opens the file read-only and the model has no route to SharePoint at all. Deleted matters keep their row with sp_deleted_at set, and if your retention rules say a deleted record must go, deletes: hard in the replication config removes the row instead.

There is one answer the reviewer must hear plainly. SPSync does not copy SharePoint item permissions into the file, and the file applies none at query time. It holds every row the associate could read when the sync ran, and whoever can open the file can read all of them. If your firm enforces ethical walls with item-level permissions on the Matters list, a laptop replica is the wrong place for walled matters.

Moving the replica to a server

A laptop file works for one person, or a small team where each person runs their own copy. Once several people need the same answers, the replica belongs on a server: run the SPSync command-line tool on a schedule against Postgres or SQL Server, give the model a database role that can read only the views it needs, and use row-level security on a column that marks walled matters to keep them out of reach. The model can stay local too, on an Ollama server or another private endpoint inside the firm’s network. That is also the route when the questions must be answerable before anyone has opened a laptop, or when an administrator would rather grant SPSync Sites.Selected on the legal site than rely on one person’s sign-in.

For the rest of the setup, see chat with your SharePoint data, SharePoint to DuckDB and SharePoint to SQLite, or SharePoint to pgvector for the semantic route. Licensing explains the per-installation, per-destination-family licence. For a shared server replica with a scoped role, read An MCP server for SharePoint data that queries SQL, and for why retrieval-based assistants miscount list rows, read Copilot on SharePoint lists.