Rebuilding a claims database of 305,713 records, and cutting duplicate customers from 39% to 18%
Every claim that comes through Claims Bible ends up as a record somewhere, and by the summer of 2026 the “somewhere” had become the problem. Five years of growth had left the data spread across four Airtable bases that had each been built for a different moment, fed by an app that dropped records without telling anyone, and I’d got to the point where every report I ran started with an hour of cleaning up.
I co-founded Claims Bible, so this was my own mess to sort out, which is a different feeling from sorting out somebody else’s. It also meant I could do it properly rather than doing what a client’s budget allowed.
Where it started
The main archive held 65,525 claims and 65,123 customer records. A separate live base had another 22,078 claims in it and was still taking new ones every day. One of the legacy tables had 161 columns, and when I went through them 118 were empty.
The customer data was the real issue. There were 87,126 customer rows in total, and when I matched them on email they collapsed to 53,559 real people, which meant nearly four in ten rows were a duplicate of somebody who was already there. Every duplicate is a person who gets emailed twice, or gets counted twice, or has half their history on one record and half on another.
Upstream of all that was a Replit app with a Postgres database that fed the bases by webhook. If Airtable didn’t answer, it retried for about seven seconds and then gave up, with no queue and no record that anything had been lost. So when something went wrong, you found out weeks later, if at all.
What I built
A backup before anything else. 305,713 records across the four bases, exported as JSON and CSV with every attachment, and the counts reconciled against the live bases before I changed a single thing. It sounds obvious, and it’s the step people skip when they’re keen to get on.
A design with four tables instead of four bases. People, Claims, Tool Activity and Inbound Leads. One person can have many claims, a claim belongs to exactly one person, and everything else hangs off those two. The old structure had a customer record inside every claim, which is where the duplicates came from in the first place.
Around thirty scripts to do the move safely. Inspection scripts that reported what the data actually looked like rather than what the column headings said it was. Migration scripts with a dry-run mode. Backfill scripts for the fields that had never been populated properly. A collision checker that flagged any two records claiming the same reference before they were loaded. A duplicate preflight that showed me exactly what would be merged before it merged anything. And every phone number normalised to the international format, because a number stored five different ways is five records for one person.
The inbound relays rebuilt as upserts. The Make.com scenarios that take a new enquiry and write it to the database now go through a router that finds the person first, then creates them only if they’re genuinely new. The same person arriving twice updates their record instead of making another one, which is how the duplicate rate stays down after the migration rather than creeping back.
A written method for reconciling. How to compare the old bases against the new one, what counts as a match, and what to do with the rows that don’t. It’s a document, and it’s the thing that made the 506-row problem below solvable instead of frightening.
The decision I’m most pleased with
Among the legacy data was a table of 17,114 leads bought through a lead-distribution platform, and every single one of them was flagged as having opted in to marketing. All 17,114, without exception.
Nobody ticks a box 17,114 times out of 17,114. A flag that’s always true isn’t telling you anything about consent, it’s telling you about the system that set it. So I treated it as meaningless, found the 15,233 of those people who also existed in the new database, and excluded every one of them from marketing.
That’s a large email list to walk away from, and there was a case for keeping it because the flag said what the flag said. I’d rather have a smaller list of people I know agreed than a bigger one I can’t stand behind, and in a regulated business that’s not really a marketing decision, it’s the only one available.
Something similar turned up in the relays themselves. A consent checkbox was arriving as ticked on every submission, whether or not anyone had ticked it, because the automation tool was quietly turning the expression into a string of text on the way through. I only found it by reading the raw payloads rather than the summary screen. It’s a small thing that would have made every consent record we held worthless, and it’s the kind of thing you don’t find unless you go and look.
What went wrong
Part way through, 506 rows that should have been in the new base weren’t. That’s 64% of the expected rows across 195 claims, and for an afternoon it looked like the migration had silently lost a big chunk of the data.
It hadn’t. Working back through the three bases with the reconciliation method, the loss came down to a power cut during a load combined with Airtable rate-limiting the retries, and the genuine gap was 58 rows, every one of which could be identified and reloaded. I’m including it because a migration with nothing going wrong is usually a migration nobody checked.
The result
- 2,512 claims and 1,108 people migrated into the new structure with zero rejections.
- Duplicate customer records down from 39% to 18%, with the relays rebuilt so it stays there.
- 157 claim outcomes carried across from the old bases, so the history didn’t reset.
- 406 signed consent documents exported and kept as evidence, each one linked to the person it belongs to.
- 15,233 people with unreliable consent excluded from all marketing.
- A single backup of 305,713 records that I can go back to.
How it was built
Same as the site rebuild. I designed the structure, specified every script, ran everything against copies first and checked the counts by hand before and after. The code was written working alongside Claude. Where that mattered most here was the inspection stage, because I could ask for a report on what a column actually contained, read it, and go and look at the rows myself before deciding anything.
What this looks like for your business
Most businesses I talk to have a version of this. Leads in a form tool, customers in a CRM, invoices in the accounts package, and nobody quite sure which one is right when they disagree. It doesn’t need to be 300,000 records to cost you money. It needs to be two systems that both think they own the customer.
The approach is the same at any size. Back it up, work out what the data actually is rather than what the labels say, design the structure so a person exists once, and then build the plumbing so it stays that way.
Tell me what’s not working.
I’ll tell you whether I can help and roughly what it would involve.