// 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 }