One contract, two implementations

Case study A ended with 1,757 verified records. The uncomfortable truth about that sentence is that it was only true on the day I wrote it. The people in that dataset move jobs constantly, and every move makes a row quietly wrong. Nobody reports it. The row still looks perfectly valid — it just now points at someone who left.

Re-checking 1,757 records by hand on any useful cycle is not a thing a person can do. So it had to be automated, and my first attempt worked beautifully on about two thirds of the sources and returned completely empty results on the rest.

The pages weren't empty. They just hadn't been built yet at the moment I read them.

The lightweight fetcher I'd started with retrieves a page's HTML and stops. A large share of these sites assemble their staff list in the browser after load, so what came back was a shell with nothing in it. Worse, it failed silently — an empty staff list is indistinguishable from a page that genuinely lists nobody, so the first version would have cheerfully reported that several hundred organisations had lost their entire staff overnight.

The obvious fix is to throw away the lightweight fetcher and render everything in a real browser. I didn't, for one reason: the lightweight one is bound directly to the spreadsheet the team already works in, runs on a schedule without a machine being switched on, and needs no maintenance. Replacing it would have cost something real to fix a problem that only affects some sources.

So: two fetchers, chosen by capability rather than preference — and the rest of the pipeline written exactly once, in a form both of them have to obey. Which meant deciding, up front, precisely what “the same” means. Three things must agree, and changing one means changing the other:

• The extraction schema The exact fields pulled from a page, and the closed set of values the classifying field is allowed to take. Typed and validated on the Python side rather than hoped for.
• The extraction prompt Including the rule that matters most: when a person is listed without a published contact, emit the literal string MISSING rather than dropping them. An absent row and a row with a known gap are completely different facts.
• The destination headers Columns are looked up by name, never by position — so the team can reorder the spreadsheet freely and nothing breaks, but a renamed header is a real breaking change and is treated as one.

And one rule that keeps all of it honest: the extraction logic lives in one file, which the sync layer imports. It is not copied. Two copies of a function are two functions, and they will diverge the first time someone is in a hurry.

1. Split the sources by what they actually need Plain pages go to the lightweight scheduled script. Pages that build themselves in the browser go to the Python pipeline, which renders them properly first. Neither is the “real” one.
2. Choose the model as an engineering decision, not a default The bulk sweep runs on a small fast model rather than the largest available one. Not to save money as such — the large model's per-minute input-token limit produced rate-limit errors on big pages, which is a correctness problem when you are sweeping over a thousand sources. The smaller model is around five times cheaper with materially similar extraction accuracy on this task, and the call retries on rate-limit and overload responses while honouring the server's own back-off header.
3. Match people by a key that survives how humans type names Records get typed inconsistently — a middle name appears in one place and not another, a compound surname lands in the wrong field. Comparing first and last name directly produces false departures. The key sorts every name token alphabetically instead, so the same person matches regardless of which field each part landed in.
4. Report the change, never the state The job does not overwrite the dataset. It emits typed changes into a review queue for a person to accept or reject. Three types, and the third is the one people forget.
5. Advance the pointer only after the write lands Batch state is a single integer on disk, written after a successful save and never before — so an interrupted run redoes its last batch instead of skipping it.

The name key

def name_key(first: str, last: str) -> str:
    """Split-insensitive name key: sort all tokens alphabetically."""
    tokens = (first.strip() + " " + last.strip()).split()
    return " ".join(sorted(tokens)).lower()

Verbatim. Four lines, and it removed an entire category of false “this person has left” alerts caused by nothing more than a middle name being recorded in one source and not the other.

The three change types

The third type is the whole argument for treating MISSING as a value rather than an absence. A system that only compared names would report no change at all here — same person, same role — while the one thing the dataset exists to provide had silently disappeared.

A dataset that tells me when it has gone stale, instead of waiting for someone to trip over a dead contact. Changes arrive as a reviewable queue, typed and attributed, and a human decides. Nothing is overwritten automatically — which matters, because an extraction failure and a genuine departure look identical until someone looks.

The parts I'd point at as real engineering rather than scripting: it is resumable, so a thousand-source sweep survives a dropped network; it is idempotent, so re-running a batch cannot double-post; it degrades honestly, because a page that fails to fetch is recorded as a fetch failure and not as an organisation with no staff; and it retries politely, backing off on the server's own terms rather than hammering.

Three things, and the first one still slightly embarrasses me.

I solved a coordination problem with a sentence in the documentation

Both implementations write to the same destination. If they ever run against the same sources, they both post the same change and the review queue fills with duplicates. My solution was to write this in the contract document: keep their source lists separate, or run everything through one of them.

That is not a solution, it's an instruction — and instructions are only followed until somebody is busy. It should have been a lock, or a single dispatcher owning the list and handing work to whichever fetcher suits each source. The correct fix is maybe an afternoon's work, and I chose to document the hazard instead of removing it.

I wrote down that the prompt is duplicated instead of de-duplicating it

The extraction schema lives in one file and is imported. Good. The prompt text does not — it exists in both implementations, and my own documentation carefully instructs the reader to keep the two copies in sync.

The moment you find yourself writing “remember to change this in both places”, you have identified the bug and then filed it as a convention.

It should be one file that both sides read. I knew that while I was writing the warning.

The name key trades one error for another, and I should have measured it

Sorting name tokens fixed false departures, which were frequent and noisy. It also throws away word order entirely, so two genuinely different people whose names use the same set of tokens collide into one — and it cannot follow someone through a change of surname, which is common in this data and shows up as a departure plus an arrival rather than one person.

I made that trade on instinct because the noisy failure was the one I could see. I never counted how often the quiet one happens. Next time I'd keep the sorted key as a candidate matcher and confirm against a second signal — the role, or the contact address itself — before deciding two records are the same person.

And one constraint I'd accept again

The spreadsheet is the database; there is no local copy. That sounds wrong and I'd still choose it. The team lives in that sheet all day, and a second store would have meant a sync problem and two versions of the truth — which is precisely the failure I'd just spent months undoing in case study A. The right answer isn't a local mirror. It's the one from that post-mortem: put the data somewhere that enforces its own integrity, and let the spreadsheet be the window onto it.