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.
