Insurance Client Data Management System

Replaced a binder-based workflow for ~2,000 client records with a searchable, exportable web system

The client search page filtered to Keystone plans in 2026, showing 8 results — Search, filtered to one carrier's 2026 plans. Every client here is generated sample data from the app's demo mode.

Stack: Python, Flask, SQLite, Google Sheets API (gspread), Google sign-in (Authlib), deployed on Render
Timeline: first built June 2025; rebuilt September 2026
What it replaced: a binder-based (physical/paper) workflow covering ~2,000 records
How it works: the agency's Google Sheet stays the source of truth, where records are entered and edited; the app syncs it into a read-only SQLite copy for fast search, sorting, filtering and CSV export, and refuses to sync if the sheet comes back empty rather than wiping the copy
Renewals: clients whose coverage ends in the next 30, 60 or 90 days, soonest first, with any unreadable end date flagged separately so no client is silently missed
Plans by year: any <year> Plan column in the sheet is picked up automatically, so a new enrollment season needs no code change
Access and audit: Google sign-in limited to an allow-list of emails; every sign-in (refused ones included), search, export and refresh is logged
Testing and data safety: a pytest suite runs on every push in GitHub Actions, which also fails the build if a database, credentials or .env file is ever committed; a demo mode runs on generated sample clients and never opens the real data

Who it's for

First Senior Services, my dad's insurance company, which keeps records for roughly 2,000 clients. Before this, those records lived in physical binders. Now looking up a client's information and storing a new client's data both happen in one searchable place instead of paging through binders.

Simple by design

Out of the binders, the records now live in a Google Sheet, and staff add and edit clients right there. The app doesn't replace the sheet: it syncs it into a read-only SQLite copy for fast search, sorting and export, and never writes back. So the part people use every day didn't change, and if a sync ever goes wrong, the sheet is untouched.

Diagram: agency staff edit the Google Sheet, which syncs into a read-only SQLite copy behind a sign-in-protected Flask dashboard — How it fits together. The Google Sheet stays where records are entered; the app keeps a read-only copy for searching.

The renewals page listing coverage ending in the next 60 days, with one unreadable end date flagged — Renewals due in the next 60 days. A client whose end date reads "TBD after AEP" is flagged instead of silently dropped.