DEV Community: Gatusso

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

Gatusso — Mon, 10 Aug 2026 19:34:19 +0000

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.

The Numbers That Matter

Since launching the optimized version:

Login time: 8-12s → 0.8s (91% improvement)
Dashboard load: 30s → 2.3s (92% improvement)
Monthly reconciliation time: 4 hours → 20 minutes (92% improvement)
Member disputes: 5-8 per month → 0-1 per month (95% reduction)
Loan recovery rate: 67% → 89% (22% improvement)
But the best metric? The treasurer actually sleeps at night now.

What's Next?

We're not done. The roadmap includes:

Live M-Pesa API Integration: No more manual statement uploads.
SMS Notifications: "Your KES 150 penalty just accrued" alerts.
React/Django Pathway due to Streamlit UI restrictions.
Mobile App: Native iOS/Android for members.
Multi-Chama Support: Other groups want to use this.
Investment Tracking: MMF returns, SACCO dividends, etc.

*All the data used in the description of this app is dummy data for Data Protection purposes.

How I Built an End-to-End HR Attrition Dashboard Using MySQL & Power BI

Gatusso — Tue, 26 May 2026 17:37:53 +0000

Losing great employees is incredibly expensive for businesses. To show potential employers how I tackle real-world business problems using data engineering and visualization, I built an end-to-end HR Attrition Analysis project using the classic IBM HR Analytics dataset (1,470 employees, 35 features).

Here is exactly how I took this raw data from local SQL ingestion to an executive-ready Power BI dashboard.

🏗️ Step 1: Database Ingestion & Quality Checks (MySQL)

Enterprise data lives in relational databases, not flat CSV files. I started by spinning up a local schema in MySQL Workbench and importing the raw dataset.

Before running metrics, I performed a "sanity check" to ensure data integrity. I verified that there were zero duplicate records using the unique EmployeeNumber key and checked for missing values:

SQL
-- Checking for duplicates on the primary key
SELECT EmployeeNumber, COUNT(*) 
FROM hr_employee_attrition
GROUP BY EmployeeNumber
HAVING COUNT(*) > 1;

Result: 0 duplicates. The structural data health was clean.

🧹 Step 2: Data Cleaning & Transformation

A common mistake is overloading a BI tool with uncleaned data. To optimize performance, I built a permanent Database View to drop zero-variance columns (like StandardHours, which was identical for every employee) and transform text fields into binary indicators ($1$ and $0$).

SQL
CREATE VIEW vw_hr_attrition_clean AS
SELECT 
    EmployeeNumber, Age, Department, JobRole, MonthlyIncome, YearsAtCompany,
    CASE WHEN Attrition = 'Yes' THEN 1 ELSE 0 END AS Attrition_Flag,
    CASE WHEN OverTime = 'Yes' THEN 1 ELSE 0 END AS OverTime_Flag
FROM hr_employee_attrition;

This thin architectural layer makes calculating exact percentages downstream incredibly fast.

🔍 Step 3: Segmenting the Risk with SQL

Next, I used aggregation queries to pinpoint exactly where turnover was happening. I analyzed attrition rates across different departments and salary brackets:

SQL
-- Calculating Attrition Rate by Department
SELECT Department, COUNT(*) as Total_Employees,
       ROUND(AVG(Attrition_Flag)*100, 2) as Attrition_Rate
FROM vw_hr_attrition_clean 
GROUP BY Department 
ORDER BY Attrition_Rate DESC;

📊 Step 4: Connecting & Modeling in Power BI

Instead of using static exports, I connected Power BI directly to my local MySQL server using Import Mode.

To maintain clean DAX architecture, I created a dedicated measure matrix table and wrote explicit KPIs rather than relying on default column summaries:

Total Employees = COUNT(vw_hr_attrition_clean[EmployeeNumber])

Total Attrition = SUM(vw_hr_attrition_clean[Attrition_Flag])

Attrition Rate = DIVIDE([Total Attrition], [Total Employees], 0)

The BI Dashboard

💡 Step 5: High-Impact Business Takeaways

Data is just noise without strategic context. Based on the dashboard interactions, I identified three massive "flight risks" and drafted immediate HR action items:

