GET /xrpc/app.bsky.actor.searchActorsTypeahead
typeahead.waow.tech
1CREATE TABLE IF NOT EXISTS actors (
2 did TEXT PRIMARY KEY,
3 handle TEXT NOT NULL DEFAULT '',
4 display_name TEXT DEFAULT '',
5 avatar_url TEXT DEFAULT '', -- stores CID only (e.g. bafkrei...); reconstruct URL at query time
6 updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
7 hidden INTEGER NOT NULL DEFAULT 0,
8 labels TEXT NOT NULL DEFAULT '[]',
9 created_at TEXT DEFAULT '',
10 associated TEXT DEFAULT '{}',
11 pds TEXT DEFAULT '',
12 identity_checked_at INTEGER DEFAULT 0,
13 profile_checked_at INTEGER DEFAULT 0
14);
15
16CREATE INDEX IF NOT EXISTS idx_actors_handle ON actors(handle COLLATE NOCASE);
17CREATE INDEX IF NOT EXISTS idx_actors_hidden ON actors(hidden) WHERE hidden != 0;
18
19-- sync index: incremental sync queries filter on updated_at
20CREATE INDEX IF NOT EXISTS idx_actors_updated_at ON actors(updated_at, did);
21
22-- enrichment indexes: avoid full-table scans in cron enrichment queries
23CREATE INDEX IF NOT EXISTS idx_actors_enrich_identity ON actors(identity_checked_at) WHERE handle = '';
24CREATE INDEX IF NOT EXISTS idx_actors_enrich_pds ON actors(identity_checked_at) WHERE handle != '' AND pds = '';
25CREATE INDEX IF NOT EXISTS idx_actors_enrich_profile ON actors(profile_checked_at) WHERE handle != '' AND (avatar_url = '' OR labels = '[]' OR created_at = '');
26
27CREATE VIRTUAL TABLE IF NOT EXISTS actors_fts USING fts5(
28 handle, display_name,
29 content='actors', content_rowid='rowid',
30 tokenize='unicode61 remove_diacritics 2'
31);
32
33-- keep FTS5 in sync via triggers
34CREATE TRIGGER IF NOT EXISTS actors_ai AFTER INSERT ON actors BEGIN
35 INSERT INTO actors_fts(rowid, handle, display_name)
36 VALUES (new.rowid, new.handle, new.display_name);
37END;
38
39CREATE TRIGGER IF NOT EXISTS actors_ad AFTER DELETE ON actors BEGIN
40 INSERT INTO actors_fts(actors_fts, rowid, handle, display_name)
41 VALUES ('delete', old.rowid, old.handle, old.display_name);
42END;
43
44CREATE TRIGGER IF NOT EXISTS actors_au AFTER UPDATE ON actors
45 WHEN old.handle <> new.handle OR old.display_name <> new.display_name
46BEGIN
47 INSERT INTO actors_fts(actors_fts, rowid, handle, display_name)
48 VALUES ('delete', old.rowid, old.handle, old.display_name);
49 INSERT INTO actors_fts(rowid, handle, display_name)
50 VALUES (new.rowid, new.handle, new.display_name);
51END;
52
53CREATE TABLE IF NOT EXISTS metrics (
54 hour INTEGER PRIMARY KEY,
55 searches INTEGER NOT NULL DEFAULT 0,
56 total_ms REAL NOT NULL DEFAULT 0,
57 cache_hits INTEGER NOT NULL DEFAULT 0,
58 cache_ms REAL NOT NULL DEFAULT 0
59);
60
61CREATE TABLE IF NOT EXISTS snapshots (
62 hour INTEGER PRIMARY KEY,
63 total INTEGER NOT NULL DEFAULT 0,
64 with_handles INTEGER NOT NULL DEFAULT 0,
65 with_avatars INTEGER NOT NULL DEFAULT 0,
66 hidden INTEGER NOT NULL DEFAULT 0
67);
68
69CREATE TABLE IF NOT EXISTS actor_deltas (
70 bucket INTEGER PRIMARY KEY, -- 5-min bucket (Date.now() / 300_000)
71 actors_delta INTEGER NOT NULL DEFAULT 0,
72 handles_delta INTEGER NOT NULL DEFAULT 0,
73 avatars_delta INTEGER NOT NULL DEFAULT 0
74);
75
76CREATE TABLE IF NOT EXISTS traffic_sources (
77 domain TEXT PRIMARY KEY,
78 hits INTEGER NOT NULL DEFAULT 0
79);
80
81CREATE TABLE IF NOT EXISTS tombstones (
82 did TEXT PRIMARY KEY,
83 deleted_at INTEGER NOT NULL
84);
85
86CREATE INDEX IF NOT EXISTS idx_tombstones_deleted_at ON tombstones(deleted_at);
87
88CREATE TABLE IF NOT EXISTS mod_overrides (
89 did TEXT PRIMARY KEY,
90 action TEXT NOT NULL, -- 'show' or 'hide'
91 reason TEXT DEFAULT '',
92 created_at INTEGER NOT NULL DEFAULT (unixepoch())
93);