Preventing duplicate records in CRUD apps: practical defenses

What duplicate records are and why they happen

A duplicate record is when your app stores the same thing twice. It might be two orders for the same checkout, two support tickets with the same details, or two accounts created from the same signup flow. In a CRUD app, duplicates usually look like normal rows on their own, but they’re wrong when you look at the data as a whole.

Most duplicates start with normal behavior. Someone clicks Create twice because the page feels slow. On mobile, a double tap is easy to miss. Even careful users will try again if the button still looks active and there’s no clear sign anything is happening.

Then there’s the messy middle: networks and servers. A request can time out and get retried automatically. A client library might repeat a POST if it thinks the first attempt failed. The first request might succeed, but the response gets lost, so the user tries again and creates a second copy.

You can’t solve this in only one layer because each layer sees only part of the story. The UI can reduce accidental double submits, but it can’t stop retries from bad connections. The server can detect repeats, but it needs a reliable way to recognize “this is the same create again.” The database can enforce rules, but only if you define what “the same thing” means.

The goal is simple: make creates safe even when the same request happens twice. The second attempt should become a no-op, a clean “already created” response, or a controlled conflict, not a second row.

Where duplicates slip in (client, network, server)

Many teams treat duplicates as a database problem. In practice, duplicates are usually born earlier, when the same create action gets triggered more than once.

Client-side: the user submits twice

A user clicks Create and nothing seems to happen, so they click again. Or they press Enter, then click the button right after. On mobile, you can get two quick taps, overlapping touch and click events, or a gesture that registers twice.

Network and transport: the request gets re-sent

Even if the user only submits once, the network can still repeat the request. A timeout can trigger a retry. An offline app may queue a “Save” and re-send it on reconnect. Some HTTP libraries retry automatically on certain errors, and you won’t notice until you see duplicate rows.

Server-side: retries, webhooks, and background workers

Servers repeat work on purpose. Job queues retry failed jobs. Webhook providers often deliver the same event more than once, especially if your endpoint is slow or returns a non-2xx status. If your create logic is triggered by these events, assume duplicates will happen.

Concurrency: two requests race

Concurrency creates the sneakiest duplicates. Two tabs submit the same form within milliseconds. If your server does “does it exist?” and then inserts, both requests can pass the check before either insert happens.

Treat the client, network, and server as separate sources of repeats. You’ll need defenses in all three.

Start with database unique constraints (the last line of defense)

If you want one reliable place to stop duplicates, put the rule in the database. UI fixes and server checks help, but they can fail under retries, lag, or two users acting at the same time. A database unique constraint is the final authority.

Start by choosing a real-world uniqueness rule that matches how people think about the record. Common examples:

• Email address for a user
• Order number for a purchase
• An external_id from a payment provider

Be careful with fields that look unique but aren’t, like a full name.

Once you have the rule, enforce it with a unique constraint (or unique index). This makes the database reject a second insert that would violate the rule, even if two requests arrive at the same moment.

When the constraint triggers, decide what the user should experience. If creating a duplicate is always wrong, block it with a clear message (“That email is already in use”). If retries are common and the record already exists, it’s often better to treat the retry as success and return the existing record (“Your order was already created”).

Use safe write patterns when they match your rules

If your create is really “create or reuse,” an upsert can be the cleanest pattern. Example: “create customer by email” can insert a new row or return the existing one. Use this only when it matches the business meaning. If slightly different payloads could arrive for the same key, decide which fields are allowed to update and which must stay unchanged.

Unique constraints don’t replace idempotency keys or good UI states, but they give you a hard stop that everything else can lean on.

Idempotency keys: make repeated creates safe

An idempotency key is a unique token that represents one user intent, like “create this order once.” If the same request is sent again (double click, network retry, mobile resume), the server treats it as a retry, not a new create.

This is one of the most practical tools for making create endpoints safe when the client can’t tell whether the first attempt succeeded.

Endpoints that benefit most are the ones where a duplicate is costly or confusing, like orders, invoices, payments, invites, subscriptions, and forms that trigger emails or webhooks.

On a retry, the server should return the original result from the first successful attempt, including the same created record ID and status code. To do that, store a small idempotency record keyed by (user or account) + endpoint + idempotency key. Save both the outcome (record ID, response body) and an “in progress” state so two near-simultaneous requests don’t create two rows.

Keep idempotency records long enough to cover real retries. A common baseline is 24 hours. For payments, many teams keep 48-72 hours. A TTL keeps storage bounded and matches how long a retry is likely.

If you generate APIs with a chat-driven builder like Koder.ai, you still want to make idempotency explicit: accept a client-sent key (header or field) and enforce “same key, same result” on the server.

Step by step: add idempotency to a create endpoint

Idempotency makes a create request safe to repeat. If the client retries because of a timeout (or a user clicks twice), the server returns the same result instead of creating a second row.

A practical 5-step recipe

1. Create one idempotency key per user action (for example, when they press “Create invoice”). Generate it on the client and keep it stable for that single action.
2. Send the key with the request every time you attempt that create. A header works well (like Idempotency-Key), but sending it in the JSON body can also work.
3. On the server, store the key with a clear scope: who is calling (user or account), what endpoint it applies to, and what create it represents. Save the final response payload too.
4. If the same key arrives again for the same scope, do not create anything. Return the stored response (including the original record id).
5. Test it under stress: rapid clicks, client retries, and fake network timeouts. Confirm you still get exactly one new record.

What the server logic looks like

The key detail is that “check + store” must be safe under concurrency. In practice, you store the idempotency record with a unique constraint on (scope, key) and treat conflicts as a signal to reuse.

if idempotency_record exists for (user_id, key):
  return saved_response
