Telegram Bot PostgreSQL Analytics Guide: Best Practices for Sessions and Events Table Schema Design

When building a Telegram Bot for customer service or operations, the most overlooked aspect is often the data foundation. Many teams start with BotFather and a simple webhook to receive messages, with logs scattered across files or a single message table. When you want to answer questions like “Which agent responds the fastest?”, “What is the average time from ad click to conversation?”, or “How many closed conversations this week were transferred?”, you find that data cannot be correlated at all.

This is why a structured PostgreSQL analytics schema is essential for a custom Telegram Bot. A good schema design elevates you from “looking at logs” to “viewing reports”, providing reliable support for operational decisions, agent performance evaluation, and conversion attribution. This article uses a customer service analytics scenario to provide a practical schema approach, covering sessions table, events table, and common report queries.

---

Why Does a Custom Telegram Bot Need a PostgreSQL Analytics Schema?

If your bot only handles simple auto-replies, a single table for messages might suffice. But once you involve human agents, multi-channel lead generation, conversation transfers, and user segmentation, your data needs quickly become complex:

• Scattered logs: Messages, status changes, and diversion clicks are mixed together, unable to be aggregated by conversation.
• No unified conversation association: A user sends a message, an agent replies—they belong to the same conversation, but lack a “conversation ID” to thread them together.
• Difficult conversion attribution: From ad link click → bot launch → auto-reply → human agent handoff, any data loss at any step prevents you from evaluating channel effectiveness.

The role of a structured PostgreSQL schema is to organize scattered data into an analyzable model using two core tables (conversations and events). Subsequent reports, user profiles, and funnel analysis are all based on these two tables.

---

Core Table Design (1): Conversations Table — The Foundation of Customer Service Analytics

The conversations table is the starting point for customer service analytics. It records the complete lifecycle of a human-bot conversation: from session initiation, to agent intervention, to session closure.

SQL Example for Table Creation

