Supabase and SharePoint lists for an app and an AI agent

21 October 2026

Tags: Relational databasesAIDesktopCommand line

You are building a field-ops app on Supabase. Engineers open it on a phone in a plant room to check the hazards on an asset before they touch it, then add notes when the job is done. The assets and jobs they see live in SharePoint. The office keeps an Assets list and a Jobs list on the operations site, and the schedulers have edited those lists every day for years. Those lists are staying where they are.

The first version calls Microsoft Graph from an edge function. It works on the test site and then meets the real Jobs list, which passed the 5,000-item view threshold some time ago. A filter on Team fails unless the column is indexed. The Asset lookup comes back as AssetLookupId with no tag. Resolving it, and the engineer’s name, means more calls, and on a weak mobile signal each one is felt. Then the operations manager asks for something new: she wants to ask Claude which pumps keep breaking, and she would like the answer to match what the app shows.

That last request is the useful one. If the app and the agent read the same Postgres tables, you build and secure the data layer once, and the two can never disagree about what the list said at the last sync.

A field-ops app on Supabase with its data in SharePoint

Supabase is a sensible home for this app. It gives you Postgres, a generated REST API through PostgREST, auth, and row-level security, and the supabase-js client covers the rest. It has no way to read a SharePoint list, which is where SPSync comes in.

SPSync reads the lists you choose through Graph and keeps a copy current in your Supabase database on a schedule. It reads and never writes. The office goes on editing in SharePoint while the app and the agent both read Postgres. Graph is called by one process on a timer, and opening the app never causes a request to SharePoint.

A client portal on Supabase covers the destination setup and the table editor in detail. This post assumes the replica exists and spends its time on the parts that bite once two kinds of reader share it.

Replicating the Assets and Jobs lists into Supabase

In the desktop app you connect the operations site, tick Assets and Jobs, and add the Supabase connection string from the project’s Connect dialog. From the command line it is one config file:

spsync export-config --output fieldops.yaml --pipeline-name fieldops \
  --prefix fo --destination-kind postgres \
  --connection-string-env SPSYNC_SUPABASE_URL --dataset-name sharepoint \
  --client-id $SPSYNC_CLIENT_ID \
  --site-url https://contoso.sharepoint.com/sites/operations \
  --lists-by-name Assets,Jobs
spsync run fieldops.yaml

The --dataset-name matters more than it looks. SPSync writes into a Postgres schema called sharepoint, and stages changes in sharepoint_staging, away from public. On Supabase the default privileges in public grant the API roles access to new tables, so replicated tables created there would be reachable through the API from the moment they exist. In a schema of their own they get no grants at all until you give them.

After the first run you have sharepoint.fo_assets and sharepoint.fo_jobs, one row per item, keyed on sp_item_id. The Asset lookup on Jobs becomes asset_id, which joins to fo_assets.sp_item_id, with asset_value beside it holding the tag. The Engineer person column becomes engineer_user_id, a uuid that joins to the shared sp_users table. Hazards, a multi-choice column on Assets, becomes a child table, sharepoint.fo_assets__hazards, with one row per asset and hazard. Every row carries sp_modified and sp_synced_at, and a deleted item stays with sp_deleted_at set until you decide what the app does with it.

SPSync’s connection owns these tables, because it created them. That detail does a lot of work in the next section.

Row-level security per team on replicated tables

The rule the app needs is short. An engineer sees the assets of the teams they belong to, and the jobs on those assets. Team membership is app data, so it lives in a table the app owns, keyed on the Supabase user:

create table public.team_members (
  user_id uuid not null references auth.users on delete cascade,
  team    text not null,
  primary key (user_id, team)
);
alter table public.team_members enable row level security;
create policy "read own memberships" on public.team_members
  for select to authenticated
  using (user_id = (select auth.uid()));

The policies on the replicated tables come next. Run them in the SQL editor as the same role SPSync connects with, since only a table’s owner can enable row-level security on it:

alter table sharepoint.fo_assets enable row level security;
alter table sharepoint.fo_jobs enable row level security;
alter table sharepoint.fo_assets__hazards enable row level security;

create policy "team reads assets" on sharepoint.fo_assets
  for select to authenticated
  using (team in (select tm.team from public.team_members tm
                  where tm.user_id = (select auth.uid())));

create policy "team reads jobs" on sharepoint.fo_jobs
  for select to authenticated
  using (asset_id in (select a.sp_item_id from sharepoint.fo_assets a));

create policy "team reads hazards" on sharepoint.fo_assets__hazards
  for select to authenticated
  using (sp_item_id in (select a.sp_item_id from sharepoint.fo_assets a));

The jobs policy looks too simple at first. Its subquery on fo_assets runs as the signed-in user, so the assets policy filters it first and a job shows only when its asset does. A job with no asset set falls out. If every job has to appear somewhere, make Asset a required column in the Jobs list. Wrapping auth.uid() in a select is Supabase’s own advice in its row-level security performance notes, and it lets Postgres evaluate the call once per query.

