Skip to content
|By Muhammad Wilayat Shah|5 views • 5 unique IPs

Prevent Duplicate Transactions in ERP Systems: Step-by-Step Fix

Learn how to prevent duplicate transactions in ERP systems using idempotency keys, PostgreSQL unique constraints, ON CONFLICT inserts and idempotent Laravel jobs.

ShareLinkedInX
Prevent Duplicate Transactions in ERP Systems: Step-by-Step Fix

A customer clicks "Pay" twice, a mobile app retries after a timeout, or a queue job runs again after a worker restart, and suddenly the ERP shows two identical invoices, two stock deductions or two supplier payments. If you need to prevent duplicate transactions, the fix is not a disabled button or a quick check in the controller. It is a combination of database idempotency, unique constraints and correct transaction handling, so the same business operation can arrive many times but is only applied once. This guide walks through the problem, symptoms, root cause and a step-by-step fix for PostgreSQL and Laravel based ERP systems.

Problem: The Same Operation Is Applied More Than Once

Every ERP has operations that must happen exactly once from a business point of view: posting a payment, issuing goods from a warehouse, creating a journal entry, approving a payroll run. The network and the infrastructure around them, however, deliver requests at least once. Browsers resend forms, load balancers retry upstreams, HTTP clients retry on timeouts, and queue systems redeliver jobs that did not acknowledge in time.

When the application treats each delivery as a new operation, you get duplicate rows that look valid. They pass validation, they have different primary keys, and they quietly break transaction integrity in your ledgers and stock balances.

Symptoms of Duplicate Transactions in an ERP

  • Two payments or receipts with the same amount, customer and reference, created a few hundred milliseconds apart.
  • Stock on hand is lower than the physical count because the same goods issue was posted twice.
  • Bank reconciliation shows one bank entry matched against two ERP entries.
  • Logs show the same request ID or job payload processed twice, often after a 504 Gateway Time-out or a worker restart.
  • Queue workers log messages like Job has been attempted too many times or MaxAttemptsExceededException, while earlier attempts actually succeeded.
  • Adding a unique index later fails with ERROR: could not create unique index ... Key (...) is duplicated. because duplicates already exist.

Root Cause: At-Least-Once Delivery Without Idempotency

Duplicate transactions almost always come from one of these root causes:

  1. Client and proxy retries. The server finished the work, but the response was lost or slow. The client retried, and the server did the work again.
  2. Check-then-insert race conditions. Code runs SELECT to see whether a payment exists, finds nothing, then runs INSERT. Two concurrent requests both pass the check. This is a classic race condition in programming.
  3. Queue redelivery. A job commits to the database but crashes or times out before acknowledging, so the broker delivers it again.
  4. No natural business key. The table only has an auto-increment id, so the database has no way to know that two rows represent the same real-world event.
  5. Side effects outside the transaction. An external payment gateway is charged before the local transaction commits, and a retry charges it again.

The common thread: correctness depends on application code being perfectly timed, instead of on guarantees the database can enforce.

prevent duplicate transactions flow with idempotency key, unique constraint and single database commit
An idempotency key plus a unique constraint lets retries return the first result instead of creating a second transaction.

Step-by-Step Solution to Prevent Duplicate Transactions

Step 1: Find and Clean Existing Duplicates

Before adding constraints, measure the damage. Group by the fields that define "the same transaction" in your business, for example customer, amount, reference and a short time window.

Code
-- Find likely duplicate payments
SELECT customer_id, amount, reference, COUNT(*) AS copies,
       MIN(created_at) AS first_seen, MAX(created_at) AS last_seen
FROM payments
GROUP BY customer_id, amount, reference
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Do not bulk delete duplicates in a financial module. Reverse them through your normal credit note or reversal process so the audit trail stays complete, then add constraints.

Step 2: Add a Unique Constraint on the Business Key

The database is the only place that can reliably stop two concurrent inserts. Give each transaction table a natural key, such as a gateway transaction ID, a bank reference or a document number per company, and enforce it with a unique index.

