Prisma schema blueprint

Status: Implementation reference, not yet a complete executable schema. Model names and constraints must be checked against chosen provider semantics.

Conventions

• PostgreSQL and Prisma migrations; application root without a src directory where applicable.
• UUID primary keys, createdAt, updatedAt, and explicit actor/audit references.
• BigInt integer minor-unit amounts for all financial values; ISO 4217 currency (AUD-only in v1 unless agreed otherwise).
• Decimal for pricing rates, never binary floating-point.
• All merchant-owned objects include merchantId; compound uniqueness for external identifiers.
• Do not persist raw card PAN, CVV, bank passwords or unencrypted provider secrets.
• Use dedicated append-only ledger tables; prohibit update/delete through application permissions.

Suggested Prisma excerpt

enum BillStatus {
  RECEIVED
  VALIDATED
  SCHEDULED
  COLLECTING
  COLLECTED
  FAILED
  CANCELLED
  EXCEPTION
}

enum AttemptStatus {
  CREATED
  SUBMITTED
  PENDING
  SUCCEEDED
  DECLINED
  FAILED
  UNKNOWN
}

model Merchant {
  id        String   @id @default(uuid()) @db.Uuid
  legalName String
  abn       String?  @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  bills     Bill[]
}

model Bill {
  id             String     @id @default(uuid()) @db.Uuid
  merchantId     String     @db.Uuid
  externalBillId String
  amountMinor    BigInt
  currency       String     @default("AUD") @db.VarChar(3)
  dueAt          DateTime
  version        Int        @default(1)
  status         BillStatus @default(RECEIVED)
  createdAt      DateTime   @default(now())
  updatedAt      DateTime   @updatedAt
  merchant       Merchant   @relation(fields: [merchantId], references: [id])
  attempts       PaymentAttempt[]
  @@unique([merchantId, externalBillId])
  @@index([merchantId, status, dueAt])
}

model PaymentAttempt {
  id                     String        @id @default(uuid()) @db.Uuid
  billId                 String        @db.Uuid
  attemptNumber          Int
  provider               String
  providerIdempotencyKey String        @unique
  providerPaymentId      String?
  status                 AttemptStatus @default(CREATED)
  createdAt              DateTime      @default(now())
  updatedAt              DateTime      @updatedAt
  bill                   Bill          @relation(fields: [billId], references: [id])
  @@unique([billId, attemptNumber])
  @@index([provider, providerPaymentId])
}

model LedgerTransaction {
  id          String   @id @default(uuid()) @db.Uuid
  eventKey    String   @unique
  currency    String   @db.VarChar(3)
  postedAt    DateTime @default(now())
  entries     LedgerEntry[]
}

model LedgerEntry {
  id            String            @id @default(uuid()) @db.Uuid
  transactionId String            @db.Uuid
  accountId     String            @db.Uuid
  amountMinor   BigInt
  // Signed amount convention: debit positive, credit negative.
  transaction   LedgerTransaction @relation(fields: [transactionId], references: [id])
  @@index([accountId, transactionId])
}

This excerpt deliberately omits the full LedgerAccount model and relations, consent, customer linking, collection and payout tables. It must not be pasted directly into production without completing those dependencies.

Database-level invariants

• CHECK amount > 0 for bills and payout instructions, except signed journal entries.
• Transactional assertion that journal entry signed amounts sum to zero per transaction and currency; Prisma schema alone cannot enforce this.
• Unique (provider, providerEventId) for webhook inbox.
• Unique (merchantId, externalBillId); bill version history stored separately.
• Unique (merchantId, operation, idempotencyKey) for request replay.
• Foreign keys on payout allocations and collection IDs.
• Reserve allocations atomically using locks or unique allocation constraints.

Migration order

Identity/merchant → consumer and consent → billing → provider inbox/outbox → payment attempts → ledger → settlement/collections → payout allocation → merchant pricing/invoices → refunds/disputes. Run migration verification and rollback rehearsals before live payments.