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 deleted_at TEXT
21);
22
23CREATE INDEX IF NOT EXISTS idx_documents_did ON documents(did);
24
25CREATE INDEX IF NOT EXISTS idx_documents_collection ON documents(collection);
26
27CREATE INDEX IF NOT EXISTS idx_documents_record_type ON documents(record_type);
28
29CREATE INDEX IF NOT EXISTS idx_documents_repo_did ON documents(repo_did);
30
31CREATE INDEX IF NOT EXISTS idx_documents_created_at ON documents(created_at);
32
33CREATE INDEX IF NOT EXISTS idx_documents_deleted_at ON documents(deleted_at);
34
35CREATE TABLE IF NOT EXISTS sync_state (
36 consumer_name TEXT PRIMARY KEY,
37 cursor TEXT NOT NULL,
38 high_water_mark TEXT,
39 updated_at TEXT NOT NULL
40);
41
42CREATE TABLE IF NOT EXISTS document_embeddings (
43 document_id TEXT PRIMARY KEY REFERENCES documents(id),
44 embedding F32_BLOB(768),
45 embedding_model TEXT NOT NULL,
46 embedded_at TEXT NOT NULL
47);
48
49CREATE INDEX IF NOT EXISTS idx_embeddings_vec ON document_embeddings(
50 libsql_vector_idx(embedding, 'metric=cosine')
51);
52
53CREATE TABLE IF NOT EXISTS embedding_jobs (
54 document_id TEXT PRIMARY KEY REFERENCES documents(id),
55 status TEXT NOT NULL,
56 attempts INTEGER NOT NULL DEFAULT 0,
57 last_error TEXT,
58 scheduled_at TEXT NOT NULL,
59 updated_at TEXT NOT NULL
60);
61
62CREATE INDEX IF NOT EXISTS idx_embedding_jobs_status ON embedding_jobs(status);
63
64CREATE TABLE IF NOT EXISTS record_state (
65 subject_uri TEXT PRIMARY KEY,
66 state TEXT NOT NULL,
67 updated_at TEXT NOT NULL
68);