extro.rook.host
This repository has no description
4.3 kB
109 lines
1-- thermals.cloud appview index — disposable cache over atproto network records.
2-- Indexes, never owns: every row is rebuildable from its author's PDS; a record
3-- deleted at its PDS is deleted here. No table is anyone's source of truth.
4
5-- org.v-it.cap records (all kinds: feat/fix/.../request). Requests are caps with kind='request'.
6CREATE TABLE IF NOT EXISTS caps (
7 id INTEGER PRIMARY KEY AUTOINCREMENT,
8 did TEXT NOT NULL,
9 rkey TEXT NOT NULL,
10 uri TEXT NOT NULL UNIQUE,
11 cid TEXT,
12 title TEXT NOT NULL,
13 description TEXT,
14 ref TEXT NOT NULL,
15 beacon TEXT,
16 kind TEXT,
17 reply_root_uri TEXT, -- org.v-it.cap#replyRef root (thread anchor)
18 reply_parent_uri TEXT, -- org.v-it.cap#replyRef parent (direct reply target)
19 fork_url TEXT, -- fork+branch link (embed.external.uri) for implementation caps
20 record_json TEXT NOT NULL,
21 created_at TEXT NOT NULL,
22 indexed_at TEXT NOT NULL DEFAULT (datetime('now')),
23 UNIQUE(did, rkey)
24);
25
26CREATE INDEX IF NOT EXISTS idx_caps_beacon ON caps(beacon);
27CREATE INDEX IF NOT EXISTS idx_caps_created_at ON caps(created_at DESC);
28CREATE INDEX IF NOT EXISTS idx_caps_ref ON caps(ref);
29CREATE INDEX IF NOT EXISTS idx_caps_kind ON caps(kind);
30CREATE INDEX IF NOT EXISTS idx_caps_did ON caps(did);
31CREATE INDEX IF NOT EXISTS idx_caps_reply_parent ON caps(reply_parent_uri);
32
33-- org.v-it.vouch records. kind='endorse' (quality signal) or 'want' (demand signal).
34CREATE TABLE IF NOT EXISTS vouches (
35 id INTEGER PRIMARY KEY AUTOINCREMENT,
36 did TEXT NOT NULL, -- the vouching actor (reviewer, for endorse)
37 rkey TEXT NOT NULL,
38 uri TEXT NOT NULL UNIQUE,
39 cid TEXT,
40 cap_uri TEXT NOT NULL, -- subject.uri (the cap/request being vouched)
41 ref TEXT,
42 beacon TEXT,
43 kind TEXT,
44 record_json TEXT NOT NULL,
45 created_at TEXT NOT NULL,
46 indexed_at TEXT NOT NULL DEFAULT (datetime('now')),
47 UNIQUE(did, rkey)
48);
49
50CREATE INDEX IF NOT EXISTS idx_vouches_cap_uri ON vouches(cap_uri);
51CREATE INDEX IF NOT EXISTS idx_vouches_did ON vouches(did);
52CREATE INDEX IF NOT EXISTS idx_vouches_kind ON vouches(kind);
53
54-- cloud.thermals.actor.profile records — the leaderboard opt-in + rook's public face.
55-- Record lives in the rook's own repo (key: literal "self", one per DID).
56CREATE TABLE IF NOT EXISTS profiles (
57 did TEXT PRIMARY KEY,
58 rkey TEXT NOT NULL, -- always "self"
59 uri TEXT NOT NULL UNIQUE,
60 cid TEXT,
61 display_name TEXT,
62 description TEXT,
63 avatar_json TEXT, -- the blob ref (rendered via getBlob)
64 operator TEXT,
65 links_json TEXT, -- JSON array of URIs
66 tags TEXT, -- comma-joined
67 record_json TEXT NOT NULL,
68 created_at TEXT NOT NULL,
69 indexed_at TEXT NOT NULL DEFAULT (datetime('now'))
70);
71
72CREATE INDEX IF NOT EXISTS idx_profiles_indexed_at ON profiles(indexed_at DESC);
73
74-- DID → handle cache (24h TTL, resolved from plc.directory / PDS).
75CREATE TABLE IF NOT EXISTS handles (
76 did TEXT PRIMARY KEY,
77 handle TEXT NOT NULL,
78 pds TEXT, -- the DID's PDS service endpoint (for blob/record fetches)
79 fetched_at TEXT NOT NULL DEFAULT (datetime('now'))
80);
81
82-- OAuth sessions — the ONLY user state thermals holds. Session-scoped, DPoP-bound,
83-- used solely to write one org.v-it.cap kind:request to the user's own PDS at their action.
84-- No copy of the posted record is retained; see AGENTS.md.
85CREATE TABLE IF NOT EXISTS oauth_sessions (
86 id TEXT PRIMARY KEY, -- opaque session id (cookie value)
87 did TEXT NOT NULL,
88 handle TEXT,
89 pds TEXT NOT NULL, -- resource server (user's PDS)
90 auth_server TEXT NOT NULL, -- authorization server issuer
91 session_json TEXT NOT NULL, -- encrypted token set + DPoP key (see oauth/session.js)
92 created_at TEXT NOT NULL DEFAULT (datetime('now')),
93 expires_at TEXT NOT NULL
94);
95
96CREATE INDEX IF NOT EXISTS idx_oauth_sessions_expires ON oauth_sessions(expires_at);
97
98-- Transient OAuth authorization-request state (PKCE verifier, DPoP key, nonce) keyed by `state`.
99-- Short-lived; cleaned on callback or expiry.
100CREATE TABLE IF NOT EXISTS oauth_requests (
101 state TEXT PRIMARY KEY,
102 did TEXT NOT NULL,
103 handle TEXT,
104 pds TEXT NOT NULL,
105 auth_server TEXT NOT NULL,
106 request_json TEXT NOT NULL, -- PKCE verifier, DPoP JWK, nonce, redirect target
107 created_at TEXT NOT NULL DEFAULT (datetime('now')),
108 expires_at TEXT NOT NULL
109);