Managed IT · September 17, 2026 · intSignal Team

Charged Twice, Still Owing: Two Payment Bugs and Their Database Fixes

Two bugs turned up in our billing code during a review. Neither had produced a complaint yet, both were in code that reads correctly, and both were the same mistake underneath: the application was promising something only the database can guarantee.

The first could charge a customer's card twice. The second could leave an invoice showing a balance forever, after the customer had paid it.

Why does a paid invoice still show a balance?

Because the handler marks the payment succeeded before it reduces the invoice balance, and every later delivery of that event sees "already succeeded" and stops. The retry your payment provider sends is only useful if a retry can still do the work that was missed. Mark the work done first, and retries become the thing that guarantees it never gets finished.

Bug 1: the check that doesn't hold

Here's the shape of the original guard. It looks defensive:

const inFlight = await db.payment.findFirst({
  where: { invoiceId, status: { in: ["pending", "succeeded"] } },
});
if (inFlight) throw new Error("A payment for this invoice is already in progress.");

const payment = await db.payment.create({ data: { invoiceId, amount, status: "pending" } });
await stripe.charge({ amount, ... });

Two requests can run that at the same time. Both read the table, both find nothing in flight, both insert, both charge. The customer sees one payment, their statement shows two.

This isn't hypothetical. It needs no attacker and no load test, just any of the everyday cases:

A double-click on the pay button, or an impatient second click while the spinner runs.
A retry after a slow response, from the browser, a mobile client, or a fetch wrapper.
Two people paying the same invoice from the same company, in the same minute.
Your own automation: our nightly auto-pay job could run while the customer was paying manually.

Wrapping it in a transaction does not fix it. At the default isolation level, both transactions can read the same "nothing in flight" state and both proceed; a SELECT cannot lock rows that don't exist yet. You could reach for SERIALIZABLE and retry logic, or lock the invoice row with SELECT … FOR UPDATE, and both work. But there is a simpler tool for "at most one of these may exist at a time", and it's the one the database is built around.

The fix: let the insert be the lock

A partial unique index says the rule once, in the schema, and enforces it for every code path forever, including the SQL someone runs by hand at 2 a.m.

CREATE UNIQUE INDEX payment_invoice_inflight_key
  ON payment (invoice_id)
  WHERE status = 'pending' AND method IN ('card', 'ach');

Read it as: at most one pending live charge per invoice. The WHERE clause is what makes this usable on a real table:

History is untouched. Past succeeded and failed payments don't count, so an invoice can be charged again after a failed attempt, and part-payments still work.
Offline methods are exempt. Two outstanding cheques against one invoice are normal book-keeping, not a double charge.

Now the insert itself is the guard, and the loser of the race finds out before any money moves:

try {
  await db.query(
    `INSERT INTO payment (id, invoice_id, amount, method, status) VALUES ($1, $2, $3, $4, 'pending')`,
    [id, invoiceId, amount, method],
  );
} catch (err) {
  if (err.code === "23505") return { started: false, reason: "already_in_progress" };
  throw err;
}

23505 is Postgres's unique-violation code. Prisma reports it as P2002, and most drivers expose the same code somewhere on the error. Only swallow that code: turning every insert error into "already in progress" hides real failures.

Adding the index to a table that already has duplicates

If you already have duplicate in-flight rows, CREATE UNIQUE INDEX fails, which is exactly what you want to hear before it runs against production. Collapse them first in the same migration, newest attempt wins:

UPDATE payment p
SET status = 'failed'
WHERE p.status = 'pending'
  AND p.method IN ('card', 'ach')
  AND EXISTS (
    SELECT 1 FROM payment p2
    WHERE p2.invoice_id = p.invoice_id
      AND p2.status = 'pending'
      AND p2.method IN ('card', 'ach')
      AND (p2.created_at > p.created_at OR (p2.created_at = p.created_at AND p2.id > p.id))
  );

The tie-breaker on id matters more than it looks: rows created in the same race often share a timestamp, and without it every tied row survives, so the index still won't build. On a large table, use CREATE UNIQUE INDEX CONCURRENTLY so writers aren't blocked while it builds, and check that the in-flight rows you're voiding really are abandoned before you void them.

Bug 2: the balance that never catches up

The webhook handler for a captured payment did this:

// The original. Read the order carefully.
await db.payment.update({ where: { id }, data: { status: "succeeded" } });
try {
  await accounting.recordPayment(payment);                // external HTTP call
  await db.invoice.update({ where: { id: invoiceId },
    data: { balance: { decrement: amount } } });          // the customer-facing number
} catch (err) {
  logger.error("post-payment steps failed", err);         // swallowed
}

