[Backend] Idempotent On-Chain Transaction Submission via Bull Queue with Deduplication and Reconciliation

[david87131]

Task Classification: Reliability / Data-Integrity hardening (transaction lifecycle)
Affected Layers: backend (primary), frontend (status surface)
Affected Paths: backend/src/modules/stellar/, backend/src/modules/queues/, backend/src/modules/transactions/, backend/src/modules/payments/, backend/src/blockchain/, backend/src/migrations/, frontend/app/payments/, frontend/lib/stellar.ts
Severity: High
Estimated Window: 40-64 hours

Technical Context & Monorepo Integration Failure

The queues module enqueues blockchain jobs onto a Bull queue backed by Redis; the stellar module consumes them, builds a Stellar transaction, signs it with a source account, and submits it through Horizon/RPC. Two properties of that pipeline are unmodeled:

• Bull retries a job on any thrown error, including timeouts where the transaction may already have reached the network. A retried job rebuilds and resubmits, producing a second on-chain effect for one logical operation (rent payment, escrow release, obligation settlement).
• All jobs draw from a shared source account. Sequence numbers are read from Horizon at build time. Concurrent jobs, or a retry racing the original, read the same sequence, so one transaction succeeds (tx_bad_seq on the loser) and the DB row is marked failed while funds moved, or both are built against stale sequences and both fail.

The database status column and the chain state are updated in separate steps with no ledger of intent between enqueue and confirmation. A worker crash after submit but before the DB write leaves a transactions row in pending forever while the ledger holds a confirmed payment. A single-layer fix is insufficient: enforcing dedup only in stellar still lets payments enqueue duplicate logical operations; adding a DB unique constraint without sequence serialization still double-spends when two workers hold the same sequence; and the frontend continues to poll a status that never resolves because no component distinguishes "submitted but unconfirmed" from "confirmed on ledger."

Drift walkthrough (missing control: idempotency key + submission ledger + confirmation reconciler):

1. Tenant submits monthly rent. payments service enqueues job pay-rent with obligation id O.
2. stellar worker builds tx against source sequence S, signs, submits. Horizon accepts but the HTTP response is lost to a 30s gateway timeout.
3. Bull marks the job failed and re-queues attempt 2.
4. Attempt 2 reads sequence S+1 (ledger already advanced), rebuilds the identical payment, submits, succeeds.
5. Ledger now holds two USDC transfers for obligation O. The transactions table has one failed row (attempt 1) and one confirmed row (attempt 2); the landlord is paid twice and reconciliation has no record tying both effects to O.

Absent invariant: exactly one confirmed on-chain effect per idempotency key, with every submission attempt recorded before network I/O and resolved to a terminal chain status by a reconciler.

Core Component Invariants & Code Paths

Smart Contract Infrastructure
No new contract logic. The payment, escrow, and rent_obligation crates already emit typed events via env.events().publish (for example rent_paid, escrow_released). The reconciler keys off these event topics and the transaction hash. Invariant: the backend treats the contract event, not the Horizon submit response, as proof of effect. No crate edits beyond confirming event topics are stable and documented; if a topic symbol is renamed, update the backend decoder in the same PR to avoid silent reconciliation misses.

Backend/API Layer

• New TypeORM entity submission_ledger (migration under backend/src/migrations/): columns idempotency_key (unique), logical_operation, source_account, allocated_sequence, tx_hash (nullable), status enum (building, submitted, confirming, confirmed, failed), attempt_count, last_error_code, created_at, updated_at. Unique index on idempotency_key; index on (source_account, allocated_sequence).
• stellar module: introduce SequenceAllocatorService. Two supported strategies - a pool of channel accounts (each job leases one, so sequences never collide across jobs) or a Redis-serialized allocator (SETNX/Lua) that hands out monotonic sequences per source account. Invariant: no two in-flight submissions ever hold the same (source_account, sequence).
• TransactionBuilderService.submit() must write the submission_ledger row to submitted before network I/O and record tx_hash immediately after build. Invariant: a submission attempt is persisted before it can reach Horizon.
• queues module: derive the Bull job id from the idempotency key so Bull's own dedup rejects duplicate enqueues; classify errors in a RetryClassifierService - transient (504, tx_too_late, connection reset, tx_bad_seq) versus terminal (tx_failed with op_underfunded, malformed tx, auth failure). Only transient errors retry; terminal errors mark the ledger failed and stop. Invariant: retries never re-run a job whose logical effect is already confirmed (guard reads submission_ledger first).
• New ReconcilerService (scheduled via a Bull repeatable job): for every submitted/confirming row, query Horizon by tx_hash and the contract event, then transition to confirmed or failed. Invariant: no ledger row remains non-terminal past the confirmation window; DB status converges to chain status.
• transactions module exposes read model GET /transactions/:id/status returning the ledger status enum. payments service generates the idempotency key deterministically from (logical_operation, obligation_id, period) so the same logical operation always maps to one key.
• Env vars: STELLAR_CHANNEL_ACCOUNTS (comma-separated secret keys, sourced from AWS Secrets Manager), TX_CONFIRMATION_TIMEOUT_MS, RECONCILER_INTERVAL_MS, TX_MAX_TRANSIENT_RETRIES.

