Mastodon relationship graphs | InfoWorld

The new version of Steampipe is all about relationship graphs. Our blog post shows how these graphs provide contextual insights for developers and security professionals who can now see all the resources related to an EC2 instance or determine at a glance if the permissions related to an IAM role are properly scoped. As always, developers are free to explore and remix the code that builds these graphs and adapt the idioms for their own purposes in any data domain.

These relationship graphs are driven by SQL queries that define nodes and edges. Such queries can use any column from any table provided by any Steampipe plugin to form nodes and then borders between nodes. If you want to see the connections between people and objects represented by various APIs, you can now use SQL idioms to represent them graphically. The only limit is your imagination.

Naturally, I envisioned graphing Mastodon’s relationships. So far I have built two charts that visualize the timeline of my house. Here is the first one.

Here we are looking at the 50 most recent pushes (the Mastodon version of retweet) in my home line. This is the query to find them.

select
  *
from
  mastodon_toot
where
  timeline="home"
  and reblog_server is not null
  limit  50

If we focus on Brian Marick we can see that:

Brian belongs to mastdn.social.
Brian prompted a Tim Bray post.
Tim belongs to hachyderm.io.

So this graph shows people on a selected server boosting people on other servers. In this case, mastdn.social is the selected server, but we can refocus the graph on any other server that is sending pulses.

The second graph zooms out to show the network of boost relationships between servers. If someone on infosec.exchange powers someone on mastodon.world, there is an edge connecting the two nodes. Although it’s not happening anywhere in this graph, the arrow can point both ways and would if someone on mastodon.world also boosted someone on infosec.exchange.

mastodon increases from server to server

Let’s build the first graph step by step.

Step 1: Identify the selected server

Here is the definition of the node that represents the selected server.

node {
  category = category.selected_server
  args = [ self.input.server.value ]
  sql = <<EOQ
    select
      server as id,
      server as title,
      jsonb_build_object(
        'server', server
      ) as properties
    from
      mastodon_boosts()
    where
      server = $1
  EOQ
}

According to the documentation, a node query must select at least one aliased column as id. Here is the server column in a row returned by the above query. I have packaged that query into a SQL function, mastodon_booststo hide details (timeline="home" reblog_server is not null limit 50) and makes it easier to focus on what’s special about each node. In this case, the special quality is that the server column that gives the node its identity coincides with the selected server.

If the graph block includes only this node and mastdn.social is the selected server, here is the representation. There is not much to see here yet!

The node defines a property bag that can be any of the columns returned by the underlying query; these appear when the node is scrolled. Node also refers to a category that governs the node’s icon, color, and link. Here is the category for the selected server.

category "selected_server" {
  color = "darkgreen"
  icon = "server"
  href  = "https://{{.properties.'server'}}"
}

Step 2: Identify the boosted servers

Now we will add boosted servers. This node uses the same set of records: the most recent 50 impulses in my feed. Once again find only those whose server column matches the selected server. But id is now the reblog_server which is the destination, rather than the source, of pulses from the selected server.

node {
  category = category.boosted_server
  args = [ self.input.server.value ]
  sql = <<EOQ
    select
      reblog_server as id,
      reblog_server as title
    from
      mastodon_boosts()
    where
      server = $1
    EOQ
}

Here is the graph with both selected_server and boosted_server nodes. We have used another category to differentiate the boosted nodes.

There is only one server selected, but you can send impulses to more than one boosted server. The default representation collapses them into one node, but you can click to expand them to see them all.

Also Read: Optimizing metadata performance for web-scale applications

Step 3: Identify the people who drive others

Where are the people? Let’s add them next, starting with the people who send pulses.

node {
  category = category.person
  args = [ self.input.server.value ]
  sql = <<EOQ
    select
      username as id,
      display_name as title,
	    jsonb_build_object(
        'instance_qualified_account_url', instance_qualified_account_url
      ) as properties
    from
      mastodon_boosts()
    where
     server = $1
    EOQ
  }

He Username column gives the node its identity. Also note the property instance_qualified_account_url. That’s the synthetic column we added to the Mastodon plugin last time to ensure that links to people and toots work correctly in the Mastodon client. Because it’s included in a property here, and because category.person references that property, links representing people in the graph will be resolved correctly.

Step 4: Identify the people who are driven

This node takes its identity from reblog_username column, and uses the synthetic column instance_qualified_reblog_url to provide the link.

node {
  category = category.boosted_person
  args = [ self.input.server.value ]
  sql = <<EOQ
    select
      reblog_username as id,
      reblog_username as title,
      jsonb_build_object(
        'instance_qualified_reblog_url', instance_qualified_reblog_url
      ) as properties
    from
      mastodon_boosts()
    where
      server = $1
  EOQ
}

Step 5 – Connect the amps on the selected server to that server

So far we have only seen nodes, whose queries minimally return the id property. An edge connects nodes by means of a query that returns at least columns with aliases from_id and to_id.

edge {
  sql = <<EOQ
    select
      username as from_id,
      server as to_id,
      'belongs to' as title
    from
      mastodon_boosts()
  EOQ
}

You’ll also want to provide a title to label the border. Here, this border appears twice to represent “John Mashey belongs to mstdn.social” and “Brian Marick belongs to mstdn.social”.

Step 6 – Connect people on powered servers to your servers

This edge works the same way, but captures the relationship between the driven people and their servers.

edge {
  args = [ self.input.server.value ]
  sql = <<EOQ
    select
      reblog_username as from_id,
      reblog_server as to_id,
      'belongs to' as title
    from
      mastodon_boosts()
    where
      server = $1
  EOQ
}

Step 7: Connect drivers with the people they drive

Finally, we added a lead to connect boosters with the people they boost.

edge {
  category = category.boost
  args = [ self.input.server.value ]
  sql = <<EOQ
    select
      username as from_id,
      reblog_username as to_id,
      'boosts' as title,
      jsonb_build_object(
        'reblog_username', reblog_username,
        'reblog_server', reblog_server,
        'content', reblog ->> 'content'
      ) as properties
    from
      mastodon_boosts()
    where
      server = $1
  EOQ
}

And now we have completed the first chart shown above.

Graphing GitHub relationships

You can use this grammar of nodes and edges to describe relationships in any domain. Here’s a chart that breaks down all Steampipe-related repositories and shows recently updated PRs from external contributors.

And here’s one that uses any Steampipe plugin to display recently updated pull requests for a selected repository.

These two views share a common SQL query and have complementary purposes. The table is useful for sorting by date or author, the chart highlights one-to-many relationships.

Lift assembly load from context

At What TimeDance got it right, I mourned the passing of a meeting scheduling tool that had excelled at pulling together meeting-related messages and documents. I called this “context assembly,” a term I borrowed from Jack Ozzie, co-founder of Groove, another collaboration tool whose passing I mourn. Context assembly is hard work. Too often, the burden falls on people who only need to use that context and would rather not spend the time and effort creating it.

We have seen how SQL can unify access to APIs. Now it can also help us see the relationships between the data we pull from those APIs.