Telegram Bot PostgreSQL 分析指南：會話表與事件表 Schema 設計最佳實踐

自建 Telegram Bot 做客服或營運時，最容易被忽視的環節是數據底座。很多團隊初期用 BotFather + 簡單 Webhook 接收訊息，日誌散落在檔案或單一訊息表裡。當你想回答「哪個坐席回覆最快？」「用戶從廣告連結到對話的平均時長是多少？」「本週關閉的會話中，有多少被轉接過？」時，才發現數據根本無法關聯查詢。

這就是為什麼自建 Telegram Bot 需要一套結構化的 PostgreSQL 分析 Schema。一個好的 schema 設計，能讓你從「看日誌」升級到「看報表」，為營運決策、客服考核、轉換歸因提供可靠支撐。本文以客服分析場景為例，提供一套可落地的基础 schema 思路，涵蓋會話表、事件表及常見報表查詢。

---

為什麼自建 Telegram Bot 需要一套 PostgreSQL 分析 Schema？

如果你的 Bot 只處理簡單自動回覆，用單表記錄訊息可能就夠了。但一旦涉及人工坐席、多渠道引流、會話轉移、用戶分群，你的數據需求會迅速複雜化：

• 日誌散亂：訊息、狀態變更、分流點擊混在一起，無法按會話聚合。
• 無統一會話關聯：用戶發一則訊息，坐席回覆一則，它們屬於同一個會話，但缺乏一個「會話 ID」來貫穿全程。
• 難以做轉換歸因：用戶從廣告連結點擊 → 跳轉 Bot → 自動回覆 → 人工坐席承接，中間任何環節的數據遺失，都讓你無法評估渠道效果。

結構化 PostgreSQL schema 的作用，就是用兩張核心表（conversations 和 events）將散亂數據組織成可分析的模型。後續的報表、用戶畫像、漏斗分析，都基於這兩張表展開。

---

核心表設計（一）：會話表（conversations）——客服分析的基石

conversations 表是客服分析的起點。它記錄一次完整的人機對話生命週期：從用戶發起會話，到坐席介入，到會話關閉。

建表 SQL 範例

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()
);

會話狀態機與關鍵時間戳

status 欄位通常包含三個列舉值：

• open：會話進行中，坐席或用戶仍可發送訊息。
• closed：會話已結束（例如坐席標記完成或用戶超時）。
• pending：等待用戶回覆，坐席已回覆但用戶未回。

關鍵時間戳：

• first_response_time：計算方式為 staff_first_reply_at - start_at。這個值直接反映坐席回應速度，是客服考核的核心指標。
• last_message_at：記錄最近一則訊息的時間，用於判斷會話是否超時（例如超過 24 小時無訊息可自動關閉）。

外鍵關係與索引最佳化

• user_id 應關聯用戶表（users），儲存 Telegram 用戶的基本資訊（username、語言碼等）。
• staff_id 關聯坐席表（staffs），記錄坐席姓名、分組等。
• 建議複合索引：
  • (bot_id, status)：快速統計某 Bot 的開放/關閉會話數。
  • (staff_id, created_at)：按坐席維度查看歷史接待量。
  • (start_at, status)：按時間範圍分析會話趨勢。

---

核心表設計（二）：事件表（events）——精細行為追蹤

會話表記錄的是「結果」，事件表記錄的是「過程」。每一則訊息、每一次狀態變更、每一次分流連結點擊，都是一個原子事件。

建表 SQL 範例

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()
);

常用事件類型與 payload 結構

事件類型	payload 範例
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}

payload 使用 JSONB 的好處是：你可以在不修改表結構的情況下，增加任意欄位。例如 diversion_click 事件後期想添加 referrer 欄位，直接寫入 JSON 即可。

事件表 vs 會話表的查詢場景區分

• 查會話表：當你想知道「今天關閉了多少會話？」「哪個坐席接待量最高？」時，直接查 conversations 表。
• 查事件表：當你想分析「用戶從分流連結點擊到首次回覆的平均步驟數」「某用戶的操作路徑」時，需要按 conversation_id 排序事件。

設計原則：會話表存狀態快照（當前誰在處理、是否結束），事件表存變更序列（每一步做了什麼）。兩者不要合併，否則查詢時會因數據冗餘而變慢。

---

進階分析：基於會話與事件的報表查詢範例

以下 SQL 基於上述 schema，假設你的資料庫已儲存真實數據。

範例 1：按日統計坐席接待量

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;

解讀：這個查詢讓你快速了解每個坐席每天的接待負載，以及閉環率（closed 佔比）。如果某個坐席閉環率長期偏低，可能需要檢查是否頻繁轉移會話。

