tg-sub-history-bot

Simple and lightweight Telegram bot that records channel and chat subscriber join/leave events to SQLite. Add the bot as an administrator to a channel or group to start logging.

Why

Telegram does not provide built-in history of subscriber changes. This bot records join and leave events so administrators can analyze channel growth and churn over time.

Stack: Python 3.11+, aiogram 3.x, SQLite, Poetry for dependencies.

Features

• Listens for chat_member updates (join, leave, kick).
• Writes each event to SQLite: user_id, username, channel_id, event_type (joined / left), timestamp.
• Deduplicates by (user_id, channel_id, event_type, timestamp).

Project structure

• bot.py — entry point, /start and chat_member handlers, logging, polling
• db.py — SQLite connection, init, event insert/deduplication
• config.py — BOT_TOKEN, DB_PATH, LOG_FILE, POLLING_TIMEOUT (set token before run)
• schema.py — events table definition
• pyproject.toml — Poetry deps (Python 3.11+, aiogram)

Secrets can live in .env and be loaded in code; the bot does not read .env by default.

Database

Table events; full schema is in schema.py. Columns:

• id — auto-increment primary key
• user_id — Telegram user id
• username — Telegram @username (optional; may be empty)
• channel_id — chat/channel id
• event_type — joined or left
• timestamp — UTC

Example rows:

id	user_id	username	channel_id	event_type	timestamp
1	123456789	johndoe	-1001234567890	joined	2025-03-09 12:00:00
2	123456789	johndoe	-1001234567890	left	2025-03-09 14:30:00

Setup

Prerequisites

• Python 3.11+
• Poetry (pip install poetry or official installer)

Steps

1. Clone the repository.
2. Install dependencies: poetry install.
3. Configure: edit config.py and set BOT_TOKEN (from @BotFather). Optionally set DB_PATH and LOG_FILE.
4. Run: poetry run python bot.py (or poetry shell then python bot.py).

Running

Add the bot as an administrator to the desired Telegram channel or group (with permission to receive chat_member updates). It will log join/leave events to the database; no extra commands are required.

Auto-restart on crash: use systemd (Linux), Supervisor, or PM2 to run the bot as a service and restart it when it stops.

Backups and monitoring: back up the database file (see DB_PATH in config.py) regularly. Watch LOG_FILE for errors and recorded events.

Security: Do not commit a real BOT_TOKEN. Keep it in config.py only on the server, or use .env and load it in code (if so, consider adding config.py to .gitignore when it holds secrets).

Idle CPU: If the bot uses noticeable CPU in idle, increase POLLING_TIMEOUT in config.py (default 30; try 50–60). Higher value = fewer getUpdates cycles and less CPU, at the cost of slightly slower reaction to shutdown.