The Overtime Smoking Gun: Employees logging chronic overtime exhibit a 30.6% attrition rate (3x higher than non-overtime peers).
Recommendation: Deploy an automated HR flag system when operational teams cross consecutive overtime thresholds.
The 1-Year Tenure Cliff: Attrition is heavily concentrated among employees in their first 12 months (>30%).
Recommendation: Revamp onboarding tracks with structured 30/60/90-day sentiment check-ins.
Sales Representative Volatility: Sales Reps had an outlier attrition rate of 39.8%, linked to low starting base pay (<$4k/month).
Recommendation: Restructure early compensation frameworks to favor a higher base salary over pure commission during year one.

Why My Baseline Random Forest Model Beat XGBoost: A Deep Dive into the Titanic Survival Prediction Dataset

Gatusso — Sun, 24 May 2026 14:02:03 +0000

A practical look at feature engineering, model optimization, and why simpler models sometimes win on smaller datasets.

When you start out in data science, you are often led to believe that there is a strict hierarchy of algorithms. You start with Linear Regression, move up to Random Forests, and eventually reach the holy grail: Gradient Boosting models like XGBoost. The assumption is usually that more complex equals better results.

But data science in the real world rarely follows a perfect script.

I recently built a survival classification model using the classic Titanic dataset for my portfolio. I set up an end-to-end pipeline, built a solid baseline, ran a rigorous hyperparameter grid search, and threw an XGBoost classifier at the problem.

The results threw me a curveball, and they taught me a massive lesson about data scale and model variance. Here is how I built the pipeline and what the results actually mean.

Before feeding any data into a machine learning model, it’s critical to understand that algorithms are essentially giant math equations. They don't understand context, and they don't handle missing data well. My workflow followed six key stages:

Exploratory Data Analysis (EDA): Finding the historical patterns.
Missing Data Imputation: Smart strategies to fill the blanks.
Feature Engineering: Creating high-signal columns from raw text.
Categorical Encoding: Transforming strings to numbers safely.
Model Evaluation: Setting up an 80/20 train-validation split.
Hyperparameter Tuning & Comparison: Pit baseline RF vs. GridSearch RF vs. XGBoost.

The Power of Feature Engineering

Most beginners simply drop text columns or fill missing values with a global average. To build a production-grade portfolio project, I implemented domain-specific feature engineering choices using pandas:

Smart Age Imputation via Titles: Instead of filling the 177 missing age values with the ship's average age (29), I extracted social titles (Mr., Mrs., Miss, Master) from the names. Because a "Master" is historically a young boy, filling his missing age with the median of the Master group is significantly more accurate than giving him an adult's age.
The Family Size Matrix: I combined SibSp (siblings/spouses) and Parch (parents/children) into a single FamilySize feature. Interestingly, data analysis showed that individuals traveling entirely alone or families larger than 5 had poor survival rates, whereas small families (2-4 people) fared much better.
Handling the Cabin Sparsity: Over 70% of the Cabin column was missing. Rather than dropping it, I turned it into a binary feature: Has_Cabin (1 or 0). This captured a massive socioeconomic signal, as 1st-class passengers were far more likely to have assigned, recorded cabins closer to the deck.

The Showdown: Comparing 3 Architectures

After splitting the data and encoding text variables into numerical binaries using pd.get_dummies(drop_first=True), I trained and evaluated three distinct setups on my validation data.

Here is how they performed:

Strategy	Validation Accuracy	Notes / Settings
1. Baseline Random Forest	82.68%	Simple setup, `max_depth=5`
2. XGBoost Classifier	82.12%	`learning_rate=0.05`, `max_depth=4`
3. GridSearchCV Tuned RF	81.56%	Optimized via 5-Fold Cross-Validation

The GridSearchCV block methodically checked variations of estimators, depths, and split criteria, ultimately landing on these optimal parameters:


python
{'criterion': 'entropy', 'max_depth': 6, 'min_samples_split': 10, 'n_estimators': 50}

Customer Shopping Behavior Analysis: Driving Retail Insights with Data

Gatusso — Tue, 19 May 2026 07:18:32 +0000

I recently completed an end-to-end Customer Shopping Behavior Analysis project using a dataset of 3,900 transactions. The objective was to uncover actionable insights into spending patterns, customer segments, product preferences, and subscription behavior to support strategic business decisions.

