communication snippets

Mattermost database schema and design

Tagged communication, database, mattermost, schema, slack  Languages bash, sql

This review was done on Mattermost version 3.4.0.

Install mattermost

docker run --name mattermost-preview -d --publish 8065:8065 mattermost/mattermost-preview
docker exec -ti mattermost-preview /bin/bash
...
mysql -u root -p
use mattermost_test

Database password is mostest.

Schema

mysql> describe Posts;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| Id         | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt   | bigint(20)   | YES  | MUL | NULL    |       |
| UpdateAt   | bigint(20)   | YES  | MUL | NULL    |       |
| DeleteAt   | bigint(20)   | YES  |     | NULL    |       |
| UserId     | varchar(26)  | YES  |     | NULL    |       |
| ChannelId  | varchar(26)  | YES  | MUL | NULL    |       |
| RootId     | varchar(26)  | YES  | MUL | NULL    |       |
| ParentId   | varchar(26)  | YES  |     | NULL    |       |
| OriginalId | varchar(255) | YES  |     | NULL    |       |
| Message    | text         | YES  | MUL | NULL    |       |
| Type       | varchar(26)  | YES  |     | NULL    |       |
| Props      | text         | YES  |     | NULL    |       |
| Hashtags   | text         | YES  | MUL | NULL    |       |
| Filenames  | text         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

mysql> describe Channels;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Id            | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt      | bigint(20)   | YES  |     | NULL    |       |
| UpdateAt      | bigint(20)   | YES  |     | NULL    |       |
| DeleteAt      | bigint(20)   | YES  |     | NULL    |       |
| TeamId        | varchar(26)  | YES  | MUL | NULL    |       |
| Type          | varchar(1)   | YES  |     | NULL    |       |
| DisplayName   | varchar(64)  | YES  |     | NULL    |       |
| Name          | varchar(64)  | YES  | MUL | NULL    |       |
| Header        | text         | YES  |     | NULL    |       |
| Purpose       | varchar(128) | YES  |     | NULL    |       |
| LastPostAt    | bigint(20)   | YES  |     | NULL    |       |
| TotalMsgCount | bigint(20)   | YES  |     | NULL    |       |
| ExtraUpdateAt | bigint(20)   | YES  |     | NULL    |       |
| CreatorId     | varchar(26)  | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+


mysql> describe ChannelMembers;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ChannelId    | varchar(26) | NO   | PRI | NULL    |       |
| UserId       | varchar(26) | NO   | PRI | NULL    |       |
| Roles        | varchar(64) | YES  |     | NULL    |       |
| LastViewedAt | bigint(20)  | YES  |     | NULL    |       |
| MsgCount     | bigint(20)  | YES  |     | NULL    |       |
| MentionCount | bigint(20)  | YES  |     | NULL    |       |
| NotifyProps  | text        | YES  |     | NULL    |       |
| LastUpdateAt | bigint(20)  | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)


mysql> describe Teams;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| Id              | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt        | bigint(20)   | YES  |     | NULL    |       |
| UpdateAt        | bigint(20)   | YES  |     | NULL    |       |
| DeleteAt        | bigint(20)   | YES  |     | NULL    |       |
| DisplayName     | varchar(64)  | YES  |     | NULL    |       |
| Name            | varchar(64)  | YES  | UNI | NULL    |       |
| Email           | varchar(128) | YES  |     | NULL    |       |
| Type            | varchar(255) | YES  |     | NULL    |       |
| CompanyName     | varchar(64)  | YES  |     | NULL    |       |
| AllowedDomains  | text         | YES  |     | NULL    |       |
| InviteId        | varchar(32)  | YES  | MUL | NULL    |       |
| AllowOpenInvite | tinyint(1)   | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

mysql> describe TeamMembers;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| TeamId   | varchar(26) | NO   | PRI | NULL    |       |
| UserId   | varchar(26) | NO   | PRI | NULL    |       |
| Roles    | varchar(64) | YES  |     | NULL    |       |
| DeleteAt | bigint(20)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> describe Users;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| Id                 | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt           | bigint(20)   | YES  |     | NULL    |       |
| UpdateAt           | bigint(20)   | YES  |     | NULL    |       |
| DeleteAt           | bigint(20)   | YES  |     | NULL    |       |
| Username           | varchar(64)  | YES  | UNI | NULL    |       |
| Password           | varchar(128) | YES  |     | NULL    |       |
| AuthData           | varchar(128) | YES  | UNI | NULL    |       |
| AuthService        | varchar(32)  | YES  |     | NULL    |       |
| Email              | varchar(128) | YES  | UNI | NULL    |       |
| EmailVerified      | tinyint(1)   | YES  |     | NULL    |       |
| Nickname           | varchar(64)  | YES  |     | NULL    |       |
| FirstName          | varchar(64)  | YES  |     | NULL    |       |
| LastName           | varchar(64)  | YES  |     | NULL    |       |
| Roles              | varchar(64)  | YES  |     | NULL    |       |
| AllowMarketing     | tinyint(1)   | YES  |     | NULL    |       |
| Props              | text         | YES  |     | NULL    |       |
| NotifyProps        | text         | YES  |     | NULL    |       |
| LastPasswordUpdate | bigint(20)   | YES  |     | NULL    |       |
| LastPictureUpdate  | bigint(20)   | YES  |     | NULL    |       |
| FailedAttempts     | int(11)      | YES  |     | NULL    |       |
| Locale             | varchar(5)   | YES  |     | NULL    |       |
| MfaActive          | tinyint(1)   | YES  |     | NULL    |       |
| MfaSecret          | varchar(128) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
23 rows in set (0.00 sec)