Because SPSync’s role owns the tables, the policies never affect the sync. Postgres skips row-level security for a table’s owner unless you add force row level security, and if you ever do, add a policy for the sync role as well. The app reads views in public, created with security_invoker so the policies are checked against the person asking:

grant usage on schema sharepoint to authenticated;
grant select on sharepoint.fo_assets, sharepoint.fo_jobs,
                sharepoint.fo_assets__hazards to authenticated;
grant select (id, display_name) on sharepoint.sp_users to authenticated;

create view public.jobs with (security_invoker = true) as
select j.sp_item_id   as job_id,
       j.title        as job,
       j.job_type,
       j.status,
       j.due_date,
       j.asset_id,
       j.asset_value  as asset_tag,
       u.display_name as engineer,
       j.sp_created   as raised_at,
       j.sp_synced_at as synced_at
from sharepoint.fo_jobs j
left join sharepoint.sp_users u on u.id = j.engineer_user_id
where j.sp_deleted_at is null;

create view public.assets with (security_invoker = true) as
select a.sp_item_id   as asset_id,
       a.title        as asset_tag,
       a.asset_type,
       a.site,
       a.team,
       a.last_serviced,
       a.sp_synced_at as synced_at
from sharepoint.fo_assets a
where a.sp_deleted_at is null;

revoke all on public.jobs, public.assets from anon, authenticated;
grant select on public.jobs, public.assets to authenticated;

The revoke line is there for the same default-privileges reason as before. Views created in public pick up Supabase’s default grants to the API roles, as tables do, and we would rather state the grants we mean than rely on the underlying tables to refuse.

A third view over fo_assets__hazards follows the same pattern, so the hazards screen gets the same team filter. The column grant on sp_users keeps email addresses and logins out of reach. Every signed-in user can still see every engineer’s display name, which is usually fine inside one company. Where it is not, filter sp_users through a view of its own. From the app, a team’s open jobs are one supabase-js call:

const { data: jobs } = await supabase
  .from("jobs")
  .select("job_id, job, status, due_date, asset_tag, engineer, synced_at")
  .eq("status", "Open")
  .order("due_date");

How do the app and an AI agent read the same tables?

The operations manager’s question cuts across teams, so her agent needs a wider view than any engineer has. We give it a role of its own and a schema of views it can read, and nothing else:

create role fieldops_agent login password 'change-me';
alter role fieldops_agent set default_transaction_read_only = on;
alter role fieldops_agent set statement_timeout = '15s';

create schema agent;
grant usage on schema agent to fieldops_agent;

create view agent.assets as
select sp_item_id as asset_id, title as asset_tag, asset_type, site, team,
       last_serviced, sp_synced_at as synced_at
from sharepoint.fo_assets where sp_deleted_at is null;

create view agent.jobs as
select sp_item_id as job_id, title as job, job_type, status, asset_id,
       due_date, completed, sp_created as raised_at, sp_synced_at as synced_at
from sharepoint.fo_jobs where sp_deleted_at is null;

grant select on agent.assets, agent.jobs to fieldops_agent;

These views are ordinary owner-rights views, so they read the tables as their owner does and row-level security does not filter them. That is the intended difference from the app’s views. The agent holds SELECT on two views and owns nothing. With no grant on the sharepoint schema it cannot read the tables directly or see anyone’s email. Its read-only default and timeout are guard rails a session could change, while the grants are the boundary.

Any Postgres MCP server will do, and spsync mcp serve, SPSync’s own read-only server over the replica, is another route. We use DBHub from Bytebase with its SQL tool set to read-only, which refuses anything but reads before the statement reaches the database. Supabase’s pooler accepts custom roles with the project reference appended to the user name, so DBHub’s dbhub.toml looks like this:

[[sources]]
id = "fieldops"
dsn = "postgres://fieldops_agent.abcdefghijklmnop:${FIELDOPS_AGENT_PASSWORD}@aws-0-eu-west-2.pooler.supabase.com:5432/postgres?sslmode=require"

[[tools]]
name = "execute_sql"
source = "fieldops"
readonly = true
max_rows = 200

The Claude Desktop entry starts DBHub with the full path to that file:

{
  "mcpServers": {
    "fieldops": {
      "command": "npx",
      "args": ["-y", "@bytebase/[email protected]", "--config", "/path/to/dbhub.toml"],
      "env": {
        "FIELDOPS_AGENT_PASSWORD": "change-me"
      }
    }
  }
}

An earlier version of this post used Postgres MCP Pro. Its current release would not install on 23 October 2026, because it requires a version of its SQL parser that PyPI no longer has, so uvx postgres-mcp stops with a resolver error.

The file holds a password, so it stays on the manager’s own machine. If the agent should live inside the app instead, an edge function can hold the same connection string as a secret. Do not reuse the SUPABASE_DB_URL the function runtime provides, because that one connects as the database owner.

Then she asks, “Which pumps on the North team have had three or more reactive jobs in the last 90 days, and when were they last serviced?” The model reads the two view definitions and writes:

select a.asset_tag, a.site, a.last_serviced,
       count(*)         as reactive_jobs,
       max(j.raised_at) as latest_job,
       min(j.synced_at) as data_as_of
