atproto blogging
nonbinary.computer
1-- Valid collaborators (invite + accept pairs)
2CREATE TABLE collaborators (
3 -- Resource reference (decomposed)
4 resource_did TEXT NOT NULL,
5 resource_collection TEXT NOT NULL,
6 resource_rkey TEXT NOT NULL,
7
8 collaborator_did TEXT NOT NULL,
9
10 -- Invite record reference (decomposed)
11 invite_did TEXT NOT NULL,
12 invite_rkey TEXT NOT NULL,
13
14 -- Accept record reference (decomposed)
15 accept_did TEXT NOT NULL,
16 accept_rkey TEXT NOT NULL,
17
18 scope TEXT NOT NULL, -- 'direct' | 'inherited'
19 granted_at TEXT NOT NULL,
20 indexed_at TEXT NOT NULL,
21
22 PRIMARY KEY (resource_did, resource_collection, resource_rkey, collaborator_did)
23);
24
25CREATE INDEX idx_collaborators_did ON collaborators(collaborator_did);
26
27-- Active sessions (TTL-based, cleaned up on expiry)
28CREATE TABLE sessions (
29 -- Session record identity (decomposed)
30 did TEXT NOT NULL,
31 rkey TEXT NOT NULL,
32
33 -- Resource reference (decomposed)
34 resource_did TEXT NOT NULL,
35 resource_collection TEXT NOT NULL,
36 resource_rkey TEXT NOT NULL,
37
38 participant_did TEXT NOT NULL,
39 node_id TEXT NOT NULL,
40 relay_url TEXT, -- NULL if no relay
41 created_at TEXT NOT NULL,
42 expires_at TEXT, -- NULL = no expiry
43 indexed_at TEXT NOT NULL,
44
45 PRIMARY KEY (did, rkey)
46);
47
48CREATE INDEX idx_sessions_resource ON sessions(resource_did, resource_collection, resource_rkey);
49CREATE INDEX idx_sessions_expires ON sessions(expires_at);
50
51-- Pending invites (no accept yet)
52CREATE TABLE pending_invites (
53 -- Invite record identity (decomposed)
54 did TEXT NOT NULL, -- inviter DID
55 rkey TEXT NOT NULL,
56
57 -- Resource reference (decomposed)
58 resource_did TEXT NOT NULL,
59 resource_collection TEXT NOT NULL,
60 resource_rkey TEXT NOT NULL,
61
62 inviter_did TEXT NOT NULL, -- same as did
63 invitee_did TEXT NOT NULL,
64 message TEXT, -- NULL if no message
65 expires_at TEXT, -- NULL = no expiry
66 created_at TEXT NOT NULL,
67 indexed_at TEXT NOT NULL,
68
69 PRIMARY KEY (did, rkey)
70);
71
72CREATE INDEX idx_pending_invites_resource ON pending_invites(resource_did, resource_collection, resource_rkey);
73CREATE INDEX idx_pending_invites_invitee ON pending_invites(invitee_did);