Shelterluv data imports · removing myself from the critical path

The problem

Why it was only ever me

A shelter switching to Shelterluv moves every animal they've ever taken in, every adoption, every vaccination, and every behavioral note that tells a volunteer which dog not to put a hand near. Calling it a spreadsheet undersells it by a lot.

One real migration ran to 11,375 animals and produced 73,300 memos. If a medical record ends up in the wrong year, nobody gets an error. They find out two years later when they can't answer a question about an animal in front of them.

That work ran on a set of Excel workbooks driven by Power Query, and it ran on me. Migrations are my job, so that part is working as intended. The problem is that they could only ever be my job, because the knowledge that made them work had never been written down anywhere. It lived in my hands. Knowing that Outgoing Status is a closed list and Outgoing SubStatus is free text. Knowing that the people file has to be built in report order or the import silently keeps the worse copy of a duplicated person.

Owning the work is fine. The trouble is that nobody else can do it, which makes me a single point of failure with a name and a calendar.

Then I ruptured my Achilles tendon. I spent an afternoon in an emergency room and was out for a couple of days, and the team rescheduled two customer imports, because there was nobody to hand them to. Two shelters waited on one person's tendon.

That's the argument for this project, and I didn't have to construct it. The code was the easy half. Everything that made me the only one had been sitting in my head, unwritten, for years.

What I found

The thing I was replacing was also the answer key

The problem with rewriting a process that already works is proving the rewrite is right. I could have asked a customer for files and compared by hand.

Then I found that the Excel templates cache their own output. The workbook holds the Power Query source and the computed result for every row of the last customer it ran on. The artifact I was replacing had been quietly carrying the answer key the whole time.

That's 184,844 cells of verified ground truth, free, sitting inside the file I was throwing away.

Everything after this depends on that finding. Without a cached answer key, "zero differences" isn't a standard you can even state.

What the tests missed

Three things a green suite told me were fine

All three were found by diffing against a migration that had already succeeded, not by the test suite. The tests had encoded the same misunderstanding the code had.

01

A stale status on 9,687 of 11,375 animals

The events file carries a Current Status describing where an animal is right now. An animal that has left should not have one. The first version cleared it when the animal had a transfer-out partner. The correct rule is to clear it whenever the animal has any outcome at all: adopted, reclaimed, euthanised, anything.

Of the rows that disagreed with the proven file, every single one had an outcome and not one had a transfer-out partner. 182 tests passed throughout.

Had it shipped, a shelter would have opened their new system to find thousands of long-adopted animals listed as still in the building.

02

A date parser that deleted four of every five outcomes

The source system mixes date formats inside a single column. Some rows carry a time, some don't, and the day and month order varies. The obvious pandas call infers a format from the first value it sees and turns everything that doesn't match into null. On a realistic column it nulled four of five valid dates.

The damage was invisible. Rows with a null outgoing date get dropped, because that's how the tool tells a completed outcome from an animal still in custody. So real adoptions would have vanished with no error raised anywhere, and the animal would arrive in the new system as though it were still in a kennel.

Every date now goes through one function that handles mixed formats. Calling the parser directly is forbidden, and a test enforces the ban rather than trusting anyone to remember.

03

A validation list that had never once worked

The tool ships with Shelterluv's canonical species and breed lists. The scrape that produced them left mojibake behind, the wreckage of non-breaking spaces. 52 of 53 cat breeds and 221 of 478 species were corrupted.

So the cat breed validation wasn't degraded, it was inert. Not one entry could ever match anything, and nobody had noticed, because a validation list that matches nothing looks exactly like a validation list with nothing to complain about.

A guard that never fires looks exactly like a guard with nothing to catch. There's now a test that refuses to let corrupt reference data ship again.

The centerpiece

Reproducing sixteen known defects on purpose

For the second source system I gave myself a rule that sounds like malpractice: the tool has to match the old template cell for cell, defects included, with no cleaning up and nothing filled in along the way.

