desertthunder.dev
a love letter to tangled (android, iOS, and a search API)
1CREATE TABLE IF NOT EXISTS documents (
2 id TEXT PRIMARY KEY,
3 did TEXT NOT NULL,
4 collection TEXT NOT NULL,
5 rkey TEXT NOT NULL,
6 at_uri TEXT NOT NULL,
7 cid TEXT NOT NULL,
8 record_type TEXT NOT NULL,
9 title TEXT,
10 body TEXT,
11 summary TEXT,
12 repo_did TEXT,
13 repo_name TEXT,
14 author_handle TEXT,
15 tags_json TEXT,
16 language TEXT,
17 created_at TEXT,
18 updated_at TEXT,
19 indexed_at TEXT NOT NULL,
20 web_url TEXT DEFAULT '',
21 deleted_at TEXT,
22 search_vector TSVECTOR GENERATED ALWAYS AS (
23 setweight(to_tsvector('simple', COALESCE(title, '')), 'A') ||
24 setweight(to_tsvector('simple', COALESCE(author_handle, '')), 'A') ||
25 setweight(to_tsvector('simple', COALESCE(repo_name, '')), 'B') ||
26 setweight(to_tsvector('simple', COALESCE(summary, '')), 'B') ||
27 setweight(to_tsvector('simple', COALESCE(body, '')), 'C') ||
28 setweight(to_tsvector('simple', COALESCE(tags_json, '')), 'D')
29 ) STORED
30);
31
32CREATE INDEX IF NOT EXISTS idx_documents_did ON documents(did);
33CREATE INDEX IF NOT EXISTS idx_documents_collection ON documents(collection);
34CREATE INDEX IF NOT EXISTS idx_documents_record_type ON documents(record_type);
35CREATE INDEX IF NOT EXISTS idx_documents_repo_did ON documents(repo_did);
36CREATE INDEX IF NOT EXISTS idx_documents_created_at ON documents(created_at);
37CREATE INDEX IF NOT EXISTS idx_documents_deleted_at ON documents(deleted_at);
38CREATE INDEX IF NOT EXISTS idx_documents_search_vector ON documents USING GIN(search_vector);
39
40CREATE TABLE IF NOT EXISTS sync_state (
41 consumer_name TEXT PRIMARY KEY,
42 cursor TEXT NOT NULL,
43 high_water_mark TEXT,
44 updated_at TEXT NOT NULL
45);
46
47CREATE TABLE IF NOT EXISTS identity_handles (
48 did TEXT PRIMARY KEY,
49 handle TEXT NOT NULL,
50 is_active BOOLEAN NOT NULL DEFAULT TRUE,
51 status TEXT,
52 updated_at TEXT NOT NULL
53);
54
55CREATE INDEX IF NOT EXISTS idx_identity_handles_handle ON identity_handles(handle);
56
57CREATE TABLE IF NOT EXISTS record_state (
58 subject_uri TEXT PRIMARY KEY,
59 state TEXT NOT NULL,
60 updated_at TEXT NOT NULL
61);