Building a Real-World Wallet

Mobile money platforms like M-Pesa, PhonePe, and Google Pay need to track accounts, balances, and transactions securely. In this capstone, you'll design the database schema for QuickCart Wallet, a mobile money service used in India and Kenya.

By the end, you'll understand:

  • Why certain tables and constraints matter
  • How to design for data integrity (CHECK constraints, NOT NULL)
  • How to write reporting queries that answer real business questions
  • How to handle complex aggregations (money in vs money out)
Real-world context: 15 customer accounts with 30 transactions across multiple currencies (INR, KES) and statuses (success, failed, pending, reversed). You'll seed this data and run reports on it.

Schema Design โ€” Why These Tables?

Two core tables: wallet_accounts and wallet_transactions

A mobile wallet needs to track who has money (accounts) and where it moved (transactions). Let's build them step by step.

wallet_accounts

Stores customer wallet information:

  • account_id โ€” Primary key, auto-increment
  • customer_id โ€” Links to customer record (NOT NULL)
  • phone โ€” Unique phone number (UNIQUE, NOT NULL)
  • balance โ€” Current balance with CHECK (>=0) to prevent negative
  • currency โ€” INR or KES (CHECKed list of valid values)
  • status โ€” active, frozen, or closed (CHECKed list)
  • created_at โ€” Timestamp for auditing

wallet_transactions

Stores every money movement:

  • txn_id โ€” Primary key, auto-increment
  • from_account โ€” Who sent money (FK to wallet_accounts)
  • to_account โ€” Who received money (FK to wallet_accounts)
  • amount โ€” Money moved, always > 0
  • txn_type โ€” deposit, withdrawal, transfer, or payment (CHECKed)
  • description โ€” User-provided note
  • status โ€” success, failed, pending, or reversed (CHECKed)
  • created_at โ€” When the transaction occurred
Design decisions:
โ€ข Constraints over application logic: CHECK (balance >= 0) prevents negative balances at the database level, not just in code.
โ€ข Foreign keys: from_account and to_account reference wallet_accounts, ensuring no orphaned transactions.
โ€ข Status column: Transactions can fail or be reversed. The status column tracks this without deleting the record (audit trail).

Schema Creation

Here's the complete SQL to create both tables with all constraints:

Create wallet_accounts

sql

Create wallet_transactions

sql

Run both queries above in psql to set up your tables. After you create them, move to the next section to seed data.

Seed Data โ€” 15 Accounts, 30 Transactions

Let's insert realistic wallet data. We'll create 15 accounts from India and Kenya with varying balances, then add 30 transactions.

Q1: Insert 15 Wallet Accounts

sql

Q2: Insert 30 Wallet Transactions

sql

Run both inserts. After this, your wallet has 15 accounts with 30 real-world transactions distributed across currencies and statuses.

Write Reports โ€” 5 Queries

Now that you have data, write queries to answer business questions. These are the types of reports wallet managers run daily.

Q3: Total Balance by Currency

Question: How much money is in wallets by currency?

sql
currency | accounts | total_balance
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
KES | 7 | 234718.59
INR | 8 | 178004.78

Key insight: Majority of funds are in KES. Currency mix helps with cross-border flows.

Q4: Transaction Volume by Type

Question: What types of transactions are most common, and what's the total value?

sql
txn_type | count | total
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
deposit | 8 | 23606.27
payment | 11 | 21042.27
transfer | 5 | 14464.60
withdrawal | 6 | 10376.06

Key insight: Payments are the most frequent transaction type. Deposits are the highest value (salary inflows).

Q5: Top 5 Accounts by Balance

Question: Which customers have the most money in their wallets?

sql
phone | balance | currency
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
+254722467350 | 48723.29 | KES
+254728611881 | 43922.79 | KES
+91-3210987654 | 31456.78 | INR
+91-5432109876 | 31456.78 | INR
+254788112233 | 31456.78 | KES

Key insight: Top users are in Kenya with KES accounts. This could inform VIP customer programs.

Q6: Failed Transactions

Question: What transactions have failed, and why? (for support/debugging)

sql
txn_id | amount | txn_type | description
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
6 | 2000.00 | transfer | Sent to friend
8 | 1800.00 | payment | Mobile recharge

Key insight: 2 failed transactions out of 30 = 6.7% failure rate. Investigate these in your payment gateway logs.

Challenge โ€” Money In vs Money Out

Question: For each account, how much money came in (received) and how much went out (sent)?

This is a complex query because you need to:

  • Sum transactions where the account is the to_account (money in)
  • Sum transactions where the account is the from_account (money out)
  • Handle accounts with no transactions (use LEFT JOIN and COALESCE)
Show solution
sql

Expected output sample:

phone | money_in | money_out
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
+254722467350 | 7288.36 | 588.72
+91-9876543210 | 3750.00 | 8984.56
+254728611881 | 5000.00 | 6345.67
...

What this query teaches:

  • CASE statements: Conditional aggregation (sum only when condition is true)
  • COALESCE: Handle NULL sums from accounts with no transactions
  • LEFT JOIN: Include accounts even if they have no transactions
  • OR in JOIN: Match accounts as either sender or receiver

Test Your Knowledge

Question 1 of 5
Why is CHECK (balance >= 0) important in wallet_accounts?
Question 2 of 5
What is the purpose of wallet_transactions.status?
Question 3 of 5
In the wallet_transactions table, from_account can be NULL. Why?
Question 4 of 5
What SQL keyword allows you to sum amounts only when to_account matches an account_id?
Question 5 of 5
What does COALESCE do in the money_in vs money_out query?
0
/5
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

You've designed your first database.

Join the Best AI Program: The AI-Era Data Engineer โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer โ†’