- open source blockchain explorer and data playground (alpha release)

in #steemdb6 years ago (edited)

One thing I get asked for a lot on is historical data. The problem I ran into was in the way steemstats was designed - everything is loaded in real time from the blockchain. I needed a way to store and retrieve data based on time and date - (steem database) is the concept I ended up with, which will allow all of us to use this data.

Before I go further, this post will be split into two parts: the first part is for everyone and outlines the benefits brings to steem users; and the second part for the developers interested in learning and/or contributing.

Also - This is a super early alpha, it might crash :)

Introducing, the website

steemdb is about being able to look at data in different ways, as well as back historically in time, using techniques that aren't currently available through the steem blockchain. It also serves as a playground for new ideas and experiments. It's a block explorer at heart, but doesn't feel like your typical explorer.

I'd invite you to check out my account overview here:

or you can even continue reading this post via steemdb:

Currently steemdb allows you to do the following things:

  • View detailed account information + historical charts
  • View detailed information about posts
  • View the current witnesses + mining queue
  • Search for accounts using the box in the upper right
  • (Experiment #1) Browse posts by creation date, filter by tag, sorted by either votes or payouts

You currently can't do anything that requires a login - steemdb is all about exploration for the time being.

This post is going to get long, so for those of you who need a TLDR, I'd invite you to scroll through the small gallery of images below that show the various pages available.

Account View


The account view breaks down the different components of an account into separate tabs and tries to make the information easy to digest. You can look at anyone's account with this detail. It also charts and shows historical data for some areas of information to track changes over time. It's not mean to be a replacement to steemstats, but a tool that steemstats could link to to learn about others.

Much of this data will be available on in the near future. I'll also hopefully be providing some public APIs of the same thing for everyone else to use. This is all dependent on how optimized I can make the data and how much the infrastructure would cost to scale.


Post View


This was my take on how I'd want to explore the data behind a post. It's not interactive at this point, but already provides a bit more insight than we have here on steemit. It features the content broken out into a tabular view based on data aspect, and a sidebar that shows you about the author, other things they've written, and other meta information.

I've also included buttons on each post to quickly view it on, just in case you discover something you'd like to vote on or respond to.


Witness View

This section is a mirror of the witness page on It displays information important to the witnesses and miners on the steem network.


Posts by Date (Experiment #1)

Example: (the current homepage)

This section was designed as an experimental view of data on the blockchain. This was my first attempt at organizing the data on steem in a more "content silo'd" fashion to see how it could be browsed.

I plan on doing a number of experiments on different ways to view the content in an attempt to help determine the best way to present it. My thought is that I'll be able to move a lot faster (since it's a database, not a blockchain) and help prototype what sort of things we may see here someday on

Currently, the posts page is set to:

  • Grouping: It's grouped by the creation date. You only view one day at a time, and can go forwards and backwards in time.
  • Sorting: Posts by default are sorted by payouts which can be changed to votes.
  • First Tag Priority: If you click on a tag, if will only show you posts where that is the first tag.

It's pretty interesting to go back in time and view what was popular on specific dates. It's even more interesting when you pick a tag, and then paginate by date. I've found a few posts that I never would have seen by doing this.



What's next for (the website)?

The short version is more historical data, charts, APIs and ways to view the data that exists on the blockchain.

One of the biggest goals with steemdb is to help people learn about all the various aspects of the steem platform. has the same goal, but just from a very different angle. is about someone specific, and is about everyone in general.

Over time, I think that these two projects will integrate with each other more and more.

The steemdb platform will also be used to show the community alternate ways that the content here on steem can be presented. I plan on trying out a bunch of interfaces you're likely familiar with to see what works best and what the community would like.

I feel the best way to actually garner feedback is with a prototype, so I plan on delivering them, hopefully with the help of others!

Speaking of which...

Introducing aaroncox/steemdb - the code

Let me start by saying I most definitely followed the mantra of "do things that don't scale" for this prototype. If I never updated this code ever again, it wouldn't be scalable to the size that I believe steem can achieve. In fact I don't even know how the servers will hold up with the announcement and general usage of the site. Gotta love these alpha versions!

It's also not really that easy to use yet! The reasons for this are:

  • It's 2 weeks old
  • I used technologies I didn't have to learn
  • I'm learning the best ways to retrieve the data from the blockchain

Just like steem itself, if you're going to get involved, you're going to have to be able to figure how things work without documentation (for now). Learn to ask questions - lots of them.

I'd invite you to join the #steemdb channel on, as I'll try to focus any interested parties there. If you join to promote your posts... you're gonna get the evil eye.

The technologies behind steemdb currently

Technology stack was chosen based on technologies I've used recently, not what's best. I'm still working full time on other projects and steem is still only a portion of my time each week. So if I needed a hammer, and all I could find was a rock, I used it.

That doesn't mean these technologies can't change and be adapted. Remember, it's an alpha and an experiment...

Currently these are the components that make up the project:

  1. Data Storage: MongoDB. Say what you will about it, but I was able to throw blocks of json at it, index it, and then begin querying/aggregating it.
  2. The Website: Powered by PHP7/nginx, using the Phalcon 3 framework, and a small collection of composer packages. On the frontend it uses semantic-ui for UI and plottable.js for charts. It connects to MongoDB as well as directly to the steem blockchain (very lightly).
  3. The Services: Written in Python3, and powered by Piston, these services act as the synchronization tools between the blockchain and the database.

All of this is encapsulated in a variety of docker containers that's controlled through docker-compose.

Future direction of the project

Since this is the first release, a firm direction hasn't been set in stone. I'd like to see a number of improvements, as well as some optimizations, to really firm up it's core.

Right now off the top of my head, I believe it's immediate future includes:

  • Some build processes to help manage js/css dependencies. It's very manual right now. You'll actually find a nice symlink from the public folder into the bower components.
  • Cleaning up the synchronization scripts so they don't store quite so much data. Right now there's a lot of duplicate data, including posts, so the database is huge. Luckily disk space is cheap right now, and the project is still rather limited.
  • The charts aren't in the best of places. The JS is actually stored in a volt file simply because docker was fighting me by corrupting my JS files.
  • Most of the aggregation queries should be moved out of the controllers and into the appropriate models for reusability. There's very little duplicate code right now, but it might get out of hand if that pattern continues.

There are also some loftier goals of removing the frontend from PHP, and using something like react. This would make the PHP layer purely an API for the frontend to interact with.

How do I run the damn thing?

I will get a full development environment guide up in the coming weeks. It's going to take a LOT of disk space and some patience for the setup. You're going to have to build your own entire database version of the 4+ million blocks in the blockchain. Currently, completely unoptimized, it's consuming 21.943GB of disk space on my server.

If this is something you're interested in - the first step I'd take in getting started is setup a local steemd instance and synchronize the entire blockchain to a local computer/server. It just so happens I wrote a guide on a web instance of steemd a few weeks ago. I'd start there.

The first step of running steemdb is going to be letting the services run for like 6-12 hours as it creates your database.

Wrapping things up

I'm pretty excited about this project, being able to query the database for pretty much any bit of information is like having a super power. I will do my best to keep the community up to date of changes, digest feedback, and help shape it into another great tool for the community to use.

Philosophically, steemdb is what I'd consider to be the 3rd and final part of the trinity of projects I've been working on:

  • steemstats: All about you and your data
  • steemdb: Aggregate information about everyone and everything
  • steempress: Taking your data and letting you start your own website

These projects combined will hopefully form a powerful open source combination to help contribute to the overall steem ecosystem. steempress is up next in the list of priorities, with two new themes to implement and a few projects that have expressed interest in using it to power their ideas.

As a current developer, backup witness, writer and normal guy - I hope to continue being a meaningful part of this community far into the future. Thank you all for your support!


This is not great - it is exceptional!!!

Where do you find the time to do it all? Expertise is no problem as I can see :)

