Construct a Mastodon dashboard with Steampipe

I used to be decided to write down my Mastodon #introduction at the moment. To get began I used the tag search within the dashboard I’m constructing.

IDG

The thought was to take a look at a bunch of different #introduction posts to get a really feel for the way mine ought to go. Once you search particularly for hashtags, the Mastodon search API returns this data.

"hashtags": [
    {
      "name": "introduction",
      "url": "https://mastodon.social/tags/introduction",
      "history": [
        {
          "day": "1574553600",
          "uses": "10",
          "accounts": "9"
        },
        // ...
      ]
    },

A primary model of the dashboard, having solely this information to work with, simply listed the names of tags matching the search time period together with corresponding URLs. Right here was the preliminary question.

choose 
  identify,
  url
from 
  mastodon_search_hashtag 
the place 
  question = 'introduction'

That produced an inventory of hyperlinks, like https://mastodon.social/tags/introduction, to dwelling pages for variants of the tag. These are helpful hyperlinks! Every goes to a web page the place you possibly can see who’s posting to the tag.

To make this view barely extra helpful, I tapped the third ingredient of the API response, historical past, in a revised question.

with information as (
  choose 
    identify,
    url,
    ( jsonb_array_elements(historical past) ->> 'makes use of' )::int as makes use of
  from 
    mastodon_search_hashtag 
  the place 
    question = 'introduction'
)
choose
  identify,
  url,
  sum(makes use of)
from
  information
group by
  identify, url
order by
  sum desc

These outcomes assist me resolve which variant to make use of.

+-------------------+---------------------------------------------------+------+
| identify              | url                                               | sum  |
+-------------------+---------------------------------------------------+------+
| introduction      | https://mastodon.social/tags/introduction         | 1816 |
| introductions     | https://mastodon.social/tags/introductions        | 218  |
| introductionpost  | https://mastodon.social/tags/introductionpost     | 19   |
| introductionfr    | https://mastodon.social/tags/introductionfr       | 6    |

However I nonetheless want to go to every hyperlink’s web page to discover the way it’s getting used. It could be good to floor extra context within the dashboard, and I discovered a nifty option to do it, however first let’s dwell on the revised question for a minute. Postgres’s JSON options are highly effective and it’s usually a problem (no less than for me) to visualise how they work.

The Postgres jsonb_array_elements() perform is what’s referred to as a set-returning perform. Right here it unpacks Postgres’s JSON illustration of the listing of historical past buildings returned from the Mastodon API. In its easiest kind, the perform name jsonb_array_elements(historical past) produces a brief desk with per-tag, per-day information.

choose
  identify,
  jsonb_array_elements(historical past) as historical past
from
  mastodon_search_hashtag 
the place 
  question = 'introduction'

+--------------------------------+----------------------------------------------------+
| identify                           | historical past                                            |
+--------------------------------+----------------------------------------------------+
| introduction                   | {"accounts":"16","day":"1670371200","makes use of":"19"}   |
| introduction                   | {"accounts":"250","day":"1670284800","makes use of":"269"} |
| introduction                   | {"accounts":"259","day":"1670198400","makes use of":"274"} |
| introduction                   | {"accounts":"253","day":"1670112000","makes use of":"270"} |
| introduction                   | {"accounts":"245","day":"1670025600","makes use of":"269"} |
| introduction                   | {"accounts":"345","day":"1669939200","makes use of":"383"} |
| introduction                   | {"accounts":"307","day":"1669852800","makes use of":"339"} |
| introductionsfr                | {"accounts":"0","day":"1670371200","makes use of":"0"}     |
| introductionsfr                | {"accounts":"0","day":"1670284800","makes use of":"0"}     |
| introductionsfr                | {"accounts":"0","day":"1670198400","makes use of":"0"}     |
| introductionsfr                | {"accounts":"0","day":"1670112000","makes use of":"0"}     |
| introductionsfr                | {"accounts":"0","day":"1670025600","makes use of":"0"}     |

historical past is a JSONB column that holds an object with three fields. The revised question makes use of Postgres’s JSON indexing operator ->> to achieve into that object and hoist the variety of each day makes use of into its personal column, so it may be the goal of a SQL SUM perform.

OK, prepared for the nifty resolution? Recall that https://mastodon.social/tags/introduction is the house web page for that variant of the tag. There you possibly can see introduction posts from individuals utilizing the tag. These posts sometimes embrace different tags. Within the dashboard proven above you possibly can see that Kathy Nickels is utilizing these: #Music #Artwork #Equestrian #Nature #Animals. The tags seem in her introduction put up.

IDG

I didn’t instantly see the way to seize them to be used within the dashboard. Then I remembered that sure lessons of Mastodon web page have corresponding RSS feeds, and puzzled if the tag pages are members of 1 such class. Positive sufficient they’re, and https://mastodon.social/tags/introduction.rss is a factor. That hyperlink, fashioned by tacking .rss onto the bottom URL, gives the additional context I used to be on the lookout for. Right here’s the ultimate model of the question.

with information as (
  choose 
    identify,
    url,
    ( jsonb_array_elements(historical past) ->> 'makes use of' )::int as makes use of
  from 
    mastodon_search_hashtag 
  the place 
    question = 'introduction'
  ),
  makes use of as (
    choose 
      identify,
      url || '.rss' as feed_link,
      sum(makes use of) as recent_uses
    from 
      information 
    group 
      by connection, identify, url
  )
  choose
    u.identify,
    r.guid as hyperlink,
    to_char(r.revealed, 'YYYY-MM-DD') as revealed,
    r.classes
  from
    makes use of u
  be a part of
    rss_item r
  on 
    r.feed_link = u.feed_link
  the place
    recent_uses > 1
  order by 
    recent_uses desc, revealed desc
)

The brand new elements, courtesy of the RSS feed, are guid, which hyperlinks to a person introduction like Kathy’s; revealed, which is the day the introduction appeared; and classes, which has the tags used within the introduction put up. Candy! Now I can scan the dashboard to get a way of which introductions I need to take a look at.

The primary three queries use the Steampipe plugin for Mastodon, and specifically its mastodon_search_hashtag desk, which encapsulates the Mastodon API for looking tags. The ultimate model joins that desk with the rss_item desk offered by the RSS plugin, utilizing the widespread base URL as the premise of the be a part of.

This delights me in so some ways. When the blogosphere first emerged within the early 2000s, a few of us found that the RSS protocol was able to excess of simply delivering feeds to RSS readers. The opposite new scorching protocol in that period was XML internet companies. As an InfoWorld analyst I used to be speculated to be cheering the latter as an enterprise-grade know-how, however I couldn’t assist noticing that RSS stored turning out to be an effective way to maneuver information between cooperating techniques. That’s all the time been true, and I like how this instance reminds us that it’s nonetheless true.

I’m equally delighted to indicate how Steampipe permits this contemporary train in RSS-powered integration. Steampipe was, initially, an engine for mapping outcomes from JSON API endpoints to SQL tables. Over time, although, it has broadened its view of what constitutes an API. You should utilize Steampipe to question CSV information, or Terraform information, or—as we see right here—RSS feeds. Knowledge is available in every kind of flavors. Steampipe abstracts these variations and brings all of the flavors into a standard area the place you possibly can purpose over them utilizing SQL.

And at last, it’s simply great to be on the intersection of Mastodon, Steampipe, and RSS on this exceptional second. I’ll readily admit that nostalgia is an element. However RSS did bust issues huge open 20 years in the past, Mastodon’s doing that now, and I like that RSS may also help it occur once more.

Now I would like to write down that #introduction!