286 lines
11 KiB
SQL
286 lines
11 KiB
SQL
-- file: validation_sql/SQL_VALIDATION_METEORA_DLMM_0_7_57.sql
|
|
|
|
-- 0.7.57 meteora_dlmm validation checklist.
|
|
-- Run on a dedicated fresh SQLite database for the Meteora DLMM tranche.
|
|
-- Recommended replay settings:
|
|
-- skipDexDecode=no, forceDexDecode=yes, deferInstructionObservations=yes.
|
|
-- This file is read-only.
|
|
|
|
-- 00. Upstream fallback samples to backfill/promote.
|
|
SELECT
|
|
json_extract(de.payload_json, '$.upstreamEntryName') AS upstream_entry_name,
|
|
json_extract(de.payload_json, '$.upstreamDiscriminatorHex') AS upstream_discriminator_hex,
|
|
json_extract(de.payload_json, '$.upstreamSourceRepo') AS source_repo,
|
|
COUNT(*) AS fallback_count,
|
|
COUNT(DISTINCT de.transaction_id) AS tx_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_chain_transactions tx
|
|
ON tx.id = de.transaction_id
|
|
WHERE de.protocol_name = 'upstream_git'
|
|
AND de.event_kind = 'upstream_git.instruction_match'
|
|
AND json_extract(de.payload_json, '$.upstreamDecoderCode') = 'meteora_dlmm'
|
|
GROUP BY upstream_entry_name, upstream_discriminator_hex, source_repo
|
|
ORDER BY fallback_count DESC, upstream_entry_name, upstream_discriminator_hex;
|
|
|
|
-- 01. Local instruction observations.
|
|
SELECT
|
|
instruction_name,
|
|
discriminator_hex,
|
|
COUNT(*) AS observed_count,
|
|
COUNT(DISTINCT signature) AS tx_count,
|
|
MIN(signature) AS sample_signature
|
|
FROM k_sol_instruction_observations
|
|
WHERE decoder_code = 'meteora_dlmm'
|
|
GROUP BY instruction_name, discriminator_hex
|
|
ORDER BY observed_count DESC, instruction_name, discriminator_hex;
|
|
|
|
-- 02. Coverage entries.
|
|
SELECT
|
|
entry_name,
|
|
entry_kind,
|
|
event_family,
|
|
expected_db_target,
|
|
proof_status,
|
|
local_event_kind,
|
|
discriminator_hex,
|
|
observed_count,
|
|
materialized_count,
|
|
trade_count
|
|
FROM k_sol_dex_event_coverage_entries
|
|
WHERE decoder_code = 'meteora_dlmm'
|
|
ORDER BY entry_kind, entry_name, discriminator_hex;
|
|
|
|
-- 03. Decoded DLMM summary.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(*) AS decoded_count,
|
|
COUNT(DISTINCT de.transaction_id) AS tx_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_chain_transactions tx
|
|
ON tx.id = de.transaction_id
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
GROUP BY de.event_kind
|
|
ORDER BY decoded_count DESC, de.event_kind;
|
|
|
|
-- 04. Decoded DLMM without coverage. Target: empty.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(*) AS decoded_count,
|
|
COUNT(DISTINCT de.transaction_id) AS tx_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_chain_transactions tx
|
|
ON tx.id = de.transaction_id
|
|
LEFT JOIN k_sol_dex_event_coverage_entries ce
|
|
ON ce.decoder_code = 'meteora_dlmm'
|
|
AND ce.local_event_kind = de.event_kind
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
AND ce.id IS NULL
|
|
GROUP BY de.event_kind
|
|
ORDER BY decoded_count DESC, de.event_kind;
|
|
|
|
-- 05. Successful non-materialized without explicit skip/policy. Target: empty.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(*) AS unexplained_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_dex_decoded_events de
|
|
JOIN k_sol_chain_transactions tx
|
|
ON tx.id = de.transaction_id
|
|
LEFT JOIN k_sol_trade_events te ON te.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_launch_events lae ON lae.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_liquidity_events lie ON lie.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_lifecycle_events ple ON ple.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_fee_events fee ON fee.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_reward_events rew ON rew.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_admin_events adm ON adm.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_orderbook_events obe ON obe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_token_account_events tae ON tae.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
AND (tx.err_json IS NULL OR tx.err_json = '' OR tx.err_json = 'null')
|
|
AND COALESCE(json_extract(de.payload_json, '$.eventActionability'), '') NOT IN (
|
|
'informational',
|
|
'decoded_only_anchor_event',
|
|
'decoded_only_missing_mint_context',
|
|
'decoded_only_with_explicit_skip_reason'
|
|
)
|
|
AND te.id IS NULL AND lae.id IS NULL AND lie.id IS NULL AND ple.id IS NULL
|
|
AND fee.id IS NULL AND rew.id IS NULL AND adm.id IS NULL AND obe.id IS NULL AND tae.id IS NULL
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipTradeReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipCandleReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipLiquidityReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipLifecycleReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipCatalogReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipFeeReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipRewardReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipAdminReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipOrderbookReason')), '') = ''
|
|
GROUP BY de.event_kind
|
|
ORDER BY unexplained_count DESC, de.event_kind;
|
|
|
|
-- 06. Failed transaction materialization. Target: empty.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(DISTINCT de.id) AS decoded_failed_count,
|
|
COUNT(DISTINCT te.id) AS trade_count,
|
|
COUNT(DISTINCT lie.id) AS liquidity_count,
|
|
COUNT(DISTINCT ple.id) AS lifecycle_count,
|
|
COUNT(DISTINCT fee.id) AS fee_count,
|
|
COUNT(DISTINCT rew.id) AS reward_count,
|
|
COUNT(DISTINCT adm.id) AS admin_count,
|
|
COUNT(DISTINCT obe.id) AS orderbook_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_dex_decoded_events de
|
|
JOIN k_sol_chain_transactions tx
|
|
ON tx.id = de.transaction_id
|
|
LEFT JOIN k_sol_trade_events te ON te.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_liquidity_events lie ON lie.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_lifecycle_events ple ON ple.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_fee_events fee ON fee.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_reward_events rew ON rew.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_admin_events adm ON adm.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_orderbook_events obe ON obe.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
AND tx.err_json IS NOT NULL
|
|
AND tx.err_json <> ''
|
|
AND tx.err_json <> 'null'
|
|
GROUP BY de.event_kind
|
|
HAVING trade_count > 0 OR liquidity_count > 0 OR lifecycle_count > 0
|
|
OR fee_count > 0 OR reward_count > 0 OR admin_count > 0 OR orderbook_count > 0
|
|
ORDER BY decoded_failed_count DESC, de.event_kind;
|
|
|
|
-- 07. Multi-target materialization. Target: empty.
|
|
SELECT *
|
|
FROM (
|
|
SELECT
|
|
de.id AS decoded_event_id,
|
|
de.event_kind,
|
|
tx.signature,
|
|
(CASE WHEN te.id IS NULL THEN 0 ELSE 1 END)
|
|
+ (CASE WHEN lie.id IS NULL THEN 0 ELSE 1 END)
|
|
+ (CASE WHEN ple.id IS NULL THEN 0 ELSE 1 END)
|
|
+ (CASE WHEN fee.id IS NULL THEN 0 ELSE 1 END)
|
|
+ (CASE WHEN rew.id IS NULL THEN 0 ELSE 1 END)
|
|
+ (CASE WHEN adm.id IS NULL THEN 0 ELSE 1 END)
|
|
+ (CASE WHEN obe.id IS NULL THEN 0 ELSE 1 END) AS target_count
|
|
FROM k_sol_dex_decoded_events de
|
|
JOIN k_sol_chain_transactions tx ON tx.id = de.transaction_id
|
|
LEFT JOIN k_sol_trade_events te ON te.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_liquidity_events lie ON lie.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_lifecycle_events ple ON ple.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_fee_events fee ON fee.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_reward_events rew ON rew.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_admin_events adm ON adm.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_orderbook_events obe ON obe.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
)
|
|
WHERE target_count > 1
|
|
ORDER BY target_count DESC, event_kind, signature;
|
|
|
|
-- 08. Non-swap trade/candle safety. Target: empty.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(DISTINCT te.id) AS trade_count,
|
|
COUNT(DISTINCT pc.id) AS candle_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_dex_decoded_events de
|
|
JOIN k_sol_chain_transactions tx ON tx.id = de.transaction_id
|
|
LEFT JOIN k_sol_trade_events te ON te.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pair_candles pc ON pc.pair_id = te.pair_id
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
AND de.event_kind NOT IN (
|
|
'meteora_dlmm.swap',
|
|
'meteora_dlmm.swap2',
|
|
'meteora_dlmm.swap_exact_out',
|
|
'meteora_dlmm.swap_exact_out2',
|
|
'meteora_dlmm.swap_with_price_impact',
|
|
'meteora_dlmm.swap_with_price_impact2'
|
|
)
|
|
GROUP BY de.event_kind
|
|
HAVING trade_count > 0 OR candle_count > 0
|
|
ORDER BY trade_count DESC, candle_count DESC, de.event_kind;
|
|
|
|
-- 09. Fee parent/legs summary.
|
|
SELECT
|
|
de.protocol_name,
|
|
de.event_kind,
|
|
COUNT(DISTINCT fee.id) AS fee_parent_count,
|
|
COUNT(DISTINCT CASE
|
|
WHEN COALESCE(TRIM(fee.fee_token_mint), '') <> ''
|
|
AND COALESCE(TRIM(fee.fee_amount_raw), '') <> ''
|
|
THEN fee.id
|
|
ELSE NULL
|
|
END) AS parent_with_scalar_amount_count,
|
|
COUNT(DISTINCT fea.id) AS fee_amount_leg_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_fee_events fee
|
|
JOIN k_sol_dex_decoded_events de ON de.id = fee.decoded_event_id
|
|
JOIN k_sol_chain_transactions tx ON tx.id = fee.transaction_id
|
|
LEFT JOIN k_sol_fee_event_amounts fea ON fea.fee_event_id = fee.id
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
GROUP BY de.protocol_name, de.event_kind
|
|
ORDER BY de.protocol_name, de.event_kind;
|
|
|
|
-- 10. Fee parent scalar without leg. Target: empty.
|
|
SELECT
|
|
de.protocol_name,
|
|
de.event_kind,
|
|
tx.signature,
|
|
fee.id AS fee_event_id,
|
|
fee.fee_token_mint,
|
|
fee.fee_amount_raw,
|
|
fee.payload_json
|
|
FROM k_sol_fee_events fee
|
|
JOIN k_sol_dex_decoded_events de ON de.id = fee.decoded_event_id
|
|
JOIN k_sol_chain_transactions tx ON tx.id = fee.transaction_id
|
|
LEFT JOIN k_sol_fee_event_amounts fea ON fea.fee_event_id = fee.id
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
AND COALESCE(TRIM(fee.fee_token_mint), '') <> ''
|
|
AND COALESCE(TRIM(fee.fee_amount_raw), '') <> ''
|
|
AND fea.id IS NULL
|
|
ORDER BY de.protocol_name, de.event_kind, tx.signature
|
|
LIMIT 100;
|
|
|
|
-- 11. Orphan fee amount legs. Target: empty.
|
|
SELECT
|
|
fea.id,
|
|
fea.fee_event_id,
|
|
fea.transaction_id,
|
|
fea.decoded_event_id
|
|
FROM k_sol_fee_event_amounts fea
|
|
LEFT JOIN k_sol_fee_events fee ON fee.id = fea.fee_event_id
|
|
WHERE fee.id IS NULL;
|
|
|
|
-- 12. Reward/fee separation overview.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(DISTINCT fee.id) AS fee_count,
|
|
COUNT(DISTINCT rew.id) AS reward_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_dex_decoded_events de
|
|
JOIN k_sol_chain_transactions tx ON tx.id = de.transaction_id
|
|
LEFT JOIN k_sol_fee_events fee ON fee.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_reward_events rew ON rew.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'meteora_dlmm'
|
|
GROUP BY de.event_kind
|
|
HAVING fee_count > 0 OR reward_count > 0
|
|
ORDER BY de.event_kind;
|
|
|
|
-- 13. Global watchlist after replay.
|
|
SELECT
|
|
COALESCE(json_extract(de.payload_json, '$.upstreamDecoderCode'), de.protocol_name) AS backlog_decoder,
|
|
de.event_kind,
|
|
json_extract(de.payload_json, '$.upstreamEntryName') AS upstream_entry_name,
|
|
json_extract(de.payload_json, '$.upstreamDiscriminatorHex') AS upstream_discriminator_hex,
|
|
COUNT(*) AS decoded_count,
|
|
COUNT(DISTINCT de.transaction_id) AS tx_count,
|
|
MIN(tx.signature) AS sample_signature
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_chain_transactions tx ON tx.id = de.transaction_id
|
|
WHERE de.protocol_name IN ('upstream_git', 'meteora_dlmm')
|
|
GROUP BY backlog_decoder, de.event_kind, upstream_entry_name, upstream_discriminator_hex
|
|
ORDER BY decoded_count DESC, backlog_decoder, de.event_kind
|
|
LIMIT 100;
|