An agent data layer for SharePoint, with one SQL copy every agent reads

Last updated 26 October 2026

Each new agent gets its own Graph connection and pages through the same lists to answer a question that one join would settle.

Your second AI agent is usually where the trouble starts. The first one, a service desk assistant, got an app registration with Graph permissions and a few tools that read the Change Requests list, and on a demo it was fine. Then the change advisory board wanted an agent that checks supplier risk before approving work, and finance asked for one of their own. Now three agents hold Graph credentials, and every question each of them answers pages through the same SharePoint lists again.

Stacksync made the general case well in a blog post of 29 September 2026, “The AI Agent Data Layer: Per-App MCP Servers vs a Synced Postgres Database”, which we read on 20 October 2026. Its argument is that an agent should query one synced database through a small set of SQL tools, and the sync engine should own every call to the source system. We agree. For SharePoint, SPSync already provides the sync engine and the database schema, and this page shows how the rest fits together.

The four layers of an agent data layer on SharePoint

Each layer has one owner, and the agents only ever touch the last one.

1. SharePoint lists and libraries stay the system of record. People keep editing them in the browser and in Power Apps, as they do now.
2. SPSync owns every Graph call. It holds the app registration, reads each list with delta queries, meters the tenant’s resource units, honours Retry-After and keeps the delta token between rounds.
3. One database holds the copy in a normalised schema. Each list is a table carrying sp_ metadata columns such as sp_item_id, sp_modified and sp_deleted_at. A single lookup becomes two columns, supplier_id and supplier_value, and a multi-value lookup, choice or person column becomes a child table with one row per value.
4. Agents connect with a database login. There is no Entra app registration to create for them and no Graph permission to approve.

Adding a fifth agent adds connections to your database. SharePoint sees exactly the delta reads it saw with one agent, because SPSync is still the only thing reading it.

Which tools does an agent need over the replica?

Four, and the first three are ordinary database tools. The agent needs to list the tables, describe a table’s columns and run a read-only query. Any Postgres or SQL Server MCP server gives you those when you point it at the replica with a read-only login, and so does spsync mcp serve, SPSync’s own read-only MCP server over the replica. SPSync ships no agent. The replica is a plain database, so the servers people already run against Postgres and SQL Server work with it unchanged. The framework guide shows the tool set-up in LangChain, LlamaIndex, Semantic Kernel, CrewAI, AutoGen and n8n.

The fourth tool, proposing a change, is a pattern you build. SPSync reads SharePoint and never writes to it, so a proposal can go no further than a table in your database. Give the agent’s role INSERT on a staging table and nothing else that writes. A trigger stamps who made the proposal and forces its status to pending, so the agent cannot approve its own work.

CREATE TABLE agent_proposals (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  list_table  text   NOT NULL,
  sp_item_id  bigint NOT NULL,
  column_name text   NOT NULL,
  proposed    text,
  reason      text   NOT NULL,
  proposed_by text,
  proposed_at timestamptz,
  status      text
);

CREATE FUNCTION stamp_proposal() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.proposed_by := current_user;
  NEW.proposed_at := now();
  NEW.status      := 'pending';
  RETURN NEW;
END $$;

CREATE TRIGGER stamp_proposal BEFORE INSERT ON agent_proposals
  FOR EACH ROW EXECUTE FUNCTION stamp_proposal();

GRANT INSERT ON agent_proposals TO change_agent;

Keep the MCP server’s query tool on the read-only login, and give the insert its own small tool that runs one parameterised statement as the agent’s role. A person works through the pending rows and makes each approved change in SharePoint. The next sync round brings the edit back into the replica, where the agent sees it. Writing approved changes back to SharePoint from SPSync itself is on the roadmap.

A worked example across three SharePoint lists

An IT operations site has three lists. Change Requests holds 18,000 items, with a Status choice column and a multi-value Affected Assets lookup. Assets holds 2,400 items, each with a Supplier lookup. Suppliers holds 600 items and a Contract End date. The change manager asks the agent which open change requests touch assets whose supplier contract ends this quarter.

