The Integration Nobody Was Going to Build

I am the membership chair for a men’s golf club in Southern California. It is a volunteer job, and the part of it that actually takes time has nothing to do with membership. It is data entry.

Here is the shape of it. Our members’ handicap records live with the USGA, which runs the national handicap system and issues every golfer a GHIN number. Our subscriptions, the record of what someone paid for and when it renews, live with the SCGA, the governing body for golf clubs down here. Our monthly tournaments run in Golf Genius, a third.

Those three systems have never spoken to each other, and as far as I can tell they never will.

What that means day to day: every time a member joins, renews, or lapses, the SCGA sends me an email. Not a digest. An individual email, at the moment it happens. Some days there are five. Some weeks there are none. On top of that comes a weekly recap of the same events I was already notified about one at a time, which is either a safety net or a second thing to reconcile, depending on how the week went.

Then I log into Golf Genius and type it in. Add the new member. Update the one who changed email addresses. Deactivate the expired.

None of it is difficult. All of it is load-bearing.

Why it is load-bearing

Our tournaments run monthly and they pay out. Different membership types carry different benefits, and the one that matters most is whether you are allowed to enter an event with a purse.

That eligibility is decided by data sitting in Golf Genius. If a member is not in the system, or their membership type is stale, or their subscription end date is last month’s, they cannot register. They find this out when they try to sign up, which is a few days before the event, which is when they call me.

So the reconciliation has to happen at least twice a month, and it has to be right before the entry window opens. That is the real requirement. Not “keep the roster current.” Make sure the person who paid can play.

Golf Genius does have an import. It takes a spreadsheet. Building that spreadsheet by hand from three sources is the same work as typing it in one record at a time, with an extra step where you can make a mistake nobody catches until someone is locked out of a tournament.

Add up the email triage, the typing, and the checking of my own work, and it was at least two hours a week. Every week, whether anything interesting happened or not.

What I asked for

Take the exports. Do the merge. Tell me exactly what changed before anything gets uploaded.

Three files in, one import file out, plus a report I can read.

What got built

A Python script that joins three spreadsheets on GHIN, the golfer identifier all three systems already carry. That shared key is the only reason this is possible at all, and it is worth saying plainly: the integration exists because somebody, decades ago, gave every golfer in the country one number.

The script walks the USGA roster, which is the authoritative list of who is a member. For each of those golfers it writes eighteen fields into the Golf Genius record: fifteen from the USGA roster, two from the SCGA subscription record (membership type and subscription end date), and one derived field that answers whether the handicap record is active.

The direction rules are the whole design:

• In the USGA roster and in Golf Genius: the Golf Genius record gets updated.
• In the USGA roster, not in Golf Genius: reported as a new member.
• In Golf Genius, not in the USGA roster: reported as a possible dropout, and never touched.
• In the SCGA subscription data but not the USGA roster: ignored completely.

The output file contains only the rows that actually changed, with every column intact, because an import file that restates the entire roster gives you no way to see what you just did. Alongside it comes a workbook with a sheet per outcome: what was updated and which fields moved, who is new, who might have dropped. The source files are moved into a timestamped archive, so every run is auditable and cannot be accidentally repeated against stale exports.

What changed once I could see it

The first version was a two-file merge: SCGA subscription data into Golf Genius. It ran, and it was incomplete in a way I only saw by looking at real output.

It could keep a membership type and a renewal date current on someone already in the system. It could not add a new member, and it could not correct an email address or a phone number, because none of that detail is in the subscription export. That is the USGA record.

Both systems are authoritative about what they hold. The USGA carries the member record: name, contact details, the GHIN itself. The SCGA carries the two facts nobody else has, the membership type and the date it runs out. Neither one is a subset of the other, and the question was never which system to trust.

The question was which list defines the population.

I chose the USGA roster, and that decision is the spine of the whole tool. The sync walks it, top to bottom, and everything else is layered on top of a golfer who is already on it. Subscription fields get written only where that golfer also exists in the SCGA data. Where there is no match, those fields are left alone rather than guessed at. A blank is honest. An invented value is not.

Everything downstream follows from that one choice, including the four direction rules above. Pick the other list and you get a different tool with different failure modes.

What broke

Five things, none of them clever, all of them the difference between a demo and something you rely on before a tournament.

