WellChina Chat System Design

Real-time customer support chat, built on Supabase free tier.

1. Background

Current State

The existing contact system (ContactInquiry) is a one-way form: visitors submit name/email/message, admins see it in a table, manually update status (new/replied/closed). No reply capability, no conversation threading, no real-time interaction.

Problem

Medical tourism decisions are high-stakes. Users need to ask follow-up questions, share medical documents, and get timely responses. A contact form with 24-48h email turnaround creates friction and erodes trust.

Goal

Build a real-time chat system where:

Visitors can start a conversation without registration
Agents (customer service) can handle multiple conversations simultaneously
Messages persist and history is loadable
Online status and typing indicators provide presence feedback

2. Supabase Free Tier Assessment

Resource	Limit	Sufficient?
Realtime concurrent connections	200	Yes -- unlikely to have 200 simultaneous chats
Realtime messages/sec	100	Yes -- support chat is low-frequency
Realtime messages/month	2,000,000	Yes -- ~65K sessions of 30 messages each
Database storage	500 MB	Yes -- ~1-2M text messages
File storage	1 GB	Moderate -- compress images client-side
Auth MAUs	50,000	Yes
Edge Function invocations	500,000/month	Yes -- for notifications

Risk: Free tier projects pause after 1 week of inactivity. For production, upgrading to Pro ($25/mo) is recommended. During development and early launch, the free tier is sufficient as long as the site has regular traffic.

3. Architecture

3.1 Authentication Strategy

聊天系统依赖 auth-system-design.md 中定义的用户认证体系。

Registered user              Anonymous visitor            Agent
       |                            |                       |
  已有 Supabase session       signInAnonymously()    signInWithPassword()
       |                            |                       |
  JWT { is_anonymous: false }  JWT { is_anonymous: true }  JWT { role: 'agent' }
       |                            |                       |
       +────────────── Supabase Auth ───────────────────────+

三种聊天身份：

已注册用户: 使用已有的 Supabase Auth session 发起聊天。聊天记录绑定 user ID，跨设备、跨浏览器均可查看历史。登录即可恢复对话。
匿名访客: 未注册用户打开聊天时自动调用 signInAnonymously()。可以正常聊天，但 session 依赖浏览器 cookie -- 清理浏览器数据将丢失身份和聊天历史。UI 中需明确提示并鼓励注册。
客服 (Agent): Email/password 认证，通过 /agent/login 登录。Agent 记录存储在 chat_agents 表，关联 auth.users。

身份升级路径：

匿名用户聊天中 → 点击 "注册以保留聊天记录"
    │
    ├── linkIdentity({ provider: 'google' })  → Google 绑定
    │   或 updateUser({ email, password })    → Email 绑定
    │
    └── 同一个 user ID → 所有聊天历史自动保留

聊天 Widget 中的提示策略：

场景	提示
匿名用户首次打开聊天	"You're chatting as a guest. Sign up to save your chat history."
匿名用户发送第 3 条消息	顶部 banner: "Create an account to keep this conversation accessible anytime."
匿名用户关闭聊天	"Remember: guest chats may be lost if you clear your browser data."
已注册用户	无提示，直接显示历史对话列表

3.2 Data Model

sql

-- ─── Agent profiles ─────────────────────────────────────────
create table chat_agents (
  id          uuid primary key references auth.users(id),
  name        text not null,
  avatar_url  text,
  is_online   boolean default false,
  max_chats   int default 5,          -- concurrent chat limit
  created_at  timestamptz default now()
);

-- ─── Conversations ──────────────────────────────────────────
create table conversations (
  id          uuid primary key default gen_random_uuid(),
  visitor_id  uuid not null references auth.users(id),
  agent_id    uuid references chat_agents(id),  -- null = unassigned
  status      text not null default 'waiting',   -- waiting | active | closed
  subject     text,                               -- auto or visitor-provided
  metadata    jsonb default '{}',                 -- { hospital, procedure, city }
  created_at  timestamptz default now(),
  updated_at  timestamptz default now(),
  closed_at   timestamptz
);

create index idx_conversations_status on conversations(status);
create index idx_conversations_agent on conversations(agent_id) where status = 'active';
create index idx_conversations_visitor on conversations(visitor_id);

