How to Remove Burned NFTs from SQL Output

Developers Native Tokens
1

I am querying the Cardano Postgres DB and can’t figure out how to remove NFTs (multi-assets) that have been burned. In the SQL query below, I am selecting the mint transactions for an asset fingerprint that I know to be a duplicate (meaning there was a burn associated with it). The SQL output is below.

After doing some manual research I was able to figure out that the 3rd row in the output is a mint transaction that ‘burned’ the 1st row’s mint transaction (Hence the -1 quantity). The 2nd row is the NFT that is active today.

However, I am unsure how to know the 3rd transaction burned the 1st row and not the 2nd row. I would like to have my SQL script automatically remove the 1st row and only show active (not burned) NFTs.

Any help would be greatly appreciated. Thanks! :slight_smile:

SQL Output:

Screen Shot 2022-01-14 at 1.06.49 PM
Screen Shot 2022-01-14 at 1.06.49 PM2926×202 173 KB 

with policy_assets as (
    select
        id as ma_id
        , encode(policy, 'hex') as policy_id
        , name
        , fingerprint
    from multi_asset
    where encode(policy, 'hex') = 'b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b9494'
    order by id
)

-- Dupe asset fingerprint
select *
from ma_tx_mint m
inner join policy_assets p
    on m.ident = p.ma_id
where fingerprint in ('asset1x0hrrjs6xtmhnwy8ee60j2jrn4k005awqum4zd')
order by m.id
2

A token can only be burnt, when it is in one of the input UTxOs. But after it has been minted, it could have been transferred elsewhere and then later back to the address that does the burning. So, you probably would have to trace all transactions moving the tokens around until the burning transaction.

And then you could still have that both of them were in the same UTxO going into the burning. Then you cannot even decide, which of the two was burnt.

Also: I’ve read a recipe here to change the metadata of an NFT by minting it with the same policy and name and the new metadata and burning it again, immediately. All NFT services will, nevertheless, use the new metadata also for the old NFT (that might have been transferred into the wild, already). So, depending on what you want to achieve, you might not want to throw away the minting of burnt NFTs, because it may still contain the most up-to-date metadata.

3

Thanks for the reply! To get the most recent NFT (not burned), I am thinking about just doing a window function to order the policy assets by mint date (partitioned by policy/asset fingerprint that’s ordered by transaction id). I believe that should give me the most recent NFT and exclude the ‘burned’ ones. I just wish there was a way to make that connection between the burn transaction and the asset it burned.

4

Sounds about right.

Wouldn’t be if the transaction ids were the long ones we use on cardanoscan.io et al. They are not ordered by transaction time, but are hashes.

But the ones in the SQL look like shorter, time-ordered ones.