Data

mysql> select * from Channels;
+----------------------------+---------------+---------------+----------+----------------------------+------+-------------+--------------------------------------------------------+--------+---------+---------------+---------------+---------------+----------------------------+
| Id                         | CreateAt      | UpdateAt      | DeleteAt | TeamId                     | Type | DisplayName | Name                                                   | Header | Purpose | LastPostAt    | TotalMsgCount | ExtraUpdateAt | CreatorId                  |
+----------------------------+---------------+---------------+----------+----------------------------+------+-------------+--------------------------------------------------------+--------+---------+---------------+---------------+---------------+----------------------------+
| 1h3rs83b3bnwzn3xb1ow49rssy | 1474786289480 | 1474786289480 |        0 | chjgwwy9qbbsdcz4ry4a6fbsoy | O    | Town Square | town-square                                            |        |         | 1474786555810 |             2 | 1474786555808 |                            |
| 1muwbod1hjrmzcr6iukhzqk4dc | 1474786289482 | 1474786289482 |        0 | chjgwwy9qbbsdcz4ry4a6fbsoy | O    | Off-Topic   | off-topic                                              |        |         | 1474786565693 |             2 | 1474786555815 |                            |
| 3pocxn4za7rd58q6rzcycgerna | 1474786510558 | 1474786510558 |        0 | ehwmnuwmztrujxhtb65kpgq4sy | O    | Off-Topic   | off-topic                                              |        |         | 1474786524398 |             1 | 1474786510570 |                            |
| f3j13bk4fi8qb8hh4mzgfbmmjc | 1474786769364 | 1474786769364 |        0 |                            | D    |             | abkob7omkjynfqfsc8shkbwkfo__u7s1oanujifg8ywuirnmhk8r3a |        |         | 1474786777955 |             1 | 1474786769364 |                            |
| g46cuaw3u781bjfyowudtfso7y | 1474786510553 | 1474786510553 |        0 | ehwmnuwmztrujxhtb65kpgq4sy | O    | Town Square | town-square                                            |        |         | 1474786510566 |             0 | 1474786510565 |                            |
| wi87emmg6786inbhgxzbnfgj3r | 1474786711899 | 1474786711899 |        0 | chjgwwy9qbbsdcz4ry4a6fbsoy | P    | XXX         | XXX                                                    |        |         | 1474786723310 |             1 | 1474786719125 | abkob7omkjynfqfsc8shkbwkfo |
+----------------------------+---------------+---------------+----------+----------------------------+------+-------------+--------------------------------------------------------+--------+---------+---------------+---------------+---------------+----------------------------+

