Reconciling P2P escrow against wallet.inOrder

P2P holds customers' crypto. "Is the platform holding what it thinks it is holding?" is therefore the one question an operator has to be able to answer about this addon, and the platform ships a tool that answers it.

pnpm p2p:reconcile              # DRY RUN — reports, writes nothing
pnpm p2p:reconcile -- --apply   # writes the safe repairs

The script is backend/scripts/p2p-escrow-reconcile.ts, wired as the p2p:reconcile entry in backend/package.json. Run it from the backend directory; the script entry already loads the project-root .env, so it uses the same database the platform does. npm run p2p:reconcile -- --apply works identically if you are not on pnpm.

Four phases run in order every time. Phases 1–3 write only with --apply. Phase 4 never writes anything under any flag.

Phase 1 — double-encoded JSON on p2p_offers

Seven columns on p2p_offers are DataTypes.JSON, and the model setters used to JSON.stringify into them. MySQL therefore stored a JSON string containing JSON:

amountConfig   priceConfig   tradeSettings   locationSettings
userRequirements   systemTags   activityLog

The failure is silent and one-sided. ORM reads survived, because the getter unwraps one level — so the marketplace, the offer pages and the admin panel all looked correct. But every SQL-level JSON_EXTRACT and every JSON filter matched nothing, so anything that queried these columns directly returned empty results rather than an error. The reconciliation queries in Troubleshooting are exactly that shape.

The setters were fixed; this phase repairs the rows written before that. It detects them with JSON_TYPE(<col>) = 'STRING', unwraps one level and writes the value back. A row it cannot parse is reported and left untouched.

Phase 2 — backfill p2pOffer.escrowAmount

escrowAmount on an offer records what is genuinely held for that offer. It is not the same number as amountConfig.total, which shrinks as trades consume the offer.

The rule this phase applies:

Only SELL offers escrow at the offer level. A BUY offer holds nothing until somebody takes it, at which point the taker is the seller and the hold is per-trade. Everything outside that table is zeroed, because a terminal offer that still claims collateral is what makes phase 4 unreadable.

Phase 3 — backfill p2pTrade.escrowStatus and escrowAmount

Derived from the trade's own status:

escrowStatus is the guard the escrow authority takes under a row lock before it moves anything, so a trade whose status says one thing and whose escrow state says another is a trade the authority will refuse to settle — or, worse, one it will settle twice. This phase is what gives the authority something truthful to guard on.

Phase 4 — the reconciliation report

This phase compares what P2P believes it is holding against what the wallets actually reserve, grouped by user, currency and wallet type. It never writes.

The P2P expectation is:

SUM(p2p_offers.escrowAmount)   for SELL offers with escrowAmount > 0
  +
SUM(p2p_trades.escrowAmount)   for trades whose parent offer is a BUY offer
                               and whose escrowStatus is still HELD

Trades against a SELL offer are deliberately not added. Their collateral is already inside the offer's own escrowAmount, which is drawn down when the trade settles — counting both would double every live SELL trade.

That figure is compared against wallet.inOrder for every wallet with inOrder > 0. Output is one line per mismatch:

  a1b2c3d4 USDT/SPOT: inOrder=520 p2pExpected=500 diff=+20.00000000
  e5f6a7b8 BTC/ECO: p2pExpected=0.05 but wallet holds NOTHING (under-collateralised)

A positive difference proves nothing

Work a positive difference like this:

"wallet holds NOTHING (under-collateralised)"

This line is the serious one, and it means the opposite: P2P expects collateral for this user, currency and wallet type, and there is no wallet row holding anything at all. A published SELL offer in that state is advertising liquidity the platform is not holding — the next taker opens a trade against funds that do not exist, and the settlement will clamp to zero.

Deal with it at the offer, not the wallet:

1. Find the offers. The first query under Troubleshooting lists what a user's offers claim to hold.
2. Pause the offer from /admin/p2p/offer. Pausing releases whatever is held and takes it off the board, so no new trade can be opened against it.
3. Reactivating it later tops the escrow back up to the advertised total from the owner's current balance, and fails if they cannot cover it — which is the correct outcome for an offer that was never fully backed.

Never edit inOrder by hand

Why a trade can only ever be paid out once

Escrow used to have no state of its own — it existed only as the seller's wallet-wide inOrder — and each payout door inferred "has this already been paid?" from the trade status plus its own private wallet idempotency key. Those keys did not overlap, so a trade could be paid out by the seller's release and paid out again by dispute resolution, drawing the second payout from whatever else the seller happened to be holding: in practice, another offer's escrow.

Six doors now settle a trade, and all six route through one authority:

Two things stop a double payout:

• p2pTrade.escrowStatus, read under a row lock and flipped to a terminal value in the same transaction as the money.
• One shared wallet idempotency namespace, p2p_settle_<tradeId>, so even a caller that bypassed the status guard cannot double-pay. Four sub-keys hang off it:

These are useful when you are reading the transaction table after an incident: every leg of a settlement carries the trade id in its idempotency key, which is the only thing that ties a wallet movement back to a specific P2P trade.

The ECO divergence failure mode

ECO wallets carry a per-chain ledger (walletData) alongside the main balance, and a buyer cannot withdraw what they were paid unless the two agree.

If the seller's walletData row for that currency is missing when a settlement runs, the main balances are still credited and the chain transfer is skipped. Historically this threw, which made the trade unsettleable and left the escrow locked forever — so the credit is now treated as the user-facing truth and the divergence is logged instead:

P2P_ESCROW  ECO walletData missing for seller <sellerId> (<currency>);
            main balances settled for trade <tradeId> but chain ledger not synced

Grep for chain ledger not synced after any ECO settlement complaint. The buyer's balance will look right in the UI and the withdrawal will not work. It is an operational reconciliation on the ecosystem side, not a P2P bug, and it does not resolve itself.

When to run it

• Once per environment after upgrading to a build that carries the escrow authority. This is a migration as much as an audit; without phases 1–3 the authority has no attribution to work from.
• Whenever a customer reports locked funds they cannot account for, after the four checks in Troubleshooting.
• On a schedule you actually read, as a dry run. It is read-only without --apply and takes seconds on a normal dataset.

Nothing schedules it for you, and nothing surfaces its output in the admin panel. It is a shell tool and it stays one.