Human-in-the-loop AI agents over SharePoint evidence

20 October 2026

Tags: Relational databasesAIDesktopCommand line

“Every Monday one of my coordinators spends most of the day with four browser tabs open,” an operations manager at a facilities maintenance firm told us. Next week’s jobs are in a SharePoint list. For each job she finds the contractor in the Suppliers list and checks they are still approved, then opens the Contractor Certificates library and scrolls to that contractor’s gas and asbestos certificates. The fourth tab is the Site Access list, which says what each client site insists on before anyone is allowed through the gate. Around sixty jobs a week go through that routine.

The check matters because the failure is expensive. An engineer turned away at a hospital gate costs a day, and an engineer let through with a lapsed certificate costs far more. The manager’s question was whether an AI agent could do the assembling, so the coordinator only reads a summary and decides. It can. The pattern is the one we now recommend for most agent work on SharePoint data, where the agent gathers evidence from a database copy and writes a packet that a person approves.

Checking contractor certificates before a job, by hand

Look at what the coordinator does for one job. She reads the job date and the client site. She finds the contractor and checks the Status column says Approved and the insurance expiry is after the job date. Then she reads the site’s required certificates from a multi-choice column, and for each one looks for a certificate from that contractor with an expiry date on or after the job date. If one is missing or lapses first, the job goes on hold and someone emails the contractor.

None of this needs judgement until the end. It is lookups and date comparisons, repeated sixty times. A person does it slowly and, by Thursday afternoon, with mistakes. A database does it in one query. The judgement at the end, whether to hold the job or accept a renewal that is in the post, is the part to keep with a person.

What an AI agent needs from SharePoint for a compliance check

An agent working against SharePoint live would need the same four tabs, through Microsoft Graph. That means paging through the Jobs list, resolving the contractor lookup, filtering a library by a lookup column, and reading a multi-choice column whose shape varies by API. On a list past the 5,000-item view threshold the filters on unindexed columns start failing, and an agent that retries in a loop collects throttling responses that also slow the intranet down. It also needs a Graph permission someone in security has to sign off. What the agent needs is the four sources as tables it can join, current to within a few minutes, behind a login that can read them and nothing else, which is what a replica gives you.

Replicating the operations lists and library to Postgres

SPSync reads the lists you pick through Graph and keeps a copy current in a database you own. It never writes to SharePoint. In the desktop app you connect the Operations site, tick the three lists and the library, pick PostgreSQL and check the schema preview. From the command line it is one config file:

spsync export-config --output ops-agent.yaml --pipeline-name ops_agent \
  --prefix ops --destination-kind postgres \
  --connection-string-env OPS_REPLICA_URL --dataset-name sharepoint \
  --client-id $SPSYNC_CLIENT_ID --site-time-zone Europe/London \
  --site-url https://contoso.sharepoint.com/sites/operations \
  --lists-by-name "Jobs,Suppliers,Site Access,Contractor Certificates"
spsync run ops-agent.yaml

Do not skip --site-time-zone. SharePoint stores a date-only column as midnight in the site’s time zone and Graph sends it as a UTC instant, so without the setting a UK expiry date entered during British Summer Time arrives in the replica a day early. For an expiry check that is the difference between a pass and a fail.

Table and column names come from SharePoint’s internal names, lowercased and split at word boundaries, so spsync discover schema is worth running first. Ours gave us this:

Every table also carries sp_item_id, sp_site_id, sp_content_type, sp_modified and sp_deleted_at, which is null while the item still exists in SharePoint. The child table holds sp_item_id and value, so it joins back to its Site Access row on the item id.

SPSync replicates the library’s own columns the same way as a list’s, and those columns (certificate type, number and expiry date, typed in by whoever filed the certificate) are what the check uses. The file columns SPSync adds to library tables (file_name, file_url and the rest) are not reliable on a real tenant today, so we do not build anything on them. We point to a certificate by its library item id, and SharePoint opens any item from its id at Forms/DispForm.aspx?ID= under the library’s address. The agent also never sees the certificate itself, because file contents are not replicated. If someone typed the wrong expiry date, the packet will repeat it, which is one reason a person approves.

How does an agent check certificate expiry against the job date?

The agent works through the job in steps, and each one is a short query against the replica. First the job:

SELECT sp_item_id, title AS job_ref, job_date, work_type,
       contractor_id, contractor_value AS contractor,
       client_site_id, client_site_value AS client_site
FROM sharepoint.ops_jobs
WHERE title = 'J-24117'
  AND sp_deleted_at IS NULL;

That returns job 2291, booked for 4 November at Riverside Hospital, Block C, with Northgate Heating as the contractor (supplier item 412). The contractor check is a single row:

SELECT title, supplier_status, insurance_expiry,
       insurance_expiry >= DATE '2026-11-04' AS insured_on_job_date