mysql> select * from Posts;
+----------------------------+---------------+---------------+----------+----------------------------+----------------------------+----------------------------+----------------------------+------------+---------------------------------------+-------------------+-------+----------+-----------+
| Id                         | CreateAt      | UpdateAt      | DeleteAt | UserId                     | ChannelId                  | RootId                     | ParentId                   | OriginalId | Message                               | Type              | Props | Hashtags | Filenames |
+----------------------------+---------------+---------------+----------+----------------------------+----------------------------+----------------------------+----------------------------+------------+---------------------------------------+-------------------+-------+----------+-----------+
| 1mmp5u8t8fgffkkysrgcddj9oh | 1474786565691 | 1474786565691 |        0 | u7s1oanujifg8ywuirnmhk8r3a | 1muwbod1hjrmzcr6iukhzqk4dc |                            |                            |            | -                                     |                   | {}    |          | []        |
| 37wm6h78j7fh8dtq6p66uohjyy | 1474786329527 | 1474786329527 |        0 | abkob7omkjynfqfsc8shkbwkfo | 1h3rs83b3bnwzn3xb1ow49rssy | 811nz3334pbg78338ebbjfd8yy | 811nz3334pbg78338ebbjfd8yy |            | -                                     |                   | {}    |          | []        |
| 5ed5ghxp33gbik9denmqg17w6y | 1474786510566 | 1474786510566 |        0 | u7s1oanujifg8ywuirnmhk8r3a | g46cuaw3u781bjfyowudtfso7y |                            |                            |            | hithere has joined the channel.       | system_join_leave | {}    |          | []        |
| 811nz3334pbg78338ebbjfd8yy | 1474786320981 | 1474786329530 |        0 | abkob7omkjynfqfsc8shkbwkfo | 1h3rs83b3bnwzn3xb1ow49rssy |                            |                            |            | Hello                                 |                   | {}    |          | []        |
| anr5wpaoa3yuibqgmruiw98a5o | 1474786555809 | 1474786555809 |        0 | u7s1oanujifg8ywuirnmhk8r3a | 1h3rs83b3bnwzn3xb1ow49rssy |                            |                            |            | hithere has joined the channel.       | system_join_leave | {}    |          | []        |
| ct7wcfon87rnxkna313esudzhw | 1474786719128 | 1474786719128 |        0 | abkob7omkjynfqfsc8shkbwkfo | wi87emmg6786inbhgxzbnfgj3r |                            |                            |            | hithere added to the channel by hello | system_add_remove | {}    |          | []        |
| dmt68m5b3fd9zr16g9ci4mpdqc | 1474786777954 | 1474786777954 |        0 | abkob7omkjynfqfsc8shkbwkfo | f3j13bk4fi8qb8hh4mzgfbmmjc |                            |                            |            | -                                     |                   | {}    |          | []        |
| kss8nustk3nqjnnf9dsrw914my | 1474786311271 | 1474786311271 |        0 | abkob7omkjynfqfsc8shkbwkfo | 1muwbod1hjrmzcr6iukhzqk4dc |                            |                            |            | Hello!                                |                   | {}    |          | []        |
| maa1ttmi9pri8xwb9jekyci3hw | 1474786723309 | 1474786723309 |        0 | abkob7omkjynfqfsc8shkbwkfo | wi87emmg6786inbhgxzbnfgj3r |                            |                            |            | WTF                                   |                   | {}    |          | []        |
| soizm5ey3ig3bqkkygxjngodya | 1474786289489 | 1474786289489 |        0 | abkob7omkjynfqfsc8shkbwkfo | 1h3rs83b3bnwzn3xb1ow49rssy |                            |                            |            | hello has joined the channel.         | system_join_leave | {}    |          | []        |
| yj8aayxz4ir38m9r6b97cxbgco | 1474786524397 | 1474786524397 |        0 | u7s1oanujifg8ywuirnmhk8r3a | 3pocxn4za7rd58q6rzcycgerna |                            |                            |            | Jebus                                 |                   | {}    |          | []        |
+----------------------------+---------------+---------------+----------+----------------------------+----------------------------+----------------------------+----------------------------+------------+---------------------------------------+-------------------+-------+----------+-----------+
11 rows in set (0.00 sec)

Notes

  • There are 3 types of channels: open, private groups, direct messages
  • Direct messages have no team members. The channel name contains the members, e.g., “user_1__user_2”.
  • Search uses database’s full-text search by default.
  • Reads are performed on a read-only slave for high-availability: s.GetReplica().Select(&posts, “SELECT * FROM Posts”)
  • Writes are performed on the master: s.GetMaster().Exec(“Update Channels SET DeleteAt
  • Unread count is calculated using SQL and the cached values (channel.TotalMsgCount - channel_member.MsgCount):
-- unread count for user
SELECT
  SUM(CASE
    WHEN c.Type = 'D' THEN (c.TotalMsgCount - cm.MsgCount)
    ELSE cm.MentionCount
  END)
FROM Channels c
INNER JOIN ChannelMembers cm
  ON cm.ChannelId = c.Id
  AND cm.UserId = :UserId
  
-- unread count for user and channel
SELECT
  SUM(CASE
    WHEN c.Type = 'D' THEN (c.TotalMsgCount - cm.MsgCount)
    ELSE cm.MentionCount
  END)
FROM Channels c
INNER JOIN ChannelMembers cm
  ON c.Id = :ChannelId
  AND cm.ChannelId = :ChannelId
  AND cm.UserId = :UserId
  • Unread count is updated, e.g., when a channel is viewed. This involves storing the “last viewed” timestamp:
-- set last viewed at
UPDATE ChannelMembers
SET MentionCount = 0,
    MsgCount = Channels.TotalMsgCount - (SELECT
      COUNT(*)
    FROM Posts
    WHERE ChannelId = :ChannelId
    AND CreateAt > :NewLastViewedAt),
    LastViewedAt = :NewLastViewedAt
FROM Channels
WHERE Channels.Id = ChannelMembers.ChannelId
AND UserId = :UserId
AND ChannelId = :ChannelId

-- update last viewed at (new post, edit post, mark channel unread, etc)
UPDATE ChannelMembers
SET MentionCount = 0,
    MsgCount = Channels.TotalMsgCount,
    LastViewedAt = Channels.LastPostAt,
    LastUpdateAt = Channels.LastPostAt
FROM Channels
WHERE Channels.Id = ChannelMembers.ChannelId
AND UserId = :UserId
AND ChannelId = :ChannelId

TODO