Persist Web Leads to MySQL Before Dispatching Telegram Claim Keyboards in PHP

Building a web lead capture form that instantly alerts a sales team via a Telegram channel or group chat sounds straightforward. A user fills out their contact details on your site, your PHP backend processes the payload, and a Telegram bot broadcasts an alert containing an inline keyboard button labeled "Claim Lead". When a sales representative clicks the button, the bot assigns the lead to them and updates the broadcast message.

However, naive implementations of this pattern frequently break in production. Developers often attempt to put auto-incrementing database primary keys directly into the inline button's callback_data, or worse, attempt to embed full UUID v4 strings along with action prefixes. Telegram enforces a strict 64-byte limit on callback_data. Exceeding this limit causes the Telegram Bot API to silently truncate or reject your request with a 400 Bad Request: BUTTON_DATA_INVALID response.

Another common failure mode occurs when developers fire the Telegram notification before writing the submission to the database, or without binding the lead to a predictable, collision-resistant identifier. If the database transaction hangs or fails after the Telegram message has been broadcast, a manager will click the claim button only for the webhook script to query a record that does not exist.

To build a bulletproof lead dispatch pipeline in PHP, you must generate a short, cryptographically secure identifier before writing to the database, store the lead in an unassigned state, construct a payload guaranteed to sit under Telegram's 64-byte limit, and process incoming claim button clicks using atomic database operations.

Step 1: Database Schema and Cryptographic Lead Identifiers