The payment is marked succeeded, then the accounting call throws — the vendor is down, the token expired, whatever — and the balance update never runs. The error is logged and swallowed.

Now the crucial part: the provider will deliver that event again, and the handler will see status === "succeeded" and return early. The retry that should have saved you is the reason nothing gets fixed. The customer has been charged, the invoice still says they owe, and nothing in the system will ever correct it. Every payment provider delivers webhooks at least once, and that's a feature — but only if your handler leaves work available to redo.

The fix: claim and reconcile in one transaction

Two changes. First, the claim and the balance change live in a single transaction, so either both happen or neither does. Second, the external call moves after the commit, because an HTTP call must never hold a database transaction open — it ties up a connection and a row lock for as long as someone else's API takes to answer.

const claimed = await db.transaction(async (tx) => {
  const { rows } = await tx.query(
    `UPDATE payment SET status = 'succeeded'
     WHERE provider_ref = $1 AND status <> 'succeeded'
     RETURNING id, invoice_id, amount`,
    [providerRef],
  );
  if (rows.length === 0) return null;   // another delivery already finalized it
  const payment = rows[0];
  await tx.query(
    `UPDATE invoice
     SET balance = GREATEST(balance - $2, 0),
         status  = CASE WHEN balance - $2 <= 0 THEN 'paid' ELSE 'partial' END
     WHERE id = $1`,
    [payment.invoice_id, payment.amount],
  );
  return payment;
});

The conditional UPDATE … WHERE status <> 'succeeded' is the whole concurrency design. It's an atomic claim: whichever delivery gets there first updates a row and proceeds, and every other delivery updates zero rows and stops. No advisory locks, no distributed lock service, no SELECT then UPDATE window.

The rule to take from it: make the claim and the effect atomic, and check how many rows you actually claimed. An UPDATE that matched nothing is information, not a no-op.

Then the accounting mirror, after the commit, as best effort:

try {
  await accounting.recordPayment(claimed);
} catch {
  // Deferred, not lost — reconcilable separately, and it must not undo a captured payment.
}

That ordering says something about priorities, and it's the right way round: the customer-facing balance is correct the moment the transaction commits, and the accounting system catches up. The reverse — the way we had it — let a third party's outage corrupt our own records.

Check whether it already happened to you

Before changing anything, ask your database. These two queries find the evidence of both bugs. Adjust the table and column names; the shape is what matters.

-- 1. Invoices with more than one live charge in flight.
SELECT invoice_id, count(*) AS in_flight, min(created_at) AS oldest
FROM payment
WHERE status = 'pending' AND method IN ('card', 'ach')
GROUP BY invoice_id
HAVING count(*) > 1
ORDER BY oldest;

-- 2. Money captured against an invoice that was never reconciled.
SELECT p.id AS payment_id, p.invoice_id, p.amount, i.balance, i.status
FROM payment p
JOIN invoice i ON i.id = p.invoice_id
WHERE p.status = 'succeeded'
  AND i.status = 'open'
ORDER BY p.created_at;

The first query's min(created_at) tells you whether you're looking at a live race or an abandoned row from months ago. The second is the one to run first thing: anything it returns is a customer who paid and is still being told they owe.

Also compare totals with your provider. Every processor can export captured payments for a period; sum them by day and compare against what your own records say you captured. A gap that repeats on particular days usually points at a failing step in the handler, not at the provider.

The whole thing, as tested code

This is the module our tests run against: the schema, the migration cleanup, the two handler functions, and the audit queries.

// payments.mjs — the two fixes, as plain SQL plus the handler logic around them.
// Works with any Postgres client that exposes query() and a transaction helper.

/** The schema this example assumes, and the constraint that does the real work. */
export const SCHEMA = `
CREATE TABLE invoice (
  id       text PRIMARY KEY,
  balance  numeric(12,2) NOT NULL,
  status   text NOT NULL DEFAULT 'open'
);

CREATE TABLE payment (
  id           text PRIMARY KEY,
  invoice_id   text NOT NULL REFERENCES invoice(id),
  amount       numeric(12,2) NOT NULL,
  method       text NOT NULL,                 -- 'card' | 'ach' | 'check' | 'wire'
  status       text NOT NULL,                 -- 'pending' | 'succeeded' | 'failed'
  provider_ref text UNIQUE,                   -- the payment intent id from the processor
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- At most ONE in-flight live charge per invoice. A partial index, so historical
-- 'succeeded' and 'failed' rows are unaffected and offline methods are exempt.
CREATE UNIQUE INDEX payment_invoice_inflight_key
  ON payment (invoice_id)
  WHERE status = 'pending' AND method IN ('card', 'ach');
`;