Extremely cool and useful. You came up with a proper data warehouse for a steem blockchain.

Is this a first for any blockchain?

It comes as somewhat lame but ... keep on the good work ;)

You should create a tab to show account is ranked by amount of followers from most followers to lowest followers, not just by vesting.

Account with a lot of followers are more important.

Agreed, I'll add some options for you to sort on that page ;)

also, you need to create a tab to show account rank by number of post from highest to lowest.

Fantastic post! Thank you for your dedication to this enterprise and thrive on! All for one and one for all! I'll surely be using this and you got an extra upvote from here. Namaste :)

Thank you for everything you do for the Steem community! I find your tools extremely helpful. I have some suggestions I think would make them even better.

Suggested Improvements for Steemstats:

1. Add notifications for new followers

2. Allow for sorting of followers, sort by:

  • Date they started following you
  • Amount of Steem Power

3. Add cumulative statistics for followers

  • Total Steem Power of followers
  • How this number compares to other users

Oh some of these are fun :)

I'll add them to my list and see what I can do!

Excellent, I look forward to seeing what you produce!

This is AWESOME @jesta! I just released a little tool for Steemit a few days ago --! It provides stats and analytics plus free custom short vanity URLs for Steemit articles. I'm not much of a coder, but I wanted to provide a valuable tool for this community. I hope you'll check it out :) Thanks for all your great contributions!

