TheLaughingManProposals Roster/Metadata
Updated 2025-06-14Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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