Repository navigation
Expand file tree
/
Copy path0001_schema.sql
More file actions
245 lines (213 loc) · 8.65 KB
/
Copy path0001_schema.sql
File metadata and controls
245 lines (213 loc) · 8.65 KB
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
CREATE TABLE users (
id TEXT PRIMARY KEY,
display_name TEXT NOT NULL,
avatar_url TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL,
handle TEXT NOT NULL DEFAULT '',
kind TEXT NOT NULL DEFAULT 'human',
owner_user_id TEXT REFERENCES users(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX idx_users_handle ON users(handle) WHERE handle <> '';
CREATE TABLE identities (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
provider TEXT NOT NULL,
provider_subject TEXT NOT NULL,
email TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL,
UNIQUE(provider, provider_subject)
);
CREATE TABLE workspaces (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
slug TEXT NOT NULL UNIQUE,
created_at TEXT NOT NULL,
route_id TEXT
);
CREATE UNIQUE INDEX idx_workspaces_route_id ON workspaces(route_id) WHERE route_id IS NOT NULL;
CREATE TABLE workspace_members (
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role TEXT NOT NULL,
created_at TEXT NOT NULL,
PRIMARY KEY (workspace_id, user_id)
);
CREATE TABLE channels (
id TEXT PRIMARY KEY,
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
name TEXT NOT NULL,
kind TEXT NOT NULL,
created_at TEXT NOT NULL,
archived_at TEXT,
route_id TEXT,
UNIQUE(workspace_id, name)
);
CREATE UNIQUE INDEX idx_channels_workspace_route_id ON channels(workspace_id, route_id) WHERE route_id IS NOT NULL;
CREATE TABLE direct_conversations (
id TEXT PRIMARY KEY,
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
created_at TEXT NOT NULL,
route_id TEXT
);
CREATE UNIQUE INDEX idx_direct_conversations_workspace_route_id ON direct_conversations(workspace_id, route_id) WHERE route_id IS NOT NULL;
CREATE TABLE direct_conversation_members (
conversation_id TEXT NOT NULL REFERENCES direct_conversations(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TEXT NOT NULL,
PRIMARY KEY (conversation_id, user_id)
);
CREATE INDEX idx_direct_conversation_members_user ON direct_conversation_members(user_id, conversation_id);
CREATE TABLE messages (
id TEXT PRIMARY KEY,
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
channel_id TEXT REFERENCES channels(id) ON DELETE CASCADE,
direct_conversation_id TEXT,
author_id TEXT NOT NULL REFERENCES users(id),
parent_message_id TEXT REFERENCES messages(id) ON DELETE CASCADE,
thread_root_id TEXT NOT NULL,
channel_seq BIGINT,
thread_seq BIGINT,
body TEXT NOT NULL,
body_format TEXT NOT NULL,
created_at TEXT NOT NULL,
edited_at TEXT,
deleted_at TEXT,
quoted_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
quoted_body_snapshot TEXT NOT NULL DEFAULT '',
quoted_author_id TEXT REFERENCES users(id) ON DELETE SET NULL,
client_nonce TEXT NOT NULL DEFAULT '',
route_id TEXT
);
CREATE INDEX idx_messages_channel_seq ON messages(channel_id, channel_seq);
CREATE INDEX idx_messages_thread_seq ON messages(thread_root_id, thread_seq);
CREATE INDEX idx_messages_channel_root_page ON messages(channel_id, parent_message_id, channel_seq)
WHERE channel_id IS NOT NULL AND parent_message_id IS NULL;
CREATE INDEX idx_messages_direct_page ON messages(direct_conversation_id, parent_message_id, channel_seq)
WHERE direct_conversation_id IS NOT NULL AND parent_message_id IS NULL;
CREATE UNIQUE INDEX idx_messages_author_client_nonce ON messages(author_id, client_nonce) WHERE client_nonce <> '';
CREATE UNIQUE INDEX idx_messages_workspace_route_id ON messages(workspace_id, route_id) WHERE route_id IS NOT NULL;
CREATE UNIQUE INDEX idx_messages_channel_root_unique_seq ON messages(channel_id, channel_seq)
WHERE channel_id IS NOT NULL AND parent_message_id IS NULL AND channel_seq IS NOT NULL;
CREATE UNIQUE INDEX idx_messages_direct_unique_seq ON messages(direct_conversation_id, channel_seq)
WHERE direct_conversation_id IS NOT NULL AND parent_message_id IS NULL AND channel_seq IS NOT NULL;
CREATE UNIQUE INDEX idx_messages_thread_unique_seq ON messages(thread_root_id, thread_seq)
WHERE parent_message_id IS NOT NULL AND thread_seq IS NOT NULL;
CREATE INDEX idx_messages_search_fts ON messages
USING GIN (to_tsvector('simple', body))
WHERE direct_conversation_id IS NULL AND channel_id IS NOT NULL AND deleted_at IS NULL;
CREATE TABLE thread_state (
root_message_id TEXT PRIMARY KEY REFERENCES messages(id) ON DELETE CASCADE,
reply_count BIGINT NOT NULL DEFAULT 0,
last_reply_at TEXT,
last_reply_author_ids_json TEXT NOT NULL DEFAULT '[]'
);
CREATE TABLE reactions (
message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
emoji TEXT NOT NULL,
created_at TEXT NOT NULL,
PRIMARY KEY (message_id, user_id, emoji)
);
CREATE TABLE events (
id TEXT PRIMARY KEY,
cursor TEXT NOT NULL UNIQUE,
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
channel_id TEXT,
type TEXT NOT NULL,
seq BIGINT,
payload_json TEXT NOT NULL,
created_at TEXT NOT NULL,
is_private BIGINT NOT NULL DEFAULT 0 CHECK (is_private IN (0, 1))
);
CREATE INDEX idx_events_workspace_cursor ON events(workspace_id, cursor);
CREATE TABLE event_recipients (
event_id TEXT NOT NULL REFERENCES events(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
PRIMARY KEY (event_id, user_id)
);
CREATE INDEX idx_event_recipients_user_event ON event_recipients(user_id, event_id);
CREATE TABLE uploads (
id TEXT PRIMARY KEY,
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
owner_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
filename TEXT NOT NULL,
content_type TEXT NOT NULL,
byte_size BIGINT NOT NULL,
storage_path TEXT NOT NULL,
created_at TEXT NOT NULL,
width BIGINT NOT NULL DEFAULT 0,
height BIGINT NOT NULL DEFAULT 0,
duration_ms BIGINT NOT NULL DEFAULT 0
);
CREATE TABLE message_attachments (
message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
upload_id TEXT NOT NULL REFERENCES uploads(id) ON DELETE CASCADE,
created_at TEXT NOT NULL,
PRIMARY KEY (message_id, upload_id)
);
CREATE INDEX idx_message_attachments_message_created ON message_attachments(message_id, created_at, upload_id);
CREATE TABLE invites (
id TEXT PRIMARY KEY,
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
token TEXT NOT NULL UNIQUE,
created_by TEXT NOT NULL REFERENCES users(id),
created_at TEXT NOT NULL,
accepted_at TEXT
);
CREATE TABLE auth_magic_links (
id TEXT PRIMARY KEY,
token TEXT NOT NULL UNIQUE,
token_hash TEXT NOT NULL DEFAULT '',
email TEXT NOT NULL,
display_name TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL,
expires_at TEXT NOT NULL,
used_at TEXT
);
CREATE INDEX idx_auth_magic_links_token ON auth_magic_links(token);
CREATE UNIQUE INDEX idx_auth_magic_links_token_hash ON auth_magic_links(token_hash) WHERE token_hash <> '';
CREATE TABLE sessions (
id TEXT PRIMARY KEY,
token TEXT NOT NULL UNIQUE,
token_hash TEXT NOT NULL DEFAULT '',
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TEXT NOT NULL,
expires_at TEXT NOT NULL,
revoked_at TEXT
);
CREATE INDEX idx_sessions_token ON sessions(token);
CREATE UNIQUE INDEX idx_sessions_token_hash ON sessions(token_hash) WHERE token_hash <> '';
CREATE TABLE channel_reads (
channel_id TEXT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
last_read_seq BIGINT NOT NULL DEFAULT 0,
last_read_at TEXT NOT NULL,
PRIMARY KEY (channel_id, user_id)
);
CREATE TABLE direct_reads (
conversation_id TEXT NOT NULL REFERENCES direct_conversations(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
last_read_seq BIGINT NOT NULL DEFAULT 0,
last_read_at TEXT NOT NULL,
PRIMARY KEY (conversation_id, user_id)
);
CREATE TABLE user_notification_settings (
user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
pushover_enabled BIGINT NOT NULL DEFAULT 0,
pushover_user_key TEXT NOT NULL DEFAULT ''
);
CREATE TABLE bot_tokens (
id TEXT PRIMARY KEY,
token_hash TEXT NOT NULL UNIQUE,
bot_user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
owner_user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
name TEXT NOT NULL,
scopes_json TEXT NOT NULL,
created_by TEXT REFERENCES users(id),
created_at TEXT NOT NULL,
last_used_at TEXT,
revoked_at TEXT
);
CREATE INDEX idx_bot_tokens_hash ON bot_tokens(token_hash);
CREATE INDEX idx_bot_tokens_bot ON bot_tokens(bot_user_id);