共享收件箱建模：為何應以訊息而非對話作為主鍵

Every shared-inbox implementation starts with a conversations table and ends up regretting it. Here is the model that survives contact with real support traffic.

The problem with conversation-first

A conversation is a derived thing. It is what you get after you decide which messages belong together. If you store conversations as the source of truth, every new channel forces you to write merge logic that mutates the conversation rows — and merges are the one operation that must never lose data.

Messages, by contrast, arrive once and never change. They are the natural primary key.

The model

-- the immutable fact
messages(id, channel, external_id, direction, sent_at, body, raw)

-- who it came from (may be shared by many messages)
contacts(id, email, phone, name)

-- the derived grouping, rebuildable at any time
threads(id, subject_key, opened_at, closed_at)

-- the link, append-only
thread_messages(thread_id, message_id, added_at, reason)

Three properties matter:

1. thread_messages is append-only. A merge inserts rows; it never rewrites history. If the merge was wrong, you can split by deleting the link rows — no data lost.
2. Threads can be rebuilt. Delete every row in threads and you can regenerate them from messages plus your merge rules. That is the test for whether your grouping logic is a projection or a tangle.
3. reason is stored. "Same contact, 4h window" vs "manual merge by ana" answers the question every support lead eventually asks: why are these two emails in one thread?

Identity is the hard part

The same person writes from ana@company.com, then from a personal address, then from a second work address after a rebrand. Matching on the exact string gives you three contacts and three threads.

A workable compromise:

• Exact match first (email, phone, then channel-specific ID)
• Then a domain-and-name heuristic that suggests a merge rather than performing one
• Never auto-merge across organisations

The suggestion queue is boring, and it is what keeps identity resolution honest: a human confirms the ambiguous cases rather than a regex.

Delivery state belongs to the message, not the conversation

Outbound mail can be queued, sent, delivered, bounced, complained. That lifecycle is per message and per recipient. Storing "the conversation was sent" hides the one case that matters: one of three recipients bounced.

message_delivery(message_id, recipient, state, updated_at, provider_id)

With that table, "did they get it?" is a query instead of a support escalation.

What this buys you

• Channel integrations become writers of messages, not reimplementations of threading
• AI summarisation or tagging attaches to messages/threads as a separate table, never mutating the record
• Deleting a thread is a policy operation (remove link rows + messages), so retention rules become enforceable
• Migration to a new database is a row copy, not a logic rewrite

The one thing to get right early

Make messages.external_id unique per channel and dedupe on insert. Every provider retries webhooks. Without that constraint you will ship duplicate replies, and duplicate replies are the failure customers remember.

This is the shape we use in Onsites AI — a shared inbox, CRM and light invoicing for teams of three to thirty, self-hostable or cloud. The schema notes above are portable either way.