Skip to content
This repository was archived by the owner on Aug 30, 2026. It is now read-only.
BranchesTags

Repository files navigation

Telegram Logger Bot

Silent chat logger for AI-powered analysis

Python 3.9+ aiogram 3.x SQLite License Version Tests

English | Русский

Why This Tool?

Modern teams communicate in Telegram. Conversations contain decisions, action items, feedback, and context that gets lost in the chat scroll. This bot captures everything silently and stores it in a structured database that AI can query directly.

The problem:

  • Important decisions get buried in chat history
  • No searchable archive of group discussions
  • AI assistants can't access your Telegram chat context
  • Manual chat exports are messy and unstructured

The solution: Add this bot to your group chats. It silently records every message, media metadata, and event into a normalized SQLite database. Then point any AI tool at the database and ask questions about your conversations.

Зачем этот инструмент?

Современные команды общаются в Telegram. В переписках содержатся решения, задачи, обратная связь и контекст, который теряется в потоке сообщений. Этот бот молча записывает всё в структурированную базу данных, которую AI может читать напрямую.

Проблема:

  • Важные решения теряются в истории чата
  • Нет удобного архива групповых обсуждений
  • AI-ассистенты не имеют доступа к контексту из Telegram
  • Ручной экспорт чатов неструктурирован и неудобен

Решение: Добавьте бота в групповые чаты. Он молча записывает каждое сообщение, метаданные медиа и события в нормализованную SQLite базу. Затем подключите любой AI-инструмент к базе и задавайте вопросы о ваших переписках.

Features

Feature Description
Silent logging Bot never responds in chat — invisible to participants
All message types Text, photos, videos, documents, audio, voice, video notes, stickers, GIFs
Service events Member join/leave, title changes, pinned messages
Edit tracking Edited messages logged separately with is_edit flag
Reply chains reply_to field links responses to original messages
Forward origin Full chain: user, chat, channel, or hidden user
Normalized DB 4 tables, no data duplication — minimal token cost for AI
Media download Optional: save files to disk or just log metadata
Albums kept whole media_group_id ties an album's files to the caption Telegram puts on the first one
Multi-chat One bot instance handles unlimited groups simultaneously
Chat allowlist Optional: confine logging to named chats, so being added elsewhere collects nothing
Error resilient Errors in one handler don't crash the bot or lose other messages

Quick Start

1. Create a Telegram Bot

  1. Open @BotFather in Telegram
  2. Send /newbot and follow the instructions
  3. Copy the API token
  4. Important: Send /setprivacy to @BotFather, select your bot, choose Disable

Without disabling privacy mode, the bot can only see commands (/) and direct mentions. It won't log regular messages.

2. Install & Configure

git clone https://github.com/snjrusmn/telegram-logger-bot.git
cd telegram-logger-bot

cp .env.example .env

Edit .env and paste your bot token:

BOT_TOKEN=123456789:ABCdefGHIjklMNOpqrsTUVwxyz
DOWNLOAD_MEDIA=false
DATA_DIR=./data

3. Run

pip install -r requirements.txt
python bot.py

4. Add to Chats

Add the bot to any group chat. It will start logging immediately. No commands needed.

Configuration

Variable Default Description
BOT_TOKEN (required) Telegram Bot API token from @BotFather
DOWNLOAD_MEDIA false Download media files to disk. Accepts: true, false, 1, 0, yes, no
DATA_DIR ./data Directory for the SQLite database and downloaded media files
ALLOWED_CHAT_IDS (empty) Comma-separated chat ids to log. Empty means every chat the bot is in. Set it when the bot must stay confined to one group even if someone adds it elsewhere

When DOWNLOAD_MEDIA=true, files are saved to DATA_DIR/media/ with the naming pattern:

{chat_id}_{message_id}_{original_filename}

The saved path is also stored in the message's media metadata as path, so a reader of the database does not have to reconstruct filenames. Note that the Telegram Bot API only hands bots files up to 20 MB — a larger file is still logged (id, name, size), just not downloaded, and its metadata carries no path.

