this repo has no description
lewis.moe
1CREATE TYPE notification_channel AS ENUM ('email', 'discord', 'telegram', 'signal');
2CREATE TYPE notification_status AS ENUM ('pending', 'processing', 'sent', 'failed');
3CREATE TYPE notification_type AS ENUM (
4 'welcome',
5 'email_verification',
6 'password_reset',
7 'email_update',
8 'account_deletion',
9 'admin_email',
10 'plc_operation',
11 'two_factor_code'
12);
13
14CREATE TABLE IF NOT EXISTS users (
15 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
16 handle TEXT NOT NULL UNIQUE,
17 email TEXT UNIQUE,
18 did TEXT NOT NULL UNIQUE,
19 password_hash TEXT NOT NULL,
20 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
21 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
22
23 deactivated_at TIMESTAMPTZ,
24 invites_disabled BOOLEAN DEFAULT FALSE,
25 takedown_ref TEXT,
26
27 preferred_notification_channel notification_channel NOT NULL DEFAULT 'email',
28
29 password_reset_code TEXT,
30 password_reset_code_expires_at TIMESTAMPTZ,
31
32 email_pending_verification TEXT,
33 email_confirmation_code TEXT,
34 email_confirmation_code_expires_at TIMESTAMPTZ,
35 email_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
36
37 two_factor_enabled BOOLEAN NOT NULL DEFAULT FALSE,
38
39 discord_id TEXT,
40 discord_verified BOOLEAN NOT NULL DEFAULT FALSE,
41
42 telegram_username TEXT,
43 telegram_verified BOOLEAN NOT NULL DEFAULT FALSE,
44
45 signal_number TEXT,
46 signal_verified BOOLEAN NOT NULL DEFAULT FALSE
47);
48
49CREATE INDEX IF NOT EXISTS idx_users_password_reset_code ON users(password_reset_code) WHERE password_reset_code IS NOT NULL;
50CREATE INDEX IF NOT EXISTS idx_users_email_confirmation_code ON users(email_confirmation_code) WHERE email_confirmation_code IS NOT NULL;
51CREATE INDEX IF NOT EXISTS idx_users_discord_id ON users(discord_id) WHERE discord_id IS NOT NULL;
52CREATE INDEX IF NOT EXISTS idx_users_telegram_username ON users(telegram_username) WHERE telegram_username IS NOT NULL;
53CREATE INDEX IF NOT EXISTS idx_users_signal_number ON users(signal_number) WHERE signal_number IS NOT NULL;
54
55CREATE TABLE IF NOT EXISTS invite_codes (
56 code TEXT PRIMARY KEY,
57 available_uses INT NOT NULL DEFAULT 1,
58 created_by_user UUID NOT NULL REFERENCES users(id),
59 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
60 disabled BOOLEAN DEFAULT FALSE
61);
62
63CREATE TABLE IF NOT EXISTS invite_code_uses (
64 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
65 code TEXT NOT NULL REFERENCES invite_codes(code),
66 used_by_user UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
67 used_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
68 UNIQUE(code, used_by_user)
69);
70
71CREATE TABLE IF NOT EXISTS user_keys (
72 user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
73 key_bytes BYTEA NOT NULL,
74 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
75 encrypted_at TIMESTAMPTZ,
76 encryption_version INTEGER DEFAULT 0
77);
78
79CREATE TABLE IF NOT EXISTS repos (
80 user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
81 repo_root_cid TEXT NOT NULL,
82 repo_rev TEXT,
83 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
84 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
85);
86
87CREATE TABLE IF NOT EXISTS blocks (
88 cid BYTEA PRIMARY KEY,
89 data BYTEA NOT NULL,
90 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
91);
92
93CREATE TABLE IF NOT EXISTS records (
94 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
95 repo_id UUID NOT NULL REFERENCES repos(user_id) ON DELETE CASCADE,
96 collection TEXT NOT NULL,
97 rkey TEXT NOT NULL,
98 record_cid TEXT NOT NULL,
99 takedown_ref TEXT,
100 repo_rev TEXT,
101 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
102 UNIQUE(repo_id, collection, rkey)
103);
104
105CREATE INDEX idx_records_repo_rev ON records(repo_rev);
106
107CREATE TABLE IF NOT EXISTS blobs (
108 cid TEXT PRIMARY KEY,
109 mime_type TEXT NOT NULL,
110 size_bytes BIGINT NOT NULL,
111 created_by_user UUID NOT NULL REFERENCES users(id),
112 storage_key TEXT NOT NULL,
113 takedown_ref TEXT,
114 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
115);
116
117CREATE TABLE IF NOT EXISTS app_passwords (
118 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
119 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
120 name TEXT NOT NULL,
121 password_hash TEXT NOT NULL,
122 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
123 privileged BOOLEAN NOT NULL DEFAULT FALSE,
124 UNIQUE(user_id, name)
125);
126
127CREATE TABLE reports (
128 id BIGINT PRIMARY KEY,
129 reason_type TEXT NOT NULL,
130 reason TEXT,
131 subject_json JSONB NOT NULL,
132 reported_by_did TEXT NOT NULL,
133 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
134);
135
136CREATE TABLE IF NOT EXISTS account_deletion_requests (
137 token TEXT PRIMARY KEY,
138 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
139 expires_at TIMESTAMPTZ NOT NULL,
140 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
141);
142
143CREATE TABLE IF NOT EXISTS notification_queue (
144 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
145 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
146 channel notification_channel NOT NULL DEFAULT 'email',
147 notification_type notification_type NOT NULL,
148 status notification_status NOT NULL DEFAULT 'pending',
149 recipient TEXT NOT NULL,
150 subject TEXT,
151 body TEXT NOT NULL,
152 metadata JSONB,
153 attempts INT NOT NULL DEFAULT 0,
154 max_attempts INT NOT NULL DEFAULT 3,
155 last_error TEXT,
156 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
157 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
158 scheduled_for TIMESTAMPTZ NOT NULL DEFAULT NOW(),
159 processed_at TIMESTAMPTZ
160);
161
162CREATE INDEX idx_notification_queue_status_scheduled
163 ON notification_queue(status, scheduled_for)
164 WHERE status = 'pending';
165
166CREATE INDEX idx_notification_queue_user_id ON notification_queue(user_id);
167
168CREATE TABLE IF NOT EXISTS reserved_signing_keys (
169 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
170 did TEXT,
171 public_key_did_key TEXT NOT NULL,
172 private_key_bytes BYTEA NOT NULL,
173 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
174 expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '24 hours',
175 used_at TIMESTAMPTZ
176);
177
178CREATE INDEX IF NOT EXISTS idx_reserved_signing_keys_did ON reserved_signing_keys(did) WHERE did IS NOT NULL;
179CREATE INDEX IF NOT EXISTS idx_reserved_signing_keys_expires ON reserved_signing_keys(expires_at) WHERE used_at IS NULL;
180
181CREATE TABLE repo_seq (
182 seq BIGSERIAL PRIMARY KEY,
183 did TEXT NOT NULL,
184 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
185 event_type TEXT NOT NULL,
186 commit_cid TEXT,
187 prev_cid TEXT,
188 ops JSONB,
189 blobs TEXT[],
190 blocks_cids TEXT[]
191);
192
193CREATE INDEX idx_repo_seq_seq ON repo_seq(seq);
194CREATE INDEX idx_repo_seq_did ON repo_seq(did);
195
196CREATE TABLE IF NOT EXISTS session_tokens (
197 id SERIAL PRIMARY KEY,
198 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
199 access_jti TEXT NOT NULL UNIQUE,
200 refresh_jti TEXT NOT NULL UNIQUE,
201 access_expires_at TIMESTAMPTZ NOT NULL,
202 refresh_expires_at TIMESTAMPTZ NOT NULL,
203 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
204 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
205);
206
207CREATE INDEX idx_session_tokens_did ON session_tokens(did);
208CREATE INDEX idx_session_tokens_access_jti ON session_tokens(access_jti);
209CREATE INDEX idx_session_tokens_refresh_jti ON session_tokens(refresh_jti);
210
211CREATE TABLE IF NOT EXISTS used_refresh_tokens (
212 refresh_jti TEXT PRIMARY KEY,
213 session_id INTEGER NOT NULL REFERENCES session_tokens(id) ON DELETE CASCADE,
214 used_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
215);
216
217CREATE INDEX idx_used_refresh_tokens_session_id ON used_refresh_tokens(session_id);
218
219CREATE TABLE IF NOT EXISTS oauth_device (
220 id TEXT PRIMARY KEY,
221 session_id TEXT NOT NULL UNIQUE,
222 user_agent TEXT,
223 ip_address TEXT NOT NULL,
224 last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
225);
226
227CREATE TABLE IF NOT EXISTS oauth_authorization_request (
228 id TEXT PRIMARY KEY,
229 did TEXT REFERENCES users(did) ON DELETE CASCADE,
230 device_id TEXT REFERENCES oauth_device(id) ON DELETE SET NULL,
231 client_id TEXT NOT NULL,
232 client_auth JSONB,
233 parameters JSONB NOT NULL,
234 expires_at TIMESTAMPTZ NOT NULL,
235 code TEXT UNIQUE
236);
237
238CREATE INDEX idx_oauth_auth_request_expires ON oauth_authorization_request(expires_at);
239CREATE INDEX idx_oauth_auth_request_code ON oauth_authorization_request(code) WHERE code IS NOT NULL;
240
241CREATE TABLE IF NOT EXISTS oauth_token (
242 id SERIAL PRIMARY KEY,
243 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
244 token_id TEXT NOT NULL UNIQUE,
245 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
246 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
247 expires_at TIMESTAMPTZ NOT NULL,
248 client_id TEXT NOT NULL,
249 client_auth JSONB NOT NULL,
250 device_id TEXT REFERENCES oauth_device(id) ON DELETE SET NULL,
251 parameters JSONB NOT NULL,
252 details JSONB,
253 code TEXT UNIQUE,
254 current_refresh_token TEXT UNIQUE,
255 scope TEXT
256);
257
258CREATE INDEX idx_oauth_token_did ON oauth_token(did);
259CREATE INDEX idx_oauth_token_code ON oauth_token(code) WHERE code IS NOT NULL;
260
261CREATE TABLE IF NOT EXISTS oauth_account_device (
262 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
263 device_id TEXT NOT NULL REFERENCES oauth_device(id) ON DELETE CASCADE,
264 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
265 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
266 PRIMARY KEY (did, device_id)
267);
268
269CREATE TABLE IF NOT EXISTS oauth_authorized_client (
270 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
271 client_id TEXT NOT NULL,
272 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
273 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
274 data JSONB NOT NULL,
275 PRIMARY KEY (did, client_id)
276);
277
278CREATE TABLE IF NOT EXISTS oauth_used_refresh_token (
279 refresh_token TEXT PRIMARY KEY,
280 token_id INTEGER NOT NULL REFERENCES oauth_token(id) ON DELETE CASCADE
281);
282
283CREATE TABLE oauth_dpop_jti (
284 jti TEXT PRIMARY KEY,
285 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
286);
287
288CREATE INDEX idx_oauth_dpop_jti_created_at ON oauth_dpop_jti(created_at);
289
290CREATE TABLE plc_operation_tokens (
291 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
292 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
293 token TEXT NOT NULL UNIQUE,
294 expires_at TIMESTAMPTZ NOT NULL,
295 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
296);
297
298CREATE INDEX idx_plc_op_tokens_user ON plc_operation_tokens(user_id);
299CREATE INDEX idx_plc_op_tokens_expires ON plc_operation_tokens(expires_at);
300
301CREATE TABLE IF NOT EXISTS account_preferences (
302 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
303 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
304 name TEXT NOT NULL,
305 value_json JSONB NOT NULL,
306 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
307 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
308 UNIQUE(user_id, name)
309);
310
311CREATE INDEX IF NOT EXISTS idx_account_preferences_user_id ON account_preferences(user_id);
312CREATE INDEX IF NOT EXISTS idx_account_preferences_name ON account_preferences(name);
313
314CREATE TABLE oauth_2fa_challenge (
315 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
316 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
317 request_uri TEXT NOT NULL,
318 code TEXT NOT NULL,
319 attempts INTEGER NOT NULL DEFAULT 0,
320 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
321 expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '10 minutes'
322);
323
324CREATE INDEX idx_oauth_2fa_challenge_request_uri ON oauth_2fa_challenge(request_uri);
325CREATE INDEX idx_oauth_2fa_challenge_expires ON oauth_2fa_challenge(expires_at);