TG Group Analytics Bot

Telegram group analytics bot + small web UI.

Stack: Node.js + TypeScript, Telegraf (bot), Next.js App Router (web), Postgres (raw SQL via pg), Redis (cache + rate limit), Gemini API (@google/genai), Docker Compose, Vitest.

What It Does

• Ingest group messages (privacy mode must be disabled).
• Bot commands:
  • /stats: top talkers + totals with time range buttons.
  • /analyze: Gemini-backed personality/behavior summary (reply to a user or pass @username).
  • /forgetme: delete your stored messages in the current chat.
• Web:
  • POST /api/analyze: public endpoint with Redis rate limiting; analyzes @username scoped to the most recent chat where that username was seen.
  • Minimal UI at /.

Architecture

Repo layout:

./
├── apps/
│   ├── bot/              # Telegraf bot app
│   └── web/              # Next.js app
└── packages/
    └── shared/           # db/models/redis/services

Key design:

• packages/shared: single source of truth for config, DB access, Redis primitives, and Gemini analysis.
• DB access: raw, parameterized SQL in packages/shared/src/models/ (no ORM).
• Migrations: packages/shared/migrations executed via migrateOnce() with an advisory lock.

Why these technologies:

• Telegraf: solid Telegram bot framework + middleware model.
• Next.js App Router: simplest way to ship a small UI + server route handlers.
• Postgres: durable storage for message history and stats.
• Redis: cheap caching for /stats and rate limiting for public web endpoint.
• Docker Compose: reproducible local/prod-like stack.

Data Stored

Postgres tables (see packages/shared/migrations/001_init.sql):

• chats: chat id + title
• users: user id + username/name
• messages: chat id + message id + user id + message text + timestamp
• chat_users: first/last seen + message_count per (chat,user)

Deletion:

• /forgetme deletes your rows in messages and chat_users for that chat.

AI / Privacy Notes

• The bot stores message text to compute stats and build Gemini prompts.
• The analysis prompt uses the last N messages; each message line is truncated to avoid runaway prompt size.
• Do not log raw message text in production logs.

Setup

1. Create a Telegram bot via @BotFather.

2. Disable privacy mode (CRITICAL): @BotFather -> /setprivacy -> select your bot -> Disable.

If privacy mode is enabled, the bot will only see commands/replies and message ingestion will not work.

3. Create .env in repo root (do not commit). Start from .env.example.

Required:

• BOT_TOKEN
• GEMINI_API_KEY

Run With Docker Compose

Prereqs:

• Docker Engine + docker compose
• docker buildx (on Arch/EndeavourOS: sudo pacman -S docker-buildx)

Run:

docker compose up --build

Web UI: http://localhost:3000

Local Dev

npm install

# shared
npm -w packages/shared run typecheck
npm test

# bot
npm -w apps/bot run dev

# web
npm -w apps/web run dev

Extra Feature: /forgetme

What it does:

• Deletes your stored messages (and chat_users row) for the current chat.

Why:

• Basic privacy control: lets a user remove their historical data on demand.

How to use:

• Run /forgetme in a group and confirm via the inline button.

Decisions And Fixes

• Ingest vs commands: message ingestion ignores /commands to avoid polluting stats and to prevent middleware from blocking command handlers.
• Gemini schema enforcement: switched to SDK-native responseSchema format to avoid the model returning {} and failing validation.
• Docker reliability: ensured images include packages/shared/migrations so migrations can run in containers; documented installing docker-buildx on Arch.

AI Usage

Tools used:

• AI assistance for scaffolding monorepo layout, initial SQL models, and first-pass bot/web wiring.

Where AI helped most:

• Boilerplate generation (Next.js + Telegraf setup), and quickly exploring SDK surface area.

Where manual work was needed:

• Fixing Telegraf middleware ordering (ensuring command handlers still run).
• Making Gemini output reliably structured (correct schema type + retries + truncation).
• Docker runtime correctness (migrations present in runtime images, compose startup behavior).

Rough impact:

• AI reduced time spent on boilerplate substantially; most debugging/polish was manual and verified via the QA checklist.