-- ─── Messages ───────────────────────────────────────────────
create table messages (
  id              uuid primary key default gen_random_uuid(),
  conversation_id uuid not null references conversations(id) on delete cascade,
  sender_id       uuid not null references auth.users(id),
  content         text,
  message_type    text not null default 'text',  -- text | image | file | system
  attachment_url  text,
  attachment_name text,
  is_read         boolean default false,
  created_at      timestamptz default now()
);

create index idx_messages_conversation on messages(conversation_id, created_at);

-- ─── Typing indicators (optional, can use Presence instead) ─
-- Not persisted -- handled via Supabase Realtime Presence.

-- ─── Canned responses ───────────────────────────────────────
create table canned_responses (
  id         uuid primary key default gen_random_uuid(),
  shortcut   text not null unique,   -- e.g., "/greeting"
  content    text not null,
  category   text,
  created_at timestamptz default now()
);

3.3 Row Level Security (RLS)

sql

alter table conversations enable row level security;
alter table messages enable row level security;
alter table chat_agents enable row level security;
alter table canned_responses enable row level security;

-- Visitors: can only see their own conversations
create policy "visitors_own_conversations" on conversations
  for select using (visitor_id = auth.uid());

-- Visitors: can create a conversation
create policy "visitors_create_conversation" on conversations
  for insert with check (visitor_id = auth.uid());

-- Agents: can see all conversations
create policy "agents_all_conversations" on conversations
  for all using (
    auth.uid() in (select id from chat_agents)
  );

-- Messages: participants can read
create policy "participants_read_messages" on messages
  for select using (
    conversation_id in (
      select id from conversations
      where visitor_id = auth.uid()
         or agent_id = auth.uid()
    )
  );

-- Messages: participants can insert
create policy "participants_send_messages" on messages
  for insert with check (
    sender_id = auth.uid()
    and conversation_id in (
      select id from conversations
      where visitor_id = auth.uid()
         or agent_id = auth.uid()
    )
  );

-- Agents: can update messages (mark read)
create policy "agents_update_messages" on messages
  for update using (
    auth.uid() in (select id from chat_agents)
  );

-- Agents: can read canned responses
create policy "agents_read_canned" on canned_responses
  for select using (
    auth.uid() in (select id from chat_agents)
  );

-- Agent profiles: public read (for displaying name/avatar in chat)
create policy "public_read_agents" on chat_agents
  for select using (true);

3.4 Real-time Architecture

The system uses a dual-channel approach for optimal performance:

┌─────────────────────────────────────────────────────────┐
│                   Supabase Realtime                      │
│                                                         │
│  Channel: conversation:{id}                             │
│  ├── Broadcast  → instant message delivery (low latency)│
│  ├── Presence   → typing indicators, online status      │
│  └── Postgres Changes → message persistence trigger     │
│                                                         │
│  Channel: agent:queue                                   │
│  └── Postgres Changes → new conversation notifications  │
└─────────────────────────────────────────────────────────┘

Message flow:

Sender inserts message into messages table via Supabase client
Postgres Changes broadcasts the INSERT to all subscribers of conversation:{id}
Recipient's UI updates in real-time
On page load, historical messages are fetched from the database

Why not Broadcast-only? Broadcast doesn't persist. By writing to the database first, we get persistence + real-time delivery via Postgres Changes in one step. The latency difference (~50ms) is negligible for a support chat.

Presence (typing indicators):

typescript

// Subscribe to presence on conversation channel
const channel = supabase.channel(`conversation:${id}`)
channel.on('presence', { event: 'sync' }, () => {
  const state = channel.presenceState()
  // Update typing indicator UI
})

// Track typing state
channel.track({ user_id: uid, typing: true })

3.5 Agent Queue & Assignment

New conversation (status: 'waiting')
        │
        ▼
  Agent Dashboard sees it in queue
        │
        ▼
  Agent clicks "Accept" or auto-assign
        │
        ▼
  status → 'active', agent_id → agent.id
        │
        ▼
  System message: "Agent {name} joined the chat"
        │
        ▼
  Conversation proceeds
        │
        ▼
  Agent clicks "Close" → status → 'closed'

Auto-assignment (Phase 2): Round-robin among online agents with active_chats < max_chats.

4. Frontend Components

4.1 Visitor Side

