SQLite's Quiet Strength: Replacing Chaos with One Database

SQLite’s Quiet Strength: Why One Database Beat a Complex Infrastructure

The Telegram bot was managing users beautifully, but it had a blind spot. As the bot-social-publisher project scaled—new users launching campaigns daily, feature requests piling up—there was nowhere permanent to store critical information about which chats the bot actually manages, who owns them, or what settings apply to each conversation. Everything lived in process memory or scattered across handler functions. When the service restarted, that knowledge evaporated.

The real problem wasn’t the lack of a database. The project already had data/agent.db running SQLite with a solid UserManager handling persistence through aiosqlite, enabling async database access without blocking the event loop. The decision crystallized immediately: stop fragmenting the data layer. One database. One connection pattern. One source of truth.

First, I examined the existing architecture. UserManager wasn’t fancy—no ORM abstractions, no excessive patterns. It used parameterized queries for safety, leveraged aiosqlite for async operations, and kept the logic straightforward. That became the blueprint. I sketched out the managed_chats schema: chat_id as the primary key, owner_id linking to users, chat_type with a CHECK constraint to validate only legitimate Telegram chat types (private, group, supergroup, channel), a title field, and a JSON column for future extensibility. The critical piece was the index on owner_id—users would constantly query their own managed chats, and sequential table scans don’t scale gracefully.

Rather than introduce another layer—a cache, a separate microservice, an ORM framework—I replicated the UserManager pattern exactly. Same dependency injection, same async/await style, same single connection point for the entire application. The new ChatManager exposed three core methods: add_chat() to register managed conversations, is_managed() to verify whether the bot should handle incoming events, and get_owner() to check permissions. Every database interaction used parameterized statements, eliminating SQL injection risk at the source.

Here’s where SQLite surprised me. Using INSERT OR REPLACE with chat_id as the primary key created elegant behavior for free. If a chat got re-registered with updated metadata, the old record simply evaporated. It wasn’t explicitly designed—it emerged naturally from the schema structure.

An often-missed reality about SQLite: developers dismiss it as a testing toy, but with proper indexing and prepared statements, it handles millions of rows reliably. The overhead of Redis caching or a separate PostgreSQL instance didn’t make sense at this growth stage.

The result: one database, one familiar pattern, one mental model to maintain. When analytics queries eventually demand complexity, the index is already there. When chat permissions or advanced settings need storage, the JSON field waits. When it’s time to analyze bot behavior across millions of chats, the foundation won’t require a painful rewrite—just optimization.

Deferring complex infrastructure until it’s actually needed beats over-engineering from day one.

😄 Developer: “I understand distributed databases.” HR: “And your experience level?” Developer: “According to Stack Overflow comments.”