My Database Is Full of Duplicate and Half-Finished Records

You open your admin table and find the same customer three times, two of them with an email that differs only by capitalization. There are orders with no line items. There is a subscription row pointing at a user id that no longer exists. Someone's phone number is the string "n/a".

Nothing here was malicious. Every one of those rows is what happens when the only thing standing between a user and your database is a form. Users double-click. Networks retry. Tabs get refreshed mid-submit. Someone hits your API directly from a script. Every path that is not the happy path in the browser writes whatever it likes.

AI-generated backends are especially prone to this because generated code is written to make the demo work. Validation lands in the React component where it is visible; the schema underneath gets nullable columns and no unique indexes, because nothing in the prompt asked for them. The result is an app that looks disciplined and a database that is not.

Why the UI is the wrong place for the last line of defense

Client-side validation is a user experience feature. It tells someone their email is malformed before they wait for a round trip. It is not a guarantee, because everything about it is optional from the database's point of view.

There are at least five write paths that skip your form entirely: direct API calls, mobile clients on an older build, your own admin scripts, background jobs and webhooks, and imports. If the rule only exists in the form, only one of those five obeys it.

Correct systems layer validation. The form gives fast feedback. The API validates the request body against a schema and rejects anything malformed. The database enforces the invariants that must never be violated, no matter which code path is writing. Skip the third layer and the first two are suggestions.

What each type of bad row is telling you

That table is worth walking with your own data open next to it. The pattern of what is broken tells you exactly which constraint was never written.

The specific mechanics of a duplicate

Duplicates come from three distinct sources and each needs its own fix.

Double submission. The user clicks Submit twice, or clicks once on a slow connection and clicks again. Two requests arrive, both pass a "does this exist?" check because neither has committed yet, and both insert. Disabling the button helps the common case and fixes nothing underneath it. A unique index does.

Client and network retries. Fetch libraries, mobile clients, and payment providers all retry on timeout. If the first request actually succeeded and only the response was lost, the retry creates a second record. The fix is idempotency: the client generates a key, the server stores it with the record, and a second request with the same key returns the original result rather than creating a new one. This matters most on anything involving money — the same discipline that keeps Stripe behaving the same in test and live.

Check-then-insert races. The classic pattern in generated code: query for an existing row, and if none is found, insert. Between the query and the insert there is a window, and under concurrency two requests both find nothing. This is not fixable in application code alone. Let the database decide by inserting with a unique constraint and handling the conflict — either by ignoring it or by updating the existing row.

Cleaning up what is already there

Do the cleanup before adding constraints, because the constraints will not apply while violations exist. Work in this order, on a copy first.

1. Measure. Write a query that counts rows per candidate key — lowercased email, or the tuple you consider unique. Sort by count descending. You now know the scale, which is usually smaller or much larger than it felt.

2. Decide the survivor rule. For each duplicate group, which row wins? Usually the oldest (it owns the history) or the most complete (it has the most non-null fields). Write the rule down before you write the query, because you will need to apply it consistently.

3. Repoint the children first. Orders, sessions, and comments attached to a losing row must move to the survivor before deletion, or you will trade duplicates for orphans. This is the step people skip and regret.

4. Snapshot, then delete. Copy every row you plan to remove into a backup table in the same transaction. Deleting customer data with no way to reverse the decision is the kind of mistake that only announces itself a week later — the same lesson taught the hard way by migrations that damaged data.

5. Backfill the missing values. Rows with nulls in columns you are about to make NOT NULL need a real value or an explicit sentinel. Decide which per column; do not default everything to an empty string, or you have preserved the problem in a new form.

Putting the invariants in the database

Now add the constraints, one at a time, verifying after each.

Start with uniqueness on the natural keys — one active subscription per user, one email per account. Normalize before indexing: store emails lowercased and trimmed, and use a case-insensitive index so future writes cannot reintroduce the same problem in different capitalization.

Add foreign keys with explicit delete behavior on every relationship. Cascade where a child is meaningless without its parent, restrict where deletion should be blocked. Making that choice deliberately is the whole point; the default is usually wrong for at least one relationship in your schema.

Make columns NOT NULL wherever a missing value is not a valid state, and add check constraints for the rules that are obvious to you and invisible to the database: quantities greater than zero, status values from a fixed set, end dates after start dates.

Then wrap multi-step writes in transactions. An order and its line items must both exist or neither should. Half-finished records are almost always a sequence of independent writes where one of them failed and nothing rolled back — which is really a symptom of error handling that was never written.

Finally, mirror the same rules at the API boundary with a schema validator, so callers get a clear 400 instead of a database error string. Two layers, both enforced, both saying the same thing. If the schema itself needs rethinking rather than patching, designing a database schema for an AI-built app covers the modeling decisions that prevent this from the start.

Keep it clean

Add a scheduled integrity check — a handful of queries counting orphans, duplicates, and null violations — and alert when any of them returns a non-zero result. It takes an afternoon and turns a quiet corruption problem into a same-day notification. If you want an outside read on what else the schema is missing, that is exactly the ground an AI code audit covers.

Frequently asked questions

Can I just add a unique index and be done? Only after cleanup — the index creation fails while duplicates exist. And a unique index alone does not fix the code path that was producing duplicates; it converts a silent duplicate into a visible error your application still has to handle. Add the constraint and handle the conflict deliberately.

Should I delete duplicate customer records or merge them? Merge, nearly always. A duplicate customer usually has real activity attached to both rows — orders on one, support history on the other. Pick a survivor, repoint every child record, then archive the loser rather than deleting it outright.

How did this happen if my form has validation? Because the form is one of several ways rows get created. Retries, direct API calls, background jobs, imports, and older clients all bypass it. Validation in the UI is for humans; constraints in the database are for everything else.

---

If your admin panel has become a museum of every record your users almost created, the cleanup is the easy half — the constraints that stop it recurring are the part worth getting right. SprintX de-duplicates production data safely and rebuilds the schema underneath it with real keys, constraints, and transactions. Send us your schema or repo.