src/
  components/
    chat/
      ChatWidget.tsx          -- Floating bubble + expandable panel (client component)
      ChatWindow.tsx          -- Message list + input area
      ChatMessage.tsx         -- Single message bubble (text/image/system)
      ChatInput.tsx           -- Text input + file upload + send button
      TypingIndicator.tsx     -- "Agent is typing..." animation
      ChatHeader.tsx          -- Agent info, minimize/close buttons
  hooks/
    useChat.ts               -- Core hook: messages, send, subscribe, presence
    useChatAuth.ts           -- Anonymous auth + session persistence
  lib/
    supabase-browser.ts      -- Supabase client (browser singleton)

ChatWidget behavior:

Floating button in bottom-right corner (all pages)
Click to expand into a chat panel (400x500px, or fullscreen on mobile)
已登录用户: 直接显示历史对话列表或新建对话
未登录用户: 自动调用 signInAnonymously() → 创建对话 → 显示注册提示 banner
Returning visitor → reconnect to existing open conversation
Badge shows unread message count
Minimized state persists via localStorage

4.2 Agent Side

src/app/agent/
  login/page.tsx              -- Agent login (email/password)
  (dashboard)/
    layout.tsx                -- Agent dashboard shell
    page.tsx                  -- Queue overview: waiting + active conversations
    conversations/
      [id]/page.tsx           -- Single conversation view
    canned/page.tsx           -- Manage canned responses
    settings/page.tsx         -- Agent profile settings

Agent Dashboard features:

Left sidebar: conversation list, sorted by status (waiting first, then active)
Unread badge per conversation
Main area: selected conversation's messages
Right panel (desktop): visitor info, conversation metadata
Canned responses: type / to trigger autocomplete
Multi-conversation: agent can switch between active chats
Sound notification on new conversation or message

4.3 Component Details

ChatWidget (visitor):

tsx

'use client'

// Floating chat bubble, present on all pages via layout.tsx
// States: collapsed (bubble) | expanded (chat panel)
// Mobile: full-screen overlay when expanded

export function ChatWidget() {
  const [isOpen, setIsOpen] = useState(false)
  const { conversation, messages, send, isConnected } = useChat()
  const { isTyping } = usePresence(conversation?.id)

  return (
    <>
      {/* Floating bubble */}
      <button onClick={() => setIsOpen(true)} className="fixed bottom-6 right-6 ...">
        <MessageCircle />
        {unreadCount > 0 && <span className="badge">{unreadCount}</span>}
      </button>

      {/* Chat panel */}
      {isOpen && (
        <div className="fixed bottom-6 right-6 w-[400px] h-[500px] ...">
          <ChatHeader agent={conversation?.agent} onClose={() => setIsOpen(false)} />
          <ChatWindow messages={messages} />
          {isTyping && <TypingIndicator />}
          <ChatInput onSend={send} />
        </div>
      )}
    </>
  )
}

useChat hook (core logic):

typescript

export function useChat() {
  // 1. Ensure anonymous auth session
  // 2. Find or create conversation
  // 3. Fetch historical messages
  // 4. Subscribe to new messages via Postgres Changes
  // 5. Provide send() function that inserts into messages table
  // 6. Track presence for typing indicators

  return { conversation, messages, send, isConnected, isLoading }
}

5. Page-by-Page Integration

Existing pages (context passing)

The chat widget should be context-aware. When a visitor opens chat from a specific page, the conversation's metadata field captures context:

Page	Metadata
`/procedures/[slug]`	`{ procedure: "Dental Implants" }`
`/hospitals/[slug]`	`{ hospital: "Beijing United" }`
`/pricing`	`{ page: "pricing" }`
`/compare`	`{ hospitals: ["A", "B"] }`
`/contact?hospital=X&procedure=Y`	`{ hospital: "X", procedure: "Y" }`

This context appears in the agent dashboard so agents have immediate context.

Contact form coexistence

The existing ContactInquiry form stays as-is for users who prefer email. Add a prompt below the form:

"Want a faster response? Chat with us now." → opens ChatWidget.

6. Notification System

In-app (Phase 1)

Browser Notification API for agents when a new conversation enters the queue
Sound effect on new message (configurable)
Unread badge on chat widget (visitor) and sidebar items (agent)

Email fallback (Phase 2)

If no agent responds within 5 minutes, send email notification to admin via Edge Function
If visitor leaves (disconnects) with an active conversation, send chat transcript to their email

7. Implementation Phases

Phase 1: Core Chat (MVP)

前置依赖: auth-system-design.md Phase 1（Supabase Auth + AuthProvider）必须先完成。

Scope: 已注册用户和匿名访客均可与客服实时聊天。