Project Approach

Data Preparation & Cleaning (Python)

Loaded and explored the dataset using pandas.

Handled 37 missing values in the Review Rating column by imputing with category-specific medians.

Standardized column names to snake_case, performed consistency checks, and dropped redundant features (e.g., promo_code_used was identical to discount_applied).
Created new features: age_group and purchase_frequency_days (converted textual frequency into numeric days).
Loaded the cleaned data into PostgreSQL for efficient querying.

SQL Analysis – Answering Key Business Questions I developed structured SQL queries in PostgreSQL to deliver clear business insights, including:

Revenue split by gender (Males: ~$157K vs. Females: ~$75K).

High-spending customers who used discounts.

Top 5-rated products per ratings.

Shipping type performance (Express vs. Standard).

Subscribers vs. non-subscribers comparison.

Discount dependency by product.

Customer segmentation (New, Returning, Loyal) based on purchase history.

Revenue contribution by age group.

Visualization & Storytelling Built an interactive Power BI dashboard featuring key KPIs, revenue breakdowns by category/age/subscription, sales trends, and filters for dynamic exploration.

Key Business Recommendations

Strengthen subscription programs with exclusive benefits to convert more loyal customers.
Implement targeted loyalty programs to grow the “Loyal” segment (currently 80% of customers).
Review discount strategy on high-dependency items (e.g., Hats, Sneakers) to protect margins.
Focus marketing on high-revenue age groups and customers preferring Express shipping.

How Data Science and Analytics are transforming industries today

Gatusso — Wed, 23 Apr 2025 08:08:10 +0000

Data science and analytics are more than simply IT departments' technical tools in today's hyper connected, digital-first world; they are essential to contemporary decision-making, innovation, and competitive advantage. Organizations in almost every industry now depend on data to inform strategy, streamline operations, customize consumer experiences, and predict future trends as a result of the rise of big data, artificial intelligence (AI), and machine learning (ML). Data science has the unquestionable ability to revolutionize a variety of industries, including marketing, manufacturing, healthcare, and finance.

Understanding Data Science and Analytics

It's crucial to understand the differences between data science and analytics before exploring their transformative potential. Data science is a multidisciplinary field that uses scientific methods, processes, algorithms, and systems to extract knowledge and insights from both structured and unstructured data, while data analytics is a subset of data science that specifically refers to the process of analyzing datasets to make inferences and guide decision-making. Together, they make a powerful combination: data analytics shows "what" is happening, while data science frequently delves deeper into "why" it's happening and "what might happen next."

Health Care: Precision and Predictive Medicine

Healthcare has seen a profound transformation thanks to data science. With the introduction of wearable technology, genomics, and electronic health records (EHRs), an unprecedented amount of data is now being collected and analyzed.
Predictive analytics in healthcare helps identify high-risk patients, potential epidemics, and the progression of diseases. For instance, by analyzing historical patient data, machine learning models can predict hospital readmissions or the onset of chronic diseases like diabetes.

Precision medicine, which customizes treatment plans based on individual variability in genes and lifestyle, is also becoming a reality thanks to advanced data analysis techniques. The Human Genome Project, and the subsequent rise of bioinformatics, owes much of its success to the computational power of data science.
AI models can mimic how drugs interact with the human body, predicting side effects and success rates even before clinical trials start. Pharmaceutical companies also use data science for drug discovery and development, which drastically cuts down on the time and expense needed to bring new drugs to market.

Finance: Risk Management and Algorithmic Trading

Although data has always been at the heart of the financial industry, its size and scope have significantly increased. These days, data science plays a key role in algorithmic trading, fraud detection, credit scoring, and client segmentation.

Banks can prevent fraud before it happens by using machine learning algorithms to detect suspect transaction patterns in real time. To evaluate credit risk more precisely than with conventional scoring techniques, credit card firms employ large databases and prediction models.

To create complex models that can evaluate market trends, forecast stock prices, and execute transactions in milliseconds, quantitative analysts, or quants, use data science in the trading industry. High-frequency trading (HFT) platforms, powered by AI, make transactions based on real-time data feeds, ensuring both speed and efficiency.