Docker

cp .env.example .env      # fill in BOT_TOKEN
docker compose up -d --build
docker compose logs -f

The database and media land in ./data next to the compose file — a plain bind mount, so ordinary file backups and other tools on the host can reach them.

Project Structure

telegram-logger-bot/
│
├── bot.py                 # Entry point: bot lifecycle, DI setup, polling
├── config.py              # Configuration loader from .env
├── db.py                  # SQLite layer: schema, upsert, insert functions
├── handlers.py            # All message/media/event handlers (aiogram Router)
│
├── tests/                 # Test suite (69 tests)
│   ├── __init__.py
│   ├── test_config.py     # Config parsing tests (26 tests)
│   ├── test_db.py         # Database operations tests (15 tests)
│   └── test_handlers.py   # Handler logic & media extraction tests (28 tests)
│
├── data/                  # Created at runtime (gitignored)
│   ├── logger.db          # SQLite database
│   └── media/             # Downloaded files (if enabled)
│
├── docs/plans/            # Implementation plans
├── Dockerfile             # Container image
├── docker-compose.yml     # Deployment: bind-mounted ./data volume
├── .dockerignore
├── .env.example           # Environment template
├── .gitignore
├── requirements.txt       # Python dependencies
├── VERSION                # Semantic version (1.0.0)
└── LICENSE                # Apache 2.0

Architecture

Telegram API
    │
    ▼
┌─────────┐     ┌──────────────┐     ┌────────┐
│  bot.py  │────▶│ handlers.py  │────▶│ db.py  │──▶ SQLite
│          │     │  (Router)    │     │        │
│ Polling  │     │              │     │ upsert │
│ DI Setup │     │ text handler │     │ insert │
│ Startup  │     │ media handler│     │        │
│ Shutdown │     │ event handler│     └────────┘
└─────────┘     │ edit handler │
                │ catch-all    │
                └──────────────┘
                       │
                       ▼
                 ┌──────────┐
                 │config.py │
                 │ .env     │
                 └──────────┘

Data flow:

  1. bot.py starts polling Telegram API via aiogram
  2. Each incoming update is routed to the appropriate handler in handlers.py
  3. Handlers extract structured data and call db.py functions
  4. db.py writes to SQLite with WAL mode for concurrent read safety
  5. Dependencies (db, config) are injected via aiogram's built-in DI system

Database Schema

The database is designed for minimal token consumption when used with AI/LLM tools. Data is normalized into 4 tables to avoid repeating usernames, chat titles, etc. in every row.

┌──────────────┐       ┌──────────────────────────────────────┐
│    chats     │       │             messages                 │
├──────────────┤       ├──────────────────────────────────────┤
│ chat_id (PK) │◄──────│ chat_id (FK)                        │
│ title        │       │ user_id (FK, nullable)               │
│ type         │       │ msg_id, date, type, text             │
│ updated_at   │       │ media_file_id, media_meta (JSON)     │
└──────────────┘       │ reply_to, fwd_from, fwd_name         │
                       │ is_edit, created_at                   │
┌──────────────┐       └──────────────────────────────────────┘
│    users     │
├──────────────┤       ┌──────────────────────────────────────┐
│ user_id (PK) │       │             events                   │
│ username     │       ├──────────────────────────────────────┤
│ first_name   │       │ chat_id (FK)                         │
│ last_name    │       │ user_id, type                        │
│ updated_at   │       │ data (JSON), date, created_at        │
└──────────────┘       └──────────────────────────────────────┘

What Gets Logged