[ ] Supabase migration: create tables (chat_agents, conversations, messages) + RLS policies
[ ] useChat hook: conversation lifecycle, message CRUD, realtime subscription (基于 AuthContext 获取 user)
[ ] ChatWidget: floating bubble + expandable panel（匿名用户自动 signInAnonymously，已登录用户直接使用 session）
[ ] ChatWindow + ChatMessage + ChatInput: core chat UI
[ ] 匿名用户注册提示 banner（鼓励绑定账号保留聊天历史）
[ ] Agent login page (/agent/login)
[ ] Agent dashboard: conversation queue + chat view
[ ] System messages: "waiting for agent", "agent joined", "conversation closed"
[ ] Seed one agent account for testing

Estimated DB tables: 3 new tables, ~5 RLS policies.

Phase 2: Polish

[ ] Typing indicators (Presence)
[ ] Online/offline status for agents
[ ] Unread message count + badge
[ ] Sound notifications
[ ] Canned responses for agents
[ ] File/image upload (Supabase Storage)
[ ] Mobile-responsive chat panel (fullscreen on small screens)
[ ] Page context capture in metadata

Phase 3: Advanced

[ ] Auto-assignment (round-robin)
[ ] Email fallback when no agent online
[ ] Chat transcript export
[ ] Agent performance metrics (response time, resolution rate)
[ ] Conversation tags/categories
[ ] Integration with existing ContactInquiry (convert inquiry to conversation)

8. File Structure (New Files)

src/
  app/
    agent/
      login/page.tsx
      (dashboard)/
        layout.tsx
        page.tsx
        conversations/[id]/page.tsx
  components/
    chat/
      ChatWidget.tsx
      ChatWindow.tsx
      ChatMessage.tsx
      ChatInput.tsx
      ChatHeader.tsx
      TypingIndicator.tsx
  hooks/
    useChat.ts
    useChatAuth.ts
    usePresence.ts
  lib/
    supabase-browser.ts
prisma/
  migrations/
    xxxx_add_chat_tables/migration.sql
supabase/
  functions/
    chat-notification/index.ts    -- Phase 2: email fallback

9. Key Technical Decisions

Decision	Choice	Rationale
Auth for visitors	Supabase Auth (registered) + anonymous fallback	已登录用户历史永久保留；未登录用户零摩擦进入聊天
Anonymous history	Cookie-dependent, 明确提示风险	平衡易用性和数据持久性，鼓励注册
Identity upgrade	`linkIdentity()` / `updateUser()`	匿名 → 正式账户，同一 user ID，数据无损迁移
Realtime method	Postgres Changes	Persistence + realtime in one write
Data access for chat	Supabase JS Client	Realtime requires Supabase client; Prisma stays for existing pages
Chat state	React hooks + AuthContext	复用全局 auth 状态，chat 逻辑局部化
Agent routing	`/agent/` (separate from `/admin/`)	Different auth, different concerns
Mobile chat	Fullscreen overlay	Better UX than small floating panel
Existing contact form	Keep as-is	Different use case (async inquiry vs real-time chat)

10. Supabase Client Coexistence with Prisma

The existing app uses Prisma for all server-side data access. The chat system introduces Supabase JS Client for browser-side realtime features. These coexist:

Prisma (server): All existing pages (hospitals, procedures, cities, admin CRUD). Runs in Server Components and API routes.
Supabase JS Client (browser): Chat widget, agent dashboard. Runs in Client Components. Uses Supabase Auth, Realtime, and direct database access via PostgREST + RLS.

No migration of existing code is needed. The chat tables are accessed exclusively through Supabase JS Client, not Prisma. The Prisma schema does NOT need to include chat tables -- they are managed via Supabase migrations.

Server Components (existing)     Client Components (new: chat)
        │                                  │
     Prisma                        Supabase JS Client
        │                                  │
        └────── PostgreSQL (Supabase) ─────┘

11. Production Hardening (post-MVP)

The sections above describe the MVP design. The following adjustments were made after the MVP shipped, when we observed bugs and gaps in production.

11.1 One active conversation per visitor

Problem observed: A visitor who double-clicked "send" before the first conversation INSERT returned could create 2+ conversations within ~200ms. On reload, loadConversation() picks the latest created_at, which was the empty duplicate, so the visitor perceived "chat history lost."

Fix (two layers):

