zzstoatzz.io
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
19CREATE VIRTUAL TABLE IF NOT EXISTS actors_fts USING fts5(
20 handle, display_name,
21 content='actors', content_rowid='rowid',
22 tokenize='unicode61 remove_diacritics 2'
23);
24
25-- keep FTS5 in sync via triggers
26CREATE TRIGGER IF NOT EXISTS actors_ai AFTER INSERT ON actors BEGIN
27 INSERT INTO actors_fts(rowid, handle, display_name)
28 VALUES (new.rowid, new.handle, new.display_name);
29END;
30
31CREATE TRIGGER IF NOT EXISTS actors_ad AFTER DELETE ON actors BEGIN
32 INSERT INTO actors_fts(actors_fts, rowid, handle, display_name)
33 VALUES ('delete', old.rowid, old.handle, old.display_name);
34END;
35
36CREATE TRIGGER IF NOT EXISTS actors_au AFTER UPDATE ON actors
37 WHEN old.handle <> new.handle OR old.display_name <> new.display_name
38BEGIN
39 INSERT INTO actors_fts(actors_fts, rowid, handle, display_name)
40 VALUES ('delete', old.rowid, old.handle, old.display_name);
41 INSERT INTO actors_fts(rowid, handle, display_name)
42 VALUES (new.rowid, new.handle, new.display_name);
43END;
44
45CREATE TABLE IF NOT EXISTS metrics (
46 hour INTEGER PRIMARY KEY,
47 searches INTEGER NOT NULL DEFAULT 0,
48 total_ms REAL NOT NULL DEFAULT 0,
49 cache_hits INTEGER NOT NULL DEFAULT 0,
50 cache_ms REAL NOT NULL DEFAULT 0
51);
52
53CREATE TABLE IF NOT EXISTS snapshots (
54 hour INTEGER PRIMARY KEY,
55 total INTEGER NOT NULL DEFAULT 0,
56 with_handles INTEGER NOT NULL DEFAULT 0,
57 with_avatars INTEGER NOT NULL DEFAULT 0,
58 hidden INTEGER NOT NULL DEFAULT 0
59);
60
61CREATE TABLE IF NOT EXISTS actor_deltas (
62 bucket INTEGER PRIMARY KEY, -- 5-min bucket (Date.now() / 300_000)
63 actors_delta INTEGER NOT NULL DEFAULT 0,
64 handles_delta INTEGER NOT NULL DEFAULT 0,
65 avatars_delta INTEGER NOT NULL DEFAULT 0
66);
67
68CREATE TABLE IF NOT EXISTS traffic_sources (
69 domain TEXT PRIMARY KEY,
70 hits INTEGER NOT NULL DEFAULT 0
71);
72
73CREATE TABLE IF NOT EXISTS tombstones (
74 did TEXT PRIMARY KEY,
75 deleted_at INTEGER NOT NULL
76);
77
78CREATE TABLE IF NOT EXISTS mod_overrides (
79 did TEXT PRIMARY KEY,
80 action TEXT NOT NULL, -- 'show' or 'hide'
81 reason TEXT DEFAULT '',
82 created_at INTEGER NOT NULL DEFAULT (unixepoch())
83);