A caseworker checking FOI deadlines on a train has a laptop and, quite often, no signal. At Riverside Borough Council the requests, the councillor enquiries and the councillors themselves are SharePoint lists, which are no help offline. This walkthrough keeps all three in a single SQLite file with SPSync. The file refreshes every five minutes while the laptop is connected and answers queries when it is not.
A dashboard built on the lists has the usual problems. Once a list passes 5,000 items a query that filters or sorts on a non-indexed column can fail, refresh takes minutes, and each councillor comes back as an id. A Power Automate flow copies rows until a lookup or multi-value column changes, and an export script needs editing each time a column is added. That is why deadlines end up being reconciled by hand in a spreadsheet.
A file that keeps its schema
A spreadsheet export is a snapshot: it carries no schema, it forgets deletions and it never refreshes. SPSync builds SQLite tables from the lists, reads them in full once and then applies only the changes, so DB Browser for SQLite, DuckDB or a small local tool reads the file and leaves SharePoint alone. Lookups and people become key columns and multi-value columns become child tables. The SharePoint to SQLite page describes the destination itself.
Signing in and choosing a path on disk
Install SPSync and sign in with the democratic services account that already opens the
three lists. The sign-in is delegated, so SPSync reaches what that account reaches. The
permissions wizard shows an admin what Sites.Selected grants before they approve it, and
SPSync never writes to SharePoint. On the command line, sign-in is a device code with the
token kept in the OS keychain.
Then tick the lists and choose SQLite. The destination is just a file path. The schema preview lists every column, its type and the lookups before anything is written, and reporting views give each table a companion view that skips deleted rows and names people.
Requests, enquiries and councillors as tables
Each table is keyed on the SharePoint item id. The councillor on an enquiry becomes
councillor_id, pointing at sp_councillors. Category, a multi-value choice on Councillor
Enquiries, becomes sp_councillor_enquiries__category, one row per category per enquiry.
Caseworkers and assignees resolve to one shared users table, so a person joins the same
way in every query.
As the lists change, so does the file. A new SharePoint column is added on the next run. A deleted column remains in the file, so the deadline report that reads it keeps running. If a column changes type, SPSync adds a column for the new type and flags the old one, which leaves earlier values as they were. Key columns are created without foreign key constraints, so a sync never trips over load order.
The first run and what it wrote
The first run is a full read, after which each run is a delta. Tables are committed one transaction at a time, once each read finishes, so the dashboard never reads a half-written table. The events screen splits the totals by list, and you can open the file straight afterwards to check the 420 FOI and subject access requests, 260 councillor enquiries and 14 councillors.
Opening the file offline in DB Browser or DuckDB
DB Browser for SQLite opens the file with no setup. DuckDB reads it in place through its SQLite extension, without an import. Both work with the network switched off. The statutory deadline dashboard reads from the file too, and while the replication runs it is never more than five minutes old, which retires the spreadsheet rebuilt before every ICO return. WAL mode lets a reader keep querying while the next sync writes.
SQLite is a good fit for one analyst or a small local tool. For many people querying at once, point the same lists at a server database such as PostgreSQL. Related reading: reporting and analytics on SharePoint list data and a read replica for internal apps.
Refresh schedule and the reconcile
A five-minute interval keeps the file a few minutes behind SharePoint while the machine is
on. A full reconcile, every seven days by default, re-reads every item to confirm the file
and SharePoint agree. Start one by hand as a QA check, or to catch up after your own
machine has been off or Microsoft 365 has had an incident. The desktop app has a schedule
picker; on the command line, spsync run performs one run, and cron, a scheduled task or a
CI pipeline sets the pace.
Requests that are edited or withdrawn
The next delta run picks up an edit. A request deleted in SharePoint is soft-deleted: it stays in the file with a flag, the reporting view hides it, and an auditor can still read it in the raw table.
The same lists can also go to a server database, a warehouse or a search index, one replication per destination; the destinations page has the full list. If the one you need is missing, let us know. Book a call to talk it through, and we will set up a trial against your own tenant on the same call.
Connect to SharePoint and sign in
What you see: The connect wizard asking for the council site URL, a delegated sign-in, and then the permissions wizard's one-page pack.
Why it matters: Request and enquiry records leave SharePoint only under the access democratic services already holds, and SPSync changes nothing on the council site.
What it replaces: Asking IT for a tenant-wide read permission just to take three lists offline.
Pick the lists and the SQLite file
What you see: FOI and Subject Access Requests, Councillor Enquiries and Councillors ticked together, mapped onto one SQLite file with a preview of every column and type.
Why it matters: Enquiries reference councillors, so keeping both lists in one file is what lets a caseworker join them offline.
What it replaces: A hand-rolled export or a Power Automate flow per list, each one breaking separately when a column changes.
Tick FOI and Subject Access Requests, Councillor Enquiries and Councillors, name the replication and choose the SQLite destination, a path on disk.
Turn on reporting views, then use Preview schema to check every column, its type and which columns are lookups before anything runs.
Write the replication config for all three lists against a SQLite file in one file; the connection string is the path on disk.
spsync export-config --output public-sector.yaml --pipeline-name spsync-public-sector-sqlite --prefix sp --destination-kind sqlite --dataset-name main --client-id $SPSYNC_CLIENT_ID --connection-string-env SPSYNC_DEST_CONNECTION_STRING --site-url https://riversideboroughcouncil.sharepoint.com/sites/demo-public-sector --lists-by-name "FOI and Subject Access Requests,Councillor Enquiries,Councillors" --create-reporting-viewsCheck the Graph permissions and that the SQLite file is writable before the first run.
spsync doctor public-sector.yaml
Run the first sync
What you see: 420 FOI and subject access requests, 260 councillor enquiries and 14 councillors written, broken out by list on the run's own events screen.
Why it matters: After one full read, a request logged in SharePoint shows up in the file within a single five-minute cycle.
What it replaces: A deadline spreadsheet reconciled by hand.
Click Save and run, open the run's own events and watch the per-list row counts arrive: 420 FOI and subject access requests, 260 councillor enquiries, 14 councillors.
Set the schedule to five minutes so the three-list replication checks for changes twelve times an hour.
Run the replication; the terminal shows the full load first, then delta counts on later runs.
spsync run public-sector.yaml
See the result: lookups, child tables and joins
What you see: sp_councillor_enquiries joined to sp_councillors in a SQL query, and a new column added to sp_foi_requests automatically after a change in SharePoint.
Why it matters: Ranking councillors by open enquiries takes a join, and SQLite can run one where the SharePoint list view cannot.
What it replaces: An export to a spreadsheet every time a question needs a join SharePoint cannot answer on its own.
Open the destination file and browse sp_foi_requests, sp_councillor_enquiries and sp_councillors; each keeps the SharePoint item id as its key.
Run a join between sp_councillor_enquiries and sp_councillors on councillor_id to see which councillor is carrying the most open enquiries.
Query the SQLite file directly to confirm the join and the row counts.
sqlite3 "$SPSYNC_DEST_CONNECTION_STRING" "select c.title, count(*) from sp_councillor_enquiries e join sp_councillors c on e.councillor_id = c.sp_item_id group by c.title order by count(*) desc limit 10"
Use the file offline: DB Browser and DuckDB
What you see: The same file opened in DB Browser for SQLite and a closed-case count run from DuckDB on a laptop with no network connection.
Why it matters: A caseworker can answer a deadline question on a train, because every query runs against the file on the laptop.
What it replaces: A spreadsheet rebuilt before each ICO return and a dashboard that times out against the list.
Open the file in DB Browser for SQLite and browse v_sp_foi_requests, the reporting view that hides soft-deleted rows and resolves people to names.
Point DuckDB at the same file for analytics, with no import step.
Query the file from DuckDB, entirely offline.
duckdb -c "install sqlite; load sqlite; select count(*) from sqlite_scan('replication.db', 'sp_foi_requests') where status = 'Closed';"