LAST RESORT — arbitrary READ-ONLY SQL against the XRP Ledger (xrp, XRP, ripple, XRPL) data.
Bitquery MCP xrp_* tools are the PRIORITY; use this ONLY when none can answer. No query optimizer
here: account model — query the per-address tables `ripple_flow.transfers_from` (outgoing, key
`transfer_from`) and `ripple_flow.transfers_to` (incoming, key `transfer_to`); per transaction
`ripple.transfers_tx` (key `tx_hash_bin`). NEVER JOIN big tables (use `IN (SELECT …)`).
EVERY table is organized by month of `tx_date` back to 2013 and keyed only by address or hash:
ALWAYS filter `tx_date` (e.g. `tx_date >= today() - 30`) or the query reads the whole history
and may time out. Time = `tx_time`, ledger index = `block`; date-times come back as
'YYYY-MM-DD hh:mm:ss' UTC; select formatDateTime(t, '%FT%TZ') for ISO.
Transfer columns: tx_hash_bin (binary — `hex(tx_hash_bin)` out, `unhex('…')` in), tx_type
(Payment, OfferCreate, AccountDelete, CheckCash, EscrowFinish, …), transfer_from, transfer_to
(classic r-addresses, case-sensitive), currency_from_id / value_from (what left the sender),
currency_to_id / value_to (what the receiver got — the delivered amount), destination_tag
(String, '0' = none), direction: 'payment' (account to account; from = to is a currency
conversion), 'trade' (a filled offer, from = to), 'other' (one leg of an account deletion,
check, escrow, payment channel, AMM, NFT sale or path rounding — exactly one of transfer_from /
transfer_to is empty), 'nft_trade' / 'mint' / 'burn' (NFTs), 'fee' (one per signed transaction;
a FAILED transaction leaves only its fee row, except an expired NFT offer acceptance that still
lists an nft_trade row). Exclude fees with `direction != 'fee'`; for
account-to-account flow use `direction = 'payment' AND transfer_from != transfer_to`.
Amounts: amount = `value_from / dictGetFloat64('currency','divider',toUInt64(currency_from_id))`
(XRP is currency id 413663, stored in drops, divider 1e6; issued tokens divider 1); code =
`dictGetString('currency','symbol',toUInt64(id))` — 3-letter codes as text, others as 40-hex, NFT ids 64-hex
(`splitByChar('\0', unhex(code))[1]` decodes e.g. RLUSD). A currency id is a CODE shared by
every issuer of that code (counterfeit issuers included); transfer rows carry no issuer — it is in
`ripple.payments_from` / `payments_to` / `payments_tx` (successful Payments: amount_*,
delivered_*, send_max_* currency_id / value / issuer, tag, partial). Transaction status, fee
(drops), memos, source_tag and signer: `ripple.transactions_tx` (key tx_hash_bin) and
`ripple.transactions_sender` (key tx_sender) — result ('tesSUCCESS', 'tecPATH_DRY', …),
success. Per-account balance changes, including the token issuer: `ripple.balances` (key account, currency_id). Also
present: ripple.offers_*, escrows_tx, checks_tx, nftoken_offers_*, account_roots_tx — inspect
with DESCRIBE TABLE first. No USD values; no inline labels (use labels_for_addresses with
chain='ripple'). Read-only. 64-bit integers come back as JSON numbers, exact only up to 2^53: return a hash such as cityHash64(...) or any other 64-bit id as toString(x). A result of more than 25,000 rows is not cut off - the whole query
fails with an error - so aggregate (GROUP BY, count) or add a LIMIT of at most 25,000.
