Designing a Concurrent Donation Ledger With FastAPI and PostgreSQL

Every year, billions of dollars flow through online crowdfunding platforms. Yet, one fundamental question continues to cause donor friction: "Where did my money actually go?"Traditional crowdfunding applications often function like black boxes. Payments are processed, progress bars increment, and confirmation emails are dispatched. However, under the hood, funds often linger in unverified balances without real-time auditability, milestone-driven fund releases, or atomic guarantees preventing double-allocations.When engineering GoodCause—a social impact and giving platform—the primary objective was to eliminate this transparency deficit. The goal was to construct a full-stack system that guarantees financial integrity, atomic fund distribution, and a seamless cross-platform mobile experience.This article breaks down the technical architecture of GoodCause, detailing how we integrated React Native (Expo Router), FastAPI domain micro-routers, and PostgreSQL Common Table Expressions (CTEs) to build an auditable impact ledger.System Architecture OverviewThe GoodCause architecture is designed around three core principles: Mobile-First Delivery, Atomic Auditability, and Strict Data Isolation.graph TD A[Expo Mobile App / iOS & Android] -->|HTTPS REST API / Bearer Token| B[Python FastAPI Gateway] A -->|Native OAuth / JWT| C[Supabase Auth] B -->|Atomic SQL Execution| D[(Supabase PostgreSQL + RLS)] A -->|In-App Subscriptions| E[RevenueCat Engine] B -->|Webhook Signature Verification| F[Paystack Payment Engine] Technology Stack & Component ResponsibilitiesLayerTechnologyKey ResponsibilityMobile ClientReact Native, Expo Router, TypeScriptCross-platform navigation, local state, secure hardware token storage (expo-secure-store).Backend APIPython 3.11, FastAPIDomain-driven routing (routes_campaigns, routes_donations, routes_impact, routes_payouts).Database & AuthSupabase (PostgreSQL + RLS)Data persistence, media storage, Row-Level Security policy enforcement.SubscriptionsRevenueCat SDKManagement of recurring tier commitments and Giving Circles.Payment GatewayPaystack + Custom Sandbox EngineWebhook processing with HMAC-SHA512 signature verification.The Engineering Challenge: Preventing Race Conditions & Double-SpendingIn a crowd-backed impact platform with concurrent donations and automated subscription payouts, multiple background workers or payment webhooks may attempt to credit a campaign or allocate funds simultaneously.Consider a scenario where two automated tasks try to apply a ₦50,000 impact allocation to a campaign that is only ₦30,000 away from reaching its funding target. A naive SELECT -> UPDATE query chain introduces a critical race condition:# ❌ INCORRECT: Non-atomic application logic (Subject to race conditions) campaign = await db.fetch_one("SELECT raised_kobo, goal_kobo FROM campaigns WHERE id = $1", campaign_id) if campaign['raised_kobo'] + allocation_amount = campaign.goal_kobo THEN 'COMPLETED' ELSE campaign.status END, updated_at = NOW() FROM claimed WHERE campaign.id = claimed.campaign_id RETURNING campaign.id AS campaign_id, campaign.raised_kobo, campaign.goal_kobo, claimed.amount_kobo ) SELECT * FROM credited; Architectural Advantages:Database-Enforced Atomicity: Both table updates execute within a single transaction block inside the PostgreSQL engine.State Machine Verification: WHERE allocation.status = 'ALLOCATED' ensures allocations cannot be processed twice. If a parallel process alters the status first, the claimed CTE yields 0 rows, automatically causing credited to NO-OP safely.64-Bit Integer Currency Accounting: All currency values are stored as 64-bit integers (amount_kobo / cents) to avoid floating-point arithmetic drift during aggregation.Milestone Evaluation & Automated Event NotificationsUpon successful atomic verification via _apply_paid(), the system calculates progress thresholds (25%, 50%, 75%, 90%, 100%) and dispatches notifications across the network:# Real-time milestone detection in backend/routes_donations.py MILESTONES = [25, 50, 75, 90, 100] previous_raised = ledger["raised_kobo"] - ledger["amount_kobo"] prev_pct = campaign_percent(previous_raised, ledger["goal_kobo"]) new_pct = campaign_percent(ledger["raised_kobo"], ledger["goal_kobo"]) reached = list(campaign.get("milestones_reached", [])) newly_reached = [m for m in MILESTONES if prev_pct = 100: set_fields["status"] = "COMPLETED" await db.campaigns.update_one({"id": ledger["id"]}, {"$set": set_fields}) # Trigger notifications for organizers and campaign followers for milestone in newly_reached: await dispatch_milestone_notifications(ledger["id"], milestone) Client Security & State Management in ExpoOn the mobile client, session tokens are stored using hardware-backed keychains via expo-secure-store to prevent token leakage on jailbroken or rooted devices:// Hardware-backed secure storage implementation import * as SecureStore from 'expo-secure-store'; export async function storeSessionToken(token: string): Promise { await SecureStore.setItemAsync('user_session_token', token, { keychainAccessible: SecureStore.WHEN_UNLOCKED_THIS_DEVICE_ONLY, }); } export async function getSessionToken(): Promise { return await SecureStore.getItemAsync('user_session_token'); } By maintaining hardware-enforced client security while offloading transactional constraints to PostgreSQL, GoodCause delivers native mobile speed alongside bank-grade data safety.Key Engineering LessonsShift Constraints to the Database Layer: Do not rely exclusively on application code or distributed locks for financial mutations. Leverage atomic SQL engine patterns like CTEs.Never Represent Money as Floating Points: Always store currency amounts in the smallest subunit (e.g., kobo, cents) using 64-bit integers.Domain-Driven Backend Isolation: Structure backend routes into dedicated domain modules (routes_donations.py, routes_payouts.py) to reduce coupling and simplify unit testing.ConclusionThe development of GoodCause demonstrates that combining modern mobile frameworks (Expo Router), performant API gateways (FastAPI), and robust PostgreSQL engine patterns allows developers to build social impact software that is scalable, performant, and fully auditable.