Sequential integer IDs (like lead #1042) leak business metrics to anyone monitoring network traffic or bot interactions. Conversely, standard UUID v4 strings are 36 characters long. When paired with an action namespace like claim_lead:, a UUID string easily approaches 50 bytes, leaving virtually no margin for additional metadata or sub-actions.

Using bin2hex(random_bytes(7)) generates a 14-character hexadecimal string representing 56 bits of entropy. This offers over 72 trillion unique combinations, making collision practically impossible for web form leads while keeping byte size exceptionally low.

First, create a leads table in MySQL using PDO. This table tracks the unique lead identifier, customer details, lead assignment status, and the Telegram ID of the manager who claims the record.

CREATE TABLE IF NOT EXISTS leads (
    lead_id VARCHAR(14) NOT NULL PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(50) NOT NULL,
    status ENUM('pending', 'claimed') NOT NULL DEFAULT 'pending',
    claimed_by_user_id BIGINT UNSIGNED NULL,
    claimed_by_username VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    claimed_at TIMESTAMP NULL,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Notice that lead_id is defined as VARCHAR(14). Because a 7-byte raw binary array yields exactly 14 hex characters, bin2hex(random_bytes(7)) aligns perfectly with this column size.

Step 2: Ingesting the Lead and Dispatching the Telegram Alert

When processing incoming web form POST requests, your PHP backend must perform three operations in strict order: 1. Validate incoming form fields. 2. Generate the 14-character lead_id and execute a SQL INSERT inside a PDO transaction. 3. Send an HTML-formatted message to the Telegram manager chat containing an inline keyboard with callback_data formatted as take:<lead_id>.

If you use Telegram's parse_mode=HTML, any user input containing characters like <, >, or & will break Telegram's parser, triggering an HTTP 400 response and dropping the notification entirely. You must run all user-supplied values through htmlspecialchars() before building the message template.

Here is the full implementation for submit.php:

<?php
declare(strict_types=1);

$dbHost = getenv('DB_HOST') ?: '127.0.0.1';
$dbName = getenv('DB_NAME') ?: 'crm';
$dbUser = getenv('DB_USER') ?: 'root';
$dbPass = getenv('DB_PASS') ?: '';
$botToken = getenv('TELEGRAM_BOT_TOKEN') ?: '123456:ABC-DEF1234ghIkl-zyx57W2v1u123ew11';
$managerChatId = getenv('TELEGRAM_MANAGER_CHAT_ID') ?: '-1001234567890';

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode(['error' => 'Method not allowed']);
    exit;
}

$name = trim($_POST['name'] ?? '');
$phone = trim($_POST['phone'] ?? '');

if ($name === '' || $phone === '') {
    http_response_code(400);
    echo json_encode(['error' => 'Name and phone are required']);
    exit;
}

try {
    $pdo = new PDO("mysql:host={$dbHost};dbname={$dbName};charset=utf8mb4", $dbUser, $dbPass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    // Generate a 14-character hex ID (7 bytes * 2 = 14 chars)
    $leadId = bin2hex(random_bytes(7));

    // Step 1: Commit to the database FIRST
    $stmt = $pdo->prepare("
        INSERT INTO leads (lead_id, customer_name, customer_phone, status) 
        VALUES (:lead_id, :name, :phone, 'pending')
    ");
    $stmt->execute([
        ':lead_id' => $leadId,
        ':name' => $name,
        ':phone' => $phone,
    ]);

    // Step 2: Format HTML text securely to prevent Telegram parse errors
    $safeName = htmlspecialchars($name, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    $safePhone = htmlspecialchars($phone, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');

    $messageText = "<b>🚨 New Lead Received</b>

";
    $messageText .= "<b>ID:</b> <code>{$leadId}</code>
";
    $messageText .= "<b>Name:</b> {$safeName}
";
    $messageText .= "<b>Phone:</b> {$safePhone}
";
    $messageText .= "<b>Status:</b> Unassigned";

    // Callback payload: 'take:' (5 bytes) + 14 bytes = 19 bytes (well below 64-byte limit)
    $callbackData = "take:" . $leadId;

    $payload = [
        'chat_id' => $managerChatId,
        'text' => $messageText,
        'parse_mode' => 'HTML',
        'reply_markup' => [
            'inline_keyboard' => [
                [
                    [
                        'text' => '✋ Claim Lead',
                        'callback_data' => $callbackData
                    ]
                ]
            ]
        ]
    ];

    // Step 3: Send Telegram message via cURL
    $ch = curl_init("https://api.telegram.org/bot{$botToken}/sendMessage");
    curl_setopt_array($ch, [
        CURLOPT_POST => true,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
        CURLOPT_POSTFIELDS => json_encode($payload),
        CURLOPT_TIMEOUT => 5,
    ]);

    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $curlError = curl_error($ch);
    curl_close($ch);

    if ($response === false || $httpCode !== 200) {
        error_log("Telegram API Error (HTTP {$httpCode}): {$curlError} | Response: {$response}");
        // Note: The database record exists, so managers can still claim via fallback systems
    }

    echo json_encode(['success' => true, 'lead_id' => $leadId]);

} catch (Exception $e) {
    error_log("Lead Processing Error: " . $e->getMessage());
    http_response_code(500);
    echo json_encode(['error' => 'Internal server error']);
}

Calculating the byte size of your payload ensures you never breach Telegram's boundaries. In the code above, the prefix take: takes up 5 bytes, and $leadId takes up 14 bytes, bringing the total callback_data string length to 19 bytes. This leaves 45 bytes of headroom for future expansion without risk of clipping.

Step 3: Processing Webhook Callbacks Atomically

When a sales manager taps the "Claim Lead" inline button in Telegram, the Telegram Bot API dispatches an update containing a callback_query object to your registered webhook URL.

Two critical requirements must be handled inside your webhook processor:

1. Prevent Race Conditions: If three sales managers tap the button simultaneously, only one manager must successfully claim the record. You achieve this in SQL by adding WHERE status = 'pending' to an UPDATE statement and checking rowCount(). 2. Acknowledge the Callback Immediately: Telegram client UI displays a continuous loading spinner on the manager's screen until your script invokes answerCallbackQuery. You must issue this API request on every incoming click, even if the lead was already claimed by someone else.

Here is the robust webhook processing script (webhook.php):

<?php
declare(strict_types=1);

$botToken = getenv('TELEGRAM_BOT_TOKEN') ?: '123456:ABC-DEF1234ghIkl-zyx57W2v1u123ew11';
$secretToken = getenv('TELEGRAM_WEBHOOK_SECRET') ?: 'my-super-secret-token';

// Validate header secret token from Telegram
$headers = getallheaders();
$receivedToken = $headers['X-Telegram-Bot-Api-Secret-Token'] ?? '';
if ($receivedToken !== $secretToken) {
    http_response_code(403);
    echo "Unauthorized";
    exit;
}

$input = file_get_contents('php://input');
$update = json_decode($input, true);

if (json_last_error() !== JSON_ERROR_NONE || !$update) {
    http_response_code(400);
    echo "Invalid JSON";
    exit;
}

// Ignore updates that are not callback queries
if (!isset($update['callback_query'])) {
    http_response_code(200);
    echo "OK";
    exit;
}

$callbackQuery = $update['callback_query'];
$callbackId = $callbackQuery['id'];
$data = $callbackQuery['data'] ?? '';
$fromUser = $callbackQuery['from'];
$userId = $fromUser['id'];
$userFullName = trim(($fromUser['first_name'] ?? '') . ' ' . ($fromUser['last_name'] ?? ''));
$username = $fromUser['username'] ?? '';

$message = $callbackQuery['message'] ?? null;
$chatId = $message['chat']['id'] ?? null;
$messageId = $message['message_id'] ?? null;

// Parse namespace and lead_id
if (strpos($data, 'take:') !== 0) {
    sendTelegramRequest($botToken, 'answerCallbackQuery', [
        'callback_query_id' => $callbackId,
        'text' => 'Unknown action.',
        'show_alert' => true,
    ]);
    exit;
}

$leadId = substr($data, 5);

$dbHost = getenv('DB_HOST') ?: '127.0.0.1';
$dbName = getenv('DB_NAME') ?: 'crm';
$dbUser = getenv('DB_USER') ?: 'root';
$dbPass = getenv('DB_PASS') ?: '';

try {
    $pdo = new PDO("mysql:host={$dbHost};dbname={$dbName};charset=utf8mb4", $dbUser, $dbPass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    // Atomic update to prevent race conditions across multiple managers
    $stmt = $pdo->prepare("
        UPDATE leads 
        SET status = 'claimed', 
            claimed_by_user_id = :user_id, 
            claimed_by_username = :username,
            claimed_at = NOW()
        WHERE lead_id = :lead_id AND status = 'pending'
    ");

    $stmt->execute([
        ':user_id' => $userId,
        ':username' => $username ?: $userFullName,
        ':lead_id' => $leadId,
    ]);

    if ($stmt->rowCount() === 1) {
        // Success: This manager got the lead first
        sendTelegramRequest($botToken, 'answerCallbackQuery', [
            'callback_query_id' => $callbackId,
            'text' => '✅ You successfully claimed this lead!',
            'show_alert' => false,
        ]);

        // Edit the original chat message to update the lead status and remove the button
        if ($message && $chatId && $messageId) {
            $existingText = $message['text'] ?? '';
            $claimantName = htmlspecialchars($userFullName ?: ($username ? '@' . $username : (string)$userId), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
            
            $updatedText = str_replace("Status: Unassigned", "Status: Claimed by {$claimantName}", $existingText);

            sendTelegramRequest($botToken, 'editMessageText', [
                'chat_id' => $chatId,
                'message_id' => $messageId,
                'text' => $updatedText,
                'parse_mode' => 'HTML',
                'reply_markup' => ['inline_keyboard' => []] // Remove keyboard
            ]);
        }
    } else {
        // Row count is 0: The lead was already claimed or does not exist
        sendTelegramRequest($botToken, 'answerCallbackQuery', [
            'callback_query_id' => $callbackId,
            'text' => '❌ Lead has already been claimed by another manager.',
            'show_alert' => true,
        ]);
    }

} catch (Exception $e) {
    error_log("Webhook Error: " . $e->getMessage());
    sendTelegramRequest($botToken, 'answerCallbackQuery', [
        'callback_query_id' => $callbackId,
        'text' => '⚠️ System error while processing lead.',
        'show_alert' => true,
    ]);
}

http_response_code(200);
echo "OK";

function sendTelegramRequest(string $token, string $method, array $params): array
{
    $ch = curl_init("https://api.telegram.org/bot{$token}/{$method}");
    curl_setopt_array($ch, [
        CURLOPT_POST => true,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
        CURLOPT_POSTFIELDS => json_encode($params),
        CURLOPT_TIMEOUT => 3,
    ]);
    
    $result = curl_exec($ch);
    curl_close($ch);
    
    return $result ? (json_decode($result, true) ?: []) : [];
}

Production Notes & Failure Scenarios

Deploying this pipeline requires attention to several real-world operational edge cases:

### 1. Webhook Secret Token Verification When registering your webhook with Telegram via setWebhook, pass a secret_token parameter. Telegram includes this string in every incoming update request as the X-Telegram-Bot-Api-Secret-Token header. Your PHP code must assert that this header matches your secret. If it does not, abort immediately with a 403 Forbidden status code to prevent unauthorized users from spoofing callback queries.

### 2. Idempotency and Webhook Retries If your PHP script takes too long to execute or returns an HTTP 5xx error code, Telegram will retry sending the exact same update multiple times. Because our claim query relies on an atomic UPDATE ... WHERE status = 'pending' check, duplicate deliveries of a claim request will safely hit the rowCount() === 0 fallback path, keeping your database consistent.

### 3. Telegram API Rate Limits (HTTP 429) If your site experiences high submission volumes, Telegram may rate limit outgoing requests (429 Too Many Requests). Ensure your HTTP transport logs retry_after parameters from Telegram responses. For high-volume environments, decouple the lead insertion from Telegram dispatch by pushing notification jobs to a queue system (such as Redis or RabbitMQ) processed by a background worker.

When scaling your customer messaging pipelines or building specialized Mini Apps for your sales teams, working with an experienced development team helps avoid subtle production bottlenecks. BotCreator — studio that ships Telegram bots / Mini Apps.