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