turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

LDG-004

Guard against a negative balance, and claw back correctly

Make the column unsigned, check inside the transaction, and let a clawback take a balance to zero rather than below it.

Balances go negative through races, through reversals arriving after a withdrawal, and through admin adjustments typed with the wrong sign. Once one account is negative, your totals stop meaning anything.

Two layers stop it. The column type refuses the value at the database level, which turns a logic bug into a loud error instead of a quiet loss. The application check inside the locked transaction gives you a clean message before the database has to complain.

Clawbacks are the case that needs thought. A fraudulent completion reversed after the user has withdrawn cannot always be recovered — take what is there, record the shortfall, and carry it as a debt rather than forcing the balance below zero.

PHP 
// balance BIGINT UNSIGNED NOT NULL DEFAULT 0 — the database refuses to go below zero.

function debit(PDO $pdo, int $userId, int $units, string $kind): bool
{
    if ($units <= 0) {
        throw new InvalidArgumentException('debit must be positive');
    }

    $pdo->beginTransaction();
    try {
        $st = $pdo->prepare('SELECT balance FROM users WHERE id = ? FOR UPDATE');
        $st->execute([$userId]);
        $balance = (int) $st->fetchColumn();

        if ($balance < $units) {
            $pdo->rollBack();
            return false;
        }

        $pdo->prepare('UPDATE users SET balance = balance - ? WHERE id = ? AND balance >= ?')
            ->execute([$units, $userId, $units]);      // belt and braces

        $pdo->prepare('INSERT INTO ledger (user_id, kind, units, created_at) VALUES (?, ?, ?, NOW())')
            ->execute([$userId, $kind, -$units]);

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

function clawback(PDO $pdo, int $userId, int $units, string $reason): array
{
    $pdo->beginTransaction();
    try {
        $st = $pdo->prepare('SELECT balance FROM users WHERE id = ? FOR UPDATE');
        $st->execute([$userId]);
        $balance = (int) $st->fetchColumn();

        $taken = min($balance, $units);
        $shortfall = $units - $taken;

        if ($taken > 0) {
            $pdo->prepare('UPDATE users SET balance = balance - ? WHERE id = ?')->execute([$taken, $userId]);
            $pdo->prepare('INSERT INTO ledger (user_id, kind, units, memo, created_at) VALUES (?, "clawback", ?, ?, NOW())')
                ->execute([$userId, -$taken, $reason]);
        }
        if ($shortfall > 0) {
            // Carried as a debt, not as a negative balance.
            $pdo->prepare('INSERT INTO debts (user_id, units, reason, created_at) VALUES (?, ?, ?, NOW())')
                ->execute([$userId, $shortfall, $reason]);
        }

        $pdo->commit();
        return ['taken' => $taken, 'shortfall' => $shortfall];
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Using it

Settle outstanding debts before crediting new earnings, so an account that owes you works its way back to zero rather than withdrawing again.

Put the shortfall total on the admin dashboard. It is the number that tells you how much reversal fraud is actually costing.

What bites people

An unsigned column throws on underflow, which is what you want — but only if your error handling surfaces it. Swallowed exceptions turn a loud guard into a silent one.

Admin balance adjustments need the same guard. A typed minus sign in the wrong field is the most common way a balance goes negative.

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