A twenty copy Excel workbook became a live capacity planner

The problem

Our operations lead planned which doctor works where across Rochester, Buffalo and Syracuse in one Excel workbook. Every week was a block of formulas pasted under the last one. Twenty copies of the same week, and when the board ran out you pasted another.

It worked better than it had any right to. It also failed quietly. The row that counted Rochester’s doctors was a COUNTIF over rows 6 to 11, so a doctor added on row 12 did not exist. Typing over a cell to move someone for a day deleted its formula, so that cell forgot about time off. And none of it knew what Denticon, our practice system, actually had booked.

The real cost was people. Last minute coverage calls, and a doctor slammed at one office while another office had a free chair.

What I built

One forward view of demand, capacity and capability for all three offices. It reads our Denticon mirror every minute through the CDF, my service that holds the Denticon credentials, and it has no way to write back. A person still makes every real change in Denticon. The planner shows who is where, which day has patients and no doctor, who is overloaded, and who could cover.

All the planning math lives in one pure TypeScript package with no database, no network and no clock. The same code runs in the minute sync on the server and in the browser when someone asks what happens if a doctor takes Thursday off. Open the drawing to see every part.

The sprint

On September 11 I took the workbook apart cell by cell, wrote the spec, and sent our operations lead eleven questions. Each one already had a default coded, so nothing waited on an answer. The first commit went in the same day.

v0.1.0 was tagged at 3:27 PM on September 28 and running in production by 3:48 PM. By 6:56 PM the People screen, the Bandwidth screen, the Coverage board and the Projector were live too. The first full read of live Denticon data landed at 5:34 the next morning. On October 1 a release went in at 4:53 AM. Later that day a practice director wrote, word for word, “I need an undo button!” It shipped as v0.38.0 at 3:34 PM the same day.

The hard parts

Denticon outranks the plan. Live data broke two assumptions within hours. Denticon books the doctor as the provider on visits in the assistant’s chair too, so one Buffalo doctor stacked three rows deep. And Syracuse showed “Need Dr” on a day Denticon had two doctors booked there. The roster said one thing, the bookings said another, and the bookings were right. So every day now runs one ordered chain: an override, then time off, then a Denticon block note, then a booking, then the doctor calendar, then the weekly pattern. Each cell says which rule placed it.

The bug that could have cancelled an office. A full read upserts what the CDF lists, then cancels what the planner holds that the CDF no longer lists. That is how a cancelled visit disappears. It is also a loaded gun. I found an older CDF route that dropped an unknown office code instead of refusing it, which would have read as “this office has no appointments” and swept its whole calendar away. So the routes refuse unknown offices, a full read writes nothing unless every 31 day window answered, and the sweep counts before it cuts:

// apps/web/lib/import/syncBatch.ts
if (stale >= batch.guard.min && stale > batch.guard.maxShare * active) {
  throw new MassCancelError(locationCode, stale, active);
}

More than half of an office’s rows and at least 20 of them, and the whole read rolls back. The error names only an office and two counts.

Patient names stay out of logs. The logger describes a failed query by its Postgres code, constraint and table only, and an integration test proves no log line carries a patient style name.

What I said no to

Writing to Denticon. The contract every appointment source implements has only read methods, and a type level test fails the build if anything shaped like a write appears.

Generated advice on who to place. Cover suggestions rank people on five plain rules: can do the work, will work at that office, already there, provably free, preferred assistant free too. Ties break on person id. Every reason on screen comes from a rule our operations lead can read.

Guessing from free text. Staff type notes into Denticon blocks. The reader acts only on a note that names exactly one doctor and says plainly out or here. On my local copy it acted on about one noted block in ten. I would rather miss a note than move a doctor on a guess.

Where it landed

The operations lead and the office directors plan from it every day. Each tab answers one question: Calendar, Coverage, Needs action, Bandwidth, and since October 20 a Room Board where people and blocks drag between rooms with Undo. A 7 AM email goes to the office directors Monday to Friday with what needs action.

It cost $27 to run from October 1 to 28. The database is nine tenths of that. Cloud Run, which serves the app and runs the sync every minute, cost $1.29.

I have not measured hours saved. I do not have a before number for how long the workbook took each week. Office hours for each site and categories for 44 appointment types are still waiting on answers.

What I would keep: write the old system’s rules down cell by cell, make the new engine prove it reproduces them, and get onto live data on day two. And anywhere software can cancel something at scale, make it refuse first and explain itself second.