Investigate SubQuery as a substitute for QueryAPI

[Tguntenaar]

Affected Portal:
All

Description:
Provide a clear and concise description of the bug. What exactly is happening that should not?

The QueryAPI is going to be deprecated. So we will need to migrate all portals from QueryAPI to another solution. SubQuery seems like a good option on the surface, however that needs to be investigated further.

A good start would be to make a working prove of concept to see if we are able to index historical data.

User Type:
All users

Expected behavior
We want the data to be indexed in a simlar fashion as we do now. Ideally all functionality stays the same we just move away from pagoda's QueryApi service.

Priority
Critical (P0) – Cannot complete critical action

Additional Context
Include any other relevant information that might help diagnose the issue, such as error messages or other observations.

[Tguntenaar]

While trying to work with the near example of subquery network, I ran into a few minor issues and a blocker. For that I've created an issue in the subql repository.

[Tguntenaar]

I've planned in a call with @petersalomonsen on Thursday to have some extra eyes looking at this. For now I will be picking up another issue.

[Tguntenaar]

@frol @petersalomonsen Closing this issue as the Subquery network indexer feels like overkill for our use case. With only 200 proposals annually, we don't need such a complex solution. Indexing an entire year would involve making RPC calls for 25 million blocks, even with batching and skipping historical blocks.

Additionally, the cost is $150 per indexer per month. To address this, @petersalomonsen and I discussed merging the event-committee, infra, and devhub indexers into one using composite IDs. However, this approach would still be more expensive than implementing an RPC caching layer, which I’ve outlined in issue #948.