desertthunder.dev
search and/or read your saved and liked bluesky posts
wails
go
svelte
sqlite
desktop
bluesky
1-- Combined initial schema for bsky-browser-desktop
2-- Includes all migrations: auth, posts, FTS5, OAuth fields, and facets
3
4-- Auth table with all OAuth fields
5CREATE TABLE IF NOT EXISTS auth (
6 did TEXT PRIMARY KEY,
7 handle TEXT NOT NULL,
8 access_jwt TEXT NOT NULL,
9 refresh_jwt TEXT NOT NULL,
10 pds_url TEXT NOT NULL,
11 session_id TEXT,
12 auth_server_url TEXT,
13 auth_server_token_endpoint TEXT,
14 auth_server_revocation_endpoint TEXT,
15 dpop_auth_nonce TEXT,
16 dpop_host_nonce TEXT,
17 dpop_private_key TEXT,
18 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
19);
20
21-- Posts table with facets support
22CREATE TABLE IF NOT EXISTS posts (
23 uri TEXT PRIMARY KEY,
24 cid TEXT NOT NULL,
25 author_did TEXT NOT NULL,
26 author_handle TEXT NOT NULL,
27 text TEXT NOT NULL DEFAULT '',
28 created_at DATETIME NOT NULL,
29 like_count INTEGER DEFAULT 0,
30 repost_count INTEGER DEFAULT 0,
31 reply_count INTEGER DEFAULT 0,
32 source TEXT NOT NULL CHECK(source IN ('saved', 'liked')),
33 facets TEXT,
34 indexed_at DATETIME DEFAULT CURRENT_TIMESTAMP
35);
36
37-- FTS5 virtual table for full-text search
38CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5(
39 text,
40 author_handle,
41 content='posts',
42 content_rowid='rowid',
43 tokenize='unicode61'
44);
45
46-- Triggers to keep FTS5 index in sync with posts table
47CREATE TRIGGER IF NOT EXISTS posts_ai AFTER INSERT ON posts BEGIN
48 INSERT INTO posts_fts(rowid, text, author_handle)
49 VALUES (new.rowid, new.text, new.author_handle);
50END;
51
52CREATE TRIGGER IF NOT EXISTS posts_ad AFTER DELETE ON posts BEGIN
53 INSERT INTO posts_fts(posts_fts, rowid, text, author_handle)
54 VALUES ('delete', old.rowid, old.text, old.author_handle);
55END;
56
57CREATE TRIGGER IF NOT EXISTS posts_au AFTER UPDATE ON posts BEGIN
58 INSERT INTO posts_fts(posts_fts, rowid, text, author_handle)
59 VALUES ('delete', old.rowid, old.text, old.author_handle);
60 INSERT INTO posts_fts(rowid, text, author_handle)
61 VALUES (new.rowid, new.text, new.author_handle);
62END;