You've outdone yourself this time @jesta. This historical data is really useful. I particularly like the incoming/ outgoing votes graph, don't think I've seen that feature anywhere else. Good job.

Thanks :)

I had a lot of ideas for charts, I just haven't had time to implement them all just yet. I actually have some prototypes sitting here on this URL for more global stats:

They take a few seconds to load, but show the capabilities of the database :)

A second take of your newest tools gives me another opportunity to applaud you.

Raw data tab within posts is an essential help.

Here I can copy the body and paste it into new posts :)

Comes handy when you want to respin articles which are older that 12 hours and you can't edit them any longer.


Great tool! I pretty much confirmed my suspicion that my account is broken using it.

One question, can you make the overview tab go "all the way?" Paginate it or whatever if needed, but I've been finding POW's and not seeing them credited for a few days now and can only see the last attempt on that tab...

And if anyone sees this and thinks they can help me with my mining issue I've posted here:

(Screenshots from Steemdb to come in the morning.)

Yeah definitely a good suggestion. I'll add it to my list!

Awesome website @jesta! Glad there are a lot of steemians contributing to the grow of this platform. I like to consider myself and active contibutor and recently I also released a cool website!
Steem Locator
A website to find Venues accepting Steem or SBD all over the world. Please check it out!
Lets continue supporting this awesome community!

Wow! Another fantastic contribution, @jesta! I was super excited that you included a tech section! =D

Awesome! I can't wait to play around with this 😀

I hope you don't mind Me throwing in a shameless plug on your behalf- if you haven't already you should upvote @jesta as a witness. This is definitely someone we want running the backbone of Steem!

Great work!
Is it update for steemstats or something different?
How often database is updated, I see my account was updated 4 hours ago?
Love charts btw, they look great!

Totally different website, as I explained in the OP. My projects are:

  • steemstats: All about you and your data
  • steemdb: Aggregate information about everyone and everything
  • steempress: Taking your data and letting you start your own website

Fantastic stuff as always, Aaron :) Looks like I won't be visiting much henceforth.

@jesta, Thank you for a great tool supplementing

@Jesta this is a really good start - so congrats on this - i will def push a donation to you ). A couple of thoughts my side:

  1. View data between dates (eg 1 Aug to 24 Aug)
  2. Export raw data to xls, for data from point 1
    Keep it up )

Nice.. Great work. I appreciate your contribution in steemit :)

This is beautiful work.

Congratulations again and thanks for being such an essential part of the community.

thanks information @jesta

This is great, thanks for all of your hard work on!

Great work! @jesta

Actually, I made a similar website and submit a post
in here

Why my post is so poor? :-) provides income information for Steemit users. On this website, you can see how much money you have earned from steem website. There are details, a summary and a chart. The amount is converted into US dollars using steem's real time exchange rates.

I saw that post the other day and gave you an upvote.

I'm just guessing here - but it may have not done as well simply because it's a lot simpler and most of those features already exist elsewhere. Right now (as far as I can tell) all that does is:

  • a little bit of global historical information on payouts (available on steemit)
  • the top posts form the API (available on steemit)
  • a page that shows a single user's earnings recently (available on steemwhales)

