A client portal on Supabase, fed from SharePoint lists

Supabase gives an app a Postgres database, a generated REST API and Realtime, but it has no way to pull rows out of SharePoint. Whitfield Reyes LLP keeps its Client Register, Matters and Conflicts Register there, and wants a client portal built on that data. This walkthrough uses SPSync to replicate the three lists into the firm’s Supabase project and then builds on the tables with Supabase’s own tools.

Writing the portal against the SharePoint list API is the alternative, and it means paginated calls, lookups that come back as ids and queries on non-indexed columns that can fail once a list passes 5,000 items. A Power Automate flow can copy rows until Matter Team, a multi-value person column, changes shape. Without either, someone exports to a spreadsheet and loads it by hand.

SharePoint for editing, Supabase for the app

The firm keeps editing in SharePoint. The portal reads from Supabase. SPSync reads the lists once in full and then follows them with delta queries, writing to Supabase Postgres on a schedule, so the app never calls SharePoint. The tables come from the lists: lookups and people become key columns, multi-value columns become child tables. The destination is covered on the SharePoint to Supabase page, and the wider pattern in building web and mobile apps on data the business keeps in SharePoint.

Connecting through Supabase’s pooler

Install the desktop app, or SPSync in your Azure for a tenant-wide setup, and sign in as a user who already opens the three lists. Sign-in is delegated, so SPSync sees what that user sees, and the permissions wizard lets an admin read what Sites.Selected grants before approving it. SPSync never writes to SharePoint.

When you add the Supabase destination, copy the pooler host from the project’s Connect dialog. SPSync connects through the transaction pooler by default, and always with TLS. That default is ours. The shared pooler is reachable over IPv4 on every Supabase plan, and a sync run opens a connection, loads and disconnects. Supabase’s connection guide describes transaction mode for serverless and edge functions, session mode for persistent backends on IPv4-only networks, and the direct connection for migrations. A run through the transaction pooler on a live Supabase project has not been tested yet, so if the first run cannot connect, switch the destination to the session pooler or the direct connection. The direct connection is IPv6 only unless the project has Supabase’s IPv4 add-on, which the SharePoint to Supabase page covers with the other connection details. On the command line, sign-in is a device code with the token held in the OS keychain.

Matters, clients and fee earners as Postgres tables

Rows are keyed on their SharePoint item id. The Client lookup on Matters becomes client_id, pointing at sp_clients. Matter Team becomes sp_matters__matter_team, one row per fee earner per matter, in place of a delimited string. Relationship partners and fee earners all resolve to sp_users, so the portal joins people the same way everywhere. New SharePoint columns appear on the next run; columns removed in SharePoint stay in Postgres, so the portal keeps working. Key columns have no foreign key constraints, which keeps load order from failing a sync. Add constraints yourself once the schema settles, if your app relies on them.

The first sync, seen in the table editor

Trigger the first run. It reads everything, and each run after it reads only what changed. The events screen reports 150 clients, 500 matters and 350 conflicts register entries. A table is committed in one transaction when its read completes, so the portal sees the previous data or the complete new data. Open Supabase’s table editor and filter sp_matters to one client’s open matters as you would in a spreadsheet. Joining sp_matters.client_id to sp_clients.sp_item_id is a plain SQL query, which the list cannot run because it stores the client as a lookup id.

The portal on the generated API and Realtime

Supabase generates a REST API over the replicated tables, and it regenerates after any schema change a later run makes. A small web app lists a client’s open matters from that endpoint without a backend of its own. Realtime can subscribe to sp_matters and show a changed row once a run writes it. Row Level Security is yours to configure for the roles the app reads with: SPSync writes the tables with its own role and leaves policies to you. A Power BI or Metabase report can read the same tables if the partners want one.

Refreshing every five minutes

On a five-minute interval each run reads only the changes, so the portal trails SharePoint by a few minutes at most. A full reconcile runs every seven days by default and re-checks every item. You can also run one on demand to confirm the two sides agree, or after an outage such as your own server failing or a Microsoft 365 incident. The desktop app and SPSync in your Azure set the schedule from a picker; with the command line, spsync run does one run and your scheduler decides how often.

Edits and deletions

An edit arrives with the next delta run. A matter deleted in SharePoint is soft-deleted in Supabase, kept and flagged, so the app decides whether to hide it or show it as closed. If you want Supabase to drop deleted rows outright, the command-line tool can hard delete.

SPSync can also send the same lists to other destinations, one replication each, all listed on the destinations page. If yours is not there, tell us. Book a call to go through your own lists; a trial in your tenant can be arranged on the call.