an attempt to make a lightweight, easily self-hostable, scoped bluesky appview
whey.party
1import { Database } from "jsr:@db/sqlite@0.11";
2
3export function setupSystemDb(db: Database) {
4 const createTableINE = "CREATE TABLE IF NOT EXISTS";
5 const createIndexINE = "CREATE INDEX IF NOT EXISTS";
6
7 db.exec(`
8 -- Master list of all users known to the system
9 ${createTableINE} users (
10 did TEXT PRIMARY KEY NOT NULL,
11 role TEXT,
12 registrationdate TEXT,
13 onboardingstatus TEXT
14 );
15
16 -- Cache of profiles for *other* users, prevents storing this in every user's DB
17 ${createTableINE} remoteprofileview (
18 did TEXT PRIMARY KEY NOT NULL,
19 handle TEXT,
20 displayname TEXT,
21 description TEXT,
22 avatar TEXT,
23 banner TEXT
24 );
25
26 -- Central DID document cache and handle resolution
27 ${createTableINE} did (
28 did TEXT PRIMARY KEY NOT NULL,
29 method TEXT,
30 string TEXT,
31 doc TEXT,
32 pds TEXT,
33 handle TEXT
34 );
35 ${createIndexINE} idx_did_handle ON did(handle);
36 `);
37}