The post itself could have just been missed by many too, we all know there are some content discovery issues that happen from time to time. You could also open source it for extra credit, which would help others learn from your work :)

Thank you! I will add more useful feature to

Fantastic work. Can it be made like, where we can run SELECT query?

Possibly in the future, it's an option. Right now if I let anyone run any query they wanted it's likely it would grind the server to a halt. It's only optimized to run the queries I've setup - so it'd take some work to get it fast enough to handle other requests.

It's also not SQL, but I get the premise of the idea ;)

Stupendous! How do you create such tools so fast?!? Do you not sleep?
I think it takes me more time to explore, understand, and appreciate your work than it does for you to create it. Keep it up.

I've been building sites like these forever... though I have lost a bit of sleep ;)


Incredibly awesome, i'm looking into it right now, really appreciate your work, thank you :)

I like numbers, here have an upvote, im sorry my upvote is worthless, i havnt been blessed by the gatekeepers yet.

This Steem blockchain explorer is long overdue and steemd can use some competition :) Great work!! Again !!!!!!

service number 1 for steemit.

@jesta Really appreciate all your hard work and contributions to the evolution and development of this platform. Your efforts have helped me immensely. I have Steemstats open 24/7. It's my "go to" page for all things Steemit.

@jesta, Another great site!
Can you add the number next to the miner queue like Thanks

Thanks, and done! Numbers have been added.

Great! Thanks!
It is also great that you included the block number on under blocks.
Is it possible to tally up the number of blocks found in the 45-days chart where witness and pow is?

Both good suggestions, I'll see what I can do!

I know you are very busy with all these projects.
Thank you for take it into consideration.

Awesome @jesta!

I have a few ideas for other Steemit related services I have been mulling around in my head and it seems every idea I come up with the current API is a major design limitation. It's just too difficult and expensive to get at older data.

The only way I could think of to solve this is to pull every block and parse the information I need into a local db that is optimized for the searches I want to perform. I haven't had the initiative to attempt that though, and now you have gone and done it. Superb!

I look forward to looking over the code when I have the time to see if I can integrate into my projects. Super good work man!

curation_reward 1 minute ago View '_elements/tx/curation_reward' was not found in any of the views directory #0 [internal function]: Phalcon\Mvc\View->_engineRender(Array, '_elements/tx/cu...', false, false) #1 [internal function]: Phalcon\Mvc\View->partial('_elements/tx/cu...', NULL) #2 /var/www/com_steemdb/cache/volt/_var_www_com_steemdb_app_views_account_view.volt.php(227): Phalcon\Mvc\View\Engine->partial('_elements/tx/cu...') #3 [internal function]: unknown() #4 [internal function]: Phalcon\Mvc\View\Engine\Volt->render('/var/www/com_st...', Array, true) #5 [internal function]: Phalcon\Mvc\View->_engineRender(Array, 'account/view', true, true, NULL) #6 [internal function]: Phalcon\Mvc\View->render('account', 'view', Array) #7 /var/www/com_steemdb/public/index.php(41): Phalcon\Mvc\Application->handle() #8 {main}

What's wrong?

I visited the page of my friend @oflyhigh , and got above message
Now, It's seems normal.

Should be fixed now. Appears that one of the operations was renamed and was causing problems.

thanks information, support @jesta

Very awesome work from a genius like yourself.

how get user blog posts list through api?

There's a bunch of ways that return different sets of blog posts.

If you're looking for posts by a single user, you can use get_state t3ran13, which will return the account as well as all of the blog posts by that user.

I'd recommend checking out @lantto's api explorer to browse through all the possible api calls:

thank you very much!

Great work @jesta, almost what I needed in terms of additional functionality that steemstats did not have. The historical data charts are actually quite useful to see how things have progressed.

You probably have not seen my idea, but maybe you can consider further extending some of the functionality like I'm describing here:

So exciting, @jesta! I've been waiting for this since you teased it in the chat. Thank you for all you're doing for the community!

Coin Marketplace

STEEM 0.27
TRX 0.07
JST 0.034
BTC 23782.42
ETH 1875.89
USDT 1.00
SBD 3.25