TX History API - Githubissues

I implemented a fairly rough tx history requests, but I'm not too sure about the implementation details, so I'm using this PR to get some feedback.

One of the fundamental issues was that a transactions have three dimensions, which meant splitting them up into three tables:

pn_transaction_history this is the factom entryhash itself
pn_transaction_history_action this corresponds to one of the transactions inside the txbatch
pn_transaction_history_action_data this is one of the actions inside a transaction. for conversions there will only be a single one, for transfers there can be > 1

This table gets populated while syncing with all the relevant data (there is some redundancy for sake of ease of use). When a transaction is applied, the row's executed value is updated with the height at which it's updated. if there's a balance error, it sets executed to -1. pending conversions are 0.

The API endpoint get-transaction-status returns height/executed for a single entryhash. The endpoint get-transactions takes a number of parameters:

either entryhash, address, or height. this gets all of the actions for either one entry, one address, or for a specific height. address is both recipient and sender
offset: a value that indicates there are more records. if the result's nextoffset is > 0, you can call the same query again but using that value for offset
desc: bool. if true it returns results in reverse order

Now the kicker is that this will get individual transactions, not batches. Meaning if a batch has a transaction that sends PEG from one address to 50, it counts those as 50 transactions.

count: the total number of transactions for this api call
nextoffset: the number to use for the next call's offset
actions: array of actions (up to 50)

One action is a json object of:

hash: the entryhash
height: height of the entry
timestamp: the time of the entry
executed: block the transaction was executed, -1 if failed, 0 if pending
txindex: the transaction's index inside the batch
txaction: 0 for transfer, 1 for conversion
actionindex: the individual action index in a multi-output transfer (always 0 for conversions)
fromaddress: sending address
fromasset: sent asset type
fromamount: sent amount
toaddress: recipient (always same as fromaddress for conversions)
toasset: target asset type (always the same as fromasset for transfers)
toamount: the target amount (calculated using the actual rate for conversions)

i'm not too sure about this. it's possible to remove actionindex, and just make it a list of outputs

Example:

curl --data-binary '{"jsonrpc": "2.0", "id": 0, "method": "get-transactions", "params": {"entryhash": "5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08"}}' -H 'content-type:text/plain;' http://localhost:8070/v2

{"jsonrpc":"2.0","result":{"actions":[{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":0,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA24rGQ6RRSecbZX9UkWQj9JmLAo4aJAJKVHCqoEc49rLkSfup3Q","toasset":"PEG","toamount":510452044439},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":1,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA22ipp34No2NwDtPXfMe9JLu4v3HhvmWfpecobQZbRUasHh22Ao","toasset":"PEG","toamount":11090569785405},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":2,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA3Mqp479YXyFRCeYg2mJsV7vdVsrNtcGP9cYTktbfEHa8UZVqVk","toasset":"PEG","toamount":1565760803531},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":3,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2S7Phu67QgBjyzorjgM1A3bhjJN7jGvWmsz6LKXcV37DTv7kYJ","toasset":"PEG","toamount":4412127628365},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":4,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2v54DsgmPxy8DtrdTBWwj2SbvB27JH3gkCPNsQ3HBd84QKv8Zh","toasset":"PEG","toamount":59275247799160},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":5,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA3T9ExTxzBEunxyfhj3Z5coZvJXBjLczzyByHem7k9bgt3PAc4P","toasset":"PEG","toamount":707115444701},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":6,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2qi9D6Y3hxrCrSjTxbLYndkGgRgkEwMuyaf8PvWy5Hvp1iwWiB","toasset":"PEG","toamount":5653129294939},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":7,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2zCbYNHa24RMt1AgsfHWXZkZSWETKcNAJFqwZGM9bow9pHyzC9","toasset":"PEG","toamount":5022495314072},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":8,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA3i3tpmDfAKPK94oZcifXGXjWcWV2rNmMuXwMRoWNZGKsmjoN3E","toasset":"PEG","toamount":954977301054},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":9,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA28MV2VvvsdjjgXoHwsadtMWqM5mt7bZU3hMjLuDLLN1DBhK48g","toasset":"PEG","toamount":5330401161},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":10,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA31HYpPkouiYUnLQND2S8UGgJ5pp7fmJG2axZz617sbcVP1BVvX","toasset":"PEG","toamount":387980373386},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":11,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA3rh2jbXwazcrUYuHgPi5nn4KfjC65QQNyKqS2w8JmeEQ5X4m9g","toasset":"PEG","toamount":579911791613},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":12,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2pcJRQdjNrxoQoSNMM6NxB8iXnqf8cMNEh4bYafHywmTMH4U58","toasset":"PEG","toamount":623857187303},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":13,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2L1d6ggHYT1EP9C2CutoWrWFjYMDijjiCXGgoCpA34uZmCRtYq","toasset":"PEG","toamount":22987950378596},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":14,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2SLvyvtqQAkyC1TV2n1wSps2xvAVZfGFcn9pAHAd3H3zgoTPgM","toasset":"PEG","toamount":2070887773132},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":15,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA37Nz8Hx6zWRmpqNUVkmuBCUkWkX83o4dnFJZL2jbGKuuK1P7DE","toasset":"PEG","toamount":792558856046},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":16,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2Z4BSZzSUcnLUsCrPBjXNMKUQaXFGtY3SdFYYuMbzsBkMtcHBX","toasset":"PEG","toamount":457103211386},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":17,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA21s1bZX8r5EqiSEDyaFuDi1gQbX7BPyyAMkqdN1jhhiuSiyXab","toasset":"PEG","toamount":8924066625802},{"hash":"5621ed410f6cbb1ed3827643b676d29f5c542b6a7fe4fa1cf600bc4694c79c08","height":215631,"timestamp":"2019-10-24T01:37:00+02:00","executed":215631,"txindex":0,"txaction":0,"actionindex":18,"fromaddress":"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM","fromasset":"PEG","fromamount":222007522014091,"toaddress":"FA2Q3a7yufLuL5LQE73B68Gvef5V9rpVWdVTAm8XYJiMviKif6A7","toasset":"PEG","toamount":95986000000000}],"count":19,"nextoffset":0},"id":0}

