Create channels, messages, conversations

[FelixMalfait]

Context

This is a backend ticket to prepare for the email integration.
Later on, we'll want to integrate other communication channels (e.g. WhatsApp Business, LinkedIn, Twilio SMS, etc.)

Implementation

Create data model

Create migrations and GraphQL API for model described below.

Scope: Backend
Time: 1 day

Prepare endpoints to display threads in UI

Cf discussion on nested relations, we'll need to create a custom endpoint for the show UI (getThreads(companyid / personId))
Scope: backend
Time: 1 day

Display threads in activity feed for companies

Scope: frontend
Time: 1 day

Display list of threads on dedicated tab of contact & company show page

Scope: frontend
Time: 1 day

Display detailed view of a thread

Scope: frontend
Time: 2 days

Discussion

Data model

Message

• id, createdAt, updatedAt
• external - json
• messageThreadId - string fk
• direction - outgoing or incoming
• subject - string
• body - string

Note: external used to store channel-specific variables (e.g. historyId, id...)

MessageThread

• id, createdAt, updatedAt
• external - json
• subject string
• messagingChannelId
• visibility string enum: default, subject, share_everything

MessageRecipient

• id, createdAt, updatedAt
• messageId - string fk
• role - string enum: from, to, cc, bcc
• address - string
• alias - string, nullable
• personId - nullable, fk to people
• userId - nullable, fk to users

MessageChannel

• id, createdAt, updatedAt
• visibility - string enum: metadata, subject, share_everything ; default to metadata, not nullable
• address - string, who receives the message (phone number or email)
• connectedAccountId - string, foreign key, not null
• type - email, sms, chat

Access-control

One strong choice being made is that all message metadata is accessible by anyone in the workspace.
That way, we don't have to implement object-level permissions, which would be a pain.
Instead we can focus on a simpler masking system done in the application layer, which we'll call field-level permission or masking. This combines a traditional field level permission (as set per the messaging channel), along with row-level field permission overwrite (I can grant access to a given message/thread).

There are two types of mask:

• full masking: we mask the subject lines, message content, attachments
• light masking: we mask the message content, attachments
  (note attachments will not be implemented in v1)

Another strong choice that should help simplify things is that permissions are managed at the thread level, regardless of the individual messages recipients. Let's say there's a thread with a client involving 2 team members and then the client replies to the same thread but without CC'ing the second teammate, then that teammate will still have visibility over the full thread.
Some software take a more conservative approach that enables stronger privacy, but that involves complex engineering to know when to split or merge virtual threads that share common messages but don't have the exact same message in it.
We feel that the primary goal of a CRM is to share information across the company and that this is an acceptable trade-off.

Filtering through a nested relation

Sometimes we need to filter through a nested relation.
For example, given a company, we would need to fetch threads' messages' recipients' companies to retrieve the relevant thread they participated in.
If it was supported, the request could look like something like this in an ORM: MessageThread::whereHas('message.recipient.company', function(query) { query.where('id', $company_id) })
But pg_graphql doesn't support this.

So we have different options:

1. Consolidating data during email import
   — Pros: best performance, simple frontend query
   — Cons: duplicated data in db, risk on inconsistency
2. Custom endpoint with custom logic just dedicated to messaging
   — Pros: simple frontend query
   — Cons: do we want to introduce this pattern?
3. Creating a dedicated Postgres Views
   — Pros: simple frontend query
   — Cons: do we want to introduce this pattern?

Not mentioning about other options we can quickly discard like doing multiple requests on the frontend!

Tasks

Preview Give feedback

1. Create message data model #2810
   type: feature +1
   
2. Create message-thread data model #2811
   type: feature +1
   
3. Create message-recipient data model #2812
   type: feature +1
   
4. Create message-channel data model #2813
   type: feature +1