khadhroony-bobobot/validation_sql/SQL_VALIDATION_METEORA_DBC_0_7_56.sql

-- file: validation_sql/SQL_VALIDATION_METEORA_DBC_0_7_56.sql

-- 0.7.56 meteora_dbc validation and corpus-seed checklist.
-- Run on a dedicated fresh SQLite database for the Meteora DBC tranche.
-- Recommended replay settings after each backfill group:
-- skipDexDecode=no, forceDexDecode=yes, deferInstructionObservations=yes.
-- This file is intentionally read-only: it never mutates the database.

-- 00. Corpus seed: upstream fallback samples to backfill first.
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_dbc'
GROUP BY upstream_entry_name, upstream_discriminator_hex, source_repo
ORDER BY fallback_count DESC, upstream_entry_name, upstream_discriminator_hex;

-- 01. Corpus seed: 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_dbc'
GROUP BY instruction_name, discriminator_hex
ORDER BY observed_count DESC, instruction_name, discriminator_hex;

-- 02. Coverage meteora_dbc.
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_dbc'
ORDER BY entry_kind, entry_name, discriminator_hex;

-- 03. Decoded events 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_dbc'
GROUP BY de.event_kind
ORDER BY decoded_count DESC, de.event_kind;

-- 04. Decoded meteora_dbc events without coverage.
-- Target after closure: empty for all locally decoded meteora_dbc rows.
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_dbc'
   AND ce.local_event_kind = de.event_kind
WHERE de.protocol_name = 'meteora_dbc'
  AND ce.id IS NULL
GROUP BY de.event_kind
ORDER BY decoded_count DESC, de.event_kind;

-- 05. Residual upstream fallback for entries covered locally.
-- Target after local promotion: empty for every entry that has a local_event_kind.
SELECT
    json_extract(ug.payload_json, '$.upstreamEntryName') AS upstream_entry_name,
    json_extract(ug.payload_json, '$.upstreamDiscriminatorHex') AS upstream_discriminator_hex,
    json_extract(ug.payload_json, '$.upstreamSourceRepo') AS source_repo,
    ce.local_event_kind,
    ce.expected_db_target,
    ce.proof_status,
    COUNT(*) AS fallback_count,
    COUNT(DISTINCT ug.transaction_id) AS tx_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events ug
LEFT JOIN k_sol_chain_transactions tx
    ON tx.id = ug.transaction_id
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 COALESCE(ce.discriminator_hex, '') = COALESCE(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') = 'meteora_dbc'
GROUP BY upstream_entry_name, upstream_discriminator_hex, source_repo, ce.local_event_kind, ce.expected_db_target, ce.proof_status
ORDER BY fallback_count DESC, upstream_entry_name;

-- 06. Successful non-materialized events without explicit skip reason.
-- Target after closure: empty. Decoded-only rows must carry skip*Reason or informational/audit policy.
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_dbc'
  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, '$.skipAdminReason')), '') = ''
GROUP BY de.event_kind
ORDER BY unexplained_count DESC, de.event_kind;

-- 07. Failed transaction materialization safety.
-- Target after closure: empty. Failed transactions may be decoded for audit, but must not be business-materialized.
SELECT
    de.event_kind,
    COUNT(DISTINCT de.id) AS decoded_failed_count,
    COUNT(DISTINCT te.id) AS trade_count,
    COUNT(DISTINCT lae.id) AS launch_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,
    COUNT(DISTINCT tae.id) AS token_account_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_dbc'
  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 launch_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
    OR token_account_count > 0
ORDER BY decoded_failed_count DESC, de.event_kind;

-- 08. Multi-target materialization safety.
-- Target after closure: empty. One decoded event must not feed several business targets.
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 lae.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)
        + (CASE WHEN tae.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_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_dbc'
)
WHERE target_count > 1
ORDER BY target_count DESC, event_kind, signature;

-- 09. Materialization summary by table with successful/failed split.
SELECT
    de.event_kind,
    COUNT(DISTINCT de.id) AS decoded_count,
    COUNT(DISTINCT CASE WHEN tx.err_json IS NULL OR tx.err_json = '' OR tx.err_json = 'null' THEN de.id END) AS successful_decoded_count,
    COUNT(DISTINCT CASE WHEN tx.err_json IS NOT NULL AND tx.err_json <> '' AND tx.err_json <> 'null' THEN de.id END) AS failed_decoded_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 adm.id) AS admin_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_pool_admin_events adm
    ON adm.decoded_event_id = de.id
WHERE de.protocol_name = 'meteora_dbc'
GROUP BY de.event_kind
ORDER BY decoded_count DESC, de.event_kind;

-- 10. Instruction observation versus coverage.
-- Target after closure: every observed discriminator must map to a known coverage row.
SELECT
    io.instruction_name,
    io.discriminator_hex,
    COUNT(*) AS observed_count,
    MIN(io.signature) AS sample_signature,
    ce.entry_name,
    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 = 'meteora_dbc'
   AND (
       ce.discriminator_hex = io.discriminator_hex
       OR ce.entry_name = io.instruction_name
   )
WHERE io.decoder_code = 'meteora_dbc'
GROUP BY io.instruction_name, io.discriminator_hex, ce.entry_name, ce.local_event_kind, ce.expected_db_target, ce.proof_status
ORDER BY observed_count DESC, io.instruction_name, io.discriminator_hex;

-- 11. Anti-faux trade/candle for non-swap events.
-- Target after closure: 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_dbc'
  AND de.event_kind NOT IN ('meteora_dbc.swap', 'meteora_dbc.swap2')
GROUP BY de.event_kind
HAVING trade_count > 0 OR candle_count > 0
ORDER BY trade_count DESC, candle_count DESC, de.event_kind;

-- 12. Global watchlist after replay.
-- Target after closure: no meteora_dbc as dominant backlog; unrelated residuals may remain.
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_dbc')
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;

-- 13. Fee parent/legs summary after 0.7.56 fee model.
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_dbc'
GROUP BY
    de.protocol_name,
    de.event_kind
ORDER BY
    de.protocol_name,
    de.event_kind;

-- 14. Fee parent scalar without amount leg.
-- Target after closure: 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_dbc'
  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;

-- 15. Orphan fee amount legs.
-- Target after closure: 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;

-- 16. Generic allowlisted recovery must not be used by DBC.
-- Target after closure: empty; DBC uses protocol-specific fee recovery paths.
SELECT
    de.protocol_name,
    de.event_kind,
    tx.signature,
    fee.id AS fee_event_id,
    fea.leg_index,
    fea.token_mint,
    fea.amount_raw,
    fea.amount_source
FROM k_sol_fee_event_amounts fea
JOIN k_sol_fee_events fee
    ON fee.id = fea.fee_event_id
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
WHERE de.protocol_name = 'meteora_dbc'
  AND fea.amount_source = 'allowlisted_inner_spl_transfer'
ORDER BY
    de.event_kind,
    tx.signature,
    fea.leg_index;