turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

LDG-003

Idempotency keys on every operation that moves money

A caller-supplied key with a unique index turns every retry, refresh and double-click into a no-op. The single most valuable column in a payments schema.

Everything retries. Browsers resend, networks reconnect, admins click twice, cron overlaps. Any operation that adds or subtracts money and can be called twice will eventually be called twice.

The general fix is a key supplied by whoever initiates the operation, unique-indexed in the database. The first call inserts the row and does the work. Every later call with the same key hits the constraint, does nothing, and returns the original result.

The key has to come from the caller and be derived from the intent, not from the moment. A key containing a timestamp is a different key on every retry, which is no key at all.

PHP 
/*
CREATE TABLE money_ops (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  idem_key VARCHAR(80) NOT NULL,
  kind VARCHAR(32) NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  units BIGINT NOT NULL,
  result_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_idem (idem_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/

function with_idempotency(PDO $pdo, string $key, string $kind, int $userId, int $units, callable $work)
{
    $pdo->beginTransaction();
    try {
        $pdo->prepare(
            'INSERT INTO money_ops (idem_key, kind, user_id, units, created_at)
             VALUES (?, ?, ?, ?, NOW())'
        )->execute([$key, $kind, $userId, $units]);
    } catch (PDOException $e) {
        $pdo->rollBack();
        if (($e->errorInfo[1] ?? 0) === 1062) {
            // Seen before. Return what the first call produced.
            $st = $pdo->prepare('SELECT result_id FROM money_ops WHERE idem_key = ?');
            $st->execute([$key]);
            return ['replayed' => true, 'result_id' => (int) $st->fetchColumn()];
        }
        throw $e;
    }

    try {
        $resultId = (int) $work($pdo);        // the actual debit, credit or payout
        $pdo->prepare('UPDATE money_ops SET result_id = ? WHERE idem_key = ?')->execute([$resultId, $key]);
        $pdo->commit();
        return ['replayed' => false, 'result_id' => $resultId];
    } catch (Throwable $e) {
        $pdo->rollBack();                     // the key row rolls back too: a retry may proceed
        throw $e;
    }
}

Using it

Build the key from the intent: withdraw:412:1700000000000 where the last part is a request id you generated when the form was rendered, not the time the button was clicked.

For third-party callbacks, use the network's own transaction id. It is stable across their retries, which is exactly the property you need.

What bites people

A key derived from the current time defeats the whole mechanism. Generate it once, at the point the intent is formed, and carry it through every retry.

Rolling back the key row on failure is deliberate: a genuine failure should be retryable. If you want failures to be final, record the failure against the key instead of rolling it back.

This one touches real money. Point it at a throwaway wallet and watch a full cycle before you trust it with a live balance.

Also in Money and Ledger