BlueSky & more on desktop
lazurite.stormlightlabs.org/
tauri
rust
typescript
bluesky
appview
atproto
solid
1CREATE TABLE IF NOT EXISTS accounts (
2 did TEXT PRIMARY KEY,
3 handle TEXT,
4 pds_url TEXT,
5 active INTEGER NOT NULL DEFAULT 0 CHECK(active IN (0, 1))
6);
7
8CREATE TABLE IF NOT EXISTS posts (
9 uri TEXT PRIMARY KEY,
10 cid TEXT NOT NULL,
11 author_did TEXT NOT NULL,
12 author_handle TEXT,
13 text TEXT,
14 created_at TEXT,
15 indexed_at TEXT DEFAULT CURRENT_TIMESTAMP,
16 json_record TEXT,
17 source TEXT NOT NULL
18);
19
20CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5(
21 text,
22 uri UNINDEXED,
23 content=posts,
24 content_rowid=rowid
25);
26
27CREATE VIRTUAL TABLE IF NOT EXISTS posts_vec USING vec0(
28 uri TEXT PRIMARY KEY,
29 embedding float[768]
30);
31
32CREATE TRIGGER IF NOT EXISTS posts_ai AFTER INSERT ON posts BEGIN
33 INSERT INTO posts_fts(rowid, text, uri) VALUES (new.rowid, new.text, new.uri);
34END;
35
36CREATE TRIGGER IF NOT EXISTS posts_ad AFTER DELETE ON posts BEGIN
37 INSERT INTO posts_fts(posts_fts, rowid, text, uri)
38 VALUES('delete', old.rowid, old.text, old.uri);
39END;
40
41CREATE TRIGGER IF NOT EXISTS posts_au AFTER UPDATE ON posts BEGIN
42 INSERT INTO posts_fts(posts_fts, rowid, text, uri)
43 VALUES('delete', old.rowid, old.text, old.uri);
44 INSERT INTO posts_fts(rowid, text, uri) VALUES (new.rowid, new.text, new.uri);
45END;