Blockchain Analytics 101: Building an Indexer from Scratch by Benjamin Memisevic | Devcon Bogotá

foreign [Music] contract engineer being in the space since about 2018. today I'm basically going to do a tutorial where we'll build through a simple blockchain indexer and just sort of explain how indexes work different pipelines you can design and make and in general just interacting with Geth on a sort of obviously level okay uh just some background quickly about me and how I would love to run the presentation in general um yeah so as I mentioned before I've been in the space since 2018 I used to work at a consultant in labyrus which is an Australian uh smart contract consultancy and then I worked as an engineer at Tracer Dao in mycelium which is Perpetual swaps uh protocol on Arbitron um yeah I was the team lead at reputation dial which was a project at mycelium where we focused on indexing and monitoring article systems on chain to make sure Oracle was made uh well essentially held accountable and also that they're functioning properly and accurately so sort of just detecting uh errors sort of in that pipeline oh um so in terms of my experiencing in the indexing space I've been working in indexing for about two years um I'd like to know what I'm talking about but I guess we'll see uh in terms of questions please just like shoot up your hand if you have anything as I go um okay ether scan uh either scan is probably the most popular indexing tool all around um I've spent a lot of time on an etherscan it's basically a hobby of mine I'll click through random blocks and see if there's something fun uh if you've ever accidentally sent eth to a contract instead of calling the function and you think nobody saw it I saw it uh so this is one Trends oh there's a screen here uh this is just one address that I found this morning in a block and it's essentially an eoa that seems to be Fanning out Eve to a bunch of addresses extremely quickly so it's sending out full five transactions trying to spread out I think about 0.8 eth um yeah but it's really worthwhile if you haven't done that so sort of explore what's underneath the scan and see how interactions are happening on June okay so observability and transparency I know at the moment uh ZK and privacy and transactions is highly important and that's absolutely true and if you want privacy in your transactions oh go for it uh but on the other side observability is also highly important um just as a question does anybody have any pros that they think having transparent transactions have over ZK uh yeah no corruption exactly nice and uh I'm not trying to just Shield transparency don't worry I don't work for the SEC or anything like that I'm not after the taxes um but it's still highly important in certain systems uh oracles which I work for don't really function on zero knowledge you have to know that your oracles are operating properly and you have to know what each Oracle is submitting uh having anonymity there reduces the accountability of those oracles and it can cause your systems to not function the way you hope if you uh you know want bad Oracle transaction can completely crash the market especially derivative markets foreign this is actually one example here also from this morning um so mango markets lost 100 million dollars uh last night because of Oracle manipulation so making sure that you can observe what's happening and also the exposure that those oracles have onto the each market is very important and something where transparency and building an indexer is highly useful okay what actually ends an indexer foreign the way I Define an indexer and the way I like to think about it is it's basically an ETL program where your back end is communicating directly with the node you're requesting data from the blockchain and you are converting that data or storing it in a unique way that makes it more accessible or useful to you um yeah sorry I'm just run into here so uh why would I actually want to store it differently than on chain because you know the blockchain is a database in and of itself all the data is always there the problem becomes though if I want to retrieve specific data or a range of data or God forbid create some sort of average across it so if I wanted to do that I might have to make hundreds of equals to process one transaction or one event that I defined on chain um so one really popular reason to make an indexer which a lot of now RPC sort of services like Alchemy and coinbase support is to fetch the transaction history of an address that is not possible directly when you're directly interacting with the node you would have to pass through every single block that uh that address has made a transaction on and then manually look at those transactions another really good reason is uh non-effemeral data on the chain so mempool data uh anything that you run through as a test is an eth call doesn't stay on the blockchain forever mempool data in particular is highly useful for arbitrage strategy but there's actually no consistent history of the mempool um obviously manpul also differs from node to node but having a history from a range of nodes is highly useful if you want to sort of background strategies that you could have done uh if you're an arbitrator uh yeah also a gas estimation as well if you're making eth calls that don't actually go through chain you want to have a history of how your contracts will look when they're finally deployed creating a mini indexer that runs off your localhost smart contract known is absolutely a valid option uh now I just want to quickly break down a log uh that like happens on train quite frequently so this is what happens when a chain link Oracle submits oh well lots of chain link articles actually submit an answer for a price feed on chain so this is the decoded version I'll show you the encoded version a little bit later on um but even here not everything is super obvious to the eye right so you have the answer at the top which is obviously the new value of that price feed will have uh for those curious this is the price of one inch um but there's other points that are really confusing right if you look there's observations where you have what seems to appear as a intera right and you have observers which is just this confusing byte array with a weird name you have raw report context um let's sort of just look at what's going on okay so step one to creating any indexer creating any indexer sorry or um sort of observation solution is first you have to understand the contracts that you're working with what your ETL really does and what it should aim to do is to take this raw like hex data on chain and give it new meaning or new accessibility and to do that you have to know what the contracts are doing already just copying the data across isn't really enough so sort of on that talk on that point sorry I'll just quickly explain how OCR works that was the chain link log that we just looked at previously so how chain link operates nowadays is that also I don't work for chain link I just want to say um how trainlink operates now is that most of the aggregation and collection of data happens off chain so uh a price deviation will happen all these nodes will look at their different apis and they'll send all their answers to a single node and then this node will submit all of these answers and the final aggregated answer on chain on behalf of everybody else who's supporting that price for you so sort of knowing that um I'll also just go through a simple timeline so as an example say I have a chain link feed that's monitoring the price of ethereum when the price of ethereum dips by certain percentage all the chain link nodes will know and they'll start a new round every every single chain link node will then send an answer that they've collected from their apis to a leader that leader will aggregate that answer normally taking a medium of some sort he'll pass this along to the elected transmitter for that round and then that transmitter will send off a message on chain containing all the information that everybody submitted individually as well as the final answer and why I sort of went through this is by knowing this information knowing how the system works we can now make sense of the log that we looked at earlier okay so believe it or not this observers array right here uh byte array is actually a list of the addresses that each of these observations come from so uh if you all those numbers there's a corresponding Oracle address of the Oracle that submitted that answer and that answer is the number that they thought is true another thing to note is that number is very large um yeah if that number was exactly how it is I would be retired as holding like 51 inch tokens but here I am so this is sort of another step we have to overcome so we're sort of missing more information but we know it's available somewhere within the contract so this information is available through a view function in the chain link aggregator contract called transmitters which returns a list uh which returns a list of the addresses of each article that is supplying that contract with data and there's another function called decimals which tells us by how much I should Define divide those numbers those observations to get the uh accurate like human readable price of one inch in this case the problem though is that these variables can change block by block these are adjustable variables transmitter has changed all the time uh not only for security reasons but sometimes uh certain articles are better at performing on certain feeds than others and decimals can change when different markets require more Precision out of a price feed okay so this is sort of how we break it down so I had my uh observations arrayed there and the way to break it down um and you can find this out by sort of reading the contract and then doing a bit of guesswork is that if I look at that byte array and I split it apart into pairs each of those pairs can be decoded from hex into a number this number represents the index of which uh that observation in order corresponds to which item in that transmitters array um the observation was from so in this case the second observation here was from the second article so we're indexing from Counting from zero so zero X zero three one two e a dot dot dot responded with four three nine three nine nine um so already we're sort of getting kind of complex and how we're just deciphering this one single look sir um we'll go through the steps and code as well how we would do all the different calls but essentially at the minimum what we have to do to fetch this data is first we have to encounter this chain link log then we have to pass the data back into regular data types take out transmitters buy it array break it up into pairs convert that into hex to get a number call the transmitters function with an eth call and then pass out which address is with which address now I'm doing this for One log not too bad I probably wouldn't want to do it by hand but imagine if you wanted some sort of aggregate data imagine if I told you okay tell me how accurate this Oracle was on every single Wednesday for the past six months now you're passing hundreds of logs you're making thousands of calls and if you want this to be done on demand it's going to be incredibly slow it might take minutes uh on the other side on the other hand sorry I could make like an SQL statement like select average where Oracle equals this where time equals this and this I'm converting hundreds of lines of code thousands of uh like HTTP requests to one line in SQL that's sort of the value that indexing can bring uh before we actually get into the indexer I also just want to go through the data types ethereum has so these are sort of uh our Avenues into getting different data thank you sir there's actually more than this but this is just a quick summary so uh does anybody know by heart what things that you can search on etherscan well any like blocks contract addresses any others okay yeah transaction hashes yeah so transaction hashes addresses block number block hash um sorry ens names yes nice no you cannot uh not in within the um like top search bar yes um so these things are the things that uh ethereum will naturally index so you can already select these from the node relatively quickly and they each have a relationship with each other if I have the log I can retrieve the contract address that that log was emitted from and I can also retrieve the transaction that that log came from and then through that I can also retrieve the block number that that transaction happened on and so on um and receipts as well so transaction receipts so you can see how much gas was paid for that receipt but the point here is that you're sort of migrating across these different data types to collect all the information you need for your index [Music] so dissecting a log um I'm just going to go over the structure of logs and for those curious the reason why I'm concentrating on logs is that there's quite a lot of functionality within the get client with blockchain clients in general um for creating these indexers based on logs uh creating them based on transactions is also possible but a lot more manual you have less sort of filtering power on that first step um so how log works so logs are constructed out of topics and data essentially so topics are also data topics one two three are data types that you can emit in your event and mark them as indexed and why this exists is that uh instead of building an indexer I could potentially just use the blockchain client as well to look for uh cases where the data is equal to a particular amount so I could tell Geth tell me when topic zero is equal to X and topic one is equal to this and retrieve all those cases for me within some block range um topic zero I'll go over in a moment uh the data uh sorry something to also known on topics is that you can only index up to three fields data though you can throw as much stuff as you want in there um and this is like highly valuable and a good way to think of logs and data in general is sort of like a print statement for your smart contracts so if you've ever like debugging and uh you know we're all programmers here and we hate using the actual debug tools we just print hello um this is exactly what a lug is [Music] um yeah so we also have the transaction index which is at what point our transaction appeared within that block and also log index which is separate which is where this log appeared within that block which is normally completely different uh this also removed which is like a bull that just signifies if our log was removed from the canonical chain due to a reorganization topic zero topic zero is highly useful um topic zero defines the log I would say so what topic zero is is I take my event definition so say I have an event called uh transfer and it takes a uint as an argument um I want to look at every single instance on the blockchain where this transfer event happened and to do that I have to calculate topic zero and to do that I take the kikak of my event name so transfer and then each data type that is within that event definition so here it would be you know un256 or uh whatever um uh some important things to note you do not include uh and I learned this painfully by experience do not put spaces between the types because your hash will be wrong and also don't put the name of the variables within the type because the evm doesn't care about that so something to note as well is that this topic zero is unique for every single contract that is to say that you can only have one event with one exact hash definition per contract but this is not stipulated across the whole chain so I could potentially say look for every single log with topic zero but I might actually collect data for four or five contracts that are doing a completely separate thing that's just something to be wary of [Music] storage another really cool thing that you can do uh when you're building an indexer is accessing the actual contract storage so private variables now become completely accessible to you uh this is really good um one of the main use cases that I've certainly used this for is when a contract is implementing uh EIP 1967 which is the proxy pattern so you have the contract that's implementing all the calls from another contract and that contract's address that's actually got the implementation is always in a specific storage slot um I can go into this example later as well but this is just one hugely powerful example of stuff you can access if you're building your own indexer okay infrastructure design so this is a bit of a web 2 thing but um it's important to think about how you want your indexing application to run you can make it as complex or as simple as you want so if it's just a regular ETL you can have your program connected to a node and then you're inserting into a database but you can do so much more you can create a hackathon submission that is looking for events on lens for example and then I'm sending out alerts through epns every single time someone's profile gets locked I could take uh you know I could scale my application I could have several nodes have a load balancer between it so I'm not overwhelming any single mode I could be sending off my messages after I've dealt with them to an AI analysis and I could be storing them across several database nodes um stuff I've worked with personally that I recommend is a sort of microservice architecture you can have Kafka sort of in the middle as a message broker and pause information between all your services all your different storage points uh maybe you're an arbitrager um and you want to do sort of an analysis through that analysis inside uh in-memory database like readers for quit act for quick access while simultane simultaneously uh throwing any archive data into your own sort of postgres database [Music] database options um so indexing is really powerful because you can choose how quickly you want to access what subset of data so these are just like a few I threw up so time scale GB is extremely interesting um but any time series database sort of proves this point the blockchain doesn't give you an option directly to look through transactions within time range you can specify a block range but that's not really always ideal blocks take a different amount of time to create over time and you have to do sort of extra leg work there if you want to convert from block to time um time scale off is something really awesome especially when you're looking at a large data set uh uh like a blockchain and that's continuous Aggregates and these are materialized views that you can create for aggregate data so uh for example I can calculate the average every single week of how much gas is spent on ethereum and I can permanently store that data in a view and access it instantly as opposed to having to recalculate that data kdb is very popular within sort of the Mev Community from what I've seen it's a completely in-memory database it's extremely fast so similar along the lines of readers but far more performance it's used a lot by Quant firms Arbitrage uh and also uh Formula One um like racing so they use it for in race analysis and stuff like that postgres uh absolute classic completely free perfect audio system why would you use anything else don't talk to me about MySQL I hate it uh okay now I just want to go through a quick Code walkthrough of a simple application so we can actually build these index areas in about 150 lines of go code easy okay so first I want to explain my language Choice um because I've been contested on it in the past I really like using go for the back end and interacting directly with Geth and with the Geth Library um like first of all Geth is a go is fast Geth is probably the most well maintained uh Library it obviously doesn't have features of certain other um blockchain ethereum clients but it is definitely the most well maintained and it always fits through the specs the yellow paper specifications uh go has a huge uh bonus and that parallelization is directly built into the language so we're going to be doing a lot of calls uh through web circuits or through HTTP and we want to paralyze these and we don't have to worry about race conditions when we're using go um yeah another huge bonus is that all of these functions that we're going to call to extract data our program is going to be completely portable to every single evm chain except for memory xdi so we can Port our application uh make it as generic as possible and we can start looking at events on polygon uh polygon ethereum optimism arbitrum whatever you want without having to do any extra legwork it's like deploying a smart contract to multiple chains uh and the reason this is is uh all these RPC calls that we're making are specified within the yellow paper so any evm client that is being created is going to have to fit through these specs okay creating a client um this is like pretty basic code so I'm just creating a HTTP client I'm feeding in my Alchemy uh sort of key that's what the RPC thing is there it's just a string I'm doing a bit of error handling here making sure that I am in fact connected and in fact connecting and uh that's really it for this um there's really not too much to worry about here yes okay that's a good question uh I'll just repeat it um so the question was is should you run your own node infrastructure or use an outsourced node like Alchemy so there's several advantages to running your own um and a few disadvantages running your own node Beyond you just have direct access and you can manage all your load yourself uh you can also put other sort of add-ons on top of that node on top you can put add-ons on top of that node to make the indexing even faster um so when you're doing retrieval you're sort of indexing already on an index which is fantastic um me personally uh if I'm running in a production environment and I have an application that's fully running as an indexer and my system is depending on it then I would run my own node um I see some like node operators here and I feel that um but full hackathon projects absolutely use alchemy um fantastic solution not just Alchemy uh and any inferior whatever I'm not sponsored guys I'm sorry I keep using the same names uh websockets versus HTTP um so most providers will allow you the option of accessing their service to a websocket or HTTP um I'm just going to go over this quickly because it's more of a web 2 thing um HTTP is normally how your wallet connects to the chain so whenever I'm making a request my wallet is creating that TLC connection and it's shooting back a message and that connection is then closed when you're creating an indexer you're going to want to make lots and lots of requests and that extra extra latency between um creating a new handshake every single time is actually quite cumbersome I would just say uh straight up that if you can use a websocket connection always use a websocket connection um no reason not to really okay uh now we're going to look how to create a query and this is an SQL by query I mean we're going to query data directly from the node so uh there's this ethereum uh get object called filter query and it does exactly what you think it does it takes an array of addresses from which I want to collect logs from and it takes a list of topics uh so before we talked about topics and how you can index up to three and you have topic zero um so you can specify here I want to collect data from which topic zero and uh which other topics I want to be equal to some hex value and I sort of just declare this object and that's all you really need from here um we haven't actually made the call yet this is just constructing the query uh this also takes a block range which I didn't include for some reason um next okay so now we're actually making the query and this is where block range becomes very important so there's two ways in which I can request data from a blockchain node I can do subscribe filter logs which is I'm establishing a connection to the node and I'm telling the node okay here are my parameters whenever this happens on chain send me a message so this is really useful because now I can sort of distribute my load uh from the node because I get these messages one at a time I can process them and as long as my application is at least decently efficient I'll process everything without any lag uh now this always isn't an option because uh one thing about throw uh subscribe filter logs is that I can't request historical information I will only be receiving data from the point I've subscribed so from the next block that's coming now I can also do filter logs in which I can request a bunch of logs that have already happened um so that's essentially what that does uh it's really useful especially uh if you want to do a historical analysis um the thing to be wary here is that it is like quite hard on the note if you're asking a retrieval of you know megabytes or gigabytes of data and also your application is going to have to keep time as well um definitely super expensive uh if you want to collect the entire history for a particular log throughout the whole chain okay channels um this is a go thing um but it's also extremely important to know and it's one of the reasons that I suggest making these index applications and go so what a channel is and goes is essentially like a pipe so if I'm sending data from a process to B process I use a pipe but there's certain issues associated with um doing this right so you have to deal with race conditions for one which is a huge headache also piping can be quite messy I don't know how you're doing it but you can do it like through a bash script it can get pretty weird what go offers is channels and channels are naturally blocking so I don't have to worry about message one getting there before message three and screwing up all my analysis the channel will only send a message across to my next process when that process is ready to receive the message um and this for Loop here was sort of declaring an infinite for Loop and using the select statement and the select statement is just saying uh I've created this channel logs one and I'm waiting continuously until something is sent to that channel and uh the uh blockchain node is sending data to the channel and I will pull out uh that message as soon as it comes as select is just saying uh whichever one comes first so if my channel comes back with an error I'll deal with that and crash my program or if a log comes first I'll go and do some processing foreign how do I actually process data um so when I request data from the blockchain and I get logs back I actually get back a pretty messy data structure um not messy but not human readable so what I'm going to get back is logs data structure which we went over earlier I'm going to have these topics and I'm going to have the data field inside the log uh and if I'm pulling the transaction similarly I will have the data field within uh the transaction field which is identical to the sort of log data now uh passing this across is pretty complex so there's natural pot padding that happens from rlp encoded values and I also could convert from hex back into sort of regular values at least for go to interpret but also uh so humans can interpret it I don't really know what's happening when somebody says oh the value came back as like 0x 60 zeros and then a three uh so what we can do instead is we can generate an ABI and this is exactly how etherscan does it and um for those not familiar and ABI is a specification of all the functions and all the events that happen on a particular contract and I can generate this by uh going to a contract and using cell C so that's the command at the top uh so I'll see ABI and the name of the file or the path sorry and then I'll get a huge spit out of a Json file and we'll use this and Define it as a Sprint as a string and we'll use it further along but the API is highly useful but something to note is that it does not directly appear on chain um on chain is only sort of the compiled like bytecode so the ABI you have to get by having access to the source code or sometimes people upload it to ether's Camp um and what the API allows us to do is the ABI knows uh as I described before with the CAC it knows all the input types for all the functions and all the events that are defined within that contract so now I can use the evm to oh sorry it's plugged in [Music] it should come back up in a moment um okay oh it is back fantastic so I can use this API and use the evm to decompile all of this hex data that I'm going to get blurted out back into regular data types so creating an API object um similar to the JavaScript concept but I'm going to call this API function which is also a API Library sorry that's also part of the GIF module and I'm going to pass in the ABI string that I defined previously I'm going to check that it is in fact a valid string which is important and then I can start unpacking data and that's that second sort of code Block in there I call my object I tell it to unpack I pass it in a string which is the name of my function and then I pass in my data and what it's going to spit out is a huge array of interfaces and interfaces are just generics and go so it's a it's data that I don't know the type of and I have to just tell go which data type that is and I can find that out by looking at the Smart contract code I can find that out by looking at the API and even if I make a mistake here go will tell me that I made a mistake if I try to assert wrongly that it enters a string go will tell me I'll um actually you want to type assert to a string um I don't know why I can't just do it automatically but life can't be that easy um okay working with a database okay so now essentially what we've constructed is we have a system that's requesting data from the node either live or through uh through a historical query I've collected that data I now have a method to unpack that data into regular go data types and I can convert between them as I wish now I want to insert that data somewhere so uh this is a pretty standard way of just interacting with a database and go uh completely fault proof um and it's using a library called Gom so go RM it's a used to be sponsored by chain link actually as well um pretty fantastic Library highly recommended uh so it's very easy for what I have to do I just Define a struct which has what my tape what I want my table to look like I throw on some strings where I want my primary keys or foreign keys or indexes and uh I'll just leave that in the module by itself and then what I'll do is I'll uh initiate a connection to my database and it's also just this one line code so I just tell it to open um I have postgres listed here but it has support for most popular uh database Management Systems and uh Gom config which you can specify different things in but we can just leave it blank if you're not doing something fancy you can pretty much leave it alone I do some error tracking and then I've got migrations for those unfamiliar migrations are fantastic if you've ever worked for a production system and you've wanted changes in your database and you basically it's like a nuclear reactor like two people have to turn their keys at the same time to edit a table migration sort of sidestep that what gorm will do is I can pass in the my uh structs that I defined before and it'll automatically create or edit the tables that I already have um so that way the code that I have is going to be exactly represented in my database um inserting so we're pretty much like done here guys um all I have to do now is use the structs that I previously defined pass in my decoded values and then I just have this one line um insert or update on conflict so what this is essentially doing is I'm sending a message and it'll be acid depending on your database of choice I guess uh out to the database and I'm telling it okay um if there's no entry inserted if there is updated um and updating actually absolutely can happen although the blockchain is immutable uh remember that the node is getting new data all the time reorgs do happen so something you want to watch out for is your block hash changing uh and also your block times down um profile idb oh sorry we just have like type assertion here so you can sort of see how these are getting paused back into the struct so I have my profile ID and this is an example that I did based off lens um so profile IG and lens is an integer which represents uh your user on lens and then also nft which is an nft that's generated on lens whenever you follow somebody and I just passed these into that struct I'm calling my uh uh sorry database dot Clauses update function and I'm passing in a pointer to the struct okay um so that's basically it I can go through some like actual examples as well but I just want to do a q a um actually how much time do I have left got plenty of time um yeah oh okay um so EIP 1967 basically says uh it's a proxy pattern so say I want to make changes to a Smart contract um you obviously can't really do that so what I can do is I can define a vip1967 contract and tell it do all the functions of another smart contract and I can change which smart contract is uh which my contract logic is actually being changed by just changing that variable but the problem is is that there's no like view function for the contract address that's uh on that 1967 contract but the storage slot where that address is stored is always the same across any uh EIP 1967 contract so I can retrieve this address and then I can query that contract for any information that I want uh does that help okay uh yes [Music] yes I will um I wanted to clean up a bit but it's uh on it's submitted within the if hackathon I can send the link out uh if I'll send it out on my Twitter which is at the end [Music] yeah I'll just repeat the question but uh the question is how many block confirmations do you wait before inserting the data into the database or how do you deal with updates so the great thing about the system is that you don't have to wait at all every time there's a new reorganization the logs will be uh like re put through the node and then uh the indexer will automatically update that column for you so the data you have in your database will always be the most accurate that it can be and most up-to-date sorry yes um [Music] why is it volunteering um I mean it costs gas to emit events um why isn't it standardized I mean it is standardized in development it's not like you have to deploy a contract with events um and if there is contracts deployed with that events you can also create an indexer based on that I think that's what you're getting at or yeah oh yeah okay for sure um sorry can you repeat the question oh [Music] okay um y indexes mostly use logs uh that's also I want to say more my opinion than true fact but also our most popular uh index platforms so uh the graph and stuff focus on logs quite a bit and sort of why there's so many blogs and you have to find the useful ones that information is hard to come by and if you want to create your indexer as I said you should know the contracts and the system but also you can make a general solution like the graph or like uh oh I forgot the name um oh can someone help me their logos like June analytics thank you like June analytics where I can do an SQL query on absolutely anything um but in terms of why there's so many events happening uh a lot of these events are just shut out for debugging purposes and they're left in the contract and you sort of have to decipher as you go um any other questions yes yes thank you contract state yeah yeah um so as before as I went like through storage so if you wanna uh if you want to get contracts staged there's three main things that you can do so there's access through variables um so every single public variable on a contract automatically has a getter assigned to it so that's one way where you can access State the second one is through storage um as I mentioned before so it's kind of tricky to find out exactly where a variable is in storage but if you go on the solidity um uh documentation page they will actually walk you through uh how storage is structured based on the contract and you can sort of mull your way through and find the variable the third is traces so traces are the individual op codes that a contract is pushing which you can also request from the node and uh with combination of those three you can see absolutely everything that a contract does um in terms of sort of creating strategies based on collecting that state I think the best indexers are ones uh well that you make yourself and they're purpose-built and you're traversing across these different data types to create sort of your perfect arrangement of data um so I built one on top of Avo a while ago and that can be was like relatively tricky so they have a EIP 1967 pattern where accessing storage to find that contract that contract is a pointer to other contracts I'm doing an eth call to access the variables to find those other contracts and then I'm calling balance to find each balance of those contracts uh I'm also utilizing storage um yeah stuff like that and then you just have a really neat one row item in your database for what you want sorry yes closure would you notice um do you mean deploying the same contract on two different chains or okay um so yeah that is actually important to note so if somebody like destructs a contract yes okay if somebody self-destructure contract I'll just repeat the question um if somebody self-destructs a contract I believe it's possible for another contract to appear on that identical address and that can have different cards so it can potentially break your indexer uh what you can do there to solve that system is you can have a micro service looking for Destruction transactions and matching them up doing a query in your database to see if that's affecting one of your indexers and then making changes to that indexer live so I'll normally do that through a microservice architecture so I'd have one looking for destructs if it destructs happen send a message adjust or uh cancel my indexer for a period the great thing about the blockchain is the dot is always there so even if you miss a few logs you can do that historical query and rebuild your data set uh yes [Music] yeah sure um the advantages are sort of okay so specifically to the graph I would say is that uh there isn't a lot of functionality I believe but please do correct me if I Wrong to do queries across different subgraphs at least currently okay um that was definitely one use case when I built them incredible oh okay um I mean okay so number one I would say in general is latency you don't have to wait on any graph note operators to fill up the graph for you second of all uh I can create a graph that is what a graph sorry a data set that's infinitely more complex uh than what I could do I mean I can theoretically do anything on the graph but here I have very close knit control of exactly what my code is doing and what I'm going uh what I'm putting through uh sort of third I have control of my own infrastructure so I'm not dependent uh the graph is decentralized in a fantastic protocol um but having your own data set with your own database that you know exactly what's happening on is extremely useful um fourth uh you don't have to pay it's free um yeah I guess that's like some of them um but really my main pitch for it is that this isn't that much code it's not expensive to run it's mainly really just doing HTTP calls you can run this for free on any cloud provider on like the E2 micro or whatever the AWS equivalent is you can run it on the free tier Super Bass has free postgres databases you're running your own infrastructure uh creating your own dap um and it has no cost to you whatsoever uh yeah anyone else yes um [Music] [Music] so uh yes there is a way to handle this and uh you can subscribe new so there's a function called subscribe new head inside the get client and every single time there's a new block header or an uncle you'll get a message there so you can make a service where you're looking for uncles it'll give you the information you need and then you can uh look at what that Uncle did and it should produce new events and it should tell you like the removed tag for the event for the lot so you can retrieve so when your block hash gets Uncle like uh let's just assume that you detected properly through subscribe new head you can re-request that block through the block hash and then it'll give you uh inside the log field a removed field which is a ball to tell you if that log has been deleted or not and if it has been deleted then you can mock that in your database you can delete the row or you could it depends on your private key um sorry not private key uh your primary key setup you can delete it or change that variable to true that it has been removed to mark that it's not uh valuable okay looks like we might throw a rave here instead uh yeah anyone else yes sort of Hardware that you need um it's so I'll say like for a couple different components uh from a database point of view absolutely um it depends what sort of service you're running uh how quickly you want to retrieve that data but in terms of storage uh I don't know I would say maybe six seven hundred gigs uh for the database the more difficult component is if you want to do that historically for every single block number you're going to run into hiccups with having nodes uh requesting that data from nodes so that's going to be very expensive if you're doing that through Alchemy or inferior or one of those node subscribers where you're paying per call and if you're running your own node I would say one isn't enough if you wanted to finish like this Century so what I would do is run you know whatever 20 nodes uh put uh hook them all up through historical data so you can use snapshots now so it's nice and easy to set that up and then have a load balancer and distribute out your calls into the different notes and that's probably the quickest way to do it uh in terms of the actual indexing um pretty lightweight I wouldn't really worry about it the way I normally run it is have a kubernetes cluster and then just Auto scale as I need it uh yes [Music] okay um okay uh it really depends what your application is doing but what I would say is that uh uh for long-term use I would recommend a relational database system so SQL um just because you can get a lot more use out of it and if you're storing the data long term you probably are imagining some use cases that you don't have yet nosql I would uh suggest for when you need really quick retrieval of that data um but in terms of sort of data strategy a pretty common one that I've been successful with is uh time partitioning my database um for those who are not familiar uh partitioning is sort of uh imagine you have a CSV and you have a huge Excel file now instead of having this huge Excel file that I have to search through I break out the Excel file into like five different files and I Define them by a range and now I can just search through that range for a particular transaction so there's lots of time partition databases but I would time partition your data if that's relevant and then create indexes um on everything the regular blockchain does and also anything of direct interest to you so just like a concrete index so whatever you want like block number plus the value of some variable plus like topic zero hash for example um also another good thing to do is to break apart uh into different tables your events um yeah I think that sort of goes for best retrieval um obviously also if your database is getting huge um also separated out into different nodes so you're not paying uh for like vertical scaling um oh the other one I didn't mention is graph databases I haven't seen that much use for them um I haven't used them myself sorry but that's also an extremely powerful way to store your blockchain data so I can see the relationships between different addresses or I can see the relationship between for example like oracles and the Ave Market um yes sing for indexing um so the different channels uh I mean it's paralyzation but essentially I don't have to wait for one request to get through before I do the other one so it's very very useful so I'd say if you're doing the Subscribe filter logs method when you're getting one event at a time you're probably absolutely fine without using a routine but if I'm doing the filter log method and I'm requesting a huge amount of logs at the same time and I have additional calls to do on each of those that's where parallelization will really help you out uh you'll be able to make you know thousands millions of calls at the same time instead of one at a time you're going to be cutting down the amount of time you need to index from uh whatever weeks to hours foreign yeah you can insert more than one row at the same time I could run uh multiple index programs for example from like block range zero to 100 100 to 200 300 to 400 and they can all insert separately so I'm finishing that index far quicker than if I had to go from zero to four hundred on one routine or one program oh yes um yeah I mean look node providers are fantastic and highly professional so uh you're going to get amazing Service uh and you're not going to really experience much delays there um but you can set up your node in gcp and you should not really have too much more of a delay uh the bigger risk of running your own node is if you make a mistake or you're missing a Geth update a lot of the manual stuff you have to do around managing your node is automatically done by node providers while you have to take care of those operations yourself see something okay uh mental data yes uh are you trying to do like Arbitrage or something somewhere okay uh one thing that you can do is that you can run multiple nodes in different regions because the mempool is not necessarily synced between all nodes at the same time so a popular setup is to run three or four nodes in different regions and they'll have different peers and then you can congregate that data uh yourself like through another process and that way you'll get a much more uh complete view of the mempool at like block execution time yes [Music] um sorry I have like five more minutes uh anyone else okay fantastic um yeah thank you for listening uh here are my details oh thank you