slices.network
Auto-indexing service and GraphQL API for AT Protocol Records
quickslice.slices.network/
atproto
gleam
graphql
chadtmiller.com
1-- migrate:up
2
3-- =============================================================================
4-- Core Tables
5-- =============================================================================
6
7-- Record table for AT Protocol records
8CREATE TABLE IF NOT EXISTS record (
9 uri TEXT PRIMARY KEY NOT NULL,
10 cid TEXT NOT NULL,
11 did TEXT NOT NULL,
12 collection TEXT NOT NULL,
13 json TEXT NOT NULL,
14 indexed_at TEXT NOT NULL DEFAULT (datetime('now'))
15);
16
17CREATE INDEX IF NOT EXISTS idx_record_did ON record(did);
18CREATE INDEX IF NOT EXISTS idx_record_collection ON record(collection);
19CREATE INDEX IF NOT EXISTS idx_record_did_collection ON record(did, collection);
20CREATE INDEX IF NOT EXISTS idx_record_indexed_at ON record(indexed_at DESC);
21CREATE INDEX IF NOT EXISTS idx_record_cid ON record(cid);
22
23-- Actor table for AT Protocol actors (users)
24CREATE TABLE IF NOT EXISTS actor (
25 did TEXT PRIMARY KEY NOT NULL,
26 handle TEXT,
27 indexed_at TEXT NOT NULL
28);
29
30CREATE INDEX IF NOT EXISTS idx_actor_handle ON actor(handle);
31CREATE INDEX IF NOT EXISTS idx_actor_indexed_at ON actor(indexed_at DESC);
32
33-- Lexicon table for schema definitions
34CREATE TABLE IF NOT EXISTS lexicon (
35 id TEXT PRIMARY KEY NOT NULL,
36 json TEXT NOT NULL,
37 created_at TEXT NOT NULL DEFAULT (datetime('now'))
38);
39
40CREATE INDEX IF NOT EXISTS idx_lexicon_created_at ON lexicon(created_at DESC);
41
42-- Config table for application settings
43CREATE TABLE IF NOT EXISTS config (
44 key TEXT PRIMARY KEY NOT NULL,
45 value TEXT NOT NULL,
46 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
47);
48
49-- =============================================================================
50-- Jetstream Tables
51-- =============================================================================
52
53-- Jetstream activity log for 24h activity tracking
54CREATE TABLE IF NOT EXISTS jetstream_activity (
55 id INTEGER PRIMARY KEY AUTOINCREMENT,
56 timestamp TEXT NOT NULL,
57 operation TEXT NOT NULL,
58 collection TEXT NOT NULL,
59 did TEXT NOT NULL,
60 status TEXT NOT NULL,
61 error_message TEXT,
62 event_json TEXT NOT NULL
63);
64
65CREATE INDEX IF NOT EXISTS idx_jetstream_activity_timestamp ON jetstream_activity(timestamp DESC);
66
67-- Jetstream cursor for tracking stream position
68CREATE TABLE IF NOT EXISTS jetstream_cursor (
69 id INTEGER PRIMARY KEY CHECK (id = 1),
70 cursor INTEGER NOT NULL,
71 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
72);
73
74-- =============================================================================
75-- OAuth Tables
76-- =============================================================================
77
78-- OAuth clients (registered applications)
79CREATE TABLE IF NOT EXISTS oauth_client (
80 client_id TEXT PRIMARY KEY,
81 client_secret TEXT,
82 client_name TEXT NOT NULL,
83 redirect_uris TEXT NOT NULL,
84 grant_types TEXT NOT NULL,
85 response_types TEXT NOT NULL,
86 scope TEXT,
87 token_endpoint_auth_method TEXT NOT NULL,
88 client_type TEXT NOT NULL,
89 created_at INTEGER NOT NULL,
90 updated_at INTEGER NOT NULL,
91 metadata TEXT NOT NULL DEFAULT '{}',
92 access_token_expiration INTEGER NOT NULL DEFAULT 86400,
93 refresh_token_expiration INTEGER NOT NULL DEFAULT 1209600,
94 require_redirect_exact INTEGER NOT NULL DEFAULT 1,
95 registration_access_token TEXT,
96 jwks TEXT
97);
98
99-- OAuth access tokens
100CREATE TABLE IF NOT EXISTS oauth_access_token (
101 token TEXT PRIMARY KEY,
102 token_type TEXT NOT NULL DEFAULT 'Bearer',
103 client_id TEXT NOT NULL,
104 user_id TEXT,
105 session_id TEXT,
106 session_iteration INTEGER,
107 scope TEXT,
108 created_at INTEGER NOT NULL,
109 expires_at INTEGER NOT NULL,
110 revoked INTEGER NOT NULL DEFAULT 0,
111 dpop_jkt TEXT,
112 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
113);
114
115CREATE INDEX IF NOT EXISTS idx_oauth_access_token_expires_at ON oauth_access_token(expires_at);
116CREATE INDEX IF NOT EXISTS idx_oauth_access_token_client_id ON oauth_access_token(client_id);
117CREATE INDEX IF NOT EXISTS idx_oauth_access_token_user_id ON oauth_access_token(user_id);
118CREATE INDEX IF NOT EXISTS idx_oauth_access_token_dpop_jkt ON oauth_access_token(dpop_jkt);
119
120-- OAuth refresh tokens
121CREATE TABLE IF NOT EXISTS oauth_refresh_token (
122 token TEXT PRIMARY KEY,
123 access_token TEXT NOT NULL,
124 client_id TEXT NOT NULL,
125 user_id TEXT NOT NULL,
126 session_id TEXT,
127 session_iteration INTEGER,
128 scope TEXT,
129 created_at INTEGER NOT NULL,
130 expires_at INTEGER,
131 revoked INTEGER NOT NULL DEFAULT 0,
132 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
133);
134
135CREATE INDEX IF NOT EXISTS idx_oauth_refresh_token_expires_at ON oauth_refresh_token(expires_at);
136CREATE INDEX IF NOT EXISTS idx_oauth_refresh_token_client_id ON oauth_refresh_token(client_id);
137
138-- OAuth Pushed Authorization Requests (PAR)
139CREATE TABLE IF NOT EXISTS oauth_par_request (
140 request_uri TEXT PRIMARY KEY,
141 authorization_request TEXT NOT NULL,
142 client_id TEXT NOT NULL,
143 created_at INTEGER NOT NULL,
144 expires_at INTEGER NOT NULL,
145 subject TEXT,
146 metadata TEXT NOT NULL DEFAULT '{}',
147 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
148);
149
150CREATE INDEX IF NOT EXISTS idx_oauth_par_request_expires_at ON oauth_par_request(expires_at);
151
152-- OAuth DPoP nonces
153CREATE TABLE IF NOT EXISTS oauth_dpop_nonce (
154 nonce TEXT PRIMARY KEY,
155 expires_at INTEGER NOT NULL
156);
157
158CREATE INDEX IF NOT EXISTS idx_oauth_dpop_nonce_expires_at ON oauth_dpop_nonce(expires_at);
159
160-- OAuth DPoP JTI replay protection
161CREATE TABLE IF NOT EXISTS oauth_dpop_jti (
162 jti TEXT PRIMARY KEY,
163 created_at INTEGER NOT NULL
164);
165
166CREATE INDEX IF NOT EXISTS idx_oauth_dpop_jti_created_at ON oauth_dpop_jti(created_at);
167
168-- OAuth authorization requests (client flow state)
169CREATE TABLE IF NOT EXISTS oauth_auth_request (
170 session_id TEXT PRIMARY KEY,
171 client_id TEXT NOT NULL,
172 redirect_uri TEXT NOT NULL,
173 scope TEXT,
174 state TEXT,
175 code_challenge TEXT,
176 code_challenge_method TEXT,
177 response_type TEXT NOT NULL,
178 nonce TEXT,
179 login_hint TEXT,
180 created_at INTEGER NOT NULL,
181 expires_at INTEGER NOT NULL,
182 FOREIGN KEY (client_id) REFERENCES oauth_client(client_id) ON DELETE CASCADE
183);
184
185CREATE INDEX IF NOT EXISTS idx_oauth_auth_request_expires_at ON oauth_auth_request(expires_at);
186CREATE INDEX IF NOT EXISTS idx_oauth_auth_request_client_id ON oauth_auth_request(client_id);
187
188-- OAuth ATP session (bridge sessions to AT Protocol)
189CREATE TABLE IF NOT EXISTS oauth_atp_session (
190 session_id TEXT NOT NULL,
191 iteration INTEGER NOT NULL,
192 did TEXT,
193 session_created_at INTEGER NOT NULL,
194 atp_oauth_state TEXT NOT NULL,
195 signing_key_jkt TEXT NOT NULL,
196 dpop_key TEXT NOT NULL,
197 access_token TEXT,
198 refresh_token TEXT,
199 access_token_created_at INTEGER,
200 access_token_expires_at INTEGER,
201 access_token_scopes TEXT,
202 session_exchanged_at INTEGER,
203 exchange_error TEXT,
204 PRIMARY KEY (session_id, iteration)
205);
206
207CREATE INDEX IF NOT EXISTS idx_oauth_atp_session_did ON oauth_atp_session(did);
208CREATE INDEX IF NOT EXISTS idx_oauth_atp_session_access_token ON oauth_atp_session(access_token);
209
210-- OAuth ATP requests (outbound OAuth to AT Protocol)
211CREATE TABLE IF NOT EXISTS oauth_atp_request (
212 oauth_state TEXT PRIMARY KEY,
213 authorization_server TEXT NOT NULL,
214 nonce TEXT NOT NULL,
215 pkce_verifier TEXT NOT NULL,
216 signing_public_key TEXT NOT NULL,
217 dpop_private_key TEXT NOT NULL,
218 created_at INTEGER NOT NULL,
219 expires_at INTEGER NOT NULL
220);
221
222CREATE INDEX IF NOT EXISTS idx_oauth_atp_request_expires_at ON oauth_atp_request(expires_at);
223
224-- OAuth authorization codes
225CREATE TABLE IF NOT EXISTS oauth_authorization_code (
226 code TEXT PRIMARY KEY,
227 client_id TEXT NOT NULL,
228 user_id TEXT NOT NULL,
229 session_id TEXT,
230 session_iteration INTEGER,
231 redirect_uri TEXT NOT NULL,
232 scope TEXT,
233 code_challenge TEXT,
234 code_challenge_method TEXT,
235 nonce TEXT,
236 created_at INTEGER NOT NULL,
237 expires_at INTEGER NOT NULL,
238 used INTEGER NOT NULL DEFAULT 0
239);
240
241CREATE INDEX IF NOT EXISTS idx_oauth_authorization_code_expires_at ON oauth_authorization_code(expires_at);
242
243-- =============================================================================
244-- Admin Tables
245-- =============================================================================
246
247-- Admin browser sessions
248CREATE TABLE IF NOT EXISTS admin_session (
249 session_id TEXT PRIMARY KEY,
250 atp_session_id TEXT NOT NULL,
251 created_at INTEGER NOT NULL DEFAULT (unixepoch())
252);
253
254CREATE INDEX IF NOT EXISTS idx_admin_session_atp_session_id ON admin_session(atp_session_id);
255
256-- migrate:down
257
258DROP TABLE IF EXISTS admin_session;
259DROP TABLE IF EXISTS oauth_authorization_code;
260DROP TABLE IF EXISTS oauth_atp_request;
261DROP TABLE IF EXISTS oauth_atp_session;
262DROP TABLE IF EXISTS oauth_auth_request;
263DROP TABLE IF EXISTS oauth_dpop_jti;
264DROP TABLE IF EXISTS oauth_dpop_nonce;
265DROP TABLE IF EXISTS oauth_par_request;
266DROP TABLE IF EXISTS oauth_refresh_token;
267DROP TABLE IF EXISTS oauth_access_token;
268DROP TABLE IF EXISTS oauth_client;
269DROP TABLE IF EXISTS jetstream_cursor;
270DROP TABLE IF EXISTS jetstream_activity;
271DROP TABLE IF EXISTS config;
272DROP TABLE IF EXISTS lexicon;
273DROP TABLE IF EXISTS actor;
274DROP TABLE IF EXISTS record;