Недавно мы с командой переехали в мессенджер Mattermost на замену Slack, но мы столкнулись с одной неприятной проблемой. мы не можем в бесплатной версии уведомлять пользователей через тег группы @groupname в бесплатной версии этой возможности нет, и групп не предусмотрено.

Покопавшись в настройках, заметили что у каждого пользователя есть ключевые слова для упоминаний.

После мы проверили еще одну идею если мы создадим пользователя @devops и упомянем его где нибудь то он будет подсвечиваться как пользователь:

Но самое интересное то, что у всех пользователей у которых есть в ключевых словах упоминаний @devops тоже получат уведомление. Так родилась идея это автоматизировать и как то структурировать.

Подключаемся к БД Mattermost нас интересует 3 таблицы:

table usergroups
(
    id             varchar(26) not null primary key,
    name           varchar(64) unique,
    displayname    varchar(128),
    description    varchar(1024),
    source         varchar(64),
    remoteid       varchar(48),
    createat       bigint,
    updateat       bigint,
    deleteat       bigint,
    allowreference boolean,
    unique (source, remoteid)
);

table groupmembers
(
    groupid  varchar(26) not null,
    userid   varchar(26) not null,
    createat bigint,
    deleteat bigint,
    primary key (groupid, userid)
);

table users
(
    id                 varchar(26) not null
    primary key,
    createat           bigint,
    updateat           bigint,
    deleteat           bigint,
    username           varchar(64) unique,
    password           varchar(128),
    authdata           varchar(128) unique,
    authservice        varchar(32),
    email              varchar(128) unique,
    emailverified      boolean,
    nickname           varchar(64),
    firstname          varchar(64),
    lastname           varchar(64),
    roles              varchar(256),
    allowmarketing     boolean,
    props              jsonb,
    notifyprops        jsonb,
    lastpasswordupdate bigint,
    lastpictureupdate  bigint,
    failedattempts     integer,
    locale             varchar(5),
    mfaactive          boolean,
    mfasecret          varchar(128),
    position           varchar(128),
    timezone           jsonb,
    remoteid           varchar(26)
);

Первые 2 таблицы usergroups, groupmembers были пустыми и так как у нас бесплатная версия и мы их не используем решил сделать на их базе.

Создаем 2х пользователей в Mattermost, devops и developers.

Создаем группы с названием таким же как пользователи:

INSERT INTO usergroups (id, name, displayname, description, source, remoteid, createat, updateat, deleteat,allowreference)
VALUES ('1', 'devops', 'devops', null, null, null, null, null, null, null)

INSERT INTO usergroups (id, name, displayname, description, source, remoteid, createat, updateat, deleteat,allowreference)
VALUES ('2', 'developers', 'developers', null, null, null, null, null, null, null)

Привязываем пользователя testuser к группе devops и developers:

INSERT INTO groupmembers (groupid, userid, createat, deleteat)
VALUES (
        (select id from usergroups where 
        usergroups.name = 'devops'),
        (select id from users where 
        users.username = 'testuser'),
        null,null);

INSERT INTO groupmembers (groupid, userid, createat, deleteat)
VALUES (
        (select id from usergroups where 
        usergroups.name = 'developers'),
        (select id from users where 
        users.username = 'testuser'),
        null,null);

Проверяем, к каким группам привязаны пользователи:

select string_agg(concat('@', usergroups.name), ',') as groups, users.username
from groupmembers
         left join users on groupmembers.userid = users.id
         left join usergroups on groupmembers.groupid = usergroups.id
group by users.username;

Также нам пригодится еще один запрос, который делает такую же табличку, но теперь какие пользователи в каких группах состоят:

select string_agg(concat('@', users.username), ',') as users, usergroups.name as groupname
from groupmembers
         left join users on groupmembers.userid = users.id
         left join usergroups on groupmembers.groupid = usergroups.id
group by usergroups.name;

В таблице users есть столбец notifyprops в нем содержится jsonb нас интересует там mention_keys, который как раз отвечает за "ключевые слова для упоминаний" из первого скриншота.

Идея следующая. Зная, к каким группам привязаны пользователи, мы можем найти их в таблице users и заменить у них поле mention_keys на результат выполнения из из первой таблицы:

UPDATE users
SET notifyprops = jsonb_set(notifyprops, '{mention_keys}', to_jsonb(subq.groups), true)
from (select string_agg(concat('@', usergroups.name), ',') as groups, users.username as un
      from groupmembers
               left join users on groupmembers.userid = users.id
               left join usergroups on groupmembers.groupid = usergroups.id
      group by users.username) as subq
where username = subq.un

Теперь для всех пользователей которые находятся в группах добавятся ключевые слова для упоминаний с тегами этих групп.

Но удобно было бы видеть, кто состоит в наших "группах", если нажать на пользователя.

У каждого пользователя есть поле position, в которой записана занимаемая должность, мы решили складывать информацию о пользователях туда.

Находим всех пользователей у которых название группы совпадает с названием пользователя и записываем в position данные из о пользователях которые состоят в этой группе.

UPDATE users
SET position = subq.users
from (select string_agg(concat('@', users.username), ',') as users, usergroups.name as groupname
      from groupmembers
               left join users on groupmembers.userid = users.id
               left join usergroups on groupmembers.groupid = usergroups.id
      group by usergroups.name) as subq
where username = subq.groupname

На этом собственно все. После нехитрых манипуляций получаем очень нужную функцию, которая изначально отсутствовала.