Physical Therapy Scheduling Intelligence Tracker
Designed and built an offline scheduling intelligence tool that turned recurring clinic, employee, and timecard Excel exports into a self-updating analytics database for a 12-clinic outpatient physical therapy operation.
This project shows how I approach messy operational healthcare data under hard deployment constraints and still ship a tool a non-technical user can run on a locked-down work machine.
Project frame
Internal analytics tool, offline dashboard, and owned local data product.
Role
Sole designer and builder: discovery, data architecture, development, UX, documentation, and validation.
Context
Multi-clinic outpatient physical therapy operation: 12 clinics, roughly 100 staff, weekly scheduling data and timecards.
Deliverable
One self-contained offline HTML file with IndexedDB and inline SVG. No installs, no dependencies, no admin rights.
The problem
Disconnected scheduling reports in Excel.
A multi-clinic physical therapy office had key scheduling and staffing KPIs trapped in separate Excel exports with no reliable way to join them.
The work was not "just build a BI dashboard." The real problem was to turn recurring, disconnected scheduling exports into linked records a user could trust, refresh, and explore without IT help.
Discovery
Timecards became the bridge table.
Two recurring scheduling exports reconciled perfectly: both summed to 40,692 visits across 23 weeks, proving the underlying numbers were trustworthy. But they could not be joined in a useful way because they only shared a week date, which was not enough to connect employees to specific clinics and days.
The missing link was the timecard export. Timecards carried Employee + Location + Date on every row, making them the natural bridge table between clinic performance data and employee performance data.
After normalizing name formats and stripping title noise, 100% of timecard employees matched the scheduling roster. The joined dataset also revealed that roughly 1 in 5 employees worked across multiple clinics, which is common in outpatient rehab staffing.
That meant any naive model that assumed one "home clinic" per employee would systematically misrepresent staffing reality and scheduling patterns.
The timecard-driven bridge justified a relational model.
Proof points and data scale
A small local file, real operational scale.
Data volume
About 1,769 visits per week, or about 147 visits per clinic per week, across 23 weeks of historical scheduling and timecard data.
Entity resolution
100% employee match from timecards to the scheduling roster after normalization across inconsistent names, titles, and credentials.
Operational truth
Roughly 20% multi-clinic staff confirmed by the bridge table, avoiding false single-site assumptions.
Owned deliverable
One offline HTML deliverable the user can run from disk, with 0 third-party libraries in the final file.
Estimated operational impact
A multi-hour reconciliation loop became drag-and-drop ingest.
Across 23 weeks and 40,692 visits, the tool consolidated recurring Excel reports into a single linked local analytics database. That likely reduced weekly reporting work from a multi-hour manual reconciliation process to a few minutes of drag-and-drop ingest and review.
In a typical 12-clinic outpatient PT environment, this is plausibly 2-6 admin hours saved per weekly refresh, depending on the baseline level of manual filtering, cross-checking, duplicate handling, and split-site staff validation.
Operational estimate, not an audited financial KPI.
The estimate is based on specific manual steps the tool eliminated: re-exporting filtered views, rebuilding pivot tables, matching employee names and locations by hand, double-checking totals between clinic and employee views, and maintaining historical views in spreadsheets.
Constraints
Offline, locked-down, no installs.
The deployment environment dictated the architecture. An early Python + library prototype validated the data logic, but it failed the deployment constraints. The final browser-native implementation kept the same modeling and validation while removing every environmental blocker.
No admin rights
The tool had to run on a locked-down workstation without installing Python, R, desktop BI, or helper packages.
No external dependency
No package registry, npm access, guaranteed internet connectivity, or SaaS analytics platform could be assumed.
User-owned refresh
The handoff needed to support recurring weekly scheduling and staffing analytics for a non-technical operations user.
The better answer was browser-native: one HTML file, no install, no dependency chain, no subscription platform, and no reliance on external servers.
What I built
Offline scheduling analytics in one HTML file.
The user drags the recurring Excel exports onto the page. The tool parses the files, normalizes the records, and upserts data into a browser database that grows additively as new weeks are dropped. Re-adding a file updates existing records instead of duplicating them.
Clinic and employee views
Separate scheduling performance views with date-range filtering, search across clinics/employees/locations, and cross-filters like "show everyone who worked at this clinic."
Exploratory controls
Sortable and hideable columns, visit-weighted summary cards, and row-level drill-downs into individual clinic and staff histories.
Inline SVG charts
Trend, ranking, and scatter charts rendered as inline SVG with no charting libraries.
Portable backup
Compressed backup and restore so a user can export and rehydrate the local dataset.
Failure-state safety
Duplicate file drops, mismatched export combinations, and backup/restore misuse fail with guided messaging instead of quiet corruption.
Non-technical UX
First-run guidance, a KPI glossary written in operational language, in-app help, and clear safety rails.
Technical highlights
Data modeling and browser-native engineering.
Data modeling
- Star schema with clinic-week and employee-week fact tables.
- Location, employee, and week dimensions.
- Derived timecard bridge so clinics and staff join through a clean analytics model instead of ad-hoc spreadsheet formulas.
ETL and normalization
- Entity resolution across inconsistent name formats.
- Title and credential stripping.
- Sunday-aligned week buckets and zero-padded location codes so joins stay stable across exports and time.
Browser-native data engineering
- Custom XLSX/ZIP parsing built on web-standard browser APIs.
- IndexedDB persistence, so the tool keeps state locally without installed software, a shared drive, or a server.
- Compressed backup/restore through platform-native compression streams.
Zero-dependency delivery
- Trend, ranking, and scatter charts hand-rendered as inline SVG.
- No charting libraries or third-party packages in the final file.
- Lean, portable, dependency-free HTML deliverable.
Validation and regression safety
Every interactive path reconciled back to the same total.
I first built the normalization logic in Python to establish ground truth for joins, week buckets, and KPIs. I then ported that logic to JavaScript and verified that both implementations produced identical results across the full 23-week dataset.
Rendered the app paths. The headless mock-DOM harness exercised the main dashboard, subviews, chart types, filters, search, and date-range math.
Tested persistence. Backup creation and restore flows were included in the regression checks.
Tested live ingest. Drag-and-drop ingest of all recurring reports was exercised before handoff.
Reconciled the number. Every path landed back on the same 40,692 visit total.
That test harness turned every interactive path into a regression check, making handoff safer for a non-technical user who still needed strong trust in the numbers and scheduling analytics.
Outcome and reusable pattern
Four disconnected exports became one linked scheduling intelligence system.
The tool runs fully offline, requires nothing to install, and gives a non-technical operations user a repeatable way to keep building the dataset as new weekly reports arrive.
The business change was visibility: clinic, employee, and cross-site staffing patterns became explorable from sources that originally could not talk to each other.
The same architectural pattern can be reused anywhere recurring reports exist but admin rights, software installs, and external platforms are off the table.
Owned by the user, not rented from a platform.
Stack
Small by design.
Vanilla JavaScript, web-standard browser APIs, IndexedDB, custom XLSX/ZIP parser, inline SVG, Python validation, and a single-file offline-first HTML deliverable.
Proof copy note
Architecture and outcome, not source-code disclosure.
This case study describes the architecture, constraints, validation, and outcomes without exposing private operational files, patient data, employee data, credentials, or proprietary source material.