I’ve been discussing Mastodon UX wishlists with some new acquaintances there. This excerpt from A Bloomberg terminal for Mastodon concludes with part of my own wishlist.
In a Mastodon timeline, a chatty person can dominate what you see at a glance. When we participate in social media we are always making bids for one another’s attention. As publishers of feeds it’s wise to consider how a flurry of items can overwhelm a reader’s experience. But it’s also useful to consider ways that feed readers can filter a chatty source. Steampipe’s SQL foundation affords an easy and natural way to do that. Here’s part of the query that drives the list view.
select distinct on (list, person, hour) -- only one per list/user/hour person, url, hour, toot from data order by hour desc, list, personIt was easy to implement a rule that limits each person to at most one toot per hour. Next steps here will be to apply this rule to other views, show the number of collapsed toots, and enable such rules on a per-person basis.
As a warmup exercise, I decided to first add a simple control for boosts that enables me to see my home timeline with or without boosts. To give technically-inclined readers a sense of what’s involved in doing this kind of thing with Steampipe, I’ll describe the changes here. I’m obviously biased but I find this programming environment to be accessible and productive. If it seems that way to you as well, you might want to try out some of the items on your own UX wishlist. And if you do, let me know how it goes!
Here are the original versions of the two files that I changed to add the new feature. First there’s home.sp which defines the dashboard for the home timeline.
dashboard "Home" {
tags = {
service = "Mastodon"
}
container {
// a text widget with the HTML links that define the menu of dashboards
}
container {
text {
// a block that displays the HTML links that form a menu of dashboards
}
card {
// a block that reports the name of my server
}
input "limit" {
width = 2
title = "limit"
sql = <<EOQ
with limits(label) as (
values
( '50' ),
( '100' ),
( '200' ),
( '500' )
)
select
label,
label::int as value
from
limits
EOQ
}
}
container {
table {
title = "home: recent toots"
query = query.timeline
args = [ "home", self.input.limit ]
column "person" {
wrap = "all"
}
column "toot" {
wrap = "all"
}
column "url" {
wrap = "all"
}
}
}
}
And here’s the new version. It adds an input block called boosts, and passes its value to the referenced query.
dashboard "Home" {
tags = {
service = "Mastodon"
}
container {
// a text widget with the HTML links that define the menu of dashboards
}
container {
text {
// a block that displays the HTML links that form a menu of dashboards
}
card {
// a block that reports the name of my server
}
input "limit" {
// as above
}
input "boosts" {
width = 2
title = "boosts"
sql = <<EOQ
with boosts(label, value) as (
values
( 'include', 'include' ),
( 'hide', ' ' ),
( 'only', ' 🢁 ' )
)
select
label,
value
from
boosts
EOQ
}
}
container {
table {
// as above
args = [ "home", self.input.limit, self.input.boosts ]
}
}
}
Steampipe dashboards are built with two languages. HCL (Hashicorp configuration language) defines the UX widgets, and SQL fills them with data. In this case we’re selecting static values for the boosts input. But any Steampipe query can run there! For example, here is the input block I use on the dashboard that filters the timeline by the list to which I’ve assigned people.
input "list" {
type = "select"
width = 2
title = "search home timeline"
sql = <<EOQ
select
title as label,
title as value
from
mastodon_list
order by
title
EOQ
}
Now here is the referenced query, query.timeline, from the file query.sp which contains queries used by all the dashboards.
query "timeline" {
sql = <<EOQ
with toots as (
select
account_url as account,
case
when display_name = '' then user_name
else display_name
end as person,
case
when reblog -> 'url' is null then
content
else
reblog_content
end as toot,
to_char(created_at, 'MM-DD HH24:MI') as created_at,
case
when reblog -> 'url' is not null then '🢁'
else ''
end as boosted,
case
when in_reply_to_account_id is not null then ' 🢂 ' || ( select acct from mastodon_account where id = in_reply_to_account_id )
else ''
end as in_reply_to,
case
when reblog -> 'url' is not null then reblog ->> 'url'
else url
end as url
from
mastodon_toot
where
timeline = $1
limit $2
)
select
account,
person ||
case
when in_reply_to is null then ''
else in_reply_to
end as person,
boosted || ' ' || toot as toot,
url
from
toots
order by
created_at desc
EOQ
param "timeline" {}
param "limit" {}
}
And here is the new version of that query.
query "timeline" {
sql = <<EOQ
with toots as (
// as above
),
boosted as (
select
$3 as boost,
boosted,
account,
in_reply_to,
person,
toot,
url
from
toots
order by
created_at desc
)
select
account,
person ||
case
when in_reply_to is null then ''
else in_reply_to
end as person,
boosted || ' ' || toot as toot,
url
from
boosted
where
boost = boosted
or boost = 'include'
or boost = 'n/a'
EOQ
param "timeline" {}
param "limit" {}
param "boost" {}
}
The original version uses a single CTE (aka common table expression aka WITH clause), toots, to marshall data for the concluding SELECT. The new version inserts another CTE, boosts, into the pipeline. It uses $3 to reference param "boost" {} which maps to the self.input.boosts passed from home.sp
The SQL code is all standard. Postgres is the engine inside Steampipe, and I sometimes use Postgres-specific idioms, but I don’t think any of those are happening here.
The HCL code may be unfamiliar. Steampipe uses HCL because its core audience are DevSecOps pros who are familiar with Terraform, which is HCL-based. But its a pretty simple language that can be used to describe all kinds of resources. Here the resources are widgets that appear on dashboards.
The other thing to know, if you want to roll up your sleeves and try building your own dashboards, is that the developer experience is — again in my biased opinion! — pretty great because if you’re using an autosaving editor you’ll see your changes (to both HCL and SQL code) reflected in realtime.
To illustrate that, here’s the screencast we included in our blog post introducing the dashboard system.
Not shown there, because we wanted to focus on the happy path, is realtime feedback when your SQL queries provoke Postgres errors. The experience feels very much like the one Bret Victor champions in Inventing on Principle. The core principle: “Creators need an immediate connection to what they’re creating.”
Here’s the wrong way that too often constrains us.
If there’s anything wrong with the scene, or if I go and make changes, or if I have further ideas, I have to go back to the code, and I edit the code, compile and run, see what it looks like. Anything wrong, I go back to the code. Most of my time is spent working in the code, working in a text editor blindly, without an immediate connection to this thing, which is what I’m actually trying to make.
And here is the right way.
I’ve got this picture on the side, and the code on the side, and this part draws the sky and this draws the mountains and this draws the tree, and when I make any change to the code, the picture changes immediately. So the code and the picture are always in sync; there is no compile and run. I just change things in the code and I see things change in the picture.
We want to work the right way wherever we can. The experience isn’t available everywhere, yet, but it is available in Steampipe where it powerfully enables the experimentation and prototyping that many of us are inspired to do as we delve into Mastodon.
If you want to try this for yourself, please check out the setup instructions for the plugin that maps Mastodon APIs to Postgres tables, and the dashboards that use those tables, and ping me (on Mastodon if you like!) with any questions you may have.
—
1 https://blog.jonudell.net/2022/11/28/autonomy-packet-size-friction-fanout-and-velocity/
2 https://blog.jonudell.net/2022/12/06/mastodon-steampipe-and-rss/
3 https://blog.jonudell.net/2022/12/10/browsing-the-fediverse/
4 https://blog.jonudell.net/2022/12/17/a-bloomberg-terminal-for-mastodon/
5 https://blog.jonudell.net/2022/12/19/create-your-own-mastodon-ux/
6 https://blog.jonudell.net/2022/12/22/lists-and-people-on-mastodon/
7 https://blog.jonudell.net/2022/12/29/how-many-people-in-my-mastodon-feed-also-tweeted-today/
8 https://blog.jonudell.net/2022/12/31/instance-qualified-mastodon-urls/
9 https://blog.jonudell.net/2023/01/16/mastodon-relationship-graphs/
10 https://blog.jonudell.net/2023/01/21/working-with-mastodon-lists/
11 https://blog.jonudell.net/2023/01/26/images-considered-harmful-sometimes/
12 https://blog.jonudell.net/2023/02/02/mapping-the-wider-fediverse/
13 https://blog.jonudell.net/2023/02/06/protocols-apis-and-conventions/
14 https://blog.jonudell.net/2023/02/14/news-in-the-fediverse/
15 https://blog.jonudell.net/2023/02/26/mapping-people-and-tags-on-mastodon/
16 https://blog.jonudell.net/2023/03/07/visualizing-mastodon-server-moderation/
17 https://blog.jonudell.net/2023/03/14/mastodon-timelines-for-teams/
18 https://blog.jonudell.net/2023/04/03/the-mastodon-plugin-is-now-available-on-the-steampipe-hub/
19 https://blog.jonudell.net/2023/04/11/migrating-mastodon-lists/
20 https://blog.jonudell.net/2023/05/24/when-the-rubber-duck-talks-back/
Jon, we knew each other 100 years ago 😊. I am unsure of what steampipe is. I am investigating an Apache product called superset. Is steampipe analogous? Or is it just for administration and management of aws resources?
Hi Pito, yeah it’s been a while!
Steampipe is a Postgres-based engine that maps APIs to db tables, by way of a suite of plugins. https://hub.steampipe.io/plugins
An overview: https://www.infoworld.com/article/3648648/how-sql-can-unify-access-to-apis.html
Superset is one of many tools that can connect to Steampipe’s Postgres instance and leverage the ability to query APIs. https://steampipe.io/docs/cloud/integrations/apachesuperset
> is it just for administration and management of aws resources?
That was the initial use, and remains the strongest, but the plugin ecosystem is robust and growing. What are the APIs of most interest to you?