Something went wrong. Try again.
a bluesky feed generator framework
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272// Package store persists matched posts and the Jetstream cursor in SQLite.package store
import ( "context" "database/sql" "errors" "fmt" "net/url" "strings" "time"
_ "modernc.org/sqlite"
"tangled.org/edavis.dev/updraft/internal/ranking")
const schema = `CREATE TABLE IF NOT EXISTS posts ( feed TEXT NOT NULL, uri TEXT NOT NULL, cid TEXT NOT NULL, indexed_at INTEGER NOT NULL, -- Jetstream event time, unix microseconds score INTEGER NOT NULL DEFAULT 0, -- like count heat REAL NOT NULL, heat_updated_at INTEGER NOT NULL, -- unix microseconds PRIMARY KEY (feed, uri));CREATE INDEX IF NOT EXISTS posts_feed_indexed ON posts (feed, indexed_at DESC, uri DESC);CREATE INDEX IF NOT EXISTS posts_uri ON posts (uri);CREATE TABLE IF NOT EXISTS jetstream_cursor ( id INTEGER PRIMARY KEY CHECK (id = 1), seq INTEGER NOT NULL);`
// Post is a stored post as seen by one feed.type Post struct { URI string IndexedAtUS int64 Score int64 Heat float64 HeatUpdatedAtUS int64}
// Store is a SQLite-backed post store. It is safe for concurrent use.type Store struct { db *sql.DB}
// Open opens (creating if needed) the SQLite database at path and applies the schema.func Open(path string) (*Store, error) { // Immediate transactions take the write lock up front so busy_timeout applies, // instead of failing when a read-then-write transaction races another writer. query := url.Values{} query.Add("_pragma", "journal_mode(WAL)") query.Add("_pragma", "synchronous(NORMAL)") query.Add("_pragma", "busy_timeout(5000)") query.Set("_txlock", "immediate")
db, err := sql.Open("sqlite", "file:"+path+"?"+query.Encode()) if err != nil { return nil, fmt.Errorf("opening %s: %w", path, err) } if _, err := db.Exec(schema); err != nil { db.Close() return nil, fmt.Errorf("applying schema to %s: %w", path, err) } return &Store{db: db}, nil}
// Close closes the database.func (s *Store) Close() error { return s.db.Close()}
// Tx is a write transaction used to apply one batch of firehose events.type Tx struct { tx *sql.Tx}
// WithTx runs fn in a transaction, committing if fn returns nil and rolling back otherwise.func (s *Store) WithTx(ctx context.Context, fn func(*Tx) error) error { tx, err := s.db.BeginTx(ctx, nil) if err != nil { return fmt.Errorf("beginning transaction: %w", err) } if err := fn(&Tx{tx: tx}); err != nil { return errors.Join(err, tx.Rollback()) } if err := tx.Commit(); err != nil { return fmt.Errorf("committing transaction: %w", err) } return nil}
// InsertPost stores uri in feed with initial heat. Inserting a post the feed// already holds is a no-op, so replayed events are harmless.func (t *Tx) InsertPost(ctx context.Context, feed, uri, cid string, indexedAtUS int64) error { _, err := t.tx.ExecContext(ctx, ` INSERT OR IGNORE INTO posts (feed, uri, cid, indexed_at, heat, heat_updated_at) VALUES (?, ?, ?, ?, ?, ?)`, feed, uri, cid, indexedAtUS, ranking.InitialHeat, indexedAtUS) if err != nil { return fmt.Errorf("inserting %s into %s: %w", uri, feed, err) } return nil}
// DeletePost removes uri from every feed.func (t *Tx) DeletePost(ctx context.Context, uri string) error { if _, err := t.tx.ExecContext(ctx, `DELETE FROM posts WHERE uri = ?`, uri); err != nil { return fmt.Errorf("deleting %s: %w", uri, err) } return nil}
// ApplyLike records a like of uri at likedAtUS in every feed listed in// gravityByFeed (feeds that track likes, mapped to their gravity). Rows in// other feeds are left untouched.func (t *Tx) ApplyLike(ctx context.Context, uri string, likedAtUS int64, gravityByFeed map[string]float64) error { rows, err := t.tx.QueryContext(ctx, `SELECT feed, heat, heat_updated_at FROM posts WHERE uri = ?`, uri) if err != nil { return fmt.Errorf("reading heat for %s: %w", uri, err) } type update struct { feed string heat float64 updatedAt int64 } var updates []update for rows.Next() { var feed string var heat float64 var updatedAtUS int64 if err := rows.Scan(&feed, &heat, &updatedAtUS); err != nil { rows.Close() return fmt.Errorf("reading heat for %s: %w", uri, err) } gravity, tracked := gravityByFeed[feed] if !tracked { continue } updates = append(updates, update{ feed: feed, heat: ranking.AddLike(heat, microsToDuration(likedAtUS-updatedAtUS), gravity), updatedAt: max(likedAtUS, updatedAtUS), }) } if err := errors.Join(rows.Err(), rows.Close()); err != nil { return fmt.Errorf("reading heat for %s: %w", uri, err) }
for _, u := range updates { _, err := t.tx.ExecContext(ctx, ` UPDATE posts SET score = score + 1, heat = ?, heat_updated_at = ? WHERE feed = ? AND uri = ?`, u.heat, u.updatedAt, u.feed, uri) if err != nil { return fmt.Errorf("applying like to %s in %s: %w", uri, u.feed, err) } } return nil}
// SaveCursor records the Jetstream cursor to resume from.func (t *Tx) SaveCursor(ctx context.Context, seq uint64) error { _, err := t.tx.ExecContext(ctx, ` INSERT INTO jetstream_cursor (id, seq) VALUES (1, ?) ON CONFLICT (id) DO UPDATE SET seq = excluded.seq`, int64(seq)) if err != nil { return fmt.Errorf("saving cursor: %w", err) } return nil}
// Cursor returns the saved Jetstream cursor, or 0 if none has been saved.func (s *Store) Cursor(ctx context.Context) (uint64, error) { var seq int64 err := s.db.QueryRowContext(ctx, `SELECT seq FROM jetstream_cursor WHERE id = 1`).Scan(&seq) if errors.Is(err, sql.ErrNoRows) { return 0, nil } if err != nil { return 0, fmt.Errorf("reading cursor: %w", err) } return uint64(seq), nil}
// Purge deletes posts in feed indexed before olderThanUS and returns how many were deleted.func (s *Store) Purge(ctx context.Context, feed string, olderThanUS int64) (int64, error) { result, err := s.db.ExecContext(ctx, `DELETE FROM posts WHERE feed = ? AND indexed_at < ?`, feed, olderThanUS) if err != nil { return 0, fmt.Errorf("purging %s: %w", feed, err) } return result.RowsAffected()}
// PostURIs returns the distinct URIs stored in any of feeds.func (s *Store) PostURIs(ctx context.Context, feeds []string) ([]string, error) { if len(feeds) == 0 { return nil, nil } placeholders := strings.TrimSuffix(strings.Repeat("?,", len(feeds)), ",") args := make([]any, len(feeds)) for i, feed := range feeds { args[i] = feed } rows, err := s.db.QueryContext(ctx, `SELECT DISTINCT uri FROM posts WHERE feed IN (`+placeholders+`)`, args...) if err != nil { return nil, fmt.Errorf("listing post URIs: %w", err) } defer rows.Close()
var uris []string for rows.Next() { var uri string if err := rows.Scan(&uri); err != nil { return nil, fmt.Errorf("listing post URIs: %w", err) } uris = append(uris, uri) } return uris, rows.Err()}
// ChronologicalCursor marks the last post of a chronological page.type ChronologicalCursor struct { IndexedAtUS int64 URI string}
// ChronologicalPage returns up to limit posts in feed, newest first, starting// after the cursor (or from the newest post when after is nil).func (s *Store) ChronologicalPage(ctx context.Context, feed string, after *ChronologicalCursor, limit int) ([]Post, error) { query := `SELECT uri, indexed_at, score, heat, heat_updated_at FROM posts WHERE feed = ?` args := []any{feed} if after != nil { query += ` AND (indexed_at < ? OR (indexed_at = ? AND uri < ?))` args = append(args, after.IndexedAtUS, after.IndexedAtUS, after.URI) } query += ` ORDER BY indexed_at DESC, uri DESC LIMIT ?` args = append(args, limit) return s.queryPosts(ctx, query, args...)}
// FeedPosts returns every post stored in feed, in no particular order.func (s *Store) FeedPosts(ctx context.Context, feed string) ([]Post, error) { return s.queryPosts(ctx, `SELECT uri, indexed_at, score, heat, heat_updated_at FROM posts WHERE feed = ?`, feed)}
func (s *Store) queryPosts(ctx context.Context, query string, args ...any) ([]Post, error) { rows, err := s.db.QueryContext(ctx, query, args...) if err != nil { return nil, fmt.Errorf("querying posts: %w", err) } defer rows.Close()
var posts []Post for rows.Next() { var p Post if err := rows.Scan(&p.URI, &p.IndexedAtUS, &p.Score, &p.Heat, &p.HeatUpdatedAtUS); err != nil { return nil, fmt.Errorf("querying posts: %w", err) } posts = append(posts, p) } return posts, rows.Err()}
func microsToDuration(us int64) time.Duration { return time.Duration(us) * time.Microsecond}