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