Additionally, budgeting programs like YNAB and personal finance apps like Mint leverage data analytics to provide users with personalized recommendations, spending insights, and saving techniques.

Retail: Personalization and Inventory Optimization

The clever use of customer data has permanently altered the retail environment. Retailers are more able to comprehend and predict customer behavior than ever before, whether through e-commerce platforms or physical storefronts with integrated digital systems.

One of the first companies to use data science to power recommendation engines is Amazon. Amazon provides highly tailored recommendations by examining previous purchases, search histories, and browsing habits, which raises conversion rates and improves consumer happiness.

Analytics are also used by retailers to improve inventory control. Businesses can lessen overstock and stockouts by using predictive models that forecast demand based on geographical trends, promotions, and seasonality. By guaranteeing product availability, this raises consumer satisfaction in addition to profits.

In order to stay responsive to public opinion, brands can make real-time adjustments to their marketing strategy and product offerings by using sentiment analysis, which involves examining social media posts and customer evaluations.

Manufacturing: Predictive Maintenance and Smart Factories

Smart manufacturing and the Industrial Internet of Things (IIoT), which both mainly rely on data science and analytics, are the driving forces behind Industry 4.0, the fourth industrial revolution.

Predictive analytics can be used to predict equipment failures based on the constant data generated by sensors integrated in manufacturing equipment. Predictive maintenance extends the life of machinery, improves operating efficiency, and decreases unscheduled downtime.

Data science is also essential to quality control, as computer vision and machine learning algorithms check products for flaws faster and more accurately than a human can.

In addition, entire smart factories are being constructed in which all of the components, from supply chains to assembly lines, are digitally connected and optimized in real time, and production is streamlined and scenarios tested using digital twins and simulations before any physical changes are made.

Marketing: Targeting, Optimization and Campaign Optimization

Without data, marketing in the digital age is like driving blind. With the help of data science, marketers can gain a detailed understanding of consumer behavior, enabling hyper-targeted campaigns that send the appropriate message to the right person at the right moment.

Marketers employ clustering algorithms to categorize consumers based on demographics, internet behavior, purchase history, and psychographics. These insights lead to more relevant adverts and content, enhancing engagement rates.

Marketers can optimize anything from landing page layouts to email subject lines by comparing performance metrics across variations using A/B testing, a basic analytical technique.

Additionally, social media analytics monitor brand sentiment and engagement on various platforms, providing businesses with a real-time understanding of public opinion. Data on audience demographics, reach, and engagement frequently serve as the basis for influencer marketing tactics.

Transportation and Logistics: Route Optimization and Autonomous Vehicles

Data has always been important to logistics companies like UPS and FedEx, but recent advances in analytics have boosted their operations. For example, ride-sharing services like Uber and Lyft use real-time analytics for dynamic pricing, demand forecasting, and driver allocation; these platforms predict where demand will surge and deploy drivers accordingly, minimizing wait times and maximizing profits. In the world of autonomous vehicles, data science is essential: self-driving cars process massive amounts of sensor data in real time, from cameras and LiDAR to radar and GPS, to make safe and effective driving decisions.

Challenges and Ethical Considerations

Data science and analytics present significant ethical and technical issues despite their advantages. Concern over data privacy is growing, especially as more businesses gather and keep private data. These issues have been addressed by regulatory frameworks such as the CCPA and GDPR, but compliance and enforcement are still difficult.

Algorithm and data bias is another significant problem. In industries like hiring, lending, and law enforcement, in particular, predictive models have the potential to reinforce existing disparities if a dataset is biased or lacking.

Additionally, there is a bottleneck for companies looking to implement these technologies since the demand for qualified data scientists frequently outpaces the supply. For insights to be not only generated but also applied successfully, companies need to invest in data literacy for all employees.

The Future: Data Driven Everything

In the future, augmented intelligence—where people and robots collaborate to make smarter decisions—is probably going to be a reality as computing power keeps increasing and data becomes progressively more abundant. Data science will be pushed to its limits by technologies like edge computing, quantum computing, and real-time analytics.

In order to address some of the most important issues facing the globe today, from food security to climate change, emerging sectors like agritech, edtech, and climate tech are also starting to use data-driven approaches.