Wallet Audit Guide

How to run the per-wallet pack audit and read the resulting CSV. The audit reconciles every pack the wallet has opened against the Solana chain so you can find spins where a user paid but never got a card, refund, or turbo-buyback.

When to run an audit

• A user reports they paid for a pack and never received their card / refund / USDC.
• You want to confirm that every spin recorded in the DB has a matching outcome on chain.
• You want to find chain activity for a wallet that the DB doesn't know about.

Running the script

From the project root of the gachamachine repo:

/Users/admin/.pyenv/versions/3.11.1/bin/uv run --script scripts/python/audit_user_packs.py <WALLET_ADDRESS>

The script writes two files to scripts/python/results/:

• pack-audit-<WALLET_ADDRESS>.csv — one row per spin, with a verdict column
• pack-audit-<WALLET_ADDRESS>.raw.jsonl — the raw /api/stats rows, for debugging

A 100k-spin wallet takes ~5–10 minutes. Progress lines stream to the terminal as pages are verified.

Useful flags

• --no-cross-check — skips the wallet-history walk (Step 3 below). Use for very large wallets when you only need the DB-side reconciliation.
• --devnet — point at devnet instead of mainnet.

What the script does

1. Pulls every pack record for the wallet from /api/stats?data=linkedAll&userWallet=<wallet> (the DB's pre-joined view).
2. Verifies every recorded signature on-chain via Helius getSignatureStatuses (batched 100 at a time, with full transaction-history search).
3. (Cross-check) Walks the wallet's parsed Helius transaction history and flags any memo that interacts with the gacha wallet but doesn't appear in the DB. These are written as CHAIN_ORPHAN rows.

Reading the CSV in Excel

Setup (once per file)

1. Open the CSV. The header row is row 1.
2. Data → Filter (or Ctrl/Cmd + Shift + L). Dropdown arrows appear on every column header.
3. View → Freeze Top Row so headers stay visible while scrolling.

The four filters that matter

A. Real liabilities — the only rows employees usually act on

User paid, nothing came back. To find them:

• Filter verdict = MISSING_OUTCOME
• Filter spin_onchain = finalized

For each row:

1. Copy spin_signature and paste into solscan.io to confirm the payment landed on chain.
2. Use spin_cost to compute what's owed.
3. Use pack_type and spin_created_at to find the user / session in the DB.

B. Chain orphans — chain has it, DB doesn't

• Filter verdict = CHAIN_ORPHAN

Each row gives:

• chain_orphan_signature — paste into Solscan to inspect.
• memo — search the DB for this memo. If it doesn't exist, the chain processed something the DB missed.

C. Send / refund / buyback failures

Older issues, mostly resolved. To narrow to current ones only:

• Filter verdict = any of SEND_FAILED, SEND_NOT_ON_CHAIN, REFUND_FAILED, REFUND_NOT_ON_CHAIN, TURBO_BUYBACK_FAILED, TURBO_BUYBACK_NOT_ON_CHAIN
• Filter note = (Blanks) — this excludes rows auto-tagged as stale or known-bug artifacts.

D. Everything safe to ignore (typically >99% of rows)

• verdict ∈ FREE_SPIN_NO_OUTCOME

Key columns

Column	What it tells you
verdict	Overall outcome. First thing to filter on.
note	Auto-tagged reason if the row is likely a stale / old-bug artifact. Blank = current issue worth investigating.
age_days	How old the spin is. Sort ascending to see newest issues first.
spin_onchain	finalized = user paid; failed / not_found = user not charged.
spin_cost	Dollar amount the spin charged.
spin_signature	Paste into solscan.io to see the chain tx.
pack_type	Which pack (pokemon_50, onepiece_250, …).
spin_created_at	When the spin was attempted (UTC).

Verdict reference

Verdict	Meaning	Action
SEND_OK	Card NFT was sent to user.	None — healthy.
TURBO_BUYBACK_OK	Turbo mode auto-sold the card and sent USDC.	None — healthy.
REFUND_OK	Refund landed on chain.	None — healthy.
FREE_SPIN_NO_OUTCOME	Free spin with no recorded outcome.	None — expected for free spins.
MISSING_OUTCOME	Spin recorded, but no card / refund / buyback. Combine with spin_onchain to triage.	If spin_onchain = finalized → user paid, owe them. If failed / not_found → user not charged, no action.
SEND_FAILED	Send-card tx errored on chain.	Investigate; may need manual re-send.
SEND_NOT_ON_CHAIN	Send sig recorded in DB but Helius can't find the tx.	Check note; if blank, escalate. If "likely indexer-pruning", ignore.
REFUND_FAILED / REFUND_NOT_ON_CHAIN	Same as above for refunds.	Same logic.
TURBO_BUYBACK_FAILED / _NOT_ON_CHAIN	Same as above for turbo buybacks.	Same logic.
CHAIN_ORPHAN	Tx involves the gacha wallet on chain but no DB row matches.	Look up the memo in the DB; if missing, the system never recorded it.
PLACEHOLDER_NO_OUTCOME	Spin sig is a placeholder (freepack, purchasedpack, turbomode) and no outcome recorded.	Usually benign; check pack_type.
EMPTY_ROW	No signatures at all.	DB hygiene issue; report.

One-screen TL;DR for employees

1. Open the CSV. Apply filter (Ctrl/Cmd + Shift + L).
2. Set verdict = MISSING_OUTCOME AND spin_onchain = finalized. These are the only rows where a user actually paid and got nothing.
3. For each row, copy spin_signature → paste into solscan.io to confirm the payment landed.
4. Use spin_cost to compute what's owed. Use pack_type and spin_created_at to find the user / session.
5. (Optional) Filter verdict = CHAIN_ORPHAN to find chain activity the DB doesn't know about.

Troubleshooting

• HELIUS_API_KEY not set — make sure .env.local in the gachamachine repo root contains a HELIUS_API_KEY=… line.
• uv: command not found — use the explicit path: /Users/admin/.pyenv/versions/3.11.1/bin/uv. The pyenv shim is broken on the current global Python version.
• 429 rate-limited — the script auto-retries with exponential backoff. If it persists, the Helius plan is being hit too hard; reduce concurrency or run during off-hours.
• Script crashes mid-run — both the CSV and the raw JSONL flush every page (~1000 rows), so partial output is always usable. Re-running starts fresh; resumable runs are not currently supported.