Every Bluesky post, counted by language.
A sqlflow pipeline reads the public Bluesky firehose, counts posts per minute by language in a tumbling window, and writes each closed minute to Postgres. sqlflow serve defines and serves the analytic queries that power this page over HTTP. The page is live: nothing is static or staged. Every chart queries the full stack. The sqlflow pipeline and the sqlflow analytics server run in under 256 MB of memory together, beside a 256 MB Postgres.
websocket://jetstream-->SELECT bucket, lang, count(*) ... TUMBLING (1 minute)-->postgres://posts_per_minute_by_lang
Posts per minute by language
The six languages with the most posts in the range keep their own line. The rest sum into one. Click a language to hide or show it.
| Language | Posts | Share | Share, as a bar |
|---|
A post counts under its first language tag; a post with no tag counts as untagged. A gap in a line is a minute the pipeline was not running. Nothing is backfilled: a restart loses the open minute and rejoins the firehose at the live head.
How it works
One worker, one Postgres, one API.
The pipeline is a YAML file with SQL in it. So is the API. Both run from one image on 512 MB Render instances beside the database, and the page has no backend of its own.
01Read the firehose
sqlflow opens a websocket to Jetstream, filtered to post records. Every 500 messages form a batch, loaded into DuckDB in the same process.
02Count each batch by minute and language
03Close each minute and upsert it
A one-minute tumbling window closes a minute once the stream is 60 seconds past its end. sqlflow's Postgres sink upserts the closed minute on (bucket, lang) in one transaction. The whole pipeline is pipeline.yml.
04Declare the analytics queries
rollups.yml names the grains Postgres keeps and the dataset the API serves. sqlflow rollup generates the triggers that re-merge each grain when a minute lands, and the serve.yml dataset with one SQL query per grain. Change the declaration, regenerate, commit.
05Request it over HTTP
sqlflow serve runs serve.yml as a read-only HTTP API. The dashboard calls it from your browser. So can you. The client id names the caller in the API's log. It is not a credential: the API serves public data only.
The response names the grain it chose and the range it resolved. Ask for a day and you get 5-minute buckets; ask for a month and you get 6-hour ones. Every route and every error code is in the repository README.
The pipeline, the rollup declaration, the API config and the Render blueprint are in the demo repository. The tumbling window tutorial builds the same pipeline on your laptop.