Frontend Client

• frontend/lib/stellar.ts and frontend/app/payments/: consume GET /transactions/:id/status via TanStack Query with polling that backs off once confirmed/failed is reached. Render four states: submitted, confirming, confirmed, failed. Invariant: the UI never shows a terminal "success" from an enqueue acknowledgement alone; success requires confirmed from the ledger read. Failed states surface the classified error category (transient exhausted vs terminal) without a raw stack. Reuse the query key convention already in app/payments.

Root Configuration & Orchestration

• No Cargo/root workspace change. Backend CI (.github/workflows/backend-ci-cd.yml) gains a migration-check step and a job asserting the new entity migration is present and reversible. Frontend CI (frontend-ci-cd.yml) needs no new job beyond type checks for the added status types. Shared status enum string values are duplicated as a frontend/lib type; keep the two definitions in sync in the same PR.

Verification & Acceptance Criteria

• Backend compiles; pnpm --filter backend build and pnpm --filter backend lint pass.
• Frontend compiles; pnpm --filter frontend build and type check pass with the four-state union.
• Migration migration:run and migration:revert both succeed against a clean PostgreSQL instance; submission_ledger unique index on idempotency_key verified.
• Unit tests: RetryClassifierService maps each transient/terminal code correctly; SequenceAllocatorService never returns a duplicate (source_account, sequence) under concurrent requests.
• Integration tests (Bull + Redis + PostgreSQL): a job that submits then throws a transient timeout does not produce a second on-chain effect; the guard reads submission_ledger and short-circuits.
• Integration test: two concurrent jobs on one source account resolve to distinct sequences and both reach a terminal state with no tx_bad_seq double-spend.
• End-to-end (testnet or Horizon stub): enqueue rent payment, kill the worker after submit, restart, confirm ReconcilerService converges the row to confirmed with no duplicate.
• Frontend end-to-end: status transitions submitted -> confirming -> confirmed render; failed path renders classified category.
• PR attachments: submission_ledger state dump before/after the crash-recovery test; Bull job-id dedup log showing the duplicate enqueue rejected; reconciler log showing a stuck submitted row transitioned to terminal.

Suggested Execution Path

Phase 1 - Backend data model and allocator (12-18h). Add submission_ledger entity and migration; implement SequenceAllocatorService (channel-account pool plus Redis serialized fallback). Deliverable: migration up/down green, allocator unit tests. Exit check: concurrent allocator test yields zero duplicate sequences.

Phase 2 - Idempotent submit and retry classification (12-18h). Derive Bull job id from idempotency key; write ledger row before network I/O; implement RetryClassifierService; guard workers against re-running confirmed keys. Deliverable: TransactionBuilderService.submit() writing the ledger, classifier unit tests. Exit check: transient-timeout integration test produces exactly one on-chain effect.

Phase 3 - Reconciler (8-14h). Implement ReconcilerService as a Bull repeatable job querying Horizon and contract events; converge non-terminal rows. Deliverable: reconciler with crash-recovery integration test. Exit check: worker-kill-after-submit test converges to confirmed with no duplicate.

Phase 4 - Frontend status and CI (8-14h). Wire GET /transactions/:id/status into TanStack Query with backoff polling; render the four states in app/payments; add backend CI migration-check job. Deliverable: status UI, CI step. Exit check: frontend end-to-end shows all four transitions; both CI pipelines green.