[SPIKE] SubQuery Integration

[timolegros]

Description

• [x] Research SubQuery capabilities and document general idea
• [x] Build a quick MVP for indexing stake trades using the Ethereum starter template
• [x] List all app/platform features that internal indexing enables and provide possible alternative implementations using SubQuery

Notes

• TheGraph is not being considered because they do not provide a high-performance hosted offering like SubQuery does. Given our requirements (multi-chain, fast-changing requirements, and a small very busy team) a hosted solution is likely our best bet. In addition to this, there are a multitude of other reasons why SubQuery is superior. Most importantly, is the fact that Sub-Query is Typescript native so it supports executing arbitrary TS code during indexing.

Additional context

• Running into problems like how we should handle chain reorganizations (https://github.com/hicommonwealth/commonwealth/issues/8764) as led us to question whether internally building an indexer (regardless of complexity i.e. Alchemy Webhooks vs CE Poller) is the correct choice.

[timolegros]

Intro

SubQuery is an indexing system built in Typescript that supports 208+ networks (any EVM chain, most Substrate chains, and most Cosmos chains). In SubQuery you create mapping functions at the block, transaction, or log level to populate a database with a fully customizable schema defined in GraphQL. SubQuery pairs a Postgres database with a PostGraphile server which enables running GraphQL queries over Postgres data. SubQuery supports real-time indexing via 'unfinalized blocks' and real-time client updates via GraphQL subscriptions.

Managed Hosting

• Free plan (2 projects i.e. 2 chains with 800 deployment hours i.e. live indexing)
  • This is only sufficient for development purposes and would not be sufficient for production usage since it only allows us to index 1 chain full-time.
  • This tier does not support advanced aggregation queries (unclear exactly what the limit is) or GraphQL subscriptions
• Standard plan ($146 minimum per month per chain - excludes 800 free hours reserved for development)
  • 800 free deployment hours then $0.20 per deployment hour
  • $0.10 per additional compute unit (it is unclear what a compute unit is e.g. vCPU and/or worker thread)
  • The total cost per hour = $0.20 + $0.12(total number of chains - 1) + $0.10(total number of compute units - 1)
  • For 1 chains with 1 compute unit we would pay $0.20 per hour or roughly $146. This excludes any additional compute units we would need to index initially.
  • This tier also does not support advanced aggregation queries (unclear exactly what the limit is) or GraphQL subscriptions
  • Once SubQuery supports historical indexing for multi-chain projects each additional chain will cost an additional $0.12 instead of $0.20.

Chain-Related functionality

Reserving Namespace

Existing

1. Txn to reserve a namespace executes
2. PATCH to /communities/:community_id
   • queries the chain to get namespace/namespace_address and saves it to Communities.namespace and Communities.namespace_address respectively.

Change

• Add a Communities.namespace_created_at timestamp column and a Communities.namespace_finalized boolean column
• If namespace_finalized is false and not null AND namespace_created_at is less than X hours/days in the past when querying a community (or schedule a cron job to execute in X hours/minutes via graphile-worker on namespace creation), query SubQuery NamespaceDeployed events to find a finalized (X confirmations) transaction with a matching namespace.
• If namespace_created_at is older than X hours/days ago, clear Communities.namespace, Communities.namespace_address and Communities.namespace_finalized.

Deploy Community Stakes

Existing

1. Txn to deploy stake ERC1155
2. POST to /communityStakes/:community_id/:stake_id
   • queries the chain to verify that community stakes is configured for the namespace using Communities.namespace_address and stake_id
   • Creates a CommunityStakes record in the database
   • Creates a group for community members that own stake

Change TBD

My Stake Transactions (or any txns list UI)

Existing

1. Community stake transactions indexed into the StakeTransactions table via CE v2
2. Client GET community.getStakeTransaction (v1 tRPC API) to get transaction history

Change

1. Index stake transactions in SubQuery using namespace address and wallet (from) address
2. Client queries SubQuery to get relevant transactions
3. Remove StakeTransactions table and community.getStakeTransaction route

Launch Contest

Existing

1. Create contest txn
2. Client POST contest.createContestMetadata
   • Creates a ContestManagers record
   • Creates ContestTopics records

Change

• Index contest (recurring and single) deployed events with SuqQuery. Ensure namespace is a property in the new model.
• Drop the ContestManagers table
• Client queries contests from SubQuery and ContestTopics as usual
  • If a contest manager doesn't exist in SubQuery but is expected from ContestTopics then delete the ContestTopics record since the contest no longer exists -> eventually consistent (this can also be done from the API - TBD)

Other Contest Projections

Existing

• Contests
• ContestActions

Change

• Replace Contests projection with an equivalent model in SubQuery that is created from the ContestStarted event
  • Replacing the calculated score column is TBD. Could include a combination of other indexed data. Reference: updateScore function
• Replace ContestActions projection with an equivalent model in SubQuery that is created from the ContestContentAdded or ContestContentUpvoted events

Notifications

Existing

• Community Stake trade notifications

Change

• Create a script which queries SubQuery and selects any new relevant records since the last time the script was executed -> emit events to Outbox (script can be used to capture chain-events into our pipeline for other uses)
• Execute the script every X minutes via Heroku scheduler (or think about replacing with graphile-worker).

Notes

• We require unfinalized blocks to provide a snappy user experience. Unfortunately, unfinalized blocks SubQuery feature depends on enabling historical indexing which is not yet possible for multi-chain projects. See this issue for more info. To work around this issue in the meantime we will maintain a separate project for each chain we support.
• Since a namespace is the root of all other on-chain interactions, it is crucial that the community <> namespace link is accurate (handle re-orgs that affect namespaces).
• We require support indexing contract deployments and events emitted from those contracts. SubQuery handles this via a dynamic data source feature called Templates.
• I provided a quick demo of SubQuery used to index community stake trade transactions to the platform team on Friday, August 9 and there were no major disagreements with using SubQuery at that time (still need to review the impact of changes as listed above though).

Next Steps

1. Decide whether to move forward with SubQuery or maintain in-house indexing. It seems that Alchemy is no longer an option given Growth's demands to expand to new chains like Linea and BSC.
2. If we decide to move forward with SubQuery, create new tickets for each of the above 'change' sections (include much more detail). Tackle the transition to SubQuery in small pieces by gradually transition specific features/sub-systems to use SubQuery.

[timolegros]

Bumping to reflect the amount of time this ticket really took. Creating the MVP took longer than anticipated since the MVP initially did not work due to caveats like:

• Type/build issues when projects are nested in the Commonwealth repository
• Cannot use historical indexing on multi-chain projects (found out through debugging/trial and error)
• Global type errors/missing packages in SubQuery starter-projects

While it may seem like a long time for a spike, using SubQuery would be a significant shift in how we interact with chain data so it warranted a deeper investigation to discover the caveats/underlying issues before diving into the deep end.

[timolegros]

We will not use SubQuery or its managed service until https://github.com/subquery/subql/issues/2493 and a follow-up ticket for unfinalized blocks in multichain projects are complete since the list of chains we need to index is growing and costs (both in the managed service and development) for separate SubQuery projects would become unsustainable.

[jnaviask]

Thanks for the research, Tim! I'll close this ticket as completed. Let's follow up once the subquery updates land.