A fullstack app for indexing standard.site documents
stevedylan.dev
1-- Records synced from external tap instance
2CREATE TABLE IF NOT EXISTS repo_records (
3 id INTEGER PRIMARY KEY AUTOINCREMENT,
4 did TEXT NOT NULL,
5 rkey TEXT NOT NULL,
6 collection TEXT NOT NULL,
7 cid TEXT,
8 synced_at TEXT DEFAULT (datetime('now')),
9 UNIQUE(did, collection, rkey)
10);
11
12CREATE INDEX IF NOT EXISTS idx_repo_records_collection ON repo_records(collection);
13CREATE INDEX IF NOT EXISTS idx_repo_records_did ON repo_records(did);
14CREATE INDEX IF NOT EXISTS idx_repo_records_rkey ON repo_records(rkey DESC);
15
16-- Cache for resolved PDS endpoints
17CREATE TABLE IF NOT EXISTS pds_cache (
18 did TEXT PRIMARY KEY,
19 pds_endpoint TEXT NOT NULL,
20 cached_at TEXT DEFAULT (datetime('now'))
21);
22
23-- Sync metadata to track last sync
24CREATE TABLE IF NOT EXISTS sync_metadata (
25 key TEXT PRIMARY KEY,
26 value TEXT NOT NULL,
27 updated_at TEXT DEFAULT (datetime('now'))
28);
29
30-- Pre-resolved documents for fast feed serving
31CREATE TABLE IF NOT EXISTS resolved_documents (
32 uri TEXT PRIMARY KEY,
33 did TEXT NOT NULL,
34 rkey TEXT NOT NULL,
35 -- Document fields
36 title TEXT,
37 description TEXT,
38 path TEXT,
39 site TEXT,
40 content TEXT, -- JSON blob for content union
41 text_content TEXT,
42 cover_image_cid TEXT, -- CID for cover image blob
43 cover_image_url TEXT, -- Full URL: {pds}/xrpc/com.atproto.sync.getBlob?did={did}&cid={cid}
44 bsky_post_ref TEXT, -- JSON blob for strong reference {uri, cid}
45 tags TEXT, -- JSON array of strings
46 published_at TEXT,
47 updated_at TEXT,
48 -- Publication fields (resolved from site at:// URI)
49 pub_url TEXT, -- Publication base URL
50 pub_name TEXT,
51 pub_description TEXT,
52 pub_icon_cid TEXT, -- CID for publication icon blob
53 pub_icon_url TEXT, -- Full URL to publication icon
54 -- Metadata
55 view_url TEXT, -- Constructed canonical URL (pub_url + path)
56 pds_endpoint TEXT, -- Cached PDS endpoint for this DID
57 resolved_at TEXT DEFAULT (datetime('now')),
58 stale_at TEXT, -- When this record should be re-resolved
59 verified INTEGER DEFAULT 0 -- Whether the record has been verified via .well-known or link tag
60);
61
62CREATE INDEX IF NOT EXISTS idx_resolved_documents_rkey ON resolved_documents(rkey DESC);
63CREATE INDEX IF NOT EXISTS idx_resolved_documents_stale ON resolved_documents(stale_at);
64CREATE INDEX IF NOT EXISTS idx_resolved_documents_pub_url ON resolved_documents(pub_url);
65CREATE INDEX IF NOT EXISTS idx_resolved_documents_verified ON resolved_documents(verified);