Something went wrong. Try again.
bluesky viewer in the terminal
Something went wrong. Try again.
1.8 kB · 48 lines
SQL
at main
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849-- Follower snapshots metadataCREATE TABLE IF NOT EXISTS follower_snapshots ( id TEXT PRIMARY KEY, created_at DATETIME NOT NULL, user_did TEXT NOT NULL, snapshot_type TEXT NOT NULL, total_count INTEGER NOT NULL, expires_at DATETIME NOT NULL);
CREATE INDEX IF NOT EXISTS idx_snapshots_user_type ON follower_snapshots(user_did, snapshot_type);CREATE INDEX IF NOT EXISTS idx_snapshots_created ON follower_snapshots(created_at);CREATE INDEX IF NOT EXISTS idx_snapshots_expires ON follower_snapshots(expires_at);
-- Snapshot entries (actors in each snapshot)CREATE TABLE IF NOT EXISTS follower_snapshot_entries ( snapshot_id TEXT NOT NULL, actor_did TEXT NOT NULL, indexed_at TEXT, PRIMARY KEY(snapshot_id, actor_did), FOREIGN KEY(snapshot_id) REFERENCES follower_snapshots(id) ON DELETE CASCADE);
CREATE INDEX IF NOT EXISTS idx_snapshot_entries_actor ON follower_snapshot_entries(actor_did);
-- Cached post rate metricsCREATE TABLE IF NOT EXISTS cached_post_rates ( actor_did TEXT PRIMARY KEY, posts_per_day REAL NOT NULL, last_post_date DATETIME, sample_size INTEGER NOT NULL, fetched_at DATETIME NOT NULL, expires_at DATETIME NOT NULL);
CREATE INDEX IF NOT EXISTS idx_post_rates_fetched ON cached_post_rates(fetched_at);CREATE INDEX IF NOT EXISTS idx_post_rates_expires ON cached_post_rates(expires_at);
-- Cached activity data (last post dates)CREATE TABLE IF NOT EXISTS cached_activity ( actor_did TEXT PRIMARY KEY, last_post_date DATETIME, fetched_at DATETIME NOT NULL, expires_at DATETIME NOT NULL);
CREATE INDEX IF NOT EXISTS idx_activity_fetched ON cached_activity(fetched_at);CREATE INDEX IF NOT EXISTS idx_activity_expires ON cached_activity(expires_at);