Use Cases
Wallet queries
Smart contract transaction data

12. Smart contract transaction data

For our final query, we will inspect the details of a specific transaction, namely, the transaction id 52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57. Note we are not modifying the blockchain in any way, we are just reading data from it.

💡

For most users, the wallet is their gateway when interacting with dApps (decentralized applications) on Cardano. There is even a CIP-30 (opens in a new tab) outlining a consistent standard for developers to follow. For example, when a user swaps a token on a dApp, the wallet securely signs the transaction, and this transaction typically triggers smart contract(s). Most smart contracts on Cardano are written in Aiken. To learn more, head over to aiken-lang.org (opens in a new tab) where you will find some excellent primers and guides. Learn how Aiken's compiler and standard library enforce safe patterns to aid in secure code. Add to that a full-blown property test framework, you can be confident Aiken is fully equipped for high assurance.

🔎 As usual, you can also take a closer look using the Cardano Explorer (opens in a new tab)

12.1 Transaction information

Let's start by retrieving the transaction details:

  • transaction hash
  • timestamp
  • block
  • fee
  • epoch
  • slot
  • source address
  • destination address
  • assets
  • amount
  • UTXOs used
SELECT tx_detail.* FROM (
	SELECT 
		'destination_address' AS direction,
		tx.hash AS txhash,
		tx_out.address AS address,
		sa."view" AS stake_address,
		tx_out.value AS ADA_amount,
		ma.name AS asset_name,
		sum(mto.quantity) AS quantity,
		b.time AS time_stamp,
		tx.fee AS transaction_fee,
		b.block_no AS block,
		b.slot_no AS slot,
		b.epoch_no AS epoch,
		NULL AS utxo
	FROM tx
		LEFT JOIN tx_out ON tx_out.tx_id = tx.id
		LEFT JOIN stake_address sa ON sa.id = tx_out.stake_address_id
		LEFT JOIN ma_tx_out mto ON mto.tx_out_id = tx_out.id
		LEFT JOIN multi_asset ma ON ma.id = mto.ident
		LEFT JOIN block b ON b.id = tx.block_id
	WHERE
		tx.hash = '52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57'
	GROUP BY direction, txhash, address, stake_address, ADA_amount, asset_name, time_stamp, transaction_fee, block, slot, epoch, utxo
	UNION ALL
		SELECT 
			'source_address' AS direction,
			tx.hash AS txhash,
			tx_out.address AS address,
			sa."view" AS stake_address,				
			tx_out.value ADA_amount,
			ma.name AS asset_name,
			sum(mto.quantity) AS quantity,
			b.time AS time_stamp,
			tx.fee AS transaction_fee,
			b.block_no AS block,
			b.slot_no AS slot,
			b.epoch_no AS epoch,
			txin.hash AS utxo
	FROM tx
		LEFT JOIN tx_in ti ON ti.tx_in_id = tx.id
		LEFT JOIN tx_out ON tx_out.tx_id = ti.tx_out_id AND tx_out."index" = ti.tx_out_index 
		LEFT JOIN tx txin ON txin.id = tx_out.tx_id
		LEFT JOIN stake_address sa ON sa.id = tx_out.stake_address_id
		LEFT JOIN ma_tx_out mto ON mto.tx_out_id = tx_out.id
		LEFT JOIN multi_asset ma ON ma.id = mto.ident
		LEFT JOIN block b ON b.id = tx.block_id
	WHERE
		tx.hash = '52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57'			
	GROUP BY direction, txhash, address, stake_address, ADA_amount, asset_name, time_stamp, transaction_fee, block, slot, epoch, utxo
				) AS tx_detail
	ORDER BY tx_detail.direction ASC

Expected results format

 
direction           |  txhash            |  address              | stake_address              |ada_amount|asset_name|quantity|time_stamp	     |tx_fee |block     | slot      |epoch|utxo
--------------------+--------------------+-----------------------+----------------------------+----------+----------+--------+-------------------+------------------+-----------+-----+----------
destination_address | 52385f2dd....ebb57 |addr1q94ga....grqd9dw0d|stake1u9d9...sapektypsmfx9qr|20,000,000|          |        |2024-07-13 15:33:54|395,771|10,568,064|129,318,543| 496 | 	 
source_address      | 52385f2dd....ebb57 |addr1qyvl8....vgsyxh7q9|stake1uxw4...8f43pxkyg5842yn|33,080,297|          |        |2024-07-13 15:33:54|395,771|10,568,064|129,318,543| 496 |9d8...57a
 
 
(Note: full results trimmed for readability)

🔎 Let's zoom in to see what the query does step-by-step:

Overall query structure

To find the information we need, the query focuses on two perspectives:

  • Destination Addresses: Where did the funds/assets end up?
  • Source Addresses: Where did the funds/assets come from?

UNION ALL combines the results from the two separate SELECT statements. Each SELECT statement targets one of the perspectives above.

Destination Addresses, the first SELECT

