Schema for db

[ffaex]

When the project moved to prisma #13 , I would suggest to work out some schema files. Something crucial would be to use an ENUM for the order status, instead of VARCHAR(20). A suggestion would be to take a look at robosats Order model here.

After that we should enforce that the order changes the state in a controlled manner. So for example the /fiat-received route can only succeed when the order state is in "CHA = 9, "Sending fiat - In chatroom". And so on...

[ffaex]

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id             Int     @id @default(autoincrement())
  pubkey         String  @unique
  created_orders Order[] @relation("created_orders")

  orders_taken Order[] @relation("orders_taken")

  lock_funds_invoice lock_funds_invoice[]
  taker_hold_invoice taker_hold_invoice[]
}

model Order {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())

  amount_satoshis Int
  amount_fiat     Int
  currency        String    @db.VarChar(3)
  payment_method  String
  description     String
  type            OrderType

  status OrderStatus

  created_by_user_id Int  @unique
  created_by_user    User @relation(fields: [created_by_user_id], references: [id], name: "created_orders")

  taken_by_user_id Int?  @unique
  taken_by_user    User? @relation(fields: [taken_by_user_id], references: [id], name: "orders_taken")

  maker_hold_invoice maker_hold_invoice?
  taker_hold_invoice taker_hold_invoice[]

  lock_funds_invoice lock_funds_invoice?
}

enum invoice_status {
  PAID
  OPEN
  EXPIRED
  CANCELLED
  FAILED
}

model maker_hold_invoice {
  id     Int    @id @default(autoincrement())
  bolt11 String @unique

  expires_at     BigInt
  payment_hash   String
  payment_secret String

  status invoice_status

  orderId Int   @unique
  order   Order @relation(fields: [orderId], references: [id])
}

model taker_hold_invoice {
  id     Int    @id @default(autoincrement())
  bolt11 String @unique

  expires_at     BigInt
  payment_hash   String
  payment_secret String

  status invoice_status

  orderId Int   @unique
  order   Order @relation(fields: [orderId], references: [id])

  created_by_user_id Int
  created_by_user    User @relation(fields: [created_by_user_id], references: [id])
}

model lock_funds_invoice {
  id     Int    @id @default(autoincrement())
  bolt11 String @unique

  expires_at     BigInt
  payment_hash   String
  payment_secret String

  status invoice_status

  orderId Int   @unique
  order   Order @relation(fields: [orderId], references: [id])

  created_by_user_id Int
  created_by_user    User @relation(fields: [created_by_user_id], references: [id])
}

enum OrderType {
  BUY
  SELL
}

enum OrderStatus {
  WFB // Waiting for maker bond
  PUB // Public
  PAU // Paused
  TAK // Waiting for taker bond
  UCA // Cancelled
  EXP // Expired
  WF2 // Waiting for trade collateral and buyer invoice
  WFE // Waiting only for seller trade collateral
  WFI // Waiting only for buyer invoice
  CHA // Sending fiat - In chatroom
  INC // in chatroom
  FSE // Fiat sent - In chatroom
  DIS // In dispute
  CCA // Collaboratively cancelled
  PAY // Sending satoshis to buyer
  SUC // Successful trade
  FAI // Failed lightning network routing
  WFR // Wait for dispute resolution
  MLD // Maker lost dispute
  TLD // Taker lost dispute
}

This is a first idea