Code
-- Build the index without blocking writes on a busy table
CREATE UNIQUE INDEX CONCURRENTLY payments_gateway_txn_uq
    ON payments (company_id, gateway_txn_id)
    WHERE gateway_txn_id IS NOT NULL;

Now a second insert with the same key fails with ERROR: duplicate key value violates unique constraint "payments_gateway_txn_uq" (SQLSTATE 23505) instead of silently creating a copy.

Step 3: Use Idempotency Keys for API Requests

Database idempotency for APIs means the client sends a unique key with every operation, and the server stores the key with the result. A repeated request with the same key returns the stored result rather than running the operation again. This is the same pattern payment providers use with an Idempotency-Key header.

Code
CREATE TABLE idempotency_keys (
    key           uuid PRIMARY KEY,
    user_id       bigint      NOT NULL,
    request_hash  text        NOT NULL,
    status        text        NOT NULL DEFAULT 'processing',
    response_code int,
    response_body jsonb,
    created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idempotency_keys_created_idx ON idempotency_keys (created_at);

The client generates the key once per user action, for example when the payment form is opened, and reuses it for every retry. Combined with sensible exponential backoff for API retries, clients can retry safely.

Step 4: Claim the Key and Do the Work in One Transaction

Insert the key first with ON CONFLICT DO NOTHING. If the insert succeeds, this request owns the operation. If it does not, another request already handled it or is handling it. Here is a Laravel version:

Code
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

public function store(Request $request)
{
    $key  = $request->header('Idempotency-Key');
    abort_unless($key && Str::isUuid($key), 400, 'Missing Idempotency-Key');
    $hash = hash('sha256', json_encode($request->all()));

    return DB::transaction(function () use ($request, $key, $hash) {
        $claimed = DB::table('idempotency_keys')->insertOrIgnore([
            'key' => $key, 'user_id' => $request->user()->id,
            'request_hash' => $hash, 'created_at' => now(),
        ]);

        if ($claimed === 0) {
            $row = DB::table('idempotency_keys')->where('key', $key)->lockForUpdate()->first();
            abort_if($row->request_hash !== $hash, 422, 'Key reused with different payload');
            abort_if($row->status === 'processing', 409, 'Request still processing');
            return response()->json(json_decode($row->response_body), $row->response_code);
        }

        $payment = Payment::create($request->validated());
        $body = ['id' => $payment->id, 'status' => 'posted'];

        DB::table('idempotency_keys')->where('key', $key)->update([
            'status' => 'done', 'response_code' => 201, 'response_body' => json_encode($body),
        ]);
        return response()->json($body, 201);
    });
}

Because the key row and the payment are written in the same database transaction, either both commit or neither does. A retry after a crash finds no key and runs cleanly. For browser forms, combine this with the techniques in how to prevent duplicate form submissions in Laravel.

Step 5: Make Queue Jobs Idempotent

Assume every job can run twice. Give each job a stable business key and let the database decide whether the work is new.

Code
// Inside the job's handle() method
DB::transaction(function () {
    $inserted = DB::table('stock_movements')->insertOrIgnore([
        'source_type' => 'goods_issue',
        'source_id'   => $this->goodsIssueId,
        'item_id'     => $this->itemId,
        'qty'         => -$this->qty,
        'created_at'  => now(),
    ]);
    if ($inserted === 0) {
        return; // already processed, safe to acknowledge
    }
    DB::table('stock_balances')
        ->where('item_id', $this->itemId)
        ->decrement('qty', $this->qty);
});
Code
-- Matching constraint so one goods issue line moves stock once
ALTER TABLE stock_movements
  ADD CONSTRAINT stock_movements_source_uq UNIQUE (source_type, source_id, item_id);

Laravel's ShouldBeUnique interface and WithoutOverlapping middleware help reduce concurrent runs, but they rely on cache locks that can expire. Treat them as an optimisation and keep the database constraint as the real guarantee.

Step 6: Handle Unique Violations Gracefully

When two requests race, one will hit the unique constraint. Catch that specific error and return the existing record instead of a 500 error.

Code
use Illuminate\Database\UniqueConstraintViolationException;

try {
    $payment = Payment::create($data);
} catch (UniqueConstraintViolationException $e) {
    $payment = Payment::where('company_id', $data['company_id'])
        ->where('gateway_txn_id', $data['gateway_txn_id'])
        ->firstOrFail();
}

UniqueConstraintViolationException is available in Laravel 10.20 and later. On older versions, catch QueryException and check that $e->getCode() equals '23505'.

Step 7: Protect External Side Effects

For calls to payment gateways or bank APIs, pass your own idempotency key or order reference to the provider if it supports one, and record the outgoing request before calling it. A transactional outbox table, processed by a worker after commit, keeps the local transaction and the external call from drifting apart.

Verification: Prove Duplicates Can No Longer Happen

  1. Send the same request twice in parallel with the same key and confirm only one row is created and both responses carry the same ID.
  2. Insert a duplicate business key directly in psql and confirm you get SQLSTATE 23505.
  3. Kill a queue worker in the middle of a job, let the job retry, and confirm stock moved only once.
  4. Rerun the duplicate-finding query from Step 1 after a busy day and confirm it returns no new rows.
Code
# Fire two identical requests at the same time
KEY=$(uuidgen)
for i in 1 2; do
  curl -s -X POST https://erp.example.com/api/payments \
    -H "Authorization: Bearer $TOKEN" \
    -H "Idempotency-Key: $KEY" \
    -H "Content-Type: application/json" \
    -d '{"customer_id":42,"amount":1500,"gateway_txn_id":"TXN-9001"}' &
done; wait

psql -d erp -c "SELECT COUNT(*) FROM payments WHERE gateway_txn_id = 'TXN-9001';"

The count should be 1. One response may be 201 and the other 409 or a replayed 201, depending on timing; both are correct.

Prevention: Keep Transaction Integrity by Design

  • Define a business key for every new transaction table and add the unique constraint in the same migration.
  • Require an Idempotency-Key header on every API endpoint that creates money, stock or accounting entries.
  • Never use the check-then-insert pattern without a constraint behind it; prefer INSERT ... ON CONFLICT.
  • Keep database writes and the idempotency record in one transaction, and move external calls to an outbox.
  • Expire old idempotency keys with a scheduled command, for example after 24 to 72 hours, depending on how long clients may retry.
  • Record who created each transaction and from which request in your audit log database design, so any duplicate that slips through can be traced quickly.

FAQs

What is database idempotency?

Database idempotency means an operation can be applied many times with the same result as applying it once. In practice you achieve it with unique business keys, ON CONFLICT inserts and stored idempotency keys that map a request to its first result.

Is disabling the submit button enough to prevent duplicate transactions?

No. It helps with double clicks, but it does nothing against network retries, proxy retries, API clients or queue redelivery. Server-side constraints are required.

Should I use Redis locks or database constraints?

Use database constraints as the guarantee. Redis locks are useful for reducing duplicate work, but a lock can expire while the work is still running, and it does not protect rows written by other code paths.

How long should idempotency keys be stored?

Keep them at least as long as any client might retry, plus a margin. Many teams use 24 hours for web and mobile clients and longer for batch integrations.

How do I prevent duplicate payments with a payment gateway?

Store the gateway transaction ID with a unique constraint, send your own order reference or idempotency key to the gateway when supported, and process webhooks idempotently because gateways may deliver the same webhook more than once.

Does SERIALIZABLE isolation solve duplicate transactions?

It can detect some conflicting concurrent transactions, but it adds retries and does not help when the same request arrives seconds apart in separate transactions. A unique constraint is simpler and stronger for this problem.

ShareLinkedInX
Back to blog

Keep reading

Newsletter

Occasional notes on Laravel, React, and shipping production systems — no spam.

Comments

Join the discussion. Replies load on demand, up to 5 levels.

0 threads

Loading editor…
Reviewed before publishing

No comments yet

Be the first to start the conversation.