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