The problem nobody planned for
Your app has done its job for years. Customers use it, staff rely on it, and the business has grown on top of it. Somewhere along the way the data started drifting.
The same customer appears three times with slightly different spellings. An order’s status is “shipped”, “Shipped”, “sent” or blank, depending on who entered it. The monthly report says one number and the finance spreadsheet says another, and someone spends two days every month working out which is right. When a record changes unexpectedly, nobody can say who changed it or when.
None of this means anyone was careless. Early on, speed mattered more than rules, and that was the right call. But code and data age differently. You can rewrite an app’s code in weeks. Years of inconsistent records are much harder to undo, and every new feature built on top of them inherits the mess.
What I do
The goal is data you can trust, enforced by the database itself rather than hoped for in the code.
- Measure first. I profile the data before touching it: how many duplicates, how many values that break the obvious rules, which tables disagree with each other. You get those numbers in writing.
- Agree the rules with you. What counts as the same customer? Which statuses are real? You know the business; I turn those answers into precise rules.
- Put the rules in the database. Constraints, unique indexes, foreign keys and proper status types, so bad data is refused at the door instead of discovered in a report.
- Clean the existing data carefully. Duplicates are merged with every reference updated. Odd values are mapped to real ones. Anything ambiguous goes on a list for a person to decide, not to a guess.
- Add an audit trail. Who changed what, when, and what it was before. It answers the “who did this” question and makes mistakes reversible.
- Reconcile across systems. Where your app, your billing provider and your accounting tool should agree, a regular check that lists every difference.
- Test the backups. A backup is only real once it has been restored. I restore it, check it, time it, and write down how.
Changing live data without drama
The hard part of this work isn’t writing the rules. It’s applying them to a database people are using right now. A careless migration can lock a busy table, fail halfway, or quietly drop records. I work in a way that avoids those outcomes:
- Every change is rehearsed first on a recent copy of the production data.
- Large changes run in small batches, so the app stays responsive.
- New rules are added in stages: first checked against new data, then the old data is fixed, then the rule is fully enforced.
- Every step has a written way back.
- Counts are compared before and after, so nothing goes missing quietly.
Where the experience comes from
For seven years I was a senior engineer on a multi-tenant B2B SaaS product that grew from about four million to thirteen million dollars in annual revenue, and I ran its production Postgres database, around a terabyte. Data cleanup and live migrations on a database that customers were using all day were a regular part of that work. I’ve written about some of it on the site, including counting very large tables, fixing slow query patterns across a whole fleet of apps, and building audit logs. Those essays are under Writing.
Documentation, so the rules don’t get lost
When I finish, you have a written record of your data: a plain-language overview of the main tables and what each one means, the rules now enforced and why, decision notes on every merge and mapping, runbooks for restoring a backup and running the reconciliation, the before-and-after numbers, and instructions for AI assistants so anyone generating code against your database knows the rules. You own all of it, along with the database, the code and the accounts. Any future developer or freelancer can pick it up.
Take a business like this one
The business in this section is a composite of the kind of company I talk to, not a named client. The numbers are the shape of the problem, not a case study.
Picture a membership organisation for independent pharmacies, with about four thousand members, an events program and an online store, all run from one web app built over ten years by a series of contractors. Renewals are handled in the app, payments in a separate billing service, and the accounts in an accounting package.
The member count differs depending on which screen you ask. Some pharmacies are listed twice, once under the business name and once under the owner. Renewal reminders go to members who’ve already paid. The board wants accurate numbers for its annual report, and the finance lead doesn’t trust any of them.
What I’d do for an organisation shaped like this:
- Profile everything. Count the duplicates, the conflicting statuses and the members whose payment records don’t match.
- Agree definitions with the staff: what makes a member active, what makes two records the same pharmacy.
- Prove the backups by restoring one to a separate server and checking it.
- Merge duplicates in batches, keeping every payment and event registration attached to the right member, with a short list of uncertain cases for staff to decide.
- Enforce the rules so a second record for the same pharmacy can’t be created, and statuses come from a fixed list.
- Add the audit trail and a weekly reconciliation between the app, the billing service and the accounts, with any differences emailed to the finance lead.
The staff go back to serving members, and the annual report has one number for membership, the right one.
What it costs
It starts with a fixed-price data assessment: I profile your database and give you a written report of what’s wrong, how much of it there is and what fixing it involves. The repair work is then scoped and quoted from what the assessment finds, usually in stages so the riskiest problems are fixed first. No retainer required.
What I need from you
Read access to a copy of the database, and the hosting details for where it runs. Someone who knows the business well enough to answer “are these two records the same?” when the data can’t decide. And a list of the reports or numbers you currently don’t trust.