/** Run this BEFORE creating the index on a live table: collapse duplicates, newest wins. */
export const COLLAPSE_EXISTING_DUPLICATES = `
UPDATE payment p
SET status = 'failed'
WHERE p.status = 'pending'
  AND p.method IN ('card', 'ach')
  AND EXISTS (
    SELECT 1 FROM payment p2
    WHERE p2.invoice_id = p.invoice_id
      AND p2.status = 'pending'
      AND p2.method IN ('card', 'ach')
      AND (p2.created_at > p.created_at OR (p2.created_at = p.created_at AND p2.id > p.id))
  );
`;

export const UNIQUE_VIOLATION = "23505"; // Postgres; Prisma reports it as P2002

/** Run these against production before you change anything: they find both bugs' evidence. */
export const AUDIT_QUERIES = {
  // Invoices with more than one live charge in flight — the double-charge window, or a leftover.
  duplicateInFlight: `
SELECT invoice_id, count(*) AS in_flight, min(created_at) AS oldest
FROM payment
WHERE status = 'pending' AND method IN ('card', 'ach')
GROUP BY invoice_id
HAVING count(*) > 1
ORDER BY oldest;`,

  // Money captured against an invoice that was never reconciled: the balance is still untouched.
  capturedButUnreconciled: `
SELECT p.id AS payment_id, p.invoice_id, p.amount, i.balance, i.status
FROM payment p
JOIN invoice i ON i.id = p.invoice_id
WHERE p.status = 'succeeded'
  AND i.status = 'open'
ORDER BY p.created_at;`,
};

/**
 * Start a charge. The insert is the lock: if another attempt is already in flight for this
 * invoice, the partial unique index rejects this row and we stop before charging anything.
 */
export async function beginCharge(db, { id, invoiceId, amount, method }) {
  try {
    await db.query(
      `INSERT INTO payment (id, invoice_id, amount, method, status) VALUES ($1, $2, $3, $4, 'pending')`,
      [id, invoiceId, amount, method],
    );
    return { started: true, paymentId: id };
  } catch (err) {
    if (err.code === UNIQUE_VIOLATION) return { started: false, reason: "already_in_progress" };
    throw err;
  }
}

/**
 * Finalize a captured payment. Safe to call any number of times, in any order, from the
 * webhook and from the inline success path.
 *
 * The claim and the balance change happen in ONE transaction: exactly one caller wins the
 * claim, and because the decrement is in the same transaction, a captured payment always
 * leaves the invoice reconciled — a mid-way failure rolls back and the next delivery retries.
 */
export async function finalizeSucceeded(db, providerRef, { syncToAccounting } = {}) {
  const claimed = await db.transaction(async (tx) => {
    const { rows } = await tx.query(
      `UPDATE payment SET status = 'succeeded'
       WHERE provider_ref = $1 AND status <> 'succeeded'
       RETURNING id, invoice_id, amount`,
      [providerRef],
    );
    if (rows.length === 0) return null; // unknown payment, or another delivery already finalized it
    const payment = rows[0];
    const { rows: invoices } = await tx.query(
      `UPDATE invoice
       SET balance = GREATEST(balance - $2, 0),
           status  = CASE WHEN balance - $2 <= 0 THEN 'paid' ELSE 'partial' END
       WHERE id = $1
       RETURNING balance, status`,
      [payment.invoice_id, payment.amount],
    );
    return { payment, invoice: invoices[0] };
  });
  if (!claimed) return { finalized: false };

  // Everything external happens AFTER the transaction commits: an HTTP call must never hold a
  // database transaction open, and the customer-facing balance is already correct without it.
  if (syncToAccounting) {
    try {
      await syncToAccounting(claimed.payment);
    } catch {
      // Deferred, not lost: this is reconcilable separately and must not undo the payment.
    }
  }
  return { finalized: true, ...claimed };
}

How we tested it

The suite runs against a real Postgres engine — PGlite, which is Postgres 18 compiled to WebAssembly, so the constraints, transactions and error codes are the genuine ones rather than a mock. 37 tests cover:

The second in-flight charge is refused, while a retry after a failed attempt is allowed, other invoices are unaffected, cheques are exempt, and a card charge is refused while an ACH debit is in flight.
Creating the index on dirty data fails, the cleanup collapses duplicates newest-wins (including the same-timestamp tie), offline payments survive it, and the index then builds.
A redelivered webhook changes nothing, an unknown reference is ignored, part-payments mark the invoice partial, the final payment marks it paid, and an overpayment can't push the balance below zero.
A failure inside the transaction rolls the claim back, so the next delivery still reconciles — the exact scenario that produced the permanent wrong balance.
A failing accounting call doesn't undo a captured payment.
Both audit queries find the seeded problems and ignore the healthy rows.

