Gatusso

Posted on Aug 10

Building a Fintech App for a Kenyan Chama: From Local Development to Aiven PostgreSQL at Scale

#database #automation #dashboarding #cloud

How I built a contribution tracking system for my investment group and learned hard lessons about database performance along the way.

The Problem That Started It All

It was March 2026, and our chama (that's "investment group" in Swahili) was in chaos. Twelve members. Monthly contributions via M-Pesa. Loans being issued. Penalties for late payments. Everything was tracked in... wait for it... a WhatsApp group and a poorly maintained Excel Sheet.

When Brian (let's call him that) checked his standings in the excel sheet that month, he saw he owed KES 10,150. But was that right? Had his KES 1,530 M-Pesa payment from July 3rd been counted? What about the KES 150 penalty for his Merry Go Round contribution? And that loan he took in April, was the monthly penalty accruing correctly?

Nobody knew. The treasurer was overwhelmed. Members were confused. Trust was eroding. That's when I decided to build something better.

Enter Chama Contribution Tracker

Fast forward to today, and here's what Brian sees when he logs in:

Clean. Clear. No confusion.

In just a few seconds, Brian can see:

His Merry-Go-Round balance: KES 10,150 of KES 12,180 expected (KES 2,030 in arrears)
His active loan: KES 12,850 outstanding, due August 12, 2026
His WEB project contributions: KES 4,500
His group standing: "Action needed" with a warning that SHER for July is outstanding

But this isn't just a pretty dashboard. It's a complete financial management system built for the unique way Kenyan chamas operate.

What Makes Chama Finance Different?

If you're not familiar with chamas, here's the deal:

Monthly Obligations:

MGR (Merry-Go-Round): KES 1,500/month mandatory contribution
SHER (Group Activities): KES 500/month for group activities (some members are exempt)
WEB (Web Project): Contributions to specific group projects

The Complexity:

Payments come via M-Pesa (Kenya's mobile money system)
If you pay after the 10th of each month, you get a KES 150 penalty
If you don't pay at all, you still get the KES 150 penalty
Members can take loans (principal + 10% interest)
Late loan repayments accrue KES 1000 monthly penalties
Every month, one member gets a KES 6,800 payout from the MGR pool
There's a KES 100 group Welfare from the member savings.

Try tracking that in Excel. I dare you.

The Tech Stack: Why Streamlit + PostgreSQL?

I'll be honest, I didn't start with the "perfect" architecture. I started with what I knew:

Frontend: Streamlit (Python-based, rapid prototyping)
Database: PostgreSQL (initially local, then migrated to Aiven)
ORM: SQLAlchemy with raw SQL for performance-critical queries
Deployment: Streamlit Community Cloud
Data Processing: Pandas for M-Pesa statement parsing

Why Not React/Django? Or Dash?

I evaluated them. Seriously. But here's the thing: the bottleneck was never the frontend framework. It was database query performance and network latency.

Streamlit let me iterate fast. When the treasurer said, "Can we see penalties broken down by month?" I could add that in an hour. When members wanted a visual chart of their contributions vs. obligations, it was an afternoon project.

The Performance Nightmare (And How I Fixed It)

Here's where the story gets interesting.

Phase 1: Local Development (Blazing Fast!)

On my local machine with PostgreSQL running on localhost, everything was instant. Login: 50ms. Dashboard load: 200ms. Perfect.

Phase 2: Aiven Migration (Everything Broke)

When i moved to Aiven PostgreSQL (hosted in Europe, while our users are in Kenya), login times jumped to 8-12 seconds. The dashboard? Sometimes 30+ seconds. Members thought the app was broken. I panicked. Was Streamlit the wrong choice? Did I need to rewrite everything in a "real" framework?

Phase 3: The Optimization Journey

Instead of rewriting, I profiled. Here's what I found:

Problem 1: N+1 Queries Everywhere

python
# BAD: Loading member dashboard made 15+ separate queries
opening_balance = get_opening_balance(member_id)
contributions = get_contributions(member_id)
loans = get_loans(member_id)
# ... and so on

Solution: Batch Queries with Caching

@st.cache_data(ttl=45)
def load_member_dashboard(member_id: int, is_sher_exempt: bool):
    # Single connection, multiple queries in one batch
    with eng.connect() as conn:
        opening_balance = conn.execute(opening_query).scalar()
        loan_rows = conn.execute(loan_query).fetchall()
        web_total = conn.execute(web_query).scalar()
        # ... all in one connection

Result: Dashboard load time dropped from 30s to 2-3 seconds.

Problem 2: No Database Indexes

Our loans table query was doing a sequential scan:

SELECT * FROM chama.loans 
WHERE beneficiary_id = 1 
AND status IN ('ACTIVE', 'DEFAULTED')

Solution: Strategic Indexes on Aiven

CREATE INDEX CONCURRENTLY idx_loans_beneficiary_status 
ON chama.loans (beneficiary_id, status);

CREATE INDEX CONCURRENTLY idx_contrib_member_fy_month 
ON chama.contributions (member_id, financial_year_id, month);

CREATE INDEX CONCURRENTLY idx_members_phone_normalized 
ON chama.members (REPLACE(phone_number, '-', ''));

Result: Query execution time dropped from 150ms to 0.059ms.

Problem 3: Connection Pooling

Streamlit reruns the entire script on every interaction. Without connection pooling, we were opening/closing database connections constantly.

Solution: PgBouncer + SQLAlchemy Pool

@st.cache_resource
def get_engine():
    return create_engine(
        DATABASE_URL,  # Aiven PgBouncer transaction mode URI
        pool_size=5,
        max_overflow=10,
        pool_pre_ping=True,  # Keep connections alive
        pool_recycle=300,    # Recycle every 5 minutes
        connect_args={"sslmode": "require"},
    )

The Admin Dashboard: Power Without Complexity

Now let's look at what the treasurer sees:

Key Metrics at a Glance

M-Pesa Collected (July): KES 128,450 (↑ 8.4% vs June)
Loans Disbursed (YTD): KES 415,000 (14 active loans)
Penalties Applied (July): KES 2,850 (19 penalty rows)
Group Reserve Balance: KES 86,320 (includes member savings)

The M-Pesa Ingestion Workflow

Here's where the magic happens for admins:

Upload: Treasurer downloads the monthly M-Pesa statement (CSV/Excel) and uploads it

Parse: The system automatically:

Extracts phone numbers, names, amounts, dates
Filters out "Charge" and "Reversal" transactions
Normalizes data formats

Allocate: Admin matches each transaction to:

A member (by phone number or name)
A category (MGR, SHER, LOAN, WEB)

Confirm: System shows a preview before committing
This used to take the treasurer 3-4 hours per month. Now? 20 minutes.

Lessons Learned

Don't Prematurely Optimize (But Do Profile Early)
I spent weeks worrying about Streamlit vs. Dash when the real problem was missing database indexes. Profile first, optimize second.
Connection Pooling is Non-Negotiable for Remote Databases
When your database is in a different continent, every connection handshake costs you. PgBouncer + SQLAlchemy pooling cut our latency by 60%.
Transparency Builds Trust
When members can see exactly where every shilling went—and why they owe what they owe, arguments disappear. The data doesn't lie.
Edge Cases Will Haunt You
What if a member pays via M-Pesa but has no active loan? (Payment gets discarded)
What if a member is both borrower AND beneficiary on different loans? (Track both)
What if the treasurer uploads the same M-Pesa statement twice? (Idempotency checks)
Performance is a Feature
When login takes 10 seconds, members think the app is broken. When it takes 1 second, they think it's magic. Speed is UX.