Modelling a shared inbox: why the message is the primary key, not the conversation

Every shared inbox starts with a conversations table and often ends up regretting it. The model that survives real support traffic stores messages as the primary key, not conversations. A conversation is derived from messages, and storing conversations as the source of truth requires complex merge logic that can lose data. Messages, on the other hand, arrive once and never change, making them the natural primary key.

The model consists of immutable fact messages (id, channel, external_id, direction, sent_at, body, raw), contacts who sent them, and threads that group messages. The key properties are that thread_messages is append-only, allowing merges to insert rows without rewriting history, and that threads can be rebuilt from messages and merge rules.

Identity resolution is challenging, as a single person may send emails from different addresses during a work rebrand. A compromise involves exact matches on email, phone, or channel-specific IDs, followed by domain-and-name heuristics to suggest merges rather than performing them automatically. Conversation state is stored as a separate message_delivery table, tracking outbound mail lifecycle per message and recipient.

This approach ensures accurate answers to support leads about why emails are grouped together, and that delivery state belongs to the message, not the conversation. With this schema, channel integrations become writers of messages, and AI summarisation or tagging attaches to messages or threads without mutating the record. Deleting a thread is simply a policy operation, enabling enforceable retention rules.

When migrating to a new database, it's a straightforward row copy, not a logic rewrite. The most important early step is ensuring messages.external_id is unique per channel and deduped on insert, preventing duplicate replies from webhooks that customers remember as failures.