FROM sharepoint.ops_suppliers
WHERE sp_item_id = 412
  AND sp_deleted_at IS NULL;

Next comes the step that took the coordinator longest. The site’s requirements come from the child table, the contractor’s certificates from the library, and the newest certificate of each type is compared with the job date:

WITH job AS (
  SELECT job_date, contractor_id, client_site_id
  FROM sharepoint.ops_jobs
  WHERE sp_item_id = 2291 AND sp_deleted_at IS NULL
),
required AS (
  SELECT r.value AS certificate_type
  FROM sharepoint.ops_site_access__required_certificates r
  JOIN sharepoint.ops_site_access a ON a.sp_item_id = r.sp_item_id
  JOIN job ON job.client_site_id = a.sp_item_id
  WHERE a.sp_deleted_at IS NULL
),
held AS (
  SELECT DISTINCT ON (c.certificate_type)
         c.certificate_type, c.certificate_number, c.expiry_date,
         c.sp_item_id AS library_item_id
  FROM sharepoint.ops_contractor_certificates c
  JOIN job ON job.contractor_id = c.supplier_id
  WHERE c.sp_deleted_at IS NULL
    AND c.sp_content_type <> 'Folder'
  ORDER BY c.certificate_type, c.expiry_date DESC NULLS LAST
)
SELECT r.certificate_type, h.certificate_number, h.expiry_date,
       h.library_item_id,
       CASE
         WHEN h.certificate_type IS NULL THEN 'missing'
         WHEN h.expiry_date IS NULL THEN 'no expiry recorded'
         WHEN h.expiry_date < job.job_date THEN 'expires before job'
         ELSE 'in date'
       END AS finding
FROM required r
CROSS JOIN job
LEFT JOIN held h ON h.certificate_type = r.certificate_type
ORDER BY r.certificate_type;

For J-24117 the query returned three rows, one for each certificate Riverside requires. Two of them are problems:

A model could write that query itself from the schema, and given a good schema it often does. For a check you run sixty times a week we would not leave it to chance. Put the query in a view or a stored function, have the agent call it with the job reference, and let the model spend its effort on the summary.

Writing the evidence packet to a review table

The packet goes into a table SPSync knows nothing about. We keep it in a schema of its own, so a sync never touches it:

CREATE SCHEMA review;

CREATE TABLE review.job_packets (
  packet_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_item_id  bigint NOT NULL,
  job_ref      text NOT NULL,
  contractor   text NOT NULL,
  job_date     date NOT NULL,
  verdict      text NOT NULL CHECK (verdict IN ('clear', 'gaps')),
  summary      text NOT NULL,
  findings     jsonb NOT NULL,
  prepared_by  text NOT NULL DEFAULT current_user,
  prepared_at  timestamptz NOT NULL DEFAULT now(),
  decision     text CHECK (decision IN ('approved', 'held', 'rejected')),
  decided_by   text,
  decided_at   timestamptz
);

Each job gets one row. The findings column holds the rows above as JSON and the summary holds two or three sentences in plain English, which is where the model earns its place:

INSERT INTO review.job_packets
  (job_item_id, job_ref, contractor, job_date, verdict, summary, findings)
VALUES
  (2291, 'J-24117', 'Northgate Heating', DATE '2026-11-04', 'gaps',
   'Northgate is approved and insured to 15 Jan 2027. Their asbestos awareness '
   'certificate (UKATA-88412) expires on 28 Oct, a week before the job. Riverside '
   'requires IPAF and there is no IPAF certificate for Northgate in the library.',
   '[{"certificate_type": "Asbestos awareness", "finding": "expires before job",
      "expiry_date": "2026-10-28", "library_item_id": 1102},
     {"certificate_type": "IPAF", "finding": "missing"},
     {"certificate_type": "Gas Safe", "finding": "in date",
      "expiry_date": "2027-03-31", "library_item_id": 1184}]'::jsonb);

The coordinator now starts Monday with a list of sixty packets sorted by verdict, in whatever she already uses to read a table. We put a page on it in Power BI; a saved query in Excel works as well. The clear ones take a glance each, which leaves her day for the dozen with gaps.

Read-only database role and row-level security for an AI agent

The agent’s login is where the governance lives, so it is worth writing out in full. On Postgres, as an administrator:

CREATE ROLE packet_agent LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE ops_replica TO packet_agent;
ALTER ROLE packet_agent SET statement_timeout = '15s';

GRANT USAGE ON SCHEMA sharepoint TO packet_agent;
GRANT SELECT ON sharepoint.ops_jobs,
                sharepoint.ops_suppliers,
                sharepoint.ops_site_access,
                sharepoint.ops_site_access__required_certificates,
                sharepoint.ops_contractor_certificates
