-
Notifications
You must be signed in to change notification settings - Fork 0
/
db_init.sql
44 lines (38 loc) · 1.3 KB
/
db_init.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE TABLE conversation_table (
conversation_id uuid PRIMARY KEY,
created_by VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT TIMEZONE('UTC', NOW()),
title VARCHAR(300),
is_group BOOLEAN
);
CREATE TABLE participant_table (
user_id VARCHAR(50),
conversation_id uuid,
PRIMARY KEY (user_id, conversation_id),
CONSTRAINT fk_conversation
FOREIGN KEY (conversation_id)
REFERENCES conversation_table (conversation_id)
);
CREATE INDEX user_idx ON participant_table (user_id);
CREATE TABLE message_table (
message_id uuid,
conversation_id uuid,
sent_at TIMESTAMP WITH TIME ZONE NOT NULL,
sender_id VARCHAR(50) NOT NULL,
content VARCHAR(1000) NOT NULL,
PRIMARY KEY (message_id),
CONSTRAINT fk_conversation
FOREIGN KEY (conversation_id)
REFERENCES conversation_table (conversation_id)
);
CREATE INDEX message_date_sort_idx
ON message_table (conversation_id, sender_id, content, sent_at DESC NULLS LAST);
CREATE TABLE message_is_read_table (
message_id uuid,
user_id VARCHAR(50),
is_read BOOLEAN,
PRIMARY KEY (message_id, user_id),
CONSTRAINT fk_message
FOREIGN KEY (message_id)
REFERENCES message_table (message_id)
);