Source Type Stored As
Text messages text Full text in messages.text
Photos photo file_id + {size, width, height} in media_meta
Videos video file_id + {size, mime, duration, width, height}
Documents document file_id + {size, mime, name}
Audio audio file_id + {size, mime, name, duration}
Voice messages voice file_id + {size, mime, duration}
Video notes video_note file_id + {size, duration, length}
Stickers sticker file_id + {emoji, set_name, width, height}
GIFs/Animations animation file_id + {size, mime, duration, width, height}
Edited messages any + is_edit=1 New row with updated content
Replies any + reply_to Links to original msg_id
Forwards any + fwd_from/fwd_name Origin user/chat/channel info
Member joined member_joined event {username, first_name} in events
Member left member_left event {username, first_name} in events
Title changed title_changed event {new_title} in events
Message pinned message_pinned event {pinned_message_id} in events
Other (polls, etc.) other content_type string in text

Media Metadata Format

Only non-null fields are stored to save space:

{"size": 52480, "mime": "image/jpeg", "width": 1920, "height": 1080,
 "group": "13847290184", "path": "/data/media/-4228822135_881_file_12.jpg"}
Field Meaning
group Album id, present only when the file arrived as part of an album. Telegram splits an album into separate messages and puts the caption on the first one — this is what ties the caption to the rest of the files
path Where the file was saved. Present only when the download succeeded

Using the Database with AI

The SQLite file at data/logger.db can be directly consumed by AI tools. Here are example queries:

Get all messages with authors

SELECT u.first_name, u.last_name, m.text, m.date, c.title
FROM messages m
JOIN users u ON m.user_id = u.user_id
JOIN chats c ON m.chat_id = c.chat_id
WHERE m.type = 'text'
ORDER BY m.date;

Reconstruct a conversation thread

SELECT
    u.first_name AS author,
    m.text,
    ru.first_name AS reply_to_user
FROM messages m
JOIN users u ON m.user_id = u.user_id
LEFT JOIN messages rm ON m.reply_to = rm.msg_id AND m.chat_id = rm.chat_id
LEFT JOIN users ru ON rm.user_id = ru.user_id
WHERE m.chat_id = ?
ORDER BY m.date;

Chat activity summary

SELECT
    c.title,
    COUNT(m.id) AS total_messages,
    COUNT(DISTINCT m.user_id) AS active_users,
    MIN(m.date) AS first_message,
    MAX(m.date) AS last_message
FROM messages m
JOIN chats c ON m.chat_id = c.chat_id
GROUP BY c.chat_id;

Find who shares the most media

SELECT u.first_name, u.username, COUNT(*) AS media_count
FROM messages m
JOIN users u ON m.user_id = u.user_id
WHERE m.type IN ('photo', 'video', 'document', 'animation')
GROUP BY m.user_id
ORDER BY media_count DESC;

Track message edits

SELECT m.msg_id, m.text, m.is_edit, m.created_at
FROM messages m
WHERE m.chat_id = ? AND m.msg_id = ?
ORDER BY m.created_at;

Member activity timeline

SELECT e.type, u.first_name, u.username, e.date, c.title
FROM events e
LEFT JOIN users u ON e.user_id = u.user_id
JOIN chats c ON e.chat_id = c.chat_id
ORDER BY e.date;

Testing

python -m pytest tests/ -v

The test suite includes 50 tests covering:

  • Configuration parsing (17 tests) — env variables, defaults, edge cases
  • Database operations (15 tests) — CRUD, upserts, schema validation
  • Handler logic (18 tests) — message extraction, media metadata, forward parsing, filename sanitization

Requirements

  • Python 3.9+
  • Dependencies: aiogram, aiosqlite, python-dotenv
  • Dev dependencies: pytest, pytest-asyncio

Roadmap

  • ChatMemberUpdated handlers for large groups (requires bot admin)
  • Configurable media download size limit
  • Database schema migrations
  • Web UI for browsing logs
  • Export to CSV/JSON

License

This project is licensed under the Apache License 2.0.

Copyright 2026 Sanjar Usman.

About

Silent Telegram bot that logs all chat activity into a normalized SQLite database optimized for AI/LLM consumption

Topics

aiaiogrambotchat-loggerllmloggerpythonsqlitetelegramtelegram-bot

Resources

Readme
Apache-2.0 license
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Used by

Contributors

Languages