It fetches details about the outputs tx_out (opens in a new tab) of the transaction. It then links to other tables to fetch further information such as:

  • Stake address associated with the output
  • Asset names and quantities
  • Block, slot, and epoch details of the transaction
  • Transaction fee

Source Addresses, the second SELECT

This SELECT statement looks back to the inputs tx_in (opens in a new tab) of the transaction. It traces the chain of transactions to find the original outputs tx_out (opens in a new tab) that were spent as inputs in this transaction. It retrieves similar information as the first SELECT, but additionally includes the 'utxo' (unspent transaction output) hash which represents the specific output that was spent.

Result returned

The two result sets are combined with a UNION ALL clause, and the final output is sorted by direction so that destination_address rows appear before source_address rows in the results.

12.2 Contracts details

For this query, we are looking for the Redeemer, datum hash and datum associated with the transaction. A good recap on there terms is available on Aiken-lang.org (opens in a new tab).

SELECT 
	re.script_hash AS scriptHash, 
	txOut.address AS address, 
	re.purpose as purpose,
        encode(rd.bytes, 'hex') as redeemerData, 
        re.unit_mem as redeemerMem, 
        re.unit_steps as redeemerSteps,
        d.hash as datumHash, 
        encode(d.bytes, 'hex') as datum
           FROM tx
           INNER JOIN redeemer re ON re.tx_id = tx.id 
           LEFT JOIN redeemer_data rd ON re.redeemer_data_id = rd.id 
           LEFT JOIN tx_in txIn ON (txIn.redeemer_id = re.id and txIn.tx_in_id = tx.id)
           LEFT JOIN tx_out txOut ON txIn.tx_out_id = txOut.tx_id AND txIn.tx_out_index = txOut.index
           LEFT JOIN datum d ON (txOut.data_hash = d.hash OR txOut.inline_datum_id = d.id)
           WHERE tx.hash = '52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57'

Expected results format

 
scripthash    | address      |purpose|redeemerbytes| redeemermem| redeemersteps	 | datumhash         | datumbytes
--------------+--------------+-------+-------------+------------+----------------+-------------------+------------------------
9068...ce138ad|addr1zx...5plm|spend  |d87a80       | 2,463,014  |  689,900,093	 | 3c27703297...1fce | d8799f581c...00ffffffff
 
 
(Note: full results trimmed for readability)

🔎 Let's zoom in to see what the query does step-by-step:

Target transaction

WHERE tx.hash = '...' filters to the specific transaction we are interested in.

Redeemer details

INNER JOIN redeemer re ON re.tx_id = tx.id ensures we only consider redeemers associated with the target transaction. re.script_hash, re.purpose, re.unit_mem, re.unit_steps fetch the script hash the redeemer interacts with, its purpose, memory usage, and computational steps, respectively.

Redeemer Data

LEFT JOIN redeemer_data rd ON re.redeemer_data_id = rd.id brings in redeemer data if it exists. encode(rd.bytes, 'hex') as redeemerData converts the raw redeemer data into a human-readable hexadecimal format.

Transaction input and output JOIN

LEFT JOIN tx_in txIn ON ... joins the transaction input that triggered the redeemer to the transaction itself. LEFT JOIN tx_out txOut ON ... links this input with the specific transaction output it spends from. txOut.address extracts the address of the output being spent.

Datum information

LEFT JOIN datum d ON ... attempts to find a datum linked to the spent output, either through a hash reference or an inline datum. d.hash and encode(d.bytes, 'hex') as datum provide the datum hash and its hexadecimal representation.

Output

The query will produce a result set containing:

  • scriptHash ...the script hash the redeemer interacts with.
  • address...the address of the UTxO spent by the transaction input.
  • purpose...the purpose of the redeemer
  • redeemerData...the Hex-encoded redeemer data (if present).
  • redeemerMem & redeemerSteps...the redeemer's memory & computational step usage.
  • datumHash...the hash of the datum associated with the spent output.
  • datum ...the hex-encoded datum content (if present).
💡

Remember you can also review the results in any Cardano explorer. The Cardano Developer Portal has compiled a list (opens in a new tab) in their 'Showcase' section. For example, you can review review the results of Queries 12.1 and 12.2 at this link (opens in a new tab) 🔎

12.3 Collateral input & output

The collateral mechanism is an important feature that has been designed to ensure successful smart contract execution. You can read more about it in the documentation (opens in a new tab).

This query retrieves information about the inputs and outputs associated with our transaction, identified by its hash 52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57. The inputs and outputs are categorized in the results in the 'collateral' column.

	   SELECT 
		'input' AS collateral,
	       	txOut.address AS address, 
             	txOut.value AS value,
             	txOut.index AS index
           FROM unconsume_tx_in cti 
           INNER JOIN tx_out txOut on txOut.tx_id = cti.tx_out_id and txOut.index = cti.tx_out_index 
           LEFT JOIN tx ON cti.tx_in_id = tx.id 
           JOIN tx txin ON cti.tx_out_id = txin.id 
           WHERE 
		tx.hash  = '52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57'
           
           UNION
          
           SELECT 
		   'output' AS collateral,
	           failedTxOut.address AS address, 
	           failedTxOut.value AS value,
	           failedTxOut.index AS index
           FROM failed_tx_out failedTxOut 
           INNER JOIN tx ON failedTxOut.tx_id = tx.id
           WHERE tx.hash = '52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57'

