Bot Data, SQLite and Durable Work

Raw MD
Prepared statements, transactions, state, backups and duplicate updates.

Variables disappear after an ordinary PHP request ends. Store conversation state, job progress and business records in the bot's own database.db. Platform MySQL stores platform data and is a different boundary; hosted bots must not use internal platform database functions.

Open your bot database

This is plain PHP. It does not need tg_all_fun(). The example requires PDO SQLite and a writable bot directory.

Code example
<?php
declare(strict_types=1);
$db = new PDO('sqlite:' . __DIR__ . '/database.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$db->exec('PRAGMA busy_timeout = 5000');
$db->exec('PRAGMA journal_mode = WAL');
$db->exec('CREATE TABLE IF NOT EXISTS conversation_state (
    chat_id TEXT PRIMARY KEY,
    step TEXT NOT NULL,
    updated_at INTEGER NOT NULL
)');

WAL permits useful reader/writer concurrency; it does not create unlimited concurrent writers. Keep write transactions short. Do not hold a transaction open while waiting for Telegram or another HTTP service.

Parameterized reads and writes

The following fragment assumes $db, $chatId and $step are already defined. Values come through bound parameters; table/column names should come from your fixed schema, not user input.

Code example
$save = $db->prepare('INSERT INTO conversation_state(chat_id, step, updated_at)
    VALUES(:chat, :step, :time)
    ON CONFLICT(chat_id) DO UPDATE SET step=excluded.step, updated_at=excluded.updated_at');
$save->execute(['chat'=>(string)$chatId, 'step'=>$step, 'time'=>time()]);
$read = $db->prepare('SELECT step FROM conversation_state WHERE chat_id = ?');
$read->execute([(string)$chatId]);
$currentStep = $read->fetchColumn();

Do not build SQL with concatenated usernames, message text or callback payloads. Return only the data needed by the operation; avoid sending raw database errors or paths to chat users.

Group related local changes

Code example
$db->beginTransaction();
try {
    // Execute the related prepared database statements here.
    $db->commit();
} catch (Throwable $e) {
    if ($db->inTransaction()) {
        $db->rollBack();
    }
    throw $e;
}

A database transaction cannot roll back a Telegram message already sent. An HTTP timeout also does not prove an external action failed. For consequential actions, keep an operation record with a unique key, current state and external result identifiers. Reconcile uncertain results before retrying.

Duplicate updates and idempotency

Telegram can deliver an update again. Do not award credits twice merely because the webhook runs twice. Use a unique database key for the relevant update/order/payment event and perform local state changes atomically. Marking an update processed before an external request can lose work if the request never happens; marking it afterwards can duplicate side effects if the process crashes. An outbox/job state machine makes this boundary explicit, but still requires reconciliation for ambiguous external results.

For a simple greeting bot, a duplicate reply may be acceptable. Payment fulfillment, wallet adjustments and irreversible actions need a stronger design. Do not reuse a greeting-bot deduplication shortcut for accounting.

Schema migrations and backups

Keep schema changes versioned and explicit. Back up data before destructive migrations. CREATE TABLE IF NOT EXISTS does not add a newly introduced column to an existing table. Avoid silently deleting database.db to fix a schema error.

A live WAL database can have recent data in its sidecar files. Use a consistent SQLite backup mechanism or a controlled maintenance procedure instead of copying only database.db while writers are active. Test restores into a separate bot before trusting a backup. Preserve .env separately, with restricted access.

Files versus database rows

Keep media bytes on disk when required; store metadata and Telegram IDs in the database. Use paths inside your bot workspace. A temporary download may disappear; do not treat temporary paths as permanent storage. Track quota usage and remove only files owned by your application.

For small JSON state files, writes need appropriate locking and error checks. JSON files are not a substitute for transactional concurrent updates. Do not create paths directly from user-provided filenames.

See PDO transactions, update routing, and file examples.