326 lines
11 KiB
SQL
326 lines
11 KiB
SQL
-- file: validation_sql/SQL_VALIDATION_PUMP_SWAP_0_7_53.sql
|
|
|
|
-- 0.7.53 pump_swap validation checklist.
|
|
-- Run on a dedicated fresh SQLite database after corpus construction and replay with:
|
|
-- skipDexDecode=no, forceDexDecode=yes, deferInstructionObservations=yes.
|
|
|
|
-- 01. Coverage pump_swap.
|
|
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 = 'pump_swap'
|
|
ORDER BY entry_kind, entry_name, discriminator_hex;
|
|
|
|
-- 02. Instruction observations.
|
|
SELECT
|
|
instruction_name,
|
|
discriminator_hex,
|
|
COUNT(*) AS observed_count,
|
|
COUNT(DISTINCT signature) AS tx_count
|
|
FROM k_sol_instruction_observations
|
|
WHERE decoder_code = 'pump_swap'
|
|
GROUP BY instruction_name, discriminator_hex
|
|
ORDER BY observed_count DESC, instruction_name, discriminator_hex;
|
|
|
|
-- 03. Residual local instruction audit.
|
|
SELECT
|
|
json_extract(payload_json, '$.discriminatorHex') AS discriminator_hex,
|
|
COUNT(*) AS audit_count,
|
|
COUNT(DISTINCT transaction_id) AS tx_count
|
|
FROM k_sol_dex_decoded_events
|
|
WHERE protocol_name = 'pump_swap'
|
|
AND event_kind = 'pump_swap.instruction_audit'
|
|
GROUP BY discriminator_hex
|
|
ORDER BY audit_count DESC, discriminator_hex;
|
|
|
|
-- 04. Residual upstream fallback for covered local entries.
|
|
SELECT
|
|
json_extract(ug.payload_json, '$.upstreamDecoderCode') AS upstream_decoder_code,
|
|
json_extract(ug.payload_json, '$.upstreamEntryName') AS entry_name,
|
|
json_extract(ug.payload_json, '$.upstreamDiscriminatorHex') AS discriminator_hex,
|
|
json_extract(ug.payload_json, '$.upstreamSourceRepo') AS source_repo,
|
|
COUNT(*) AS fallback_count,
|
|
COUNT(DISTINCT ug.transaction_id) AS tx_count
|
|
FROM k_sol_dex_decoded_events ug
|
|
JOIN k_sol_dex_event_coverage_entries ce
|
|
ON ce.decoder_code = json_extract(ug.payload_json, '$.upstreamDecoderCode')
|
|
AND ce.entry_name = json_extract(ug.payload_json, '$.upstreamEntryName')
|
|
AND ce.discriminator_hex = json_extract(ug.payload_json, '$.upstreamDiscriminatorHex')
|
|
AND ce.local_event_kind IS NOT NULL
|
|
AND ce.local_event_kind <> ''
|
|
WHERE ug.protocol_name = 'upstream_git'
|
|
AND ug.event_kind = 'upstream_git.instruction_match'
|
|
AND json_extract(ug.payload_json, '$.upstreamDecoderCode') = 'pump_swap'
|
|
GROUP BY upstream_decoder_code, entry_name, discriminator_hex, source_repo
|
|
ORDER BY fallback_count DESC, entry_name;
|
|
|
|
-- 05. Non-swap safety: non-swap event must not materialize as trade.
|
|
SELECT
|
|
de.event_kind,
|
|
ce.event_family,
|
|
COUNT(*) AS decoded_count,
|
|
COUNT(te.id) AS trade_count
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_dex_event_coverage_entries ce
|
|
ON ce.decoder_code = 'pump_swap'
|
|
AND ce.local_event_kind = de.event_kind
|
|
LEFT JOIN k_sol_trade_events te
|
|
ON te.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'pump_swap'
|
|
GROUP BY de.event_kind, ce.event_family
|
|
HAVING ce.event_family <> 'swap'
|
|
AND COUNT(te.id) > 0
|
|
ORDER BY trade_count DESC, de.event_kind;
|
|
|
|
-- 06. Failed transaction safety: failed tx must not materialize as business trade.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(*) AS decoded_failed_count,
|
|
COUNT(te.id) AS trade_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
|
|
WHERE de.protocol_name = 'pump_swap'
|
|
AND tx.err_json IS NOT NULL
|
|
AND tx.err_json <> ''
|
|
AND tx.err_json <> 'null'
|
|
GROUP BY de.event_kind
|
|
HAVING COUNT(te.id) > 0
|
|
ORDER BY trade_count DESC, de.event_kind;
|
|
|
|
-- 07. Decoded without coverage entry.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(*) AS decoded_count
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_dex_event_coverage_entries ce
|
|
ON ce.decoder_code = 'pump_swap'
|
|
AND ce.local_event_kind = de.event_kind
|
|
WHERE de.protocol_name = 'pump_swap'
|
|
AND ce.id IS NULL
|
|
GROUP BY de.event_kind
|
|
ORDER BY decoded_count DESC, de.event_kind;
|
|
|
|
-- 08. Multi-target materialization.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(DISTINCT de.id) AS decoded_count,
|
|
COUNT(DISTINCT te.id) AS trade_count,
|
|
COUNT(DISTINCT le.id) AS liquidity_count,
|
|
COUNT(DISTINCT pe.id) AS lifecycle_count,
|
|
COUNT(DISTINCT fe.id) AS fee_count,
|
|
COUNT(DISTINCT re.id) AS reward_count,
|
|
COUNT(DISTINCT ae.id) AS admin_count,
|
|
COUNT(DISTINCT oe.id) AS orderbook_count,
|
|
(
|
|
CASE WHEN COUNT(DISTINCT te.id) > 0 THEN 1 ELSE 0 END
|
|
+ CASE WHEN COUNT(DISTINCT le.id) > 0 THEN 1 ELSE 0 END
|
|
+ CASE WHEN COUNT(DISTINCT pe.id) > 0 THEN 1 ELSE 0 END
|
|
+ CASE WHEN COUNT(DISTINCT fe.id) > 0 THEN 1 ELSE 0 END
|
|
+ CASE WHEN COUNT(DISTINCT re.id) > 0 THEN 1 ELSE 0 END
|
|
+ CASE WHEN COUNT(DISTINCT ae.id) > 0 THEN 1 ELSE 0 END
|
|
+ CASE WHEN COUNT(DISTINCT oe.id) > 0 THEN 1 ELSE 0 END
|
|
) AS materialized_target_count
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_trade_events te
|
|
ON te.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_liquidity_events le
|
|
ON le.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_lifecycle_events pe
|
|
ON pe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_fee_events fe
|
|
ON fe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_reward_events re
|
|
ON re.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_admin_events ae
|
|
ON ae.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_orderbook_events oe
|
|
ON oe.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'pump_swap'
|
|
GROUP BY de.event_kind
|
|
HAVING materialized_target_count > 1
|
|
ORDER BY materialized_target_count DESC, de.event_kind;
|
|
|
|
-- 09. Unexplained successful non-materialized events.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(*) AS unexplained_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 le
|
|
ON le.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_lifecycle_events pe
|
|
ON pe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_fee_events fe
|
|
ON fe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_reward_events re
|
|
ON re.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_admin_events ae
|
|
ON ae.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_orderbook_events oe
|
|
ON oe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_token_account_events tae
|
|
ON tae.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'pump_swap'
|
|
AND (
|
|
tx.err_json IS NULL
|
|
OR tx.err_json = ''
|
|
OR tx.err_json = 'null'
|
|
)
|
|
AND te.id IS NULL
|
|
AND le.id IS NULL
|
|
AND pe.id IS NULL
|
|
AND fe.id IS NULL
|
|
AND re.id IS NULL
|
|
AND ae.id IS NULL
|
|
AND oe.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, '$.skipLiquidityReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipLifecycleReason')), '') = ''
|
|
AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipCatalogReason')), '') = ''
|
|
GROUP BY de.event_kind
|
|
ORDER BY unexplained_count DESC, de.event_kind;
|
|
|
|
-- 10. Materialization summary.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(DISTINCT de.id) AS decoded_count,
|
|
COUNT(DISTINCT te.id) AS trade_count,
|
|
COUNT(DISTINCT le.id) AS liquidity_count,
|
|
COUNT(DISTINCT pe.id) AS lifecycle_count,
|
|
COUNT(DISTINCT fe.id) AS fee_count,
|
|
COUNT(DISTINCT re.id) AS reward_count,
|
|
COUNT(DISTINCT ae.id) AS admin_count,
|
|
COUNT(DISTINCT oe.id) AS orderbook_count
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_trade_events te
|
|
ON te.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_liquidity_events le
|
|
ON le.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_lifecycle_events pe
|
|
ON pe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_fee_events fe
|
|
ON fe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_reward_events re
|
|
ON re.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_admin_events ae
|
|
ON ae.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_orderbook_events oe
|
|
ON oe.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'pump_swap'
|
|
GROUP BY de.event_kind
|
|
ORDER BY de.event_kind;
|
|
|
|
|
|
|
|
-- 11. PumpSwap local instruction discriminator coverage.
|
|
-- Expected result after Solscan IDL/log-name promotion: covered rows only, except the Anchor self-CPI log carrier.
|
|
-- Rows tagged coverage_gap must be promoted to an explicit local decoder status or excluded with proof.
|
|
SELECT
|
|
io.instruction_name,
|
|
io.discriminator_hex,
|
|
COUNT(*) AS observed_count,
|
|
COUNT(DISTINCT io.signature) AS tx_count,
|
|
CASE
|
|
WHEN io.discriminator_hex = 'e445a52e51cb9a1d' THEN 'technical_anchor_self_cpi_log'
|
|
WHEN ce.id IS NULL THEN 'coverage_gap'
|
|
ELSE 'covered'
|
|
END AS observation_coverage_status,
|
|
ce.local_event_kind,
|
|
ce.expected_db_target,
|
|
ce.proof_status
|
|
FROM k_sol_instruction_observations io
|
|
LEFT JOIN k_sol_dex_event_coverage_entries ce
|
|
ON ce.decoder_code = 'pump_swap'
|
|
AND ce.discriminator_hex = io.discriminator_hex
|
|
AND (
|
|
ce.entry_name = io.instruction_name
|
|
OR ce.local_event_kind = ('pump_swap.' || io.instruction_name)
|
|
)
|
|
WHERE io.decoder_code = 'pump_swap'
|
|
GROUP BY
|
|
io.instruction_name,
|
|
io.discriminator_hex,
|
|
observation_coverage_status,
|
|
ce.local_event_kind,
|
|
ce.expected_db_target,
|
|
ce.proof_status
|
|
ORDER BY observed_count DESC, io.instruction_name, io.discriminator_hex;
|
|
|
|
-- 12. PumpSwap successful trade candidates without materialized trade.
|
|
-- Expected result: empty unless skipTradeReason explicitly documents why amounts/direction are incomplete.
|
|
SELECT
|
|
de.event_kind,
|
|
json_extract(de.payload_json, '$.skipTradeReason') AS skip_trade_reason,
|
|
COUNT(*) AS decoded_count,
|
|
COUNT(te.id) AS trade_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
|
|
WHERE de.protocol_name = 'pump_swap'
|
|
AND de.event_kind IN (
|
|
'pump_swap.buy',
|
|
'pump_swap.sell',
|
|
'pump_swap.buy_exact_quote_in'
|
|
)
|
|
AND (
|
|
tx.err_json IS NULL
|
|
OR tx.err_json = ''
|
|
OR tx.err_json = 'null'
|
|
)
|
|
GROUP BY de.event_kind, skip_trade_reason
|
|
HAVING COUNT(te.id) = 0
|
|
AND COALESCE(TRIM(skip_trade_reason), '') = ''
|
|
ORDER BY decoded_count DESC, de.event_kind;
|
|
|
|
-- 13. PumpSwap exact amount-source summary.
|
|
-- Trades must come from transaction/vault deltas or another exact amount source, never from instruction bounds alone.
|
|
SELECT
|
|
de.event_kind,
|
|
json_extract(de.payload_json, '$.amountSource') AS amount_source,
|
|
COUNT(*) AS decoded_count,
|
|
COUNT(te.id) AS trade_count
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_trade_events te
|
|
ON te.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'pump_swap'
|
|
AND de.event_kind IN (
|
|
'pump_swap.buy',
|
|
'pump_swap.sell',
|
|
'pump_swap.buy_exact_quote_in'
|
|
)
|
|
GROUP BY de.event_kind, amount_source
|
|
ORDER BY de.event_kind, amount_source;
|
|
|
|
-- 14. PumpSwap trade/candle parity.
|
|
-- Expected result: no trade without at least one candle row after candle aggregation.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(DISTINCT te.id) AS trade_count,
|
|
COUNT(DISTINCT pc.id) AS candle_count
|
|
FROM k_sol_dex_decoded_events de
|
|
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 = 'pump_swap'
|
|
GROUP BY de.event_kind
|
|
ORDER BY de.event_kind;
|