The invoice and the payment attempt answer different questions and should normally be different records.
Invoice or obligation record
- id
- merchant_id
- customer_id
- billing_period
- currency
- amount_due
- due_at
- status: DUE | PAID | CANCELLED | ADJUSTED
- satisfying_attempt_id, nullable
Payment attempt record
- id
- invoice_id
- wallet_or_method
- provider
- provider_request_id
- provider_transaction_id
- normalized_status
- raw_status or result code
- amount
- created_at and finalized_at
The relationship
One invoice can have many payment attempts. One normal successful attempt satisfies the invoice. A later second success is an exception, not a second paid invoice.
Why immutable attempts help
Do not overwrite attempt 1 with attempt 2. Keep the attempt ID and provider correlation stable, and preserve append-only provider event history. The attempt's current normalized status can still change as verified evidence arrives. That history makes late callbacks, support, duplicate detection and reconciliation easier.
Useful database constraints
- Prevent duplicate invoices for the same merchant/customer/billing-period key where the business model requires uniqueness.
- Make provider request identifiers unique within the relevant provider scope.
- Protect invoice closure with an atomic state transition.
A simple relational shape
The invoice table can hold the commercial amount due and a nullable satisfying_attempt_id. The payment_attempt table carries invoice_id as a foreign key plus provider and wallet details. Provider events can live in a third append-only table keyed to the attempt.
That structure gives the system one commercial record, many attempts and many provider events without overwriting history.
Keep amount semantics clear
Store money in a representation that avoids floating-point ambiguity, such as the smallest currency unit plus an ISO currency code. The invoice amount and the attempt amount should be independently visible so amount mismatches can become explicit exceptions.
Model external satisfaction deliberately
A customer can pay by bank transfer or another accepted route outside the preferred integration. Do not manufacture a fake provider attempt. Either create an external-satisfaction record or use a clearly identified manual attempt type with evidence and verifier metadata.
Useful invariants
- A paid invoice cannot return to Due because an older event arrived.
- One normal satisfying attempt is recorded for the invoice.
- Every attempt belongs to exactly one invoice or obligation.
- Provider request identifiers are unique in the appropriate provider scope.
- Raw provider state is never lost when normalized state changes.
These invariants are what make later recovery and reconciliation code easier to reason about.
Keep invoice history append-friendly
Plan changes, credits and cancellations should create explicit adjustments or state transitions rather than rewriting old payment attempts. Historical accuracy matters when finance or support reconstructs what the customer owed at the time.