Expected results format

 
collateral | address      											    											 |value       |index
-----------+---------------------------------------------------------------------------------------------------------+------------+-----
input      | addr1qyvl8lmwpfq7pahuadpjwul9xlj7c0atr5hvu4r3cjsjlfua276aqezvwjsfucvq76welxmytfyt7ug5835wntzzdvgsyxh7q9 | 33,080,297 | 0
output     | addr1qyvl8lmwpfq7pahuadpjwul9xlj7c0atr5hvu4r3cjsjlfua276aqezvwjsfucvq76welxmytfyt7ug5835wntzzdvgsyxh7q9 | 32,486,640 | 5

🔎 Let's zoom in to see what the query does step-by-step:

First SELECT

The FROM unconsume_tx_in cti clause starts by looking at unspent transaction outputs (UTXOs) that have been used as inputs in some other transaction. The INNER JOIN tx_out txOut ... joins with the tx_out (opens in a new tab) table to get details about these UTXOs, ie. address, value, index within the transaction.

The LEFT JOIN tx ON ... attempts to join with the tx (opens in a new tab) table to see if there's a transaction where this UTXO was used as an input.

The JOIN tx txin ON ... clause also joins with the tx (opens in a new tab) table again (alias txin) to get details about the transaction where this UTXO was originally created as an output.

The WHERE tx.hash = ... clause filters the results to only include UTXOs that were used as inputs in the specific transaction we specified. The 'input' AS collateral clause labels these results as 'input' in the collateral column.

Second SELECT

The FROM failed_tx_out failedTxOut looks at outputs from transactions that failed. The INNER JOIN tx ON ... clause joins with the tx (opens in a new tab) table to get details about the failed transaction.

The WHERE tx.hash = ... clause filters the results to only include outputs from the specific failed transaction we're interested in. The 'output' AS collateral clause labels these results as 'output' in the collateral column.

UNION

The UNION clause combines the results from the two parts into a single result set:

  • collateral indicates whether the row represents a transaction input or an output
  • address ...ie, the address associated with the input or output
  • value is the amount of ADA (or other assets) associated with the input or output
  • index tells us the position of the input or output within its transaction

12.4 Reference input and contract reference

Our final query aims to retrieve details about the outputs (UTXOs) that were spent as inputs in our specific transaction, identified by its hash 52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57.

	  SELECT 
  		txOut.address as address,
     		tx.hash as txHash, 
      		txOut.value as value, 
      		r.tx_out_index as index
           FROM reference_tx_in r
           JOIN tx_out txOut ON r.tx_out_id = txOut.tx_id AND txOut.index = r.tx_out_index
           JOIN tx ON txOut.tx_id = tx.id 
	   JOIN tx txin ON txin.id = r.tx_in_id 
           WHERE 
		txin.hash = '52385f2dd6b69eb035474b529249afda132cde54f54c727842393edc51bebb57'

Expected results format

 
address 								 	                        									| txhash      			|value       |index
--------------------------------------------------------------------------------------------------------+-----------------------+------------+-----
addr1zxgx3far7qygq0k6epa0zcvcvrevmn0ypsnfsue94nsn3tvpw288a4x0xf8pxgcntelxmyclq83s0ykeehchz2wtspks905plm	| 9a3245....b0fc1d06f5b | 40,000,000 | 0
 
(Note:  results abbreviated for readability)

🔎 Let's zoom in to see what the query does step-by-step:

Query structure

The FROM reference_tx_in r starts by looking at the reference_tx_in (opens in a new tab) table, which stores information about transaction inputs and the UTXOs they reference.

JOIN tx_out txOut ON ... joins with the tx_out (opens in a new tab) table to get details about these UTXOs, ie. address, value, index within the transaction.

JOIN tx ON txOut.tx_id = tx.id joins with the tx (opens in a new tab) table to get information about the transaction where these UTXOs were created as outputs.

JOIN tx txin ON txin.id = r.tx_in_id joins with the tx (opens in a new tab) table again (aliase txin) to get details about the transaction where these UTXOs were spent as inputs.

The WHERE txin.hash = ... clause filters the results to only include UTXOs that were spent as inputs in the specific transaction we're interested in.

Results

  • address is the address associated with the spent UTXO
  • txHash is the hash of the transaction where this UTXO was created as an output
  • value is the amount of ADA (or other assets) associated with the spent UTXO
  • index is the position of the UTXO within the transaction where it was created
Find Active Governance ProposalsNFT marketplace