SharePoint lists in PostgreSQL, ready for Metabase and apps

Northfield Retail Group decides its product range in SharePoint and measures it in PostgreSQL. Buyers keep Product Range, Store Audits and Suppliers as lists because that is where the store teams edit them; the sales volumes those decisions depend on sit in a database. This walkthrough copies the three lists into PostgreSQL with SPSync, so a Metabase dashboard, an internal stock-check app and an AI agent can all read one set of tables.

Building the dashboard straight on the lists is where it goes wrong. Once a list passes 5,000 items, a query that filters or sorts on a non-indexed column can fail, Metabase refreshes slow to minutes, and the supplier on each product comes back as an id. A Power Automate flow copies rows until someone changes a lookup or a multi-value column. A hand-built loader works, but its schema has to be edited every time the range team adds a column.

What changes when Metabase reads a replica

SharePoint’s list APIs serve the SharePoint pages well and a dashboard that pulls a whole list on a timer badly. SPSync reads the lists once in full, then follows them with delta queries, and writes the result to PostgreSQL tables on a schedule. Metabase and the app talk to PostgreSQL only. SPSync creates the schema from the lists: lookups and people become key columns, multi-value columns become child tables, and every run after the first fetches only what changed. The SharePoint to PostgreSQL page has the destination detail.

Signing in with the store ops account

Install the desktop app (or SPSync in your Azure for a tenant-wide setup) and sign in with the account that already opens Product Range, Store Audits and Suppliers. The sign-in is delegated, so SPSync can see those lists and nothing the account cannot. Before an admin approves Sites.Selected, the permissions wizard sets out what it grants and why, and SPSync never writes to SharePoint. On the command line the sign-in is a device code, and the token is cached in the operating system’s keychain.

Choosing the lists and the PostgreSQL connection

Tick the three lists, name the replication and pick PostgreSQL. The destination takes a standard connection string, so any PostgreSQL server you can reach will do. Preview the schema first to see each column, its type and which columns are lookups. With reporting views switched on, each list also gets a view that drops soft-deleted rows and shows people by name.

How the retail lists look as tables

Each list becomes a table keyed on the SharePoint item id, which tells every later run which row to update. The supplier lookup on Product Range becomes supplier_id, pointing at sp_suppliers. Checklist Areas, a multi-value choice on Store Audits, becomes the child table sp_store_audits__checklist_areas with one row per area per audit, so no query has to split a delimited string. Every person on every list resolves to one row in sp_users, so a supplier account manager and a store inspector join the same way.

The schema follows SharePoint as it changes. A column added in SharePoint appears in the table on the next run, and a removed column stays where it is, so a dashboard question that uses it still works the following morning. If a column’s type changes completely, SPSync adds a second column for the new type and flags the old one, keeping the history readable. Key columns are written without foreign key constraints, so load order never fails a run.

Running the first sync

The first run reads everything and later runs read deltas. The run’s events screen splits the totals by list: 260 product range items, 420 store audits and 25 suppliers. Each table is committed in a single transaction when its read finishes, so Metabase never queries a half-written table.

Metabase, a stock-check app and an agent on the same tables

Metabase connects to the replica as an ordinary PostgreSQL database, and the store compliance league table, which used to be rebuilt from an export before each regional call, now updates every five minutes. An internal app reads v_sp_product_range to check stock and range status before a buyer places an order, and a certificate expiry alert reads the suppliers view for anything due in the next thirty days. An Azure AI Foundry agent with a read-only SQL tool can answer which stores fell below a compliance score this month. For a comparison of dashboard tools, see Tableau, Looker and Metabase on SharePoint data.

Five-minute deltas and a weekly check

Set a five-minute interval and each run reads only what changed, keeping PostgreSQL a few minutes behind SharePoint at most. A full reconcile runs every seven days by default and re-checks every item, and you can also start one by hand as a QA check or after an outage such as your own server going down or a Microsoft 365 incident. The desktop app and SPSync in your Azure have a schedule picker. On the command line, spsync run performs one run, and cron, a scheduled task or a CI pipeline sets the interval.

When a store audit is edited or deleted

The next delta run picks up an edited item. A deleted item stays in its table with a deleted flag, and the reporting view leaves it out, so the league table matches SharePoint while an auditor can still find the audit in the raw table.

The same lists can go to more than one destination, one replication each, and the destinations page lists every database, warehouse, lake and search index SPSync supports. If the one you need is missing, tell us. Book a call to walk through your own lists; trials are arranged on that call, in your tenant.