utils/dbuser.ts at main · whey.party/skylite

whey.party / skylite
an attempt to make a lightweight, easily self-hostable, scoped bluesky appview
skylite / utils / dbuser.ts
at main 6.8 kB view raw
  1import { Database } from "jsr:@db/sqlite@0.11";
  2
  3export function setupUserDb(db: Database) {
  4
  5  const baseColumns = `
  6      uri TEXT PRIMARY KEY NOT NULL,
  7      did TEXT NOT NULL,
  8      cid TEXT,
  9      rev TEXT,
 10      createdat INTEGER,
 11      indexedat INTEGER NOT NULL,
 12      json TEXT
 13  `;
 14  
 15  const innerArrayID = "id INTEGER PRIMARY KEY AUTOINCREMENT";
 16  const createTableINE = "CREATE TABLE IF NOT EXISTS";
 17  const createIndexINE = "CREATE INDEX IF NOT EXISTS";
 18  
 19  db.exec(`
 20    -- User's own personal preferences
 21    ${createTableINE} prefs (
 22      did TEXT PRIMARY KEY NOT NULL,
 23      json TEXT
 24    );
 25    ${createIndexINE} idx_prefs_did ON prefs(did);
 26
 27    -- The user's own profile record
 28    ${createTableINE} app_bsky_actor_profile (
 29      ${baseColumns},
 30      displayname TEXT,
 31      description TEXT,
 32      avatarcid TEXT,
 33      avatarmime TEXT,
 34      bannercid TEXT,
 35      bannermime TEXT,
 36      pinned TEXT
 37    );
 38    ${createIndexINE} idx_actor_profile_did ON app_bsky_actor_profile(did);
 39  
 40    -- All posts created by the user
 41    ${createTableINE} app_bsky_feed_post (
 42      ${baseColumns},
 43      text TEXT,
 44      replyroot TEXT,
 45      replyparent TEXT,
 46      quote TEXT,
 47      imagecount INTEGER,
 48      image1cid TEXT,
 49      image1mime TEXT,
 50      image1aspect TEXT,
 51      image2cid TEXT,
 52      image2mime TEXT,
 53      image2aspect TEXT,
 54      image3cid TEXT,
 55      image3mime TEXT,
 56      image3aspect TEXT,
 57      image4cid TEXT,
 58      image4mime TEXT,
 59      image4aspect TEXT,
 60      videocount INTEGER,
 61      videocid TEXT,
 62      videomime TEXT,
 63      videoaspect TEXT
 64    );
 65    ${createIndexINE} idx_post_author_timeline ON app_bsky_feed_post(did, createdat DESC);
 66    ${createIndexINE} idx_post_global_timeline ON app_bsky_feed_post(indexedat DESC);
 67    ${createIndexINE} idx_post_replyroot ON app_bsky_feed_post(replyroot);
 68    ${createIndexINE} idx_post_replyparent ON app_bsky_feed_post(replyparent);
 69    ${createIndexINE} idx_post_quote ON app_bsky_feed_post(quote);
 70  
 71    -- Data related to the user's posts
 72    ${createTableINE} app_bsky_feed_post_langs ( ${innerArrayID}, post TEXT NOT NULL, lang TEXT NOT NULL );
 73    ${createIndexINE} idx_post_langs_lang ON app_bsky_feed_post_langs(lang);
 74    ${createIndexINE} idx_post_langs_post ON app_bsky_feed_post_langs(post);
 75  
 76    ${createTableINE} app_bsky_feed_post_hashtags ( ${innerArrayID}, post TEXT NOT NULL, hashtag TEXT NOT NULL );
 77    ${createIndexINE} idx_post_hashtags_hashtag ON app_bsky_feed_post_hashtags(hashtag);
 78    ${createIndexINE} idx_post_hashtags_post ON app_bsky_feed_post_hashtags(post);
 79  
 80    ${createTableINE} app_bsky_feed_post_mentions ( ${innerArrayID}, post TEXT NOT NULL, mention TEXT NOT NULL );
 81    ${createIndexINE} idx_post_mentions_mention ON app_bsky_feed_post_mentions(mention);
 82    ${createIndexINE} idx_post_mentions_post ON app_bsky_feed_post_mentions(post);
 83
 84    -- All likes created by the user
 85    ${createTableINE} app_bsky_feed_like ( ${baseColumns}, subject TEXT NOT NULL, subjectcid TEXT );
 86    ${createIndexINE} idx_like_author_timeline ON app_bsky_feed_like(did, createdat DESC);
 87    ${createIndexINE} idx_like_subject ON app_bsky_feed_like(subject);
 88  
 89    -- All reposts created by the user
 90    ${createTableINE} app_bsky_feed_repost ( ${baseColumns}, subject TEXT NOT NULL, subjectcid TEXT );
 91    ${createIndexINE} idx_repost_author_timeline ON app_bsky_feed_repost(did, createdat DESC);
 92    ${createIndexINE} idx_repost_subject ON app_bsky_feed_repost(subject);
 93
 94    -- All follows created by the user
 95    ${createTableINE} app_bsky_graph_follow ( ${baseColumns}, subject TEXT NOT NULL );
 96    ${createIndexINE} idx_follow_author_timeline ON app_bsky_graph_follow(did, createdat DESC);
 97    ${createIndexINE} idx_follow_subject_timeline ON app_bsky_graph_follow(subject, createdat DESC);
 98  
 99    -- All blocks created by the user
100    ${createTableINE} app_bsky_graph_block ( ${baseColumns}, subject TEXT NOT NULL );
101    ${createIndexINE} idx_block_author ON app_bsky_graph_block(did);
102    ${createIndexINE} idx_block_subject ON app_bsky_graph_block(subject);
103  
104    -- All lists created by the user
105    ${createTableINE} app_bsky_graph_list ( ${baseColumns}, name TEXT, description TEXT, purpose TEXT, avatarcid TEXT, avatarmime TEXT );
106    ${createIndexINE} idx_list_author ON app_bsky_graph_list(did);
107    ${createIndexINE} idx_list_purpose ON app_bsky_graph_list(purpose);
108  
109    -- All list items created by the user
110    ${createTableINE} app_bsky_graph_listitem ( ${baseColumns}, list TEXT NOT NULL, subject TEXT NOT NULL );
111    ${createIndexINE} idx_listitem_list ON app_bsky_graph_listitem(list);
112    ${createIndexINE} idx_listitem_subject ON app_bsky_graph_listitem(subject);
113    ${createIndexINE} idx_listitem_author ON app_bsky_graph_listitem(did);
114  
115    -- All feed generators created by the user
116    ${createTableINE} app_bsky_feed_generator ( ${baseColumns}, displayname TEXT, description TEXT, avatarcid TEXT, avatarmime TEXT );
117    ${createIndexINE} idx_feed_generator_author ON app_bsky_feed_generator(did);
118  
119    -- All threadgates created by the user
120    ${createTableINE} app_bsky_feed_threadgate ( ${baseColumns}, post TEXT NOT NULL );
121    ${createIndexINE} idx_threadgate_post ON app_bsky_feed_threadgate(post);
122    ${createIndexINE} idx_threadgate_author ON app_bsky_feed_threadgate(did);
123
124    ${createTableINE} app_bsky_feed_threadgate_hiddenreplies ( ${innerArrayID}, threadgate TEXT NOT NULL, reply TEXT NOT NULL );
125    ${createIndexINE} idx_threadgate_hiddenreplies_gate ON app_bsky_feed_threadgate_hiddenreplies(threadgate);
126  
127    -- All list blocks created by the user
128    ${createTableINE} app_bsky_graph_listblock ( ${baseColumns}, subject TEXT NOT NULL );
129    ${createIndexINE} idx_listblock_subject ON app_bsky_graph_listblock(subject);
130    ${createIndexINE} idx_listblock_author ON app_bsky_graph_listblock(did);
131  
132    -- User's notification settings declaration
133    ${createTableINE} app_bsky_notification_declaration ( ${baseColumns}, allowSubscriptions TEXT );
134    ${createIndexINE} idx_notification_declaration_author ON app_bsky_notification_declaration(did);
135
136    -- A global index for relationships between any two pieces of content
137    ${createTableINE} backlink_skeleton (
138      id INTEGER PRIMARY KEY AUTOINCREMENT,
139      srcuri TEXT,
140      srcdid TEXT,
141      srcfield TEXT,
142      srccol TEXT,
143      suburi TEXT,
144      subdid TEXT,
145      subcol TEXT,
146      indexedAt INTEGER NOT NULL
147    );
148    ${createIndexINE} idx_backlink_subdid_mod ON backlink_skeleton(subdid, srcdid);
149    ${createIndexINE} idx_backlink_suburi_mod ON backlink_skeleton(suburi, srcdid);
150    ${createIndexINE} idx_backlink_subdid_filter_mod ON backlink_skeleton(subdid, srccol, srcdid);
151    ${createIndexINE} idx_backlink_suburi_filter_mod ON backlink_skeleton(suburi, srccol, srcdid);
152  `);
153}