Matthew Kim
← All projects

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.
Coverage ending in

sample data · as of Oct 1, 2026

ClientEndsDays
Rosa Quillfeather10/05/20264
Ada Brightwater10/18/202617
June Okafor-Lisle11/09/202639

Unreadable end date: check by hand

  • Theo Marchbank: “TBD after AEP”
  • Wren Calloway: “11/31/2026”
  1. refresh: 9 rows copied
The renewals rule and the refresh guard, running in your browser on generated sample clients. The copy is read-only: staff edit the sheet, and the app only ever reads it.
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.