Modelling a Shared Inbox: Why Message is the Primary Key
Explore an architectural approach to shared inbox design from Onsites AI, highlighting why using messages as primary keys prevents data loss.
Stock photo for illustration only, not from the actual event
- Treating conversations as the single source of truth often creates challenges when adding new channels.
- Messages arrive once and never change, making them the natural primary key for the system.
- Tracking email delivery status per message level helps diagnose recipient bounces accurately.
- Onsites AI shares a robust schema design that handles webhook retries and deduplication.
Building a shared inbox implementation often starts with a conversations table, only to face architectural regrets later on. An article published on Dev.to by Onsites AI explores a database schema designed to survive real support traffic and eliminate complex data merging issues as products grow.
In practice, a conversation is a derived concept—it is simply the result of deciding which messages belong together. Storing conversations as the ultimate source of truth forces developers to write complex merge logic every time a new communication channel is introduced, and data merges are operations where losing information is completely unacceptable.
Messages, on the other hand, arrive once and remain immutable. They act as the natural primary key for support systems. The recommended database schema relies on core tables such as:
- messages: stores immutable facts including id, channel, external_id, direction, sent_at, body, and raw
- contacts: manages sender information shared across multiple messages
- threads: handles derived groupings that can be rebuilt at any time
- thread_messages: provides append-only relationship links
Stock photo for illustration only, not from the actual event
The system also addresses identity resolution challenges when the same user reaches out via multiple addresses, such as a corporate email followed by a personal address or a rebrand update. Relying strictly on exact string matching would mistakenly fragment contacts into silos, which is why a suggestion queue where humans resolve ambiguous cases works better than brittle regular expressions.
Outbound communication tracking introduces lifecycle states such as queued, sent, delivered, bounced, or complained. These states exist per message and per recipient. Claiming that an entire conversation was sent obscures critical failures, such as an email bouncing for one out of three recipients.
Implementing a message_delivery table tracking message_id, recipient, state, updated_at, and provider_id transforms delivery verification into a straightforward database query rather than a support escalation.
Furthermore, enforcing unique constraints on messages.external_id per channel with deduplication on insert is crucial. Since third-party providers frequently retry webhooks, lacking this constraint inevitably leads to duplicate replies—a failure customers remember. This practical schema powers Onsites AI, a shared inbox, CRM, and light invoicing tool designed for teams of three to thirty users.
Source: Dev.to
Found something wrong in this article? Report an issue with this article
Comments
Leave a Comment