[Proposal] Public HAF database & HafSQL maintenance

Voted, Thanks for putting all this stuff together! You defiantly should get some help with funding to keep all the work you have put in up and running.

@askai give me a recap of this proposal and highlight the benefits for the Hive ecosystem

This proposal (#359) seeks $36 per day for 365 days to support the maintenance and development of the public HAF (Hive Application Framework) database and HafSQL, a powerful SQL interface built on top of it.

🔧 What’s Being Funded?

• Public HAF Database Access: A live PostgreSQL database containing the entire Hive blockchain data, updated in real time.
• HafSQL Tooling: An open-source layer that simplifies querying Hive data using standard SQL and exposes REST/gRPC APIs for developers.
• Infrastructure: Two dedicated servers (primary + backup) ensuring reliability, uptime, and seamless updates.

🌐 Current Services Hosted

The public HAF instance powers:

• HAF & HafSQL
• hivemind (backend for apps like PeakD)
• HAF Block Explorer
• Reputation tracker
• Balance tracker

📡 Access Details (Public & Free)

Anyone can connect directly:

host: hafsql-sql.mahdiyari.info
port: 5432
database: haf_block_log
user: hafsql_public
password: hafsql_public

APIs are also publicly available at:

• https://hafsql-api.mahdiyari.info/
• https://rpc.mahdiyari.info/hafsql
• https://api.syncad.com/hafsql

🚀 Benefits to the Hive Ecosystem

1. Lower Barrier to Entry: Developers can query blockchain data using simple SQL—no need to parse raw block logs or run full nodes.
2. Faster App Development: Enables rapid prototyping and deployment of dApps, analytics tools, and front-ends.
3. Reliable Infrastructure: Public access reduces duplication of effort; teams can build on a shared, well-maintained data layer.
4. Innovation Catalyst: Opens doors for advanced analytics, dashboards, bot systems, and research on Hive activity, reputation, and token flows.
5. Decentralized Tooling: Complements Hive’s decentralization by providing an open, transparent data access point.

In short: this proposal sustains a critical piece of Hive’s developer infrastructure—keeping data open, accessible, and usable for everyone.

Your thoughts? Worth supporting for a healthier Hive tech stack? 💡

Voted with my tiny HP :)

I didn't even know there was a public HAF!
Do you have any stats on availability and throughput capacity? Is it suitable for use by a Hive dApp? Thanks

Hi there!

This is probably an odd question, but: how many dapps are using this service?

Congratulations @mahdiyari! Your post has been a top performer on the Hive blockchain and you have been rewarded with this rare badge

Post with the highest payout of the day.

_{You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP}

https://hivedex.io is powered by the same instance. So yes it is suitable for use by the apps. The availability is high but it will be improved over time with smart switches to the backup server whenever the main server goes down. And the capacity shouldn't become a concern ever.

You can't get an exact answer to this question because it is impossible to track with the current setup. But from interactions I had with people, I can tell peakd is one of the apps that is using this mainly for polls and snaps. Recently themarkymark launched Hive Analytics which is also using this database.
I could gather the statistics of the server like how many queries are run per day and share that.

That's fantastic, thanks. I have been thinking about running a HAF instance, but figured the cost would be high. Can you share the specs of the server here? Just out of curiosity.

128GB RAM
7TB NVMe

The main thing you need is the fast NVMe storage. Right now with ZFS + LZ4 compression HAF + apps take 3TB. You could probably make it work with 32GB of RAM as it is mainly used as cache for ZFS and PostgreSQL.

OK, understood - yes. I'll maybe write some test code soon and see what I can do with your server. thanks

Please don't, it was just to have an idea of the impact that this has and compare it to HiveSQL.

Would it be possible to add a call for users by location?

Yes that is possible. The data is there. I could make it an API call. You could run the following query to find users with Germany location:

SELECT name FROM hafsql.accounts
WHERE json_metadata->'profile' @> '{"location": "Germany"}'::jsonb

You could even run your own website and provide this data. Just like how Hive Analytics takes data from the database and shows it on the website. This public database makes it easy for anyone to access and serve the data that otherwise would be hard to get.

That would help us with a small project with have in mind. We are already working on others that take await most of our time, but will definitely use this.

If you can make it a call, even better.

Excellent proposal.

Voted for you. Wish you all the best. More power to HafSQL & all other nodes that you run.

_{with hReplier}

So I was just reading the info on your HAFSQL page:

It is recommended to use the REST APIs as much as possible instead of direct SQL queries. The REST APIs will be supported on multiple public API nodes and will provide more reliability.
If your needs are not satisfied by the REST APIs, please consider opening an issue.

It seems like the current API calls are duplicates of what is available on existing RPC server nodes - so not so useful. Isn't the point of the HAF server really to support custom SQL calls? That's what I need to use it for. Are you saying that I should request that you add an API endpoint for custom calls once I settle on a query I want to use in production?

Thanks

I should remove that. I don't think like that anymore.

OK - My Hive site is intended to be as supportive of privacy as possible - so ideally I'd prefer any HAFSQL data retreival to be initiated from the browser, rather than a server - however, I don't think it's possible to directly query your postgres database from a browser - is that correct?
Are you open to adding REST API calls for queries that my site depends on? For example, a 'related posts' query?

What is the difference between your server running the query vs my server? Making an API doesn't stop the query from running in a server. You could say you are making an API by having it run on your server.

There would be one less server involved that can track the data and at present my system arguably does not fall into needing to be GDPR compliant, for example.
The more data it processes, the more challenging that becomes. It's kind of a grey aware without lawyers!

Where is your website hosted then? You are not processing private information. It is publicly available data. You can't expect your activity to be hidden while visiting a website on the internet.

BTW I'm arguing against your argument. I'm not against adding new APIs. Just your reasoning doesn't make sense.

I personally am in England. The GDPR legislation is somewhat open to interpretation. It has been stated that processing any information that could be used to identify someone means that GDPR is activated - which could include even otherwise anonymous usernames. So whether or not GDPR applies is somewhat open to legal interpretation - it seems that different 'experts' view this differently.

In terms of overall privacy, it's just 'best practice' to minimise exposure of user behaviour as much as possible. Surveillance capitalism is one of the most profitable 'businesses' - especially for social networks - so a commitment to limit the amount of potential tracking that my server could do is an ideal goal. If that's not possible, then so be it.

Anyways. Feel free to share your queries that you want them to become APIs. Also for the example you gave we already have an API call from Hivesense https://api.syncad.com/?urls.primaryName=HiveSense#/

See the similar posts.

OK, I don't have any queries to share yet - but I will when I have some, thanks.
I knew that there was a plan to run a 'similar posts' query, but didn't know it was live. I just tested the API there and it didn't return the kind of results I'm looking for, so I will probably try coding my own version.

Hi dear, les choses avancent dans le bon sens. Merci pour ton implication.

Very diverse and I hope everything goes as planned, I'll give you my vote