Operational visibility for a three-week rolling cadence

Context

Once the processing pipelines existed, a different problem surfaced. The operation runs on a weekly cadence, but each physician’s batch of patients takes about three weeks to move from exam to payment. That means at any given moment three separate cohorts are in flight simultaneously, each at a different stage, each with different outstanding tasks.

The state of all of it lived in folder structures, spreadsheets and memory. The daily question — what actually needs doing today? — took real effort to answer, and answering it wrong meant a batch quietly stalled.

The constraints

• It could not become another place patient data lives. A tracking tool that duplicated protected health information would have made the problem worse, not better.
• It had to read the archive that already exists — several years of week folders created by hand, with all the inconsistency that implies. Reorganising the archive to suit the software was not an acceptable answer.
• It had to be usable on a phone, from a doctor’s office, by someone who is not at a desk.

What I built

A local web application — a Flask app with a SQLite database, served on the local network — with six screens: a Today view for daily prep, a Kanban board by pipeline stage, a card detail view, a week view, a reference panel, and export.

The interesting parts are not the screens. They are the three decisions that came out of looking at the real data instead of the idealised version of it.

Week folders are matched by ISO week number, not by name

The obvious implementation regenerates the expected folder name from a date and looks for it. I checked that against the real archive: of 27 folders, one had a typo in its date. A name-matching implementation would have silently lost that week — and would have kept silently losing weeks every time someone mistyped one in future. Matching on the ISO week number the folder resolves to makes the whole class of error disappear.

Merges never overwrite hand-entered work

Some columns are filled in by a human — insurance details, comments — and some are generated. When new data merges into an existing tracker, records are matched on normalised office, date of service and name, and the human columns are preserved. Getting this wrong would destroy work quietly, which is the worst kind of bug: nobody reports it because nobody notices.

The database stores counts, never patient rows

The tracker knows a batch has 14 patients at stage 6. It does not know who they are. When an export is needed, patient rows are re-read from the source spreadsheet at export time and streamed straight to the browser without ever being persisted. I verified this empirically rather than assuming it: a real export carried 36 patient surnames while the database contained none.

Warnings and logs follow the same rule. The earlier command-line version printed row 7: Smith, John. This one prints the row number and the office. That was a deliberate downgrade in convenience.

Results

• One front door for daily operations — the day’s work is a screen, not a reconstruction.
• Nine pipeline stages tracked across three simultaneously active cohorts.
• Zero patient records stored, verified against a real export rather than assumed.
• Eight test files covering the merge, export and week-matching logic — the three places where a silent failure would cost the most.

What I’d do differently

• I’d have run a code review earlier. One review pass found five issues, two of which were silent data loss in the export path. They’d been there a while. Finding them was good; finding them in week one would have been better.
• I built the processing tools and the tracker as separate applications, then had to merge one into the other. The billing pipeline originally lived in the desktop toolkit and is now being retired into the tracker, because operationally it belongs where the daily work happens. I should have asked “where does the user go to do this?” before asking “what does this need to compute?”
• I’d write the migration path first next time. Retiring a pipeline someone depends on is more delicate than building it was.

Stack

Python · Flask · SQLite · Jinja2 · openpyxl · hand-written CSS · PyInstaller · Claude AI (development, not runtime)

Client and patient details are omitted throughout. No code, screenshots or repository access are published — this system operates on protected health information and remains the property of the company.