We also checked the tests are worth anything: weakening any of 19 details — making the index non-partial, dropping ACH from it, removing the tie-breaker from the cleanup, making the claim unconditional, moving the balance update out of the transaction, letting the balance go negative — makes the suite fail.

The general rules

Four things to take away, none of them specific to payments:

Uniqueness belongs in the database. If your code says "there can only be one of these", write a constraint. Application checks are advisory the moment two processes exist.
Claim work with a conditional update, and read the row count. "Update where it isn't done yet" is a lock you already have.
Make the claim and its effect atomic. If marking something done and doing it are separate commits, some fraction of reality will land between them.
Never hold a transaction open across a network call, and never let an external system's failure roll back your own facts.

Frequently asked questions

Doesn't an idempotency key at the payment provider prevent double charges?

It prevents duplicate submissions of the same attempt, which is a different problem. If your code creates two payment attempts, each will carry its own key and both will charge. Use both: an idempotency key derived from the payment row protects against retries of one attempt, and the database constraint stops a second attempt existing at all.

Why not `SELECT … FOR UPDATE` on the invoice?

That works, and it's a reasonable choice when you're already loading and mutating the invoice row. Two reasons we preferred the index: it protects every code path including manual SQL and future features, and it can't be forgotten by the next person who writes a payment flow.

Won't the unique index break part-payments or retries after failure?

No, and that's what the WHERE clause is for: only pending live charges are constrained. Failed attempts, captured payments and offline methods are all outside the index, so a customer can retry after a decline and pay an invoice in instalments.

What about ACH and other asynchronous methods?

They're the reason the index includes ach: the debit sits pending for days, which is a much wider window for a second attempt than a card charge. The finalization path is identical, because the provider's webhook is what tells you it settled.

Our ORM hides the error code. Now what?

Most expose it: Prisma uses P2002 with the constraint name in meta.target, node-postgres gives you err.code === "23505", and JDBC exposes the SQLState. If yours truly doesn't, match on the constraint name in the message, and add a test that fails if that message ever changes.

How do we stop this class of bug coming back?

Two habits. Write the test that demonstrates the failure before the fix, so you know it reproduces. And in review, ask of any check-then-act code: what happens if two of these run at once? If the answer depends on timing, the invariant belongs in the database.

Our review that found these also turned up credentials leaking into logs from HTTP client errors, and the Entra ID single sign-on guide covers the identity checks that sit in front of flows like this. If you'd like a second set of eyes on the systems that move your money, that's part of our managed IT services.

Charged Twice, Still Owing: Two Payment Bugs and Their Database Fixes

Why does a paid invoice still show a balance?

Bug 1: the check that doesn't hold

The fix: let the insert be the lock

Adding the index to a table that already has duplicates

Bug 2: the balance that never catches up

The fix: claim and reconcile in one transaction

Check whether it already happened to you

The whole thing, as tested code

How we tested it

The general rules

Frequently asked questions

Doesn't an idempotency key at the payment provider prevent double charges?

Why not `SELECT … FOR UPDATE` on the invoice?

Won't the unique index break part-payments or retries after failure?

What about ACH and other asynchronous methods?

Our ORM hides the error code. Now what?

How do we stop this class of bug coming back?

Related Managed IT services

Keep reading

One Provider, 495 Customers: Concentration Risk After the IDC Frontier Ransomware Attack

Fixing the Refrigeration Attack: A Remediation Plan for Danfoss AK-SM and AK-SC Sites

Warlock Ransomware and SharePoint ToolShell: Why Patching Alone Leaves You Exposed

Why does a paid invoice still show a balance?

Bug 1: the check that doesn't hold

The fix: let the insert be the lock

Adding the index to a table that already has duplicates

Bug 2: the balance that never catches up

The fix: claim and reconcile in one transaction

Check whether it already happened to you

The whole thing, as tested code

How we tested it

The general rules

Frequently asked questions

Doesn't an idempotency key at the payment provider prevent double charges?

Why not SELECT … FOR UPDATE on the invoice?

Won't the unique index break part-payments or retries after failure?

What about ACH and other asynchronous methods?

Our ORM hides the error code. Now what?

How do we stop this class of bug coming back?

Related reading

Related Managed IT services

Keep reading

One Provider, 495 Customers: Concentration Risk After the IDC Frontier Ransomware Attack

Fixing the Refrigeration Attack: A Remediation Plan for Danfoss AK-SM and AK-SC Sites

Warlock Ransomware and SharePoint ToolShell: Why Patching Alone Leaves You Exposed

Why not `SELECT … FOR UPDATE` on the invoice?