turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

FP-006

Credit a deposit exactly once, however many callbacks arrive

A unique index on your own reference turns duplicate callbacks, browser refreshes and manual replays into no-ops instead of free balance.

Payment callbacks arrive more than once. The network retries, the user refreshes the success page, an admin replays a stuck one by hand. Any path that credits without a uniqueness check will credit again.

The fix is not a flag column checked in PHP — two simultaneous callbacks both read "not credited" and both proceed. It has to be a constraint the database enforces, in the same transaction as the balance update.

Your own reference is the right key, because it exists before the payment does and survives whatever the processor does with its own ids.

PHP 
/*
ALTER TABLE deposits ADD UNIQUE KEY uq_ref (ref);
ALTER TABLE deposits ADD COLUMN credited_at DATETIME NULL;
*/

function credit_deposit(PDO $pdo, string $ref, int $units, string $processorTxid): bool
{
    $pdo->beginTransaction();
    try {
        // Lock the row and re-read state inside the transaction.
        $st = $pdo->prepare('SELECT id, user_id, credited_at FROM deposits WHERE ref = ? FOR UPDATE');
        $st->execute([$ref]);
        $dep = $st->fetch(PDO::FETCH_ASSOC);

        if (!$dep) {
            $pdo->rollBack();
            return false;                         // unknown reference: log and investigate
        }
        if ($dep['credited_at'] !== null) {
            $pdo->rollBack();
            return true;                          // already done: report success, credit nothing
        }

        $pdo->prepare('UPDATE deposits SET status = "paid", units = ?, txid = ?, credited_at = NOW() WHERE id = ?')
            ->execute([$units, $processorTxid, $dep['id']]);

        $pdo->prepare('UPDATE users SET balance = balance + ? WHERE id = ?')
            ->execute([$units, $dep['user_id']]);

        $pdo->prepare('INSERT INTO ledger (user_id, kind, units, memo, created_at) VALUES (?, "deposit", ?, ?, NOW())')
            ->execute([$dep['user_id'], $units, $ref]);

        $pdo->commit();
        return true;
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Using it

Return success to the processor when the reference is already credited. Anything else keeps the retries coming.

Log every callback with its raw payload before you touch this function. When a deposit is disputed the log is the record, not the balance.

What bites people

Checking credited_at outside the transaction is the same bug in a different place. The lock and the check have to be together.

A reference you have never seen means something is wrong — a wrong merchant account, a replayed callback from another site, a test payment. Log it loudly rather than silently ignoring it.

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 FaucetPay