forked from mtusi2026/mtusi2026lab3
53 lines
2.4 KiB
SQL
53 lines
2.4 KiB
SQL
-- Пользователи, чаты, состав и сообщения. Пароли и тексты сообщений
|
||
-- не пишутся приложением в логи. password_hash — только Argon2id.
|
||
-- Сессии в этой схеме нет: их источник истины — Redis (см. compose.yaml).
|
||
|
||
CREATE TABLE users (
|
||
id UUID PRIMARY KEY,
|
||
username TEXT NOT NULL,
|
||
display_name TEXT NOT NULL,
|
||
password_hash TEXT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL,
|
||
CONSTRAINT uq_users_username UNIQUE (username),
|
||
CONSTRAINT ck_users_username_format CHECK (username ~ '^[a-z0-9_]{3,32}$'),
|
||
CONSTRAINT ck_users_display_name_len CHECK (
|
||
char_length(btrim(display_name)) >= 1 AND char_length(display_name) <= 100
|
||
),
|
||
CONSTRAINT ck_users_password_hash_len CHECK (char_length(password_hash) >= 20)
|
||
);
|
||
|
||
CREATE TABLE chats (
|
||
id UUID PRIMARY KEY,
|
||
title TEXT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL,
|
||
CONSTRAINT ck_chats_title CHECK (char_length(btrim(title)) >= 1 AND char_length(title) <= 100)
|
||
);
|
||
|
||
-- Состав чата пишется в той же транзакции, что и строка chats.
|
||
-- Откат до COMMIT не оставляет чат без участников.
|
||
CREATE TABLE chat_members (
|
||
chat_id UUID NOT NULL REFERENCES chats (id),
|
||
user_id UUID NOT NULL REFERENCES users (id),
|
||
ordinal SMALLINT NOT NULL,
|
||
PRIMARY KEY (chat_id, user_id),
|
||
CONSTRAINT ck_chat_members_ordinal CHECK (ordinal >= 0 AND ordinal < 100)
|
||
);
|
||
|
||
-- Список чатов пользователя: фильтр по user_id, затем порядок (created_at, id) чата.
|
||
CREATE INDEX ix_chat_members_user ON chat_members (user_id, chat_id);
|
||
|
||
CREATE TABLE messages (
|
||
id UUID PRIMARY KEY,
|
||
chat_id UUID NOT NULL REFERENCES chats (id),
|
||
sender_id UUID NOT NULL REFERENCES users (id),
|
||
body TEXT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL,
|
||
CONSTRAINT ck_messages_text CHECK (char_length(btrim(body)) >= 1 AND char_length(body) <= 4000)
|
||
);
|
||
|
||
-- История: WHERE chat_id = $1 AND (created_at, id) > ($2, $3)
|
||
-- ORDER BY created_at, id LIMIT $4.
|
||
-- Ведущий chat_id отсекает чужие чаты, пара (created_at, id) продолжает
|
||
-- keyset с cursor и не читает предыдущие строки этой истории.
|
||
CREATE INDEX ix_messages_history ON messages (chat_id, created_at, id);
|