Build a Telegram Bot That Talks to the Blockchain (part 3)

Tracking balances and rewards - giving your bot real awareness.

Step 1. Why we need another table

Our wallets table already stores static configuration: who owns what, which chain, token, and threshold.

But blockchain data constantly changes, balances grow, rewards accumulate, and providers claim tokens.

That’s why we introduce a second table: rewards - a simple log that stores historical snapshots for every wallet.

Here’s the structure:

CREATE TABLE `rewards` (
  `id` int(11) NOT NULL,
  `wallet_id` int(11) NOT NULL,
  `reward_amount` decimal(18,8) NOT NULL,
  `balance_amount` decimal(18,8) NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `rewards`
  ADD PRIMARY KEY (`id`),
  ADD KEY `wallet_id` (`wallet_id`);

ALTER TABLE `rewards`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `rewards`
  ADD CONSTRAINT `rewards_ibfk_1` FOREIGN KEY (`wallet_id`) REFERENCES `wallets` (`id`) ON DELETE CASCADE;
COMMIT;

Each record represents one checkpoint in time — usually taken hourly or daily by a cron job.

where:

• wallet_id - Link to the wallets table
• reward_amount - Total claimable rewards at that moment
• balance_amount - Liquidity position size
• created_at - When the snapshot was taken

This way, you can measure reward growth, detect changes, and compare trends over time — just like a mini analytics system.

Step 2. Fetching live liquidity data

Every liquidity provider wallet on core.allbridge.io has real-time metrics available via the Allbridge Core REST API.

We’ll use two endpoints from self hosted rest-api:

• /chains — lists available chains and token addresses
• /liquidity/details?ownerAddress={wallet}&tokenAddress={token} — shows live balance and rewards

To use it, we’ll fetch the token address once from the /chains index, then request /liquidity/details for every subscribed wallet.

Step 3. Storing snapshots in the database

Inside your project root, create a new file called process_rewards.php. This script will be run periodically (for example, every hour).

At the top, it loads configuration and connects to Telegram + MySQL:

Step 3. Storing snapshots in the database

Inside your project root, create a new file called process_rewards.php — this script will be run periodically (for example, every hour).

At the top, it loads configuration and connects to Telegram + MySQL:

Then it fetches all wallets:

$stmt = $pdo->prepare('SELECT * FROM wallets');
$stmt->execute();
$wallets = $stmt->fetchAll(PDO::FETCH_ASSOC);

For each wallet:

1. Determine the blockchain and token.
2. Fetch lpAmount and rewardDebt via the API.
3. Save new record in the rewards table.

$stmt = $pdo->prepare(
  'INSERT INTO rewards (wallet_id, reward_amount, balance_amount, created_at)
   VALUES (:wallet_id, :reward_amount, :balance_amount, :created_at)'
);
$stmt->bindValue(':wallet_id', $wallet_id);
$stmt->bindValue(':reward_amount', $reward);
$stmt->bindValue(':balance_amount', $balance);
$stmt->bindValue(':created_at', date('Y-m-d H:i:s'));
$stmt->execute();

Each insert creates one snapshot.

Over time, the table becomes your bot’s blockchain diary, storing everything it has ever seen.

Step 4. Detecting reward growth

Once the bot saves the new data, it compares it with the previous record for the same wallet:

SELECT reward_amount, balance_amount
FROM rewards
WHERE wallet_id = :wallet_id
ORDER BY created_at DESC
LIMIT 1;

If the new rewardDebt is higher than before, that means rewards grew.

We can calculate the difference:

$diff_reward = round($current_data['rewardDebt'] - $last_amount, 2);

If this difference passes the user’s threshold, the bot sends a message:

🎉 Your reward for ETH-USDC increased by +5.25 USDC!
Now claimable: 48.1 USDC
Balance: 1024.3 USDC

That’s how users stay in the loop, without opening any DApp.

Step 5. Summarizing everything

To give users an overview, we aggregate totals by user:

$totals[$user_id]['totalWallets']++;
$totals[$user_id]['totalRewards'] += $current_data['rewardDebt'];
$totals[$user_id]['totalBalance'] += $current_balance;

At the end of each cron run, the bot sends a short summary:

📈 Overview across all your wallets:
Total wallets: 3
Total rewards: 82.4
Total balance: 3100.9

That message alone makes this feel like a complete personal dashboard.

Step 6. Generating daily or weekly reports

Sometimes users want to see progress over time.

The same script can generate reports by comparing all snapshots within the last few hours or days.

SELECT reward_amount, balance_amount, created_at
FROM rewards
WHERE wallet_id = :wallet_id
  AND created_at >= NOW() - INTERVAL 24 HOUR
ORDER BY created_at ASC;

Then you can format results like this:

📊 24h summary for Main-ETH:
10:00 – rewards: +2.3 | balance: +0.0
15:00 – rewards: +1.2 | balance: +100.0
22:00 – rewards: +0.5 | balance: +50.0

Total change: +4.0 rewards | +150.0 balance

That’s your first lightweight on-chain analytics, delivered straight into Telegram.

Step 7. Scheduling with cron

Finally, automate everything.

Open your terminal and run:

crontab -e

Add this line to execute every hour:

0 * * * * php /path/to/project/scripts/process_rewards.php >> /path/to/logs/rewards.log 2>&1

Now your bot will wake up once per hour, fetch blockchain data, and notify users if thresholds are reached.

A fully autonomous DeFi watcher.

Summary

At this point, your bot has all the pieces of a real monitoring system: 

• It knows who the users are.
• It knows what wallets to track.
• It records how rewards change over time.
• It reacts when those rewards cross thresholds.

Next, we’ll make it interactive, allowing users to manage wallets, edit settings, and refresh views without needing to type commands.

file process_rewards.php:

<?php

require_once __DIR__ . '/../vendor/autoload.php';

// Load configuration
$config = require __DIR__ . '/../config.php';

// Create Telegram API object
$telegram = new Longman\TelegramBot\Telegram($config['api_key'], $config['bot_username']);

// Enable MySQL
$telegram->enableMySql($config['mysql']);

use Longman\TelegramBot\DB;
use Longman\TelegramBot\Request;

try {
    $pdo = DB::getPdo();

    // Fetch wallet configurations
    $wallets = fetchWallets($pdo);
    if (empty($wallets)) {
        logMessage("No wallet configurations found.");
        exit;
    }

    $totals = [];

    // figure out reporting windows just once
    $daily_report_time = '09:00'; // HH:MM, when we consider "daily" / "weekly" reports
    $now               = new DateTime();
    $current_hour      = $now->format('H:i');
    $current_minute    = $now->format('i');
    $is_monday         = $now->format('l') === 'Monday';

    $is_hourly_window  = $now->format('i') === '00';
    $is_daily_window   = ($current_hour === $daily_report_time);
    $is_weekly_window  = ($is_monday && $current_hour === $daily_report_time);

    // cache pools once
    $pools_index = fetchPoolsIndex();

    foreach ($wallets as $wallet) {
        processWallet(
            $pdo,
            $pools_index,
            $wallet,
            $totals,
            $is_hourly_window,
            $is_daily_window,
            $is_weekly_window
        );
    }

    notifyTotals($totals);

    logMessage("Rewards processing completed successfully.");
} catch (Exception $e) {
    handleError($e);
}

// Fetch all wallet configurations
function fetchWallets(PDO $pdo): array
{
    $stmt = $pdo->prepare('SELECT * FROM wallets');
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Process a single wallet
function processWallet(
    PDO $pdo,
    array $pools_index,
    array $wallet,
    array &$totals,
    bool $is_hourly_window,
    bool $is_daily_window,
    bool $is_weekly_window
) {
    $wallet_id = $wallet['id'];
    $user_id = $wallet['user_id'];
    $name = $wallet['name'] ?? "{$wallet['blockchain']}-{$wallet['wallet_address']}";

    if (!isset($totals[$user_id])) {
        $totals[$user_id] = [
            'diffExist'     => false,
            'totalWallets'  => 0,
            'totalRewards'  => 0,
            'totalBalance'  => 0,
        ];
    }

    $chain   = $wallet['blockchain'];
    $token   = $wallet['token'];
    $token_meta = $pools_index[$chain][$token] ?? null;
    if (!$token_meta || empty($token_meta['tokenAddress'])) {
        return;
    }
    $token_address = $token_meta['tokenAddress'];
    $decimals      = (int)($token_meta['decimals'] ?? 0);

    // Fetch current reward and balance
    $current_data = getLiquidityDetails($wallet['wallet_address'], $token_address);
    if (!$current_data) {
        return;
    }

    $last_rewards_row = fetchLastRewards($pdo, $wallet_id);
    $last_amount      = $last_rewards_row['reward_amount'] ?? 0;
    $diff_reward      = round($current_data['rewardDebt'] - $last_amount, 2);

    $threshold_hit = $diff_reward >= (float)$wallet['threshold'];

    $wants_hourly = ($wallet['report_frequency'] === 'Hourly' && $is_hourly_window);
    $wants_daily  = ($wallet['report_frequency'] === 'Daily'  && $is_daily_window);
    $wants_weekly = ($wallet['report_frequency'] === 'Weekly' && $is_weekly_window);

    $current_balance = $current_data['lpAmount'] / (10 ** 3);

    // Save new rewards
    saveRewards($pdo, $wallet_id, $current_data['rewardDebt'], $current_balance);

    if ($threshold_hit) {
        sendTelegramMessage(
            $user_id,
            "🎉 Your reward for *$name* increased by *$diff_reward*.\n".
            "Now claimable: *{$current_data['rewardDebt']}* {$token}\n".
            "Balance: *{$current_balance}* {$token}"
        );
        $totals[$user_id]['diffExist'] = true;
    }

    if ($wants_hourly || $wants_daily || $wants_weekly) {
        $interval = $wants_hourly ? 3 : ($wants_daily ? 24 : 168);
        $summary  = generateSummary($pdo, $wallet_id, $interval);
        sendTelegramMessage($user_id, "📊 Report for *$name*:\n$summary");
    }

    $totals[$user_id]['totalWallets']++;
    $totals[$user_id]['totalRewards'] += $current_data['rewardDebt'];
    $totals[$user_id]['totalBalance'] += $current_balance;
}

// Fetch last rewards for a wallet
function fetchLastRewards(PDO $pdo, int $wallet_id): array
{
    $stmt = $pdo->prepare(
        'SELECT reward_amount, balance_amount FROM rewards WHERE wallet_id = :wallet_id ORDER BY created_at DESC LIMIT 1'
    );
    $stmt->bindValue(':wallet_id', $wallet_id, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
}

// Save rewards into the database
function saveRewards(PDO $pdo, int $wallet_id, float $reward, float $balance): void
{
    $stmt = $pdo->prepare(
        'INSERT INTO rewards (wallet_id, reward_amount, balance_amount, created_at) VALUES (:wallet_id, :reward_amount, :balance_amount, :created_at)'
    );
    $stmt->bindValue(':wallet_id', $wallet_id, PDO::PARAM_INT);
    $stmt->bindValue(':reward_amount', $reward);
    $stmt->bindValue(':balance_amount', $balance);
    $stmt->bindValue(':created_at', date('Y-m-d H:i:s'));
    $stmt->execute();
}

// Notify totals to the user
function notifyTotals(array $totals): void
{
    foreach ($totals as $user_id => $total) {
        if (!$total['diffExist']) {
            continue;
        }
        sendTelegramMessage($user_id, "📈 Overview across all your wallets:\n".
            "Total wallets: *{$total['totalWallets']}*\n".
            "Total rewards: *{$total['totalRewards']}*\n".
            "Total balance: *{$total['totalBalance']}*");
    }
}

// Send Telegram message
function sendTelegramMessage(int $chat_id, string $text): void
{
    Request::sendMessage([
        'chat_id' => $chat_id,
        'text' => $text,
        'parse_mode' => 'Markdown',
    ]);
}

// Log a message
function logMessage(string $message): void
{
    echo $message . "\n";
}

// Handle errors
function handleError(Exception $e): void
{
    error_log('Error: ' . $e->getMessage());
    echo "An error occurred. Check logs for details.\n";
}

function fetchPoolsIndex(): array
{
    global $config;
    $base = trim($config['allbridge_core_api_url']);
    $url  = rtrim($base, '/') . '/chains';
    $response = file_get_contents($url);
    $data = json_decode($response, true);

    $index = [];
    foreach ($data as $pool_data) {
        $chain_symbol = $pool_data['chainSymbol'] ?? null;
        if (!$chain_symbol) {
            continue;
        }
        if (!isset($index[$chain_symbol])) {
            $index[$chain_symbol] = [];
        }
        foreach ($pool_data['tokens'] as $token) {
            $symbol = $token['symbol'] ?? null;
            if (!$symbol) {
                continue;
            }
            $index[$chain_symbol][$symbol] = [
                'tokenAddress' => $token['tokenAddress'] ?? null,
                'decimals'     => $token['decimals'] ?? 0,
            ];
        }
    }
    return $index;
}

function getLiquidityDetails(string $walletAddress, $tokenAddress): array
{
    global $config;
    $base = trim($config['allbridge_core_api_url']);
    $url  = rtrim($base, '/') . '/liquidity/details';
    $url .= '?ownerAddress=' . $walletAddress;
    $url .= '&tokenAddress=' . $tokenAddress;
    $response = file_get_contents($url);
    $data = json_decode($response, true);

    return $data;
}

// Function to generate daily summary
function generateSummary(PDO $pdo, int $wallet_id, int $interval = 1): string
{
    // Fetch rewards for the last interval in hours
    $stmt = $pdo->prepare(
        'SELECT reward_amount, balance_amount, created_at 
         FROM rewards 
         WHERE wallet_id = :wallet_id AND created_at >= NOW() - INTERVAL :interval HOUR 
         ORDER BY created_at ASC'
    );
    $stmt->bindValue(':wallet_id', $wallet_id, PDO::PARAM_INT);
    $stmt->bindValue(':interval', $interval, PDO::PARAM_INT);
    $stmt->execute();
    $rewards = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($rewards)) {
        return "There are no records yet.";
    }

    $summary = '';
    $total_difference_rewards = 0;
    $total_difference_balance = 0;
    $last_amount = round($rewards[0]['reward_amount'], 2);
    $last_balance = round($rewards[0]['balance_amount'], 2);

    foreach ($rewards as $reward) {
        $difference_rewards = round($reward['reward_amount'], 2) - $last_amount;
        $difference_balance = round($reward['balance_amount'], 2) - $last_balance;
        if ($difference_rewards == 0 && $difference_balance == 0) {
            continue;
        }
        $total_difference_rewards += $difference_rewards;
        $total_difference_balance += $difference_balance;
        $summary .= date('H:i:s', strtotime($reward['created_at'])) . " – rewards: " . ($difference_rewards > 0 ? '+' : '-') . " $difference_rewards". ($difference_balance > 0 ? " | balance: $difference_balance" : "" ) . " \n";
        $last_amount = round($reward['reward_amount'], 2);
        $last_balance = round($reward['balance_amount'], 2);
    }
    if ($total_difference_rewards == 0 && $total_difference_balance == 0 && count($rewards) > 1) {
        $interval_text = "";
        if ($interval < 24) {
            $interval_text = "hour";
        }
        if ($interval == 24) {
            $interval_text = "day";
        }
        if ($interval == 168) {
            $interval_text = "week";
        }
        return "No balance change recorded in the last $interval_text.";
    }
    if ($total_difference_rewards > 0) {
        $summary .= "\nTotal rewards change: +$total_difference_rewards";
    }
    if ($total_difference_balance > 0) {
        $summary .= "\nTotal balance change: +$total_difference_balance";
    }

    return $summary;
}