So the specification carries a ledger of sixteen things the template does wrong, each with its row count and a note saying leave it alone. A color field truncated at the wrong character on 61 rows. A trailing newline on 10,646 memos. A second microchip never read, on 60 animals. A junk-row filter carried over from some other shelter's broken export years ago, which matches nothing and was kept anyway.

My favorite is the file that gets parsed with quote handling switched off, so a comment containing a line break splits across two rows and corrupts two memos out of 10,830. The tool reproduces that too, because a correct parser would silently disagree with ground truth on exactly those two rows.

Reproducing a defect on purpose sounds indefensible until you try to measure anything. Allow yourself to be right in one place and zero stops being a number you can reach.

Zero differing cells out of 184,844. Later, after two deliberate deviations I asked for, 756 differences, every one attributable to a specific rule and a specific row count.

That number is only reachable because improvement was forbidden. Allow the tool to be smarter than the template anywhere and the gate becomes "differences that are all understood", which is a sentence nobody can check.

The actual point

Built for someone else's hands

The tool only counts if somebody who isn't me can run a migration with it. I assumed that was a documentation problem. It's a design constraint, and it reaches into almost every screen.

Every error a user can cause is a class with three fields: what happened, what to do about it, and the details. A real one tells you that 47 rows have an unreadable date, which animals they are, and then this:

Processing stops here on purpose. Continuing would silently attach the wrong data to these animals.

That last sentence exists so a trainee reads the halt as deliberate, doesn't escalate to me, and doesn't put me back on the critical path I'm trying to leave.

The same thinking decides what the tool refuses to complain about. 515 animals have no outcome because they're in the building right now, and blank is the correct answer. Flagging them would be a bug. Meanwhile 378 others also have no outcome, but the source marked them as gone without recording how, and those do need closing out. Telling those two groups apart takes knowledge that only ever existed in my head, and a tool that nags a trainee about 515 correct rows has taught them to ignore its warnings by the time the 378 real ones arrive.

The compounding bit

It gets better at its job every time it runs

Some things mean the same thing at every shelter and some don't. A breed is a breed everywhere, so breed translations are global. Intake and outcome types are whatever each shelter decided to call things, so those are per customer and the tool refuses to guess at them.

The consequence is the best argument for the whole project. When a user maps an unrecognized breed on the review screen, it writes to the global table, not the customer's, and they're never sent to a settings page. Which means the breed table grows useful over time instead of the same breeds being re-mapped by hand for every migration, forever.

Receipts

By the numbers

Cells matched against ground truth: 184,844
Differing cells: 0
Tests passing: 356
Bugs the tests missed: 3
Lines of application code: 6,165
Lines of specification: 2,262
Known defects reproduced on purpose: 16
Contributors: 1

Note the ratio. Seven tenths of a line of test and roughly a third of a line of written specification for every line of code that ships.

That ratio is unusual, and it's the honest summary of where the work went. The specifications are the thing the code was generated from, and they read like a deposition. Row counts, dates, direct quotes, and a section in each one titled "rules that must never be improved."

Method

How it was built

Same as Poppycal. I'm not an engineer, and the code was generated by an AI agent working from specifications I wrote.

Which is exactly why this project is the clearest thing I have to show. When the code is cheap, the specification is the whole job, and a specification is only as good as the person who knows which behaviors are load-bearing. Nothing in the data tells you that a naive name splitter must stay naive because hundreds of imports have run through it correctly. Nothing tells you that an intake reason appearing on 2,459 animals must be applied as a fallback rather than a mapping, because using it as a mapping would rewrite 2,220 rows that were already right.

Knowing which parts of a working system you're forbidden to improve is where the whole thing lives.

Where it stands

Not finished

Two source systems are built and passing, and the acceptance gate is green. What hasn't happened yet is the only thing that actually proves the thesis, which is somebody who isn't me running a real migration end to end without asking me anything.

Until that happens I'm still the critical path. Saying so here beats claiming a finish line I haven't crossed.

Screenshots are coming, generated against synthetic data. Nothing on this page will ever show a real shelter's records.