Implement a write-ahead log (WAL) for all Soroban transaction submissions to guarantee exactly-once execution and full crash recovery

[Chucks1093]

Summary

The current buy/sell flow submits a Soroban transaction and immediately updates the database. If the server crashes between submission and the database write, the transaction may have executed on-chain but the database reflects the pre-transaction state — leading to permanent inconsistency. This issue implements a write-ahead log that guarantees the server can always recover to a consistent state after a crash, even mid-submission.

Scope

1. WAL table

• Add a SorobanWALEntry table: id, operation (buy/sell), wallet, creatorWallet, amount, expectedSupplyBefore, xdrPayload, state (PENDING | SUBMITTED | CONFIRMED | FAILED | ROLLED_BACK), txHash, submittedAt, confirmedAt, error, createdAt
• Write the WAL entry with state PENDING inside the same database transaction that validates the request — before any Soroban call is made
• Only proceed to Soroban submission after the WAL entry is durably written

2. Submission and confirmation

• After WAL entry is written, submit the Soroban transaction and update the WAL entry to SUBMITTED with the txHash
• Poll Horizon for the transaction result; on confirmation update to CONFIRMED and apply all database side effects (balance update, holder count, price snapshot, transaction history) in a single atomic database transaction
• On submission failure: update WAL entry to FAILED with the error, roll back any optimistic state

3. Recovery worker

• On server startup, scan for WAL entries in PENDING or SUBMITTED state older than 30 seconds
• For PENDING entries: the transaction was never submitted — mark as ROLLED_BACK
• For SUBMITTED entries: query Horizon for the txHash result; if confirmed apply side effects and mark CONFIRMED; if failed mark FAILED; if still pending re-poll up to 10 times with exponential backoff before marking FAILED
• Recovery worker runs once at startup and then every 60 seconds

4. Idempotency guard

• Before applying database side effects from a confirmed transaction, check whether a WAL entry for that txHash has already been applied — if so skip (idempotent confirmation)
• Apply all side effects in a single database transaction with the WAL state update to CONFIRMED as the last operation so a crash during side-effect application results in a re-applied confirmation on next recovery

5. Integration tests

• Simulate a crash between WAL PENDING write and Soroban submission — run recovery worker — assert WAL entry is ROLLED_BACK and no side effects applied
• Simulate a crash between SUBMITTED and CONFIRMED database write — run recovery worker — assert side effects are applied exactly once
• Submit two requests with the same idempotency key — assert only one WAL entry and one set of side effects
• Confirm the final database state after recovery matches what a crash-free execution would produce

Acceptance Criteria

• WAL entry written before any Soroban call
• PENDING entries on startup rolled back by recovery worker
• SUBMITTED entries resolved against Horizon by recovery worker
• All side effects applied atomically with WAL state transition
• Idempotency guard prevents double-application of confirmed transactions
• Database state after crash recovery identical to crash-free execution

ETA: 24 hours

---

Coordinate on Telegram

[nwosum113-ui]

hello maintainer, i would love to work on this issue

[jchinedu]

let me work on this issue

[nomsoscript]

Hi maintainers!
I’d love to help out with this issue. I’ve checked the current codebase/documentation and know exactly what needs to be updated to make this clearer. I will ensure everything aligns perfectly with your existing contribution guidelines.

[k-deejah]

Hello Maintainer,

I’d love to work on this issue. I have experience with backend development, database transactions, and Stellar/Soroban integrations, and I understand the consistency and recovery requirements involved in making transaction submission crash-safe.

I can implement the SorobanWALEntry model and durable PENDING state before submission, the submission/confirmation flow with Horizon polling, and the startup/interval recovery worker for interrupted transactions. I’ll also ensure confirmation is idempotent and that all database side effects are applied atomically with the WAL state transition.

I’ll cover the crash-recovery scenarios and idempotency requirements with integration tests, including crashes before submission, after submission, duplicate requests, and recovery of pending/failed transactions.

I can deliver a clean, well-tested implementation within the 24-hour ETA and coordinate on Telegram as needed.

[edehvictor]

Hey, can I work on this

[grantfox-oss]

🦊 GrantFox — @edehvictor has been assigned to this issue as part of the Third Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #756)
2. Your PR will be reviewed by the accesslayerorg maintainers

Good luck! Track your progress on GrantFox.