else:
  begin transaction
  create the row
  save response under (user_id, key)
  commit
  return response

Example: a customer hits “Create invoice,” the app sends key abc123, and the server creates invoice inv_1007. If the phone drops signal and retries, the server answers with the same inv_1007 response, not inv_1008.

When you test, don’t stop at “double click.” Simulate a request that times out on the client but still completes on the server, then retry with the same key.

UI affordances that stop accidental double submits

Server-side defenses matter, but many duplicates still start with a human doing a normal thing twice. Good UI makes the safe path obvious.

Disable the submit button as soon as the user submits. Do it on the first click, not after validation or after the request starts. If the form can submit via multiple controls (a button and Enter), lock the whole form state, not just one button.

Show a clear progress state that answers one question: is it working? A simple “Saving...” label or spinner is enough. Keep the layout stable so the button doesn’t jump around and tempt a second click.

A small set of rules prevents most double submits: set an isSubmitting flag at the start of the submit handler, ignore new submits while it’s true (for click and Enter), and don’t clear it until you have a real response.

Slow responses are where many apps slip. If you re-enable the button on a fixed timer (for example after 2 seconds), users can submit again while the first request is still in flight. Re-enable only when the attempt completes.

After success, make resubmission unlikely. Navigate away (to the new record page or list) or show a clear success state with the created record visible. Avoid leaving the same filled form on screen with the button enabled.

Edge cases to plan for (tabs, refresh, mobile)

The stubborn duplicate bugs come from everyday “weird but common” behavior: two tabs, a refresh, or a phone that loses signal.

First, scope uniqueness correctly. “Unique” rarely means “unique in the whole database.” It might mean one per user, one per workspace, or one per tenant. If you sync with an external system, you may need uniqueness per external source plus its external ID. A safe approach is to write down the exact sentence you mean (for example, “One invoice number per tenant per year”), then enforce that.

Multi-tab behavior is a classic trap. UI loading states help in one tab, but they do nothing across tabs. This is where server-side defenses must still hold.

Back button and refresh can trigger accidental resubmits. After a successful create, users often refresh to “check,” or hit Back and re-submit a form that still looks editable. Prefer a created view instead of the original form, and make the server handle safe replays.

Mobile adds interruptions: backgrounding, flaky networks, and automatic retries. A request might succeed, but the app never receives the response, so it tries again on resume.

Common mistakes and traps

The most common failure mode is treating the UI as the only guardrail. A disabled button and a spinner help, but they don’t cover refreshes, flaky mobile networks, users opening a second tab, or a client bug. The server and database still need to be able to say “this create already happened.”

Another trap is picking the wrong field for uniqueness. If you set a unique constraint on something that isn’t truly unique (a last name, a rounded timestamp, a free-form title), you’ll block valid records. Instead, use a real identifier (like an external provider ID) or a scoped rule (unique per user, per day, or per parent record).

Idempotency keys are also easy to implement badly. If the client generates a brand-new key on every retry, you get a brand-new create each time. Keep the same key for the whole user intent, from the first click through any retries.

Also watch what you return on retries. If the first request created the record, a retry should return the same result (or at least the same record ID), not a vague error that makes users try again.

If a unique constraint blocks a duplicate, don’t hide it behind “Something went wrong.” Say what happened in plain language: “This invoice number already exists. We kept the original and didn’t create a second one.”

Quick checklist before you ship

Before release, do a quick pass specifically for duplicate creation paths. The best results come from stacking defenses so a missed click, retry, or slow network can’t create two rows.

Confirm three things:

• Database rules match reality (unique constraints exist in production and are correctly scoped).
• Important create endpoints are safe to repeat (idempotency keys return the same outcome).
• The UI makes state obvious (disabled submit, clear progress, and a clear post-success view).

A practical gut check: open the form, click submit twice quickly, then refresh mid-submit and try again. If you can create two records, real users will too.

Example: creating an invoice without duplicates

Imagine a small invoicing app. A user fills out a new invoice and taps Create. The network is slow, the screen doesn’t change right away, and they tap Create again.

With only UI protection, you might disable the button and show a spinner. That helps, but it isn’t enough. A double tap can still slip through on some devices, a retry can happen after a timeout, or the user can submit from two tabs.

With only a database unique constraint, you can stop exact duplicates, but the experience can be rough. The first request succeeds, the second hits the constraint, and the user sees an error even though the invoice was created.

The clean result is idempotency plus a unique constraint:

• The client generates an idempotency key for the create action and reuses it on retries.
• The server stores the key with the create result and returns the same invoice ID for repeats.
• The unique constraint still protects you if two different keys try to create the same invoice number.

A simple UI message after the second tap: “Invoice created - we ignored the duplicate submission and kept your first request.”

Next steps: keep it reliable as the app grows

Once you have the baseline in place, the next wins are about visibility, cleanup, and consistency.

Add lightweight logging around create paths so you can tell the difference between a real user action and a retry. Log the idempotency key, the unique fields involved, and the outcome (created vs returned existing vs rejected). You don’t need heavy tooling to start.

If duplicates already exist, clean them with a clear rule and an audit trail. For example, keep the oldest record as the “winner,” reattach related rows (payments, line items), and mark the others as merged instead of deleting them. That makes support and reporting much easier.

Write down your uniqueness and idempotency rules in one place: what’s unique and in what scope, how long idempotency keys live, what errors look like, and what the UI should do on retries. This prevents new endpoints from quietly bypassing the safety rails.

If you’re building CRUD screens quickly in Koder.ai (koder.ai), it’s worth making these behaviors part of your default template: unique constraints in the schema, idempotent create endpoints in the API, and clear loading states in the UI. That way, speed doesn’t come at the cost of messy data.