[BUG] Race condition in payment deduplication - concurrent workers can double-execute rules

[thisisouvik]

Description

In backend/src/engine/processor.ts, step 1 performs a deduplication check:

const existing = await sql`SELECT 1 FROM AutomatedTransaction WHERE txHash = ${paymentHorizonId} LIMIT 1`;
if (existing.length > 0) return { skipped: true };

However, there is no database-level UNIQUE constraint on txHash. Under concurrent BullMQ workers (concurrency: 5), two workers can both pass the SELECT check before either inserts - causing the same payment event to be processed twice, resulting in duplicate on-chain transactions and double vault deposits.

Steps to Reproduce

1. Configure BullMQ with concurrency: 5.
2. Send a payment to a monitored wallet.
3. Observe two identical rows in AutomatedTransaction with the same txHash.

Suggested Fix

ALTER TABLE AutomatedTransaction 
ADD CONSTRAINT AutomatedTransaction_txHash_key UNIQUE (txHash);

Then handle the unique violation error in the processor as a skip.

Impact

Critical - Users can have funds double-moved from their vaults on every incoming payment.

[Cerome360]

@Cerome360 has applied to work on this issue as part of the Stellar Wave Program's 8th wave.

Hello maintainer, I'd be delighted to work on this. Please assign

ℹ️ Repo Maintainers: To accept this application, review their application or assign @Cerome360 to this issue.

[Samanway2405]

@Samanway2405 has applied to work on this issue as part of the Stellar Wave Program's 8th wave.

i would like to work on this issue

ℹ️ Repo Maintainers: To accept this application, review their application or assign @Samanway2405 to this issue.