YeboSafe Database Models

YeboSafe uses Prisma ORM with PostgreSQL.

Merchant

Registered businesses using YeboSafe.

model Merchant {
  id         String   @id @default(cuid())
  name       String
  email      String   @unique
  password   String                // bcrypt hashed
  webhookUrl String?               // Default webhook endpoint
  isActive   Boolean  @default(true)
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
  
  // Relations
  apiKeys    ApiKey[]
  escrows    EscrowTransaction[] @relation("MerchantEscrows")
  wallet     MerchantWallet?
}

ApiKey

API keys for merchant authentication.

model ApiKey {
  id         String    @id @default(cuid())
  key        String    @unique       // Full key (hashed in prod)
  prefix     String                   // First 8 chars for identification
  name       String?                  // Optional friendly name
  isActive   Boolean   @default(true)
  merchantId String
  merchant   Merchant  @relation(fields: [merchantId], references: [id])
  createdAt  DateTime  @default(now())
  lastUsedAt DateTime?
}

EscrowTransaction

Core escrow record.

model EscrowTransaction {
  id             String        @id @default(cuid())
  reference      String        @unique @default(cuid())  // Public reference
  
  // Merchant
  merchantId     String
  merchant       Merchant      @relation("MerchantEscrows", fields: [merchantId], references: [id])
  
  // Payer info
  payerName      String?
  payerEmail     String?
  payerPhone     String?
  
  // Transaction details
  amount         Decimal       @db.Decimal(12, 2)
  currency       String        @default("USD")
  description    String?
  metadata       Json?                                    // Custom merchant data
  
  // Status
  status         EscrowStatus  @default(PENDING)
  completionCode String        @unique                    // 6-digit code for release
  webhookUrl     String?                                  // Override merchant default
  
  // Timestamps
  completedAt    DateTime?
  disputedAt     DateTime?
  disputeReason  String?
  cancelledAt    DateTime?
  cancelReason   String?
  refundedAt     DateTime?
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  
  // Relations
  logs           EscrowLog[]
}

enum EscrowStatus {
  PENDING     // Created, awaiting merchant acceptance
  ACCEPTED    // Merchant committed to deliver
  COMPLETED   // Completion code used, funds released
  REFUSED     // Merchant declined
  DISPUTED    // Dispute opened
  CANCELLED   // Cancelled before completion
  REFUNDED    // Funds returned to payer
}

EscrowLog

Audit trail for escrow actions.

model EscrowLog {
  id        String            @id @default(cuid())
  escrowId  String
  escrow    EscrowTransaction @relation(fields: [escrowId], references: [id])
  
  action    String            // 'created', 'accepted', 'completed', etc.
  actorId   String?           // Who performed the action
  metadata  Json?             // Additional context
  createdAt DateTime          @default(now())
}

MerchantWallet

Merchant balance tracking.

model MerchantWallet {
  id           String        @id @default(cuid())
  merchantId   String        @unique
  merchant     Merchant      @relation(fields: [merchantId], references: [id])
  
  balance      Decimal       @default(0) @db.Decimal(12, 2)
  currency     String        @default("USD")
  
  createdAt    DateTime      @default(now())
  updatedAt    DateTime      @updatedAt
  
  transactions WalletEntry[]
}

WalletEntry

Wallet transaction history.

model WalletEntry {
  id          String         @id @default(cuid())
  walletId    String
  wallet      MerchantWallet @relation(fields: [walletId], references: [id])
  
  type        EntryType
  amount      Decimal        @db.Decimal(12, 2)
  description String?
  reference   String?        // Link to escrow ID
  createdAt   DateTime       @default(now())
}

enum EntryType {
  CREDIT    // Funds added (escrow completion)
  DEBIT     // Funds withdrawn
}

WebhookEvent

Webhook delivery tracking.

model WebhookEvent {
  id         String   @id @default(cuid())
  merchantId String
  event      String                    // 'escrow.created', etc.
  payload    Json
  delivered  Boolean  @default(false)
  attempts   Int      @default(0)
  lastError  String?
  createdAt  DateTime @default(now())
}

Relationships Diagram

Merchant 1───────* ApiKey
    │
    ├──────────1 MerchantWallet 1──* WalletEntry
    │
    └──────────* EscrowTransaction 1──* EscrowLog
                        │
                        └───────────── WebhookEvent (tracked separately)

Example Flow

-- 1. Create escrow
INSERT INTO EscrowTransaction (merchantId, amount, status, completionCode)
VALUES ('merch_123', 100.00, 'PENDING', '123456');

-- 2. Accept
UPDATE EscrowTransaction SET status = 'ACCEPTED' WHERE id = 'esc_123';

-- 3. Complete (with code verification)
UPDATE EscrowTransaction SET status = 'COMPLETED', completedAt = NOW()
WHERE completionCode = '123456' AND status = 'ACCEPTED';

-- 4. Credit wallet
UPDATE MerchantWallet SET balance = balance + 100.00
WHERE merchantId = 'merch_123';

-- 5. Log transaction
INSERT INTO WalletEntry (walletId, type, amount, reference)
VALUES ('wallet_123', 'CREDIT', 100.00, 'esc_123');