CREATE TABLE conversations (
    id BIGSERIAL PRIMARY KEY,
    bot_id BIGINT NOT NULL,          -- 关联 Bot 项目
    user_id BIGINT NOT NULL,         -- Telegram 用户 ID
    staff_id BIGINT,                 -- 最终处理的坐席 ID
    status VARCHAR(20) NOT NULL DEFAULT 'open',  -- open | closed | pending
    channel_source VARCHAR(50),      -- 来源渠道，如 diversion_link / direct
    start_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    end_at TIMESTAMPTZ,
    first_response_time INT,         -- 秒，用户首次发送到坐席首次回复的间隔
    last_message_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Session State Machine and Key Timestamps

The status field typically includes three enum values:

• open: Conversation in progress; agents or users can still send messages.
• closed: Conversation ended (e.g., agent marked complete or user timeout).
• pending: Waiting for user reply; agent has replied but user hasn’t responded.

Key timestamps:

• first_response_time: Calculated as staff_first_reply_at - start_at. This value directly reflects agent response speed and is a core metric for agent evaluation.
• last_message_at: Records the time of the last message, used to determine session timeout (e.g., auto-close after 24 hours of inactivity).

Foreign Key Relationships and Index Optimization

• user_id should link to a user table (users), storing Telegram user basic info (username, language code, etc.).
• staff_id links to an agent table (staffs), recording agent name, group, etc.
• Recommended composite indexes:
  • (bot_id, status): Quickly count open/closed conversations for a bot.
  • (staff_id, created_at): View historical volume by agent.
  • (start_at, status): Analyze conversation trends over time.

---

Core Table Design (2): Events Table — Fine-Grained Behavior Tracking

The conversations table records “results,” while the events table records “process.” Each message, status change, or diversion link click is an atomic event.

SQL Example for Table Creation

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    conversation_id BIGINT NOT NULL REFERENCES conversations(id),
    event_type VARCHAR(50) NOT NULL,  -- message_sent, message_received, transfer, diversion_click, risk_alert
    payload JSONB,                    -- 事件详情，如消息内容、IP、UA
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Common Event Types and Payload Structure

Event Type	Payload Example
message_sent	{"content": "您好，有什么可以帮您？", "staff_id": 123}
message_received	{"content": "我想咨询订单", "user_id": 456}
transfer	{"from_staff": 123, "to_staff": 456, "reason": "业务不匹配"}
diversion_click	{"ip": "203.0.113.1", "ua": "Mozilla/5.0 ...", "campaign": "ad_campaign_1"}
risk_alert	{"risk_word": "wallet_address", "staff_id": 123, "triggered": true}

Using JSONB for payload allows you to add arbitrary fields without modifying the table structure. For example, if you later want to add a referrer field to the diversion_click event, simply write it into the JSON.

Query Scenario Distinction: Events Table vs Conversations Table

• Query conversations table: When you want to know “How many conversations were closed today?” or “Which agent handled the most?”, query the conversations table directly.
• Query events table: When you want to analyze “How many steps on average from diversion link click to first reply?” or “A specific user’s operation path?”, sort events by conversation_id.

Design principle: The conversations table stores state snapshots (who is handling, whether ended), while the events table stores change sequences (what was done at each step). Do not merge them, otherwise queries will slow down due to data redundancy.

---

Advanced Analytics: Report Query Examples Based on Sessions and Events

The following SQL assumes your database has real data based on the above schema.

Example 1: Daily Agent Volume Count

SELECT
    DATE(start_at) AS day,
    staff_id,
    COUNT(*) AS total_conversations,
    COUNT(*) FILTER (WHERE status = 'closed') AS closed_conversations
FROM conversations
WHERE bot_id = 101
  AND start_at >= '2025-01-01'
GROUP BY DATE(start_at), staff_id
ORDER BY day, staff_id;

Interpretation: This query gives you a quick view of each agent’s daily workload and closure rate (percentage of closed). If an agent’s closure rate is persistently low, it may indicate frequent conversation transfers.

Example 2: User First Response Time Distribution

WITH response_times AS (
    SELECT
        user_id,
        first_response_time
    FROM conversations
    WHERE bot_id = 101
      AND first_response_time IS NOT NULL
)
SELECT
    CASE
        WHEN first_response_time < 30 THEN '0-30秒'
        WHEN first_response_time < 60 THEN '30-60秒'
        WHEN first_response_time < 300 THEN '1-5分钟'
        ELSE '5分钟以上'
    END AS response_bucket,
    COUNT(*) AS count
FROM response_times
GROUP BY response_bucket
ORDER BY response_bucket;

Interpretation: Grouping first response times reveals the overall efficiency of your customer service team. If most conversations fall into “5+ minutes,” you need to optimize agent scheduling or routing rules.

Example 3: Diversion Link Conversion Funnel

WITH funnel AS (
    SELECT
        c.id AS conversation_id,
        EXISTS (
            SELECT 1 FROM events e
            WHERE e.conversation_id = c.id
              AND e.event_type = 'diversion_click'
        ) AS has_click,
        EXISTS (
            SELECT 1 FROM events e
            WHERE e.conversation_id = c.id
              AND e.event_type = 'message_received'
        ) AS has_message,
        EXISTS (
            SELECT 1 FROM events e
            WHERE e.conversation_id = c.id
              AND e.event_type = 'message_sent'
        ) AS has_reply
    FROM conversations c
    WHERE c.bot_id = 101
)
SELECT
    COUNT(*) AS total_conversations,
    COUNT(*) FILTER (WHERE has_click) AS clicked,
    COUNT(*) FILTER (WHERE has_click AND has_message) AS messaged,
    COUNT(*) FILTER (WHERE has_click AND has_message AND has_reply) AS replied
FROM funnel;

Interpretation: This funnel helps you evaluate the loss rate at each step from lead generation to customer service conversion. For example, if 70% of users who clicked a diversion link sent a message, but only 40% received an agent reply—the issue may lie in insufficient agents or poor routing rules.

---

Common Design Pitfalls and How to Avoid Them

Pitfall 1: Too Many Fields, Premature Optimization

Some teams add many redundant fields during schema design (e.g., user_name, staff_name), causing updates to require syncing multiple tables. Suggestion: Keep only ID foreign keys; retrieve attributes like names via JOIN queries or use materialized views for caching.

Pitfall 2: Ignoring Data Cleanup Strategies

The events table grows fastest. Without cleanup, millions of rows can accumulate in a month. Suggestion:

• Set up monthly partitions, retaining events from the last 6–12 months.
• For sessions older than 12 months, archive to cold storage (e.g., CSV export or S3).

Pitfall 3: Not Isolating Multiple Bot Projects

If your platform manages multiple bots, core tables must include a bot_id field with a foreign key to the bots table. Always filter queries by bot_id to avoid data confusion. This is a basic requirement for multi-tenant design.

Pitfall 4: Missing Unique Constraint on Event Table

The same event may be inserted twice due to retry mechanisms. Suggestion: Add a unique constraint on (conversation_id, event_type, created_at) in the events table, or implement idempotent writes at the application layer.

---

From Self-Built to SaaS: When to Migrate to TG-Staff?

Building your own PostgreSQL schema offers flexibility and control, but the cost is significant: you need development time for table design, business logic, and database performance maintenance. Consider migrating to a mature platform when your team faces:

• High maintenance costs: Requires dedicated personnel for DB management, slow query optimization, and data consistency.
• Lack of real-time customer service dashboard: Self-built reports are often T+1, unable to view agent status or session lists in real time.
• Need for internal compliance and risk control: Building content moderation (e.g., wallet address monitoring) requires extra development and real-time guarantees are hard.
• Multi-language translation: Self-built translation needs API integration, quota management, and result caching, adding significant workload.

TG-Staff directly provides these analytical capabilities without needing to build your own schema:

• User profiles and session records are automatically stored; filter by agent, time, and status in the console.
• Attribution for split links: automatically captures IP, UA, and URL parameters—no need to design your own event table.
• Content moderation: built-in risk word detection and wallet address monitoring with audit logs.
• Automatic translation: AI translation in the standard plan, with DeepL/Google professional translation in the pro plan.

If you’re still in the self-hosting phase, the above schema can be a starting point. However, if your team has limited resources or wants to quickly roll out professional customer support capabilities, consider trying the free trial of TG-Staff.

---

Frequently Asked Questions

Q: How much storage space does the self-built PostgreSQL schema require?
A: It depends on daily active users and message volume. Estimate roughly 50–100 MB per 100,000 events. The events table can be partitioned by month, with periodic archiving of historical data.

Q: How to ensure data consistency between the conversation table and the event table?
A: It’s recommended to use transactions (BEGIN/COMMIT) at the application layer to write to both tables simultaneously; alternatively, adopt an event-driven architecture—write to events first, then aggregate conversation state via asynchronous tasks.

Q: Does the schema design support multiple bot projects?
A: Yes. Core tables must include the bot_id field and establish a foreign key relationship to the bots table; always filter by bot_id in queries to avoid data mixing.

Q: Can I implement a real-time customer service dashboard directly in PostgreSQL?
A: You can use PostgreSQL’s LISTEN/NOTIFY or third-party tools (like Grafana) for near-real-time dashboards, but real-time two-way chat still requires a WebSocket layer; if you need an out-of-the-box solution, refer to TG-Staff’s web console.

Q: Should the event table’s payload use JSON or JSONB?
A: JSONB is recommended—it supports indexing and more efficient queries (e.g., payload->>'ip') and offers slightly better storage space; be sure to limit the payload field size (suggest < 8KB) to avoid performance degradation.

---

If you want to skip schema design and directly obtain professional customer support analytics, you can register for a TG-Staff free trial, or contact @tgstaff_robot for plan details. Also, feel free to visit the documentation for API and integration solutions.