Architecting Digital Trust: A Relational Deep Dive into the LocalHands Prisma Schema

Subtitle: How to model a secure, escrow-based marketplace for emerging economies using Prisma and PostgreSQL.
Schema.

In my previous article on Medium, I discussed the sociotechnical challenge of Information Poverty in the African gig economy. But as engineers, we know that solving social problems requires more than vision it requires a robust, type-safe, and scalable data architecture.
For LocalHands, I chose Prisma ORM with PostgreSQL. The goal was to build a "Technical Source of Truth" that could handle the complexity of service listings, competitive bidding (proposals), and secure escrow payments.
Below, I break down the core relational logic of the LocalHands schema

1. The Core Actor Model: User vs. Profile
In a marketplace, users often play multiple roles. However, security is paramount. I separated the User (authentication and roles) from the Profile (sensitive KYC data)

model User {
  id               Int              @id @default(autoincrement())
  role             UserRole         @default(CLIENT)
  phoneNumber      String           @unique
  email            String           @unique
  passwordHash     String
  profile          Profile?
  // ... relations to orders, contracts, and services
}

model Profile {
  id                 Int              @id @default(autoincrement())
  userId             Int              @unique
  user               User             @relation(fields: [userId], references: [id])
  verificationStatus VerificationStatus? @default(PENDING)
  nationalIdUrl      String?          // URL to encrypted storage
  mobileMoneyNumber  String?
}

Engineering Decision: By using a 1:1 relation for the Profile, we keep the User model lean for frequent authentication checks while isolating heavier metadata and verification documents.

2. Modeling the Bidding Lifecycle (Service -> Order -> Proposal)
Unlike standard e-commerce, a service marketplace is dynamic. A client doesn't just "buy"; they post a ServiceOrder, and providers reply with Proposals.

model ServiceOrder {
  id            Int              @id @default(autoincrement())
  serviceId     Int
  clientId      Int
  budget        Float?
  status        ServiceOrderStatus @default(PENDING)
  contract      Contract?          // Only exists once a proposal is accepted
}

model Proposal {
  id           Int          @id @default(autoincrement())
  providerId   Int
  serviceId    Int
  bidAmount    Float
  status       ProposalStatus @default(PENDING)
  contractId   Int?
}

Relational Integrity: Notice the optional contractId in the Proposal. This allows multiple providers to bid on one job, but ensures that only the accepted proposal transitions into a formal, binding Contract.

3. The Trust Engine: Contract and Escrow
This is where the code solves the Trust Gap. The Contract model acts as the central node for the entire transaction lifecycle.

model Contract {
  id            Int              @id @default(autoincrement())
  serviceOrderId Int              @unique
  escrowAmount  Float
  status        ContractStatus   @default(ACTIVE)
  payments      Payment[]
  reviews       Review[]
}

By enforcing a @unique constraint on the serviceOrderId, we prevent the "Double-Payment" bug. The contract is the only entity authorized to trigger a Payment release.

4. Localized FinTech Integration
To meet the reality of the Cameroonian market, the schema explicitly supports MTN Mobile Money and localized currency settings.

model Payment {
  id            Int             @id @default(autoincrement())
  contractId    Int
  amount        Float
  paymentMethod PaymentMethod   @default(MTN_MOBILE_MONEY)
  status        PaymentStatus   @default(PENDING)
}

model SystemSettings {
  currency           String   @default("XAF")
  currency_symbol    String   @default("FCFA")
  payment_gateway     String   @default("fapshi")
}

Why this matters: Hardcoding these enums and settings at the database level ensures that the business logic remains consistent and compliant with regional financial regulations.

Conclusion
This schema is designed to do more than just store data; it is designed to enforce trust. By leveraging Prisma's powerful relational features, I have built a foundation where Information Poverty is replaced by a transparent, verifiable history of service.
What’s Next?
Currently, I am stabilizing the Escrow Algorithm and the Fapshi payment integration logic. In my next post, I will dive deep into the system UI then later "Fund-Lock-Release" cycle and real-time payment webhooks.