範例 2：用戶首次回應時間分佈

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;

解讀：將首次回應時間分組，可以看出客服團隊的整體回應效率。如果大部分會話集中在「5分鐘以上」，就需要最佳化坐席排班或分流規則。

範例 3：分流連結轉換漏斗

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;

解讀：這個漏斗幫你評估引流到客服轉換的每一步流失率。例如，點擊分流連結的用戶中有 70% 發了訊息，但只有 40% 收到了坐席回覆——問題可能出在坐席不足或分流規則不合理。

---

常見設計陷阱與避坑建議

陷阱 1：欄位過多，過早最佳化

有些團隊在 schema 設計階段就加入大量冗餘欄位（如 user_name、staff_name），導致資料更新時需要同步多張表。建議：只保留 ID 外鍵，名稱等屬性透過 JOIN 查詢，或使用具體化檢視表快取。

陷阱 2：忽略資料清理策略

events 表增長最快。如果不清理，一個月可能累積數百萬筆。建議：

• 設定按月分割，保留最近 6–12 個月的事件。
• 對超過 12 個月的會話，可歸檔到冷儲存（如 CSV 匯出或 S3）。

陷阱 3：未考慮多 Bot 專案隔離

如果你的平台管理多個 Bot，核心表必須包含 bot_id 欄位，並建立外鍵關聯到 bots 表。查詢時始終以 bot_id 過濾，避免資料混淆。這是多租戶設計的基本要求。

陷阱 4：事件表缺少唯一約束

同一事件可能因重試機制被插入兩次。建議：在 events 表添加 (conversation_id, event_type, created_at) 的唯一約束，或使用應用層等冪寫入。

---

從自建到 SaaS：何時考慮遷移到 TG-Staff？

自建 PostgreSQL schema 的好處是靈活可控，但代價也很明顯：你需要投入開發時間設計表結構、寫業務邏輯、維護資料庫效能。當你的團隊面臨以下情況時，可以考慮從自建遷移到成熟平台：

• 維護成本高：需要專人管理資料庫、最佳化慢查詢、處理資料一致性。
• 缺乏即時客服面板：自建報表通常是 T+1 的，無法即時查看客服狀態、會話列表。
• 需要內控風控：自建內容風控（如錢包地址監控）需要額外開發，且難以保證即時性。
• 多語言翻譯：自建翻譯功能需對接 API、處理配額、快取結果，工作量不小。

TG-Staff 直接提供了上述分析能力，無需自建 schema：

• 使用者畫像與會話記錄自動儲存，控制台內即可按客服、時間、狀態篩選。
• 分流連結歸因：自動擷取 IP、UA 和 URL 參數，無需自己設計事件表。
• 內容風控：內建風險詞偵測與錢包地址監控，支援稽核日誌。
• 自動翻譯：標準版含 AI 翻譯，專業版可對接 DeepL/Google 專業翻譯。

如果你仍在自建階段，上述 schema 可以作為起點。但如果團隊資源有限，或希望快速上線專業客服能力，不妨試試 TG-Staff 的免費試用。

---

常見問題

問：自建 PostgreSQL schema 需要多少儲存空間？
答：取決於日活用戶與訊息量。建議按每 10 萬條事件約 50–100 MB 估算，events 表可設定按月分區，定期歸檔歷史資料。

問：如何保證會話表與事件表的資料一致性？
答：推薦在應用層使用事務（BEGIN/COMMIT）同時寫入兩表；或採用事件驅動架構，先寫 events，再透過非同步任務聚合產生會話狀態。

問：schema 設計是否支援多 Bot 專案？
答：是，核心表需包含 bot_id 欄位，並建立外鍵關聯到 bots 表；查詢時始終以 bot_id 過濾，避免資料混淆。

問：能否直接在 PostgreSQL 中實現即時客服面板？
答：可配合 PostgreSQL 的 LISTEN/NOTIFY 或第三方工具（如 Grafana）實現近即時看板，但即時雙向聊天仍需 WebSocket 層；若需開箱即用，可參考 TG-Staff 的 Web 控制台。

問：事件表 payload 用 JSON 還是 JSONB？
答：推薦 JSONB，支援索引與更高效查詢（如 payload->>'ip'），且儲存空間略優；注意限制 payload 欄位大小（建議 < 8KB）以避免效能下降。

---

若想跳過 schema 設計、直接獲得專業客服分析能力，可註冊 TG-Staff 免費試用，或聯絡 @tgstaff_robot 了解方案詳情。同時歡迎造訪 文件 查看 API 與整合方案。