SPSync replicates the site with the prefix itops. The multi-value lookup becomes the child table itops_change_requests__affected_assets, one row per asset with lookup_id pointing at the asset’s sp_item_id. The agent describes four tables and writes the query below, which runs entirely in the database and makes no call to SharePoint.

SELECT DISTINCT cr.sp_item_id, cr.title, cr.status,
       a.title AS asset, s.title AS supplier, s.contract_end
FROM itops_change_requests cr
JOIN itops_change_requests__affected_assets aa ON aa.sp_item_id = cr.sp_item_id
JOIN itops_assets a    ON a.sp_item_id = aa.lookup_id
JOIN itops_suppliers s ON s.sp_item_id = a.supplier_id
WHERE cr.status <> 'Closed'
  AND cr.sp_deleted_at IS NULL
  AND a.sp_deleted_at IS NULL
  AND s.sp_deleted_at IS NULL
  AND s.contract_end >= date_trunc('quarter', current_date)
  AND s.contract_end <  date_trunc('quarter', current_date) + interval '3 months'
ORDER BY s.contract_end;

What the same question costs against Graph

An agent with Graph tools has to fetch all three lists and join them itself. At 200 items a page, the page size SPSync asks for, that is 90 pages of change requests, 12 of assets and 3 of suppliers. So 105 requests go out before the model has seen an answer. A plain multi-item read costs 2 resource units in SharePoint’s throttling model, which puts each question at 210 resource units, and the model still has to hold 21,000 items to do the join.

Filtering on the server helps less than you would hope. Microsoft’s Graph documentation says list item filters work best on indexed columns and can use one indexed field at a time. Change Requests is also well past the 5,000 items of SharePoint’s list view threshold, where queries that touch every item are refused. Index Status and you can filter on it. The contract date sits two lookups away on a different list, and no single Graph query reaches it.

Wide lists add a second limit. SharePoint allows 12 lookup joins in one query (Microsoft documents this for the Power Query SharePoint Online list connector), so a list with more lookup and person columns than that has to be read in batches and merged by item id. SPSync does that batching on every list it reads, and an agent calling Graph directly would need to know to do it as well.

On the replica side, the only cost to SharePoint is the delta round SPSync runs anyway. Microsoft charges 1 resource unit for a delta request with a token, and that stays the same whether ten agents ask questions that hour or none do.

Controlling what an agent can read in the replica

SharePoint item permissions are not carried into the replica, so the database is where you draw the line. Create a login per agent and grant it SELECT on the tables or views its job needs. The change agent above needs four tables. The finance agent needs different ones, and neither sees the other’s.

Row-level security narrows it further. In Postgres, once row-level security is enabled on the table, a policy such as CREATE POLICY region_only ON itops_assets FOR SELECT TO change_agent USING (region = 'EMEA') limits one agent to one region. SQL Server and Azure SQL do the same with a security policy and a predicate function.

There is no write path to SharePoint to secure. SPSync has no code that writes to SharePoint, and the agent’s login reaches only the database.

Auditing what an agent read uses the database’s own logging. In Postgres, set log_statement for the agent’s role or install pgAudit. In SQL Server, a database audit specification on SELECT for the agent’s user records every query, and Azure SQL has auditing built in. When someone asks what the agent looked at before it suggested a change, the answer is in a log your DBA already keeps. The governance resource for agents that are not Copilot sets these controls against the Copilot-side ones for a security review.

Related reading

Giving agents SQL tools over SharePoint data covers the read-only role and the views for one sales assistant, and MCP Server for SharePoint Data: Query SQL, Not Graph walks through the Claude Desktop setup against Postgres.

One SQL copy of SharePoint for every agent you run works out what a cross-list question costs through Graph and shows the same question as one SQL join, with the tools each agent needs and a way to stage write proposals for a person to approve.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name ai_agent_data_layer_for_sharepoint --prefix ai_agent_data_layer_for_sharepoint --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name ai_agent_data_layer_for_sharepoint --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: ai_agent_data_layer_for_sharepoint
prefix: ai_agent_data_layer_for_sharepoint
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: ai_agent_data_layer_for_sharepoint
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist