Contract Transactions

All transactions targeting a contract address are stored in Sort and made available via SQL. For example, when the following 'tweet' contract function is called:

contract WriteTweet {

    string message;

    function tweet(string memory _message) public {
        message = _message;
    }
}

Sort stores the transaction across four tables:

transaction- (Gas data, associated addresses, JSON format function call data)
transaction_param - (all params sent to this function, _message in the example above)
transaction_log (any logs/events emitted from calling the transaction)
transaction_log_param (all params from logs/events)

You can view this data through the SQL interface as well.

An example, where we are trying to find an ENS holder for a given domain, given only transaction logs, we can run a couple of queries:

-- Broadly, look for ransaction data, where 0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85 for our ENS NFT contract
SELECT * 
FROM ethereum.transaction 
WHERE to_address = '0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85';

-- Transaction parameters for the contract calls to 0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85
-- Trying to figure out who owns the syncpad domain now and we are looking for the associated hash 
-- which is what we care about
SELECT transaction_hash, * 
FROM ethereum.transaction_param tp, ethereum.transaction t 
WHERE tp.transaction_id = t.id 
  AND to_address = '0x253553366da8546fc250f225fe3d25d0c782303b' 
  AND path = 'name' AND str_val = 'syncpad' 

-- Transaction logs for the contract 0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85
-- for the transaction hash 0x19b7131b7ba8eef8d25795b03e0171ba46c40a0050d2f22bb8859b9da5fb8dcb
-- we view this broadly now to get a sense of what happened
SELECT * 
FROM ethereum.transaction_log tl, ethereum.transaction t 
WHERE tl.transaction_id = t.id 
  AND transaction_hash = '0x19b7131b7ba8eef8d25795b03e0171ba46c40a0050d2f22bb8859b9da5fb8dcb'

-- Drilling into transaction log params table,
-- we now want to find the NewOwner event and what the resultant addresses are here
SELECT encode(bin_val::bytea, 'hex'), * 
FROM ethereum.transaction_log tl, ethereum.transaction t, ethereum.transaction_log_param tlp 
WHERE tl.transaction_id = t.id 
  AND tl.id = tlp.transaction_log_id 
  AND transaction_hash = '0x19b7131b7ba8eef8d25795b03e0171ba46c40a0050d2f22bb8859b9da5fb8dcb'
  AND tl.event = 'NewOwner' 
  AND tlp.path = 'owner'

-- We now have the addresses for the two ERC721/1155 tokens that were issued in this tx, that represent an ENS domain