bluesky viewer in the terminal
desertthunder.dev
1-- Follower snapshots metadata
2CREATE TABLE IF NOT EXISTS follower_snapshots (
3 id TEXT PRIMARY KEY,
4 created_at DATETIME NOT NULL,
5 user_did TEXT NOT NULL,
6 snapshot_type TEXT NOT NULL,
7 total_count INTEGER NOT NULL,
8 expires_at DATETIME NOT NULL
9);
10
11CREATE INDEX IF NOT EXISTS idx_snapshots_user_type ON follower_snapshots(user_did, snapshot_type);
12CREATE INDEX IF NOT EXISTS idx_snapshots_created ON follower_snapshots(created_at);
13CREATE INDEX IF NOT EXISTS idx_snapshots_expires ON follower_snapshots(expires_at);
14
15-- Snapshot entries (actors in each snapshot)
16CREATE TABLE IF NOT EXISTS follower_snapshot_entries (
17 snapshot_id TEXT NOT NULL,
18 actor_did TEXT NOT NULL,
19 indexed_at TEXT,
20 PRIMARY KEY(snapshot_id, actor_did),
21 FOREIGN KEY(snapshot_id) REFERENCES follower_snapshots(id) ON DELETE CASCADE
22);
23
24CREATE INDEX IF NOT EXISTS idx_snapshot_entries_actor ON follower_snapshot_entries(actor_did);
25
26-- Cached post rate metrics
27CREATE TABLE IF NOT EXISTS cached_post_rates (
28 actor_did TEXT PRIMARY KEY,
29 posts_per_day REAL NOT NULL,
30 last_post_date DATETIME,
31 sample_size INTEGER NOT NULL,
32 fetched_at DATETIME NOT NULL,
33 expires_at DATETIME NOT NULL
34);
35
36CREATE INDEX IF NOT EXISTS idx_post_rates_fetched ON cached_post_rates(fetched_at);
37CREATE INDEX IF NOT EXISTS idx_post_rates_expires ON cached_post_rates(expires_at);
38
39-- Cached activity data (last post dates)
40CREATE TABLE IF NOT EXISTS cached_activity (
41 actor_did TEXT PRIMARY KEY,
42 last_post_date DATETIME,
43 fetched_at DATETIME NOT NULL,
44 expires_at DATETIME NOT NULL
45);
46
47CREATE INDEX IF NOT EXISTS idx_activity_fetched ON cached_activity(fetched_at);
48CREATE INDEX IF NOT EXISTS idx_activity_expires ON cached_activity(expires_at);