Lightning OGs Dashboard

Scope

Lightning OGs analytics dashboard for our genesis wielders.

End product will be a dashboard at nft.friktion.fi hosted on a grafana dashboard similar to metrics.entropy.trade.

Features

  • Current Floor Price
  • Current Listed Count
  • SOL fees generated by project
  • Historical Floor price (SOL)
  • Historical Floor price (USDC)
  • Firehose of sales (table of price, time, link sorted by time desc)
  • Bonus Points
    • Number of original airdrop holders amongst current holders
    • Volt participation of active holders
    • ??? Any other cool complex features users would want to see about Lightning OGs

Flow

Goal:

  • Make the relevant API calls to MagicEden and on-chain to get the data.
  • Insert the data into a SQL database
  • Display the data via Grafana Dashboard that queries the SQL database

The DB and Grafana flow is already built out, so all we need you guys to do is retrieve the data, curate the relevant fields, and then write it to the DB for display on the dashboard.

# Example Workflow
import pandas as pd
import numpy as np
import asyncio
import psycopg2
from psycopg2.extras import execute_values
import traceback
import requests
import json
import sys

try:
    conn = psycopg2.connect(DB_CREDENTIALS)
    cur = conn.cursor()
except Exception as e:
    print("ERROR: Unable to connect to database:", traceback.print_exc())

async def fetchAndLoadUserTx(timeout):
		### Insert Relevant NFT data API calls here

    while True:

        def query():
            return f"""
            SELECT MAX("UnixTime") from user_transactions
            """

        def graphqlQuery(maxTs):
            endOfTime = int(1e12)
            return """
            https://solana-stream-dev-ztbl.ue1-eks-0.prod-czff.zettablock.dev/graphql?query={instructions(fromTs: %s, toTs: %s){Hash,Amount,UnixTime,Decimal,InstructionIndex,InnerIndex,Slot,UserAddress,Timestamp,UserAction,GlobalID,VaultAuthority,ShareTokenMint,DepositTokenSymbol,DepositTokenCoingeckoId}}
            """ % (
                maxTs,
                endOfTime,
            )

        cur.execute(query())
        rows = cur.fetchall()
        maxTs = rows[0][0] + 1

        new_tx = pd.DataFrame(
            json.loads(requests.get(graphqlQuery(maxTs)).content)["instructions"]
        )
        insert_data = tuple(tuple(x) for x in new_tx.values)
        print("Collecting new user stats...")
        print(new_tx.shape)

        x = execute_values(
            cur,
            """INSERT INTO user_transactions("Amount", "Decimal", "DepositTokenCoingeckoId", "DepositTokenSymbol", "GlobalID", "Hash", "InnerIndex",
                "InstructionIndex", "ShareTokenMint", "Slot", "Timestamp", "UnixTime", "UserAction", "UserAddress", "VaultAuthority") VALUES %s""",
            insert_data,
        )
        conn.commit()
        # ("Amount", "Decimal", "DepositTokenCoingeckoId", "DepositTokenSymbol", "GlobalID", "Hash", "InnerIndex",
        # "InstructionIndex", "ShareTokenMint", "Slot", "Timestamp", "UnixTime", "UserAction", "UserAddress", "VaultAuthority")
        print(f"User Transactions loaded. Sleeping for {timeout} seconds")
        sys.stdout.flush()
        await asyncio.sleep(timeout)

async def main():
    await fetchAndLoadUserTx(15)

if __name__ == "__main__":
    asyncio.run(main())

Bounty

  • 100 USDC after completion of fully productionalized result (Galileo will help out)
  • If Bonus goals are achieved : 2 Lightning OGs, 100 USDC (Partial credit awarded)
2 Likes

I might be up to do this. Since the DB is already built out, could you share the database schema I should use?

1 Like

The table isn’t built yet, up to you decide the fields for the Postgresql DB.
Once you decide on the schema (maybe just specify timestamp, integer, integer, string, etc…), I’ll help you get the table schema created and insert it into the DB and read it from grafana from our side.
Basically all we need for the grant is the request from the relevant APIs and documentation on the resulting format.

I may know a few analysts who could make this happen =]

@Galileo can you confirm this is still open?

This grant proposal is still open!

1 Like