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

Debugging tools

Tagged debugging, dstat, ngrep, perf, strace, tcpdump, wireshark, netcat, netstat, dtrace, dtruss  Languages bash

IO and system calls

  • dstat

Monitor network and disk IO:

dstat -t
  • dtrace / dtruss (OSX)

To get the list of available system calls use:

sudo dtrace -ln 'syscall:::entry'

Find which files a program is opening (same as strace -f -p $PID -e open):

sudo dtruss -t open_nocancel -p $PID

Also see ls /usr/bin/.d*

  • strace (Linux)

Monitor system calls made by an app:

strace ruby app.rb

Writes all system calls made by SSH, and subprocesses (-f), to a file named ssh.txt:

strace -f -o ssh.txt ssh jebus.com

Spy on all ‘open’ system calls made by a process:

strace -f -p $PID -e open

Use these commands to see a list of all available system calls (Linux only):

man syscalls
  • opensnoop

Monitor what files are being opened:

opensnoop -p $PID
strace -e open -p $PID

Networking

  • netcat

Pipe/copy data over a network:

cat request.txt | nc metafilter.com 80
  • netstat

Find which programs are listening to which port:

sudo netstat -tunapl
lsof -i -P # OSX
  • ngrep

Listen to traffic containing the string “localhost” on any network interface:

sudo ngrep -d any localhost
  • tcpdump

Listen to traffic containing the string “localhost” on any network interface:

sudo tcpdump port 80 -w http.pcap

Writes a pcap file that can be analyzed with Wireshark.

  • Wireshark

Analyze pcap files from ngrep, tcpdump, etc:

wireshark http.pcap

CPU (Linux)

  • perf

Run perf, a sampling profiler, to see where your application is spending its time:

sudo perf record ruby app.rb

Find out what the program using the most CPU time is doing:

sudo perf top

Find out if an app is using the L1 cache which is ~200 times faster than RAM:

sudo perf stat -e L1-dcache-load-misses my_golang_app

References

How to make Vim yank to the OSX clipboard

Tagged clipboard, osx, vim, yank  Languages 

Add this to .vimrc:

set clipboard=unnamed

Do NOT use something like this because it doesn’t work perfectly:

function! ClipboardYank()
  call system('pbcopy', @@)
endfunction
function! ClipboardPaste()
  let @@ = system('pbpaste')
endfunction

vnoremap <silent> y y:call ClipboardYank()<cr>
vnoremap <silent> d d:call ClipboardYank()<cr>
nnoremap <silent> p :call ClipboardPaste()<cr>p

How to get rid of Gulp, Grunt, and <your favorite JS build tool>

Tagged babel, browserify, build, grunt, gulp, npm  Languages bash

I would happily pay 50-100€ for a build tool that would help me get rid of Gulp, Grunt, etc, and save me from hours of unproductive fighting with unreliable tools.

The following snippet is not quite a full solution for getting rid of Gulp/Grunt.

Installation

npm install -g node-sass browserify babelify uglify-js

Usage

# Compile JS modules into one file
browserify js/app.js -o dist/js/app.js -t [ babelify --presets [ es2015 ] ]
# Compile CSS/SASS modules into one file
node-sass css/app.scss --output-style compressed --include-path node_modules/bootstrap-sass/assets/stylesheets dist/css/app.css
# Minify JS
uglifyjs --compress --mangle -o dist/js/app.js -- dist/js/app.js

Note:

See How to Use npm as a Build Tool.

Mithril button example with debounce

Tagged debounce, mithril  Languages javascript

Live example: https://jsfiddle.net/ah0pt5r3/

Javascript

// Source: https://davidwalsh.name/javascript-debounce-function
var debounce = function(func, wait, immediate) {
  var timeout;
  return function() {
    var context = this,
      args = arguments;
    var later = function() {
      timeout = null;
      if (!immediate) func.apply(context, args);
    };
    var callNow = immediate && !timeout;
    clearTimeout(timeout);
    timeout = setTimeout(later, wait);
    if (callNow) func.apply(context, args);
  };
};

// Widget model
Widget = function(data) {
  data = data || {}
  this.id = m.prop(data.id)
  this.name = m.prop(data.name)
  this.clicked = m.prop(0)
}