Application lock in useChat.sendMessage: creatingConvRef: useRef<Promise<Conversation>> holds the in-flight creation. Concurrent calls share the same promise and resolve to the same conversation. Cleared only on error so retries work.

DB unique partial index:

sql

CREATE UNIQUE INDEX conversations_one_active_per_visitor
  ON conversations (visitor_id)
  WHERE status IN ('waiting', 'active');

Covers cross-tab races. The app catches 23505 (unique_violation) and re-fetches the existing conversation.

11.2 RLS fix for system messages

The original visitors_send_messages policy required sender_role = 'visitor', which silently dropped the "You're now in the queue" system message inserted by useChat. A separate policy was added:

sql

CREATE POLICY visitors_send_system_messages ON messages
  FOR INSERT TO authenticated
  WITH CHECK (
    sender_role = 'system'
    AND message_type = 'system'
    AND sender_id = auth.uid()
    AND conversation_id IN (SELECT id FROM conversations WHERE visitor_id = auth.uid())
  );

11.3 Conversation metadata schema

The metadata JSONB column is populated at conversation creation (not updated later) with visitor context for admins:

json

{
  "region": "JP",
  "saved": {
    "plans": ["Premium Plan"],
    "procedures": ["Dental Implant", "LASIK"],
    "hospitals": ["Shanghai Jiahui Hospital"],
    "cities": ["Shanghai"]
  }
}

region comes from RegionContext; saved is a snapshot of SavedItemsContext names. Admin chat UI reads these and also fetches live profiles.{age,nationality,phone,full_name,email} for non-anonymous users (so admins see latest values, not snapshots).

11.4 Admin visitor info surface

GET /api/admin/chat/conversations returns enriched visitor data:

visitor_id_short — uuid.slice(0,4) + '…' + uuid.slice(-4), displayed as Guest #b834…9ef6 so admins can distinguish anonymous visitors at a glance
visitor_region, visitor_saved — from conversation metadata
visitor_name / age / nationality / phone / email — live join on profiles table for logged-in users

The admin chat page renders saved items as named chips (matching what the visitor sees in their own chat sidebar), not just counts.

11.5 Data retention & cleanup

Supabase does not automatically delete anonymous users. A SECURITY DEFINER function public.cleanup_chat_data() runs daily at 03:00 UTC via pg_cron with this tiered policy:

Step	Action	Retention
1	Close waiting/active conversations with no message activity	30 days
2	Delete empty anonymous conversations (no visitor text messages)	7 days
3	Delete closed anonymous conversations	30 days after `closed_at`
4	Delete anonymous users with no recent activity (CASCADE clears conversations/messages/translations)	90 days

Real users' data is never auto-deleted. conversations.visitor_id → auth.users(id) was changed to ON DELETE CASCADE to make step 4 work cleanly; the existing cascade chain (messages → conversations, message_translations → messages) handles the rest.

11.6 i18n for send-preferences message

The "Send Profile" button in the expanded chat sidebar composes a message summarizing the visitor's saved items + profile info and sends it as a chat message. All labels (My preferences:, Procedures:, Name:, Age: etc.) now use useTranslations('chat') keys across all 8 locales, so the message the admin receives is in the visitor's own language.

WellChina Chat System Design ​

1. Background ​

Current State ​

Problem ​

Goal ​

2. Supabase Free Tier Assessment ​

3. Architecture ​

3.1 Authentication Strategy ​

3.2 Data Model ​

3.3 Row Level Security (RLS) ​

3.4 Real-time Architecture ​

3.5 Agent Queue & Assignment ​

4. Frontend Components ​

4.1 Visitor Side ​

4.2 Agent Side ​

4.3 Component Details ​

5. Page-by-Page Integration ​

Existing pages (context passing) ​

Contact form coexistence ​

6. Notification System ​

In-app (Phase 1) ​

Email fallback (Phase 2) ​

7. Implementation Phases ​

Phase 1: Core Chat (MVP) ​

Phase 2: Polish ​

Phase 3: Advanced ​

8. File Structure (New Files) ​

9. Key Technical Decisions ​

10. Supabase Client Coexistence with Prisma ​

11. Production Hardening (post-MVP) ​

11.1 One active conversation per visitor ​

11.2 RLS fix for system messages ​

11.3 Conversation metadata schema ​

11.4 Admin visitor info surface ​

11.5 Data retention & cleanup ​

11.6 i18n for send-preferences message ​