Derive deposit addresses from an xpub without a hot wallet

// Derivation itself needs secp256k1 arithmetic; use a maintained BIP32 library
// rather than writing point multiplication yourself. What matters here is the
// bookkeeping around it.

function assign_deposit_address(PDO $pdo, int $userId): string
{
    $pdo->beginTransaction();
    try {
        // Reuse the one already issued: a user must not accumulate addresses.
        $st = $pdo->prepare('SELECT address FROM deposit_addresses WHERE user_id = ? LIMIT 1');
        $st->execute([$userId]);
        $existing = $st->fetchColumn();
        if ($existing !== false) {
            $pdo->rollBack();
            return (string) $existing;
        }

        // Lock the counter so two signups cannot take the same index.
        $st = $pdo->prepare('SELECT next_index FROM wallet_meta WHERE id = 1 FOR UPDATE');
        $st->execute();
        $index = (int) $st->fetchColumn();

        $address = derive_from_xpub(setting('deposit_xpub'), $index);   // library call

        $pdo->prepare('INSERT INTO deposit_addresses (user_id, address, idx, created_at) VALUES (?, ?, ?, NOW())')
            ->execute([$userId, $address, $index]);
        $pdo->prepare('UPDATE wallet_meta SET next_index = next_index + 1 WHERE id = 1')->execute();

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

// Watch for arrivals by polling a block explorer or your own node, then credit
// through the same idempotent path used by every other deposit.