Quick update: I had an epiphany over the weekend that splitting outputs into individual actions this way is a bad idea. I wanted to have a cleaner database format but the downsides just aren't worth it. I'm going to restructure this into 2.5 tables:

pn_history_txbatch one entry for every entry_hash
pn_history_transaction 1-n entries for every txbatch, indexed using (entry_hash, tx_index)
pn_history_lookup this is a table that has an entry for every address used in a transaction (inputs and outputs) and can be used to get all transactions associated with the address

the naming scheme and layout of these tables should be a lot more sensible

Okay, I pushed a new version with the revamped table structure. I have also added FCT Burns and Coinbase payouts as transactions into the system. Right now Coinbase payouts are individual transactions, though we could turn them into multi-payout transactions if necessary (though that would lose the entry-hash granularity atm).

The new JSON return structure is:

type ResultGetTransactions struct {
    Actions    interface{} `json:"actions"` // []HistoryTransaction
    Count      int         `json:"count"`
    NextOffset int         `json:"nextoffset"`
}
type HistoryTransaction struct {
    Hash      *factom.Bytes32 `json:"hash"`
    Height    int64           `json:"height"`
    Timestamp time.Time       `json:"timestamp"`
    Executed  int32           `json:"executed"`
    TxIndex   int             `json:"txindex"`
    TxAction  HistoryAction   `json:"txaction"`

    FromAddress *factom.FAAddress          `json:"fromaddress"`
    FromAsset   string                     `json:"fromasset"`
    FromAmount  int64                      `json:"fromamount"`
    ToAsset     string                     `json:"toasset,omitempty"`
    ToAmount    int64                      `json:"toamount,omitempty"`
    Outputs     []HistoryTransactionOutput `json:"outputs,omitempty"`
}
type HistoryTransactionOutput struct {
    Address *factom.FAAddress `json:"address"`
    Amount  int64             `json:"amount"`
}

The variable ones are "ToAsset", "ToAmount", and "Outputs", otherwise they are all always present.

TxAction 1: Transfer

For this one, ToAsset and ToAmount are not set. Outputs contains a list of at least one address/amount tuples, one for each output. The hash refers to the entry hash of the factom entry. TxIndex refers to the position of the transaction inside of the batch.

TxAction 2: Conversion

For this one, ToAsset is the asset being converted to. If the transaction has been executed, then ToAmount contains the actual amount of assets they received. Outputs is empty. The hash refers to the entry hash of the factom entry. TxIndex is always 0.

TxAction 3: Coinbase

For this one, FromAsset and FromAmount are blank and 0. ToAsset is always "PEG" and ToAmount is the payout. Outputs is empty. The hash refers to the winning OPR factom entry. TxIndex is always 0.

TxAction 4: FCT Burns

For this one, FromAsset is always "FCT" and ToAsset is always "pFCT" for now. ToAmount is always empty and FromAmount indicates the amount burned. Outputs is empty. The hash is the transaction hash inside the fblock of that height. TxIndex is always 0.

The upside is: it work great. The downside is: including the coinbase creates a lot of db bloat. My current db size went up to 151MB, which isn't a lot in absolute terms but something we should keep an eye on.

@WhoSoup I am playing around with the api. I think it might be beneficial to exclude transaction types from the results. When I query for an address tx list that is a miner, I get 36K tx reponses. To through that 50 at a time is unreasonable.

I think maybe we should be able to search for a specific tx type through the api?

To through that 50 at a time is unreasonable.

I think maybe we should be able to search for a specific tx type through the api?

yeah i didn't go too overboard with the requests, so i skipped the filter to get this moving quicker. i'll add one, shouldn't be too hard.

on a side note, do you think 50 is too little? too much? should we also give them a way to specify how many results to return, perhaps with an upper cap?

i'll add one, shouldn't be too hard.

turns out it was quite a bit of work to do it in a way that wouldn't be a pain in the butt down the road. i spun out the select query into its own file: txhistory_util.go. The api end now fills the struct HistoryQueryOptions and submits it along with the right function.

The HistoryQueryBuilder creates a COUNT(*) and SELECT data query using those options. It's not as elegant as GORM or something along those lines, but it should do the trick for now. That file has unit tests to make sure the queries were working right

curl --data-binary "{\"jsonrpc\": \"2.0\", \"id\": 0, \"method\": \"get-transactions\", \"params\": {\"address\": \"FA2mPQpBhr7f9XtXMVhsYQjNzsMGv1sqk2dTZp1NyTBuCaR6BjLM\"}}" -H "content-type:text/plain;" http://localhost:8070/v2 returns a total count of 11150

transfers only returns a total of 24. conversion and burns return 0. coinbase returns 11126. 11126 + 24 = 11150,

Yea, we might want to use a sql builder later. I know I'd have a difficult time adding adjustments to the existing method. But now, let's just get this out.

As for the 50, I think it's ok. The query I ran is 16KB in size for the 50, which is a reasonable amount of data imo. Paging through rewards will be a pain in the ass, but with 25 possible winners per block, that means each call for txs will likely sync at least 2 blocks, but realistically more since a single miner doesn't win all 25.

If people complain, I'd raise the maximum limit, and make it an argument with the default at 50.

pegnet / pegnetd

TX History API #66

TxAction 1: Transfer

TxAction 2: Conversion

TxAction 3: Coinbase

TxAction 4: FCT Burns