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