turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

OPS-007

Adjust a member's balance without losing the trail

Every manual adjustment writes a ledger row and an audit row, inside one transaction. An admin panel that edits a balance directly is how books stop balancing.

Every site needs manual adjustment: refunding a failed payout, compensating for an outage, correcting an import. The wrong way is an editable balance field in the admin panel, because it produces a number with no explanation attached.

Adjust by delta, never by absolute value. The admin types plus or minus an amount and a reason, and the system writes the ledger entry that justifies it. The balance becomes a consequence of the ledger rather than something typed over the top of it.

That means reconciliation still works: the sum of a member's ledger rows equals their balance, and every row says who did it and why.

PHP 
function adjust_balance(PDO $pdo, int $userId, int $deltaUnits, string $reason, int $adminId): bool
{
    if ($deltaUnits === 0 || trim($reason) === '') {
        throw new InvalidArgumentException('a non-zero amount and a reason are both required');
    }

    $pdo->beginTransaction();
    try {
        $st = $pdo->prepare('SELECT balance FROM users WHERE id = ? FOR UPDATE');
        $st->execute([$userId]);
        $before = $st->fetch(PDO::FETCH_ASSOC);
        if (!$before) {
            $pdo->rollBack();
            return false;
        }
        $balance = (int) $before['balance'];

        if ($deltaUnits < 0 && $balance < -$deltaUnits) {
            $pdo->rollBack();
            throw new RuntimeException('adjustment would take the balance below zero');
        }

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

        $pdo->prepare(
            'INSERT INTO ledger (user_id, kind, units, memo, created_at)
             VALUES (?, "admin_adjust", ?, ?, NOW())'
        )->execute([$userId, $deltaUnits, mb_substr($reason, 0, 190)]);

        $pdo->prepare(
            'INSERT INTO audit_log (actor_id, action, target, target_id, detail, ip, created_at)
             VALUES (?, "balance_adjust", "user", ?, ?, ?, NOW())'
        )->execute([
            $adminId,
            $userId,
            sprintf('%+d units (%d -> %d): %s', $deltaUnits, $balance, $balance + $deltaUnits, $reason),
            client_ip(),
        ]);

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

Using it

Make the reason field required in the form, not just in the function. An empty reason six months later is the same as no record.

Show the member's recent ledger next to the adjustment form so the admin can see what they are correcting before they type.

What bites people

Never expose an absolute balance input. Two admins working at once will overwrite each other, and neither will know.

Include the before and after values in the audit detail. The ledger has the delta; the audit line is what makes it readable at a glance.

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 Admin and Operations