[Phase 1] PaymentAttempt & PaymentTransaction State Machine

[syed-reza98]

Priority: P0 (Critical)

Phase: 1 - E-Commerce Core
Epic Link: Checkout / Payments (Stripe Integration #27 placeholder)
Estimate: 4 days
Type: Story

Context

Introduce PaymentAttempt and PaymentTransaction models to ensure atomic, auditable payment flows with retriable, idempotent state transitions. Prevent double-charging, enable reconciliation, and support future split payouts (Marketplace epic #37).

Problem

Current architecture lacks discrete tracking of payment lifecycle states (INITIATED → AUTHORIZED → CAPTURED → REFUNDED / FAILED) and does not persist provider references or failure metadata, increasing financial and integrity risk.

Scope

• Model definitions (SQLite dev): PaymentAttempt, PaymentTransaction (later extend for Marketplace split payments)
• State machine with allowed transitions & validation rules
• Persistent providerReference (Stripe payment intent ID, etc.)
• Error & retry metadata (lastErrorCode, attemptCount, nextRetryAt)
• Idempotent create/capture endpoints
• Reconciliation job (daily) to detect orphaned states

Acceptance Criteria

• Prisma models created & migrated (incl. indexes: storeId+orderId, providerReference unique)
• API endpoint: POST /api/payments/attempt (idempotent key header) creates INITIATED attempt
• API endpoint: POST /api/payments/capture transitions AUTHORIZED → CAPTURED with validation
• Double capture prevented (conflict 409 returned)
• Failed attempt logs failure reason & increments attemptCount
• Daily reconciliation job flags attempts stuck > 15m in AUTHORIZING state
• Refund flow: POST /api/payments/refund creates REFUND transaction linked to original capture
• Audit log entries for each state transition (action: payment_state_change)
• All queries scoped by storeId

Data Model (Initial)

model PaymentAttempt {
  id              String   @id @default(cuid())
  storeId         String
  orderId         String
  provider        String   // stripe, bkash, cod
  providerReference String? @unique
  status          PaymentAttemptStatus @default(INITIATED)
  amount          Int      // minor units
  currency        String
  idempotencyKey  String?  @unique
  attemptCount    Int      @default(1)
  lastErrorCode   String?
  lastErrorMessage String?
  nextRetryAt     DateTime?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  transactions    PaymentTransaction[]

  @@index([storeId, orderId])
  @@index([status])
}

enum PaymentAttemptStatus {
  INITIATED
  AUTHORIZING
  AUTHORIZED
  CAPTURED
  FAILED
  CANCELED
}

model PaymentTransaction {
  id           String   @id @default(cuid())
  attemptId    String
  storeId      String
  type         PaymentTransactionType
  amount       Int
  currency     String
  providerReference String?
  createdAt    DateTime @default(now())

  attempt      PaymentAttempt @relation(fields: [attemptId], references: [id])

  @@index([storeId, attemptId])
}

enum PaymentTransactionType {
  AUTH
  CAPTURE
  REFUND
  VOID
}

State Machine Rules

• INITIATED → AUTHORIZING → AUTHORIZED → CAPTURED
• AUTHORIZED → VOID allowed
• CAPTURED → REFUND (multi refund allowed up to captured amount)
• Any → FAILED (terminal)

Dependencies

• Blocks: Reliable checkout completion
• Blocked By: [Phase 0] Database Schema Validation & Fixes #13 Schema validation (ensure migration process readiness)
• Enhances: Refund & Return Workflow (new P0 issue), Marketplace split payments ([Phase 5] Epic: Advanced Reliability #37)

Metrics

• Mean auth latency < 3s
• Double-charge incidents = 0
• Reconciliation discrepancies < 0.5% of attempts

Testing Checklist

• Idempotent attempt creation
• Failed authorization triggers retry scheduling
• Capture only after AUTHORIZED
• Refund limited to remaining refundable amount
• Audit log written for transitions

Risk

High financial & integrity risk if absent (score: 18). Addresses double-charging & orphaned payment states.

References

• docs/GITHUB_ISSUES_COMPARISON_ANALYSIS.md (risk matrix section)
• Stripe Payment Intent lifecycle