// A button that represents a Widget and that can be clicked.
WidgetButton = function(data) {
  var widget = new Widget(data);
  //
  // Triggered by mount.
  //
  this.controller = function() {
    // This is passed to the view
    return { "one": "two" }
  };
  //
  // Triggered after controller has finished executing.
  //
  this.view = function(ctrl) {
    // ctrl contains { "one": "two" }
    return [
      m("a.btn.btn-default.btn-sm", {
        id: "widget-" + widget.id(),
        onclick: this.onclick
      }, [
        m("span", widget.name() + " " + widget.clicked()),
      ])
    ];
  };
  //
  // Triggered when clicking the widget.
  //
  // Uses debounce timeout of 200 ms with immediate triggering.
  // In other words, only the first click in each 200 ms interval is triggered
  // instead of every click.
  //
  this.onclick = debounce(function(e) {
    widget.clicked(widget.clicked() + 1)
    console.debug("Clicked widget " + widget.id())
  }, 200, true);
}

var buttons = document.getElementsByClassName('widget-button');
for (ix in buttons) {
  var button = buttons[ix];
  m.mount(button, new WidgetButton({
    id: button.getAttribute("data-id"),
    name: button.getAttribute("data-name")
  }));
}

HTML

<div class="widget-button" data-id="1" data-name="One"></div>
<div class="widget-button" data-id="2" data-name="Two"></div>

Go and Postgres Example

Tagged example, golang, polling, postgres  Languages go
package main

import (
    "database/sql"
    _ "github.com/lib/pq"
    "log"
    "time"
)

func pollChanges() {
    var id int
    var name string
    rows, err := db.Query("SELECT id, name FROM people")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
        err := rows.Scan(&id, &name)
        if err != nil {
            log.Fatal(err)
        }
        log.Println(id, name)
    }
    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
}

var db *sql.DB

func main() {
    var conninfo string = "dbname=people sslmode=disable"
    var err error
    db, err = sql.Open("postgres", conninfo)
    defer db.Close()
    if err != nil {
        log.Fatal(err)
    }
    for {
        time.Sleep(1000 * time.Millisecond)
        log.Println("Polling for changes...")
        pollChanges()
    }
}

Keyword arguments in Ruby +2.1

Tagged argument, keyword, options, ruby  Languages ruby

Before keyword arguments were introduced in Ruby:

def call(options={})
  puts options[:optional_param]
  puts options.fetch(:optional_param_with_default, 'default')
  puts options.fetch(:mandatory_param)
end

After:

def call(mandatory_param:, optional_param: nil, optional_param_with_default: 'default')
  puts optional_param
  puts optional_param_with_default
  puts mandatory_param
end

Multi-tenancy in Ecto and Phoenix

Tagged ecto, elixir, multi-tenancy, postgres, phoenix  Languages bash, elixir

Creating a new tentant

A new tenant requires a namespace, which is a schema in Postgres, and a prefix in Ecto:

$ psql -U postgres database_x
> create schema aktagon; 

Querying data

import Ecto.Query
email = "christian@aktagon.com"
q = from(m in User, where: m.email == ^email)
Repo.all(%{q | prefix: "aktagon"})

Documentation: https://hexdocs.pm/ecto/Ecto.Query.html#module-query-prefix

Inserting data

Repo.insert(
  Ecto.put_meta(
   %User{ email: "christian@aktagon.com" },
   prefix: "aktagon"
  )
)

Migrations

$ mix ecto.migrate --prefix "aktagon"

Notes

  • (KeyError) key :__meta__ not found

I got this error when passing a changeset to Ecto.put_meta instead of a User struct.

How to fix "the response was already sent" error in Elixir

Tagged elixir, phoenix, plug  Languages elixir

If you get an “(Plug.Conn.AlreadySentError) the response was already sent” error:

Server: localhost:4000 (http)
Request: POST /api/v1/xxx
** (exit) an exception was raised:
    ** (Plug.Conn.AlreadySentError) the response was already sent
        (plug) lib/plug/conn.ex:332: Plug.Conn.put_status/2

Verify that you have not defined the action plug more than once:

plug :action