From Memory to Database: Telegram Chat Management Done Right

Taming Telegram Chats: Building a Management Layer for Async Operations

The bot was working, but there was a growing problem. As the telegram agent system matured, we needed a way to track which chats the bot actually manages, who owns them, and what settings apply. Right now, everything lived in memory or scattered across different systems. It was time to give chats their own database home.

The task was straightforward on the surface: add a new table to the existing SQLite database at data/agent.db to track managed chats. But here’s the thing—we didn’t want to fragment the data infrastructure. The project already had UserManager handling user persistence in the same database, using aiosqlite for async operations. Building a parallel system would have been a disaster waiting to happen.

First thing I did was sketch out the schema. A managed_chats table with fields for chat ID, owner ID, chat type (private, group, supergroup, channel), title, and a JSON blob for future settings. Adding an index on owner_id was essential—we’d be querying by owner constantly to list which chats a user manages. Nothing groundbreaking, but the details matter when you’re hitting the database from async handlers.

Then came the integration piece. Rather than bolting on yet another manager class, I created ChatManager following the exact same pattern as UserManager. Same dependency injection, same async/await style, same connection handling. The methods were simple: add_chat() to register a new managed chat, is_managed() to check if we’re responsible for handling it, and get_owner() to verify permissions. Each one used parameterized queries—no SQL injection vulnerabilities sneaking past.

The real decision was whether to use aiosqlite.connect() repeatedly or maintain a connection pool. Given that the bot might handle hundreds of concurrent chat events, I went with the simpler approach: open, execute, close. Connection pooling could come later if profiling showed it was needed. Keep it simple until metrics say otherwise.

One thing that surprised me: SQLite’s INSERT OR REPLACE behavior handles duplicate chat IDs gracefully. If a chat gets re-added with different settings, the old entry vanishes. This wasn’t explicitly planned—it just fell out naturally from using chat_id as PRIMARY KEY. Turned out to be exactly what we needed for idempotent operations.

The beautiful part? Zero external dependencies. The system already had aiosqlite, structlog for logging, and the config infrastructure in place. I wasn’t adding complexity—just organizing existing pieces into a cleaner shape.

We ended up with a single source of truth for chat state, a consistent pattern for adding new managers, and a foundation that could support fine-grained permissions, audit logging, and feature flags per chat—all without rewriting anything.

😄 Why did the DBA refuse to use SQLite for everything? Because they didn’t want their entire schema fitting in a single emoji.