TheLaughingManProposals Roster/Metadata
    Updated 2025-06-14
    with prop_metadata as (
    SELECT
    block_timestamp as creation_time
    , DECODED_INSTRUCTION:accounts[0]:pubkey as proposal
    , DECODED_INSTRUCTION:args:title as title
    , DECODED_INSTRUCTION:args:descriptionLink as details
    from solana.core.fact_decoded_instructions
    WHERE 1=1
    and program_id = 'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'
    and event_type = 'createProposalMeta'
    )

    , prop_activation_data as (
    -- log pattern start: 'Program data: 9zWm+' (start is the instruction/function sig...should remain the same unless...)
    SELECT
    dt.block_timestamp as vote_start_time
    , dt.tx_id
    , split(value, ': ')[1] as activation_log_base64
    , to_varchar(base64_decode_binary(activation_log_base64)) as hex_data
    --, substr(hex_data, 0, 16) as instruction_sig
    --, livequery.utils.udf_hex_to_base58('0x' || substr(hex_data, 1 + 16, 64)) as governor
    , livequery.utils.udf_hex_to_base58('0x' || substr(hex_data, 1 + 16 + 64, 64)) as proposal
    , TO_TIMESTAMP_NTZ(livequery.utils.udf_hex_to_int('s2c', to_char(reverse(to_binary(substr(hex_data, 1+ 16 + 64 + 64, 16))))) ) as vote_end_time
    FROM solana.core.fact_decoded_instructions dt
    LEFT JOIN solana.core.fact_transactions t ON dt.tx_id = t.tx_id
    , lateral flatten(input=>t.log_messages)
    WHERE 1=1
    --and tx_id = 'nSofUZjcUDVpPwfxGzDyHasUskwv4GrJzfPZ6cChxPXNfCpupetNaaDo5SjGmttxxowtvJgMXMeouoNU7wHFqV8'
    and program_id = 'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'
    and value ILIKE 'Program data: 9zWm+%'
    and event_type = 'activateProposal'
    )

    , merge as (
    SELECT
    pm.creation_time
    QueryRunArchived: QueryRun has been archived