4533ceaf2d
- POST /api/v1/chats (ensure chat between users, normalized pair) - GET /api/v1/chats (list with last message + unread) - GET /api/v1/chats/:id/messages - POST /api/v1/chats/:id/messages (send text/photo, push via WS) - PUT /api/v1/chats/:id/read (mark all read) - PUT /api/v1/messages/:msgId (edit, owner only) - DELETE /api/v1/messages/:msgId (soft delete) - POST /api/v1/blocks, DELETE /api/v1/blocks/:id - POST /api/v1/reports - WS /ws?token= (real-time message push, ping/pong) - migrations 0003: chats, messages, blocks, reports
50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
-- Чаты и сообщения
|
|
CREATE TABLE IF NOT EXISTS chats (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_a UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
user_b UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
last_msg_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE (user_a, user_b),
|
|
CHECK (user_a < user_b)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chats_user_a ON chats(user_a, last_msg_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_chats_user_b ON chats(user_b, last_msg_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
|
|
sender_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
body TEXT,
|
|
photo_url TEXT,
|
|
read BOOLEAN NOT NULL DEFAULT FALSE,
|
|
edited BOOLEAN NOT NULL DEFAULT FALSE,
|
|
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messages_chat ON messages(chat_id, created_at);
|
|
|
|
-- Блокировки
|
|
CREATE TABLE IF NOT EXISTS blocks (
|
|
blocker_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
blocked_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
PRIMARY KEY (blocker_id, blocked_id)
|
|
);
|
|
|
|
-- Жалобы
|
|
CREATE TABLE IF NOT EXISTS reports (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
reporter_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
target_type TEXT NOT NULL CHECK (target_type IN ('user','message','chat')),
|
|
target_id TEXT NOT NULL,
|
|
reason TEXT,
|
|
metadata JSONB,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_reports_target ON reports(target_type, target_id);
|