Every single row looked changed. The first real run reported that essentially the entire membership had been modified. The GHIN key was arriving as an integer from one export and as text from another, so nothing matched anything and every field looked new. A report that flags everything is worth precisely as much as a report that flags nothing. The fix was to push keys and compared values through one normalization path, on both sides of every comparison.

Excel was holding the file. Run the script with a source workbook still open and it dies partway through with a permission error. Now the tool checks first and says so, because “close your spreadsheets” is a much better message than a stack trace.

The cloud version was not worth its own compute. I built a small web service around the core so the sync could be triggered from an automation platform, and deployed it on a free hosting tier. It did not fit. I cut it to a single worker, stopped reloading workbooks in memory, added a memory diagnostic endpoint, and got it running. Then I looked at what I actually had: a memory ceiling I would keep bumping into, in exchange for remote triggering of a process that runs twice a month on a schedule I control. I moved it back to my workstation, where it runs fine and costs nothing. The hosted version is abandoned on purpose. Not every automation deserves a server.

The archive was too good at its job. Processed inputs are moved, not copied, into the timestamped archive folder. That is correct behavior, because it makes a rerun against yesterday’s exports impossible. It also means that the first time I wanted to rerun a sync, my source files were gone. That is a documentation problem rather than a code problem, and documenting it was the fix.

The dropout list came back with thousands of names. Golf Genius holds every golfer the club has ever entered, going back years. Compared against a current roster, most of them are missing. The first time I saw that number I assumed the join was broken. It was not. The list was correct, and it was correct in a way that would have been ruinous to act on automatically.

The part I did not expect

That dropout list is the reason the tool refuses to delete anything.

It would have been easy to close the loop: if a golfer is not on the current roster, deactivate them. It is the obvious last step, it is what the word “sync” implies, and it would have quietly removed people who are members in good standing but whose record has a typo, an old number, or a renewal that has not propagated yet. The cost of leaving in someone who should not be there is an awkward conversation. The cost of removing someone who should be there is a member who cannot enter the tournament he paid to play in.

Those are not symmetric, so the system does not treat them symmetrically. It reports, and I decide.

The second thing I did not expect was how much of the tool was not code. Once it worked, the operating knowledge lived entirely in my head: which of the three files is authoritative, how each one has to be named, that the column headers start on a different row in each, to close Excel first, to read the report before uploading, that the inputs are gone after a run. So I wrote that down as a skill for Claude Code, the assistant I built the thing with. The checks now run before the script does, and the report gets explained in plain language instead of interpreted from a spreadsheet.

The script was half the tool. The operating layer was the other half, and it is the half most internal tools never get.

The general point

Three organizations each built exactly what they were asked to build. The USGA maintains handicaps correctly. The SCGA tracks subscriptions, payments, and renewal dates correctly. Golf Genius runs tournaments correctly. Not one of them is at fault, and not one of them has any commercial reason to care about the seam between them. A volunteer golf club is not a market.

So the seam becomes a person. In my case, a membership chair with a folder full of emails. In a growing business it is a controller re-keying invoices, or an operations manager maintaining the spreadsheet that stands between two systems that will not talk.

Every system in your building has an owner. The space between them almost never does, and that space is where the work quietly piles up. It is nobody’s fault and nobody’s job, which is exactly why it survives for years.

A run now takes about fifteen minutes, twice a month, and most of that is downloading the exports. The two hours a week are gone, and so is the class of mistake that only shows up when a member cannot register.

The reason to write this up is that closing gaps like this one used to require a budget and a vendor. It does not anymore. This one was built in evenings, by the person who had the problem.

If you are thinking about building something similar

Decide which list defines the population before you write anything. This is not the same as asking which system has the best data, and it is usually not a question about trust. Two systems can both be authoritative and still hold different things. What you have to settle is which one the process walks, because that choice decides who gets processed at all, and everything else inherits it.

Make it report where the stakes are lopsided. Automation earns trust by being correct, and the fastest way to lose it is one confident wrong deletion. Ask what the worst automatic action would cost. If that is worse than doing the step by hand, do not automate the step.

Run it locally until hosting earns its keep. A process that runs twice a month does not need a server, an uptime target, or a monthly bill. I spent real time proving that to myself.

Write down how to operate it, not just how it works. The knowledge you build up while making something is invisible to you and inaccessible to everyone else. Six months from now, that includes you.