byjp.me
forked from
slices.network/quickslice
Auto-indexing service and GraphQL API for AT Protocol Records
1CREATE TABLE IF NOT EXISTS "schema_migrations" (version varchar(128) primary key);
2CREATE TABLE record (
3 uri TEXT PRIMARY KEY NOT NULL,
4 cid TEXT NOT NULL,
5 did TEXT NOT NULL,
6 collection TEXT NOT NULL,
7 json TEXT NOT NULL,
8 indexed_at TEXT NOT NULL DEFAULT (datetime('now'))
9, rkey TEXT
10 GENERATED ALWAYS AS (
11 substr(uri, instr(substr(uri, instr(substr(uri, 6), '/') + 6), '/') + instr(substr(uri, 6), '/') + 6)
12 ) VIRTUAL);
13CREATE INDEX idx_record_did ON record(did);
14CREATE INDEX idx_record_collection ON record(collection);
15CREATE INDEX idx_record_did_collection ON record(did, collection);
16CREATE INDEX idx_record_indexed_at ON record(indexed_at DESC);
17CREATE INDEX idx_record_cid ON record(cid);
18CREATE TABLE actor (
19 did TEXT PRIMARY KEY NOT NULL,
20 handle TEXT,
21 indexed_at TEXT NOT NULL
22);
23CREATE INDEX idx_actor_handle ON actor(handle);
24CREATE INDEX idx_actor_indexed_at ON actor(indexed_at DESC);
25CREATE TABLE lexicon (
26 id TEXT PRIMARY KEY NOT NULL,
27 json TEXT NOT NULL,
28 created_at TEXT NOT NULL DEFAULT (datetime('now'))
29, validator_js TEXT);
30CREATE INDEX idx_lexicon_created_at ON lexicon(created_at DESC);
31CREATE TABLE config (
32 key TEXT PRIMARY KEY NOT NULL,
33 value TEXT NOT NULL,
34 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
35);
36CREATE TABLE jetstream_activity (
37 id INTEGER PRIMARY KEY AUTOINCREMENT,
38 timestamp TEXT NOT NULL,
39 operation TEXT NOT NULL,
40 collection TEXT NOT NULL,
41 did TEXT NOT NULL,
42 status TEXT NOT NULL,
43 error_message TEXT,
44 event_json TEXT NOT NULL
45);
46CREATE INDEX idx_jetstream_activity_timestamp ON jetstream_activity(timestamp DESC);
47CREATE TABLE jetstream_cursor (
48 id INTEGER PRIMARY KEY CHECK (id = 1),
49 cursor INTEGER NOT NULL,
50 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
51);
52CREATE TABLE oauth_client (
53 client_id TEXT PRIMARY KEY,
54 client_secret TEXT,
55 client_name TEXT NOT NULL,
56 redirect_uris TEXT NOT NULL,
57 grant_types TEXT NOT NULL,
58 response_types TEXT NOT NULL,
59 scope TEXT,
60 token_endpoint_auth_method TEXT NOT NULL,
61 client_type TEXT NOT NULL,
62 created_at INTEGER NOT NULL,
63 updated_at INTEGER NOT NULL,
64 metadata TEXT NOT NULL DEFAULT '{}',
65 access_token_expiration INTEGER NOT NULL DEFAULT 86400,
66 refresh_token_expiration INTEGER NOT NULL DEFAULT 1209600,
67 require_redirect_exact INTEGER NOT NULL DEFAULT 1,
68 registration_access_token TEXT,
69 jwks TEXT
70);
71CREATE TABLE oauth_access_token (
72 token TEXT PRIMARY KEY,
73 token_type TEXT NOT NULL DEFAULT 'Bearer',
74 client_id TEXT NOT NULL,
75 user_id TEXT,
76 session_id TEXT,
77 session_iteration INTEGER,
78 scope TEXT,
79 created_at INTEGER NOT NULL,
80 expires_at INTEGER NOT NULL,
81 revoked INTEGER NOT NULL DEFAULT 0,
82 dpop_jkt TEXT,
83 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
84);
85CREATE INDEX idx_oauth_access_token_expires_at ON oauth_access_token(expires_at);
86CREATE INDEX idx_oauth_access_token_client_id ON oauth_access_token(client_id);
87CREATE INDEX idx_oauth_access_token_user_id ON oauth_access_token(user_id);
88CREATE INDEX idx_oauth_access_token_dpop_jkt ON oauth_access_token(dpop_jkt);
89CREATE TABLE oauth_refresh_token (
90 token TEXT PRIMARY KEY,
91 access_token TEXT NOT NULL,
92 client_id TEXT NOT NULL,
93 user_id TEXT NOT NULL,
94 session_id TEXT,
95 session_iteration INTEGER,
96 scope TEXT,
97 created_at INTEGER NOT NULL,
98 expires_at INTEGER,
99 revoked INTEGER NOT NULL DEFAULT 0,
100 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
101);
102CREATE INDEX idx_oauth_refresh_token_expires_at ON oauth_refresh_token(expires_at);
103CREATE INDEX idx_oauth_refresh_token_client_id ON oauth_refresh_token(client_id);
104CREATE TABLE oauth_par_request (
105 request_uri TEXT PRIMARY KEY,
106 authorization_request TEXT NOT NULL,
107 client_id TEXT NOT NULL,
108 created_at INTEGER NOT NULL,
109 expires_at INTEGER NOT NULL,
110 subject TEXT,
111 metadata TEXT NOT NULL DEFAULT '{}',
112 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
113);
114CREATE INDEX idx_oauth_par_request_expires_at ON oauth_par_request(expires_at);
115CREATE TABLE oauth_dpop_nonce (
116 nonce TEXT PRIMARY KEY,
117 expires_at INTEGER NOT NULL
118);
119CREATE INDEX idx_oauth_dpop_nonce_expires_at ON oauth_dpop_nonce(expires_at);
120CREATE TABLE oauth_dpop_jti (
121 jti TEXT PRIMARY KEY,
122 created_at INTEGER NOT NULL
123);
124CREATE INDEX idx_oauth_dpop_jti_created_at ON oauth_dpop_jti(created_at);
125CREATE TABLE oauth_auth_request (
126 session_id TEXT PRIMARY KEY,
127 client_id TEXT NOT NULL,
128 redirect_uri TEXT NOT NULL,
129 scope TEXT,
130 state TEXT,
131 code_challenge TEXT,
132 code_challenge_method TEXT,
133 response_type TEXT NOT NULL,
134 nonce TEXT,
135 login_hint TEXT,
136 created_at INTEGER NOT NULL,
137 expires_at INTEGER NOT NULL,
138 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
139);
140CREATE INDEX idx_oauth_auth_request_expires_at ON oauth_auth_request(expires_at);
141CREATE INDEX idx_oauth_auth_request_client_id ON oauth_auth_request(client_id);
142CREATE TABLE oauth_atp_session (
143 session_id TEXT NOT NULL,
144 iteration INTEGER NOT NULL,
145 did TEXT,
146 session_created_at INTEGER NOT NULL,
147 atp_oauth_state TEXT NOT NULL,
148 signing_key_jkt TEXT NOT NULL,
149 dpop_key TEXT NOT NULL,
150 access_token TEXT,
151 refresh_token TEXT,
152 access_token_created_at INTEGER,
153 access_token_expires_at INTEGER,
154 access_token_scopes TEXT,
155 session_exchanged_at INTEGER,
156 exchange_error TEXT,
157 PRIMARY KEY (session_id, iteration)
158);
159CREATE INDEX idx_oauth_atp_session_did ON oauth_atp_session(did);
160CREATE INDEX idx_oauth_atp_session_access_token ON oauth_atp_session(access_token);
161CREATE TABLE oauth_atp_request (
162 oauth_state TEXT PRIMARY KEY,
163 authorization_server TEXT NOT NULL,
164 nonce TEXT NOT NULL,
165 pkce_verifier TEXT NOT NULL,
166 signing_public_key TEXT NOT NULL,
167 dpop_private_key TEXT NOT NULL,
168 created_at INTEGER NOT NULL,
169 expires_at INTEGER NOT NULL
170);
171CREATE INDEX idx_oauth_atp_request_expires_at ON oauth_atp_request(expires_at);
172CREATE TABLE oauth_authorization_code (
173 code TEXT PRIMARY KEY,
174 client_id TEXT NOT NULL,
175 user_id TEXT NOT NULL,
176 session_id TEXT,
177 session_iteration INTEGER,
178 redirect_uri TEXT NOT NULL,
179 scope TEXT,
180 code_challenge TEXT,
181 code_challenge_method TEXT,
182 nonce TEXT,
183 created_at INTEGER NOT NULL,
184 expires_at INTEGER NOT NULL,
185 used INTEGER NOT NULL DEFAULT 0
186);
187CREATE INDEX idx_oauth_authorization_code_expires_at ON oauth_authorization_code(expires_at);
188CREATE TABLE admin_session (
189 session_id TEXT PRIMARY KEY,
190 atp_session_id TEXT NOT NULL,
191 created_at INTEGER NOT NULL DEFAULT (unixepoch())
192);
193CREATE INDEX idx_admin_session_atp_session_id ON admin_session(atp_session_id);
194CREATE TABLE label_definition (
195 val TEXT PRIMARY KEY NOT NULL,
196 description TEXT NOT NULL,
197 severity TEXT NOT NULL CHECK (severity IN ('inform', 'alert', 'takedown')),
198 created_at TEXT NOT NULL DEFAULT (datetime('now'))
199, default_visibility TEXT NOT NULL DEFAULT 'warn');
200CREATE TABLE label (
201 id INTEGER PRIMARY KEY AUTOINCREMENT,
202 src TEXT NOT NULL,
203 uri TEXT NOT NULL,
204 cid TEXT,
205 val TEXT NOT NULL,
206 neg INTEGER NOT NULL DEFAULT 0,
207 cts TEXT NOT NULL DEFAULT (datetime('now')),
208 exp TEXT,
209 FOREIGN KEY (val) REFERENCES label_definition(val)
210);
211CREATE INDEX idx_label_uri ON label(uri);
212CREATE INDEX idx_label_val ON label(val);
213CREATE INDEX idx_label_src ON label(src);
214CREATE INDEX idx_label_cts ON label(cts DESC);
215CREATE INDEX idx_label_takedown ON label(uri, val, neg);
216CREATE TABLE report (
217 id INTEGER PRIMARY KEY AUTOINCREMENT,
218 reporter_did TEXT NOT NULL,
219 subject_uri TEXT NOT NULL,
220 reason_type TEXT NOT NULL CHECK (reason_type IN ('spam', 'violation', 'misleading', 'sexual', 'rude', 'other')),
221 reason TEXT,
222 status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'resolved', 'dismissed')),
223 resolved_by TEXT,
224 resolved_at TEXT,
225 created_at TEXT NOT NULL DEFAULT (datetime('now')),
226 -- Prevent duplicate reports from same user for same content
227 UNIQUE(reporter_did, subject_uri)
228);
229CREATE INDEX idx_report_status ON report(status);
230CREATE INDEX idx_report_subject_uri ON report(subject_uri);
231CREATE INDEX idx_report_reporter_did ON report(reporter_did);
232CREATE INDEX idx_report_created_at ON report(created_at DESC);
233CREATE TABLE actor_label_preference (
234 did TEXT NOT NULL,
235 label_val TEXT NOT NULL,
236 visibility TEXT NOT NULL,
237 created_at TEXT NOT NULL DEFAULT (datetime('now')),
238 PRIMARY KEY (did, label_val)
239);
240CREATE INDEX idx_actor_label_preference_did ON actor_label_preference(did);
241-- Dbmate schema migrations
242INSERT INTO "schema_migrations" (version) VALUES
243 ('20241210000001'),
244 ('20241227000001'),
245 ('20251229000001'),
246 ('20251230000001'),
247 ('20260120000001');