TO packet_agent;

GRANT USAGE ON SCHEMA review TO packet_agent;
GRANT INSERT (job_item_id, job_ref, contractor, job_date, verdict, summary, findings)
  ON review.job_packets TO packet_agent;

Against the replica the role is read-only. Its one write is an insert into the review table, and only into the columns it fills; it cannot set a decision or change a packet after writing it. It has no grant on sp_users, so it never sees an email address, and a new list replicated later stays invisible to it until someone grants it. There is no path from this role to SharePoint, because nothing in the database can call SharePoint.

If one replica holds more than one site, add row-level security on sp_site_id, which every replicated row carries. A firm that merges the Jobs list from its North and South operations sites into one table would give the North agent a policy on each table it reads:

ALTER TABLE sharepoint.ops_jobs ENABLE ROW LEVEL SECURITY;
CREATE POLICY north_only ON sharepoint.ops_jobs
  FOR SELECT TO packet_agent
  USING (sp_site_id = 'contoso.sharepoint.com,<site-collection-id>,<web-id>');

With the same policy on every table it reads, the joins above only meet rows from one site, so lookup ids never cross regions. SPSync’s loader owns these tables and owners bypass row-level security, so sync carries on as before. Every other login that reads the table, the Power BI one included, sees no rows until it has a policy of its own.

For the audit trail, have Postgres log what the role runs. With superuser rights, ALTER ROLE packet_agent SET log_statement = 'all' writes every statement to the server log. Azure Database for PostgreSQL and Amazon RDS both offer the pgaudit extension, which gives the same record in a form auditors are used to. Between that log and the packet row, you can show an auditor every query behind a decision and the time it ran.

To connect a model, point a Postgres MCP server, or your own code, at the packet_agent login. A database MCP server you already run is enough for this. SPSync’s own read-only spsync mcp serve can do the reading half, but it cannot insert the packet, so that write still needs a database MCP server that allows it, or your own code.

What does the person approving the packet do in SharePoint?

For J-24117 the coordinator sets the job’s Compliance column to On hold in SharePoint and emails Northgate for the renewed asbestos certificate and an IPAF card. When Northgate’s office uploads both to the library and fills in the type, number and expiry, the next sync brings them into the replica within minutes. The agent runs again, writes a clear packet, and she sets the job to Cleared. Her decision is recorded against the packet by a separate role that can update only the three decision columns:

CREATE ROLE packet_reviewer LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE ops_replica TO packet_reviewer;
GRANT USAGE ON SCHEMA review TO packet_reviewer;
GRANT SELECT, UPDATE (decision, decided_by, decided_at)
  ON review.job_packets TO packet_reviewer;

-- As packet_reviewer
UPDATE review.job_packets
SET decision = 'approved', decided_by = current_user, decided_at = now()
WHERE packet_id = 318;

The change in SharePoint is a person’s job today, because SPSync is read-only. We have designed the next step and put it on the two-way sync roadmap. An agent or app would propose a change into a proposals table in the database for a person to approve. SPSync would then apply each approved change to the SharePoint item, but only if the item has not changed since SPSync last read it. On a conflict SharePoint wins and the proposal is marked, and every apply is logged with the before and after values and the name of the person who approved it. It is designed and not shipped, so plan your process around the manual step.

Change request packets with the same pattern

The shape carries over to most approval work. A facilities change request is a good second case. Each item in the Change Requests list names an asset in a building and a planned window. The agent looks up the asset in the Assets list and checks its maintenance contract is live, then counts incidents against that asset in the last quarter from the Helpdesk list. It also lists any other approved change on the same building whose window overlaps this one:

SELECT o.title, o.window_start, o.window_end
FROM sharepoint.fm_change_requests c
JOIN sharepoint.fm_change_requests o
  ON o.building_id = c.building_id
 AND o.sp_item_id <> c.sp_item_id
 AND o.window_start < c.window_end
 AND o.window_end > c.window_start
WHERE c.sp_item_id = 77
  AND o.approval_status = 'Approved'
  AND o.sp_deleted_at IS NULL;

The packet goes to the change board’s review table, and the chair approves or holds it in the weekly meeting with the evidence already on screen. The roles and review table carry over from the contractor case, and so does the statement log.

Not every check needs this. If yours is one lookup on one list, a Power Automate flow with the Approvals connector does it inside Microsoft 365 with no database, and that is the better choice at low volume. The replica pays off when a check joins several lists and a library and has to be repeated dozens of times a week. To set it up, start with SharePoint to PostgreSQL or SharePoint to SQL Server, and see giving agents SQL tools over SharePoint data for the editions. An MCP server for SharePoint data that queries SQL covers connecting Claude or ChatGPT to the same kind of role, and library metadata as the catalogue an AI needs goes further into document libraries.