from agent.jobs j
join agent.assets a on a.asset_id = j.asset_id
where a.asset_type = 'Pump'
  and a.team = 'North'
  and j.job_type = 'Reactive'
  and j.raised_at >= now() - interval '90 days'
group by a.asset_tag, a.site, a.last_serviced
having count(*) >= 3
order by reactive_jobs desc;

A few rows come back. The database did the counting and the date arithmetic, and SharePoint received no request at all. We put a line in the project’s instructions telling the model to quote data_as_of with every answer, so the manager knows she is reading the replica as of the last sync.

For an assistant inside the engineer’s app, use the user’s session against the public views instead. Row-level security then trims the answers to the user’s team with no extra work.

Where the app writes, and where it must not

Engineers write plenty during a visit, from notes to the parts they used. That is app data, and it goes into tables the app owns, keyed on the replicated job id:

create table public.job_notes (
  id         bigint generated always as identity primary key,
  job_id     bigint not null,
  body       text not null,
  written_by uuid not null default auth.uid() references auth.users,
  written_at timestamptz not null default now()
);
alter table public.job_notes enable row level security;
create policy "notes on visible jobs" on public.job_notes
  for all to authenticated
  using (job_id in (select j.sp_item_id from sharepoint.fo_jobs j))
  with check (written_by = (select auth.uid())
              and job_id in (select j.sp_item_id from sharepoint.fo_jobs j));

We keep job_id as a plain bigint with no foreign key into sharepoint.fo_jobs. The replicated table belongs to the sync, and an app row should never stand in the way of a row the sync needs to change or remove.

What the app must never do is write to SPSync’s tables. None of the grants above allow it, so keep it that way. An edit made to fo_jobs in Postgres changes nothing in SharePoint, and it lasts only until that item next changes in the list, when the sync writes the SharePoint values over it. Until then the app and the office are looking at different answers, and neither side knows.

Some changes do belong in SharePoint. An engineer finds the asset was replaced, or a job needs moving to next week. For those we add a change request table:

create table public.job_change_requests (
  id             bigint generated always as identity primary key,
  job_id         bigint not null,
  field          text not null check (field in ('status', 'due_date', 'asset')),
  proposed_value text not null,
  reason         text,
  requested_by   uuid not null default auth.uid() references auth.users,
  requested_at   timestamptz not null default now(),
  decision       text check (decision in ('applied', 'rejected')),
  decided_at     timestamptz
);
alter table public.job_change_requests enable row level security;
create policy "request on visible jobs" on public.job_change_requests
  for insert to authenticated
  with check (requested_by = (select auth.uid())
              and job_id in (select j.sp_item_id from sharepoint.fo_jobs j));

A scheduler works through the open requests, makes each change in the Jobs list, and marks the request applied. The next sync brings the new value into fo_jobs, and the app shows it. This is slower than writing straight to SharePoint. For most schedules the delay is a fair price, because every change still goes through the people who own the schedule.

SPSync has no write path to SharePoint today. Staged write-back, where a change in a table like this one is approved and then written to the list, is designed and on the two-way sync roadmap. Until you see it there as shipped, the change request table is the pattern.

How current is the replica, and what if the list changes?

On a short schedule, such as every five minutes, each run reads only the items that changed, and each table is committed in one transaction, so a reader sees the previous data or the new data and never half a load. The synced_at column in every view tells the app and the agent how old a row is, and the app shows it in a footer on every list.

A reconcile compares every item with SharePoint, weekly by default. Treat it as a QA check you can also run on demand with the command-line tool, for instance after an outage on your side or a Microsoft 365 incident, to confirm the two sides agree before anyone relies on the numbers.

Lists change shape, and the office will add a column without telling you. A new SharePoint column becomes a new column in the replicated table on the next run, empty for older rows. Our views name their columns, so the new column does not appear in the API or to the agent until you add it with create or replace view.

A new multi-choice column arrives as a new child table, which has no grants and no policy, so it stays invisible until you add both. A removed column stays in Postgres, so a view that reads it keeps working. If someone changes a column’s type, SPSync writes the new values to a {column}_v2 column beside the old one and logs a warning naming both. Point the view at the new column, or coalesce the pair while the history matters. Renaming a column’s display name in SharePoint changes nothing in Postgres, because the table uses the column’s internal name.

When to use something else

If engineers must change SharePoint the moment they press a button, the replica will not do that for you. A Power Automate flow or a Graph call from an edge function, with its own narrow permission on the one list, is the right tool there, and it can sit alongside the replica for reads.

If the whole team lives in Microsoft 365 and the lists are small, a Power Apps canvas app on the list may be all you need. When the office is willing to edit in your app, the cleanest design is to move the data into Supabase for good and retire the lists. SPSync is for the common case in between, where SharePoint stays the place the business edits and everything else reads a copy.

For vector search over the same tables, see RAG on SharePoint lists. The agent pattern on other databases is in an MCP server for SharePoint data that queries SQL, and the destination itself is on SharePoint to Supabase. The wider case for building apps this way is web and mobile apps on SharePoint data. If you want to walk through your own lists, book a call.