Query and store old OpenBook orders into our DB

[LukasDeco]

We want to query these OpenBook programs and store the orders accordingly in our table. There are some functions and classes in the futarchy-indexer repo as well as accompanying tables that could allow this to be done with a lot of the initial setup taken care of.

You can use the transaction_watchers table with the Openbook Twap program account to index all transactions from this program. If you set the checked_up_to_slot column to 0, it should in theory re-index all of the transactions. You shouldn't get many new txns in the `transactions' table, but you will be able to run any new indexing logic you have built on these transactions.

Here are all the program account IDs you want "watch"/query transactions from:

twAP5sArq2vDS1mZCT7f4qRLwzTfHvf5Ay5R5Q5df1m
TWAPrdhADy2aTKN5iFZtNnkQYXERD9NvKjPFVPMSCNN
TWAP7frdvD3ia7TWc8e9SxZMmrpd2Yf3ifSPAHS8VG3

Definitely for staging, you will have to insert records in the transaction_watchers table with the checked_up_to_slot field at 0 and then set the first_tx_sig to null.

In the DB, if txns already are in there, you might need to delete those or maybe not.

An unfilled order record can be built from a placeOrder instruction based off the args: Max Base Lots = unfilled base amount Price Lots = quotePrice * 10 **6

a following cancelOrderByClientId ix will often let us know that the order is cancelled. BUT this will be tricky. We don't store the clientID of the order... so you might need to figure things out in a roundabout way. You might be able to use the openOrdersAccount pubkey on this tx to find the data you need. Here is an example: https://solscan.io/tx/3JbFRjRfXNNP1FDFE5zK1AwDzfuXw8qRNa8ULR6eSga76Tt46QseUesw2Q2xe2bYjm3z7rcqj1fQzh82pZ6soZaS

This is the openOrdersAccount tied to that txn: https://solscan.io/account/FSBrxyZWoaXGomRH2cyfAjHhfbzDGV9DdD2Q3gK7g2kA you can find the original placeOrders ix that created the account and that would be able to potentially tell you which order in our orders table you need to update.

Example program account for openbook on devnet: https://explorer.solana.com/address/TWAPrdhADy2aTKN5iFZtNnkQYXERD9NvKjPFVPMSCNN?cluster=devnet

[LukasDeco]

How to handle different IXs coming through:

• placeOrder -> (insert into orders table)
• cancelOrderByClientId -> mark order as cancelled

Arcana market: https://app.arcana.markets/data/CciLvCtXAkGS5TMKNRDYBPFzfWWL7fwCQi79NRx5kTHz

[LukasDeco]

Examples for how to index this by txns/accounts maybe: https://github.com/zhmoly/openbookv2-geyser-rs https://github.com/openbook-dex/scripts-v2 https://github.com/openbook-dex/openbook-v2 https://github.com/skynetcap/solanaj-programs https://github.com/Norbaeocystin/openbook-v2-trades https://github.com/Norbaeocystin/openbook-v2-trades/blob/main/openbookv2-printer/src/main.rs

[LukasDeco]

Examples of past proposals: https://docs.google.com/spreadsheets/d/1ZtUdWiWTavwdTHib6zHEb4xlRUgXRCnHwLb1DZdfSXo/edit?gid=1094152800#gid=1094152800

[LukasDeco]

Notes on convo with @R-K-H regarding actual trades(takes and makes):

• the openbook program itself has a consumeEvents ix that will tell us when that market is being cranked/orders are matched on it
• A great example of how logs on this program are consumed and the trades are indexed is here: https://github.com/Norbaeocystin/openbook-v2-trades/blob/main/openbookv2-printer/src/logs.rs
• consider trying that example out on one of our markets, like this $10 one: https://app.arcana.markets/data/CciLvCtXAkGS5TMKNRDYBPFzfWWL7fwCQi79NRx5kTHz see how you could possibly extract a volume of $10 from the matching logs hopefully logged here